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.
Hi fellow 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?
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)
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.
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.
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;
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.
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.
The good news is, there is a much easier way to make your data fit the width of your screen.
Here it is...
1) 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.
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.
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...
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.
|"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