June 18, 2014
This newsletter is being sent to 33,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,
Thanks for joining me this week for more time-saving Excel tips. A lot of new people have joined The Excel Addict over the past few weeks so I want to welcome them to our growing community.
As you may know, I have been sharing my passion for and knowledge of Excel with tens of thousands of people all over the world for more than 10 years. In the past 20+ years that I have been using Excel, I have been frustrated over and over by people who use Excel as a main part of their job for years but yet have learned very little beyond the basics of creating formulas and formatting worksheets. The REALLY frustrating part is that many of them show no initiative or desire to learn new skills and there is no initiative from their managers or bosses to give them the training that could vastly improve the way they do their jobs.
I believe that there is no other software application around that has the potential for exponential improvement in productivity that Excel does. There are so many tasks in Excel that can be streamlined from hours of work for a 'beginner' to mere seconds for someone who has learned the skills, the real potential is almost unimaginable.
The fact that hundreds of millions of users in almost every business and organization all around world use Excel and the fact more than 90% of Excel users are working at little more than a beginner's skill level, means that there is possibly 'billions' of dollars in productivity and missed opportunities lost year after year because most businesses view employee training as an expense rather than an investment with an extraordinary high rate of return.
I hope that my newsletter and the resources that I share with you will help you and your organization tap into this Excel gold mine that's right under your nose. This may be a discussion you should have with your boss, or your employees if YOU are the boss.
Now that I've vented on this subject...again (I need to vent every couple of years), I'd like to share something else with you that I think you'll like.
If you've read my newsletters in the past couple of weeks you will know that this spring, there has been an extraordinary parade of icebergs floating past the coast of Newfoundland, which I shared with you in pictures.
People come to Newfoundland from all around the world for this experience. This week I personally experienced a couple of these amazing icebergs up a little closer.
One thing I discovered is that when you see an iceberg in the background of a scene, there is a sort of optical illusion, where the iceberg appears much larger than it really is.
Below is a picture I took on Tuesday from my office window, of an iceberg just outside St. John's harbour with a lighthouse in the foreground.
Notice how large the iceberg appears compared to the lighthouse.
(Click image to enlarge)
Here is a picture taken about three hours later at that same lighthouse with the same iceberg in the background...
Isn't that amazing? It's almost unbelievable.
And just to prove that the iceberg didn't move further away by the time I got to the lighthouse, here is another picture I took from my office the next day...
I can't help it. I have to show you just one more iceberg pic that will 'blow you away' This one is from last week in Harry's Harbour, Newfoundland.
I hope you enjoy these pictures. This will probably be the last of the icebergs that we will see come this far south this year now that the weather is warming up.
If you have any comments, questions, suggestions or just want to say "Hi", shoot me an email. I always love hearing from other Excel Addicts.
Take care and keep on Excelling.
Francis Hayes (The Excel Addict)
This week's tips...
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
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) Remove/Delete Duplicate Values From Your Data
2) How To Prevent Duplicates On Entry
3) Easily Click A Text Box's Border
Go here to view my Previous Week's Newsletter
You can access even more tips on my website by going to my members' page.
How To Work With Separate Worksheets On Dual Monitors
If you are fortunate enough to have two monitors connected to your computer, and are NOT using Excel 2013, this is something you may have struggled with: how to work with separate worksheets on dual monitors.
If you are using Excel 2013, each workbook opens in a separate window and all is good. You can move each window around to your hearts content.
However, for anyone using Excel 2010 or earlier, I often see people suggesting that to work with workbooks on separate monitors you have to open separate instances of Excel and then open one workbook in each. Although, opening Excel in two separate instances makes it easy to move each workbook to a separate monitor, it also presents a whole other set of problems. These include, not being able to: open two worksheets from the same workbook (i.e. the second will open as Read Only), create formulas that reference the other workbook, paste formulas from one worksheet to the other (i.e. values are pasted rather than formulas).
Although it takes a little more preparation, here's the method that I find works best for me.
You can work with two workbooks or worksheets on separate monitors. Here's what you need to do...
1) Close all workbooks except those you want to work with. If you want to work with separate sheets in the same workbook, from the View tab click the New Window command in the Window group;
2) Make sure your Excel window is not maximized. If it is, click the Restore Down button on the top right corner;
3) Click and drag the Excel window header so that the left edge of the Excel window is at the left edge of your left monitor;
4) If the top and bottom edges of the Excel window aren't already maximized to fit your monitor, point to the edge of the Excel window (the mouse pointer will change to a two-headed-vertical arrow) and drag to resize;
5) Now, drag the right edge of your Excel window across to the far edge of the second monitor. Your Excel window should now be stretched across both monitors;
6) Next, tile the windows by clicking View, Arrange All, Vertical;
7) You're now ready to work with both windows visible. See NOTES below
• When you're finished working on dual monitors, remember to resize the Excel window by dragging the right edge of the window back to the left monitor. At this point, you can maximize the window and it will fit on one monitor.
• When Excel is stretched across both monitors, the Ribbon and message boxes for both workbooks will appear in only the left window or sometimes partially split across both.
• The messages (e.f. AutoCalculate, etc...) usually displayed on the right side of your status bar will be visible on the second monitor only;
How To Locate All Merged Cells On A Worksheet
Merged cells in a worksheet can sometimes be helpful but, more often than not, they can cause you plenty of grief when it comes to selecting ranges, using Paste commands, sorting and many other everyday functions.
Maybe you have developed a practice of not using merged cells in your worksheet but chances are you will sometimes find yourself working with worksheets that someone else has 'had a hand in'.
Merged cells are not always easy to locate in a worksheet. Depending on how the worksheet is formatted, merged cells are often indistinguishable from other cells.
If you want to 'exorcise' all merged cells from a worksheet, finding them all could be quite a challenge. So here's a simple technique for locating all merged cells in a worksheet.
CAUTION: You should always make a backup copy of any workbook before making major changes.
1) Press CTRL+F to open the Find and Replace dialog;
2) Make sure that the 'Find what' field is empty and the 'Match case' and 'Match entire cell contents' options are not checked;
3) Click the Format... button;
4) Select the Alignment tab;
5) Check the 'Merge cells' option;
6) Click OK to close the Find Format dialog;
7) Click the Find All button. All merged cells will be listed at the bottom of the dialog;
8) With the Find dialog still open, press CTRL+A to select all cells listed at the bottom of the dialog and you'll notice that all of those cells will be select on the worksheet as well;
At this point I usually either:
(a) close the Find dialog and apply a background color to the selected cells to make them easier to identify or
(b) keep the Find dialog open and do my editing on the worksheet. When I need to select another group of merged cells, I click the cell reference in the Find dialog. If you have unmerged some cells, you can click the Find All button again to update the list.
Anytime you use this Find - Format process, I recommend that you clear the formatting from the 'Find what' criteria by clicking Format..., Clear, OK in the Find dialog to avoid problems the next time you need to use the Find dialog.
Let's Dump Sheets 2 and 3
I don't know about you, but it annoys me to see workbooks with three sheets when two of them are empty. When someone sends me a workbook with three sheets, I have to check each sheet to see if there's data in them. In most cases Sheet2 and Sheet3 are empty.
Since many, if not most, of the workbooks we create use only one sheet, the other two sheets that are included with each default workbook become redundant.
I always recommend changing the default number of sheets in new workbooks from 3 to 1. If you need more sheets, you can easily insert them as needed.
To change the default number of sheets in all new workbooks
1) Click the File tab and then select Options (In Excel 2007, click the Office Button, click the Excel Options button);
2) On the General tab in the Excel Options dialog, change the 'Include this many sheets' option to 1.
To insert a new worksheet
• Click the small Insert Worksheet button to the right of your sheet tabs,
• or Press SHIFT+F11
• or, on the Home tab, in the Cells group, click the dropdown arrow on the Insert option and select Insert Sheet
In my opinion, Excel's Insert Worksheet button to the right of the sheet tabs could have been made better. I see two problems with it. First, when you have multiple sheets in your workbook, the button is often pushed far to the right and hidden out of view behind the horizontal scroll bar. You then need to scroll to the right to access it. Second, when you click the button, it inserts the new sheet tab on the far right of the tab strip. It seems to me that the sheet tab should be inserted to the left of the currently selected tab. Now you have to move the newly inserted sheet tab to the position you want. I don't think this feature was fully thought out.
However, the best option in my opinion is to add the Insert Worksheet command to your Quick Access Toolbar (QAT).
To add the Insert Worksheet button to your QAT
Right-click anywhere on the Ribbon and select 'Customize Quick Access Toolbar'. From the 'Choose Commands from' dropdown, select All Commands, then scroll half-way down and select the Insert Worksheet command. Click the Add>> button. (If you don't have the New Workbook tool on your QAT, now is a good time to add it.
|"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