How to Remove Auto-Formatting of Hyperlinks

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 info@thespreadsheetwizard.com. 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:

  1. Select File from Menu Bar. Then select Options almost at the bottom of the list.

How to Remove Auto-formatting of Hyperlinks Step 1

2. Select Proofing and then click the AutoCorrect Options button.

How to Remove Auto-formatting of Hyperlinks Step 2

3. Select AutoFormat As You Type tab. Then uncheck the Internet and network paths with hyperlinks box.

How to Remove Auto-Formatting of Hyperlinks Step 3

 

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.

 

 

How to Sort Dates by Month and Day in Excel

Sort_Dates_Excel_3The 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:

Sort_Dates_Excel_1

A quick re-sort to put in chronological and you’ll be done:

Sort_Dates_Excel_2

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.

Sort_Dates_Excel_4

Sort_Dates_Excel_5

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.

Sort_Dates_Excel_7Because 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.

Sort_Dates_Excel_7

The sort is complete and now your Party Planning Team can be ready for each month’s celebrations. Let the good times roll!

Sort_Dates_Excel_8

 

How to Change Default Settings in Excel

“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. Change_Default_1

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.

Change_Default_2a

With all due respect Mr. Ford, I think I’ll take my font in PURPLE!

 

Keyboard Shortcuts in Excel for Formatting

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.

Keyboard_Shortcuts_1
By MC3 Adam D. Wainwright [Public domain], via Wikimedia Commons
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.

Hey, here’s one more tip before you turn out the lights for the night: Center that spreadsheet before printing. Now…..good night, sleep tight.

 

ACTIONSHORTCUT
BoldCTRL + B
ItalicsCTRL + I
UnderlineCTRL + U
Format BoxCTRL + 1
Outline borderSHIFT + CTRL + &
Remove borderSHIFT + CTRL + _
Add CommentSHIFT + F2
General Number CTRL + SHIFT + ~
Currency FormatCTRL + SHIFT + $
Percentage FormatCTRL + SHIFT + %
Exponential Number FormatCTRL + SHIFT + ^
Date formatCTRL + SHIFT + #
Time FormatCTRL + SHIFT + !

Keyboard Shortcuts in Excel for Data Entry & Editing

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.

 

ACTIONSHORTCUT
Edit cellF2
Create formula= (equal sign)
CopyCTRL + C
CutCTRL + X
PasteCTRL + V
Paste SpecialALF H + V + S
Repeat last actionCTRL + Y
UndoCTRL + Z
Fill DownCTRL + D
Fill RightCTRL + R
FindCTRL + F
ReplaceCTRL + H
Enter AutoSum formulaALT + '+' (plus sign)
Show formula/valueCTRL + ~
Start new line in same cellALT + ENTER
Delete cell contentDELETE
Clear cell content & formattingALT + H + E + A
Delete cell and move adjacent cellsCTRL + - (minus sign)
Insert cell and move adjacent cellsCTRL + SHIFT + '+' (plus sign)
Insert row/columnCTRL + SHIFT + '+' (plus sign)
Delete row/columnCTRL + - (minus sign)
Hide rowCTRL + 9
Unhide rowCTRL + SHIFT + (
Enter current dateCTRL + ;
Enter current timeCTRL + SHIFT + :
Spell CheckF7