Excel2010 ebook

Chapter 5: Introducing Tables You can filter by multiple values in a column by using multiple check marks. For example, to filter the table to show only N. County and Central, place a check mark next to both values in the drop- down list in the Area Row Header. 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 col- umn. 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 val- ues). 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. Note As you may expect, the Total Row is updated to show the total only for the visible rows. n When you copy data from a filtered table, only the visible data is copied. In other words, rows that are hidden 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, though, that the pasted data is not a table — it’s just a normal range. You can, however, convert the copied range to a table. 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. 113



CHAPTER Worksheet Formatting F IN THIS CHAPTER ormatting 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. Understanding how formatting can improve your worksheets Stylistic formatting isn’t essential for every workbook that you develop — Getting to know the formatting especially if it is for your own use only. On the other hand, it takes only a tools few moments to apply some simple formatting; and, after you apply it, the formatting will remain in place without further effort on your part. Using formatting in your worksheets In Chapter 5, I show how easy it is to apply formatting to a table. The infor- Using named styles for easier mation in this chapter applies to normal ranges. I show you how to work formatting 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 Understanding document amounts of material in a similar way easier. themes Getting to Know the Formatting Tools Figure 6.1 shows how even simple formatting can significantly improve a worksheet’s readability. The unformatted worksheet (on the left) is perfectly functional but not very readable compared with the formatted worksheet (on the right). 115

Part I: Getting Started with Excel FIGURE 6.1 In just a few minutes, some simple formatting can greatly improve the appearance of your worksheets. The Excel formatting tools are available in three locations: l On the Home tab of the Ribbon l On the Mini toolbar that appears when you right-click a range or a cell l From the Format Cells dialog box In addition, many common formatting commands have keyboard shortcuts that you can use. Cross-Reference Excel also enables you to format cells based on the cell’s contents. Chapter 20 discusses conditional formatting. n Using the formatting tools of 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 experi- ment. 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 display your choices. Using the Mini toolbar When you right-click a cell or a range selection, you get a shortcut menu. In addition, the Mini toolbar appears above the shortcut menu. Figure 6.2 shows how this toolbar looks. 116

Chapter 6: Worksheet Formatting FIGURE 6.2 The Mini toolbar appears above the right-click shortcut menu. The Mini toolbar contains controls for common formatting: l Font l Font Size l Increase Font l Decrease Font l Accounting Number Format l Percent Style l Comma Style l Format Painter l Bold l Italic l Center l Borders l Fill Color l Font Color l Increase Decimal l Decrease Decimal l Merge & Center 117

Part I: Getting Started with Excel 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 after you use it, 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. Some people find the Mini toolbar distracting. Unfortunately, Excel does not provide an option to turn it off. The Excel Options dialog box does offer the Show Mini Toolbar on Selection option, but this option applies only to selecting text while editing a cell. Tip If you really want to disable the Mini toolbar, you can execute this VBA statement in the Immediate window of the Visual Basic Editor (VBE): Application.ShowMenuFloaties = True Yes, setting the property to True is completely counter-intuitive! But it works. You need to execute this state- ment only one time, and Excel remembers the setting between sessions. If you change your mind, execute that statement again, but change True to False. 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 for- mats 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. Note When you use the Format Cells dialog box, you don’t see the effects of your formatting choices until you click OK. n After selecting the cell or range to format, you can display the Format Cells dialog box by using any of the following methods: l Press Ctrl+1. l 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. l Right-click the selected cell or range and choose Format Cells from the shortcut menu. l 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. 118

Chapter 6: Worksheet Formatting Using Different Fonts to Format Your Worksheet 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 allows for more information on a single page. By default, Excel uses 11 point (pt) Calibri font. A font is 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 pt equal one inch.) Excel’s row height, by default, is 15 pt. Therefore, 11 pt type entered into 15 pt rows leaves a small amount of blank space between the characters in adjacent rows. Tip If you’ve not manually changed a row’s height, Excel automatically adjusts the row height based on the tallest text that you enter into the row. n Updating Old Fonts Office 2007 introduced several new fonts, and the default font has been changed for all the Office applications. In versions prior to Excel 2007, the default font was 10 pt Arial. In Excel 2007 and Excel 2010, the default font for the Office theme is 11 pt Calibri. Most people will agree that Calibri is much easier to read, and it gives the worksheet a more modern appearance. If you open a workbook created in a pre-Excel 2007 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. 2. Open your old workbook file. 3. Choose Home ➪ Styles ➪ Cell Styles ➪ Merge Styles. Excel displays its Merge Styles dialog box. 4. In the Merge Styles dialog box, select the new workbook that you created in Step 1. 5. Click OK. 6. Click Yes in response to Excel’s question regarding merging styles that have the same name. This technique changes the font and size for all unformatted cells. If you’ve applied font formatting to some cells (for example, made them bold), the font for those cells will not be changed (but you can change the font manually). If you don’t like the new look of your workbook, just close the workbook without saving the changes. 119

