10 Tips to write better Excel Formulas

Here are some of my awesome tips to help you work faster and write better Excel Formulas!

 

1 Understand how Cell Modes work

When you start writing the formula, Excel is in Enter Mode so with the arrow keys you are able to select the cells

The problem occurs when you are in the EDIT mode (by pressing F2) the arrow keys stop working. All you have got to do is to Press F2 once again Excel comes back to Enter Mode (arrow keys start working and you’ll be able to select the cells)

Using keyboard (arrow keys) increases your productivity over using a mouse

 

2. Converting Formulas into Text

At times you need the formula as text and do not want the formula to execute when you press Enter

I am going to show 3 ways to do it

1 Inserting a Semicolon at the start of the formula

2. Changing the Cell Format to Text

• Select the cell
• In the Number Format drop down
• Choose Text
• Then press F2 on the cell and press Enter. The formula will be displayed and not the result

 

3. Using FORMULATEXT to extract formulas

This function is available in Excel 2013 and above and can extract the formula used in the cell

 

3. Leave the last part of VLOOKUP empty

1. Notice that I have applied a comma and left the last part of the VLOOKUP empty. It means the same as writing FALSE or 0 (zero)
2. But if you don’t apply a comma in the end, VLOOKUP considers it to be TRUE or 1 (i.e approximate match)

 

4. Evaluate Formula using F9, F5, & Formula Evaluator

Use F9 to evaluate a part of the formula

1. Select the part that you want to evaluate
2. Press F9
3. Be sure to press Esc after that, else the formula will change the cell references to values

 

Use F5 to jump to a range / cell – Whenever you are dealing with large formulas and want to be sure if you have linked the cells correctly, you can hop over to the cells using the F5 key

1. Select the cell address / range that you want to jump to
2. Press the F5 Key to open the GOTO box
3. You’ll notice that the cell address is automatically copied in the reference area
4. Just hit enter
5. You’ll automatically jump to that cell

 

Use the Formula Evaluator Tool

1. Pick up the cell you want to evaluate
2. Use the shortcut ALT t u f to open the evaluate formula box. You can also find it in the Formulas Tab
3. Keep clicking on the Evaluate button to evaluate the formula step by step
4. You can even click on the Step in button if you want to step inside the formula further

 

Formula Evaluator is a great tool I personally use a lot to understand the workings of large & complex formulas

 

5. Use watch window to track your cells live

If you have some critical cells in your Financial Model / MIS report or a Dashboard that you always want to keep an eye out for, Watch window is a great tool for that

1. Click on Watch Window (on the right side) option in Formulas Tab
2. Click on Add watch and add the cell/s to the watch window
3. Since the Watch Window will be open always (unless you close it) any changes to the cell will reflect in the watch window. The good part is that you don’t have to go to that cell to track the changes

 

Plus Points

1. The watch window can be stowed in the excel interface just by dragging to any of the corners
2. You can place multiple cells (even between different excel workbooks) in the watch window

Read a more detailed post on Watch Window here

 

6. Copy formula down in the Edit Mode

This one is pretty cool when you have to copy the formula from a cell above but still want to make changes to it

1. Press Ctrl + ‘ (Colon) to copy the formula from the above cell in the Edit mode
2. Make sure the cell above has the formula else this won’t work
3. Note that the cell references (of the above formula) do not change when you use this shortcut

 

7. Use Wild Cards in your Formulas for approximate match

Excel provides some wild cards (they are not wild but friendly :D) that you can use in your formulas for doing approximation.

In the below example we have to find the Total of Amount Due, Paid and Difference but the problem is that for the SUMIF Formula to work you need criteria exactly matching the way it is written in the particulars column

1. The solution is using a Wild Card – Asterisks sign * before and after the word “Due”
2. This will accept any text before and after the word – Due
3. Thus making your formula robust, you can use variations of this technique to fit your case

 

8. Press Tab to autoselect Formulas

Simply use the Tab to autoselect the formula

 

9. For large worksheets use Manual Calculations

If you are working on heavy duty excel spreadsheets with a ton of data and formulas. The key to faster and smoother working is turning on manual calculations

1. In the Formulas Tab
2. Click on Calculation Options
3. And turn it to Mannual
4. Now each time you edit any formula and press enter the calculations won’t happen. You can calculate the entire sheet by pressing F9 at any time

The benefit of manual calculations is to protect you from the time wastage that happens while excel is taking its own sweet time to crunch numbers. The key is to do it all once

 

10. Show all formulas in the worksheet

It more often happens by accident than on purpose. Whatever be the case now you know how to deal with it

1. Press Ctrl + ~ to view all formulas in the sheet
2. Press Ctrl + ~ again to get back to the normal mode

 

Let me know which ones stuck with you and what are your favorite formula hacks!

 

More Tips and Hacks for you

1. 16 Pivot Table Tips and Hacks
2. 5 Crazy tricks in Excel
3. 10 Excel Habits you must develop
4. Become better at Dates 😉 – 17 Date Hacks in Excel
5.  5 Super helpful and time saving charting tricks