9.4 Formatting and Templates in Microsoft Excel

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.

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)

Borders

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.

Figure 9.28 To add borders, select the cells you want to add a border to. (Used with permission from Microsoft)

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

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.

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)

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.

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)

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.

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)

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)