Part I: Getting Started with Excel Tip 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 okay, and sometimes it doesn’t. n Use the Font and Font Size tools on the Home tab of the Ribbon (or on 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 6.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 select the Normal Font check box, Excel displays the selections for the font defined for the Normal style. I discuss styles later in this chap- ter. See “Using Named Styles for Easier Formatting.” FIGURE 6.3 The Font tab of the Format Cells dialog box gives you many additional font attribute options. Figure 6.4 shows several different examples of font formatting. In this figure, gridlines were turned off to make the underlining more visible. Notice, in the figure, that Excel provides four different underlining styles. In the two non-accounting underline styles, only the cell contents are under- lined. In the two accounting underline styles, the entire width of the cells is always underlined. 120

Chapter 6: Worksheet Formatting FIGURE 6.4 You can choose many different font-formatting options for your worksheets. If you prefer to keep your hands on the keyboard, you can use the following shortcut keys to for- mat a selected range quickly: l Ctrl+B: Bold l Ctrl+I: Italic l Ctrl+U: Underline l Ctrl+5: Strikethrough These shortcut keys act as a toggle. For example, you can turn bold on and off by repeatedly press- ing Ctrl+B. Note Beginning with Excel 2007, the Document Themes feature attempts to assist nondesigners in creating attractive worksheets. I discuss document themes later in this chapter. See “Understanding Document Themes.” n Using Multiple Formatting Styles in a Single Cell If a cell contains text (as opposed to a value or a formula), Excel also enables you to format individual characters in the cell. To do so, switch to Edit mode (press F2, or double-click the cell) and then select the characters that you want to format. You can select characters either by dragging the mouse over them or by pressing the Shift key as you press the left- or right-arrow key. After you select the characters for format, use any of the standard formatting techniques. The changes apply only to the selected characters in the cell. This technique doesn’t work with cells that contain values or formulas. 121

Part I: Getting Started with Excel Changing Text Alignment The contents of a cell can be aligned horizontally and vertically. By default, Excel aligns numbers to the right and text to the left. All cells use bottom alignment, by default. Overriding these defaults is a simple matter. The most commonly used alignment commands are in the Alignment group on the Home tab of the Ribbon. Use the Alignment tab of the Format Cells dialog box for even more options (see Figure 6.5). FIGURE 6.5 The full range of alignment options are available on the Alignment tab of the Format Cells dialog box. Choosing horizontal alignment options Horizontal alignment options, which control how cell contents are distributed across the width of the cell (or cells), are available from the Format Cells dialog box: l General: Aligns numbers to the right, aligns text to the left, and centers logical and error values. This option is the default alignment. l Left: Aligns the cell contents to the left side of the cell. If the text is wider than the cell, the text spills over to the cell on the right. If the cell on the right isn’t empty, the text is truncated and not completely visible. Also available on the Ribbon. 122

Chapter 6: Worksheet Formatting l Center: Centers the cell contents in the cell. If the text is wider than the cell, the text spills over to cells on either side if they’re empty. If the adjacent cells aren’t empty, the text is truncated and not completely visible. Also available on the Ribbon. l Right: Aligns the cell contents to the right side of the cell. If the text is wider than the cell, the text spills over to the cell on the left. If the cell on the left isn’t empty, the text is trun- cated and not completely visible. Also available on the Ribbon. l Fill: Repeats the contents of the cell until the cell’s width is filled. If cells to the right also are formatted with Fill alignment, they also are filled. l Justify: Justifies the text to the left and right of the cell. This option is applicable only if the cell is formatted as wrapped text and uses more than one line. l Center across Selection: Centers the text over the selected columns. This option is useful for precisely centering a heading over a number of columns. l Distributed: Distributes the text evenly across the selected column. Note If you choose Left, Right, or Distributed, you can also adjust the Indent setting, which adds space between the cell border and the text. n Figure 6.6 shows examples of text that uses three types of horizontal alignment: Left, Justify, and Distributed (with an indent). FIGURE 6.6 The same text, displayed with three types of horizontal alignment. 123

Part I: Getting Started with Excel Choosing vertical alignment options Vertical alignment options typically aren’t used as often as the horizontal alignment options. In fact, these settings are useful only if you’ve adjusted row heights so that they’re considerably taller than normal. Vertical alignment options available in the Format Cells dialog box are l Top: Aligns the cell contents to the top of the cell. Also available on the Ribbon. l Center: Centers the cell contents vertically in the cell. Also available on the Ribbon. l Bottom: Aligns the cell contents to the bottom of the cell. Also available on the Ribbon. l Justify: Justifies the text vertically in the cell; this option is applicable only if the cell is formatted as wrapped text and uses more than one line. This setting can be used to increase the line spacing. l Distributed: Distributes the text evenly vertically in the cell. This setting seems to have the same effect as Justify. Wrapping or shrinking text to fit the cell If you have text too wide to fit the column width but don’t want that text to spill over into adjacent cells, you can use either the Wrap Text option or the Shrink to Fit option to accommodate that text. The Wrap Text control is also available on the Ribbon. The Wrap Text option displays the text on multiple lines in the cell, if necessary. Use this option to display lengthy headings without having to make the columns too wide, and without reducing the size of the text. The Shrink to Fit option reduces the size of the text so that it fits into the cell without spilling over to the next cell. Usually, it’s easier to make this adjustment manually. Note If you apply Wrap Text formatting to a cell, you can’t use the Shrink to Fit formatting. n Merging worksheet cells to create additional text space Excel also enables you to merge two or more cells. When you merge cells, you don’t combine the contents of cells. Rather, you combine a group of cells into a single cell that occupies the same space. The worksheet shown in Figure 6.7 contains four sets of merged cells. For example, range C2:I2 has been merged into a single cell, and so has range J2:P2. In addition, ranges B4:B8 and B9:B13 have also been merged. In the latter two cases, the text direction has been changed (see “Displaying text at an angle,” later in this chapter). 124

Chapter 6: Worksheet Formatting FIGURE 6.7 Merge worksheet cells to make them act as if they were a single cell. You can merge any number of cells occupying any number of rows and columns. In fact, you can merge all 17,179,869,184 cells in a worksheet into a single cell — although I can’t think of any good reason to do so, except maybe to play a trick on a co-worker. The range that you intend to merge should be empty, except for the upper-left cell. If any of the other cells that you intend to merge are not empty, Excel displays a warning. If you continue, all the data (except in the upper-left cell) will be deleted. To avoid deleting data, click Cancel in response to the warning. You can use the Alignment tab of the Format Cells dialog box to merge cells, but using the Merge & Center control on the Ribbon (or on the Mini toolbar) is simpler. To merge cells, select the cells that you want to merge and then click the Merge & Center button. The cells will be merged, and the content in the upper-left cells will be centered horizontally. The Merge & Center button acts as a toggle. To unmerge cells, select the merged cells and click the Merge & Center button again. After you merge cells, you can change the alignment to something other than Center. The Home ➪ Alignment ➪ Merge & Center control contains a drop-down list with these additional options: l Merge Across: When a multirow range is selected, this command creates multiple merged cells — one for each row. l Merge Cells: Merges the selected cells without applying the Center attribute. l Unmerge Cells: Unmerges the selected cells. Displaying text at an angle In some cases, you may want to create more visual impact by displaying text at an angle within a cell. You can display text horizontally, vertically, or at an angle between 90 degrees up and 90 degrees down. 125

Part I: Getting Started with Excel From the Home ➪ Alignment ➪ Orientation drop-down list, you can apply the most common text angles. For more control, use the Alignment tab of the Format Cells dialog box. In the Format Cells dialog box (refer to Figure 6-5), use the Degrees spinner control — or just drag the pointer in the gauge. You can specify a text angle between –90 and +90 degrees. Figure 6.8 shows an example of text displayed at a 45-degree angle. Note Rotated text may look a bit distorted onscreen, but the printed output is usually of much better quality. n FIGURE 6.8 Rotate text for additional visual impact. Controlling the text direction Not all languages use the same character direction. Although most Western languages are read left to right, other languages are read right to left. You can use the Text Direction option to select the appropriate setting for the language you use. This command is available only in the Alignment tab of the Format Cells dialog box. Don’t confuse the Text Direction setting with the Orientation setting (discussed in the previous section). Changing the text orientation is common. Changing the text direction is used only in very specific situations. Note Changing the Text Direction setting won’t have any effect unless you have the proper language drivers installed on your system. New Feature Use the Language tab of the Excel Options dialog box to determine which languages are installed. n 126

Chapter 6: Worksheet Formatting Using Colors and Shading Excel provides the tools to create some very colorful worksheets. You can change the color of the text or add colors to the backgrounds of the worksheet cells. Note Prior to Excel 2007, workbooks were limited to a palette of 56 colors. Excel 2010 allows a virtually unlimited number of colors. n You control the color of the cell’s text by choosing Home ➪ Font ➪ Font Color. Control the cell’s background color by choosing Home ➪ Font ➪ Fill Color. Both of these color controls are also available on the Mini toolbar, which appears when you right-click a cell or range. Tip To hide the contents of a cell, make the background color the same as the font text color. The cell contents are still visible in the Formula bar when you select the cell. Keep in mind, however, that some printers may over- ride this setting, and the text may be visible when printed. n Even though you have access to an unlimited number of colors, you might want to stick with the ten theme colors (and their light/dark variations) displayed in the various color selection controls. In other words, avoid using the More Color option, which lets you select a color. Why? First of all, those ten colors were chosen because they “go together” (well, at least somebody thought they did). Another reason involves document themes. If you switch to a different document theme for your workbook, nontheme colors aren’t changed. In some cases, the result may be less than pleasing, aesthetically. See “Understanding Document Themes,” later in this chapter, for more information about themes. Using Colors with Table Styles In Chapter 5, I discuss the handy Table feature. One advantage to using tables is that it’s very easy to apply table styles. You can change the look of your table with a single mouse click. It’s important to understand how table styles work with existing formatting. A simple rule is that apply- ing a style to a table doesn’t override existing formatting. For example, assume that you have a range of data that uses yellow as the background color for the cells. When you convert that range to a table (by choosing Insert ➪ Tables ➪ Table), the default table style (alternating row colors) isn’t visible. Rather, the table will display the previously applied yellow background. To make table styles visible with this table, you need to remove the manually applied background cell colors. Select the entire table and then choose Home ➪ Font ➪ Fill Color ➪ No Fill. You can apply any type of formatting to a table, and that formatting will override the table style format- ting. For example, you may want to make a particular cell stand out by using a different fill color. 127

Part I: Getting Started with Excel Adding Borders and Lines Borders (and lines within the borders) are another visual enhancement that you can add around groups of cells. Borders are often used to group a range of similar cells or to delineate rows or col- umns. Excel offers 13 preset styles of borders, as you can see in the Home ➪ Font ➪ Borders drop- down list shown in Figure 6.9. This control works with the selected cell or range and enables you to specify which, if any, border style to use for each border of the selection. FIGURE 6.9 Use the Borders drop-down list to add lines around worksheet cells. You may prefer to draw borders rather than select a preset border style. To do so, use the Draw Border or Draw Border Grid command from the Home ➪ Font ➪ Borders drop-down list. Selecting either command lets you create borders by dragging your mouse. Use the Line Color or Line Style commands to change the color or style. When you’re finished drawing borders, press Esc to cancel the border-drawing mode. 128

Chapter 6: Worksheet Formatting Another way to apply borders is to use the Border tab of the Format Cells dialog box, which is shown in Figure 6.10. One way to display this dialog box is to select More Borders from the Borders drop-down list. FIGURE 6.10 Use the Border tab of the Format Cells dialog box for more control over cell borders. Before you display the Format Cells dialog box, select the cell or range to which you want to add borders. First, choose a line style and then choose the border position for the line style by clicking one of the Border icons (these icons are toggles). Notice that the Border tab has three preset icons, which can save you some clicking. If you want to remove all borders from the selection, click None. To put an outline around the selection, click Outline. To put borders inside the selection, click Inside. Excel displays the selected border style in the dialog box; there is no live preview. You can choose different styles for different border positions; you can also choose a color for the border. Using this dialog box may require some experimentation, but you’ll get the hang of it. When you apply diagonal lines to a cell or range, the selection looks like it has been crossed out. Tip If you use border formatting in your worksheet, you may want to turn off the grid display to make the borders more pronounced. Choose View ➪ Show ➪ Gridlines to toggle the gridline display. n 129

Part I: Getting Started with Excel Adding a Background Image to a Worksheet Excel also enables you to choose a graphics file to serve as a background for a worksheet. This effect is similar to the wallpaper that you may display on your Windows desktop or as a back- ground for a Web page. To add a background to a worksheet, choose Page Layout ➪ Page Setup ➪ Background. Excel dis- plays a dialog box that enables you to select a graphics file; all common graphic file formats are supported. When you locate a file, click Insert. Excel tiles the graphic across your worksheet. Some images are specifically designed to be tiled, such as the one shown in Figure 6.11. This type of image is often used for Web page backgrounds, and it creates a seamless background. FIGURE 6.11 You can add almost any image file as a worksheet background image. You also want to turn off the gridline display because the gridlines show through the graphic. Some backgrounds make viewing text difficult, so you may want to use a solid background color for cells that contain text. Keep in mind that using a background image will increase the size of your workbook. This may be a consideration if you plan to e-mail the workbook to others. 130

Chapter 6: Worksheet Formatting Note The graphic background on a worksheet is for onscreen display only — it isn’t printed when you print the worksheet. n Copying Formats by Painting Perhaps the quickest way to copy the formats from one cell to another cell or range is to use the Format Painter button (the button with the paintbrush image) of the Home ➪ Clipboard group. 1. Select the cell or range that has the formatting attributes you want to copy. 2. Click the Format Painter button. The mouse pointer changes to include a paintbrush. 3. Select the cells to which you want to apply the formats. 4. Release the mouse button, and Excel applies the same set of formatting options that were in the original range. If you double-click the Format Painter button, you can paint multiple areas of the worksheet with the same formats. Excel applies the formats that you copy to each cell or range that you select. To get out of Paint mode, click the Format Painter button again (or press Esc). Using Named Styles for Easier Formatting One of the most underutilized features in Excel is named styles. Named styles make it very easy to apply a set of predefined formatting options to a cell or range. In addition to saving time, using named styles also helps to ensure a consistent look. A style can consist of settings for up to six different attributes: l Number format l Font (type, size, and color) l Alignment (vertical and horizontal) l Borders l Pattern l Protection (locked and hidden) The real power of styles is apparent when you change a component of a style. All cells which use that named style automatically incorporate the change. Suppose that you apply a particular style to a dozen cells scattered throughout your worksheet. Later, you realize that these cells should have a font size of 14 pt rather than 12 pt. Rather than change each cell, simply edit the style. All cells with that particular style change automatically. 131

Part I: Getting Started with Excel Applying styles Beginning with Excel 2007, the style feature has been revamped significantly, and Excel now includes a good selection of predefined named styles that work in conjunction with document themes. Figure 6.12 shows the effect of choosing Home ➪ Styles ➪ Cell Styles. Note that this dis- play is a live preview: that is, as you move your mouse over the style choices, the selected cell or range temporarily displays the style. When you see a style you like, click it to apply the style to the selection. Note If Excel’s window is wide enough, you won’t see the Cell Styles command in the Ribbon. Rather, you will see four or more formatted style boxes. Click the drop-down arrow to the right of these boxes to display all the defined styles. n FIGURE 6.12 Excel displays samples of available cell styles. Note By default, all cells use the Normal style. If you modify the Normal style, all cells that haven’t been assigned a different style will reflect the new formatting. n After you apply a style to a cell, you can apply additional formatting to it by using any formatting method discussed in this chapter. Formatting modifications that you make to the cell don’t affect other cells that use the same style. You have quite a bit of control over styles. In fact, you can do any of the following: l Modify an existing style. l Create a new style. l Merge styles from another workbook into the active workbook. 132

Chapter 6: Worksheet Formatting The following sections describe these procedures. Modifying an existing style To change an existing style, choose Home ➪ Styles ➪ Cell Styles. Right-click the style you want to modify and choose Modify from the shortcut menu. Excel displays the Style dialog box, shown in Figure 6.13. In this example, the Style dialog box shows the settings for the Office theme Normal style — which is the default style for all cells. The style definitions vary, depending on which doc- ument theme is active. FIGURE 6.13 Use the Style dialog box to modify named styles. Here’s a quick example of how you can use styles to change the default font used throughout your workbook. 1. Choose Home ➪ Styles ➪ Cell Styles. Excels displays the list of styles for the active workbook. 2. Right-click Normal and choose Modify. Excel displays the Style dialog box (Figure 6-13), with the current settings for the Normal style. 3. Click the Format button. Excel displays the Format Cells dialog box. 4. Click the Font tab and choose the font and size that you want as the default. 5. Click OK to return to the Style dialog box. 6. Click OK again to close the Style dialog box. The font for all cells that use the Normal style changes to the font that you specified. You can change any formatting attributes for any style. 133

Part I: Getting Started with Excel Creating new styles In addition to using Excel’s built-in styles, you can create your own styles. This flexibility can be quite handy because it enables you to apply your favorite formatting options very quickly and consistently. To create a new style, follow these steps: 1. Select a cell and apply all the formatting that you want to include in the new style. You can use any of the formatting that is available in the Format Cells dialog box (refer to Figures 6-3 and 6-5). 2. After you format the cell to your liking, choose Home ➪ Styles ➪ Cell Styles, and choose New Cell Style. Excel displays its Style dialog box (refer to Figure 6-13), along with a proposed generic name for the style. Note that Excel displays the words By Example to indicate that it’s basing the style on the current cell. 3. Enter a new style name in the Style Name field. The check boxes display the current formats for the cell. By default, all check boxes are selected. 4. (Optional) If you don’t want the style to include one or more format categories, remove the check(s) from the appropriate check box(es). 5. Click OK to create the style and to close the dialog box. After you perform these steps, the new custom style is available when you choose Home ➪ Styles ➪ Cell Styles. Custom styles are available only in the workbook in which they were created. To copy your custom styles to another workbook, see the section that follows. Note The Protection option in the Style dialog box controls whether users will be able to modify cells for the selected style. This option is effective only if you’ve also turned on worksheet protection, by choosing Review ➪ Changes ➪ Protect Sheet. n Merging styles from other workbooks Custom styles are stored with the workbook in which they were created. If you’ve created some custom styles, you probably don’t want to go through all the work to create copies of those styles in each new Excel workbook. A better approach is to merge the styles from a workbook in which you previously created them. To merge styles from another workbook, open both the workbook that contains the styles that you want to merge and the workbook into which you want to merge styles. From the workbook into which you want to merge styles, choose Home ➪ Styles ➪ Cell Styles and choose Merge Styles. Excel displays the Merge Styles dialog box that shows a list of all open workbooks. Select the workbook that contains the styles you want to merge and click OK. Excel copies styles from the workbook that you selected into the active workbook. 134

Chapter 6: Worksheet Formatting Tip You may want to create a master workbook that contains all your custom styles so that you always know which workbook to merge styles from. n Controlling styles with templates When you start Excel, it loads with several default settings, including the settings for stylistic formatting. If you spend a lot of time changing the default elements, you should know about templates. Here’s an example. You may prefer that gridlines aren’t displayed in worksheets. And maybe you prefer Wrap Text to be the default setting for alignment. Templates provide an easy way to change defaults. The trick is to create a workbook with the Normal style modified to the way that you want it. Then, save the workbook as a template in your XLStart folder. After doing so, you choose Office ➪ New to display a dialog box from which you can choose the template for the new work- book. Template files also can store other named styles, providing you with an excellent way to give your workbooks a consistent look. Cross-Reference Chapter 8 discusses templates in detail. n Understanding Document Themes To help users create more professional-looking documents, the Office designers incorporated a concept known as document themes. Using themes is an easy (and almost foolproof) way to specify the colors, fonts, and a variety of graphic effects in a document. And best of all, changing the entire look of your document is a breeze. A few mouse clicks is all it takes to apply a different theme and change the look of your workbook. Importantly, the concept of themes is incorporated into other Office 2010 (and Office 2007) appli- cations. Therefore, a company can easily create a standard look and feel for all its documents. Note Themes don’t override specific formatting that you apply. For example, assume that you apply the Accent 1– named style to a range. Then you use the Fill Color control to change the background color of that range. If you change to a different theme, the manually applied fill color will not be modified. Bottom line? If you plan to take advantage of themes, stick with default formatting choices. n Figure 6.14 shows a worksheet that contains a SmartArt diagram, a table, a chart, and range for- matted with the Heading 1–named style. These items all use the default theme, which is the Office Theme. 135

Part I: Getting Started with Excel FIGURE 6.14 The elements in this worksheet use the default theme. Figure 6.15 shows the same worksheet after applying a different document theme. The different theme changed the fonts, colors (which may not be apparent in the figure), and the graphic effects for the SmartArt diagram. FIGURE 6.15 The worksheet, after applying a different theme. 136

Chapter 6: Worksheet Formatting Applying a theme Figure 6.16 shows the theme choices that appear when you choose Page ➪ Layout ➪ Themes ➪ Themes. This display is a live preview. (While you move your mouse over the theme choices, the active worksheet displays the theme.) When you see a theme you like, click it to apply the theme to all worksheets in the workbook. Note A theme applies to the entire workbook. You can’t use different themes on different worksheets within a workbook. n FIGURE 6.16 Built-in Excel theme choices. When you specify a particular theme, the gallery choices for various elements reflect the new theme. For example, the chart styles that you can choose from vary, depending on which theme is active. Because themes use different fonts and font sizes, changing to a different theme may affect the lay- out of your worksheet. For example, after applying a new theme, a worksheet that printed on a single page may spill over to a second page. Therefore, you may need to make some adjustments after you apply a new theme. 137

Part I: Getting Started with Excel Customizing a theme Notice that the Themes group on the Page Layout tab contains three other controls: Colors, Fonts, and Effects. You can use these controls to change just one of the three components of a theme. For example, if you like the Urban theme but would prefer different fonts, apply the Urban theme and then specify your preferred font set by choosing Page Layout ➪ Themes ➪ Font. Each theme uses two fonts (one for headers, and one for the body), and in some cases, these two fonts are the same. If none of the theme choices is suitable, choose Page Layout ➪ Themes ➪ Font ➪ Create New Theme Fonts to specify the two fonts you prefer (see Figure 6.17). FIGURE 6.17 Use this dialog box to specify two fonts for a theme. Tip When you choose Home ➪ Fonts ➪ Font, the two fonts for the current theme are listed first in the drop- down list. n Choose Page Layout ➪ Themes ➪ Colors to select a different set of colors. And, if you’re so inclined, you can even create a custom set of colors by choosing Page Layout ➪ Themes ➪ Colors ➪ Create New Theme Colors. This command displays the Create New Theme Colors dialog box, shown in Figure 6.18. Note that each theme consists of 12 colors. Four of the colors are for text and back- grounds, six are for accents, and two are for hyperlinks. As you specify different colors, the pre- view panel in the dialog box updates. 138

Chapter 6: Worksheet Formatting FIGURE 6.18 If you’re feeling creative, you can specify a set of custom colors for a theme. Note Theme effects operate on graphic elements, such as SmartArt, Shapes, and charts. You can’t customize theme effects. n If you’ve customized a theme using different fonts or colors, you can save the new theme by choos- ing Page Layout ➪ Themes ➪ Save Current Theme. Your customized themes appear in the theme list in the Custom category. Other Office applications, such as Word and PowerPoint, can use these theme files. 139



CHAPTER Understanding Excel Files xcel, of course, uses files to store its workbooks. This chapter describes the operations that you perform with workbook files: open- IN THIS CHAPTER E ing, saving, closing, deleting, and so on. It discusses how Excel uses Creating a new workbook files and provides an overview of the various types of files. Most of the file operations discussed here occur in the new Backstage View, the screen that Opening an existing workbook you see when you click File on the Excel Ribbon. Saving and closing workbooks This chapter also discusses the Excel 2007 and Excel 2010 file formats and Sharing workbooks with those describes how to determine what (if anything) will be lost if you save your who use an older version of workbook in an earlier file format. Excel As you read through this chapter, remember that you can have any number of workbooks open simultaneously, and that only one workbook is the active workbook at any given time. The workbook’s name appears in its title bar (or in the Excel title bar if the workbook is maximized). Creating a New Workbook When you start Excel normally, it automatically creates a new (empty) work- book called Book1. This workbook exists only in memory and has not been saved to disk. By default, this workbook consists of three worksheets named Sheet1, Sheet2, and Sheet3. If you’re starting a project from scratch, you can use this blank workbook. 141

Part I: Getting Started with Excel While you’re working in Excel, you can create a new (empty) workbook at any time. Excel pro- vides two ways to create a new workbook: l Choose File ➪ New, which displays a screen like the one shown see Figure 7.1. This screen lets you create a blank workbook, a workbook based on a template, or a workbook based on an existing workbook. To create a new blank workbook, select Blank Workbook and then click the Create button. l Press Ctrl+N. This shortcut is the fastest way to start a new workbook if you’re not using a template. FIGURE 7.1 Choosing File ➪ New presents you with options for creating a new workbook. Cross-Reference See Chapter 8 for more information about using and creating templates. n 142

Chapter 7: Understanding Excel Files Starting Excel without an Empty Workbook If you prefer to avoid the empty workbook displayed when Excel starts up, edit the command line used to start Excel. You need to create a new shortcut to excel.exe and then modify the properties: 1. Use Windows Explorer and locate the excel.exe program. The default location is C:\Program Files\Microsoft Office\Office14\ 2. Pressing and holding the right mouse button, drag the excel.exe filename (or icon) to your desktop and then release the mouse button. 3. From the shortcut menu that appears, choose Create Shortcuts Here. Windows creates a new shortcut icon on your desktop. 4. Right-click the shortcut icon and choose Properties. 5. In the Properties dialog box that appears, click the Shortcut tab. 6. Edit the Target field by adding a space, followed by /e, to the end. For example: “C:\Program Files\Microsoft Office\Office14\EXCEL.EXE” /e 7. (Optional) Specify a shortcut key. If you provide a shortcut key, you can use that keystroke combination to start or active Excel. 8. Click OK. After making that change, Excel doesn’t display an empty workbook when you start it by clicking that shortcut icon. In addition, you won’t see the normal “splash” screen. Opening an Existing Workbook Following are some of the ways to open a workbook saved on your hard drive: l Choose File ➪ Recent and then select the file you want from the Recent Workbooks list. Only the most recently used files are listed. You can specify the number of files to display (maximum of 50) in the Advanced section of the Excel Options dialog box. l Locate the Excel workbook file via a Windows Explorer file list. Just double-click the file- name (or icon), and the workbook opens in Excel. If Excel is not running, Windows auto- matically starts Excel and loads the workbook file. l Use either of the following methods to display the Open dialog box: l Choose File ➪ Open. l Press Ctrl+O. From this dialog box, you can browse your computer for workbooks. 143

Part I: Getting Started with Excel Tip When you choose File ➪ Recent, each file in the recent workbooks list displays a pushpin icon on the right. Click the pushpin icon, and that file becomes “pinned” to the list and will always appear at the top of the list. This handy feature ensures that important files always appear on the recent workbooks list — even if you haven’t opened the file recently. Also, notice that you can right-click a workbook in the list and choose Remove from List. Or, choose Clear Unpinned Items to clear the list and start fresh. n The Open dialog box, shown in Figure 7.2, is resizable. To make it larger or smaller, click the lower-right corner and drag. Also, notice that it’s virtually identical to a Windows Explorer win- dow. The only difference is that it includes additional controls at the bottom. Note The appearance of the Open dialog box varies, depending on the version of Windows you use. The dialog box in Figure 7.2 shows the Open dialog box when running Windows Vista. n FIGURE 7.2 Use the Open dialog box to open any of your Excel workbook files. To open a workbook from the Open dialog box, use the folder tree display on the left to locate the folder that contains the file, and then select the workbook file from the list on the right. After you locate and select the file, click Open, and the file opens. Or, just double-click the filename to open it. 144

Chapter 7: Understanding Excel Files About Protected View New Feature Protected View is a new feature in Excel 2010. n One of the new features in Excel 2010 is Protected View. Although it might seem like Excel is trying to keep you from opening your own files, protected view is all about protecting you from malware. Malware refers to something that can harm your system. Hackers have figured out several way to manipulate Excel files in a way that harmful code can be executed. Protected View essentially prevents these types of attacks by opening a file in a protected environment (a “sandbox”). If you use Excel 2010 to open an Excel workbook that you downloaded from the Web, you’ll see a colorful message above the Formula bar. In addition, the Excel title bar displays [Protected View]. If you are certain that the file is safe, click Enable Editing. If you don’t enable editing, you will be able to view the contents of the workbook, but you won’t be able to make any changes to it. If the workbook contains macros, you’ll see another message after you enable editing: Security Warning. Macros have been disabled. If you are sure that the macros are harmless, click Enable Content. Protected View kicks in for the following: l Files downloaded from the Internet l Attachments opened from Outlook 2010 l Files open from potentially unsafe locations, such as your Temporary Internet Files folder l File that are blocked by File Block Policy (a feature that allows administrators to define poten- tially dangerous files) l Files that were digitally signed, but the signature has expired In some situations, you don’t care about working with the document. You just want to print it. In that case, choose File ➪ Print, and then click the Enable Printing button. It would be nice if copying were allowed in Protected View — but it’s not. The only way to copy infor- mation from a workbook in Protected View is to enable editing. You have some control over how Protected View works. To change the settings, choose File ➪ Options, and click Trust Center. Then click the Trust Center Settings button and click the Protected View tab in the Trust Center dialog box. Notice that the Open button is actually a drop-down list. Click the arrow, and you see the addi- tional options: l Open: Opens the file normally. l Open Read-Only: Opens the selected file in read-only mode. When a file is opened in this mode, you can’t save changes with the original filename. 145

Part I: Getting Started with Excel l Open as Copy: Opens a copy of the selected file. If the file is named budget.xlsx, the workbook that opens is named Copy(1)budget.xlsx. l Open in Browser: Opens the file in your default Web browser. If the file can’t be opened in a browser, this option is disabled. l Open in Protected View: Opens the file in a special mode that does not allow editing. In this view, most of the Excel Ribbon commands are disabled. Read more about this new feature in the sidebar, “About Protected View.” l Open and Repair: Attempts to open a file that may be damaged and recover information contained in it. Tip You can hold down the Ctrl key and select multiple workbooks. When you click Open, all the selected work- book files open. n Right-clicking a filename in the Open dialog box displays a shortcut menu with many extra Windows commands. For example, you can copy, delete, or rename the file, modify its properties, and so on. Using the Favorite Links Your Favorite Links list appears at the top of the left panel of the Open dialog box. It contains a list of shortcuts to folders on your system. Initially, it’s just a partial list of folders, but you can click More to expand the list. To add a new folder to the Favorite Links section, click the folder in the Open dialog box and drag it into the Favorite Links area. Customizing the Favorite Links section by adding shortcuts to fold- ers that you use frequently can save you lots of clicking. Filtering filenames At the bottom of the Open dialog box is a button with a drop-down list. When the Open dialog box is displayed, this button shows All Excel Files (and a long list of file extensions). The Open dialog box displays only those files that match the extensions. In other words, you see only stan- dard Excel files. If you want to open a file of a different type, click the arrow in the drop-down list and select the file type that you want to open. This changes the filtering and displays only files of the type that you specify. You can also type a filter directly in the File Name box. For example, typing the following will dis- play only files that have an .xlsx extension (press Enter after typing the filter): *.xlsx. 146

Chapter 7: Understanding Excel Files Opening Workbooks Automatically Many people work on the same workbooks each day. If this describes you, you’ll be happy to know that Excel can open specific workbook files automatically whenever you start Excel. Any workbooks placed in the XLStart folder open automatically. The location of the XLStart folder varies, depending on your Windows version. To determine the loca- tion of the XLStart folder on your system 1. Choose File ➪ Options and click Trust Center. 2. Click the Trust Center Settings button. 3. In the Trust Center dialog box, click the Trusted Locations tab. You’ll see a list of trusted locations. 4. Look for the path for the location described as User Startup. The path might look something like this: C:\Users\<username>\AppData\Roaming\Microsoft\Excel\XLSTART\ Another XLStart folder may be located here: C:\Program Files\Microsoft Office\Office14\XLStart\ Any workbook files (excluding template files) stored in either of these XLStart folders open automati- cally when Excel starts. If one or more files open automatically from an XLStart folder, Excel won’t start with a blank workbook. You can specify an alternate startup folder in addition to the XLStart folder. Choose File ➪ Options and select the Advanced tab. Scroll down to the General section and enter a new folder name in the At Startup, Open All Files In field. Then, when you start Excel, it automatically opens all workbook files in both the XLStart folders and the alternate folder that you specified. Choosing your file display preferences The Open dialog box can display your workbook filenames in several different styles: as a list, with complete details, as icons, and so on. You control the style by clicking the Views icon and then selecting from the drop-down list. The style that you choose is entirely up to you. Saving a Workbook When you’re working in Excel, your workbook is vulnerable to day-ruining events such as power failures and system crashes. Therefore, you should save your work often. Saving a file takes only a few seconds, but re-creating hours of lost work takes many hours. 147

Part I: Getting Started with Excel Excel provides four ways to save your workbook: l Click the Save icon on the Quick Access toolbar. l Press Ctrl+S. l Press Shift+F12. l Choose File ➪ Save. Caution Saving a file overwrites the previous version of the file on your hard drive. If you open a workbook and then completely mess it up, don’t save the file! Instead, close the workbook without saving it and then reopen the good copy on your hard drive. n If your workbook has already been saved, it’s saved again using the same filename. If you want to save the workbook to a new file, choose File ➪ Save As (or press F12). If your workbook has never been saved, its title bar displays a default name, such as Book1 or Book2. Although Excel allows you to use these generic workbook names for filenames, you’ll be better off using more descriptive filenames. Therefore, the first time that you save a new workbook, Excel displays the Save As dialog box to let you provide a more meaningful name. The Save As dialog box is similar to the Open dialog box. Select the desired folder in the folder list on the left. After you select the folder, enter the filename in the File Name field. You don’t need to specify a file extension — Excel adds it automatically, based on the file type specified in the Save as Type field. By default, files are saved in the standard Excel file format, which uses an .xlsx file extension. Tip To change the default file format for saving files, access the Excel Options dialog box. Click the Save tab and change the setting for the Save Files in This Format option. For example, if your workbooks must be compatible with older versions of Excel, you can change the default format to Excel 97-2003 Workbook (*.xls). Doing so eliminates the need to select the older file type every time you save a new workbook. n Caution If your workbook contains VBA macros, saving it with an .xlsx file extension will erase all the macros. It must be saved with an .xlsm extension (or saved in the XLS or XLSB format)). If your workbook has macros, Excel will still propose to save it as an XLSX file. It other words, Excel suggests a file format that will destroy your macros! It will, however, warn you that the macros will be lost. n If a file with the same name already exists in the folder that you specify, Excel asks whether you want to overwrite that file with the new file. Be careful: You can’t recover the previous file after you overwrite it. 148

