Skip to Main Content
University of York Library
Library Subject Guides

Essential Spreadsheets: a Practical Guide

Essential Spreadsheets

Spreadsheets were developed as a tool to store, analyse and manipulate numerical data. They are now commonly used for working with sets of data containing both text and numbers, and for generating graphs and charts. They're also brilliant. Let's play with

Essential Spreadsheets: a Practical Guide

Spreadsheets were developed as a tool to store, analyse and manipulate numerical data. They are now commonly used for working with sets of data containing both text and numbers, and for generating graphs and charts. In this guide we take a look at the two spreadsheet tools available at the University of York: Microsoft Excel and Google Sheets.


Contents:

AVERAGE, COUNT, COUNTA, COUNTBLANK, COUNTUNIQUE, MAX, MIN, SUM

AVERAGEIF, AVERAGEIFS, COUNTIF, COUNTIFS, IF, MAXIFS, MINIFS, SUMIF, SUMIFS

DATE, DATEDIF, DATEVALUE, DAY, DAYS, EDATE, EOMONTH, HOUR, ISOWEEKNUM, MINUTE, MONTH, NETWORKDAYS, NETWORKDAYS.INTL, NOW, SECOND, TIME, TIMEVALUE, TODAY, WEEKDAY, WEEKNUM, WORKDAY, WORKDAY.INTL, YEAR, YEARFRAC

ABS, AVERAGE, CEILING, CONCAT, CONCATENATE, EVEN, FLOOR, INT, JOIN, LEFT, LEN, LOWER, MEDIAN, MID, MOD, MODE, ODD, PROPER, RAND, RANDBETWEEN, RIGHT, ROMAN, ROUND, ROUNDDOWN, ROUNDUP, SPLIT, SUBSTITUTE, SUBTOTAL, TEXTJOIN, TEXTSPLIT, TRIM, TRUNC, UPPER

SORT, SORTBY, TRANSPOSE

FILTER, UNIQUE

HLOOKUP, INDEX, LOOKUP, MATCH, VLOOKUP, XLOOKUP

ARRAYFORMULA, COLUMN, FLATTEN, HSTACK, IF, IFERROR, MOD, OFFSET, PIVOTBY, QUERY, ROUND, ROW, SPLIT, TRANSPOSE, TRIM

QUERY, TEXT, PIVOTBY

HSTACK, TOCOL, TOROW, VSTACK, WRAPCOLS, WRAPROWS

IMAGE

SPARKLINE

IMPORTDATA, IMPORTFEED, IMPORTHTML, IMPORTRANGE, IMPORTXML

FILTERXML, WEBSERVICE

DETECTLANGUAGE, GOOGLEFINANCE, GOOGLETRANSLATE, STOCKHISTORY

ARRAYFORMULA, ISBLANK

Spreadsheet software

There are two spreadsheet applications available to use at the University of York. Let's take a look at them:


Microsoft Excel

Excel contains a wealth of features that have helped make it become the dominant spreadsheet tool across the world. There are multiple versions available:

  • Excel is available as part of Microsoft 365 (free to students and staff at York). Newer staff laptops at the University will also have this version;
  • Classroom PCs around campus have the 'Office 2021' version of Excel installed, while older staff computers at York may have Office 2019 or in some cases even 2016.

Excel for Microsoft 365 contains some collaborative features, though not all options are available at York. In other regards, Excel is not typically so easy to work with in a collaborative context, not least when you don't have consistent access to the same computer or system.


Google Sheets

Google Sheets is part of the Google Workspace suite of apps (available via any Google account — if you're a member of the University of York, sign into Google Sheets with your University email address and password).

Sheets has very similar functionality to Excel, with some extra features that make collaboration easier. And as it runs in a browser you can access your material on almost any device with web access.

It integrates well with other Google applications, and can import and export in Excel format.

Which is better?

Both tools are brilliant, and in recent years they've been in something of a mutually beneficial development race: when one tool gets a useful new feature, the other tends to incorporate it soon afterwards.

Excel is the best option for particularly large datasets, and for some advanced visualisation and data manipulation features. Google is the best for collaboration and for working 'on-the-fly' with 'live' (changeable) data.

But for most things, the two will work just as well as each other and a lot will therefore come down to whichever you prefer or whichever is the most convenient.

Throughout this guide we'll highlight the occasional differences between the two programs as they arise.

Exercises

This site has been arranged into three sections:

The P and T sections have accompanying exercises for you to try. All the files can be found in:



Google Sheets

The Google Sheets in those folders are shared as 'view only'. In each case you will need to make copies of the files in order to edit them. So long as you're signed into a Google account, just open each file and follow the prompt, or go to File > Make a copy

Excel

You can download the whole folder in Google Drive as a zip file: right click on the folder name in Google Drive and select Download. Once unzipped, the files in the downloaded folder will be in Excel and Word format.

Excel versions of the exercise files can also be found on university-managed machines at T:\IT Training\Essential Spreadsheets

You can map to that drive at home if you use the University's VPN.

Not sure how to navigate these pages? We've put the contents and an index of functions into a Google Sheet!

For more support, including face-to-face training, take a look at:

We open an online version of the taught course to all members of the University. If that's running, it will be listed below.

Forthcoming training sessions

Forthcoming sessions on :

Taught students
Staff
Researchers
Show details & booking for these sessions

There's more training events at:

Feedback
X