Skip to ContentGo to accessibility pageKeyboard shortcuts menu
OpenStax Logo
Workplace Software and Skills

10.6 Advanced Formatting Techniques

Workplace Software and Skills10.6 Advanced Formatting Techniques

Learning Objectives

By the end of this section, you will be able to:

  • Create custom formats for worksheet or table for appearance
  • Create custom formats for a worksheet or table for content
  • Copy formatting from one range of data to a new range of data

It is important for organizations to use uniform branding and style, which can appear anywhere, from emails to invoice templates to Excel files. Having a uniform style within an organization adds a level of professionalism and consistency to the business, and these may be tied to the company’s intellectual property and similar protections. Many organizations provide guidelines or manuals for all files produced, to ensure that all employees can easily follow the same style or use the same templates.

Microsoft Excel offers many prepackaged themes and styles that can be easily applied to tables and spreadsheets. But it is also possible to create your own custom format. This can be ideal for businesses that want their products to look a certain way and contain specific branding.

Custom Formats for Appearance

The tables and datasets you’ve been working with were finished versions, with color, font, table size, and background set. Here, you’ll create these tables step-by-step with themes and other style elements.

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.

(a) Themes button is selected and opens to options for Office (various color combinations displayed), Browse for Themes, Save Current Theme. (b) Office pane displays color options for selection and Customize Colors.
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)
Cell Styles button opens to options: Good, Bad, and Neutral, Data and Model, Titles and Headings, Themed Cell Styles, and Number Format. Selections available for New Cell Style and Merge Styles.
Figure 10.60 You can use the built-in themes to automatically format heading and numbers. (Used with permission from Microsoft)
A spreadsheet is shown with a theme applied. Row 1 is highlighted light green with numbers bolded and a new font is used.
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.

Format as Table button opens to options for Light, Medium, and Dark. At the end of the list there are options for New Table Style and New PivotTable Style.
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)
A spreadsheet with information is displayed.
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.

Diagonally across the spreadsheet, the word CONFIDENTIAL is shown four times behind the data in capital, red font.
Figure 10.64 The background change tool is useful for design purposes or for company branding. (Used with permission from Microsoft)

Custom Formats for Content

Custom formats for content allow users to design a format for data contained in cells. For example, you can use custom formatting to set a format type for how your purchase order numbers are input into your company’s system, or to change how dates are displayed. This feature is different from merely changing a cell’s format such as the decimal points displayed or how months are abbreviated. Instead, the custom format can be saved as a format that is then widely applied to worksheets. You can create a format from scratch, but it is easier to use one of the built-in formats and adjust if necessary. You can customize most aspects of the formatting—from spacing, to displayed decimals, to font color.

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.

Throughout this spreadsheet, decimals are removed from the numbers. Row 1 is populated with two-digit numbers in the 2nd, 5th, 6th, 7th, and 8th columns.
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.

Number tab is selected in a Format Cells pane. Custom is selected from Category, Sample is $10,850,000, and options are visible for Type. Spreadsheet is selected in background.
Figure 10.66 There are multiple “Custom” formatting codes, as you can see in the list. (Used with permission from Microsoft)
Columns E to H in the spreadsheet are shown with numbers listed with two digits followed by an “m” in the second row and lower.
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.

In the spreadsheet, the numbers listed in Columns E (Zip Code), F (Phone), and G (SSN) are entered with no spaces or punctuation and numbers are missing from the zip code list.
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.

Zip Code column is selected in spreadsheet. Number tab in Format Cells panes is selected. Special is selected in Category, Sample is 07101, ZIP Code is selected in Type, and Locale is English.
Figure 10.69 Microsoft already has preset formatting for many number types, including zip codes. (Used with permission from Microsoft)
Column F titled Phone is selected and Phone Number is selected in the Format Cells window open under the Special Category.
Figure 10.70 Another preset formatting is for phone numbers. (Used with permission from Microsoft)
Table displays consistent information for Zip Code (five numbers), and phone numbers (first three digits in parentheses and a dash between the third and fourth numbers).
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)

Copy Formatting

Copying the format of a given cell is one of the most used commands in Microsoft Office because it is available not only in Excel, but in all other Office applications. The Format Painter command lets a user copy a certain range’s formatting and apply it to a new range by selecting the cell with the desired format to be copied, and then selecting the Format Painter icon on the Home tab. Format Painter can be used to transfer the formatting from one cell or a whole table to another cell or table. The Format Painter tool is the paintbrush icon on the Home tab in the Clipboard command group. To use the tool, first select the cell or cells that have the formatting you want to apply to other cells. Figure 10.72 shows the selected cells E1 to G7. Then, click on the paintbrush icon on the Home tab. As you do, your cursor will change to a paintbrush. With that paintbrush, select the range of cells to which you want to apply the formatting. In this example, those cells are A1 to C7. The unformatted table will then be formatted in the same manner as the formatted table. The result is apparent in Figure 10.73. The only step left is adjusting the columns by clicking on the border of each column on top.

Cells E1 to G7 are selected. Under the Home tab, the paintbrush icon is selected in the Clipboard section on the toolbar.
Figure 10.72 The Format Painter tool allows you to copy the formatting from one table and apply it to another. Notice the cursor paintbrush next to it. (Used with permission from Microsoft)
Two tables are visible with similar information and font color, but formatted differently (column sizes adjust to text in second table).
Figure 10.73 Both tables are formatted using the same font, fill color, and number format. (Used with permission from Microsoft)

Mac Tip

The Mac version uses Cell Styles to format cells.

There are also other ways to copy formatting. Another way is to use the Paste Special tool. You first select the whole formatted table and press Ctrl+C (to copy it), then select your entire unformatted table (Figure 10.74a). Then, on the Home tab, choose Paste Special from the Paste menu and click on Formatting (R) from the last row, as shown in Figure 10.74b. This tool will paste just the formatting from your original selection; it will not paste any of the values or formulas (Figure 10.74c).

(a) A table is selected for copying. (b) Paste button opens to Paste Special option. (c) Formatting has been applied to the previously selected table (Row 1 is blue and bolded).
Figure 10.74 Paste Special can be used instead of the Format Painter tool. (a) First, copy the table with the formatting you want to apply. Then, highlight the table where you want to apply the formatting. (b) Choose Paste Special. (c) The tool applies the formatting only; it does not copy any of the cell contents. You would then adjust your column widths to avoid the error. (Used with permission from Microsoft)

This book may not be used in the training of large language models or otherwise be ingested into large language models or generative AI offerings without OpenStax's permission.

Want to cite, share, or modify this book? This book uses the Creative Commons Attribution License and you must attribute OpenStax.

Attribution information
  • If you are redistributing all or part of this book in a print format, then you must include on every physical page the following attribution:
    Access for free at
  • If you are redistributing all or part of this book in a digital format, then you must include on every digital page view the following attribution:
    Access for free at
Citation information

© Jan 3, 2024 OpenStax. Textbook content produced by OpenStax is licensed under a Creative Commons Attribution License . The OpenStax name, OpenStax logo, OpenStax book covers, OpenStax CNX name, and OpenStax CNX logo are not subject to the Creative Commons license and may not be reproduced without the prior and express written consent of Rice University.