Anatomy of Excel formatting: Part 4

by Scott Lowe MCSE | Oct 10, 2005 7:26:00 PM

Tags: PRODUCTIVITY, Microsoft Office, Scott Lowe MCSE, spreadsheet, Microsoft Excel, cell

Takeaway: Sometimes we take for granted what users know. While Microsoft Excel may be familiar to you, if you are on a help desk you will have to explain how the application works to users that fall within a vast range of skill level and knowledge. This series of Excel tutorials can make that training much simpler. This installment of the series shows you how to handle conditional cell formatting.

In this final part in the Formatting Excel articles, you will learn how to format the overall printed spreadsheet. You will also learn to automatically apply formatting to cells in your spreadsheet based on their contents. For example, if you enter a negative number into a cell, you can format that cell with a dark border so that it is more pronounced.

The formatting series

Part 1

• Boldface, italicize and underline cell content
• Change the size and font of your text
• Apply a default Excel style (i.e. dollar, percent, etc) to cells
• Use date and time formatting in your spreadsheet
• Apply shading

Part 2

• Apply borders
• Resize rows
• Resize columns

Part 3

• Text formatting
• Justify cell contents
• Change the direction of the text in your spreadsheet
• Word wrap text

Part 4

• Automatically format cells based on their contents
• Change the margins for your printed page
• Add a header and footer to your printer spreadsheet

Spreadsheet page formatting

In the previous articles, I've provided you with information about formatting your spreadsheet to make it look better and provide clearer information to your audience. However, you can do even more to the overall page to provide your audience with other important information, including the exact date and time a spreadsheet was printed, and also automatically adds column and row headings to every page in a multi-page spreadsheet.

Just like any other Microsoft office program, Excel lets you change the way your spreadsheet acts when it prints. The usual suspects, including changing the page margins and the orientation (landscape vs. portrait), are present, but Excel also provides other page formatting options. For example, you can ask Excel to center your spreadsheet on the printed page—horizontally, vertically, or both. You can also combine multiple pages onto a single page by telling Excel to automatically fit any number of pages to a single sheet. This can result in unreadable information since Excel shrinks the size of the text, but can be useful.

All of these settings are accessible from the menu at File | Page Setup, shown in Figure A.

Figure A

Page setup

Many of these settings are self-explanatory. I'll go over many of them, but especially the ones that are less obvious.

Page orientation

This one is easy and the Page Setup dialog box even shows you how your page will print. Choose Portrait for a normal print. Choose Landscape to print your page sideways, which, for a spreadsheet, is often useful in helping you get more columns on a single page.

Scaling

Sometimes, you put together a spreadsheet that is just a little bigger than a single page. In these cases, you might not want to waste paper, or inconvenience your reader, by printing your work on multiple pages. This is where Excel's scaling feature comes in handy. Scaling just means that your printed sheet will be rendered a little larger or smaller by a certain factor, before it is printed. For example, if you scale your work to 50% of normal size, it will print in half the space and the information will be half as large.

Excel provides two scaling options: Adjust and Fit. When you choose Adjust, you change the width and height of your work by the specified percentage. If you choose an adjustment percentage less than 100, your spreadsheet becomes smaller, but more of it fits on each page; choosing a factor over 100% results in larger text, and less information on the page.

Fit provides an absolute way to make sure your work prints on a specific number of pages. For example, if you have a column that is moving off the page to the right, but you want your work to fit across on one page, tell Excel that you want the work to fit to 1 page wide and leave the "tall" field blank. This will let Excel print all of your columns across each page, but use as many pages as necessary to get the information printed.

Paper size

This is another of the mostly self-explanatory choices. This is where you get to pick your paper size—letter, legal, 11x17, envelopes, etc. Make sure the option you pick is supported by your printer.

First page number

By default, Excel puts "Auto" into this option, meaning that, if you put page numbers on your printed page (explained later), the page number will start at "1". If you want to start page numbering with a different number, enter that number into this field.

The Margins tab

The Margins tab has quite a few options (shown in Figure B), but is very straightforward to use.

Figure B

You can, surprisingly enough, change your page margins on this tab.

Your page margin selections define how much space you want around the outside edges of the page. New Excel worksheets have margins of 1" at the top and bottom of the page, with margins of ?" on the edges.

