10.6 Advanced Formatting Techniques

Using Existing Themes

To make formatting easy to use and high quality, Excel offers Themes. Applying a Theme can quickly change the color scheme, fonts, and size of a table or graph. For example, you could easily change the formatting of a table without manually readjusting everything. If your objective is to save time when changing the overall formatting, choose a default theme and all the formatting will be handled automatically.

To access Themes, go to the Page Layout tab and select the Themes icon (Figure 10.59a). Then, choose any of the available themes. (“Wisp” is shown in Figure 10.61.) All of the formatted tables and graphs in that particular workbook will be changed. If you have a simple worksheet with no specific formatting such as colors, font style, and the like, when you choose a theme, nothing will change. However, if you have a worksheet with formatted headings and charts, you will notice changes when you select a theme. The Color combo box lets users further modify the theme by selecting a different color palette (Figure 10.59b).

For additional changes, the Home tab has a combo box called Cell Styles that lets users select other font sizes or background colors, as shown in Figure 10.60. Depending on your selection in the Colors menu in the Themes command group (Page Layout tab), the options in the Styles menu will change. For instance, use Blue from the palette, and in the Styles combo box, select Heading 3 and Accent 5, 40%. If an orange color palette from the Color menu was chosen, the Styles options would be orange tones.

Figure 10.59 (a) Themes are useful if your company has a custom theme to apply for all charts. (b) Color schemes can be customized to meet your needs. (Used with permission from Microsoft)

Figure 10.60 You can use the built-in themes to automatically format heading and numbers. (Used with permission from Microsoft)

Figure 10.61 Applying a theme is easier than formatting each section of the table individually. (Used with permission from Microsoft)

Creating Custom Themes

Often companies will have a color scheme for all communications and materials within and outside of the company. The palette is usually linked to the company logo and is an essential part of creating a unified and professional appearance for marketing purposes and legal usage. If a company has a specific theme, it can be saved and used by all employees. The saved theme file can be shared company-wide to ensure consistency. Moreover, the saved theme can be used across all Microsoft products.

There are two ways to create a custom theme. First, users can create the theme from scratch. This means you define the font, color, and other elements of the theme. Second, and perhaps easier, is to start with an existing theme that is close to the desired theme. You can then make the needed adjustments such as changing the font or modifying heading colors slightly. First, format your table using the desired settings, and then choose Save Current Theme at the bottom of the Themes menu to save the theme as a file that can be used in other Microsoft products and/or shared with others.

Other Formatting Tools

Another time-saving strategy is using the Format as Table tool. On the Home tab, select the Format as Table combo box to see many options for formatting tables (Figure 10.62). The background color, heading font, and borders can be changed; additionally, column widths will change automatically to fit the text. This feature also automatically adds filters to the columns, which can help with organizing the data in a table Figure 10.63. However, using the Format as Table feature does not format cells according to their content, such as currency.

Figure 10.62 The Format as Table tool is a simple way to add full formatting to basic tables. When you format the selection as a table, there are several color options and themes to choose from. (Used with permission from Microsoft)

Figure 10.63 You get a filter option after the selection is formatted as a table. (Used with permission from Microsoft)

Adding Images to the Background

Beyond formatting cell content, you can also add an image to a table’s background. The background is a canvas that is displayed behind the cell contents of a worksheet. It could be useful to add a logo, or a topic-related image to the background of a table, especially for an internal or client-facing presentation. Often, this feature is used to indicate that documents are not finalized but in a “draft” stage.

To add an image in the background, first select the table, then go to the Page Layout tab and select Background. You will then be prompted to select an existing file, or you can search online for an image. An image of the word “Confidential” has been added in Figure 10.64 to show that this report should be kept confidential and not shared. You may insert an existing file or search the internet for a picture.

Figure 10.64 The background change tool is useful for design purposes or for company branding. (Used with permission from Microsoft)

Shortening Large Numbers

Sometimes, the appearance of many repeated large numbers in a spreadsheet can be overwhelming. Other times, you may simply want to reduce numbers to estimates. Excel can shorten large numbers so that they are summarized by a letter, such as K (for “thousands”) or M (for “millions”). Figure 10.65 shows that the estimated COGS varies depending on the quantity sold and the unit cost.

Figure 10.65 Shortening estimated figures can be useful for presentations and reports, as they will be easier to read than these long numbers. (Used with permission from Microsoft)

Since these figures are estimations for the coming period, they can be abbreviated to the million by marking with an “m.” To do this, select the cells on which to perform this process, and then right-click and select Format Cells. In the Format Cells dialog box, go to Custom, as shown in Figure 10.66. In the Type field, delete the existing text, type [0,,“m”], and then click OK. Using the 0 indicates no decimal points and to use only whole numbers. The “m” is used to round to the million units. Excel will change the figures in the selected cells to be rounded up to the nearest million, as shown in Figure 10.66.

Figure 10.66 There are multiple “Custom” formatting codes, as you can see in the list. (Used with permission from Microsoft)

Figure 10.67 Choosing to format large numbers to display as millions can make a table more readable. The finished product could now be further formatted for appearance. (Used with permission from Microsoft)

Common Number Formats

Some number formats come up frequently, such as addresses, phone numbers, or Social Security Numbers (SSNs). Their formats may be consistent in a specific region (like SSNs in the United States), or they may be similar with variations (like phone numbers with all hyphens, hyphens and parentheses, or spaces).

In Figure 10.68, you can see a table of names and contact information. However, there are some issues with the data: the numbers all look the same. Phone numbers should be separated by parentheses for the area code and a hyphen after the exchange, and SSNs use hyphens around the middle two digits. Zip codes should be five digits long, but some are showing up as only four digits; this is because Excel does not recognize the leading zero. As is, the data in the current table is confusing. Microsoft offers a standard command to format cells in these situations.

Figure 10.68 There are ways to fix the formatting issues in this table without having to manually fix each one. (Used with permission from Microsoft)

First, select each column separately, right-click each column, select Format Cells, and then select Special. For formatting zip codes (Figure 10.69), select the Zip Code column values (but not the header), and in the Special format cell dialog box, select Zip Code, and choose OK. Follow the same steps for formatting phone numbers (Figure 10.70). As you can see in the result shown in Figure 10.71, Excel already has special formats for these number types.

Figure 10.69 Microsoft already has preset formatting for many number types, including zip codes. (Used with permission from Microsoft)

Figure 10.70 Another preset formatting is for phone numbers. (Used with permission from Microsoft)

Figure 10.71 The finished product can save the consuming process of writing the parentheses or hyphens manually and decreases data entry errors. (Used with permission from Microsoft)