TIP TIP
NOTE NOTE
TIP TIP
Setting table options
The Table Tools➪Design➪Table Style Options group contains several check boxes that determine whether various elements of the table are displayed, and whether some formatting options are in effect:
n Header Row:Toggles the display of the Header Row.
n Totals Row:Toggles the display of the Totals Row.
n First Column:Toggles special formatting for the first column.
n Last Column:Toggles special formatting for the last column.
n Banded Rows:Toggles the display of banded (alternating color) rows.
n Banded Columns:Toggles the display of banded (alternating color) columns.
Working with the Total Row
The Total Row in a table contains formulas that summarize the information in the columns. Normally, the Total Row isn’t turned on. To display the Total Row, choose Table Tools➪Design➪Table Style Options and put a check mark next to Total Row.
By default, the Total Rows display the sum of the values in a column of numbers. In many cases, you’ll want a different type of summary formula. When you select a cell in the Total Row, a drop-down arrow appears, and you can select from a number of other summary formulas (see Figure 6.7):
n None: No formula
n Average: Displays the average of the numbers in the column
n Count: Displays the number of entries in the column (blank cells are not counted)
n Count Numbers: Displays the number of numeric values in the column (blank cells, text cells, and error cells are not counted).
n Max: Displays the maximum value in the column n Min: Displays the minimum value in the column n Sum: Displays the sum of the values in the column
Excel Remembers
When you do something with a complete column in a table, Excel remembers that and extends that “some- thing” to all new entries added to that column. For example, if you apply currency formatting to a column and then add a new row, Excel applies currency formatting to the new value in that column.
The same thing applies to other operations, such as conditional formatting, cell protection, data validation, and so on. And if you create a chart using the data in a table, the chart will be extended automatically if you add new data to the table. Those who have used a previous version of Excel will appreciate this feature the most.
n StdDev: Displays the standard deviation of the values in the column. Standard deviation is a sta- tistical measure of how “spread out” the values are.
n Var: Displays the variance of the values in the column. Variance is another statistical measure of how “spread out” the values are.
n More Functions: Displays the Insert Function dialog box so that you can select a function that isn’t in the list.
FIGURE 6.7
Several types of summary formulas are available for the Totals Row.
If you have a formula that refers to a value in the Total Row of a table, the formula returns an error if you hide the Total Row. But if you make the Total Row visible again, the formula works as it should.
For more information about formulas, including the use of formulas in a table column, refer to Chapter 11.
Removing duplicate rows from a table
If you have a table that contains duplicate items, you may want to eliminate the duplicates. In the past, removing duplicate data was essentially a manual task. But Excel 2007 makes it very easy if the data is in a table.
Start by selecting any cell in your table. Then choose Table Tools➪Design➪Remove Duplicates. Excel responds with the dialog box shown in Figure 6.8. The dialog box lists all the columns in your table. Place a check mark next to the columns that you want to be included in the duplicate search. Most of the time, you’ll want to select all the columns, which is the default. Click OK, and Excel weeds out the duplicate rows and displays a message that tells you how many duplicates it removed.
CROSS-REF CROSS-REF WARNING WARNING
FIGURE 6.8
Removing duplicate rows from a table is easy.
It’s important to understand that duplicate values are determined by the value displayedin the cell — not necessarily the value storedin the cell. For example, assume that two cells contain the same date. One of the dates is formatted to display as 5/15/2007, and the other is formatted to display as May 15, 2007. When removing duplicates, Excel considers these dates to be different.
Sorting and filtering a table
The Header Row of a table contains a drop-down arrow that, when clicked, displays sorting and filtering options (see Figure 6.9).
FIGURE 6.9
Each column in a table contains sorting and filtering option.
WARNING WARNING
Sorting a table
Sortinga table rearranges the rows based on the contents of a particular column. You may want to sort a table to put names in alphabetical order. Or, maybe you want to sort your sales staff by the totals sales made.
To sort a table by a particular column, click the drop-down in the column header and choose one of the sort commands. The exact command varies, depending on the type of data in the column.
You can also select Sort By Color, to sort the rows based on the background or text color of the data. This option is relevant only if you’ve overridden the table style colors with custom colors.
When a column is sorted, the drop-down in the header row displays a different graphic to remind you that the table is sorted by that column.
You can sort on any number of columns. The trick is to sort the least significant column first and then pro- ceed until the most significant column is sorted lasted. For example, in the real estate table, you may want the list to be sorted by agent. And within each agent’s group, the rows should be sorted by area. And within each area, the rows should be sorted by list price. For this type of sort, first sort by the List Price column, then sort by the Area column, and then sort by the Agent column. Figure 6.10 shows the table sorted in this manner.
FIGURE 6.10
A table, after performing a 3-column sort.
Another way of performing a multiple-column sort is to use the Sort dialog box. To display this dialog box, choose Home➪Editing➪Sort & Filter➪Custom Sort. Or, right-click any cell in the table and choose Sort➪Custom Sort from the shortcut menu.
In the Sort dialog box, use the drop-down lists to specify the first search specifications. Note that the searching is opposite of what I described in the previous paragraph. In this example, you start with Agent.
Then, click the Add Level button to insert another set of search controls. In this new set of controls, specify
NOTE NOTE
the sort specifications for the Area column. Then, add another level and enter the specifications for the List Price column. Figure 6.11 shows the dialog box after entering the specifications for the three-column sort.
This technique produces exactly the same sort as described in the previous paragraph.
FIGURE 6.11
Using the Sort dialog box to specify a three-column sort.
Filtering a table
Filteringa table refers to displaying only the rows that meet certain conditions. (The other rows are hidden.) Using the real estate table, assume that you’re only interested in the data for the N. Country area. Click the drop-down in the Area Row Header and remove the check mark from Select All, which unselects every- thing. Then, place a check mark next to N. County and click OK. The table, shown in Figure 6.12, is now filtered to display only the listings in the N. County area. Notice that some of the row numbers are missing;
these rows contain the filtered (hidden) data.
Also notice that the drop-down arrow in the Area column now shows a different graphic — an icon that indicates the column is filtered.
FIGURE 6.12
This table is filtered to show only the information for N. County.
You can filter by multiple values — for example, filter the table to show only N. Country and Central.
You can filter a table using any number of columns. For example, you may want to see only the N. County listings in which the Type is Single Family. Just repeat the operation using the Type column. All tables then display only the rows in which the Area is N. County and the Type is Single Family.
For additional filtering options, select Text Filters (or Number Filters, if the column contains values). The options are fairly self-explanatory, and you have a great deal of flexibility in displaying only the rows that you’re interested in.
In addition, you can right-click a cell and use the Filter command on the shortcut menu. This menu item leads to several additional filtering options.
As you may expect, the Total Row is updated to show the total only for the visible rows.
Why you copy data from a filtered table, only the visible data is copied. In other words, rows that are hid- den by filtering don’t get copied. This filtering makes it very easy to copy a subset of a larger table and paste it to another area of your worksheet. Keep in mind that the pasted data is not a table — it’s just a normal range.
To remove filtering for a column, click the drop-down in the Row Header and select Clear Filter. If you’ve filtered using multiple columns, it may be faster to remove all filters by choosing Home➪Editing➪ Sort & Filter➪Clear.
Converting a table back to a range
If you need to convert a table back to a normal range, just select a cell in the table and choose Table Tools➪ Design➪Tools➪Convert To Range. The table style formatting remains intact, but the range no longer functions as a table.
NOTE NOTE
Formatting your worksheet is like the icing on a cake — it may not be absolutely necessary, but it can make the end product a lot more attractive.
In an Excel worksheet, formatting can also make it easier for others to understand the worksheet’s purpose.
Stylistic formatting isn’t essential for every workbook that you develop — especially if it is for your own use only. On the other hand, it takes only a few moments to apply some simple formatting, and, once applied, the formatting will remain in place without further effort on your part.
In Chapter 6, I show how easy it is to apply formatting to a table. The informa- tion in this chapter applies to normal ranges. I show you how to work with the Excel formatting tools: fonts, colors, and styles, such as bold and italic. I also cover custom styles that you can create to make formatting large amounts of material in a similar way easier.
Getting to Know the Formatting Tools
Figure 7.1 shows how even simple formatting can significantly improve a work- sheet’s readability.
This workbook is available on the companion CD-ROM. The file is named loan payments.xlsx.
ON the CD-ROM ON the CD-ROM
IN THIS CHAPTER
Understanding how formatting can improve your worksheets Getting to know the formatting tools
Using formatting in your worksheets
Using named styles for easier formatting
Understanding document themes
Worksheet Formatting
FIGURE 7.1
In just a few minutes, some simple formatting can greatly improve the appearance of your worksheets.
Excel’s formatting tools are available in three locations:
n In the Home tab of the Ribbon
n In the Mini toolbar that appears when you right-click a range or a cell n In the Format Cells dialog box
In addition, many of the common formatting commands have keyboard shortcuts that you can use.
Excel provides another way to format cells based on the cell’s contents. Chapter 8 discusses conditional formatting.
Using the Formatting Tools in the Home Tab
The Home tab of the Ribbon provides quick access to the most commonly used formatting options. Start by selecting the cell or range; then use the appropriate tool in the Font, Alignment, or Number groups.
Using these tools is very intuitive, and the best way to familiarize yourself with them is to experiment. Enter some data, select some cells, and then click the controls to change the appearance. Note that some of these controls are actually drop-down lists. Click the small arrow on the button, and the button expands to dis- play your choices.
Using the Mini toolbar
When you right-click a cell or a range selection, you get a shortcut menu. In addition, a mini toolbar appears above the shortcut menu. Figure 7.2 shows how this toolbar looks.
The Mini toolbar contains controls for common formatting:
n Font n Font Size n Increase Font n Decrease Font
n Accounting Number Format
CROSS-REF CROSS-REF
n Percent Style n Comma Style n Format Painter n Bold
n Italic n Center n Borders n Fill Color n Font Color n Increase Decimal n Decrease Decimal n Merge And Center
If you use a tool on the Mini toolbar, the shortcut menu disappears, but the toolbar remains visible so you can apply other formatting if you like. Also, notice that the Mini toolbar gradually fades away if you move the mouse pointer away from it. To hide the Mini toolbar, just click in any cell.
If you find the Mini toolbar distracting, you can turn it off in the Popular tab of the Excel Options dialog box.
FIGURE 7.2
The Mini toolbar appears above the right-click shortcut menu.
TIP TIP
Using the Format Cells dialog box
The formatting controls available on the Home tab of the Ribbon are sufficient most of the time, but some types of formatting require that you use the Format Cells dialog box. This tabbed dialog box lets you apply nearly any type of stylistic formatting, as well as number formatting. The formats that you choose in the Format Cells dialog box apply to the cells that you have selected at the time. Later sections in this chapter cover the tabs of the Format Cells dialog box.
When you use the Format Cells dialog box, you don’t see the effects of your formatting choices until you click OK.
After selecting the cell or range to format, you can display the Format Cells dialog box by using any of the following methods:
n Press Ctrl+1.
n Click the dialog box launcher in Home➪Font, Home➪Alignment, or Home➪Number. The dialog box launcher is the small downward-pointing arrow icon displayed to the right of the group name in the Ribbon. When you display the Format Cells dialog box using a dialog box launcher, the dialog box is displayed with the appropriate tab visible.
n Right-click the selected cell or range and choose Format Cells from the shortcut menu.
n Click the More command in some of the drop-down controls in the Ribbon. For example, the Home➪Font➪Border➪More Borders drop-down includes an item named More Borders.
The Format Cells dialog box contains six tabs: Number, Alignment, Font, Border, Patterns, and Protection.
The following sections contain more information about the formatting options available in this dialog box.
Using Formatting in Your Worksheets
Applying stylistic formatting to Excel worksheets is not an exact science. People have varying opinions about what constitutes a good-looking worksheet. Therefore, the following sections focus on the mechanics.
It’s up to you to choose the formatting options that are most appropriate.
Excel 2007’s new document themes feature attempts to assist nondesigners in creating attrac- tive worksheets. I discuss Excel 2007 themes later in this chapter. See “Understanding Document Themes.”
Using different fonts
You can use different fonts, sizes, or text attributes in your worksheets to make various parts, such as the headers for a table, stand out. You also can adjust the font size. For example, using a smaller font will allow more information on a single page.
By default, Excel 2007 uses the 11-point Calibri font. A fontis described by its typeface (Calibri, Cambria, Arial, Times New Roman, Courier New, and so on), as well as by its size, measured in points. (Seventy-two points equal one inch.) Excel’s row height, by default, is 15 points. Therefore, 11-point type entered into a 15-point rows leaves a small amount of blank space between the characters in adjacent rows.
If you have not manually changed a row’s height, Excel automatically adjusts the row height based on the tallest text that you enter into the row.
TIP TIP
NEW FEATURE NEW FEATURE
NOTE NOTE
If you plan to distribute a workbook to other users, you should stick with the standard fonts that are included with Windows or Microsoft Office. If you open a workbook and your system doesn’t have the font with which the workbook was created, Windows attempts to use a similar font.
Sometimes this attempt works OK, and sometimes it doesn’t.
Use the Font and Font Size tools in the Home tab of the Ribbon (or in the Mini toolbar) to change the font or size for selected cells.
You also can use the Font tab in the Format Cells dialog box to choose fonts, as shown in Figure 7.3. This tab enables you to control several other font attributes that aren’t available elsewhere. Besides choosing the font, you can change the font style (bold, italic), underlining, color, and effects (strikethrough, superscript, or subscript). If you click the check box labeled Normal Font, Excel displays the selections for the font defined for the Normal style. I discuss styles later in this chapter. See “Using Named Styles for Easier Formatting.”
Figure 7.4 shows several different examples of font formatting. In this figure, the gridlines were turned off to make seeing the underlining easier. Notice, in the figure, that Excel provides four different underlining styles. In the two non-accounting underline styles, only the cell contents are underlined. In the two accounting underline styles, the entire width of the cells is always underlined.
TIP TIP
Updating Old Fonts
Office 2007 includes several new fonts, and the default font has been changed for all the Office applica- tions. In the past, Excel’s default font was 10-point Arial. In Excel 2007, the default font for the Office theme is 11-point Calibri. Most people will agree that Calibri is much easier to read, and it gives the work- sheet a more modern appearance.
If you use Excel 2007 to open a workbook created in a previous version, the default font will not be changed, even if you apply a document style (by choosing Page Layout➪Themes➪Themes). But here’s an easy way to update the fonts in a workbook that was created using an older version of Excel:
1. Press Ctrl+N to open a new, empty workbook. The new workbook will use the default document theme.