Ensuring Consistent Excel Formulas

In a previous TTS Resource, we discussed how to evaluate a formula with “Edit” mode, as well as with the F9 key. But what if you have already reviewed your formula for correct syntax and want to ensure consistent Excel formulas for other cells?

This is where CTRL + \ and CTRL + SHIFT + \ come in handy.

Many users in Excel know that to check for consistency across a row of cells (e.g., C5:H5), CTRL + \ is your friend.  This is because many models share a common theme – periods are typically built horizontally.  This naturally leads to the necessity to check if formulas are consistent across all periods.

However, in some instances, you may have built a formula down a column of cells (e.g., C5:C10), which means that you require a vertical consistency check.  In this instance, CTRL + SHIFT + \ is your friend.

Both instances require you to select the cell that you would like to test, highlight the cells that you would like to check for consistency and perform the desired shortcut.  If the formula of the cell in question is consistent with all highlighted cells, you will be greeted with a “Ding” sound and an error alert will pop up with a warning that says “No cells were found.”  This is good.  You can then press the ENTER key or ESCAPE key to acknowledge the prompt and exit the pop-up.

However, if your formula is inconsistent, the inconsistent cells will remain highlighted.  You can then check for the consistency of the new, automatically-selected cell on the remaining highlighted cells.  This process can be repeated until you eventually run out of inconsistencies and ultimately receive the pop-up.

In an ideal scenario where the cell you are testing requires consistency both horizontally and vertically (e.g., C5:H10), you can select cell C5, highlight the entire area of cells, and then perform both shortcuts.  If the formula is consistent both horizontally and vertically, these are the sequence of commands that you can use to perform the check: CTRL + \ >> ENTER >> CTRL + SHIFT + \ >> ENTER.  Of course you can perform CTRL + SHIFT + \ first if you would like, or use ESCAPE instead of ENTER, and you will obtain the same outcome.

Checking for consistent formulas is not only a great way to efficiently audit someone else’s model, but is also very effective when building your own model to ensure that you minimize potential errors.  As always, utilizing this technique from within your expanding Excel skills toolbox will continue to increase your confidence using Excel, as well as increase other’s confidence in your Excel skills.