Chapter 7: Understanding Excel Files Using AutoRecover If you’ve used computers for any length of time, you’ve probably lost some work. You forgot to save a file, or maybe the power went out and your unsaved work was lost. Or maybe you were working on something and didn’t think it was important, so you closed it without saving — and later realized that it was important. A new feature in Excel 2010 called Versions might make these types of “D’oh!” moments less frequent. As you work in Excel, your work is periodically saved, automatically. It happens in the back- ground so you don’t even know that it’s happening. What’s new in Excel 2010 is that you can access these autosaved versions of your work. And this even applies to workbooks that you never explicitly saved. The Versions feature consists of two components: l Versions of a workbook are saved automatically, and you can view them. l Workbooks that you closed without saving are saved as draft versions. Recovering versions of the current workbook To see whether any previous versions of the current workbook are available, choose File ➪ Info. The Versions section Versions lists the available old versions (if any) of the current workbook. In some cases, more than one autosaved version will be listed. In other cases, no autosaved versions will be available. You can open an autosaved version by clicking its name. Remember that opening an autosaved version won’t automatically replace the current version of your workbook. Therefore, you can decide whether the autosaved version is preferable to the current version. Or, you can just copy some information that may have been accidentally deleted, and paste it to your current workbook. When you close the workbook, the autosaved versions are deleted. Recovering unsaved work When you close a workbook without saving your changes, Excel asks whether you’re sure. If that unsaved workbook has an autosaved version, the “Are you sure?” dialog box informs you of that fact. To recover a workbook that you closed without saving, choose File ➪ Info ➪ Versions, and choose Recover Draft Versions. You’ll see a list of all draft versions of your workbooks. You can open them and (hopefully) recover something that you needed. These drafts are also listed in the recent file list, displayed when you choose File ➪ Recent. Draft versions are deleted after four days, or until you edit the file. 149

Part I: Getting Started with Excel Filenaming Rules Excel workbook files are subject to the same rules that apply to other Windows files. A filename can be up to 255 characters, including spaces. This length enables you to give meaningful names to your files. You can’t, however, use any of the following characters in your filenames: \ (slash) ? (question mark) : (colon) * (asterisk) “ (quote) < (less than) > (greater than) | (vertical bar) You can use uppercase and lowercase letters in your names to improve readability. The filenames aren’t case sensitive. For example, My 2011 Budget.xlsx and MY 2011 BUDGET.xlsx are equivalent names. Note You can adjust the Versions feature in the Save tab of the Excel Options dialog box. For example, you can change the autosave time interval (the default is 10 minutes), turn off autosave for a particular workbook, or disable this feature for all workbooks. n Specifying a Password In some cases, you may want to specify a password for your workbook. When a user attempts to open a password-protected workbook, a password must be entered before the file is opened. To set a password for a workbook, choose File ➪ Info, and click the Protect Workbook button. This button displays some additional options. Choose Encrypt With Password. Excel displays the Encrypt Document dialog box, shown in Figure 7.3. Enter the password, and then enter it again. Click OK, and save the workbook. When you re-open the workbook, you will be prompted for a password. FIGURE 7.3 The Encrypt Document dialog box is where you specify a password for your workbook. 150

