Library Subject Guides
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.
AVERAGE, COUNT, COUNTA, COUNTBLANK, COUNTUNIQUE, MAX, MIN, SUM
AVERAGEIF, AVERAGEIFS, COUNTIF, COUNTIFS, IF, MAXIFS, MINIFS, SUMIF, SUMIFS
Calculations and functions for dates and times
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
UNIQUE
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 function (Google Sheets)
QUERY, TEXT, PIVOTBY
HSTACK, TOCOL, TOROW, VSTACK, WRAPCOLS, WRAPROWS
IMAGE
SPARKLINE
Import functions in Google Sheets
IMPORTDATA, IMPORTFEED, IMPORTHTML, IMPORTRANGE, IMPORTXML
FILTERXML, WEBSERVICE
Built-in web service functions
DETECTLANGUAGE, GOOGLEFINANCE, GOOGLETRANSLATE, STOCKHISTORY
ARRAYFORMULA, ISBLANK
There are two spreadsheet applications available to use at the University of York. Let's take a look at them:
Microsoft ExcelExcel contains a wealth of features that have helped make it become the dominant spreadsheet tool across the world. There are multiple versions available:
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 SheetsGoogle 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.
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.
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:
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
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 sessions on :
There's more training events at:
