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

9.4 Formatting and Templates in Microsoft Excel

Workplace Software and Skills9.4 Formatting and Templates in Microsoft Excel

Learning Objectives

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

  • Format and manipulate a cell or group of cells
  • Design column and row headers
  • Use conditional formatting
  • Work with templates to format worksheets

At WorldCorp, there is so much data that it is impossible to look at it all at once. For example, on a long worksheet with several hundred or thousand orders, you might want to see only orders over a certain value. In the previous sections, you learned how to organize the information in a spreadsheet and analyze the data using some basic calculations. These calculations provide the information needed to present the data to other entities in the corporation, but it might not yet be formatted in a professional manner. You may want to ensure the data meets certain criteria; you might want to add additional formatting to highlight certain trends in the data or group the data in a different way to show meaningful differences. With the skills you learned in the previous sections and those to follow, you can create a meaningful report and analysis of the data to effectively communicate company performance. This report can be utilized to measure company progress toward strategic goals or to compare WorldCorp to their competitors.

Formatting Cells

We’ve discussed formatting cells according to their contents, such as number, percentage, or text, but formatting extends to the visual presentation of a worksheet. In this context, formatting involves applying colors, font types, and borders to cells for styling purposes. It can make a presentation more visually appealing or distinct from other presentations. Another important reason for formatting is to increase the readability and comprehension of data. Formatting can be applied to text and numbers, to cell backgrounds, or to cell borders. It is important to remember that sometimes additional formatting may not be necessary. The goal is to have a professional presentation of the information. Too much formatting such as bright colors or distracting fonts does not enhance the presentation of the information. It is advisable to stick to simple formatting that is not distracting and is in line with company standards for your corporation.

Using Color and Fonts

Colors and fonts can be used to highlight specific parts of a worksheet such as the header row or the total row and to indicate the end of a table. Highlighting values that are outside of a certain range or to show trends in a dataset can also be useful. In accounting, the convention is to have negative numbers in red font and positive numbers in black font. These uses of colors and fonts provide meaningful information to the user at a glance.

In Figure 9.27a, the current header row is gray with white font. Suppose your manager wants to follow the color scheme of the company and wants the header row to be highlighted with a blue cell fill and a larger font. To change the fill color of the cells, first select the cells you want to change (Figure 9.27a) and then click on the paint bucket in the Font command group on the Home tab. The drop-down arrow next to the bucket allows you to choose from many preset colors, or you can create your own by clicking on More Colors. For this table, click on the blue fill box in the middle of the fifth column of colors. Now that the background is blue, the white font is harder to read, so you should change it to a darker color. To change the color of the font, select the cells and click on the text icon next to the paint bucket. You can change the font by clicking on the Font drop-down menu and the size by clicking on the adjacent Font Size drop-down menu (Figure 9.27b). You can also change the formatting of individual cells, but it saves time to highlight all of the header cells and format them at the same time.

(a) Shading button opens to options: Theme Colors, Standard Colors, No Fill, More Colors. (b) Font size opens to font sizes options. Spreadsheet displays blue fill in row 1 with bold font.
Figure 9.27 You can easily change the cell fill color, font type, and text color. (a) Select the paint bucket from the Home tab to change the color of the background fill. A drop-down menu gives you many options or you can define your own color using More Colors. The new background fill color is more visually appealing, but the text does not stand out as it would in a different color. (b) Font size, type, and color can also be changed using the commands in the Font command group. (Used with permission from Microsoft)


The default style in Excel is for gridlines to be visible in order to distinguish one cell from another when viewed on the screen. However, there may be times when that distinction could be clearer, or you want to divide groups of columns or rows. You can use borders to make these distinctions. Let’s assume you would like to put a border around the total cell. To add this border, first select the cell or cells where you want to add a border (Figure 9.28). Then, from the Home tab, in the Font command group, you will find the Border command drop-down menu. You can also access this by right-clicking after you selected the cells and choosing Format Cells and then the Borders tab (Figure 9.29a). By selecting the down arrow of the Border command, you can choose the appropriate border for the cell or cells. You can change the border style and color. First, select the style and thickness of the outline, change the color if you want it to be a color other than black, then apply it on the preview on the right by clicking on each border (Figure 9.29b). You can choose multiple styles of borders or leave cells without borders, such as if you wanted a border around your table but not within the rows and columns. More border options are available by choosing More Borders at the bottom of the list. This will bring up the Format Cells dialog box, Borders tab. Make sure your header row is now formatted consistently.