Chapter 7: Understanding Excel Files Caution Passwords are case sensitive. Be careful with this option because it is impossible to open the workbook (using normal methods) if you forget the password. Also, remember that Excel passwords can be cracked, so it’s not a perfect security measure. n Organizing Your Files If you have hundreds of Excel files, you might have a problem locating the workbook that you need. Using descriptive filenames can help, and using folders and subfolders (with descriptive names) also makes it easier to find the particular file you need. In some cases, though, that’s not enough. Fortunately, Excel lets you assign a variety of descriptive information (sometimes known as meta- data) to a workbook. These are known as document properties. This information includes such items as the author, title, subject, and so on. When you choose File ➪ Info you can view (or modify) the document properties for the active workbook. This information is shown on the right side of the screen (see Figure 7.4). Tip If you aren’t seeing all the properties for your workbook, click the down-arrow on Properties and select Show All Properties Another option on the Document Properties drop-down list is Show Document Panel. Choosing this command displays the properties in a panel below the Ribbon. n When you use the Open dialog box, you can specify additional columns to display. Start by click- ing Views (at the top of the Open dialog box) and then choose Details. Right-click the column header to see a list of other properties to include. Click the More option for a longer list of proper- ties (see Figure 7.5). You can sort the file list in the Open dialog box by a particular column by clicking the column heading. Also, notice that each heading is actually a drop-down list. Click the drop-down arrow, and you can filter the list to show only files that match the selected properties. Using document properties lets you work with files as if they were in a database. The key, of course, is taking the time to ensure that the document properties are actually assigned, and are accurate. Tip If you would like to be prompted to ensure that the document properties are correct, choose Developer ➪ Modify ➪ Document Panel. In the Document Information Panel dialog box, select the Always Show Document Information Panel on Document Open and Initial Save check box. If the Developer tab isn’t visible, use the Customize Ribbon panel in the Excel Options dialog box to display it. n 151

