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.
Use this workbook for the chapter.
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)
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)
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:
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:
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)
You can also choose to double underline things in addition to single underline. To do this, use the following steps:
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)
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.
Excel helps you format your sheet by filling the background color of a cell. To do this, use the following steps:
To change the text color in cells, use the following steps:
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.
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)
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)
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:
To right-align the numbers, use the following steps:
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:
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:
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)
Once in the dialog box that pops up, use the following steps:
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:
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.
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:
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:
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)
The second is the Currency number format. (See Figure 16.24)
Sometimes, you have to choose to decrease the number of decimal points using the Decrease Decimals button. (See 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:
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.
To include the comma without it changing it to the Accounting format, use the following steps:
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:
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:
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.
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)
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:
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:
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:
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.
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.