Cell G11 is selected on a spreadsheet with multiple columns and rows of data.
Figure 9.28 To add borders, select the cells you want to add a border to. (Used with permission from Microsoft)
(a) Borders options include Borders (i.e., Bottom, Top, Left, Right, No, All, Outside), Draw Borders (Draw, Draw Border Grid, Erase). (b) Border tab in Format Cells options: Line Style, Color, Presets, Border.
Figure 9.29 (a) You can use the Borders command drop-down menu or right-click and choose Format Cells. (b) You can select various thickness levels of the border around the cell. (Used with permission from Microsoft)

Wrapping Text

When you are working with text that is long, you can extend the width of your columns to fit it all in. One way is to do it manually. Hover over the line between two columns. The cursor will change to a black vertical line with opposite arrows. Click on this line and drag the column to your preferred width. You can also use the AutoFit command to automatically size the columns to fit the data contained in that column (Figure 9.30a). To AutoFit the columns, hover over the line between the columns, as you would to do it manually. Then, double-click on the line, and the column will adjust to the width of the contents. For example, to automatically size column E to the appropriate size for the data in the column, double-click on the line between the D and F columns at the top of the spreadsheet.

Sometimes, the text is so long that the whole width of the column is not visible on your screen; other times, you may not want a column to take up most of the width of your table. In those cases, the text is cut off. In Figure 9.30a, the header for column E is too long to fit in the cell. To fix this, select the cell, then on the Home tab, click on Wrap Text. The Wrap Text feature arranges the text in a cell so that it extends onto another line and increases the height of the row (Figure 9.30b).

(a) Cell E1 displays cut off text. Cursor with two black arrows at top right in cell. (b) Wrap Text is selected and text in cell fits. Size of row 2 enlarged.
Figure 9.30 Text wrapping can make long text fit in a column. (a) The header for column E is too long for the width of the column. Double-click or click and drag the line between columns to widen or narrow a column. The Wrap Text feature allows you to see text that is cut off. (b) Text wrapping changes the height of a row to accommodate long text. (Used with permission from Microsoft)

Merging Cells

Borders provided one method of grouping content in a table, but there are other ways to show that some rows or columns of data should be grouped together. The merge feature combines the content from two or more cells. When merging, the content and formatting of the first cell supersedes the content and formatting of the others. Merging cells can increase the readability of the tables. Figure 9.31a shows a row added above the header row to group some of the columns together to identify important sales order information to the port dispatchers. The first two columns tell the port dispatchers when the order was placed, so you want the cell that says “When” to span both columns. First, add “When” to the first cell. Then, select both cells over the columns you want the header to span and click on Merge & Center on the Home tab. Figure 9.31b shows the merged and centered cells. This can be repeated with cells E3 and F3. You can also merge content within a table. At the bottom, merge cell A14 with cells B14, C14, D14, E14, and F14, to make the Total row more readable. Now that the merging is complete, you can format the border outlines. Format the top row, making sure all cell contents are centered and have borders, and add borders to the bottom row to highlight the totals. Figure 9.31c shows the completed table.

(a) Row 3 cells in spreadsheet all single cells. (b) Two cells in row highlighted. (c) Spreadsheet displays sets of cells merged together to create one larger cell with text centered inside.
Figure 9.31 Merging cells is useful for presentation and readability purposes. You can (a) group columns by adding a row above the header row, merge and center cells to create a new label above the date and time, (b) merge and center cells to group the quantity and the code, and merge cells next to the total to create a more readable total. You can also add borders. (c) The final table is clear and readable. (Used with permission from Microsoft)

Working with Columns and Rows

The label added to tables at the top of a column or to the left of a row to make the data understandable is called a header. You’ve already learned to type words at the top of a column and format cells so that the headers look different from the rest of the table. Fonts can be bolded or centered, and background colors or shading applied. You can also freeze columns and rows so that the viewer of the document can see the header even if the document extends beyond the screen. Hiding rows and columns can also be useful for reducing the size of a worksheet.

Freezing Columns and Rows

