I get a lot of questions from members and visitors to our site asking for help with formulas. It’s not surprising given they are one of the most useful Excel features.
However, one of the repeating problems I’ve noticed recently are formulas that are not written efficiently.
When I say ‘efficient’ I mean efficient for you to write and maintain as opposed to efficient for Excel to calculate, the latter is a topic for another day.
Here’s an example of an inefficient formula, below in columns A and B we have some employee data, and in columns E and F we’ve set up a summary table which counts the number of employees in each department using a COUNTIF formula (we can see the actual formula from column F in column G):
While this formula is technically correct, after all it returns the correct result, it's inefficient to write.
Let me explain; the ‘criteria’ argument (in the orange boxes in the image above), has been entered as text and this means each formula in column F had to be modified for each department. That's way too much work.
A more efficient way to write the formula is to reference a cell for the criteria argument as you can see in the example in column H below (especially since it’s right there in column E):
Formulas written this way are:
- Quick: with the efficient formula (as shown in column H above) you enter your formula in cell F2 and then copy it down the column and your job is done.
- Easy to Update: if you need to change the Dept. names in column E, your formula will automatically pick up the new name without the need to also edit the formula.
- Intuitive: where there are formulas in contiguous cells experienced Excel users will expect that the formula can be copied and pasted to all adjacent cells. So if someone inherits your file (or helps you with a formula ;-)) and they edit the formula, they will do so in the top left cell in the range of formulas and then copy and paste it to the remaining cells in the table. However, this could result in errors if, for example, they don't notice that your formulae 15 rows down and 3 columns to the right are subtly different because you hard keyed some criteria!
Below is an example where this ability to copy and paste one formula to many cells earns its weight in gold (i.e. the more cells you copy it to the more it weighs :-)); the SUMIFS formula in cell G2 (as seen in the formula bar below) can be written once and copied to the remaining 14 cells in the summary table:
As opposed to this inefficiently written version of the formula in cell G2:
Notice the difference between the efficient formula and the inefficient one. The inefficient formula would require you to enter 15 different formulas, whereas the efficient formula is entered once and then copied and pasted to the remaining cells in the table.
Absolute vs Relative References
The other feature of an efficient formula is leveraging absolute and relative references. Those $ signs you see in the formula are stipulating when a reference is absolute or relative.
When you copy and paste a formula containing row/column references those preceded by a $ sign do not change (i.e. they're absolute), whereas any reference without the $ sign changes relative to the number and direction of cells it has moved.
Notice how some cell references have the column and row references absolute like this: $D$2
Some just have the column reference absolute: $F2
And others just have the row reference absolute: G$1
I won't go into detail on absolute and relative references here; instead you can click here to see some examples.
If you haven’t mastered absolute and relative references yet I recommend you put them at the top of your ‘Excel To-Learn list’.
The above tips will help you leverage Excel and absolute/relative references to do a lot of the work for you, and it's a great start but, those cell references make the formula tricky to read and write.
Let's do better by using Named Ranges instead of cell references.
For example our previous SUMIFS formula can also be written like this (see formula bar below):
Now, isn’t that easier to read? It's also quicker to write since you can type the name of the range into the formula or press F3 to bring up the list of names to choose from.
Named ranges in their simplest form allow us to give a range of cells a name which can then be used in place of the actual cell references.
For example we can give the following ranges used in our SUMIFS formula names:
- $D$2:$D$64 name is ‘Salary’,
- $A$2:$A$64 name is ‘Dept’
- $C$2:$C$64 name is ‘Year’.
So now this formula:
Can be written like this:
If you’ve mastered Named Ranges then you might be interested in Dynamic Named Ranges. These are ranges that expand and contract automatically (dynamically) as your data expands and contracts, or based on criteria you stipulate.
You can create a dynamic range using the OFFSET function or INDEX function however, if the thought of using those is a bit scary then there is a very easy way to create dynamic ranges using Excel Tables.
I’m going to be blunt here; if you aren’t familiar with Excel Tables then you are missing out.
These are one of the most useful features that came out with Excel 2007 and yet they are one of the most underused.
The following features of Excel Tables are going to revolutionise the way you write formulas:
- Structured References: This is the name given to the way you reference cells in an Excel Table. Structured references work in a similar way to Named Ranges however, since they’re part of the Excel Table features you don’t need to set them up manually.
- Dynamic Ranges: The Structured References are dynamic; this means as you add new data to your Table the ranges automatically grow to incorporate that new data.
Structured References are a great alternative if the idea of having to get your head around complicated OFFSET or INDEX formulas to build your dynamic named ranges doesn't appeal.
Working with Structured References
There are various ways to reference the components of the Table, and in the example below you can see these particular references are made up of the table name (Table1), and column label, which we can either type in or choose from a list.
This ability to choose from a list is one of the great features of Excel Tables. As you type in part of the table/column name a list of names you can use becomes available (similar to named ranges), and you simply select the one you want.
With structured references our formula in G2 becomes:
The named ranges have been replaced with the table’s structured references (e.g. Table[Salary] etc.)
There are many more features that come with Excel Tables which I consider a bonus:
- Filter buttons automatically applied
- Banded row formatting
- Flexible Total Row formula using Subtotal
- Automatic Freeze Pane for column labels
- And more. Check out all the features of Excel Tables here.
Column Number and Row Number Arguments
There are a few functions which have arguments for row_num or column_num, for example:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
INDEX(array, row_num, column_num)
And it’s not unusual to have multiple columns of VLOOKUP formulas all returning different ‘col_index_num’s from the same table.
Having to manually update the ‘col_index_num’ argument is not efficient and it breaks the 'write it once and copy & paste' rule of efficient formulas.
Instead we can use the COLUMN or COLUMNS function and a clever combination of absolute and relative references to dynamically return a col_index_num that increases as you copy the formula across to the right.
The COLUMNS function returns the count of the number of columns in the range you give it, likewise the ROWS function returns the count of the number of rows in the range you give it.
For example, the formula in cell C3 below is =COLUMNS($B$1:B1) which =1 i.e. B1:B1 is 1 column wide. And when copied across to columns D and E the range referenced increases by 1 column (i.e. $B$1:C1 and $B$1:D1) and therefore returns the results; 2 and 3 respectively as we can see in the image below:
So, instead of hard coding the col_index_num argument in your VLOOKUP formula you can use the COLUMNS function to return a dynamic result which increases as you copy the formula into columns to the right.
Tip: Since you are only counting columns you don't have to include the row reference in your range, instead you could also write the formula like this:
Which would return 2
Here is a VLOOKUP and COLUMNS example which uses the COLUMNS function to return the col_index_num argument for VLOOKUP.
Note; while reading the VLOOKUP COLUMNS example don’t forget that for row_num arguments (in HLOOKUP or INDEX), you’d use the ROWS function instead of COLUMNS.
The point I’m trying to make here is that your formulas should and can be quick to write, interpret and update.
You should be aiming to write one formula for the top left cell in your table, or first cell in your column of formulas and then copy it to everywhere it needs to go. Any alterations to ranges or criteria etc. should dynamically update based on the destination of where you paste the formula.
Tools we can use to help:
- Absolute and relative references
- Named ranges
- Excel Tables
- COLUMNS/ROWS functions
- Troubleshooting Excel Formulas
The above is a solid start to efficiently writing formulas. If you've got a tip related to this please share it in the comments below for all to benefit.
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.