Microsoft Excel 2003, 2007, 2010, 2013 Tips

The Excel Addict - Help with Excel 2013, 2010, 2007, 2003
July 2, 2014
   
This newsletter is being sent to 34,000+ Excel 'addicts' around the world
using Aweber, the best solution for managing your email campaigns.  


To change your email address click the link at the end of this email

You can find the online version of this week's newsletter here.


Greetings from The Excel Addict

Hi fellow Excel Addict,

Francis Hayes 'The Excel Addict'Keep reading, there's a bit of a contest below!

Things have been pretty busy around here recently. Last week I didn't get a chance to publish my newsletter, so I'm feeling a little disappointed because I have always prided myself on consistently delivering my tips to my readers...for more than 10 years.

One of the things that I have been doing recently is beginning the process of updating my website
and newsletter. It hasn't change much in the last 10 years, so a major overhaul is long overdue. I promise you, it will get better.

So, one thing that I would like from you is a suggestion for a byline for my website. I am having a new logo designed for my website and would like to come up with a great new byline.

In the past I have used 'Helping Average Spreadsheet Users Become Local Excel Experts' and 'Learn to Love Excel Again'. Currently, I'm considering 'Helping You Become the Local Excel Expert'.

If you think you have a more catchy byline that captures the essence of The Excel Addict website and newsletter, please send me your suggestion by email to fhayes[AT]theexceladdict.com and put 'EXCEL ADDICT BYLINE' in the subject line.

What's in it for you?

Francis Hayes 'The Excel Addict'Well, over the past few weeks I have gotten a lot of positive feedback from the pictures and stories I have been sharing about my home province of Newfoundland, Canada. We have a very popular local magazine 'Downhome' that Newfoundlanders who live 'away' and people who have visited Newfoundland and fell in love with it, use to get their 'fix' of Newfoundland. Inspiring stories, tales of travel and adventure and stunning local photography are just some what you'll find in this magazine.

To help feed the appetite for more of Newfoundland, I will be giving away two subscriptions to the Downhome magazine. If you send me a suggestion for a byline for my website, I will enter your name for a draw for a one year subscription to the Downhome magazine. If I choose to use your suggested byline, you will get a subscription and I will draw a name for the other subscription. If I don't use any of the suggested bylines by my readers, I will draw names for the two subscriptions from everyone who offers a suggestion.

I look forward to your suggestions.


Take care and keep on Excelling.
Francis Hayes (The Excel Addict)
Email: 
fhayes[AT]TheExcelAddict.com



Need to Update Your Subscription Information? Please note that below every newsletter there is a link that you can use to access and update your profile in my database. You can make changes to your name and email address  - and it only takes a couple of seconds.


This week's tips...

1) Make Your Pivot Table Default To SUM Not COUNT
2) Make Your Spreadsheet Fit Your Screen
3) Create Dynamic Line Numbers

Having trouble displaying this newsletter?
Go here to view my Online Version of This Week's Newsletter

Note that if you ever find an error in my newsletter, chances are that others have too. Whenever a mistake is brought to my attention, I will make the correction (usually the same day) to the online version. So you may want to check back using the link above to read the corrected tip.

Last week's tips were...

1) How To Work With Separate Worksheets On Dual Monitors
2) How To Locate All Merged Cells On A Worksheet
3) Let's Dump Sheets 2 and 3

Go here to view my Previous Week's Newsletter

You can access even more tips on my website by going to my members' page

Thanks for supporting this newsletter and website

"You can have everything in life you want if you will just help enough other people get what they want."- Zig Ziglar

Microsoft Excel Tip #1

Make Your Pivot Table Default To SUM Not COUNT

Here is a common problem that everyone who has ever used Pivot Tables has encountered. Sometimes when you add certain fields to the Pivot Table, Excel uses the COUNT function by default in subtotals rather than SUM.

You can easily change the function from COUNT to SUM by right-clicking the column in the Pivot Table changing the Value Field Settings. But to do this for many column would be a lot of work.

Default Pivot Table settings COUNT or SUM?

The reason Excel uses the COUNT function for certain columns in a Pivot Table is that, in the original data table, those columns contain non-numeric data (either blanks or text).

There is no way to change Excel's default setting for this, however, you can avoid this problem by ensuring that your original data table does not include text or blanks in numeric columns.

You may think that replacing all those blanks cells with zeros is going to be a big chore. Well, it isn't.

Replace blank cells with zeros
 
1) Select all of the (numeric) columns of data in your original source data;
 
2) Press the F5 key on your keyboard and click the Special... button in the Goto dialog;
 
3) Select the Blanks option*** and click OK. Only the blank cells are now selected;


How to select only blank cells in Excel 2003, 2007, 2010, 2013
 
4) Type 0, hold down the CTRL key and press Enter. All blank cells are now filled with zeros. Note that holding down the CTRL key when pressing Enter forces Excel to enter the same value (or formula) in all selected cells;
 
5) Now go back, right click anywhere in your Pivot Table and select Refresh.


*** If any numeric columns in your source data contain text, repeat the steps above but instead select the Constants and Text options in Step 3.

How to select only text cells in Excel 2003, 2007, 2010, 2013




My goal: To teach One Million Excel Users

Microsoft Excel Tip #2

Make Your Spreadsheet Fit Your Screen

You put a lot of preparation into designing your spreadsheets exactly the way you want them. But often you'll find that your spreadsheet is a little wider than will fit your screen. Maybe you have many spreadsheets like this now where the last column or two are just off the right side of your screen.

The problem here is that not only do you have to scroll up and down to navigate your spreadsheet but you also have to scroll left and right.

When the data is slightly wider than will display on the screen I've seen people try using smaller font sizes, make columns narrower, or abbreviate data in some columns just to make it all fit within their screen.

Zoom to fit selection

The good news is, there is a much easier way to make your data fit the width of your screen.

Here it is...

An easy trick to make your Excel data fit your screen1) Select a range of cells from the left-most cell to the right-most cell;

2) Click the View tab and click the Zoom To Selection command.  Excel will change the zoom percentage to fit the selected cells on your screen.

You can see the zoom percentage for the active workbook in the Zoom Controls on the right side of the Status Bar.

An alternative method to Zoom to Selection is to click on the percentage value on the Zoom Controls and select Fit Selection from the Zoom dialog and click OK.


Use Zoom Controls to squeeze your spreadsheet to fit your screen


Tip #3

Create Dynamic Line Numbers


When you want to show line numbers for a list of data, you can simply enter 1 in the first cell, enter 2 in the second cell, select both cells and drag the Fill Handle down to extend the sequence of numbers.

How to create Dynamic Line Numbers in an Excel Table

However, if you're working with a long list where rows are frequently inserted and deleted, updating these numbers can become quite cumbersome.

Instead, you can use formulas that automatically update when rows are inserted or deleted.

The following formula will display the row number of the cell that contains the formula...

=ROW()

So, =ROW() in cell A4 would result in 4.

If you want to begin your row numbering in a row other than row 1, you will need to add an offset component to the formula.

So, for example, if you have a list of names in column C starting in cell C4 and you want row number 1 to start in cell B4, you would enter =ROW()-3 in cell A4 and copy the formula down column A to the bottom of your list.

Now whenever you delete a row, the row numbers adjust automatically. When you insert rows, the formulas will again adjust automatically, however, you will need to copy the formula to the inserted rows.

Dynamic Numbering in an Excel Table

If your data is set up in an Excel Table (Home tab, Styles group, Format as Table), this process is even easier. Just enter the first formula (e.g. =ROW()-3) in the column and Excel will automatically fill the remaining formulas down to the end of the Table. If you insert new rows in the Table, Excel will automatically copy the formula to the inserted rows.

How to create Dynamic Line Numbers in an Excel Table




Subscription Information

Spreadsheet Tips From An Excel Addict is available only to subscribers of my newsletter.

If this newsletter was forwarded to you and you would like to get your own copy, please visit TheExcelAddict.com or send a blank email to theexceladdict(AT)aweber.com

If you would like to share this newsletter with others...
1) Forward this newsletter by email, but first delete the unsubscribe link at the very bottom so you don't get accidentally unsubscribed

2) Ask your friend/colleague to visit TheExcelAddict.com or send a blank email to theexceladdict(AT)aweber.com

3)  Post a link to TheExcelAddict.com in a company newsletter or website

"Spreadsheets Tips From An Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630