1. Select the cell or range to be named.
2. After selecting the range, enter its name into the Name Box to the left of the formula bar.
3. After typing in the name you must press Enter to confirm it.

If you are working with listed values and the number of rows in use may change, select entire columns rather than just the rows currently in use.

You can also create named ranges from Formulas > Defined Names

You can also create named ranges from the Named ranges side-panel at Data > Named ranges (or by right-clicking the selection and choosing Define the named range...). Enter a name for your range, check the range is correct, and choose Done.

Once created, the names can be used anywhere in a formula or function where a cell reference or range would normally be used.

Here's an example using named columns and a named cell:

The following named ranges have been created (click on them to highlight them on the spreadsheet):

Let's start with a simple calculation:

In cell E4 we need to calculate the value added tax (VAT) payable for two boxes of A4 paper. Using cell references our formula would be:

But we've given cell B1 the name VATRate. Because this named range refers to one specific cell, it will effectively work as an absolute reference, so now we can use this named range in our formula instead:

Be aware that names for named ranges are case sensitive: we could have a separate cell called VATrate, and another called vatRate, etc... although that way confusion lies!

We can go even further than this, though. Because we've named column D Cost, we can use that in our formula too. The spreadsheet is clever enough to work out, from the context of the cell, that any reference to Cost in cell E4 needs to refer to the corresponding item in the range Cost: namely the item from row 4. The latest versions of Excel require an extra nudge to tell it to do this, in the form of an 'at' sign (@) before the range name, because the latest versions of Excel can do special things with named ranges that are whole columns (if you get a #SPILL! error in Excel, it's probably because you're missing an "@"; if you're using "@"s but get a "That function isn't valid" error, it's because you're on a pre-@ version of Excel so don't need the "@"s).

The formulae required, then, are:

=@Cost*VATRate (Excel for Microsoft 365)

=Cost*VATRate (Excel 2019 and earlier)

=Cost*VATRate

Excel for
Microsoft 365

Excel (older versions)
& Google Sheets

Entering names in a formula

When writing a formula, simply type in the name where you would normally enter the cell or range reference. When you start typing the name, any matching named ranges will appear among the list of functions — you can double-click on a named range in this list to add it to your formula without having to type the whole thing.

In Excel you can also enter a named range by:

• Selecting the name from the Name Box dropdown
• (if it refers to a single cell) clicking on the cell in question (the name will be entered, not the cell reference)
• (if it refers to a column / row) clicking on the column letter / row number

In Google Sheets, clicking on a cell will always give its reference rather than its name. Even clicking on the ranges in the "Named ranges" side-menu will use the underlying reference in the formula.

To list all named cells/ranges in the current spreadsheet and to edit/delete them:

1. Select Formulas > Defined Name > Name Manager
    
2. Choose the range and hit Edit or Delete as appropriate.

1. Go to Data > Named ranges to open the 'Named ranges' side-panel
    
2. Locate the named range, hover over it, and hit the Edit pencil
    
3. You can then edit the range, or Delete it with the bin icon.

Excel has an additional way to enter functions: the Insert Function dialogue. It's another way to see all the functions by category, to search the functions list, and to get further help.

To use the "Insert Function" dialogue:

1. Select the cell in which you wish to enter the function (but don’t start typing anything);
2. Select the Insert Function control (the fx to the left of the formula bar) - this will launch the dialogue;
3. Locate the function you want to use by either, searching, choosing a category, or selecting from the "Most Recently Used" list (if you’ve used it recently);
4. Once a function has been selected, you can find out more about it by following the "Help on this function" link;
5. To use the function, choose OK – this opens the Function Arguments dialogue.

The Function Arguments dialogue can be useful when you're using a function for the first time. Each bit of information required by a function (its 'arguments') is plotted out in a series of boxes, with contextual help and a summary of how each step evaluates.

The arguments help pop-up appears in Google Sheets when you open the brackets of a function to start adding its 'arguments':

The amount of help shown can be expanded and contracted according to need, using the shrink/expand chevron toggle. Sometimes the panel can get in the way, in which case you can close it with the cross icon. If closed, the panel can be reactivated by clicking the question mark icon (?) to the left of the cell / formula bar (or by pressing F1).

The IF function allows you to compare values and give a different response depending on the outcome. It needs three parameters (or 'arguments'):

The 'Result' portions of the above can be:

• a value (number or "text") that you wish to be placed in the spreadsheet as the result of the function
• a reference to a cell containing a value
• further formula to perform a calculation.

Let's look at an example:

To indicate whether each student had passed, cells in column E contain an IF function to test if the mark (in column D) has reached the pass mark (cell B1, which we've given the name passMark). There are a few options we could go with in cell E4. Here's one:

...and here's another:

This example uses text as its output, but we could equally incorporate calculations, for instance applying a discount. Here's an example taking that approach:

Nesting IFs

Suppose there are 3 possible outcomes to an exam:

Since one IF function only has two outcomes, it cannot give all three possibilities. However, combining two IF functions, one inside another, lets you test for all three results.

The first test can be done with the following formula:

This will identify marks that are a fail, but if the result is false, we now need to test between a pass and a distinction. To do this we insert a second IF:

Note that the second IF is not preceded by an equals sign (=) — that's only needed at the very start of your formula.

While this approach of nesting multiple IF functions could be extended further, it gets messy and unwieldy after a few possible outcomes. Using range lookups is simpler in these cases. We'll take a look at those later...

Nesting can be applied with any functions and is a very powerful technique; however the formulae involved can easily become confusing. Always test your formula carefully to make sure you get the expected results.

All of the basic functions we looked at earlier (the ones on the Σ "Functions" menu) have variants that incorporate conditional tests. With these, cell values can be summed, averaged, counted, etc., only if they meet certain defined criteria.

The conditions don't have to apply merely to the cells being summed, averaged, counted, etc. — the tests can be applied to associated data in a table, e.g. data on the same row (or on the same column if working horizontally rather than vertically).

These functions typically require the following arguments:

• A range of cells to test against a criterion
• A value or expression (the criterion) to test against
• Optionally, a range of corresponding cells to SUM, AVERAGE, etc. (if you want to SUM / AVERAGE a different range to that being tested)

There are also multiple-criteria versions of these functions, that let you apply tests to more than one column in a table of data. Here's a list of all the variations:

Perhaps we ought to consider some example data:

While the above example uses a standardised tabular layout, the basic versions of these functions can work with ranges of any shape or size and needn't be limited to one dimension. However, when more than one range is being used (as with the -IFS functions), all ranges used must be of the same size.

Criteria

While the criteria in the above example were all 'hard-coded' into the formula, they can, as with most formulae, be provided from cell references. But there are certain foibles to be aware of, and tricks it's useful to know about:

Adding and subtracting

Since dates are represented as whole numbers, adding or subtracting a whole number to a date will give a different date:

Difference between two dates

Since dates are represented as whole numbers, subtracting one date from another will give the number of days between those dates:

By default, times are treated as a time of day, so 12:00 means noon. But time can also be used as a duration, and can be used to calculate new times, even on a new day:

In many cases, there's no need for us to distinguish between time and duration. However, unexpected results may start happening if we try to total durations exceeding 24 hours!

In such cases, the cell(s) involved must be formatted as duration:

1. Select the cells you need to format
2. Right-click and select Format cells..., or go to Home > Number > Number format (dialogue toggle)
3. Select "Custom" from the Category list
4. Locate and select the format [h]:mm:ss and hit OK

The [ ]s around the "h" are what makes the spreadsheet know it's dealing with a duration rather than a time.

1. Select the cells you need to format
2. Go to Format > Number > Duration

Alternatively, you could set a custom format (Format > Number > Custom number format).

Let's take a look at some specialist functions for working with dates and times:

TODAY() and NOW()

Spreadsheets include some functions that are able to refer to the computer’s internal calendar and clock. These functions are unusual in that they require no arguments between their brackets:

Be aware that since these values change with the day and time, they can't be used as a date/timestamp.

These functions can be used in calculations, for example to find out how many days there are until a deadline is due:

Constructing and deconstructing dates and times

Here are a few functions for building dates and times or getting components out of them:

Date and time calculation functions

While it's relatively easy to do maths with dates at a day level, sometimes we need to get a bit cleverer:

The standard SUM, AVERAGE and COUNT functions have one particular weakness – they do not take notice of any filters applied to data. The SUBTOTAL function does, however, and is in fact used automatically in the Excel subtotal feature we'll be looking at later.

The name is misleading, as it does a lot more than simply total values. The syntax is:

The AVERAGE function uses the most common form of average: the mean. But the mean is just one form of average. The MEDIAN and MODE functions can be used to find other average measures:

ABS returns the absolute value of a number, removing any negative sign from it (e.g. -50 becomes 50); positive numbers are unchanged.

This can be useful for calculating the difference between two values, when the sign is not important. For example, if an essay has a word limit of 5,000 it makes it easier to calculate if the wordcount is within an acceptable margin:

Column C contains both positive and negative values, depending on whether the word count is over or under. Column D displays the absolute value – so only the difference from 5000 is stored, making it easier to work out if the word count is within the acceptable margin of 250. In row 6 ABS is acting upon the value in C, but in row 7 it has been reformulated to incorporate the calculation from C without needing to reference it.

Values are rounded for display, based on the chosen formatting, but the most precise stored value is used for any further calculations. There may, however, be times when you want to work with a rounded value, so several rounding functions are provided. Here's a few, where "value" would generally be a cell reference:

INT and MOD have all sorts of surprising uses. They're particularly handy for converting between non-decimal units, e.g. converting minutes into hours and minutes:

These two random number functions recalculate every time the spreadsheet changes. The ‘randomness’ of the values isn't quite good enough for some specialist uses, but it's good enough to build a set of dice!

Several functions allow text manipulation, which may be needed if data is not consistent, or is from another source. In the following examples, "text" will usually be a cell reference:

Older versions of Excel don't have a splitting function, but there are other ways to split data in Excel, including the following method:

1. Select the cell(s) containing the data to be split
2. Select Data > Data Tools > Text to Columns
3. Follow the steps of the dialogue to tell Excel about your data
4. Choose Finished when you’ve obtained the desired result in the preview

Be aware that this method replaces the existing data with the first column of split data. However, we'll see some other methods for splitting later.

Joining text

Text strings can be joined together using the ampersand character (&) — we used this method with the conditional functions earlier. Here's another example, where two cells are joined together along with a space character (" "):

As well as Google Sheets' JOIN function, both Google Sheets and Excel have further functions for joining texts together, including the TEXTJOIN function from the table above. Both programs also have functions called CONCAT and CONCATENATE, though, bizarrely, they work in opposite ways in each:

CONCAT will join together all cells in the range(s) specified. CONCATENATE is a legacy function that could only join individually specified cells.

CONCAT will only join two values together. CONCATENATE will join together all cells in the range(s) specified.

I know..! Confusing, isn't it?!