7 Powerful Tips to Help You Master Excel Formulas

Microsoft Excel is an amazing program. It can be used to track and manage all kinds of information including text, numbers, and dates – but, if you’re like most people who attend our Microsoft Excel courses, you want to become more proficient with Excel formulas.

Related: 7 Excel Functions That Will Make Your Life Easier

These 7 tips are a great first step toward mastering Excel formulas!

Tip #1 – Understand the Purpose of Excel Formulas

Formulas are used to perform calculations. Excel can use any of the mathematical operators you’re familiar with. You can add, subtract, multiply, or divide numbers in a spreadsheet.

Here are the symbols Microsoft Excel uses to represent mathematical operators.

Addition +
Multiplication *
Division /
Exponents ^
Equals =

Tip #2 – Understand the Structure of Excel Formulas

In Excel, all formulas start with an equals sign (=). While you can enter formulas using values (like =2+5 or =4*3), most of the time you’ll use cell references. Using cell references in formulas ensures that calculations are accurate even if you change the values in your spreadsheet. Let’s look at an example.

Excel Formulas 1

In cell A3 (above), the formula adds values from A1 and A2, giving us a total of 15. When we use cell references, we’re basically saying “add the value in cell A1 with the value in cell A2”. If we change the value in one of the cells, the formula’s result is updated.

Excel Formulas 2

We changed the value in cell A2 (above) to 3 and the formula results were automatically updated to display the new total: 15.

Tip #3 – Enter Formulas Using the Keyboard

There are two ways you can enter formulas in Excel. You can type cell references in a formula or you can click on the cells using your mouse.

Let’s look at how to type an Excel formula by entering cell references using your keyboard.

  1. Select the cell that will contain your formula and type an equals sign (=).
    Notice that the equals sign appears in the selected cell and the formula bar.
    Typing Formulas 1

  2. Type B2+B3.
    Typing Formulas 2

  3. Press ENTER, to complete the formula.
    Typing Formulas 3

Tip #4 – Enter Formulas Using Point and Click

Instead of typing an entire formula, you can enter cell addresses by selecting specific cells using your mouse. This method is often called the ‘point and click’ technique. Here are the steps:

  1. Select the cell that will contain your formula and type an equals sign (=).
    Point and Click 1

  2. Using your mouse, select cell B3.
    Point and Click 2

  3. Type + then select C3.
    Point and Click 3

  4. Press ENTER.
    Point and Click 4

It doesn’t matter whether you type cell references within your formulas or you click on the cells using your mouse. It really comes down to personal preference. I find that clicking on the cells results in fewer mistakes.

Tip #5 – Copy Formulas Using the Fill Handle

We can use Excel’s fill handle to copy the formula down to the remaining cells in column D.

  1. Click to select cell D3 then carefully position your pointer above the fill handle in the cell’s lower right corner.
    Your mouse pointer should turn into a small, black ‘+’ sign.
    Fill Handle 1

  2. Drag the fill handle down to cell D6.
    Formulas are entered for all cells in the range.
    Fill Handle 2

Tip #6 – Know How to Edit Formulas

If you make a mistake, you don’t have to re-enter your formula. You can edit Excel formulas in the formula bar or directly in a cell.

In this example, the formula adds B2 and C3 – an obvious mistake.

Editing 1

Let’s look at both ways to edit this formula.

Editing in the Formula Bar

If you click in the formula bar after the C3 cell reference, Excel highlights the cells included in the calculation. Press BACKSPACE twice to delete the C3 cell reference, type B3, and press ENTER.

Editing 2

Editing Directly in a Cell

To edit a formula directly in this cell, double-click cell B4 (or press F2). BACKSPACE over the C3 cell reference, click cell B3, and press ENTER.

Editing 3

Tip #7 – Apply What You’ve Learned!

All of this knowledge is useless, unless you apply what you’ve learned right away. Incorporate these skills into your day to day work as soon as possible and let us know how you’re doing.

Do you prefer to type cell references in your formulas or click on the actual cells? Do you edit formulas in the formula bar or make changes directly in the cell? Answer in the comments below!