The Header and Footer options on this page define the distance between the header and the top of the page. Make sure that this distance is smaller than your top and bottom margins. If you don't, you run the risk of your header overlapping your printed data. See Figure C for an example of what could happen when you use a header and the top margin and header margins are the same.

Figure C

Notice that the page heading overwrites some of the spreadsheet text.

See Figure D for some examples of what happens when you change different margins.

Figure D

Margins

As you adjust your margins the area available for printable text changes. In the middle example, the top margin is 2", while in the last example, the left and right margins are each 1.5".

Adding headers and footers

Spreadsheet data, especially things like financial forecasts and inventory number, are notorious for changing very quickly. Nothing is more frustrating than picking up two similar, undated spreadsheets and trying to figure out which one has the latest information. An easy way to alleviate your users of this frustration is to print a header or footer on each page listing the date and time the work was printed. Add or change your own headers and footers by going to File | Page Setup | Header/Footer.

Figure E

Dialog header

Change your header and footer to match your needs. I've included a sample custom header and footer in this example. There are two other samples here that give you a glimpse of Excel's default headers and footers.

Changing your headers and footers is easy, especially if you just want one of the defaults provided by Excel. Among these defaults are:

• The page number
• The worksheet number or name
• The file name of the workbook
• The current date
• The name person who created the work
• Any combination of the above

As an example of how the headers and footers work, take a look at Figure F, which is a printed version of the sample inventory spreadsheet with the header and footer I used in the top picture in Figure E.

Figure F

Header printed

The date and time in the footer will always be the exact date and time that the work was printed.

You don't have to use Excel's built-in options, though. You can build any header and footer you like for your own work by clicking the "Custom Header" or "Custom Footer" button. (Figure G)I'll go over the Footer option here, but be aware that the procedure for creating a custom header is the same.

Figure G

Just about anything you want can live in your custom footer.

The custom footer dialog box has three sections, allowing you to place footer information in the lower left corner, the lower right corner, and right in the center of the bottom of your work. If you like, you can simply click one of the areas and start typing; whatever you enter will appear on the printed sheet.

To make it easy for you to add elements such as page numbers and the current date and time, use the buttons in the center of the window. Table A below outlines the function of each button.

Table A