Part I: Getting Started with Excel FIGURE 7.4 Use Properties to store additional information about your workbook. FIGURE 7.5 The Choose Details dialog box, where you choose additional properties to display in the file list. 152

Chapter 7: Understanding Excel Files Using Workspace Files If you have a project that uses multiple workbooks, you probably get tired of opening the same files every time you work on the project. The solution? Create a workspace file: 1. Open all the workbooks used for your project. 2. Arrange the workbook windows the way you like them. 3. Choose View ➪ Window ➪ Save Workspace to display the Save Workspace dialog box. 4. Excel proposes the name resume.xlw, but you can specify any name you like. Just make sure that you use the XLW extension. 5. Click Save. The workspace file is created. After creating a workspace file, you can open it by choosing File ➪ Open. In the Open dialog box, specify Workspaces (*.xlw) from the Files of Type drop-down list. Better yet, pin it to the top of the recent files list so it will always be easily accessible. Note A workspace file contains only the filenames and window position information — not the workbooks. Make sure that you save the workbooks that comprise the workspace. n Note This discussion of document properties just barely scratches the surface. For example, you can display addi- tional properties and even specify a custom document information panel template that contains information specific to your needs. A complete discussion is beyond the scope of this book. n Other Workbook Info Options The Info pane of Backstage View displays more file-related options, as shown in Figure 7.6. To dis- play this pane, choose File ➪ Info. These options, described in the following sections, may be use- ful if you plan to distribute your workbook to others. Note that not all workbooks display all the options shown in Figure 7.6. Only the relevant options are shown. Security Warning section If the active workbook displayed a security warning when you opened it, the Info pane will display a Security Warning section, with an Enable Content button. Use this panel to find out more about why Excel triggered the security warning, and to enable the content if you’re sure that it’s safe to do so. 153

