Let's delve into some common practices for constructing efficient formulas in Excel with this "How to Show Formulas in Excel" guide.
Excel, often regarded as a sophisticated calculator, offers robust functionalities for creating financial calculators, automating processes, and solving complex problems. It's a widely used tool among business professionals. However, despite its popularity, not everyone can effectively write formulas in Excel.
You might question the significance of this skill. While Excel can process your formulas and deliver the desired results, larger models may encounter calculation delays. Learning to construct efficient formulas from the start can mitigate these issues as your workbooks grow.
Excel is known for its flexibility in accommodating various elements within formulas, including static values, cell references, nested formulas, named ranges, or entire tables. With numerous approaches to achieving the same outcome, it's vital to adopt best practices.
Revealing Excel Formulas: Understanding the Differences Between Static Values and Cell References Inside Formulas
Let's start with "How to Show Formulas in Excel" guide with one prevalent mistake in Excel function writing is the use of static values within a formula. For instance, when creating a VLOOKUP formula, many users tend to directly input the column index. This practice extends to formulas like SUMIF, AVERAGEIF, and others. While these formulas may yield accurate results initially, complications can arise when attempting to copy them into other cells.
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 showing 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.
Using Named Ranges In Excel Formulas
The ability to assign a name to a range in Excel is a powerful ability and let's have a look at this in our "How to Show Formulas in Excel" guide. 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)
While it's true that anyone can write formulas and build spreadsheets for their daily needs, not every Excel user can develop 'efficient' formulas. Just as training your body at a young age benefits you in sports, cultivating a habit of keeping these tips in mind can assist in creating Excel models that operate more swiftly, ultimately saving you time and frustration. If you liked this "How to Show Formulas in Excel" article, you can also check out our other articles to get more practical and comprehensive information about Excel.
