August 30, 2016
Hi fellow Excel Addict,
I've had a passion for working with Excel for more than 20 years and discovering the 'magic' hidden within it. I'm also passionate about sharing this 'Excel magic' with others who may be struggling with Excel because they either haven't received any training or because they don't realize how easy it can be to improve the way they're doing things.
I frequently hear from my readers saying, "learning small bits of Excel consistently week after week makes a big difference for me over time, without getting me overwhelmed".
I hope you are benefiting from my tips and consistently improving your Excel skills each week.
If you missed last Thursday's 'Excel in Minutes' tip, 'Left Align Text In Your Right Header or Footer', you can read it here now.
Please feel free to share my newsletter with your friends — chances are they will thank you for it.
Wishing you a great week end keep on Excelling,
Francis Hayes (The Excel Addict)
If you missed my last newsletter, you can click here to view it online.
Be Careful When Using Numeric Column Headings
Excel users frequently use numbers as column headings. For example, we often use years (e.g. 2014, 2015, 2016) as the headings across the top of a worksheet.
By default, all worksheet cells are formated as General and, when you enter a number, Excel treats it as a number. Unless you format those heading cells as Text, Excel will recognize them as numbers and there is a risk that they may erroneously get included in the calculations for some worksheet formulas and possibly go unnoticed.
For example, when you SUM a column of numbers below a numbered column heading (e.g. 2014), Excel's AutoSum sees this heading as a numeric value and includes the cell in the range used in the SUM formula, giving a wrong result. This can easily happen and possibly lead to costly and embarrassing problems.
There are a couple of easy ways to prevent this error. One is to precede numbered headings with an apostrophe (i.e. '2014). However, instead, I recommend that you format all of your heading cells (even the entire row) as Text.
1) First, select your heading cells (or the entire row);
2) On the Home tab, click the Number Format dropdown and select the Text option.
3) You will now need to re-enter these headers in order for Excel to re-evaluate them as text values. (Bonus Tip: Rather than retype each heading, select them all then repeatedly press the F2 key followed by ENTER). You will notice that, because these cells have been formatted as Text, they are now left aligned in the cell (assuming you haven't applied a different alignment to the cells previously). If you want, you can choose a different alignment from the Home tab on the Ribbon.
4) After you have formatted your headings as Text and reentered them, you will need to redo any formulas that previously included those cells. Now when you use AutoSum, Excel sees these headings as text and doesn't include them in the formulas.
| 'Spreadsheets Tips
Excel Addict' and 'Excel in Seconds' are publications of
Copyright Francis Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630