When you have a large set of data, you will need to scroll beyond the edges of your screen to see all of it. You will need to be able to look at the data in each column or row but scrolling can take the header row or column off the screen. To ensure that the column or row headers are visible, you can use a command called Freeze Panes, which fixes the headers on the screen while allowing users to scroll through the rest of the data. The table in Figure 9.32a extends beyond row 22. As you scroll down through the data, notice that you can no longer see the column headers, so you will want to freeze the header row so that you can still see the column headers when you scroll down through the data. Figure 9.32b shows the choices in the Freeze Panes command on the View tab. You can freeze the top row, the first column, or both. For this data, freeze the top row so that scrolling down will keep the column headers in view. Figure 9.32b shows a line below the top row that indicates that the row is frozen. You are not limited to only freezing a header row or column. Excel allows you to use Freeze Panes anywhere in the spreadsheet. Just click on a cell and then on Freeze Panes, and it will freeze everything above and to the left of it.

(a) Header in spreadsheet not visible. (b) Freeze Panes is selected and opens to options: Freeze Panes, Freeze Top Row, Freeze First Column. Row 1 visible in spreadsheet with row 17 beneath.
Figure 9.32 Using the Freezing Panes command keeps headers visible when data extends off the screen. (a) Before the header row is frozen, scrolling down removes the header from view. (b) The Freeze Panes options are found on the View tab. Clicking on Freeze Panes allows for freezing the top row, first column, or both. Freezing the header row shows a line that indicates the row is frozen and keeps it in view while scrolling. (Used with permission from Microsoft)

Mac Tip

On a Mac, Excel uses icons for many of the drop-down menus that appear in the Windows version. The Freeze tool, for example, is always visible at the top of your active workbook.

Hiding Columns and Rows

Sometimes when working with data, information may be irrelevant for one use but cannot be deleted. This could be important when formatting spreadsheets for various users or when performing calculations. A user might not need to see all the data that goes into the formula but needs to see the final result of the formula. For example, in Figure 9.32a, column B contains the name of the port, but all of the visible data for the port is the same. If the port of Portland, Maine, is working with this data, they won’t need to see the column that contains the port name. Additionally, the workers at the port won’t need to know the price per unit. To make the table more usable for these workers, you can use the Hide command, which temporarily removes designated rows or columns from view. To hide a row or column, click on the row or column designation (the number to the left of the row header or the letter above the column header) to select the whole row or column, then right-click and select Hide from the menu (column B in Figure 9.33a). The result is shown in Figure 9.33b. To unhide a column, select the two columns that surround the hidden column, right-click, and select Unhide (Figure 9.34a). The column appears again, as shown in Figure 9.34b.

You can tell when a row or column is hidden by looking at the header, where you can see that letters or numbers are missing and there are two lines between the column or row designations. For example, we can see in Figure 9.33b that the header row goes from column A to column C. B is hidden and is not shown as a header. It is important to watch for that when you are using a spreadsheet that you did not create.

(a) Column B is highlighted; Hide is selected from menu. (b) Column B is no longer visible on spreadsheet. Two vertical lines display between Columns A and C to indicate missing columns.
Figure 9.33 Using the Hide command makes rows/columns temporarily not visible in a table. (a) Right-click on the column and select Hide. (b) When columns are hidden, double lines and skipped letters are indicated in the letter designation header. (Used with permission from Microsoft)
(a) Columns A and C are selected in the spreadsheet and Unhide is selected from menu. (b) Column B is visible again on the spreadsheet.
Figure 9.34 (a) To reveal the columns again, select the two columns on either side of the hidden one, right-click, and select Unhide. (b) All columns are visible again. (Used with permission from Microsoft)

Mac Tip

To access the Hide function, hold the Control key and click on the column.

Conditional Formatting

All of the formatting you have learned about so far has involved manually configuring individual cells or groups of cells. Excel has additional formatting features that can save you the time of manual formatting. One of these features, conditional formatting, analyzes the cell contents and applies certain preset designs or layouts based on the content. One example is to change the color of the font if the data is above or below a certain threshold (Figure 9.35). Conditional formatting is not turned on by default, so you will need to apply and customize it. Click on the drop-down menu next to Conditional Formatting on the Home tab and choose the formatting you want to use. You can set up the parameters using the dialog box shown in Figure 9.36a. Figure 9.36b shows another example of conditional formatting: highlighting the top ten FOB $ values.

