Excel’s functionality often appears limitless, even for those who have been using the software for many years. Methods like VLOOKUP or nested IF statements can easily become muscle memory, but while they feel familiar, they aren’t always the most efficient way to work. Use the following three tips to save time.

Use R1C1 notation for bulk Find/Replace edits

It is possible to edit multiple cells at once using Find/Replace via the R1CI reference style in place of the default A1.

The A1 reference style uses a unique address for each cell, so even a consistent formula will change with every cell it’s copied to.  For example, it may initially reference column A, then column B as it’s copied across, so Find/Replace can’t be used because the column references change. By switching to the R1C1 reference style, cells contain genuinely consistent references, so the Find/Replace method can be used to edit hundreds of cells simultaneously.

Temporarily implementing R1C1 for Find/Replace

1. File > Options > Formula > enable R1C1 reference style.
2. Perform Find/Replace actions.
3. Once multiple edits have been made, disable R1C1 to return to A1 notation.

Trace multiple precedents/dependents simultaneously

It’s sometimes necessary to check a cell to identify either its ‘precedents’ (the cells which provide data for the current cell) or ‘dependents’ (cells which depend on your current cell). 
By default, Excel will only allow you to trace precedents or dependents one cell at a time, so when needing to audit large numbers of cells, it can be extremely time-consuming. Use these hacks to check the precedents/dependents of many cells in one go.

Trace multiple Dependents

1. Create a temporary input cell (any blank cell).
2. Temporarily link all the cells you want to trace dependents of to that input cell (save work first).
3. Click on the input cell. Then click Formulas tab > select ‘Trace Dependents’ and click the button twice. 
4. The first click traces to the cells you want to trace dependents of, the second click traces all dependents of those cells at once.
5. Click Remove Arrows on the ribbon and restore your previous formulae.

Trace multiple Precedents

1. Create a temporary output cell as the ‘helper’ cell - (any blank cell).
2. Enter a SUM formula which includes all the cells you want to trace precedents of, eg, =SUM(A1:A100). 
3. Click on the ‘helper’ cell > Formulas tab > select ‘Trace Precedents’ and click the button twice.
4. The first click traces to the cells you want to trace precedents of. The second click traces all precedents of those cells at once.
5. Click Remove Arrows on the ribbon and delete the helper SUM cell.

Note: blue arrows mean data and formula indicate where the linked cell is. Red arrows indicate the link is the source of a formula evaluating to an error value.

LET function to name intermediate values inside formulas 

Best practice spreadsheet design calls for short and simple formulae, with calculations laid out in easy-to-follow steps. But sometimes there may be constraints, making complex formula unavoidable. The LET function can be used to assign names/variables within single formula to improve readability and to avoid the need to repeat cell references.

The LET function enables the application of ‘nicknames’ to source cells or intermediate results – allowing calculations to be split into steps within a single formula. Additionally, if a cell reference changes, just the assigned name at the beginning of the formula needs amending: subsequent references throughout the formula are automatically updated. 

In essence, the LET function transforms a collection of non-meaningful cell references into an organised formula that’s easy to read and understand. 

Implementing the LET function

1. Enter =LET( into the empty cell.
2. Type the name you want to assign (eg, Actuals), followed by a comma.
3. Click on the cell it refers to (eg, B4), followed by another comma.
4. Repeat for additional names (eg, Type Budget, comma, click D5, comma).
5. Enter the calculation using your assigned names instead of cell addresses.
6. Close the bracket ) and hit Enter.

The final formula should look like this: 
=LET(Actuals, B4, Budget, D5, (Actuals – Budget) / Budget)

Tweaks can easily be made within this function. If ‘Budget’ moves to E10, update the D5 to E10 at the beginning of the formula. Because the rest of the formula uses the assigned name ‘Budget’, the formula updates automatically. 

But, says Ducker, there is a huge caveat in using the LET function.  While LET makes formulas more readable by naming intermediate values and enabling certain cells to be referenced just once, there are certain scenarios where LET should not be used. 

“It shouldn’t be used as a substitute for a sensible spreadsheet layout,” he explains. “So don't use it instead of named ranges or for splitting your calculations out into bite-sized pieces. If something goes wrong, it’s then harder to debug. However, while LET isn’t intended to replace best practices, there may be circumstances where complicated formulas are unavoidable, and this is a way to make it a bit more digestible.” 

Ultimately, LET is a tool to aid understanding in complex formulas, but not as a way to avoid well-structured spreadsheet.