Skip to Main Content
University of York Library
Subject Guides

Essential Spreadsheets: a Practical Guide

T3: Data sets

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

It's time to get stuck in with some data. We begin by looking at the idea of data validation, by which we can make sure our data is consistently entered. We then look at what makes a good list structure we can work with in a spreadsheet, before going about sorting and filtering our dataset. We also have a quick look at subtotaling in Excel.

Working with lists and data

Although originally designed for numeric data, spreadsheets are a powerful tool for working with broader sets of data that include text values. As well as performing calculations, we can manipulate and interrogate data in other ways, such as sorting and filtering.

A quick web search will render several definitions of ‘data’, the common theme being that they are values without context. Processing data in some way provides context and hence meaning - when it may be referred to as ‘information’. Even simple operations with a spreadsheet require some understanding of what constitutes ‘good’ data.

As an example, let’s ‘deconstruct’ some information:

“The appointment with Dr Watt is on Tuesday at 2:30pm at the Heslington Lane surgery.”

This information contains the following fields of data:

  • Who the appointment is with
  • The day (date) of the appointment
  • The time of the appointment
  • The location of the appointment

If you wanted to record appointments in a computer-based system you would need to use separate ‘fields’ for these — which would translate to separate columns in a spreadsheet.

Data types

Data processing systems struggle if you don’t stick to recognised data types, or if you add in values that don’t match others in the same context:

Data typeGoodUnrecognisable
Number5
1.6
-350
0.105
About 10
>5
10-15
25cm
Date/time01/01/2000
23-11-1963
15:30
17:16:20
01.01.2000
Mon or Fri
Next Tuesday
About 10:30
BooleanTrue
False
Maybe
?

Spreadsheets attempt to determine the data type whenever you enter a value. It will be treated as text if it is not recognised as anything else. This may have an affect on how you're able to interrogate that data. If you represent a number or date in a way that does not allow the program to determine its type correctly, you will not be able to sort and filter correctly, you will not be able to add up, find averages, find the interval between two dates...

Relational data

Sometimes a single table of rows and columns is not enough. For instance:

You need to work with information about people and the research projects they are involved in. There will be several fields of data about the people, but also several about the projects.

It would be impossible to design one table that is suitable to hold all the data about people and projects, so in this case we create separate tables – one for people and one for projects – and find ways to express the connections between them

In this example, one person can be involved in many projects, and one project can involve many people. This is a clear indication that the data is relational, and any attempt to work with it using a simple table will entail compromises.

Spreadsheets are not very good with relational data; you often end up with something that 'sort of' works.

If you're using a spreadsheet to store and process complex data, you should be aware of the advantages of using a relational database tool such as Microsoft Access. If your data includes several related sets, or a lot of repeated data, you should probably investigate using a database rather than a spreadsheet. The data can be exported to a spreadsheet for further numeric analysis if needed.

Excel's Data Model tools are looking more and more like Access with every update. They allow you to connect multiple data sources and combine them, relationally, to build queries and advanced pivot tables.

Relational databases can seem daunting at first, and simple options are limited: Access is very much a desktop database, and other options generally require a larger degree of familiarity with database systems. If online operation, access control, and collaboration are key requirements – or if you need to integrate with Google Forms, Mail, or Calendar – Google Sheets would likely be the most sensible option to explore.

Data validation

The success of any data processing will depend in large part on the quality of the source data you're working with. While you can never ensure 100% accuracy of data entered into a spreadsheet, you can take steps to minimise the likelihood of any errors and improve consistency in the data entered.

Validation is about making sure the data entered is reasonable, at the point it is entered by the user. This can be achieved by:

  • Checking the type of data entered into a cell
  • Testing data to see if it lies within a sensible range
  • Providing lists of values for a user to pick from