Elements	Description
The letter "A"	Opens the Font dialog box, allowing you to change the typeface, size, and other elements of your text.
The number sign (#)	Inserts the text "&[Page]" into the active section. When you print your work, this text is replaced by the page number for each sheet that prints.
Two plus signs (+ +)	Inserts the text "&[Pages]" into the active section. When you print your work, this text is replaced by the total number of pages that are to be printed. Using this, you can enter text such as "Page 1 of 30". If you wanted this in the center section of your printed work, it would appear as "Page &[Page] of &[Pages]".
Little calendar	Inserts the text "&[Date]" into the active section. When you print your work, this text is replaced by current date.
Clock	Inserts the text "&[Time]" into the active section. When you print your work, this text is replaced by the current time.
File folder	Inserts the text "&[Path]&[File]" into the active section. When you print your work, this text is replaced by the full path (the folder) and the file name you used to save the spreadsheet to your computer. (i.e. "C:\My Documents\Excel.xls")
Excel icon	Inserts the text "&[File]" into the active section. When you print your work, this text is replaced by the file name you used to save the spreadsheet to your computer. (i.e. "Excel.xls")
Index card	Inserts the text "&[Tab]" into the active section. When you print your work, this text is replaced by the name of the sheet in your workbook. (i.e. "Sheet1")
Mountains	Allows you to insert a picture into your footer.
Paint can	This button is only available if you put a picture into your footer, and it opens the Format Picture window, allowing you to make adjustments to how the picture looks.

Note that, when you use these items, Excel does not provide a caption. For example, if you use the &[Tab] option, Excel will print just "Sheet1", not "Sheet: Sheet1". If you want a heading to appear, you need to type it in.

Add row and column headings to every printed page

The final tab (Figure H) in the Page Setup dialog box—Sheet—provides you with a number of more advanced print options.

Figure H

Use the options on the Sheet tab to control what prints on your page.

Again, I'll go through each option.

Print area: Sometimes, you only want to print part of your spreadsheet. Maybe you're putting together a cost estimate for a customer, for example, and you don't want him to see your markup figures. Excel makes censoring this information simple—just don't include it on the printed page. In the Print Area box, type in the cell range you want to print. You don't have to manually enter the information, though. You can also click the icon to the right-hand side of the box and use the mouse to select the cells you want to print. In the example I've been using in this series, if I limit the print area to A1 to C4, I get the image shown in Figure I. Note that all of the information outside this range is now missing. Also look at Figure J, which provides you with a look at a worksheet that has a print area assigned. Note the dashed line at the edge of the print area.

Figure I

The print area is limited to cells A1 to C4. I still have the header printing, too.

Figure J

The dashed lines give you a visual cue that you have a print area assigned.

Print titles: Often, you have column and/or row headings that make it easier for your audience to keep track of what they're reading. How often have you looked at a multi-page table of information and had to flip back to the first page to find out what the information in one of the columns means? Excel makes it really easy for you to include column (and row) headings on all of your pages, without having to manually enter them at the right places.

If, for example, you want to repeat the column headings shown in the shot in Figure I, you need to repeat the first row of the spreadsheet on every page. Again, you can either enter information manually, or use the icon at the right of the titles choices to use the mouse to choose a column or row. The format is similar to a cell reference, but only uses one component of the cell address.

For example, if you want to repeat the first row on every page, in the "Rows to repeat at top" box, you would enter "$1:$1", which is an absolute row reference without a column reference. You could include multiple rows at the top of each page, if you like (i.e. "$1:$4 would print the first four rows at the top of every printed page). Column titles work the same way. If you want to include the first column of your work on every page, into the "Columns to repeat at left" box, enter "$A:$A".

Figure K shows you what happens when you tell Excel to include a row at the top of each page.

Figure K

Column headings

Note that the column headings on these two pages are identical, but I did not have to type them in manually.

Gridlines: If you check this box, Excel will print all of the borders for every cell. This can be useful when you need your reader to be able to easily follow information both across and up and down the page.

Figure L

Gridlines put a whole lot of boxes and lines on your page!

"Black and white" and "Draft quality": These two options define how you want your sheet to look on the printer and I can't really provide examples for you to see. Choose the Black and White option if you never plan to print your work in color, and the Draft option to print your work with reduced quality, which will make it print faster and might save some of your printer toner. Don't use draft for your final product, though!

Row and column headings: I showed you how to include repeated rows and columns on each page, but this is a little different in that it prints the Excel row and column headings using just numbers and letters. Take a look at the sample in Figure M.

Figure M

Row and column headings

This can be useful if you're working on a spreadsheet collaboratively and need to be able to quickly identify cells in your discussions.

Page order: Changes the direction that Excel prints your work. See Figure N for an example since, in this case, a picture really is worth a whole lot of words.

Figure N

Page order

When you change the page order, bear in mind that your page numbers maybe change, too. If you print "Down, then over" page 1 will be in the upper left and page 2 immediately below. For "Over, then down" printing, page 1 is still in the upper left, but page 2 is to the right.

Conditional formatting

I saved this topic for last. It hearkens back to cell formatting, but is a unique, and sometimes incredibly useful, way to format the contents of your spreadsheet. It solves something that can be a problem: The need to manually format individual cells that meet certain criteria. For example, suppose in a budget spreadsheet you could have Excel in some way highlight a budget line that is within 5% of the annual limit, giving you an easy visual cue that you need to do something. This feature is called Conditional Formatting, and is a fairly recent addition to Excel.

The best way to show you how useful conditional formatting can be is to show you an example. In Figure O, I've provided an excerpt from a much larger budget spreadsheet. Suppose that the real spreadsheet is thousands of lines for a large organization.

Figure O

This is just a part of an imaginary budget spreadsheet with thousands of entries

Now, suppose that, as the overall budget manager, you want to get a quick visual look at potential problem areas for the rest of the budget year. As such, you would like visual cues for the following:

• A budget line that is within 5% of being depleted. (Make text bold orange)
• A budget line that is depleted, but within 20%. (Make text bold red)
• A budget line that is depleted, and over by more than 20%. (Make text bold white with red background)

On the sample spreadsheet, this is easy since there are only a few lines, but, keep in mind, for much larger sheets, scanning the Balance column could result in missing a key number.

To accomplish this goal, go to Format | Conditional Formatting, which opens the dialog box shown in Figure P.

Figure P

The Conditional Formatting dialog box

In this dialog box are a number of options that allow you to format your cells based on their contents. Basically, this gives you dynamic formatting rather than the static formatting we've discussed to this point. At the top of the window, note the text "Condition 1". You can have up to three conditions that apply different formats, depending on the cell contents. In this example, we'll have the three different conditions I indicated earlier.

The first pull down box has two options:

• Cell Value Is: Evaluate a cell based on its contents. For example, you can tell Excel that you want only cells between two numbers. You can use formulas to determine these thresholds. For example, you can have a condition that says "For any cell between 0 and 5% of the contents of cell B4". I'll demonstrate this in a bit.
• Formulas Is: You can also use a formula to dictate the condition. For example, you could have a condition that says "If the formula in the cell is '=NOW()', make the result boldface."

If you select the Cell Value Is option, the next drop down asks for the condition to meet. About any condition you would need is present, as shown in Figure Q.

Figure Q

These are the conditions provided by Excel for Conditional Formatting.

If you choose an option that needs two numbers, such as "between", two boxes are made available in the window. For other options requiring only a single number, only a single box is made available.

Into the box (or boxes), enter the conditions you want to meet. For example, you might say that you want the cell value to be between 0 and 100. You can also use cell references in these boxes. For example, you could say that you want a value to be between A1 and D4. If A1 has 50 in the cell, and D4 has 100, that would make your condition "between 50 and 100".

After you choose your condition, choose the formatting you want to apply when the condition is true. For example, you might make the text a different size or color, or make it bold or italics, or you might put a border around the cell, or add shading. Click the Format button to make your formatting selections. This button opens up a limited version of the Format Cells dialog box that has been discussed in this series.

Figure R

You've seen a similar Format Cells window quite often in this series.

If you want to add another condition, click the Add button. You'll see this in action soon. To remove a condition, click the Delete button. When you're done applying conditions and formatting, click OK.

Now, for the example at hand; the conditions are shown in Figure S.

Figure S

Conditional

This example uses three conditions to achieve its goals. Note that this screenshot was taken when the active cell was D4 from the sheet shown earlier in Figure O, which is why it references cell B4. The same formatting is applied to all of the cells in column D, but the values reference the right cells. For example, in cell D9, the conditions reference cell B9.

• Condition 1: Basically says that, if the cell value is between 0 and 5% of the original budget amount, the cell text should be changed to orange.
• Condition 2: Says that, if the cell value is between negative 20% of the original value and 0 (up to 20% over budget) the text should be made red.
• Condition 3: Says that, if the cell value is more than 20% over budget, the text should be made white and the cell background should be red.

After setting up these three conditions, I used the "Format Painter" tool to copy the formatting to the other cells in column D. To use the Format Painter, click the cell with the formatting you want to copy (in this case, I applied the conditional formatting rule to cell D4, so I clicked that cell), then click the Format Painter button (it's the button with the icon of the paintbrush). Next, select the rest of the cells to which you want to apply this formatting (in this example, I selected cells D5 to D13). The resulting spreadsheet is shown in Figure T.

Figure T

Note that three cells now very much stand out and are quickly identifiable.

Now, as you change values in your spreadsheet, the formatting in the balance column will change automatically as conditions change. For example, suppose you update your budget after doing more spending. Let's assume that you spent another $2,500 on overtime, $100 more in paper, $1,700 more on supplies, and $2,000 more on maintenance. To get the result shown in Figure U, I didn't have to make any formatting changes at all. I just changed the figured in the YTD spent column. Since the Balance column has formulas that calculate the contents of each cell, the conditional formatting changed automatically based on the new information.

Figure U

Note the changes to the formatting in the Balance column.

More to come

This concludes the Excel formatting portion of this series. Coming soon in the Excel series are articles on graphing, pivot tables, and much, much more!

Print/View all Posts Comments on this article

Download mahansench@... | 10/11/05

null beisenhamer | 10/11/05

Download Not Working cheryl.magnuson@... | 10/12/05

Excel Part 4 trish3333@... | 10/12/05

Download Missing PSU-Curt | 10/12/05

Download Inoperative ricarditosanchez@... | 10/12/05

Part 4 of 3 parts? deadhead '59 | 10/13/05

Download Working TerryLBradshaw@... | 10/13/05

Download now working !!!! mahansench@... | 10/13/05

Not Working TerryLBradshaw@... | 10/12/05

download not working dhays | 10/12/05

Downloading the article slawo@... | 10/13/05