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, TRIM, TRUNC, UPPER
UNIQUE
SORT, SORTBY, TRANSPOSE
FILTER, UNIQUE
HLOOKUP, INDEX, LOOKUP, MATCH, VLOOKUP, XLOOKUP
ARRAYFORMULA, COLUMN, IF, IFERROR, MOD, OFFSET, QUERY, ROUND, ROW, SPLIT, TRANSPOSE, TRIM
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:
Excel is available as part of Microsoft Office 365 (free to students and staff) and earlier 'Office' versions (installed on University machines). In common use throughout the world, it contains a wealth of features. But it's not so easy to work with in a collaborative context, or when you won't have consistent access to the same computer or system.
Google Sheets is part of the Google Workspace suite of apps (via any Google account). It 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.
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:
