August 6, 2014
This
newsletter is being sent to 34,000+ Excel 'addicts'
around the
worldusing 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,
In the past week some
of you have been asking me about my running and, particularly, my
results in the local Tely 10 race which was held on July 27th. As I
mentioned in an earlier newsletter, my goal this year was to make a
significant improvement over my last year's time. Unfortunately, a week
before the race, after a 16km (10mi) practice run, my knee hurt and
never improved much. It was a difficult decision to back out of a race
that I had been preparing for more than 6 months.On the bright side, all the time I used to spend running will now be put to use for other things that I have been putting off. Yes, it's a long list.
Just a quick note, I am currently making arrangements to have the bonus 'Macros Made Easy' e-book download available to everyone who used the promo code EXCELADDICT to enroll in Mynda's Excel Dashboards course in July. That should all be ready next week.
By the way, Mynda's Excel Dashboard course is now full and the next course will be starting in October.
Also, I have randomly selected two names from the submissions to my 'Byline Contest' a few weeks back. The two winners of a one-year subscription to Newfoundland's 'Downhome' magazine are Carl Jordan and Sylvia Martin. I will be in contact soon.
I hope you enjoy my tips this week. Please send me an email at any time, even if it's just to say 'Hi'. I do love hearing from other 'Excel Addicts' around the world.
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) Quickly Clear Formula Errors From Blank Rows
2) An Alternative To Freeze Panes For Wide Spreadsheets
3) Using Very Small Fonts In A Cell
Having trouble displaying this newsletter?
Go here to view my Online Version of This Week's Newsletter
2) An Alternative To Freeze Panes For Wide Spreadsheets
3) Using Very Small Fonts In A Cell
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) A Double-Click Trick To
Quickly Move Around Your Worksheets
2) Create Dates Using Other Cell Values
3) Prevent Worksheet Colors From Printing
Go here for more timesaving Excel tips
2) Create Dates Using Other Cell Values
3) Prevent Worksheet Colors From Printing
Go here for more timesaving Excel tips
You can access even more tips on my website by going to my members' page.
Quickly Clear Formula Errors From Blank Rows
It is common in Excel reports to have blocks of data separated by blank rows. When you need to fill a formula, such as a division formula, down a column you often get errors in the blank rows.
Here's a solution to help you clear all of these formula errors quickly.
1) Select the range of cells that includes all of the errors;
2) From the Home tab select Find & Select, Go To..., Special... or press the F5 key on your keyboard;
3) In the Go To dialog, select the Formulas option, clear all checkmarks except for Errors and click OK;
What if you want to clear formulas you've filled down that don't result in errors?
Answer: You filter a column in your table for blanks and then clear the formulas from the visible cells.
Here's how...
1) Click any cell in your table and, from the Data menu, select Filter. Filter arrows should now be on the top of each column;
2) Click one of the filter arrows in a column that contains blank cells and choose Blanks. You should now see only the formulas that you filled down and you want to delete;
3) Highlight the cells containing the formulas you want to delete, hold down the ALT key and press the semicolon (;) key to select only the 'visible cells';
4) Press Delete to clear the formulas;
5) Click the Filter command on the Data tab to turn off the AutoFilter dropdown arrows.
An Alternative To Freeze Panes For Wide Spreadsheets
When using wide spreadsheets, you will often need to work with data on the far right of the sheet but also keep visible the related data on the left. You may already know that a good solution for this is Freeze Panes. With Freeze Panes, you select the column to the right of the one you want to freeze and, from the View tab, click Freeze Panes, Freeze Panes. Now you can scroll to the right while the frozen column remains visible on the left.
Freeze Panes is a good solution but is not without its drawbacks.
I often find that when I am moving around on the right side of my worksheet using the arrow keys, I sometimes scroll too far to the left and my data on the right scrolls off the screen. Then I need to scroll to the right again. This can get aggravating when it happens over and over.
One simple solution that I often use is to temporarily hide the columns that I'm not using.
Another solution that I find works great for worksheets where I regularly need to hide (and unhide) specific columns is to use Grouping (often called Outlining). Grouping is normally used for hiding the details of subtotaled data but, in some cases, it can work great for quickly hiding and unhiding columns.
This would also work for hiding and unhiding specific rows but in most cases, for me, I mostly use this for hiding columns in my very wide spreadsheets.
To hide specific column(s) using Grouping. Note that if you want to hide multiple columns, the columns for each grouping must be contiguous...
1) Select the (contiguous) columns (e.g. Columns C:P) you want to apply Grouping to;
2) From the Data tab click Group. An outline bar and [-] symbol appears above the columns. When you click the [-] symbol, the columns will be hidden and the symbol changes to a [+];
3) You can select other (contiguous) columns to apply grouping to.
Once you have finished grouping your columns, you will have a simple and quick way to quickly hide and unhide these columns whenever you need to. Click the [-] and [+] buttons to hide or unhide the adjacent columns or click the Outline Butons (the small buttons with numbers above the top left corner of the worksheet) to hide or unhide all grouped columns on one click.
Note that this also works for Grouping (i.e. hiding and unhiding) rows.
Using Very Small Fonts In A Cell
We
all are probably guilty of creating massive spreadsheets and then
attempting to fit them onto a single sheet of paper. One issue that you
may face is fitting text into a cell without resorting to increasing
the column width.Here are a couple of other options...
Option 1:
When you select a font size in a cell, it appears that the smallest font size you can use is 8. In fact, you can use any font size you want between 1 and 409 (including half sizes). Simply type the font size in the font size dropdown and press Enter. In the case of squeezing text into a cell you can choose a font size smaller than 8.
Option 2:
Another option is to tell Excel to shrink the text to fit the cells. You will find this option on the Format Cells dialog.
1)
Open the Format Cells dialog by selecting the Home tab and clicking the
small dialog launcher arrow on the bottom right corner of the Alignment
group;2) Select the 'Shrink to fit' option and click OK;
This option will automatically scale the text so that it fits within the width of the cell. As you adjust the width of the cell or as you increase or decrease the amount of text in the cell, Excel automatically resizes the text to fit within the cell. Note that this may cause different font sizes in cells even though you may have formatted the cells to a certain size. With the 'Shrink to fit' option selected, if you increase the column width or reduce the amount of text in the cells, the maximum size that the font will 'unshrink' to is the font size that was originally applied to the cell.
Note that 'Shrink to fit' doesn't work on cells formatted with 'Wrapped Text'.
Option 3: Use Arial Narrow. If you don't want to reduce the size of your font but still fit more text in a column, Arial Narrow is often a great solution.
| 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...
|
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630