Part I: Getting Started with Excel FIGURE 7.6 Choose File ➪ Info for additional options. Compatibility Mode section If the active workbook is an old workbook opened in compatibility mode, you’ll see the Compatibility Mode section in the Info pane. To convert the workbook to the Excel 2007/2010 format, click the Convert button. Caution Be aware that this command deletes the original version of the file — which seems like a rather drastic mea- sure. It’s probably wise to make a copy of your workbook before you use this command. n Permissions section In the Permissions section of the Info pane, click the Protect Workbook button to display the fol- lowing options: 154

Chapter 7: Understanding Excel Files l Mark as Final Use this option to designate the workbook as “final.” The document is saved as a read- only file to prevent changes. This isn’t a security feature. Rather, the Mark as Final com- mand is useful to let others know that you are sharing a completed version of a workbook. l Encrypt with Password Use this command to specify a password that is required to open the workbook. See “Specifying a password,” earlier in this chapter. l Protect Current Sheet This command lets you protect various elements of a worksheet. It displays the same dia- log box as the Review ➪ Changes ➪ Protect Sheet command. See Chapter 31 for more information about protecting worksheets. l Protect Workbook Structure This command lets you protect the structure of a workbook. It displays the same dialog box as Review ➪ Changes ➪ Protect Workbook. See Chapter 31 for more information for more information about protecting the structure of a workbook. l Restrict Permission by People Use this option to specify those who may open the document. This feature requires a fee- based service called Information Rights Management. l Add a Digital Signature This command allows you to “sign” a workbook digitally. See Chapter 31 for more infor- mation about digital signatures. Prepare for Sharing section The Prepare for Sharing section of the Info pane contains a Check for Issues button. When clicked, the button displays three options: l Inspect Document This command displays the Document Inspector dialog box. This feature can alert you to some potentially private information that may be contained in your workbook — perhaps information that’s contained in hidden rows or columns or hidden worksheets. If you plan on making a workbook available to a large audience, it’s an excellent idea to use the Document Inspector for a final check. l Check Accessibility This command checks the workbook for potential problems that might occur for people with disabilities. The results of the check are displayed in a task pane in the workbook. This feature works only with Excel 2007/2010 workbooks. 155

Part I: Getting Started with Excel l Check Compatibility This command is useful if you need to save your workbook in an older file format. It dis- plays a very helpful Compatibility Checker dialog box that lists potential compatibility problems. This dialog box also appears when you save a workbook using an older file for- mat. For more information, see “Excel File Compatibility,” later in this chapter. Versions section Clicking the Manage Versions button of the Versions section of the Info pane displays two options: l Recover Draft Versions This command displays the Open dialog box, pointed to the folder where Excel displays AutoRecover versions of workbooks that were not saved. If you accidentally forget to save a file, this command may help you out. l Delete All Draft Versions This command simply deletes all unsaved files on your drive. Alternatively, you can delete the files manually. Closing Workbooks After you’re finished with a workbook, you can close it to free the memory that it uses. You can close a workbook by using any of the following methods: l Choose File ➪ Close. l Click the Close button (the X) in the workbook’s title bar. l Double-click the Excel icon on the left side of the workbook’s title bar. This icon is visible only if the workbook window is not maximized. l Press Ctrl+F4. l Press Ctrl+W. If you’ve made any changes to your workbook since it was last saved, Excel asks whether you want to save the changes to the workbook before closing it. Tip When you close Excel, you are prompted to save each workbook that hasn’t been saved. The dialog box has a Save All button, but it doesn’t have a Don’t Save Anything button. If you’d like a quick exit, without saving any workbooks, press Shift while you click the Don’t Save button. Excel shuts down immediately. n 156

