21. Formatting Worksheets
Formatting adds interest and readability to documents. If you have taken time to create a spreadsheet, you should also take the time to make sure that it is eye catching and readable.
You can format documents in Excel 2016 with any of these three methods:
Use tables styles—You can use table styles to format a table with banded rows, accents for totals, and so on.
Use cell styles—You can use cell styles to identify titles, headings, and accent cells. The advantage of using cell styles is that you can quickly apply new themes to change the look and feel of a document.
Use formatting commands—You can use traditional formatting commands to change the font, borders, fill, numeric formatting, column widths, and row heights. The usual formatting icons are on the Home tab as well as in the Format Cells dialog.
Why Format Worksheets?
You can open a blank worksheet and fill it with data without ever touching any of Excel’s formatting commands. The result is functional, but not necessarily readable or eye catching. Figure 21.1 contains an unformatted report in Excel.
Figure 21.2 contains the same data but with formatting applied. The formatted report in Figure 21.2 is more interesting and easier to read than the unformatted one for the following reasons:
The reader can instantly focus on the totals for each line.
Headings are aligned with the data.
Borders break the data into sections.
Accent colors highlight the subtotals and totals.
The title is prominent, in a larger font, and a headline typeface is used.
Numeric formatting has removed the extra decimal places and added thousands separators.
Quarterly totals appear in italic.
The column widths are adjusted properly.
A short row adds a visual break between the product lines.
Headings for each product line are rotated, merged, and centered.
The formatting applied to Figure 21.2 takes a few extra minutes, but it dramatically increases the readability of the report. Because you have taken the time to put the worksheet together, it is worth a couple of extra minutes to make the worksheet easier for the consumer to read.
Using Traditional Formatting
Formatting is typically carried out in the Format Cells dialog or using the formatting icons located on the Home tab.
In Excel 2016, the traditional formatting icons are in the Font, Alignment, and Number groups on the Home tab, as shown in Figure 21.3. Additional column- and row-formatting commands are available in the Format drop-down in the Cells group on the Home tab.
Figure 21.3 Most icons from the former Formatting toolbar are in the Font, Alignment, and Number groups on the Home tab.
If your favorite setting is not on the Home tab, you can take one of the four entry paths to the Format Cells dialog, which provides access to additional settings, such as Shrink to Fit, Strikethrough, and more border settings:
Press Ctrl+1, which is Ctrl and the number 1. You can press Ctrl+Shift+F to display the Font tab on the same dialog.
Click the dialog launcher icons in the lower-right corner of the Font, Alignment, or Number groups. Each icon opens the dialog, with the focus on a different tab.
Right-click any cell and select Format Cells.
Select Format Cells from the Format drop-down on the Home tab.
As shown in Figure 21.4, the Format Cells dialog includes the following six tabs:
Number—Gives you absolute control over numeric formatting. You can choose from 96,885 built-in formats or use the Custom category to create your own.
Alignment—Offers settings for horizontal alignment, vertical alignment, rotation, wrap, merge, and shrinking to fit.
Font—Controls font, size, style, underline, color, strikethrough, superscript, and subscript.
Border—Controls line style and color for each of the four borders and the diagonals on each cell.
Fill—Offers 16 million fill colors and patterns.
Protection—Used to lock or unlock cells.
Figure 21.4 The Format Cells dialog offers complete control over cell formatting. You can visit this dialog when the icons on the ribbon do not provide enough detail.
Changing Numeric Formats by Using the Home Tab
If you ever shop for hardware at a general-purpose store, you have probably experienced how it can have almost what you need, but never exactly what you need. At this point, you probably curse your decision to stop at the general-purpose retailer and drive another mile down the road to Home Depot or Lowe’s, where you can always find exactly what you need.
Using the Number group on the Home tab is like shopping at a general-purpose retailer. It has many settings for numeric formatting, but often they are not exactly what you need. When this happens, you end up visiting the Number tab on the Format Cells dialog.
To start, there are three icons—for currency, percentage, and comma style. The Percentage icon is useful. Unfortunately, the Currency and Comma icons apply an Accounting style to a cell, and the Accounting style is inappropriate for everyone except accountants. Furthermore, these three icons are not toggle buttons, which means that when you use one of them, there is not an icon to go back quickly to a general style, other than Undo.
Tip
Excel uses the value in the active cell for each of the formats inside the drop-down, and no sample if the cell is blank.
The Increase and Decrease Decimal icons are useful. Each click of one of these buttons forces Excel to show one more or one fewer decimal place. If you have numbers showing two decimal places in all cells, two clicks on the Decrease Decimal icon solves the problem.
Figure 21.5 shows the Currency, Percentage, Comma, Increase Decimal, and Decrease Decimal buttons in the Number group of the Home tab. The Currency button offers a drop-down with choices based on your regional settings.
Figure 21.5 The Currency and Comma icons both use an Accounting style. This is wonderful for accountants, but others should resist using them.
Above the five buttons in the Number group is a drop-down that has a dozen popular number styles. Figure 21.6 shows the styles in the drop-down. The range A2:F12 shows these styles applied to four numbers.
The following list provides some comments and cautions about using the number styles from the drop-down in the Home tab:
General format is a number format. Decimal places are shown if needed. No thousands separator is used. A negative number is shown with a minus sign before the number.
Number does not use a thousands separator. It forces two decimal places, even with numbers that do not need decimal places, such as in cell F3.
Currency is a useful format for everyone. The currency symbol is shown immediately before the number. All numbers are expressed with two decimal places. Negatives are shown with a hyphen before the number.
Accounting is great for financial statements and annoying for everything else. Negative numbers are shown in parentheses. Currency symbols are left-aligned with the edge of the cell. Positive numbers appear one character from the right edge of the cell to allow them to line up with negative numbers.
Percentage uses two decimal places when selected from the drop-down. This is one format for which it is actually better to use the icon on the ribbon than the Format Cells dialog.
Fraction defaults to showing a fraction with a one-digit divisor. If you have a number such as 0.925, some Excel number formats correctly show this as 15/16. Unfortunately, the Fraction setting in this drop-down rounds it to one-digit divisors.
Changing Numeric Formats by Using Built-in Formats in the Format Cells Dialog
The Format Cells dialog offers more number formats than the Home tab. My favorite number format can be accessed only through the Format Cells dialog. I find that I avoid the buttons in the Number group in the Home tab and go directly to the Format Cells dialog.
You can display the Format Cells dialog by clicking the dialog launcher icon in the lower-right corner of the Number group of the Home tab. When you open the Format Cells dialog this way, the Number tab is the active tab.
Twelve categories appear on the left side of the Number tab. The General and Text categories each have a single setting. The Custom category enables you to use formatting codes to build any number format. The remaining nine categories each offer a collection of controls to customize the numeric format.
Using Numeric Formatting with Thousands Separators
Using numeric formatting with thousands separators is my favorite format. The thousands separators make the number easy to read. You can suppress the decimal places from the numbers. Microsoft does not offer buttons on the Home tab to select this format. The comma button is a perfect place for it, but instead Microsoft assigns that to the accounting format.
To format cells in numeric format, follow these steps:
1. Press Ctrl+1 to display the Format Cells dialog.
2. Select the Number category from the Number tab.
3. Select the Use 1000 Separator check box.
4. Optionally, adjust the Decimal Places spin button to 0.
5. Optionally, select a method for displaying negative numbers.
Figure 21.7 shows the Number category of the Format Cells dialog.
Displaying Currency
Two categories are used for currency: Currency and Accounting. The Currency category is identical to the Number category shown in Figure 21.7, with the addition of a currency symbol drop-down. This drop-down offers 409 different currencies from around the world.
The second category is Accounting. With this category, the currency symbol is always left aligned in the cell. The last digit of positive numbers appears one character from the right edge of the cell so that positive and negative numbers line up. In addition, negative numbers are always shown in parentheses.
Displaying Dates and Times
The Date category offers 17 built-in formats for displaying dates, and the Time category offers nine built-in formats for displaying time. Each category has two formats that display both date and time.
The date formats vary from short dates such as 3/14 to long dates such as Wednesday, March 14, 2012. You should pay particular attention to the Date formats and the Sample box. Some formats show only the month and the day. Other formats show the month and the year. For example, the values in the Type box are for March 14, 2012. Other types such as March-01 display month-year. Types such as 14-Mar display day-month.
An interesting format near the bottom of the list is the M type. This displays month names in JFMAMJJASOND style, as shown in Figure 21.8. Readers of the Wall Street Journal’s financial charts will instantly recognize that each month is represented by the first letter of the month in this style. This style works great when used as the labels along the x-axis of a chart.
In the Time category, pay attention to an important distinction between the 1:30 PM, 13:30, and 37:30:55 types. The first type displays times from 12:00 AM through 11:59 PM. The second type displays military time. In this system, midnight is 0:00, and 11:59 PM is 23:59. Neither of these types displays hours in excess of 24 hours. If you are working on a weekly timesheet or any application in which you need to display hours that total to more than 24 hours, you need to use the 37:30:55 type in the Time category. This format is one of a few that displays hours in excess of 24.
Displaying Fractions
The Fractions category rounds a decimal number to the nearest fraction. Types include fractions in halves, quarters, eighths, sixteenths, tenths, and hundredths. In addition, the first three types specify that the decimal should be reduced to the nearest fraction with up to one, two, or three digits in the denominator.
Figure 21.9 shows a variety of decimals formatted with five different fractional types. In row 14, notice that this random number can appear as 1/2, 49/92, or 473/888 when using the Up To N Digit types. Excel rounds the number to the closest fraction.
In column E, note that if you ask Excel to show the number in eighths, Excel uses 4/8 and 2/8 instead of 1/2 or 1/4.
You probably feel as if you spent too much time in junior high math learning how to reduce fractions. The good news is that the first three fraction types of number formatting in Excel eliminate the need for manually reducing fractions.
Displaying ZIP Codes, Telephone Numbers, and Social Security Numbers
Spreadsheets were invented in Cambridge, Massachusetts. However, if you enter the ZIP Code for Cambridge (02138) in a cell, Excel does not display the ZIP Code correctly. It truncates the leading zero, giving you a ZIP Code of 2138.
To combat this problem, Excel provides four special formatting types, all of which are U.S. centric:
The Zip Code and Zip Code + 4 styles ensure that East Coast cities do not lose the leading zeros in their ZIP Codes.
The Phone Number type formats a telephone number with parentheses around the area code and a hyphen after the exchange.
The Social Security Number type groups the digits into groups of three, two, and four numbers that are separated by hyphens.
Figure 21.10 shows cells formatted with the four types available in the Special category.
Figure 21.10 United States customers will appreciate the Special category in the Format Cells dialog.
Note
If you happen to live in one of the 195 countries in the world besides the United States, you will undoubtedly need other formatting for your postal codes, telephone numbers, or national ID numbers. You can create number formats such as the ones shown in the Special category as well as the other formats you might need by using the Custom category, as discussed in the next section.
Changing Numeric Formats Using Custom Formats
Custom number formats provide incredible power and flexibility. Although you do not need to know the complete set of rules for them, you will probably find a couple of custom number formats that work perfectly for you.
Tip
A good way to learn custom number formatting codes is to select a format and then click Custom to see the code for the selected format. For example, click Fraction and then click As Quarters (2/4). When you click Custom, you learn that the custom number code is # ?/4. Using this knowledge, you could build a new custom format code to show data in 17ths: # ?/17.
To use a custom number format, follow these steps:
1. Select the cells to be highlighted.
2. Display the Format Cells dialog by pressing Ctrl+1.
4. Select the Custom category.
5. Type the formatting codes into the Type box. Excel shows you a sample of the active cell with this format in the Sample box.
6. After you make sure this format looks correct, click OK to accept it.
Using the Four Zones of a Custom Number Format
A custom number format can contain up to four different formats, each separated by a semicolon. The semicolons divide the format into as many as four zones. Excel allows different formatting, depending on whether a cell contains a positive number, a negative number, a zero, or text. You need to keep in mind the following:
Separate formatting codes for zones by using semicolons.
If you type only one number format, it applies to all numbers.
If you type only two formats, the first format applies to positive and zero. The second format is used for negative.
If all four formats are used, they refer to positive, negative, zero, and text values, respectively.
In Figure 21.11, a custom number format uses all four zones. The table in rows 11:14 shows how various numbers are displayed in this format. Notice that cell B12 appears in red type.
Figure 21.11 The four zones of a custom number format can cause positive, negative, zero, and text values to display differently.
Controlling Text and Spacing in a Custom Number Format
You can display a mix of text and numbers in a numeric cell by including the text in double quotation marks. For example, "The total is "$#,##0 precedes the number with the text shown in quotes.
If you need a single character, you can omit the quotation marks and precede the character with a backslash (). For example, the code $#,##0,,M displays numbers in millions and adds an M indicator after the number. The letters BDEGHMNSY require a backslash. The rest of the letters can be used without a backslash.
Some characters require neither a backslash nor quotation marks. These special characters are $ - + / ( ) : ! ^ & ' ~ { } = < > and the space character.
To add a specific amount of space to a format, you enter an underscore followed by a character. Excel then includes enough space to include that particular character. One frequent use for this is to include _) at the end of a positive number to leave enough space for a closing parenthesis. The positive numbers then line up with the negative numbers shown in parentheses.
To fill the space in a cell with a repeating character, use an asterisk followed by the character. For example, the format **0 fills the leading space in a cell with asterisks. The format 0*- fills the trailing space in a cell with hyphens.
If you are expecting numbers but think you might occasionally have text in the cell, you can use the fourth zone of the format. You use the @ character to represent the text in the cell. For example, 0;0;0;"Unexpected entry of "@ highlights the text cells with a note. If someone types a number, she gets the number. If someone types hello, she gets “Unexpected entry of hello”.
Controlling Decimal Places in a Custom Number Format
Use a zero as a placeholder when you want to force the place to be included. For example, 0.000 formats all numbers with three decimal places. If the number has more than three places, it is rounded to three decimal places.
Use a pound sign (#) as a placeholder to display significant digits but not insignificant zeros. For example, 0.### displays up to three decimal places, if needed, but can display "1." for a whole number.
Use a question mark to replace insignificant zeros on either size of the decimal point with enough space to represent a digit in a fixed-width font. This format was designed to allow decimal points to line up, but with proportional fonts, it may not always work.
To include a thousands separator, include a comma to the left of the decimal point. For example, #,##0 displays a thousands separator.
To scale a number by thousands, include a comma after the numeric portion of the format. Each comma divides the number by a thousand. For example, 0, displays numbers in thousands, and 0,, displays numbers in millions.
Using Conditions and Color in a Custom Number Format
The condition codes available in numeric formatting predate conditional formatting by a decade. You should consider the flexible conditional formatting features for any new conditions. However, in case you encounter an old worksheet with these codes, it is valid to use colors in the format: red, blue, green, yellow, cyan, black, white, magenta, Color 1, ..., Color 56. You include the color in square brackets. It should be the first element of any numeric formatting zone.
You can include a condition in square brackets after the color but before the numeric formatting. For example, [Red][<=100];[Color 17][>100] displays numbers under 100 in red and other numbers in blue. The United States telephone special format uses this custom condition:
[<=9999999]###-####;(###) ###-####
Using Dates and Times in a Custom Number Format
Although many of these settings are arcane, I still regularly use many of the date and time formats shown in Table 21.1. The various m and d codes allow flexibility in expressing dates.
The custom number format m/d/yy or m/d/y displays the month and day numbers as one digit if possible. For example, dates formatted with this code display as 1/9/08, 1/31/08, 9/9/09, and 12/31/08. Note that you cannot display the year as a single digit.
A custom number format of mm/dd/yy always uses two digits to display the month and day. Examples are 01/09/08 and 01/31/08.
The remaining date and time codes can display months as Jan, January, or J and days as 1, 01, Fri, or Friday.
Note
Note that the letter m can be used either as a month or as a minute. If the m is preceded by an h or followed by an s, Excel assumes you are referring to minutes. Otherwise, the month is displayed instead.
Displaying Scientific Notation in Custom Number Formats
To display numbers in scientific format, you use E- or E+ exponent codes in a zone.
If a format contains a zero (0) or pound sign (#) to the right of an exponent code, Excel displays the number in scientific format and inserts an E. The number of zeros or pound signs to the right of a code determines the number of digits in the exponent. E- or e- places a minus sign by negative exponents. E+ or e+ places a minus sign by negative exponents and a plus sign by positive exponents.
Take the following, for example:
1450 formatted with 0.00E+00 displays as 1.45E+03.
1450 formatted with 0.00E-00 displays as 1.45E03.
0.00145 formatted with either code displays as 1.45E-03.
Aligning Cells
Worksheets look best when the headings above a column are aligned with the data in the column. Excel’s default behavior is to left align text and right align values and dates.
Note
The Alignment tab of the Format Cells dialog offers additional alignment choices, such as justified and distributed.
In Figure 21.12, the month heading in F1 is left aligned, and the numeric values starting in row 2 are right aligned. This makes the worksheet look haphazard. To solve the problem, you can right align the headings cells.
Figure 21.12 In column F, the left-aligned heading appears out of alignment with the numbers. Columns G and H show the headings after the Right Align icon is clicked.
To right align cells, select the cells and click the Right Align icon in the Alignment group of the Home tab.
Changing Font Size
There are three icons in the Font group of the Home tab for changing font size:
The Increase Font Size A icon increases the font size in the selected cells to the next larger setting.
The Decrease Font Size A icon decreases the font size in the selected cells to the next smaller setting.
The Font Size drop-down offers a list of font sizes. You can hover over any font size to see the Live Preview of that size in the selected cells of the worksheet (see Figure 21.13).
Figure 21.13 When you use the Font Size drop-down, Live Preview shows you the effect of an increased font size before you select the font.
Note
If you need a font size that is not in the drop-down, you can type a new value in the drop-down. For example, although the drop-down jumps from 12 to 14, you can click the value and type 13.
Changing Font Typeface
Since Excel 2007, changing the font typeface has been vastly improved over earlier versions of Excel. In some legacy versions of Excel, the Font drop-down showed the font names in the style of each font. However, beginning with Excel 2007, Live Preview shows how the font will look as you hover over the font in the selected cells (see Figure 21.14). Notice that the Font name drop-down is in the Font group of the Home tab.
Figure 21.14 The Font drop-down in the Home tab shows the look of each font, and Live Preview shows how individual cells will look with the font applied.
Note
By using the Font tab of the Format Cells dialog, you can also apply strikethrough, superscript, and subscript.
Applying Bold, Italic, and Underline
Three icons in the Font group in the Home tab enable you to change the font to apply bold, italic, and underline. Unlike the icons in the Number group, these icons behave properly, toggling the property on and off. The Bold icon is a bold letter B. The Italic icon is an italic letter I. The Underline icon is either an underlined U or a double-underlined D. The Underline icon is actually a drop-down. As shown in Figure 21.15, you can select the drop-down to change from Single Underline to Double Underline.
Figure 21.15 The underline drop-down offers single or double underlining, but the extra choices in the Format Cells dialog solve some text underlining issues.
The underline style applies to the characters in the cell. If you have a cell that contains 123, the underline is three characters wide. If you have a cell with 1,234,567.89, the underline is 12 characters wide.
The Format Cells dialog offers more choices. Settings for Single Accounting underline create an underline that extends nearly to the edges of the cell, but leaves a gap between the underline in the next cell. This often looks better than using a bottom border across the cells.
Using Borders
There are 1.7 billion unique combinations of borders for any four-cell range. The Borders drop-down in the Font group of the Home tab offers 13 popular border options plus five border tools. If you have to draw nonstandard borders, explore the Draw Borders tool in this drop-down.
You must understand an important concept when applying borders to a range. Suppose you select 20 rows by 20 columns, such as cells A1:T20. If you apply a top border by using the drop-down, only the top row of cells A1:T1 have the border. Often, this is not what you were expecting. For example, you might have wanted a border on the top of all 400 cells.
Notice that in the Format Cells dialog, there is a representation of a 2×2 cell range. The border style drawn in the top edge of this box affects only the top edge of the range. The border style drawn in the middle horizontal line of the box affects all the horizontal borders on the inside of the selected range.
The fastest way to select all horizontal and vertical borders in the range is to click the Outline button and then the Inside button in the Presets section of the dialog.
Coloring Cells
Excel allows you to use a gradient to fill a cell. This can provide an interesting look for a title cell. Gradient formatting is available only in the Format Cells dialog.
The Font group on the Home tab offers a paint bucket drop-down and an A drop-down. The paint bucket is a color chooser for the background fill of the cell. The A drop-down is a color chooser for the font color in the cell. Both drop-downs offer six shades of the 10 theme colors, 10 standard colors, and the More Colors option. The paint bucket drop-down also offers the menu choice No Fill, as shown in Figure 21.16.
Figure 21.16 The paint bucket drop-down offers theme colors, 10 standard colors, and the link More Colors.
The More Colors link offers the two-tabbed Colors dialog. You can either choose a color from the Standard tab or enter an RGB value on the Custom tab.
The ability to use a two-color gradient in a cell was a new feature beginning with Excel 2007. To activate this feature, follow these steps:
1. Select one or more cells. If you select a range of cells, Excel repeats the gradient for each cell in the range.
2. Press Ctrl+1 to display the Format Cells dialog.
3. Select the Fill tab.
4. Click the Fill Effects button.
5. In the Color 1 and Color 2 drop-downs, choose two colors or choose one color and white.
6. In the Shading Styles section, choose a shading style.
7. In the Variants section, choose one of the three variations. A sample is shown in the Sample box.
8. Click OK to close the Fill Effects dialog.
9. Click OK to close the Format Cells dialog.
Figure 21.17 shows the Fill Effects dialog. Cell A1 contains a vertical shading, from left to right. Cell A4 shows the opposite variant of vertical shading. Cell A9 shows the from-the-center variant of the vertical shading. Cell A13 shows a diagonal-down shading style.
In all versions except Excel 2007, pattern fills are available. Use the Pattern Color and Pattern Style drop-downs in the Fill tab of the Format Cells dialog to add a pattern shading to a cell. A15 of Figure 21.17 shows a pattern.
Adjusting Column Widths and Row Heights
You can adjust the width of every column in a worksheet. In many cases, narrowing the columns to reduce wasted space can allow a report to fit on one page.
Most tasks in Excel can be accomplished in three or more ways. In most cases, I have a favorite method to perform any task and use that method exclusively. However, setting column widths and row heights is a task where I actively use many methods, depending on the circumstances.
You can use the following seven methods to adjust column width (each method applies equally well to adjusting row heights):
Click the border between the column headings—As shown in Figure 21.18, you can drag to the left to make the column narrower. You can drag to the right to make the column wider. A ToolTip appears, showing the width in points and pixels. The advantage of this method is that you can drag until the column feels like it is the right width. The disadvantage is that this method fixes one column at a time.
Figure 21.18 The right border between one cell letter and the next is the key to adjusting column widths.
Double-click the border between column headings—Excel automatically adjusts the left column to fit the widest value in the column. The advantage of this method is that the column is exactly wide enough for the contents. The disadvantage is that a very long title in cell A1, for example, makes this method ineffective. You might have been planning to allow the title in cell A1 to spill over to B1, C1, and D1. However, the double-click method makes the column wide enough for the long title. In this case, you want to use the last method in this list.
Select many columns and drag the border for one column—When you do this, the width for all columns is adjusted. The advantage of this method is that you can adjust all columns at once, and they are all a uniform width.
Select many columns and double-click one of the borders between column letters—When you do this, all the columns adjust to fit their widest value.
Use the ribbon—Select one or more columns. From the Cells group of the Home tab, select Format, Column Width. Then enter a width in characters and click OK.
Apply one column’s width to other columns—If one column is a suitable width, and you want all other columns to be the same width, you should use this method. Select the column with the correct width and then press Ctrl+C to copy. Next, select the columns to be adjusted. Select the Clipboard section of the Home tab and select Paste, Paste Special, Column Widths. Finally, click OK.
AutoFit a column to all the data below the title rows—If you have a long title in the first few rows and need to AutoFit the column to all the data below the title rows, use this method. Click the first cell in the data range and then press the End key. Next, hold down the Ctrl and Shift keys while pressing the down-arrow key. This selects a contiguous range from the starting cell downward. Now select the Cells section of the Home tab and then select Format, AutoFit Selection. If you were a power user in Excel 2003 or earlier, you might remember this method as Alt+O+C+A. This legacy keyboard shortcut still works.
Using Merge and Center
In general, merged cells are bad. If you have a merged cell in the middle of a data table, you will be unable to sort the data. You will be unable to cut and paste data unless the same cells are merged. However, it is okay to use merged cells as a title to group several columns together.
In Figure 21.19, the Consumer and Professional headings correspond to the columns B:F and G:K, respectively. It is appropriate to center each heading above its columns.
Figure 21.19 Because the row 2 categories are not part of the data table and will never need to be sorted, it is okay to merge and center those cells.
Merging cells brings some negative side effects. Suppose that you had merged B100:G100. You start in cell B1, hold down the Shift key, and start pressing PgDn to select cells in column B. When you reach or pass the merged cell B100, your selection size will automatically expand to be six columns wide because this is the width of the merged cell. To prevent this problem, you might use Center Across Selection, found in the Home tab. This gives the same look as the merged cell, without the problems caused by the merge.
To merge and center cells, follow these steps:
1. Click in the cell that contains the value that is to be centered, and then drag to select the entire range to be merged. In this example, click in cell B2 and drag to cell F2. The result is that B2 is the active cell, and B2:F2 is selected.
2. From the Home tab, select Alignment, Merge & Center, and then select Merge & Center again, as shown in Figure 21.20.
3. Repeat steps 1 and 2 for any other column headings.
4. Optionally, apply an outline border around the merged cells.
Note that after you merge the cells, the entire range becomes one cell. In Figure 21.21, the word Consumer is in an ultra-large cell B2. In this worksheet, cells C2, D2, E2, and F2 no longer exist. If you attempt to use the Go To dialog to move to cell C2, you will be taken to cell B2 instead.
Rotating Text
Vertical text is difficult to read. However, at times space considerations make it advantageous to use vertical text. In Figure 21.22, for example, the names in row 5 are much wider than the values in the rest of the table. If you use Format, AutoFit Selection, the report is too wide.
In the Alignment tab of the Home group, an Orientation drop-down offers five variations of vertical text. Figure 21.23 compares the five available options. Although the Angle options look great, they reduce the column width by only 12%. Vertical Text reduces the column width by 75% but takes far more vertical space. The option Rotate Text Up reduces the column width by 73% and takes up less than half the vertical space of the Vertical Text option.
Note
After you rotate the text, select the Cells section of the Home tab and then select Format, AutoFit Selection again to narrow the columns.
If you need more control over the text orientation, you can select the Alignment option in the drop-down to display the Alignment tab of the Format Cells dialog. This tab allows rotation from 90 degrees to –90 degrees, in 1-degree increments, as shown in the bottom right of Figure 21.23.
Formatting with Styles
Instead of using the settings in the Font group of the Home tab, you can format a report by using the built-in cell styles. Cell styles have been popular in Word for more than a decade. They have been available in legacy versions of Excel, but because they were not given a spot on the Formatting toolbar, few people took advantage of them.
Figure 21.24 shows the styles available when you select Styles, Cell Styles in the Home tab.
An advantage to using cell styles is that you can convert the look and feel of a report by choosing from the themes on the Page Layout tab. Figure 21.25 shows one of the several themes applied to the report.
The Cell Styles gallery offers a menu item to add additional styles to a workbook. Using cell styles provides an interesting alternative to the traditional method of formatting.
Note
You might wonder why Excel 2016 suggests that calculated cells should be in orange font or why Notes should have a yellow background. I spent the first two years of working with Excel 2007 wondering why calculated cells should be orange. However, the better question is, “Why not orange?” When I receive worksheets from others who use this convention, it is easy to understand that they are using the built-in cell styles, which makes it easier to follow the logic of the worksheet. In Figure 21.26, the forecasting model was formatted using Cell Styles from the Data and Model section of the Cell Styles menu. If everyone in your company used these styles, it would be easier to spot the input cells in any model.
Figure 21.26 Adopt the cell styles suggestions for input cells, calculated cells, and so on to make it easier to see the logic in the model.
Understanding Themes
A theme is a collection of colors, fonts, and effects. Office 2016 has 20 built-in themes. If you’ve upgraded your computer from a previous version, you can also download new themes from Office Online or design your own themes.
Tip
The Office theme is the default theme in Excel 2016. In an effort to look modern, Microsoft changed the Office theme starting in Excel 2013. If you had previously embraced themes in Excel 2007 or Excel 2010, you might have become a fan of the old Office theme. The Title cell style in the old Office theme was better than the Title cell style in the new Office theme. Worse, if you open an old document created with Excel 2007 or Excel 2010, the old Office theme will still be available. New workbooks have the new Office theme. It is annoying that they used the same name for two different themes.
Here is how to get the old Office theme back. On the Page Layout tab, open three drop-downs for color, font, and effects. In each drop-down, choose Office 2007–2010 theme. After choosing from those three drop-downs, choose Themes, Save Current Theme. Save the theme with a name such as OfficeReal or aaaOffice. Custom themes appear at the top of the Themes drop-down, so it will be relatively easy to go back to the old theme, even in new workbooks.
Themes are shared in simple XML files, which means they can be propagated throughout a company. A theme has the following components:
Fonts—A theme has two fonts: one for body text and one for titles. The fonts come into play more often in PowerPoint and Word than in Excel. However, styles in Excel also use fonts.
Colors—There are 12 colors: four for text and backgrounds, six accent colors that are used in charts and table accents, and two for hyperlinks. One of the two colors for hyperlinks indicates followed hyperlinks, whereas the other color indicates hyperlinks that have not been followed. The colors shown here appear in the top of the Color Chooser shown previously in Figure 21.16.
Effects—Each theme includes a number of object effects, such as bevel and line style.
Choosing a New Theme
Themes are managed on the Page Layout tab. Listed next are the four drop-downs available in the Themes group:
Themes—Allows you to switch among the built-in themes.
Colors—Allows you to change the color scheme to use the colors from another theme.
Fonts—Allows you to use the fonts from another theme.
Effects—Allows you to use the effects from another theme.
Note
Note that you can use only one theme per workbook. If you are changing the theme on Sheet33, the same changes are made on all the other worksheets in the workbook.
Changing a theme affects charts, tables, SmartArt diagrams, and inserted objects.
To switch to another theme, follow these steps:
1. Arrange your worksheet so that you can see any themed elements, such as tables or charts, on the right side of the screen.
2. From the Page Layout tab, select the Themes drop-down from the Themes group.
3. Hover over the various themes. The worksheet updates to show the new colors, fonts, and effects.
4. When you identify a theme you like, click the theme to apply it to the workbook.
If you are strictly interested in the accent colors, you can select the Colors drop-down from the Themes group to see the accent colors used in each theme. Note that this drop-down offers a grayscale option that is not available in the Themes drop-down.
Creating a New Theme
You might want to develop a special theme, which is fairly easy to do. First, you need to select two fonts and six accent colors. For example, suppose you want to create a theme to match your company’s color scheme. The hardest part is finding six colors to represent your company, because most company logos have two or three colors. Use a tool such as colorschemedesigner.com to find complementary colors for your company colors.
Specifying a Theme’s Colors
To specify new theme colors, follow these steps:
1. Select Page Layout, Themes, Colors, Create New Theme Colors. The Create New Theme Colors dialog appears.
2. To change any accent color, select the drop-down next to Accent 1 through Accent 6. The Color Chooser appears. Select More Colors. Enter the color codes for Red, Green, and Blue. Repeat for the other accent colors.
3. In the Name box, give the theme a name, such as your company name.
4. Click Save to accept the theme.
Specifying a Theme’s Fonts
To specify new theme fonts, follow these steps:
1. Select Page Layout, Themes, Fonts, Create New Theme Fonts.
2. Select a font from the Heading Font drop-down. If a custom font is used in your company’s logo, using it might be appropriate.
3. Select a font from the Body Font drop-down. This should be a font that is easy to read. Avoid stylized fonts for body copy.
4. Give the theme a name. It is okay to reuse the same name from the color theme.
5. Click Save to accept the theme changes.
Tip
In June 2009, famed font designer Erik Spiekermann released the Axel font family, which he designed specifically for showing tables of numbers in Microsoft Excel. You can purchase Axel from https://www.fontshop.com/families/axel.
Reusing Another Theme’s Effects
There is no dialog to choose the effects associated with a theme. Other than editing the XML by hand, you are limited to using the effects from one of the built-in themes.
To select effects for a theme, from the Page Layout select Themes, Effects. Then choose one of the existing themes.
The Effects drop-down is initially vexing. There are only subtle clues about the effects used in the theme. Each effects icon consists of a circle, an arrow, and a rectangle. The circle represents effects for Simple shapes. The arrow represents Moderate effects. The rectangle represents Intense effects. These roughly correspond to rows in the Shape Styles gallery found on the Drawing Tools Format tab. Row 1 in the gallery is simple, row 4 is moderate, and row 6 is intense.
Saving a Custom Theme
To reuse a theme, you must save it. To save a theme, from the Page Layout tab select Themes, Save Current Theme.
By default, themes are stored in the Document Themes folder. In Windows Vista and Windows 7, the folder is in C:Usersuser nameAppDataRoamingMicrosoftTemplatesDocument Themes.
Be sure to give your theme a useful name and then click Save.
Using a Theme on a New Document
When you open a new document on the same computer, the Custom theme is in the Themes drop-down on the Page Layout tab. You can use this theme on all future documents.
Sharing a Theme with Others
If you want to share a theme with others, you need to send them the .thmx file from the theme folder.
The people you share the theme with can either copy the .thmx file to their equivalent folder or save the .thmx file to their desktop and use the Browse for Themes option, by choosing Page Layout, Themes, Browse for Themes.
Other Formatting Techniques
Now that you have the basics for formatting cells and worksheets, the rest of this chapter provides an overview of various formatting tips and tricks. These techniques discuss how to mix formatting within a single cell, wrap text in several cells, and use cell comments.
Tip
After selecting characters in the cell, move the mouse pointer to the right and up to activate a shortened version of the mini toolbar. You can use icons on this floating toolbar to format the selected characters.
Formatting Individual Characters
Occasionally, you might find yourself entering a short memo on a worksheet. This might occur as an introduction or as instructions to a lengthy workbook. Although Excel is not a full-featured word processor, it can do a few word processing tricks.
One trick is to highlight individual characters in a cell to add emphasis or to make them stand out. You can do this to any cell that does not contain a formula. In Figure 21.27, for example, text has been typed in column A and allowed to extend over the edge of the column into columns A:J. One word in row 4 is in a bold, underlined, red font.
Figure 21.27 Formatting for individual characters in a cell can be changed by selecting those characters while in edit mode.
To format individual characters, follow these steps:
1. Display the Home tab.
2. Select the cell that contains the characters to be formatted.
3. Press the F2 key to edit the cell.
4. Using the mouse, highlight the characters in the cell. Move up and to the right to display the mini toolbar.
5. Although most of the ribbon is grayed out, the options for font size, color, underline, bold, italic, and font name are available in the Font group of the Home tab. Apply any formatting, as desired, from this group.
6. If the changes are not visible in the formula bar, press Enter to accept the changes to preview them.
Changing the Default Font
Excel offers a default font setting to be used for all new workbooks. With the Excel 2016 paradigm of themes, the default font for new workbooks is initially the generic value of BODY FONT. However, this is not an actual font; instead, it refers to the main font used by the current theme.
Note
If you like the concept of using themes to change the look and feel of a document, you should leave the default font setting as BODY FONT and change the font used in the theme.
To change your default font for all new workbooks, follow these steps:
1. The menu for changing the default font does not offer Live Preview of the fonts. Therefore, go to the Font section of the Home tab and select the Font drop-down to inspect the available fonts in their actual styles. Find the name of the font you want to use.
2. From the File menu, select Excel Options. The Excel Options dialog appears.
3. Click the Popular category in the left margin.
4. In the second section, When Creating New Workbooks, select the Use This Font drop-down. Select the font name you chose in step 1.
5. Click OK to close the Excel Options dialog.
6. Close and restart Microsoft Excel for the changes to take effect.
The default font setting has an effect only in new workbooks. It does not affect workbooks previously created.
Wrapping Text in a Cell
You might have one column in a table that contains long, descriptive text. If the text contains several sentences, it would be impractical to make the column wide enough to include the longest value in the column. Excel offers the capability to wrap text on a cell-by-cell basis to solve this problem.
Note
If the rows are too tall, you will have a tendency to grab the right edge of the column and drag it outward to make the description column wider. A long-standing bug causes Excel not to resize the row heights automatically after this step. Instead, you need to select the Cells section of the Home tab and then select Format, AutoFit Row Height to resize the row height after adjusting the column width.
When you wrap text, one annoying feature of Excel becomes evident. All cells in Excel are initially set to have their contents aligned with the bottom of the cell. You probably do not notice this because most cells in Excel are the same height. However, when you wrap text, the cell heights double or more. When this occurs, it becomes evident that the bottom alignment looks strange. To correct this problem, follow these steps:
1. Decide on a reasonable column width for the column that contains the descriptive text. If you try to wrap text in a column that is only 8 points wide, you will be lucky to fit one word per line. If you have the space, a width of at least 24 allows suitable results for the text wrapping.
2. From the Cells section of the Home tab, select Format, Column Width. Choose a width of 24 or greater.
3. Choose the cells in the column to be wrapped.
4. From the Home tab, select Alignment, Wrap Text.
5. Select all cells in the range.
6. From the Home tab, select Alignment, Top Align. The values in the other columns now align with the top of the descriptive text.
Figure 21.28 shows a table where the descriptions in column B have had their text wrapped and all the cells are top aligned.
Justifying Text in a Range
When using Excel as a word processor to include a paragraph of explanatory body copy in a worksheet, you usually have to decide where to break each line manually. Otherwise, Excel offers a command that reflows the text in a paragraph to fit a certain number of columns.
For this reason, you should do some careful preselection work before invoking the command by following these steps:
1. Ensure that your text is composed of one column of cells that contain body copy. It is fine if the sentences extend beyond one column, but the text should be arranged so that the left column contains text and the remaining columns are blank.
2. Ensure that the upper-left cell of your selection starts with the first line of text.
3. Ensure that the selection range is as wide as you want the finished text to be.
4. If your sentences currently extend beyond the desired width, Excel requires more rows to wrap the text. Include several extra rows in the selection rectangle. Figure 21.29 shows a suitable-sized selection range.
Figure 21.29 You need to select more rows than necessary. The number of columns selected determines the width of the final text.
5. From the Home tab, select Editing, Fill, Justify. Excel flows the text so that each line is shorter than the selection range. Figure 21.30 shows the result.
Adding Cell Comments
Cell comments can contain a few sentences or paragraphs to explain a cell. Although the default is for all comments to use a yellow sticky-note format, you can customize comments with colors, fonts, or even pictures.
In the default case, a comment causes a red triangle to appear in a cell. If you hover over the triangle, the comment appears. Alternatively, you can request that comments be displayed all the time. This creates an easy way to add instructions to a worksheet.
Follow these steps to insert a comment, format it, and cause it to be displayed continuously:
1. Select a cell to which you want to add a comment.
2. Select Review, Comments, New Comment, or right-click the cell and select New Comment, or press Shift+F2.
3. The default comment starts with your name in bold on line 1 and the insertion point on line 2. To remove your name from the comment, backspace through your name and then press Ctrl+B to turn off the bold.
Note
Keep in mind that a comment can contain more than 2,000 words of body copy.
4. Type instructions to the person using the worksheet. You can make the instructions longer than the initial size of the comment.
5. After entering the text, click the resize handle in the lower-right corner of the comment. Drag to allow the comment to fit the text.
6. The selection border around the comment can be made of either diagonal lines or dots. If your selection border is diagonal lines, click the selection border to change it to dots.
7. Right-click the selection border and select Format Comment. The Format Comment dialog appears.
8. In the Format Comment dialog, change the font, alignment, colors, and so on, as desired. The Transparency setting on the Colors and Lines tab allows the underlying spreadsheet to show through the comment. If you choose the Fill Color drop-down, you can select Fill Effects and insert a picture as the background in the comment.
9. Click OK to return to the comment.
10. Right-click in the cell and select Show/Hide Comments. This causes the comment to be permanently displayed on the worksheet.
11. To reposition the comment, click the comment. Drag the selection border to a new location.
Figure 21.31 shows a comment that has been formatted, resized, and set to be displayed.
Copying Formats
Excel worksheets tend to have many similar sections of data. After you have taken the time to format the first section, it would be great to be able to copy the formats from one section to another section. The next sections in this chapter discuss the two methods offered in Excel 2016 for doing this: pasting formats and using the Format Painter icon.
Pasting Formats
An option on the Paste Options menu allows you to paste only the formats from the Clipboard. The rules for copying and pasting formats are as follows:
If your original selection is one cell, you can paste the formats to as many cells as you want.
If your original selection is one row tall and multiple cells wide, you can paste the formats to multiple rows, and the final paste area will be as wide as the original copied range.
If your original selection is one column wide and multiple cells tall, you can paste the formats to multiple columns, and the final paste area will be as tall as the original copied range.
Follow these steps to copy formats:
1. Select a formatted section of a report. This might be one cell, one row of cells, or a rectangular range of cells.
2. Press Ctrl+C to copy the selected section to the Clipboard.
3. Select an unformatted section of your worksheet. If your selection in step 1 is a rectangular range, you can select just the top-left cell of the destination range.
4. Press Ctrl+V to paste. Press Ctrl again to open the Paste Options menu. Press R to paste only the formats. The formats from the original selection are copied to the new range. Although the amounts initially changed after you pressed Ctrl+C, the original amounts are restored after you press R.
Caution
Do not attempt to use the Column Widths icon in the Paste Options menu to solve this problem. The Column Widths icon always pastes the values along with the column widths. Because you are only trying to copy formats and column widths in this example, this is not a suitable result.
5. If you have multiple target destinations to format, repeat step 4 as needed.
The disadvantage of using the Paste Formats method is that it does not change column widths. To copy column widths without pasting values, on the Home tab, click the Paste drop-down and then select Paste Special, Column Widths, OK.
Pasting Conditional Formats
Starting with Excel 2010, the rules changed when you paste a range with one conditional formatting onto another range with a different conditional formatting. The copied conditional format replaces the existing conditional formatting. There might be times when you want to merge the existing icon set in the source range with the existing color scale in the target range. In this case, choose All Merging Conditional Formats from the Paste Special dialog, or the elusive icon in the second row, fourth column of the Paste Options menu. Note that this pastes formats, formulas, and borders as well as merges the conditional formats.
Using the Format Painter
The Format Painter icon appears in the Clipboard group of the Home tab. The prominent location of the icon might encourage you to attempt to use this feature. The Format Painter is still tricky to use.
To copy a format from a source range to a destination range, follow these steps:
1. Select the source range. If you want to copy column widths, the source range must include complete columns.
2. Click the Format Painter icon once in the Clipboard group of the Home tab. The mouse icon changes to a plus and a paintbrush.
3. Immediately use the mouse to click and drag to select a destination range. If the source range was five columns wide, the destination range should also be five columns wide.
4. If you accidentally click somewhere else or click the wrong size range, undo and start over.
The ToolTip for the Format Painter icon advertises a little-known feature. This feature enables you to copy a format to many ranges. To do this, follow these steps:
1. Select the source range.
2. Double-click the Format Painter icon.
3. Click a new destination range. The format is copied. Alternatively, you can drag to paint a different size range.
4. Repeat step 3 as many times as you want.
5. When you are done formatting ranges, press Esc or single-click the Format Painter icon to turn off the feature.
Copying Formats to a New Worksheet
You can use a straightforward way to make a copy of a worksheet. This method is better than creating a new worksheet and copying formats from the original sheet to the new sheet. Among its advantages are that column widths and row heights are copied and page setup settings are copied.
To copy a worksheet within the current workbook, follow these steps:
1. Activate the worksheet to be copied.
2. Hold down the Ctrl key. Click the worksheet tab and drag it to a new location. A new sheet is created with a strange name, such as Sheet3 (2).
3. Right-click the sheet tab and select Rename. The cursor moves to the tab, which is now editable.
4. Type a new name and press Enter. The tab has a new name.
To copy a worksheet to a new workbook, follow these steps:
1. Activate the worksheet to be copied.
2. Right-click the sheet tab. Select Move or Copy to display the Move or Copy dialog.
3. In the To Book drop-down, select (new book).