For example, you might:

  • Ensure that cells which should only contain valid dates can’t include plain text (e.g. ‘Next Tuesday’)
  • Check that an entered date of birth isn’t in the future
  • Decide on a maximum price and prevent very large values being entered
  • Provide options such as ‘Pending review’, ‘Approved’, ‘Rejected’ etc., as a drop-down list – this is particularly useful if you will later do analysis on this data (where spelling mistakes could introduce errors)

ExcelValidation in Excel

  1. Select the cells to which you wish to apply a particular validation rule
  2. Choose Data > Data Tools > Data Validation to open the Data Validation dialogue:
  3. Excel's Data Validation dialogue has a number of settings spread across three tabs
  4. On the Settings tab, choose the validation criteria you require: after choosing the data type in the Allow option, the other settings will change to match, so configure these as necessary
  5. The Input Message tab lets you prepare a prompt to appear when the cell is chosen. Over-use of this can be annoying!
  6. The Error Alert tab is more important, as you can select how to respond when invalid data is encountered. The options are:
    • Stop – Prevent the data being entered
    • Warning – Advise the user that the value is not valid, but give them the option to continue entry if they prefer
    • Information – Let the user know the data is invalid, but let them carry on if they prefer (this is essentially the same as Warning, but Warning looks more scary!)

Picking from a list

If you select the List option for allowed data, you are asked to provide a source for this list, either as a cell-reference range or named range. Here's some points to note:

  • The sources list can be on another sheet in the spreadsheet
  • Choosing this List option provides a drop-down control on the cell when selected
  • If the "Error Alert" tab is set to Stop, only values in the drop-down can be used, while Warning and Information allow the user to enter a value not on the list
  • List-based validation improves consistency as it forces users to choose from a limited set of possibilities, and so can make sorting/filtering more reliable.

Google SheetsValidation in Google Sheets

  1. Select the cells to which you want to apply validation
  2. Choose Data > Data validation (or right-click Data validation) to bring up the Data validation dialogue:
  3. Google's 'Data validation' dialogue has similar settings to Excel's but all on the one panel
  4. Configure the Criteria as necessary
  5. Choose whether to Show a warning on invalid data entry or Reject input outright
  6. If Show validation help text is ticked, the message in the line below appears when you hover over the cell.

Picking from a list

There are two list options you can choose from in the "Criteria" list:

  • List from a range – you will need to define a cell range or named range (usually on a another worksheet)
  • List of items – the list is entered directly in the dialogue box (avoid using this except for simple, unchangeable lists like Yes, No)

Tick box

From the "Criteria" menu it is possible to generate tick boxes in Google Sheets (you can also add them from the Insert menu).

Values can be assigned to the ticked and unticked cells. The default values are TRUE and FALSE but you can also assign numbers or text. You can then use these values in another part of your workbook by referencing the tick box cells accordingly.

Generating a validation list from existing data values

If you use a column from an existing dataset as the range for dropdown list values, a list is automatically generated containing one of each of the unique values in that column (rather than a full list of the whole column).

It is also possible to generate a list of unique values from a range using the UNIQUE function (in Google Sheets and Excel for Microsoft 365) — but more on that in a moment!

Tip

It's a good idea to create the content for your dropdown lists at the top of an otherwise empty column on another sheet: that way, not only does it keep things tidy, but you can also select the whole column for the list range — any new items added to this column will automatically appear in the dropdown list.


Validating using custom formulae

You can validate according to a number of criteria including whether or not the validation matches the outcome of a particular formula.

When entering a custom formula, start with "=" as usual. Cell references work relatively from the first cell in your selected range, so if you're applying validation to cells B2:B10 and want to apply a condition based on the corresponding values in column A, refer to cell A2 in your formula.

Remember: validation tests what's allowed in the cell, not what's prohibited. Be sure to test your validation rigorously!

List structure

The way you organise data can also have a huge impact on your ability to process it. The most common approach is using 'tabular' form - a two-dimensional table - which is why spreadsheets are frequently used.