Column D selected. Conditional Formatting selection opens to Highlight Cell Rules, then to options: Greater Than…, Less Than…, Between…, Equal to…, Text that Contains…, A Date Occurring…, Duplicate Values…, and More Rules….
Figure 9.35 Conditional formatting automatically applies designated formatting to values that meet a certain criterion. The Conditional Formatting drop-down menu lists several commonly used choices. (Used with permission from Microsoft)
(a) Cells (2500, 3000) highlighted Red Fill with Dark Red Text (>1000). (b) Top 10 Items Format cells that rank in the TOP 10 with Red Text is selected ($39,102.00 $44,499.00).
Figure 9.36 (a) The dialog box allows you to set the parameters. (b) There are numerous preset conditional formatting choices. (Used with permission from Microsoft)

Real-World Application

Excel in the Construction Industry

The construction industry in the United States employs nearly ten million people and contributes over $1 trillion to the economy in structures built each year.1 Excel can manage all the information needed to share among the various entities involved in a construction project. From building a small home garden shed to constructing an office complex, Excel can help manage the overall progress of a construction project. Excel can be helpful when estimating costs for construction projects to submit bids for particular projects. Items that can be summarized, tracked, and analyzed in Excel include order tracking, calculating needed quantities of materials such as lumber or piping, and payroll. Even for the small construction management company, Excel can be used to effectively manage a large construction project to keep the project on time and within budget. There are many templates available to help set up a construction project from start to finish.

What are some other industries where Excel could be a useful tool?

Working with Templates

As in the other Office applications, Excel has built-in and online templates. Microsoft has their own online templates for Office, but many other websites offer free Excel templates. Templates are blank, formatted workbooks that you populate with your own data. Microsoft provides some built-in templates. Figure 9.37 shows the selection of a sales report, which has five sheets. This sales report has an order ledger, called “Sales Data,” with the product name, customer code, and quarterly sales figures. The template comes with a few lines of generic data. The other sheets use the data you enter on the first sheet. The second sheet uses the sales data but organizes it by product. The third sheet organizes the same data by customer. The fourth sheet filters the data to show the top 10 products, and the fifth sheet filters the data to show the top 10 customers. You should be able to find a similar template in your version of Excel, but there are many other sales reports templates available. The template library is frequently updated by Microsoft, so the versions you see might look a bit different than what we have shown here. The functionality will be similar, but the colors and the layout could vary.

A built-in template of a sales report with five sheets is shown. The template comes with a few lines of generic data. Four other tabs are shown along the bottom.
Figure 9.37 Templates are preformatted and predesigned tables. You can find the templates by clicking on File and New. A sales report has formatting and formulas preset so that all you need to do is enter your data. (Used with permission from Microsoft)

Figure 9.38 shows a product price list downloaded from that has columns for a product inventory number, product name, product description, retail price, and volume-selling price. This sort of price list is an information sheet for potential business clients who are considering products from you. These templates are modifiable, so you can update the header with your company’s information. You can also add more products or change the headers.

A customizable spreadsheet is shown. Columns and rows offer example of information placement as well as style, fonts, colors, and organization.
Figure 9.38 Although templates come with their own formatting, you can customize them based on your needs. (Used with permission from Microsoft)

Spotlight on Ethics

Ethical Considerations in Using Templates

Templates are a wonderful resource and can save time and money in creating spreadsheets and workbooks in Excel. Templates can come with preset formatting and formulas, with little more to do than enter your own data. There are many templates available online, but you must choose one carefully. You will need to consider the purpose of the template you are using, company policy regarding branding, the version of the software you are using, as well as copyright and licensing of the template itself prior to using it for business purposes. For instance, perhaps you are using a template to create an invoice to provide clients. The template may hold a license that allows you to use the template for commercial use so long as you do not resell the template itself, or the license might prohibit commercial use altogether, restricting its use to nonprofit activities only. If that is the case, you should not use the template for business purposes as it may place the business at risk of litigation. It is always best to carefully read the user agreement and license of a template prior to implementing it.

Where might you find a template’s licensing and permissible usage information? How can you be sure you are permitted to use a template in the manner in which you intend?


  • 1Ken Simonson. “Construction Data.” Associated General Contractors of America.

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.