3 Excel-lent tips for your Wednesday

(Sorry for the subject line, didn’t turn off dad-mode before starting to write…)

You might have noticed that Excel has about 4,237 features in its ribbon. If you’re anything like me, you probably find that overwhelming. Well today,

I’m going to share just three quick tips, no overwhelm involved.

1. Easy Math

If you ever need quick access to basic math on a range of cells, you might not even not to create a formula!

When you select a few cells, the status bar will automatically show you the average, count, and sum.

2. Absolute Cell References

When you create a formula that references another cell (ie = C2 * 3), Excel will helpfully update the reference for you, especially when you are copying the formula to other cells.

In practice, this means if you reference cell D12 in your formula, then copy your current cell one to the right, the formula will now reference E12. Likewise, if you copy the cell down one, it will reference D13.

If you don’t want this to happen, you can make all or part of the reference absolute, meaning it won’t change. This is done by adding a $ before the part you want to stay constant, ie $D$12, $D12, or D$12 to make the reference entirely absolute, or partially absolute.

3. Named Ranges

Named ranges are pure gold, and might just change your life!

A named range is exactly what it sounds like. You are able to give a cell range a name and reference it in formulas. Instead of referencing cell D12, you can name it something appropriate like “total_processing_fee”. This has three major benefits:

1. Your formulas are much easier to read. = price - cost is much easier to understand then = C12 - D27
2. Excel will suggest the named ranges as you type in your formulas, helping prevent errors and typos.
3. Named ranges separate the concept you are naming and the cell location in the spreadsheet. This means if you need to move the named range, you can update the named range once and all related formulas will automatically reference the new, correct location.

You can add a new named range by:

• selecting some cells
• going to the “formula” tab
• click “Define Name”
• fill in a name and click “Ok”

---

There you go, three simple ways to make your spreadsheeting life a little bit better.

Did you already know all three tips? Do you have a favourite tip you want to share? Reply and let me know!