To make full use of the available features when working with structured sets of data (and to make your spreadsheet more accessible, too), some simple rules are best observed:

  • Lists of tabluar data should be entered down the sheet, with each new item occupying a new row
  • Each column should contain one type of information (e.g. text, number, date)
  • Each cell should contain just one value
  • Enter column headings in one row at the top of your list. Making them bold helps the spreadsheet understand that they're headers (and also improves accessibility). Never use more than one row, and never merge cells for headings (if you need to do either of these things, put an empty row between them and your main headings)
  • Do not leave whole rows or columns empty (though blank cells where the information is not applicable/unknown are OK)
  • Anything that's not part of your list should be an empty column or row clear of it (or better still on a different sheet)
  • Stick to one list per sheet (tab) of your workbook.

Here's an example of a good (albeit rather short) list:

fx
ABCD
1 MonthFruitNumber
2 JanApple22
3 JanOrange23
4 FebApple31
5 FebOrange18

...and here's some common mistakes to avoid:

  • Store one attribute per column: don’t combine values. For instance, a full name in one column makes it impossible to sort/filter by family name, so split titles and names into multiple columns
  • Stick to one data type in a column. For instance, in a column of dates, "c.1995", "1990s", and "1990-1999" may look like dates to us but you may struggle to do much useful with them — if you need to retain the integrity of your dataset but also do analysis on it, perhaps have a second column of 'parsed' data that is corrected to match an appropriate data type.

Working with larger lists

If you're working with a list that is longer/wider than your screen, and labels keep disappearing when you scroll, or you wish to compare data from cells that are a way apart, try one of these approaches:


Zoom

Changing the zoom level can help if the list is only slightly bigger than the current screen:

Excel

The zoom controls are at the bottom right of the Excel window, or you can go to View > Zoom.

Google Sheets

As Google Sheets is a browser application, use the browser's built-in zoom options: conventionally Ctrl (or Cmnd) and + / -.


Freeze panes

This feature 'locks' rows and/or columns in position so they remain on the screen as you scroll:

Excel

Select the cell immediately below and to the right of the rows/columns you want to freeze, and choose View > Window > Freeze panes > Freeze Panes. Use the same control to unfreeze.

Google Sheets

If you want to freeze more than two rows or columns, first select the cell that is in the last row/column you want frozen, then choose View > Freeze and select as appropriate (rows and columns are treated separately). Use the same menu to unfreeze.


Split view

Excel

Excel also includes a split view, which creates up to 4 semi-independently scrollable areas on the sheet. First select the cell where you want the split, then choose View > Window > Split. You can drag the position of the split once it’s there. Use the same button to turn off the split.

Google Sheets

There's no equivalent view in Google Sheets, but it is possible to open two separate instances of the same file in two separate browser windows/tabs, allowing you to view and work with two different views of the same document.


Finding the edges of a list

Some keyboard short-cuts are useful for finding the extreme right/bottom of a long or wide list:

Ctrl + ↓Jump to the last occupied cell in a column
Ctrl + →Jump to the last occupied cell in a row
Ctrl + EndJump to the last occupied cell in a range (Excel only)

Using up, left and Home keys with Ctrl also works as you would expect.

Sorting

Sorting a list will re-arrange the rows in a specific order — for example, you could sort by price to find the most expensive item in a list.

Broadly speaking, numbers can be sorted in ascending and descending order, and text can be sorted alphabetically (A-Z and Z-A). You can also sort (in a very basic way) by colour. Since dates & times are essentially numbers, they can be sorted in ascending and descending order too.

Excel

Excel has some additional sorting capabilities: it can also sort horizontally as well as vertically (reordering columns rather than rows). It can order items by days of the week and months of the year. You can even create your own custom lists by which to sort.


Tip

One workaround for sorting by weekday or month in Google Sheets is to enter the values as dates and then format accordingly.


Sorting is a fundamentally 'destructive' act: it restructures your dataset in a new order, and depending on how your list has been built, it may be difficult to get it back to the order you started with. There are, however, ways to sort in a non-destructive way. And we'll look at those too.

