Chapter 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 2019 with any of these three methods:

• Use table 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 box.

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 box or using the formatting icons located on the Home tab.

In Excel 2019, 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 menu in the Cells group 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 box, 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 box.

• Click the dialog box launcher icons in the lower-right corner of the Font, Alignment, or Number groups. Each icon opens the dialog box, with the focus on a different tab.

• Right-click any cell and select Format Cells.

• Select Format Cells from the Format drop-down menu on the Home tab.

As shown in Figure 21.4, the Format Cells dialog box 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.

  

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

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.

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 menu with choices based on your regional settings.

Above the five buttons in the Number group is a drop-down menu that has a dozen popular number styles. Figure 21.6 shows the styles in the drop-down menu. 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 menu 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 menu. This is one format for which it is actually better to use the icon on the ribbon than the Format Cells dialog box.

• 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 menu rounds it to one-digit divisors.

Changing Numeric Formats by Using Built-in Formats in the Format Cells Dialog Box

The Format Cells dialog box offers more number formats than the Home tab. My favorite number format can be accessed only through the Format Cells dialog box. I find that I avoid the buttons in the Number group in the Home tab and go directly to the Format Cells dialog box.

You can display the Format Cells dialog box by clicking the dialog box launcher icon in the lower-right corner of the Number group of the Home tab. When you open the Format Cells dialog box 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 box.

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

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 menu. This drop-down menu 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 p.m., 13:30, and 37:30:55 types. The first type displays times from 12:00 a.m. through 11:59 p.m. The second type displays military time. In this system, midnight is 0:00, and 11:59 p.m. is 23:59. Neither of these types displays hours exceeding 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 exceeding 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.

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.

To use a custom number format, follow these steps:

1. Select the cells to be highlighted.

2. Display the Format Cells dialog box by pressing Ctrl+1.

3. Select the Number tab.

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.

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:

Click here to view code image

[<=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.

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.

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.

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 menu 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).

  

  Note

  If you need a font size that is not in the drop-down menu, you can type a new value in the drop-down menu. For example, although the drop-down menu 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 menu 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 menu is in the Font group of the Home tab.

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 menu. As shown in Figure 21.15, you can select the drop-down menu to change from Single Underline to Double Underline.

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 box 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 menu in the Font group of the Home tab offers 13 popular border options plus five border tools. If you must draw nonstandard borders, explore the Draw Borders tool in this drop-down menu.

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 menu, 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 box, 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 box.

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

The Font group on the Home tab offers a paint bucket drop-down menu and an A drop-down menu. The paint bucket is a color chooser for the background fill of the cell. The A drop-down menu is a color chooser for the font color in the cell. Both drop-down menus offer six shades of the 10 theme colors, 10 standard colors, and the More Colors option. The paint bucket drop-down menu also offers the menu choice No Fill, as shown in Figure 21.16.

The More Colors link offers the two-tabbed Colors dialog box. 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 box.

3. Select the Fill tab.

4. Click the Fill Effects button.

5. In the Color 1 and Color 2 drop-down menus, 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 box.

9. Click OK to close the Format Cells dialog box.

Figure 21.17 shows the Fill Effects dialog box. 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-down menus in the Fill tab of the Format Cells dialog box 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.

  

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

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 box to move to cell C2, you will be taken to cell B2 instead.

Troubleshooting

While selecting cells, if you touch a merged cell, your selection will instantly become as wide as the merged cell.

In the figure below, you might try selecting A1:A99 by pressing Ctrl+Shift+Up Arrow. That would usually be a great way to select the cells with data in the column.

However, the merged cell in A1 causes your selection to become as wide as the merged cell. You end up selecting A1:E99 instead of A1:A99.

To prevent the problem, you could add a blank Row 2 to separate the merged cell from your data.

Or, you could stop using merged cells. Select A1:E1 and then select, Home, Format, Format Cells, Alignment, and then open the Horizontal drop-down menu and choose Center Across Selection. This creates the same look as Merge & Center, but the selection rectangle will not widen when you touch A1.

To fix the problem shown in the figure, hold down the Shift key while you press the down arrow key. You will have A2:E99 selected. Now that the merged cell is no longer part of the selection, hold down Shift while pressing the left arrow key four times.

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

If you need more control over the text orientation, you can select the Alignment option in the drop-down menu to display the Alignment tab of the Format Cells dialog box. 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.

Understanding Themes

A theme is a collection of colors, fonts, and effects. Office 2019 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.

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 several 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-down menus 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 menu 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 menu from the Themes group to see the accent colors used in each theme. Note that this drop-down menu offers a grayscale option that is not available in the Themes drop-down menu.

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 box appears.

2. To change any accent color, select the drop-down menu 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 menu. 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 menu. 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 box 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 tab, select Themes, Effects. Then choose one of the existing themes.

The Effects drop-down menu 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 menu 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.

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.

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

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 menu 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 box 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 menu. Select the font name you chose in step 1.

5. Click OK to close the Excel Options dialog box.

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.

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.

   

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.

4. Type instructions to the person using the worksheet. You can make the instructions longer than the initial size of the comment.

   Note

   Keep in mind that a comment can contain more than 2,000 words of body copy.

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 box appears.

8. In the Format Comment dialog box, 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 menu, 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 2019 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.

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 menu 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 box, 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 box.

3. In the To Book drop-down menu, select (new book).

4. Click Create A Copy.