Spreadsheet Formatting and Design

In this chapter, we’re going to be talking about spreadsheet formatting and design. We will primarily focus on the Home tab around the font, alignment, and number sections. 

Practice Spreadsheet

Use this workbook for the chapter.

Heading

When we use Excel, we have a responsibility to make sure that whatever we create is easy for the user or the intended audience to read. One of the ways we do that is by using consistent design in terms of font, alignment, number, and sometimes even the Style boxes on the toolbar. (See Figure 16.1)

BUS115_image_16.1.png
Figure 16.1

Before we dig into the specifics of font and alignment on this tab, we will introduce you to the Page Layout tab and the Office Theme section. (See Figure 16.2)

BUS115_image_16.2.png
Figure 16.2
 

This section allows you to understand what colors you might want to use, and you can customize them. You can choose your fonts, and you can change how the page is set up to print. To change the font, use the following steps:

  1. Select the Page Layout tab. 
  2. Select Fonts
    1. This will bring up a drop-down menu.
  3. Select the desired font. For this exercise, select Arial. (See Figure 16.3)
    BUS115_image_16.3.png
    Figure 16.3
     

Should you ever need to print an Excel file, you can find print options on the Page Layout tab.

Back on the Home tab, the first section has to do with font. (See Figure 16.1)

After we had changed the font in the directions above, all of the text in the worksheet should be the same font and the same size. In this case, it should be an 11 point font. The first thing you may notice is that the rows are compressed. To change the height of the rows, use the following steps:

  1. Select the blank top-left corner, where the first row and the first column meet, to select all of the rows and columns in the worksheet. (See Figure 16.4)
    BUS115_image_16.4.png
    Figure 16.4
     

  2. Right-click on the selected rows.
    1. An options bar will appear.
  3. Select Row Height. (See Figure 16.5)
  4. Insert the desired height. For this exercise, type 20.
  5. Select OK.
BUS115_image_16.5.png
Figure 16.5
 

Across the top of the table, you can see that there are headers for each set of data. You can use some of the effects in the font section of the Home tab to bold, italicize, or underline the headers for easier identification. You should only use one of the formats at a time; it isn’t correct to format text to be bolded, italicized, and underlined. In this exercise, use the bold effect to denote it’s a header row. (See Figure 16.6)

BUS115_image_16.6.png
Figure 16.6
 

You can also choose to double underline things in addition to single underline. To do this, use the following steps:

  1. Highlight what you want to double underline. 
  2. Select the dropdown menu on the underline option. 
  3. Select Double Underline. (See Figure 16.7)
BUS115_image_16.7.png
Figure 16.7
 

Font Size

The next thing we’ll look at is font size. In addition to being able to change to a different font typeface, we can also change the size. We can pick a specific size, or we can use the Up and Down arrows to automatically move up and down between sizes. (See Figure 16.8)

BUS115_image_16.8.png
Figure 16.8
 

If something is bold already, it doesn't also need to be bold and larger. However, if we wanted to choose just a larger font size, we could remove the bold and increase the font size of our header so that it stands out from the rest of the data. For this exercise, however, keep the header bold, and leave it the regular font size.

Fill Color and Text Color

Excel helps you format your sheet by filling the background color of a cell. To do this, use the following steps:

  1. Select the cells you would like to fill with color. 
  2. Select the Theme Colors icon. (See Figure 16.9)
  3. Select which color you would like to fill the cell with.
    BUS115_image_16.9.png
    Figure 16.9
     

To change the text color in cells, use the following steps:

  1. Select the text that you want to change.
  2. Select the font colors icon. (See Figure 16.10)
  3. Select the color you want to change the text to.
    BUS115_image_16.10.png
    Figure 16.10
 