ExcelStandard sorting in Excel

Excel is able to detect the limits of a 'table' of data based on the list structure conventions outlined above. Sorting controls will act on the block of contingent cells around the current active cell.

In this example, there are two detectable tables of data: A1:B4, and D3:E6.

fxPolly
ABCDEF
1 ForenameSurname
2 SusanForeman
3 BarbaraWrightForenameSurname
4 IanChestertonBenJackson
5 Polly
6 JamieMcCrimmon
7

With D5 as the currently selected cell, any sorting we do will only act upon the table D3:E6. A1:B4 would remain in the order it is now. Likewise, if our active cell were anywhere within A1:B4 it would only affect A1:B4 and would not act upon D3:E6.

This table detection is mostly down to the empty blocks of space about the place that cause 'islands' of contingent data. It's worth noting that while cell E5 is empty, it is still identified as being part of a table of data because the spreadsheet assumes a rectangular structure.

Because the headers are in bold, they are detected as headers by Excel and will not be sorted into the data — clever innit?


Sort & Filter controls

Controls for sorting can be found at Home > Editing  Sort & Filter or at Data > Sort & Filter:

Icons for quick sort and dialogue-based sort

Quick sort

The quick sort icons let you sort your data based on the values within a single column.

  1. Select any one cell in the column by which you wish to sort (don't select multiple cells or a whole column unless you only want to sort that selection and nothing else around it!)
  2. From the Sort & Filter menu, select the direction for the quick sort: smallest to largest (A-Z) or largest to smallest (Z-A).

The whole list will sort according to the order specified for that column. So for an alphabetical sort in our example spreadsheet above, D3:E6 will reorder according to the contents in column D to give:

ForenameSurname
BenJackson
JamieMcCrimmon
Polly

Tip

If your data contains entirely blank rows/columns, this method won't work properly, and you will probably end up corrupting your data by 'shuffling' it.


If you wanted to sort a Forename / Surname list alphabetically by name, you'd probably want to first sort by forename, and then resort by surname. That way, the surnames would be alphabetical (from the latest sort), and, where there were multiple cases of the same surname, the forenames would preserve their alphabetisation from the previous sort. However, there's easier ways to go about complex sorts like this...


Advanced sorting

A Custom Sort allows you to:

  • fix incorrect detection of header rows
  • sort in non-standard orders (e.g. by weekday)
  • sort by multiple fields at once.

Here's how to go about doing one:

  1. Select any one cell in the data you wish to sort
  2. Choose Data > Sort & Filter > Sort or Home > Editing > Sort & Filter > Custom Sort... — this opens the "Sort" dialogue
  3. Configure the sort as necessary (the "Sort On" dropdowns give further sorting options, e.g. by colour) and then select OK.
Options in the Sort dialogue: add or remove sort levels, toggle header row, choose sort order; further options include case sensitivity and horizontal sorting

Google SheetsStandard sorting in Google Sheets

When working collaboratively, the disadvantage of the ‘standard’ sort/filter tools is that the data is changed for all users. Google Sheets therefore has features designed specifically with collaboration in mind.

There are two approaches, both using the same sort and filter tools:

  • Operating directly on the data set – every collaborator sees the result of this
  • Operating indirectly using a filter view – this leaves the underlying data untouched and is the best option when working collaboratively.

We'll look at filter views in a moment, so for now we'll consider a conventional, 'destructive' sort.


Single field sorting

There are sort options in the Data menu, but they're rather cumbersome. If your list has a header row, either ‘freeze’ the header row (View > Freeze…) or, better still, use the following method for both sorting and filtering:

  1. Select any one cell in the data range you wish to sort.
  2. Enable the filter controls (even if we're only going to be sorting) by choosing Data > Create a filter (also available as a button on the toolbar).Filter icon
  3. In the header of the column on which you wish to sort, select the filter control dropdown toggle, and choose Sort A→Z or Sort Z→A . You can also sort by colour.

Multiple field sorting

As with Excel, you can do a degree of multi-field sorting using the single field method, by sorting by your 'secondary' field and then by your 'primary' field. But Google Sheets also has some more advanced options.

To access these properly you must first select the whole data range you're wanting to sort — otherwise you risk corrupting your data by shuffling it. The quickest way to do this is to enable the filters, as per the single field approach we just looked at. This automatically selects the whole data range. Alternatively you could select the range using keyboard methods (Ctrl-A, for instance).

Once you've got your data selected:

  1. Go to Data > Sort range — this will open the sort dialogue
  2. Configure as appropriate and then choose OK.
Options in the sort dialogue: include header row, select sort column, select sort order, and add further columns as required

Tip

Unlike Excel, Google Sheets can't sort horizontally. But you can use the TRANSPOSE function to turn your dataset 90°.


Sorting with the SORT function

An effective, non-destructive way to work with a dataset is to generate a sub-dataset on another worksheet using data functions. As the output is generated by functions, it will automatically reflect any changes made to the underlying data.

One-such data function is SORT, variations of which exist both within Excel (currently only the Microsoft 365 version) and Google Sheets. Annoyingly they're slightly different in each program:

Excel

=SORT(data-array,[index],[order],[sort-direction])

=SORTBY(data-array,sort-array,order,[sort-array2],[order]...)

Google Sheets

=SORT(data-array,[index],[order],[index2],[order]...)


In the above formulae:

  • "data-array" is your dataset (albeit minus any header row, since SORT can't identify header rows)
  • "index" is the position of the sorting column (or potentially row in Excel) as a number, where 1 is the first column of your table, 2 is the second, etc.
  • "order" is the order of sort: ascending or descending (represented as 1 or -1 in Excel; TRUE or FALSE in Google Sheets)
  • "sort-direction" is the direction in which your data is laid out (only an option in Excel): TRUE to sort horizontally by column, FALSE to sort vertically by row (the conventional direction to sort)
  • "sort-array" is the column or row you're sorting by expressed as a range rather than a number, just to keep us on our toes!

Excel's SORTBY and Google's SORT both allow for multiple sort fields — you just keep adding arguments to the function until you're all sorted out.

Let's see an example in action:


fx
ABCDE
1 FruitNumbersFruitNumbers
2 Orange24Apple17
3 Banana11Banana11
4 Plum9Orange24
5 Apple17Plum9

Use the buttons below to show an example formula in the formula bar:

SORT
ExcelExcel 365
SORTBY
ExcelExcel 365
SORT
Google SheetsGoogle Sheets

Filtering

Filtering a list will make it display only those rows containing values which match a specified criteria, temporarily hiding all the other rows — for example, you might filter to show just those students in Year 1.

ExcelStandard filtering in Excel

Data lists are filtered by enabling a series of drop-down controls in the header row.

  1. Select any one cell in the range to be filtered (selecting more will just filter those selected cells);
  2. Choose Data > Sort & Filter > Filter or Home > Editing > Sort & Filter >Filter — this filter button will remain highlighted, and drop-down controls will be applied to the column header row;
    Icons for filtering, clearing filters, and reapplying filters
  3. Use the drop-down controls to configure the filter for one or more columns;
  4. Filtered columns show a filter icon on the drop-down; filtered row numbers are coloured, and the application status bar will display the number or records being shown.

The filter options available will depend on the type of data (text or number):


Text filters

You can select the values you want from a checkbox list or choose from the specialised text filter options

Number filters


You can select the values you want from a checkbox list or choose from the specialised number filter options to configure value ranges


Tip

If you enter new records immediately below the current data, the filter range will be automatically expanded. You can then choose the Reapply option from the Sort & Filter menu to re-filter the changed data.


Clearing filters

To clear filters:

  • Clear a single filter from the drop-down control using Clear Filter From...
  • Clear all filters using the Clear button on the Sort & Filter menu group
  • Clear and remove filters with the Filter button on the Sort & Filter menu group.

Google SheetsStandard filtering in Google Sheets

Google Sheets are collaborative documents. While filtering a list isn't 'destructive' in the same way that a sort is, a filter will still change the appearance of a list, both for you and for anybody else who happens to be in the Sheet at the same time. That being the case, Google Sheets has features designed specifically with collaboration in mind.

There are therefore two approaches, both using the same sort and filter tools:

  • Operating directly on the data set – every collaborator sees the result of this
  • Operating indirectly using a filter view – this leaves the underlying data untouched and is the best option when working collaboratively.

We'll look at filter views in a moment (no, really we will), but for now we'll consider a conventional filter:

  1. Select any one cell in the data range you wish to sort.
  2. Enable the filter controls by choosing Data > Create a filter (also available as a button on the toolbar).Filter icon — this will add the filter toggles to your header row
  3. To apply a filter, choose one or more values from the toggle drop-down list, or choose an option from Filter by condition…
Filter by condition includes criteria for text, date, and numbers

Google SheetsFilter views

When working collaboratively, the disadvantage of the ‘standard’ sort/filter tools is that the data is changed for all users. Google Sheets' filter views allow you to sort and filter a dataset in a way which doesn't disrupt the underlying data. The filter view effectively sits on top of the source data so does not affect the view of that data for other users, who can create their own filter views too!

Filter views can be named and saved for future use by you and other collaborators. They even have their own unique web address.

Furthermore, collaborators can use a filter view to interrogate data without the need for "Editor"-level access: you can give somebody "Viewer" access to a spreadsheet and still have them able to sort and filter that data thanks to filter views.

To create a new filter view:

  1. Select any one cell in the data range and choose Data > Filter views > Create new filter view — everything around your spreadsheet will go an exciting dark grey to alert you to the fact that you've entered a filter view
  2. Configure any sorting and filtering as required using the filter dropdown toggles
  3. For future use, give your filter view a suitable name, and when you’ve finished, close it
  4. Rename your filter view in the box top left; options and close are top-right
  5. To re-visit a filter view, select Data > Filter views... and choose it from the list:
  6. Saved filters appear in the Filter views menu

Filtering with the FILTER function

An effective, non-destructive way to work with a dataset is to generate a sub-dataset on another worksheet using data functions. As the output is generated by functions, it will automatically reflect any changes made to the underlying data.

One-such data function is FILTER, variations of which exist both within Excel (currently only the Microsoft 365 version) and Google Sheets. The formulae are slightly different in each program (though can to some extent be used interchangeably):

Excel

=FILTER(data-array,condition,[if-empty])

Google Sheets

=FILTER(data-array,condition,[condition2]...)


In the above formulae:

  • "data-array" is your dataset (albeit minus any header row, since FILTER can't identify header rows)
  • "condition" is the filter you're applying, in the form of a logical test against a range within your dataset
  • "if-empty" is an optional value for Excel to return if your filter results in an empty array (the default is to give a #CALC! error) — an empty text string ("") is as good an option as any.

Despite appearances, both formulae can handle more than one condition. Excel uses an * to serve as a boolean 'AND' and a + to serve as an 'OR', and these can be placed between bracket-wrapped clauses. Google Sheets can also follow this syntax.

Maybe we should see an example:

fx
ABCDE
1 FruitNumbersFruitNumbers
2 Orange24Banana11
3 Banana11Apple17
4 Plum9
5 Apple17

Use the buttons below to show an example formula in the formula bar:

FILTER
ExcelExcel 365
Google SheetsGoogle Sheets
FILTER
Google SheetsGoogle Sheets
(multiple arguments)

The UNIQUE function

We've already seen how data functions like SORT and FILTER allow you to work non-destructively with datasets by generating a sub-dataset in a second location. Another such function is UNIQUE:

=UNIQUE(range)

When a data set includes repeated data, UNIQUE will display just one instance of each value (junking the repeats). If you select a two-dimensional array (columns and rows) it will effectively filter out duplicate rows.

This function works in both Google Sheets and Excel (currently only the Microsoft 365 version). Excel also has some extra optional arguments for filtering horizontally and for junking all duplicates (including the first instance).


Tip

You can find unique values in earlier versions of Excel by using Data > Data Tools > Remove Duplicates.


Slicers

Slicers are graphical filter controls which you can add to your spreadsheet. Their appearance can even be customised for extra prettiness! They're of limited use for most working spreadsheets, but can be handy for guiding interaction if you're sharing a dataset with a third party.

Excel

In Excel, you can only add a slicer to a special formalised dataset such as a Pivot Table or an Excel Table. Excel Tables work differently to standard ranges, but you can always turn a Table back into a range if you don't like it!

  1. Select the list of data you're wanting to slice, and convert it into an Excel Table by going to Insert > Tables > Table — Your table will go all stripy and colourful;
  2. If you select within the Table, you'll have access to the Table Design tab. You can then select Table Design > Tools > Insert Slicer — an "Insert Slicers" dialog box will open;
  3. Check the box(es) for the column(s) you want to filter by, then choose OK — the slicer box(es) will be added to your sheet;
  4. You can make modifications to your slicer controls from the Slicer tab that is available whenever a slicer is selected.
Google Sheets

Google Sheets slicers will work on any range of data you choose. Like Filter Views, they can be operated by any viewer of your spreadsheet, regardless of whether they have edit access — handy for letting people play with your data in a guided way without the chance of them breaking anything!

  1. Select the range you want to slice, then go to Data > Slicer — the "Slicer" side-panel will open;
  2. In the side-panel, select the column you want to filter by — a slicer will be added to your spreadsheet;
  3. You can make changes to your slicer from the side-panel (which you can reopen from the three dots menu (⋮) on the slicer;
  4. Repeat the process to add further slicers.

Take a look at this simple slicer example to see it in action.

Subtotals (Excel)

Excel

Subtotals (not to be confused with the SUBTOTAL function) are a feature available in Excel, but not Google Sheets as they act directly on the source data and so are not really appropriate in a collaborative context.

Subtotals are used when a list includes values which fall into categories, and subtotalling of those categories is required. The name is slightly misleading since the feature can perform more calculations than just a sum total.


Creating subtotals

  1. First ensure there are no filters applied to the list;
  2. Sort the data so that records are grouped in the categories for which you need a subtotal;
  3. Ensure the active cell is within the list;
  4. Select Data > Outline > Subtotal;
  5. In the dialogue select:
    • The field by which you're grouping (the one you sorted by)
    • The function required for your subtotal
    • The field(s) requiring the subtotal
  6. Choose OK to insert the subtotals.
Grouping controls appear as a tree structure with plus/minus toggles

Extra rows are inserted containing the subtotals and a grand total.

Additional "grouping" controls appear on the left to collapse and expand sections of the worksheet, showing or hiding the data.

Tip

Group controls like these can also be added manually (from the same Outline menu in Excel, or from Data > Group in Google Sheets) — useful for when you want to tidy away information that you might occasionally want to see again.


Editing or removing subtotals

One advantage of using the subtotals feature (as opposed to manually entering subtotals) is that they are very easy to change or remove:

  1. Ensure the list is fully expanded, and that the active cell is within the list region;
  2. . Select Data > Outline > Subtotal to open the Subtotal dialogue:
    • For editing: change the dialogue settings as appropriate, and ensure Replace current subtotals is ticked (otherwise your changes will be added as a new subtotal). Click OK to apply these new options.
    • To remove subtotals: choose Remove All.
Exercises
Google Sheets

Google Sheets versions of the files are listed below. In each case you will be prompted to make your own copy of the spreadsheet files, for which you'll need to be signed into a Google account.




Excel

You can download the above Google Sheets for use in Excel by going to File > Download

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.