The glorified calculator that is Excel can help you build financial calculators, automate your processes, or solve complicated problems. It is safe to say that pretty much all business professionals are familiar with Excel. Although everyone can write formulas, not all can write them well.
You might be asking yourself, why does it even matter? Excel accepts my formulas and giving me the results I want. Well, you are right, but your calculations are probably taking a few seconds to run. You start truly feeling the difference in bigger models. Habits die hard and if you learn to write efficient formulas from get-go, you will appreciate it once your workbooks start getting bigger.
Excel is typically pretty flexible with what it accepts in formulas. You can enter static values, cell references, nest things in other formulas, named ranges, or even entire tables. There’s usually more than one way to reach your target. Let’s take a look at some common practices.
Static Values vs Cell References
One of the most common errors when writing Excel functions is to use static values inside a formula. For example, most people simply prefer entering the column index when creating a VLOOKUP formula. The same goes for SUMIF, AVERAGEIF, or similar formulas. You will get the correct result regardless of how you build these formulas. Formulas return the correct results, however things might get ugly if you try copying that formula into other fields.
Let’s see this with an example. In the table below we have a list of employees and their salaries. You can download the sample workbook here. Let’s assume that our HR department wants to find the average salaries by department. An AVARAGEIF formula can prove useful in this scenario. We’re first going to need to enter the department column J, the enter “R&D” to define the department we’re looking for, and finally the range of values we’re looking into (salary – column G).
This formula will give us what we were looking for, the average salaries of the R&D department. After this point, we could copy and paste this formula for the other departments. One small issue with that is Excel won’t recognize the fact that we want the formula to adapt to the other ‘departments’. Of course, we could copy the formula and edit it for each department. For example, for the ‘administration’ group, the formula needs to be,
=AVERAGEIF($J$2:$J$318,"Administration",$G$2:$G$318)
However, this is a time consuming task and you really wouldn’t want to do this for repetitive formulas with several differences.
A better way to do this is to use relative cell references. Using cell references may sound like more work, but this method is reliable and far more flexible. Going back to our example, the AVERAGEIF formula can be replicated within seconds, when using cell references. You need to write formula only once, and then you can copy it to apply to all sections without making any other changes.
When you copy down to the formula for the other rows, the cell reference section of the formula will be automatically updated for Administration (L3), Human Resources (L4) and the rest of the departments.
Using cell references becomes far more important when you’re dealing with 2-dimensional ranges. Taking our previous example, let’s assume that we need the average salaries by department and state. With static values we could use this formula, =AVERAGEIFS($G$2:$G$318,$J$2:$J$318,"R&D",$D$2:$D$318,"GA")
This formula contains both department and state references as static values. Again, you’re going to need to copy and paste this formula and then update the names in each function. Only this time, we’re going to have 9 * 3 = 27 cells that need editing. Moreover, there are 2 static values in each cell, and that means 27 * 2 = 54 changes. Good luck with that.
Thanks to relative cell references, you can simply copy and paste (or hold the lower right corner of the call and drag) to generate formulas for the other cells. In our sample workbook, we’re using P2 for departments and Q1 for states. Before you start applying the formula to other fields, keep in mind that if you copy the cell with P2 reference and paste it to 1 cell left and 1 cell below, reference will be changed to Q3. Both columns and rows will be updated accordingly.
Here, we need data from the column P (departments) and 1st row (states). When we copy this to the next rows, we don’t want the state reference row to change. Copying from left to right, the department reference columns should remain as is. To do this, we’re going to need to ‘lock’ column and cell references. You may have seen this before as dollar sign “$” used with a cell reference. This means that what comes after that sign will be locked and have an absolute value.
For example:
- $P$2 means both row and column are absolute, so reference is not changed after you copy cell into another cell
- $P2 means only column is absolute and stays still while row can be changed
- P$2 means only row is absolute and cannot be changed.
You can either type “$” with your keyboard, or press F4 key when cursor is on cell reference to change between relative/absolute status. This formula can be copied to the following rows to give us the values we’re looking for,
=AVERAGEIFS($G$2:$G$318,$J$2:$J$318,$P2,$D$2:$D$318,Q$1)
After we copy formula to cell S10 it will become, =AVERAGEIFS($G$2:$G$318,$J$2:$J$318,$P10,$D$2:$D$318,S$1)
$P2 becomes $P10 and Q$1 becomes S$1.
Named Ranges
The ability to assign a name to a range in Excel is a powerful ability. By defining names to a cell or a range of cells, you can create formulas that are easy to read and manage. Names ranges work with cell ranges, functions, constants, and even tables. Let’s name the data from our workbook as follows,
- $G$2:$G$318 : BaseSalary
- $J$2:$J$318 : Department
- $D$2:$D$318 : State
As a result, we can create the same formula as,
=AVERAGEIFS(BaseSalary,Department,$P2,State,V$1)
Now it looks simple and intuitive, right?
Tables
Tables are widely used in many Excel models as they have lots of advantages like growing dynamically as you enter data, automatically format options, connect data, and allow filters. Here, we’re going to use tables for their Structured References feature.
Excel automatically defines names for all columns created in a table. Unlike regular named ranges, these column names are defined with the table itself and you can get a list of these fields when you start typing a formula.
In our example, the table is named “EmployeeTable”. When you enter “[“ and start typing the table name, you will get the list of columns from this particular table, as well as special lists like headers or totals. Combining tables and names ranges, our function becomes,
=AVERAGEIFS(EmployeeTable[Base Salary],EmployeeTable[Department],$L2,EmployeeTable[State],M$1)
Everyone can write formulas and create spreadsheets for daily use. However, this doesn’t mean that every Excel user can create ‘efficient’ formulas. Just like teaching your body young when you’re doing sports, making a habit of keeping these tricks in mind can help you create Excel models that run faster and save you time and frustration.