Chapter 7: Understanding Excel Files Safeguarding Your Work Nothing is more frustrating than spending hours creating a complicated Excel workbook only to have it destroyed by a power failure, a hard drive crash, or even human error. Fortunately, protect- ing yourself from these disasters is not a difficult task. Earlier in the chapter, I discuss the AutoRecover feature that makes Excel save a backup copy of your workbook at regular intervals (see “Using AutoRecover”). AutoRecover is a good idea, but it certainly isn’t the only backup protection you should use. If a workbook is important, you need to take extra steps to ensure its safety. The following backup options help ensure the safety of indi- vidual files: l Keep a backup copy of the file on the same drive. Although this option offers some protection if you make a mess of the workbook, it won’t do you any good if the entire hard drive crashes. l Keep a backup copy on a different hard drive. This method assumes, of course, that your system has more than one hard drive. This option offers more protection than the preceding method, because the likelihood that both hard drives will fail is remote. If the entire system is destroyed or stolen, however, you’re out of luck. l Keep a backup copy on a network server. This method assumes that your system is con- nected to a server on which you can write files. This method is fairly safe. If the network server is located in the same building, however, you’re at risk if the entire building burns down or is otherwise destroyed. l Keep a backup copy on an Internet backup site. Several Web sites specialize in storing backup files. This is a safe method, as long as the company doesn’t go out of business. l Keep a backup copy on a removable medium. This is the safest method. Using a remov- able medium, such as a USB drive enables you to physically take the backup to another location. So, if your system (or the entire building) is damaged, your backup copy remains intact. Excel File Compatibility It’s important to understand the limitations regarding version compatibility. Even though your col- league is able to open your file, there is no guarantee that everything will function correctly or look the same. Checking compatibility If you save your workbook to an older file format (such as XLS, for versions prior to Excel 2007), Excel automatically runs the Compatibility Checker. The Compatibility Checker identifies the ele- ments of your workbook that will result in loss of functionality or fidelity (cosmetics). 157

Part I: Getting Started with Excel Figure 7.7 shows the Compatibility Checker dialog box. Use the Select Versions to Show button to limit the compatibility checking to a specific version of Excel. FIGURE 7.7 The Compatibility Checker is a useful tool for those who share workbooks with others. The bottom part of the Compatibility Checker lists the potential compatibility problems. To dis- play the results in a more readable format, click the Copy to New Sheet button. Keep in mind that compatibility problems can also occur with Excel 2007 even though that ver- sion uses the same file format as Excel 2010. You can’t expect features that are new to Excel 2010 to work in earlier versions. For example, if you create a workbook with Sparkline charts (a new feature in Excel 2010) and send it to a colleague who uses Excel 2007, the cells that contain the Sparklines will be empty. In addition, formulas that use any of the new worksheet functions will return an error. The Compatibility Checker identifies these types of problems. Perhaps one of the most confusing aspects of Excel is the nearly overwhelming number of file for- mats that it can read and write. With the introduction of Excel 2007, things got even more confus- ing because it has quite a few new file formats. Note Excel 2010 can open all files created with earlier versions of Excel. n 158

Chapter 7: Understanding Excel Files Recognizing the Excel 2010 file formats Excel file formats (which were introduced in Excel 2007) are l XLSX: A workbook file that does not contain macros l XLSM: A workbook file that contains macros l XLTX: A workbook template file that does not contain macros l XLTM: A workbook template file that contains macros l XLSA: An add-in file l XLSB: A binary file similar to the old XLS format but able to accommodate the new features l XLSK: A backup file With the exception of XLSB, these are all “open” XML files, which means that other applications are able to read and write these types of files. Tip XML files are actually Zip-compressed text files. If you rename one of these files to have a ZIP extension, you’ll be able to examine the contents using any of several zip file utilities — including the Zip file support built into Windows. Taking a look at the innards of an Excel workbook is an interesting exercise for curious-minded users. n Saving a file for use with an older version of Excel To save a file for use with an older version of Excel, choose File ➪ Save As. In the Save As dialog box, select one of the following from the Save as Type drop-down: l Excel 97-2003 Workbook (*.xls): If the file will be used by someone who has Excel 97, Excel 2000, Excel 2002, or Excel 2003. l Microsoft Excel 5.0/95 Workbook (*.xls): If the file will be used by someone who has Excel 5 or Excel 95. The Office Compatibility Pack Normally, those who use an earlier version of Excel can’t open workbooks saved in the new Excel file formats. But, fortunately, Microsoft has released a free Compatibility Pack for Office 2003 and Office XP. If an Office 2003 or Office XP user installs the Compatibility Pack, they can open files created in Office 2007 or Office 2010 and also save files in the new format. The Office programs that are affected are Excel, Word, and PowerPoint. This software doesn’t endow the older versions with any new features: It just gives them the capability to open and save files in the new format. To download the Compatibility Pack, search the Web for Office Compatibility Pack. 159



CHAPTER Using and Creating Templates A IN THIS CHAPTER template is essentially a model that serves as the basis for something else. An Excel template is a workbook that’s used to create other workbooks. This chapter discusses some of the templates included with Excel and also describes how to create your own template files. Understanding Excel templates Creating a template takes some time, but in the long run, doing so may save Working with the default you a lot of work. templates Creating custom templates Exploring Excel Templates The best way to become familiar with Excel template files is to jump in and try a few. Excel 2010 gives you quick access to hundreds of template files. Viewing templates To explore the Excel templates, choose File ➪ New to display the Available Templates screen in Backstage View. You can select a template stored on your hard drive, or a template from Microsoft Office Online. If you choose a template from Microsoft Office Online, you must be connected to the Internet to download it. The Office Online Templates section contains a number of icons, which rep- resent various categories of templates (see Figure 8.1). Click an icon, and you’ll see the available templates. When you select a template thumbnail, you see a preview in the right panel. 161

Part I: Getting Started with Excel FIGURE 8.1 The New page in Backstage View displays template categories. Note Microsoft Office Online has a wide variety of templates, and some are better than others. If you download a few duds, don’t give up. Even though a template may not be perfect, you can often modify a template to meet your needs. Modifying an existing template is often easier than creating a workbook from scratch. n Creating a workbook from a template To create a workbook based on a template, just locate the template and click the Create button on the right. If the template is on Microsoft Office Online, the Create button is replaced with a Download button. What you do next depends on the template. Every template is different, but most are self- explanatory. Some workbooks require customization. Just replace the generic information with your own information. Figure 8.2 shows a workbook based on an invoice template. This particular workbook has a few simple formulas that perform calculations using the data that you enter. Figure 8.3 shows the workbook after it has been customized a bit, and some items added. Notice that formulas calculate the subtotal, sales tax, and total. 162