FREE Excel Tips Newsletter from
Subscribe here to get more tips like this every week
Excel In Seconds Tips and Tricks from The Excel Addict - Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365

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)
fhayes[AT] Quote of the Day

"It is never too late to be what we might have been.”

-- George Eliot --

If you missed my last newsletter, you can click here to view it online.

Excel in Seconds with

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.

Numeric Column Headings Erroroneously Included In Autosum in Microsoft Excel 2007 2010 2013 2016 365For 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.

Here's how:

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.

Format Column Headings As Text in Microsoft Excel 2007 2010 2013 2016 365
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.

Numbered Column Headings Formatted As Text in Microsoft Excel 2007 2010 2013 2016 365

FREE Excel Tips Newsletter from
Subscribe here to get more tips like this every week

Thanks for supporting this newsletter and website

Earnings Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that for a few dollars. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are ever not completely satisfied with anything I recommend, please let me know and I will get your money back for you...GUARANTEED. You can't lose.
 'Spreadsheets Tips From An 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