Ensure the background and the foreground color are contrasted so that you can read what is in each cell. If you select the pop-out menu in the bottom right of the Font section, Excel takes you to the Format Cells font section. (See Figure 16.11

This allows you to add additional effects like strikethrough, superscript, and subscript

BUS115_image_16.11.png
Figure 16.11

 

Alignment

When you made the row height taller, you may have noticed that all of the cells, by default, are aligned to be at the bottom of the cell vertically. You can align to the top, the middle, or the bottom. (See Figure 16.12)

BUS115_image_16.12.png
Figure 16.12
 

For ease of reading, aligning to the middle is the most comfortable for most cases. 

We can also change the alignment horizontally. (See Figure 16.13)

BUS115_image_16.13.png
Figure 16.13
 

Excel recognizes when a value is text, and it automatically left-aligns it. If the value is a number, Excel automatically right-aligns it. Ensure numbers are right-aligned so that the units line up with each other. There will be more discussion about this in the number section. Left-align the dates using the following steps:

  1. Select the cells with dates in them.
  2.  Select the Left-Align icon. (See Figure 16.14)
    1. Excel will left-align the values in the selected cells.
Figure 16.14
 

To right-align the numbers, use the following steps:

  1. Select the numbers you want to right-align. 
  2. Select the Right-Align icon. (See Figure 16.15)
    1. Excel will right-align the values in the selected cells.
BUS115_image_16.15.png
Figure 16.15
 

Another formatting option is to change the angle of the text. For example, if you wanted to angle things upwards and make the row a little bit taller, this could save some space if you have narrow amounts of data and long headers. It isn’t something that’s used very frequently. To angle text, use the following steps:

  1. Select the cells containing the text you want to angle.
  2. Select the Angle Text icon. 
  3. Select Angle Clockwise. (See Figure 16.16)
    1. Excel will angle the text clockwise.
BUS115_image_16.16.png
Figure 16.16
 

Merging Cells

You can also insert a new row on the top, make a title for the table, and make it span across all four of the columns on the table. To do this, use the following steps:

  1. Select the cells you want the title to span across. 
  2. Select the Merge & Center dropdown menu. 
  3. Select Merge & Center. (See Figure 16.17)
    1. Excel will join those cells and center the header.
    2. Just selecting Merge Across will merge the cells, but it won’t center the data.
      BUS115_image_16.17.png
      Figure 16.17
 

Merging cells can cause issues when it comes to using formulas, copying and pasting, and a number of other things. One of the best things you can do is select the pop-out arrow in the bottom-left corner of the Alignment section of the toolbar. (See Figure 16.18)

BUS115_image_16.18.png
Figure 16.18
 

Once in the dialog box that pops up, use the following steps:

  1. Under Horizontal, select Center Across Selection. (See Figure 16.19)
  2. Select OK.
    1. Excel will center the heading across all four cells, but they remain four distinct cells.
BUS115_image_16.19.png
Figure 16.19
 

When you copy and paste with this formatting feature active, you won’t run into any errors.

Another thing you might notice is that the headers are a bit longer than the available space in the cells. When trying to maintain a consistent column width for this exercise, you may notice that the headings “Market Share” and “Number of Employees” do not fit. You could widen the cells to fit the headings, but then you may have a lot of wasted space on the sheet. Instead, you can choose a reasonable column width and use the following steps to wrap the text:

  1. Select the cells that have longer titles.
  2. Select Wrap Text. (See Figure 16.20)
    1. This allows the text to wrap around onto a second line.
BUS115_image_16.20.png
Figure 16.20
 

You may need to accommodate it with a slightly taller row, but it will allow the text to line up. The challenge we run into, though, is that not all of the tops will align. In this case, it might be best to align them all with the top so you can read them clearly.

Number Formatting

When it comes to number formatting, consistency is important. There are some pre-built formats in Excel, including dates. To format dates as a Long Date, use the following steps:

  1. Select a cell containing a date.
    1. The dropdown menu in the Numbers section of the toolbar will now say Date instead of General.
  2. Select the dropdown menu in Numbers.
  3. Select Long Date. (See Figure 16.21)
    1. Excel will write out the entire day of the week, month, date, and year.
      BUS115_image_16.21.png
       Figure 16.21
       

If you want to change any of these, you can select the whole range and choose one of the pre-existing formats seen in Figure 21. To use the Format Cells box, use the following steps:

  1. Select the range of cells you want to format.
  2. Press Control + 1 (PC) or Command + 1 (Mac).
    1. This will open up the Format Cells box.
  3. Select the Number tab. 
  4. Select Custom.
    1. This allows you to use special letters and symbols to make a customized format.
  5. Type ddd, dd mmm yyyy under Type
    1. The first three d’s will give an abbreviation of weekday.
      1. Adding a fourth will give you the whole day.
    2. The three m’s indicate the month.
    3. Four y’s indicate the whole year.
  6. Select OK. (See Figure 16.22)
    1. Excel will change the dates using the custom format. 
    2. The dropdown menu in the number section will say Custom when any of these formatted cells are selected.
BUS115_image_16.22.png
Figure 16.22
 

The data in the Revenue column represents dollars. There are typically two types of dollar formats. The first is the accounting number format in the number section. (See Figure 16.23)

BUS115_image_16.23.png
Figure 16.23

 

The second is the Currency number format. (See Figure 16.24)

Figure 16.24

 

Sometimes, you have to choose to decrease the number of decimal points using the Decrease Decimals button. (See Figure 16.25)

BUS115_image_16.25.png
Figure 16.25

 

The data in the Market Share column is represented as a percentage. To format a set of data as a percent, use the following steps:

  1. Select the data you want to turn into a percentage.
  2. Select the percentage button. (See Figure 16.26)
    1. This will turn decimals into percentages.
      BUS115_image_16.26.png
      Figure 16.26
       
  3. For this exercise, use the Increase Decimals button to increase the percentage of decimal points by one. (See Figure 16.27)
BUS115_image_16.27.png
Figure 16.27
 

The data under Employees is a traditional number, but when you format it using the Number preset, they will have unneeded decimal points that need to be taken out. If you select the comma style shown in Figure 16.28 to add commas, it formats the number back to Accounting.

BUS115_image_16.28.png
Figure 16.28
 

To include the comma without it changing it to the Accounting format, use the following steps:

  1. Select the pop-out icon in the bottom-right corner of the Number section. 
    1. This will bring up the Format Cells dialog box.
  2. Select the Number tab. 
  3. Make sure the Use 1000 Separator ( , ) box is checked.
  4. Set Decimal Places to 0. (See Figure 16.29)
  5. Select OK
    1. Excel will now include the numbers correctly, with a comma and no decimals.
BUS115_image_16.29.png
Figure 16.29
 

There are all sorts of things that you can do with Number Formatting. For example, if you have a negative number, you can determine how that number is displayed. Let’s say that one of the percentages in the data is negative. You can make negative numbers show up in parentheses and in different colors. To do this, use the following steps:

  1. Select the cell that you want to change the format in.
  2. Use Control + 1 (PC) or Command + 1 (Mac) to get into the Format Cells dialog box.
  3. Select the Custom tab. 
  4. Type ;[red](0.0%) after the original 0.0%. (See Figure 16.30)
    1. The semicolon lets Exel know that what comes after it is how you want the number to display if it’s negative.
  5. Select OK.
    1. The negative number will show up in parentheses, and it will be red.
    2. You may choose other colors, like blue or green, using this format as well.
BUS115_image_16.30.png
Figure 16.30
 

More Professional Designs

There are several ways to make this table we are working on look more professional. The Page Layout tab lets you see the print area. 

The View tab lets you uncheck the Gridline so that you don’t see any of the gridlines in the worksheet. However, you may still want to see the gridlines that should be in the table. 

To use borders to make those gridlines reappear in the table, use the following steps:

  1. Select the entire table.
  2. Select the Home tab. 
  3. Select the Borders dropdown menu. 
    1. This has all sorts of different border options.
  4. Select More Borders. (See Figure 16.31)
    1. This will take you to the Format Cells dialog box.
      BUS115_image_16.31.png
      Figure 16.31
       
  5. Select the Color dropdown menu and select gray.
  6. Select Outline and inside horizontal. (See Figure 16.32)
  7. Select OK.
    1. The table will be formatted with a border and horizontal lines separating the data.

BUS115_image_16.32.png
Figure 16.32
 

You can also change the thickness of the borders, which can be used to make the outside border thicker than the horizontal lines on the inside. When you put vertical bonders into the table, you can adjust the width of each column to help the data fit. Wrap the text on the titles so it all fits together. Make the title of the table larger and adjust the width accordingly to help it stand out better. Add 1960–1979 to the title of the table. Because the fill color and text color of the column titles are shades of red, the text doesn’t stand out very well. To fix this issue, change the fill color to a lighter gray and the text color to black.

Cell Styles

These are just some of the formatting options that you can use in Excel. Much of this can be customized even further using Cell Styles on the right-hand side of the toolbar. (See Figure 16.33)

BUS115_image_16.33.png
Figure 16.33

These Cell Styles allow you to quickly replicate or duplicate the same type of formatting across the workbook.

For example, if you want all of your headers to look the same, use the following steps:

  1. Select the title of the table.
  2. Select the Cell Styles dropdown menu. (See Figure 16.33)
  3. Select the Heading 1 option.
    1. Excel will overwrite any existing formatting to use the Heading 1 Formatting. 
  4. Select the column titles.
  5. Select Heading 2. (See Figure 16.34)
    1. Excel will overwrite any existing formatting, and the result will be the same style and font as the title, but with smaller font size.
      BUS115_image_16.34.png
      Figure 16.34
 

You may not like all of the styles, but there are some presets in the Cell Styles menu that can help save you time as you’re seeking to format your data and make it look good.

You may notice that in the Dates column, the dates are all different widths because of the font. Arial is a variable-width font. This means that if there is an I instead of an F, it takes up less width. To fix this, you can use a fixed-width or mono-width font. Courier New is a fixed-width font. Using Courier New formats the dates to line up perfectly. This helps the content read more easily because the units will always line up: the ones, tens, hundreds, thousands, millions, and so on

The More Borders option provides a way to strikethrough in the middle. To do this, use the following steps:

  1. Select the cell you want to have a strikethrough in.
  2. Select the Borders dropdown menu.
  3. Select More Borders. (See Figure 16.31)
    1. This will take you to the Format Cells dialog box.
  4. Select both of the diagonal inside borders. (See Figure 16.35)
    BUS115_image_16.35.png
    Figure 16.35
     
  5. Select OK.
    1. Excel will put an X through the data in that cell. (See Figure 16.36)
BUS115_image_16.36.png
Figure 16.36

 

Format Painter

Another thing that is important to note is the Format Painter option. 

Let’s say that you really like the format in the strikethrough cell, but you don’t want to go through the menu to get it again. To reproduce a format in another cell quickly, use the following steps:

  1. Select the cell with the format you want to reproduce. 
  2. Select the Format Painter button. (See Figure 16.37)
  3. Select another cell.
    1. Excel will copy the formatting and reproduce it in that second cell.
BUS115_image_16.37.png
Figure 16.37
 

In the Standards of Professionalism chapter, we talked about alignment and how things should look. Review the chapter for information on text alignment and formatting. To some extent, formatting is a matter of personal preference, but you should always make sure that it’s clear to the user what it is that they’re looking at.

Supplemental Resource

This content is provided to you freely by BYU-I Books.

Access it online or download it at https://books.byui.edu/bus_115_business_app/spreadsheet_formatti.