• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Pro Excel Formula Writing Tips

You are here: Home / Excel Formulas / Pro Excel Formula Writing Tips
Pro Excel Formula Writing Tips
November 30, 2022 by Mynda Treacy

Excel Formula Writing Tips, Tricks and Tools that will change how you work in Excel

Formulas are THE MOST IMPORTANT EXCEL SKILL you can master, but when you start nesting functions, things can quickly get out of hand. In this lesson I’m going to cover some little known Excel formula writing tips, tricks and tools that’ll give you ninja level skills and enable you to decipher any formula. Even ones you haven’t written yourself.

Watch the Video

Subscribe YouTube


Download the Quick Reference Guide

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook and follow along. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.



Formula References

The first thing we can do to make formulas easy to write and later read is to format our source data in an Excel Table with the keyboard shortcut CTRL+T. This way we can use the Table’s structured references in our formulas.

In the example below you can see the formula references the table name and column names, making the formula much clearer to read without having to refer back to the cells themselves:

structured references in excel table

Bonus tip: Tables are given a default name when they’re created, in the example above it’s Table1. You can rename the table with something more useful via the Table Design Tab, then enter a new name in the Table Name field:

rename an excel table

Comparing the Table’s structured references to regular cell references and you can see it’s a no brainer:

=INDEX(D2:D76, MATCH("Socks", C2:C76, 0))

Another benefit of using Excel Tables is the Structured References automatically include any new data added to the table, so there’s no need to edit formulas to pick up new cells.

Automatic updating and structured references are just a couple of the benefits of using Tables, see them all in my Excel Tables course.

Alternatively, if your data isn’t in an Excel Table, you can define names and use those in your formulas instead. If your data range is likely to grow, then you should use dynamic named ranges that automatically adjust.

Formula Structuring

When you start nesting functions inside one another it’s a good idea to wrap the individual components onto separate lines so that it’s easy to read. It’s easy to do in the formula bar by entering a line break with ALT+ENTER:

entering line breaks in a formula

Alternatively, if you have Excel 2013 or later you can use the Advanced Formula Environment. Simply paste the unformatted formula in, and it will format it for you (see video for steps):

advanced formula environment in excel

And the nice thing about using the Advanced Formula Environment is the colour coding that makes it easier to read the formula. Simply copy the formula out of the Advanced Formula Environment and paste it over the top of the original formula in the formula bar* or define the formula as a name by saving it in the Advanced Formula Environment.

*Note: it will not retain the colour coding shown in the Advanced Formula Environment if you copy and paste it into the formula bar.

You can download the Advanced Formula Environment via the Insert tab > Get Add-ins:

install the advanced formula environment in excel

Then search for ‘Advanced Formula Environment’:

search for the advanced formula environment

Once installed it will be available on the Home tab of the ribbon:

advanced formula environment on excel ribbon

Formula Constants

Try to avoid hard keyed values in formulas like in the previous examples where the lookup value, “Socks”, was hard keyed. Instead, place the value in a cell. You can then reference this cell from multiple formulas and should you need to update it, simply edit that one cell and it will update all formulas:

use constants in excel formula

Even better is to give that cell a name by typing it in the name box:

give cell a name for excel formula

And use that in your formulas:

use name in excel formula

Alternatively, you could define the constant in a name:

use defined name for constant in excel formula

And use the name in the formula:

use defined name in excel formula

You can then edit the defined name to change it, and it will update all formulas that reference that name.

Formula Navigating

If you’ve ever experienced Excel not letting you enter a formula because you’re missing a closing parenthesis, but you just can’t see where it should go, then you’ll find these formula navigating tips helpful for identifying where each component of the formula starts and ends.

When you’re in edit mode inside a formula, the function tooltip will display however, often this can be in the way. Hover your mouse over the edge of the tooltip until the 4-headed arrow appears, then left-click and drag it out of the way.

move formula tooltip in excel

Navigate to each argument using the function hyperlinks in the tooltip:

navigate to function arguments in excel function using hyperlinks

The bold argument in the tooltip indicates the current location of the cursor or selected component of the formula:

bold highlighted selected argument in excel function

After the last argument you should have a closing bracket/parenthesis. We can see one here for the MATCH function’s match_type argument:

highlighted argument for excel match function

And another here for INDEX’s row_num argument:

highlighted argument for excel index function

If you can’t remember how a function works, click on the function icon to the left of the formula bar to open the function wizard:

open the excel function wizard

The function wizard dialog box contains more information about the function and each argument:

the excel function wizard dialog box

Formula Evaluating with F9

You may have managed to get Excel to accept your formula, but perhaps it’s not returning the correct result. Here we have a couple of tools that will help identify where the problem is.

First, we can use the F9 key to evaluate parts of the formula then press F9:

evaluate parts of excel formula using f9 function key

It will display the result of the selected element. Continue selecting elements and evaluating with F9. Press CTRL+Z to undo the last element evaluated, or Escape to return the formula to its prior, unevaluated state.

Formula Evaluating with the Evaluate Formula Tool

Sometimes you have many nested functions and elements and it’s difficult to tell what order Excel is evaluating in. This is when the Evaluate Formula tool is handy. It takes you step by step through the formula in order of evaluation displaying the results one component at a time:

excel evaluate formula tool

Pressing ‘Evaluate’ displays the cell range and moves the underline to the next element to be evaluated:

using the excel evaluate formula tool

If the formula references the result of another formula, you can step in and step out of the referenced formulas. In the example below, cell C7 contains a formula:

using step in to another formula in excel evaluate formula tool

Stepping in shows the formula and I can then start evaluating it:

evaluate another formula using step in in excel evaluate formula tool

evaluate formula in excel evaluate formula tool step 1

evaluate formula in excel evaluate formula tool step 2

evaluate formula in excel evaluate formula tool step 3

The limitation of the Evaluate Formula dialog box is that you cannot make it any bigger and often it’s too small to display the entire formula results, which means lots of scrolling making it difficult to use.

Pro Excel Formula Writing Tips

More Excel Formulas Posts

top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.

5 Excel SUM Function Tricks

Excel SUM Function tricks uncover a load of hidden features that aren’t obvious at first glance. Some are super useful in other functions too.
Category: Excel Formulas
Previous Post:dark theme dashboardsDark Themed Excel Dashboards
Next Post:Excel Advanced Formula Environmentexcel advanced formula environment

Reader Interactions

Comments

  1. Robert Creamer

    January 15, 2023 at 5:37 am

    Great Video.
    The AFE comes up on my Formula bar, not sure why, and it doesn’t matter, but I guess that is an update (perhaps because Microsoft is pushing the AI tool Analze Data on the Home page now.

    Reply
    • Mynda Treacy

      January 17, 2023 at 2:39 am

      It’s an update, Robert 🙂

      Reply
  2. Michel Tremblay

    December 1, 2022 at 2:35 pm

    Thank you so much for sharing your knowledge. I’m a retired accountant but still in love with Excel. That’s what I do as a hobby. I admire your generosity, its help me a lot.

    Reply
    • Mynda Treacy

      December 1, 2022 at 4:58 pm

      That’s wonderful to hear, Michel!

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x