Category Archives: Formatting

5 Quick Formatting Tips

Sometimes we don’t have a lot of time to fancy up the presentation of data in an Excel spreadsheet. On these occasions, try these 5 quick formatting tips.

  1. Change column widths and row heights to provide extra visual white space instead of inserting blank columns or rows.
  2. Set alignment of each column appropriately for the data it contains.
  3. Use Word Wrap or Alt-Enter to make text spread across multiple lines in individual cells.
  4. Bold and increase font size of title. Merge and Center across multiple cells.
  5. Use borders and shading to further define columns and header information.

Take a look at the difference these 5 tips make:

BEFORE:Quick Formatting
AFTER:Quick Formatting

Get more formatting ideas here. If you found this post helpful, you may subscribe to receive more great tips, tricks, and templates by email.

Shrinking a Spreadsheet to Fit on One Page in Excel

Fit_One_Page_Excel_a
Photo: David Castor via Wikimedia Commons

Ever wish you could shrink your car when faced with parallel parking in a really tight space? That’s how I feel when a spreadsheet is just slightly bigger than will fit on one page. The usual solutions are choosing larger paper (e.g., 11″ x 17″), making the page margins smaller or shrinking the size of the columns or rows. That can be a lot of trial and error, making a change, checking Page Preview, tweaking a little more, checking again (not unlike inching the car forward, reverse, forward, reverse).

The faster solution is to use Scaling in the Print menu.

1. Click File on the Menu bar, select Print, and then Scaling under Settings. The default is No Scaling.

Fit_One_Page_Excel_1

2. Click on the dialog launcher (small down arrow) and 3 other options appear.

Fit_One_Page_Excel_2

Fit Sheet on One Page will take all of the selected print area or the whole sheet and shrink everything horizontally and vertically to fit on one page.

Fit All Columns on One Page will shrink the selection horizontally so that all columns fit on a page, but rows will print on as many pages as needed.

Fit All Rows on One Page will shrink the selection vertically so that all rows fit on one page but if the columns do not fit on one page, they will flow to additional pages.

The Print Preview should adjust as you choose different options.

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.