You know that feeling when something is just a little bit bothersome but not quite annoying enough to spend time to resolve? When I have one of those, the annoyance simmers below the surface for a really long time and then one day I get the sudden urge to fix it. As in, fix it right now, I can’t stand it another minute! After finding the solution, there is such joy that I wonder, “Why did I wait so long?”
So, without building up any more suspense, today’s “I can’t stand it another minute!” was the default AutoCorrect which replaces Internet and network paths with hyperlinks. Email addresses convert to a hyperlink, such as firstname.lastname@example.org. I don’t like this as I am not usually emailing directly from the Excel spreadsheet and if the list is printed, the hyperlinks appear in a different color and underlined. I used to go back and remove the hyperlink manually by right-clicking and selecting “Remove Hyperlink.” Really not a big deal, until the day it suddenly becomes an annoying big deal!
Here’s how to change this default setting:
Select File from Menu Bar. Then select Options almost at the bottom of the list.
2. Select Proofing and then click the AutoCorrect Options button.
3. Select AutoFormat As You Type tab. Then uncheck the Internet and network paths with hyperlinks box.
4. Click OK button. Click second OK button.
That’s it! Just a few clicks and I’m doing the happy dance! This reminded me of how much customization is available throughout the program and how just a few minutes of time can streamline my usual tasks or eliminate default settings that don’t suit me.
The new chairperson of your “Staff Party Planning Team” bounces in your office Monday morning. You learn that plans are underway to begin celebrating employees’ birthdays and the team needs a list of all staff. No problem. A quick download from the payroll system yields an alphabetical list of names and birthdays:
A quick re-sort to put in chronological and you’ll be done:
Well, not so fast. The chronological sort uses the full date and thus, places the list in order from oldest employee to youngest! What the team is looking for is to sort dates based on month and day only, such that all January birthdays would be listed first, then February, and so on.
Simple formulas will accomplish the goal. The functions MONTH(date) and DAY(date) will extract the month and day numbers to columns D and E.
At last we are ready to sort! Select rows 1 through 8 and click on the down arrow on the Sort & Filter option in the Editing group on the Ribbon. In the drop down, click on Custom Sort.
Because we included Row 1 in the selection, the box “My data has headers” should be checked. Had we selected Rows 2 through 8, Row 2 contains data rather than headers, so we would uncheck that box.
Including the header row makes selecting the sort levels very straightforward. Here we select Month as the first level and Day as the second level. Then click OK.
The sort is complete and now your Party Planning Team can be ready for each month’s celebrations. Let the good times roll!
“Any customer may have a car painted any color that he wants so long as it is black.” Henry Ford
Modern day product development has come a long way from Henry Ford’s 1909 perspective about customer’s desires! Our lament is often the polar opposite, we now have so many product variations, our problem is often how to choose between so many choices!
Though many options exist in Excel, new worksheets are created in Excel with default settings which may not suit your company’s style guidelines, the typical spreadsheets you create or even your own personal preferences. If you find yourself making repetitive changes in the spreadsheets you create, why not change the default settings? Here’s how:
1. Identify formatting you would like to set as your default. Examples are font, font size and color, column width, page size, margins, alignment, etc.
2. Search for the location of the XLSTART folder in Programs file hierarchy. Using Windows Explorer, search for XLSTART kind:=folder. Make note of the location. In my Win7 OS and Office installation, my XLSTART folder is c:\Program Files (x86)\Microsoft Office\Office14\XLSTART. Yours may be different.
3. Create a new blank workbook. Make all of your preferred changes.
4. Click File, Save As.
5. Enter Book as the File Name. Select Excel Template in the Save as Type drop down options. Navigate to the XLSTART folder located in step 2. Click the Save button. The next time that Excel is started and a new workbook is created, it will match your changed settings.
With all due respect Mr. Ford, I think I’ll take my font in PURPLE!
Ever have one of those late nights finishing a marathon number crunching session? Just about the time you breathe that deep sigh of relief that everything is ticked and tied, you realize that your 9 am presentation tomorrow morning is only a few short hours away and your spreadsheet is nowhere near presentation ready.
Having a few essential formatting shortcuts under your belt will make quick work of polishing your spreadsheet for printing or projecting. This is the third in a series of keyboard shortcuts that reduce the time reaching for the mouse to find and click common actions. Keyboard shortcuts for navigation may be found here and shortcuts for data entry and editing may be found here.
In this earlier post, a couple dozen keyboard shortcuts were listed to help you navigate around your spreadsheets more quickly and smoothly. The following list provides another set which make data entry and editing quicker. Remember, don’t try to remember the whole list at once. Pick two or three that are common actions you use. Memorize those and practice, practice, practice. Then add a couple more to your repertoire. One more shortcut list may be found here.
P.S. The CTRL + Shift + ) shortcut to unhide columns has a Windows Vista and Windows 7 operating system conflict but can be adjusted using these instructions.