We probably spend the majority of our Excel lives writing and modifying formulas. They are the heart of Excel, and amazingly powerful!
Formulas can also be frustrating, overwhelming, and difficult to figure out. So, in this post I share five tips & shortcuts to help you read and write formulas faster.
#1 – Enter & Edit Modes with F2
When you have a cell selected, pressing the F2 key puts the cell in Edit mode. If the cell contains a formula, you will see the formula in the cell and be able to edit it. This is the same as double-clicking the cell with the mouse.
But there's more to it…
Sometimes you might want to select a cell/range with arrow keys to change a reference. When you press the arrow keys in Edit mode, the text cursor moves within the formula.
At this point you can press F2 again to put the formula in Enter or Point mode. Now the arrow keys will select cells instead of moving the text cursor.
The mode is displayed in the bottom-left corner of the application window in the Status Bar.
You can press F2 again to toggle back to Edit mode, to move the text cursor. This toggle allows you to modify formulas by just using the keyboard.
The use of F2 will depend on the scenario. Sometimes it's faster to use the mouse. However, Enter & Edit modes make it very easy to select text or ranges with shortcut keys like Ctrl+Shift+End.
If you find yourself typing the $ symbol to create absolute/relative references, then the F4 key will quickly become your new best friend.
After creating a range reference in a formula (with either the mouse or keyboard), pressing the F4 key will add the $ symbols and make the reference absolute.
The F4 key is a toggle that will cycle through all absolute, mixed, and relative reference states.
1st press – Full Absolute Reference: $A$2
2nd press – Absolute Row Reference: A$2
3rd press – Absolute Column Reference: $A2
4th press – Full Relative Reference: A2
You can continue to press F4 to cycle through these states. The cycle will start at the existing state for the reference and change to the next when you press F4.
F4 also works on range references ($A$2:$A$10).
When you first create the reference by selecting it with the mouse or keyboard, press F4 to make apply the change to the full reference.
If you modify an existing range reference then you will need to select the entire reference before pressing F4. Otherwise it will only modify the part of the reference that the text cursor is in.
I share a quick way to select the entire range reference in the next tip
#3 – Select Argument Text with Screentip Hyperlinks
Sometimes it's difficult to find and modify arguments within complex formulas. We can use the function's screentip hyperlinks to help with this.
When you put the text cursor inside a function, a screentip appears below with the function's arguments. Click the hyperlink for any argument to select the argument's text in the formula.
If you are modifying the absolute/relative reference of the formula, then you can press F4 directly after clicking the link.
Bonus tip: If the function screentip is in your way, you can also move it by hovering the mouse over the border until the cursor turns into a cross arrow.
#4 – Evaluate Formula Components with F9
When dealing with long and complex formulas, you might want to see the results of a nested function/formula (a formula within the formula).
Select the text within the formula that you want to evaluate, then press the F9 key. Excel will calculate the selected text and return the result direct to the formula.
This can help you step through and understand how a formula calculates, but there are a few important things to know.
You must select the entire expression or function. If you only select part of a function's text then Excel will show an error message.
Press Escape after evaluating parts of the formula. This is important! Escape will exit out of Edit mode and not commit the changes. If you press Enter then the results of the evaluation will be committed to the formula, and you will lose the parts of the formula you evaluated.
The F9 shortcut works well when you are trying to figure out what a specific component of the formula is calculating. The next tip explains how to step through the entire formula.
#5 – The Evaluate Formula Window
The Evaluate Formula Window is a great tool if you've inherited a workbook that contains complex formulas. It allows you to step through each component of the formula and see how it calculates.
Here's how to use it.
Select the cell that contains the formula you want to evaluate.
Go to the Formulas tab in the Ribbon and press the Evaluate Formula button. (keyboard shortcut: Alt, T, U, F)
The Evaluate Formula window will appear with the formula loaded in the Evaluation box.
The underlined expression within the formula will be evaluated next.
Press the Evaluate button, or the Space Bar, to evaluate the expression and see the result in the formula.
You can continue to press Evaluate or Space Bar to evaluate each step.
You will eventually see the result of the entire formula. You can then press Restart or Space Bar again to start over.
You can close the window at any time during the evaluation process. The window does NOT change the formula at all.
If you find yourself stepping through the formula several times, then you might want to switch back to editing the formula and use the F9 shortcut. I often use both of these techniques when learning or debugging a complex formula.
Function Lock on Laptop Keyboards
At the end of the video I mentioned a tip if the function keys (F1-F12) are not working for you. This is typically due to your keyboard having multi-purpose function keys, which is common on laptops and smaller/newer keyboards.
If that's the case, you will have to press the Fn key in combination with the function key. This can definitely slow you down!
Fortunately, many keyboards have an Fn Lock feature that allows you make the function keys the primary command. You can usually do a quick Google search with the model name of your keyboard/laptop and the phrase “fn lock”, to see if it is available. All manufactures handle this differently.
As a last resort, you can also modify the BIOS to lock the function keys. This is not a permanent change, but harder to toggle on/off quickly.
Often times we receive a data set that does not have a column of sequential numbers or dates. If we sort one or more columns in the range, it can be difficult or impossible to revert back to the original sort order.
One way to solve this problem is to add a column of sequential numbers to the data set. I typically refer to this as an index column.
After sorting the data you can then go back and sort the index column to restore the original sort order.
Setting up the Index Column
It's important to setup the index column BEFORE you apply any sorting to the data.
Here are the steps to create the index column:
Type a 1 in a blank column to the right of the data range/table.
Double-click the fill handle to fill the number down. Checkout this article on the fill handle to learn more.
Select Series from the Auto Fill Options menu to create a sequential list of numbers 1,2,3,…
IMPORTANT: If you are NOT using an Excel Table then you will need to reapply the filters to include this new column. This must be done BEFORE you sort the other columns so that the index column is included in the sort.
If you are using an Excel Table then you do not have to worry about step 4. The new column will automatically be included in the table as long as you create it directly to the right of the last column. See the video above for more details.
My friend Robbie asked me this question about retaining sort order the other day, which sparked the idea for this post, and this index column method is the one I've always used.