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:

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...

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. Sometimes you can use Google Apps Script coding to make Sheets behave a bit more like a database.

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. 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
4. The Input Message tab lets you prepare a prompt to appear when the cell is chosen. Over-use of this can be annoying!
5. 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 want people to pick a value from a dropdown list you can choose the List option for the allowed data. For this option you'll need to provide a Source which will be used to populate the values in the dropdown control. You can enter a comma-separated list of items but it's better to provide an existing list of values from elsewhere in the spreadsheet (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.

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 side panel:

3. Configure the Criteria as necessary
4. In the Advanced options choose whether to Show a warning on invalid data entry or Reject the input outright
5. If Show help text for selected cell 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:

• Drop-down – the list is entered directly into the side panel (avoid using this except for simple, unchangeable lists like Yes and No options)
• Drop-down (from a range) – you will need to define a cell range or named range (usually on a another worksheet) but you can still apply colours in the side panel when using chips.

These options can be customised extensively:

• You can add colours to your menu options. These are essentially a form of conditional formatting but controlled from the validation options
• In the Advanced options section you can customise the type of dropdown displayed — lozenge-shaped 'chips' or more traditional arrow toggles — or choose Plain text for entrants to type against a dynamic list
• If you choose the chips option then you can also Allow multiple selections in a single cell. For analysis purposes a cell with multiple selections will behave as a comma separated text string: the multiple selection Cat Dog will have the underlying value of Cat, Dog.

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.

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:

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

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:

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.

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 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.

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:

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

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.

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:

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:

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.

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).
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 > Advanced range sorting options — this will open the sort dialogue
2. Configure as appropriate and then choose OK.

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

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 (2021 or later, and Microsoft 365) and Google Sheets. Annoyingly they're slightly different in each program:

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

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

=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:

SORT
Excel

SORTBY
Excel

SORT
Google Sheets

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;
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

Number filters

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 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). — 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…

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. These filter views 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 > Create filter view — everything around your spreadsheet will go pale green (it used to go dark grey to make it stand out from a standard filter but you can still tell the difference by the presence of the extra control panel above the column headers)
2. Configure any sorting and filtering as required using the filter dropdown toggles
3. For future use, choose Save view from the top right and give your filter view a suitable name
4. You can close a filter view with the ✕ in the top right

5. To re-visit a filter view, select Data > Change view... and choose it from the list
6. Check the Range of the filter view — if new data has been added since the filter view was created, it may not be being captured, so you might need to expand the range to include it.

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 (2021 or later, and Microsoft 365) and Google Sheets. The formulae are slightly different in each program (though can to some extent be used interchangeably):

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

=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:

FILTER
Excel
Google Sheets

FILTER
Google Sheets
(multiple arguments)

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:

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 (2021 or later and Microsoft 365). Excel also has some extra optional arguments for filtering horizontally and for junking all duplicates (including the first instance).

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

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.

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 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 > Add a 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 'kebab' 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.

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.

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.

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.

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.