As Ben Ducker, Chartered Accountant, Excel specialist and Founder of Modelling Expert explains, there is one approach that should be common knowledge when using Excel, but isn’t.

“Plan first, then build. You don’t necessarily start with the output, but you do need to think about the outputs at the beginning. People should give some thought about how they structure the data and the solution,” he says. “What are you trying to achieve? What do you need to show or identify? These questions will inform the design architecture.”

In a business context, there might be discussions around the necessity of department reporting. If it’s not necessary, financial data can be aggregated into a single revenue line. However, if reporting departmental EBITA (Earnings Before Interest, Taxes, Depreciation and Amortisation) is required, the model must maintain separate revenues and costs per department throughout.

“This then changes the entire structure. It’s not something you can easily retrofit,” says Ducker.

Here are some other, more specific tried-and tested Excel tips and hacks for smarter working.

Named Ranges

Improve formula clarity by replacing cell references with meaningful names. For example, the range =E5*C2 can be changed to =LoanBalance*InterestRate so it’s immediately clear what the range refers to. 

Named ranges can also be ‘scoped’ to the entire workbook or specific sheets so they can be used for frequently used values such as interest rates, VAT rates and so on. 

Method 1 - Direct naming via name box

1. Click on the cell you want to name.
2. Click the Name Box (located to the left of the formula bar)
3. Type the name you want to assign the range (eg, "InterestRate")
4. Press Enter.

Method 2 - Create from Selection (when labels are already in place)

Select two adjacent cells. The first column should contain the label/name you want to use and the second column should contain the cell you want to name.

1. Go to Sheet > Named Ranges and Expressions > Define Name (or use the Name Manager).
2. Click ‘Create from Selection’
3. Tick the box for ‘Left Column’ (to use the labels in the left column as names)
4. Click OK.

To scope the named range, use the Name Manager and select to scope the name to the workbook or sheet.

Use Excel Tables Objects to store and manipulate data

Converting data ranges into table objects can make data easier to work with. Benefits include easier-to-read formula, automatic formula fill-down and built-in styling options. When new data is added, tables automatically expand and formulas automatically update.

1. Click on any cell within your data range. You don't need to select the entire range.
2. Press Ctrl+T (or go to Insert > Table).
3. A ‘Create Table’ dialog box will appear.
4. Tick the box ‘My table has headers’ if your data includes a header row.
5. Click OK.
6. A Table Design menu appears in the ribbon.
7. Name the table and/or change the style with predefined formatting options.
8. When you add or change a formula column, the formulae will automatically use the column names you reference and will automatically ‘fill down’ the length of the table.

XLOOKUP rather than INDEX/MATCH

Both Index/Match and XLOOKUP are used for the same purpose – to look up a value and return a corresponding result from another column - but they work differently. INDEX/MATCH uses two separate functions ‘nested’ together. The order of the inputs isn’t necessarily intuitive, and it doesn’t have a built-in way to identify missing values. (An ‘IFNA()’ function would be required).

XLOOKUP uses one straightforward function and is a simpler alternative with easier-to-understand code. The order of inputs may be more intuitive and include a built-in functionality to return a default value if a lookup is ‘not found’.

Using X-LOOKUP

1. Click the cell where you want the result to appear.
2. Type the formula: =XLOOKUP(
3. Select the lookup value you are searching for.
4. Select the lookup range.
5. Select the return array. (must be same length as lookup range)
6. Enter a default value (or leave empty if you want #N/A to be returned)
7. Enter ‘0’ as the match model for an ‘exact match’ (this specifies that the lookup value must appear exactly in the lookup array)
8. Close the formula: eg, =XLOOKUP(D4, D1:D12, E1:E12). The basic structure is: =XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, match_mode)
9. Press Enter.

Tracing precedents and dependents 

Trace tools can help users visualise data flow across the workbook. Precedents are cells which feed in, while dependents are cells which depend on a certain cell. Checking for dependents in this way is a good way to see if a cell is used in later calculations. But there’s a caveat: there are circumstances where the dependent arrows do not appear. “This can occur when a cell is referenced by an INDIRECT or OFFSET function, meaning it has dependents but the arrows will not report them,” says Ben.

Tracing Precedents and Dependants

1. Click on the cell containing the formula you want to audit.
2. Go to Formulas > Formula Auditing > Trace Precedents or Trace Dependents.
3. For Precedents: Excel draws blue arrows showing which cells are referenced in your formula. For Dependents: Excel draws arrows showing all cells that use this cell in their formulas.
4. Double-click on an arrow to jump to that source cell.
5. Click ‘Trace Precedents’ or ‘Trace Dependants’ again to go back one level further.
6. Click "Remove Arrows" to clear the trace lines.