Excel2010 ebook

Chapter 21: Creating Sparkline Graphics FIGURE 21.13 After specifying a date axis, the Sparkline shows the values accurately. Auto-Updating Sparklines If a Sparkline uses data in a normal range of cells, adding new data to the beginning or end of the range does not force the Sparkline to use the new data. You need to use the Edit Sparklines dialog box to update the data range (choose Sparkline Tools ➪ Design ➪ Sparkline ➪ Edit Data). But, if the Sparkline data is in a column within a table (created by using Insert ➪ Tables ➪ Table), then the Sparkline will use new data that’s added to the end of the table. Figure 21.14 shows an example. The Sparkline was created using the data in the Rate column of the table. When you add the new rate for September, the Sparkline will automatically update its Data Range. FIGURE 21.14 Creating a Sparkline from data in a table. 513

Part III: Creating Charts and Graphics Displaying a Sparkline for a Dynamic Range The example in this section describes how to create a Sparkline that display only the most recent data points in a range. Figure 21.15 shows a worksheet that tracks daily sales. The Sparkline, in cell F4, displays only the seven most recent data points in column B. FIGURE 21.15 Using a dynamic range name to display only the last seven data points in a Sparkline. Need More about Sparklines? This chapter describes pretty much everything there is to know about Excel 2010 Sparklines. You may be left asking, Is that all there is? Unfortunately, it is. The Sparklines feature in Excel 2010 certainly leaves much to be desired. For example, you’re limited to three types (Line, Column, and Win/Loss). It would be useful to have access to other Sparkline types, such as a column chart with no gaps, an area chart, and a stacked bar chart. Although Excel provides some basic formatting options, many users would prefer to have more control over the appearance of their Sparklines. If you like the idea of Sparklines — and you’re disappointed by the implementation in Excel 2010 — check out some add-ins that provide Sparklines in Excel. These products provide many additional Sparkline types, and most provide many additional customization options. Search the Web for spark- lines excel, and you’ll find several add-ins to choose from. 514

Chapter 21: Creating Sparkline Graphics I started by creating a dynamic range name. Here’s how: 1. Choose Formulas ➪ Defined Names ➪ Define Name, specify Last7 as the Name, and enter the following formula in the Refers To field: =OFFSET($B$2,COUNTA($B:$B)-7-1,0,7,1) This formula calculates a range by using the OFFSET function. The first argument is the first cell in the range (B2). The second argument is the number of cells in the column (minus the number to be returned and minus 1 to accommodate the label in B1). This name always refers to the last seven non-empty cells in column B. To display a dif- ferent number of data points, change both instances of 7 to a different value. 2. Chose Insert ➪ Sparklines ➪ Line. 3. In the Data Range field, type Last7 (the dynamic range name). Specify cell E4 as the Location Range. The Sparkline shows the data in range B11:B17. 4. Add new data to column B. The Sparkline adjusts to display only the last seven data points. 515



CHAPTER Enhancing Your Work with Pictures and Drawings W hen it comes to visual presentation, Excel has a lot more up its sleeve than charts. As you may know, you can insert a wide IN THIS CHAPTER variety of graphics into your worksheet to add pizzazz to an Inserting and customizing otherwise boring report. Shapes This chapter describes the non–chart-related graphic tools available in Excel. An overview of SmartArt and These tools consist of Shapes, SmartArt, WordArt, and imported or pasted WordArt images. In addition to enhancing your worksheets, you’ll find that working with these objects can be a nice diversion. When you need a break from Working with other types of crunching numbers, you might enjoy creating an artistic masterpiece using graphics Excel’s graphic tools. Using Shapes Microsoft Office, including Excel, provides access to a variety of customiz- able graphic images known as Shapes. You might want to insert shapes to create diagrams, store text, or just add some visual appeal to a worksheet. 517

Part III: Creating Charts and Graphics Inserting a Shape You can add a Shape to a worksheet’s draw layer by choosing Insert ➪ Illustrations ➪ Shapes, which opens the Shapes gallery, shown in Figure 22.1. Shapes are organized into categories, and the category at the top displays the Shapes that you’ve used recently. To insert a Shape on a work- sheet, you can do one of the following: l Click the Shape in the Shapes gallery and then click in the worksheet. A default-sized shape is added to your worksheet. l Click the Shape and then drag in the worksheet to create a larger or smaller Shape, or a Shape with different proportions than the default When you release the mouse button, the object is selected, and its name appears in the Name field (as shown in Figure 22.2). FIGURE 22.1 The Shapes gallery. 518

Chapter 22: Enhancing Your Work with Pictures and Drawings FIGURE 22.2 This Shape was drawn on the worksheet. Its name (Quad Arrow 1) appears in the Name field. Tip You can also insert a Shape into a chart. Just select the chart before you choose the Shape from the gallery and then click inside the chart to insert the Shape. When a chart is selected, the Chart Tools contextual tab also displays an icon to access the Shapes gallery: Chart Tools ➪ Layout ➪ Insert ➪ Shapes. n A few Shapes require a slightly different approach. For example, when adding a FreeForm Shape (from the Lines category), you can click repeatedly to create lines. Or click and drag to create a nonlinear shape. Double-click to finish drawing and create the Shape. The Curve and Scribble Shapes (in the Lines category) also require several clicks while drawing. Here are a few tips to keep in mind when creating Shapes: l Every Shape has a name. Some have generic names like Shape 1 and Shape 2, but others are given more descriptive names (for example, Rectangle 1). To change the name of a Shape, select it, type a new name in the Name field, and press Enter. l To select a specific shape, type its name in the Name field and press Enter. l When you create a Shape by dragging, hold down the Shift key to maintain the object’s default proportions. l You can control how objects appear onscreen in the Advanced tab of the Excel Options dialog box (File ➪ Excel). This setting appears in the Display Options for This Workbook section. Normally, the All option is selected under For Objects Show. You can hide all objects by choosing Nothing (Hide Objects). Hiding objects may speed things up if your worksheet contains complex objects that take a long time to redraw. 519

Part III: Creating Charts and Graphics About the Drawing Layer Every worksheet and chart sheet has a drawing layer. This invisible surface can hold Shapes, SmartArt, WordArt, graphic images, embedded charts, inserted objects, and so on. You can move, resize, copy, and delete objects placed on the drawing layer, with no effect on any other elements in the worksheet. Objects on the drawing layer have properties that relate to how they’re moved and sized when underlying cells are moved and sized. When you right-click a graphic object and choose Size and Properties from the shortcut menu that appears, you get a tabbed dialog box. Click the Properties tab to adjust how the object moves or resizes with its underlying cells. Your choices are as follows: l Move and Size with Cells: If this option is selected, the object appears to be attached to the cells beneath it. For example, if you insert rows above the object, the object moves down. If you increase the column width, the object gets wider. l Move But Don’t Size with Cells: If this option is selected, the object moves whenever rows or columns are inserted, but it never changes its size when you change row heights or column widths. l Don’t Move or Size with Cells: This option makes the object completely independent of the underlying cells. The preceding options control how an object is moved or sized with respect to the underlying cells. Excel also lets you attach an object to a cell. To do so, open the Excel Options dialog box, click the Advanced tab, and select the Cut, Copy, and Sort Inserted Objects With Their Parent Cells check box. After you do so, graphic objects on the drawing layer are attached to the underlying cells. If you copy a range of cells that includes an object, the object is also copied. Note that this is a general option that affects all objects and, by default, this option is enabled. Adding text to a Shape Many Shape objects can display text. To add text to such a Shape, select the Shape and start typing the text. To change the formatting for all text in a Shape, Ctrl-click the Shape object. You can then use the formatting commands on the Home tab of the Ribbon. To change the formatting of specific charac- ters within the text, select only those characters, and use the Ribbon buttons. Or, right-click and use the Mini toolbar to format the text. In addition, you can dramatically change the look of the text by using the tools in the Drawing Tools ➪ Format ➪ WordArt Styles group. You can read more about WordArt later in the chapter. Formatting Shapes When you select a Shape, the Drawing Tools ➪ Format contextual tab is available, with the following groups of commands: 520

Chapter 22: Enhancing Your Work with Pictures and Drawings l Insert Shapes: Insert new Shapes; change a Shape to a different Shape. l Shape Styles: Change the overall style of a Shape; modify the Shape’s fill, outline, or effects. l WordArt Styles: Modify the appearance of the text within a Shape. l Arrange: Adjust the “stack order” of Shapes, align Shapes, group multiple Shapes, and rotate Shapes. l Size: Change the size of a Shape. Additional commands are available from the Shape’s shortcut menu (which you access by right- clicking the Shape). In addition, you can use your mouse to perform some operations directly: for example, resize or rotate a Shape. Selecting and Hiding Objects An easy way to select an object is to use the Selection and Visibility task pane. Just select any Shape and then choose Drawing Tools ➪ Format ➪ Arrange ➪ Selection Pane. Or, choose Home ➪ Editing ➪ Find & Select ➪ Selection Pane. Like with all task panes, you can undock this pane from the side of the window and make it free- floating. The accompanying figure shows the Selection and Visibility task pane as a floating window. Each object on the active worksheet is listed in the Selection and Visibility pane. Just click the object’s name to select it. To select multiple objects, press Ctrl while you click the names. To hide an object, click the “eye” icon to the right of its name. Use the buttons at the bottom of the task pane to quickly hide (or show) all items. 521

Part III: Creating Charts and Graphics Figure 22.3 shows a worksheet with some Shapes that use various types of formatting. As an alternative to the Ribbon, you can use the Format Shape dialog box. Right-click the Shape and choose Format Shape from the shortcut menu. You’ll get a stay-on-top tabbed dialog box that contains some additional formatting options that aren’t on the Ribbon. Changes appear immedi- ately, and you can keep the Format Shape dialog box open while you work. I could probably write 20 pages about formatting Shapes, but it would be a waste of paper and cer- tainly not a very efficient way of learning about Shape formatting. The best way, by far, to learn about formatting Shapes is to experiment. Create some shapes, click some commands, and see what happens. The commands are fairly intuitive, and you can always use Undo if a command doesn’t do what you expected it to do. FIGURE 22.3 A variety of Shapes. Grouping objects Excel lets you combine (or group) two or more Shape objects into a single object. For example, if you create a design that uses four separate Shapes, you can combine them into a group. Then, you can manipulate this group as a single object (move it, resize it, apply formatting, and so on). To group objects, press Ctrl while you click the objects to be included in the group. Then right- click and choose Group ➪ Group from the shortcut menu. When objects are grouped, you can still work with an individual object in the group. Click once to select the group; then click again to select the object. To ungroup a group, right-click the group object and choose Group ➪ Ungroup from the shortcut menu. This command breaks the object into its original components. 522

Chapter 22: Enhancing Your Work with Pictures and Drawings Aligning and spacing objects When you have several objects on a worksheet, you may want to align and evenly space these objects. You can, of course, drag the objects with your mouse (which isn’t very precise). Or, you can use the navigation arrow keys to move a selected object one pixel at a time. The fastest way to align and space objects is to let Excel do it for you. To align multiple objects, start by selecting them (press Ctrl and click the objects). Then use the tools in the Drawing Tools ➪ Format ➪ Arrange ➪ Align drop-down list. Note Unfortunately, you can’t specify which object is used as the basis for the alignment. When you’re aligning objects to the left (or right), they’re always aligned with the leftmost (or rightmost) object that’s selected. When you’re aligning objects to the top (or bottom), they’re always aligned with the topmost (or bottommost) object. Aligning the centers (or middles) of objects will align them along an axis halfway between the left and right (or top and bottom) extremes of the selected shapes. n You can instruct Excel to distribute three or more objects so that they’re equally spaced horizon- tally or vertically. Choose Drawing Tools ➪ Format ➪ Arrange ➪ Align and then select either Distribute Horizontally or Distribute Vertically. Reshaping Shapes Excel has many Shapes to choose from, but sometimes the Shape you need isn’t in the gallery. In such a case, you may be able to modify one of the existing shapes using one of these techniques: l Rotate the Shape. When you select a Shape, it displays a small green dot. Click and drag this dot to rotate the Shape. l Group multiple Shapes. You may be able to create the Shape you need by combining two or more Shapes and then grouping them. (See “Grouping objects,” earlier in this chapter.) l Reconfigure the Shape. Many Shapes display one or more small yellow diamonds when the Shape is selected. You can click and drag this diamond to change the Shape’s outline. The exact behavior varies with the Shape, so you should experiment and see what hap- pens. Figure 22.4 shows six variations of an up-down arrow. This particular shape, when selected, has two yellow diamonds, which allows lots of variations. l Create a Freeform Shape. Select the Freeform Shape (in the Lines category of the Shapes gallery) to create a custom Shape. Figure 22.5 shows a Freeform Shape, with eyes and a mouth added. The shadow effect completes the masterpiece. l Editing a Shape’s Points. Another way to create a unique Shape is to edit its points. Select the Shape and choose Drawing Tools ➪ Format ➪ Insert Shapes ➪ Edit Shape ➪ Edit Points. You can then drag the points to reconfigure the Shape. Figure 22.6 shows an example of a Shape that has been edited beyond all recognition. 523

Part III: Creating Charts and Graphics FIGURE 22.4 Six variations on a Shape. FIGURE 22.5 When none of the existing Shapes will do, create your own Freeform Shape. Printing objects By default, objects are printed along with the worksheet. To avoid printing a Shape, right-click the Shape and choose Size and Properties. In the Format Shape dialog box, click the Properties tab and then clear the Print Object check box. 524

Chapter 22: Enhancing Your Work with Pictures and Drawings Exporting Graphic Objects If you create a graphic in Excel using Shapes, SmartArt, or WordArt, you may want to save the graphic as a separate file for use in another program. Unfortunately, Excel doesn’t provide a direct way to export a graphic, but here’s a trick you can use. Make sure that your graphic appears the way you want it and then follow these steps: 1. Save your workbook. 2. Choose File ➪ Save As to save your workbook as a Web Page. In the Save As dialog box, select Web Page (*.htm; *.html) from the Save as Type drop-down list. 3. Close the workbook. 4. Use Windows Explorer to locate the HTML file you saved in Step 2. You’ll notice that Excel also created a companion directory for the HTML file. If you save the file as myart.htm, the directory will be named myart_files. 5. Open the directory, and you’ll find *.png graphic files — one for each graphic object in your workbook. The *.png files have a transparent background. FIGURE 22.6 A Shape, before and after editing its points. Using SmartArt Excel’s Shapes are certainly impressive, but the SmartArt feature is downright amazing. Using SmartArt, you can insert a wide variety of highly customizable diagrams into a worksheet, and you can change the overall look of the diagram with a few mouse clicks. This feature was introduced in Office 2007, and is probably more useful for PowerPoint users. But many Excel users will be able to make good use of SmartArt. Inserting SmartArt To insert SmartArt into a worksheet, choose Insert ➪ SmartArt. Excel displays the dialog box shown in Figure 22.7. The diagrams are arranged in categories along the left. When you find one 525

Part III: Creating Charts and Graphics that looks appropriate, click it for a larger view in the panel on the right, which also provides some usage tips. Then click OK to insert the graphic. Note Don’t be concerned about the number of elements contained in the SmartArt graphics. You can customize the SmartArt to display the number of elements you need. n FIGURE 22.7 Inserting a SmartArt graphic. Figure 22.8 shows a SmartArt diagram after I customized it and added text. When you insert or select a SmartArt diagram, Excel displays its SmartArt Tools contextual tab, which provides many customization options. FIGURE 22.8 This SmartArt shows a simple organizational chart. 526

Chapter 22: Enhancing Your Work with Pictures and Drawings Customizing SmartArt Figure 22.9 shows a SmartArt graphic (named Vertical Equation, from the Process category) imme- diately after I inserted it into a worksheet. The Type Your Text Here window makes it very easy to enter text into the elements of the image. If you prefer, you can click one of the [Text] areas in the image and type the text directly. FIGURE 22.9 This SmartArt needs to be customized. Figure 22.10 shows the SmartArt after I added some text. This particular diagram depicts two items combining into a third item. Suppose that your boss sees this graphic and tells you that you need a third item: Advanced Technology. To add an ele- ment to the SmartArt graphic, just select an item and choose SmartArt Tools ➪ Design ➪ Create Graphic ➪ Add Shape. Or you can just select an item and press Enter. Figure 22.11 shows the modified SmartArt. FIGURE 22.10 The SmartArt now has text. 527

Part III: Creating Charts and Graphics FIGURE 22.11 The SmartArt, after adding a new element. When working with SmartArt, keep in mind that you can move, resize, or format individually any element within the graphic. Select the element and then use the tools on the SmartArt Tools ➪ Format tab. Changing the layout You can easily change the layout of a SmartArt diagram. Select the object and then choose SmartArt Tools ➪ Design ➪ Layouts. Any text that you’ve entered remains intact. Figure 22.12 shows a few alternate layouts for the previous example. Changing the style After you decide on a layout, you may want to consider other styles or colors available in the SmartArt Tools ➪ Design ➪ SmartArt Styles group. Figure 22.13 shows the diagram after I chose a different style and changed the colors. Tip SmartArt styles available vary depending upon the document theme assigned to the workbook. To change a workbook’s theme, choose Page Layout ➪ Themes ➪ Themes. Switching to a different theme can have a dramatic impact on the appearance of SmartArt diagrams. n 528

Chapter 22: Enhancing Your Work with Pictures and Drawings FIGURE 22.12 A few different layouts for the SmartArt. FIGURE 22.13 A few mouse clicks changed the style of this diagram. Learning more about SmartArt This section provided a basic introduction to SmartArt. The topic is complex enough to warrant an entire book, but I recommend simply experimenting with the commands. 529

Part III: Creating Charts and Graphics Using WordArt WordArt is available in previous versions of Excel, but this feature got a well-needed facelift in Excel 2007. You can use WordArt to create graphical effects in text. Figure 22.14 shows a few examples of WordArt. To insert a WordArt graphic on a worksheet, choose Insert ➪ WordArt and then select a style from the gallery. Excel inserts an object with the placeholder text Your text here. Replace that text with your own, resize it, and apply other formatting if you like. When you select a WordArt image, Excel displays its Drawing Tools contextual menu. Use the controls to vary the look of your WordArt. Note The controls in the Drawing Tools ➪ Format ➪ Shape Styles group operate on the Shape that contains the text — not the text. To apply text formatting, use the controls in the Drawing Tools ➪ Format ➪ WordArt Styles group. You can also use some of the standard formatting controls on the Home tab or the Mini toolbar. In addition, right-click the WordArt and choose Format Text Effects for more formatting options. n FIGURE 22.14 WordArt examples. 530

Chapter 22: Enhancing Your Work with Pictures and Drawings Working with Other Graphic Types Excel can import a wide variety of graphics into a worksheet. You have several choices: l Use the Clip Art task pane to locate and insert an image. l Import a graphic file directly. l Copy and paste an image using the Windows Clipboard. About graphics files Graphics files come in two main categories: bitmap and vector (picture). l Bitmap images are made up of discrete dots. They usually look pretty good at their origi- nal size, but often lose clarity if you increase the size. Examples of common bitmap file formats include BMP, PNG, JPEG, TIFF, and GIF. l Vector-based images, on the other hand, are comprised of points and paths that are rep- resented by mathematical equations, so they retain their crispness regardless of their size. Examples of common vector file formats include CGM, WMF, and EPS. You can find hundreds of thousands of graphics files free for the taking on the Internet. Be aware, however, that some graphic files have copyright restrictions. Caution Using bitmap graphics in a worksheet can dramatically increase the size of your workbook, resulting in more memory usage and longer load and save times. n Using the Clip Art task pane Clip art refers to pre-made images (as opposed to custom image) that are used to illustrate just about any medium. The term originated back in the days when images were actually clipped from books and reused in new print projects. Nowadays, clip art is almost always in electronic form. Want a Great Graphics File Viewer? Many users are content to use the graphics file-viewing capabilities built into Windows. If you do a lot of work with graphics files, though, you owe it to yourself to get a real file-viewing program. Many graphics viewers are available, but one of the best products in its class is IrfanView. It enables you to view just about any graphics file you can find, and it has features and options that will satisfy even hard-core graphics mavens. Best of all, it’s free. To download a copy, visit www.irfanview.com. 531

Part III: Creating Charts and Graphics Microsoft Office includes lots of clip art images, and you access them via the Clip Art task pane. This task pane provides an easy way to locate and insert images into a worksheet. Note The Clip Art task pane also allows you to insert sound and video files, and gives you direct access to the Microsoft Design Gallery Live on the Web. n Display the Clip Art task pane by choosing Insert ➪ Clip Art. You can search for clip art by using the controls at the top of the task pane. Figure 22.15 shows the task pane, along with the thumb- nail images resulting from a search for “people”. To insert an image into the active worksheet, just double-click the thumbnail. For additional options, right-click the thumbnail image. FIGURE 22.15 Use the Clip Art task pane to search for clip art and other multimedia files. If you can’t find a suitable image, go online and browse through the extensive clip art at the Microsoft Clip Gallery Live Web site. Click the Find More at Office.com link (at the bottom of the task pane), and your Web browser will be activated, at which point you can view the images (or listen to the sounds) and add those you want to your Clip Organizer. Inserting graphics files If the graphic image that you want to insert is available in a file, you can easily import the file into your worksheet. Choose Insert ➪ Picture. Excel displays its Insert Picture dialog box, from which you can browse for the file. 532

Chapter 22: Enhancing Your Work with Pictures and Drawings When you insert a picture on a worksheet, you can modify the picture in a number of ways from the Picture Tools ➪ Format contextual tab, which becomes available when you select a picture object. For example, you can adjust the color, contrast, and brightness. In addition, you can add borders, shadows, reflections, and so on — similar to the operations available for Shapes. And don’t overlook the Picture Tools ➪ Format ➪ Picture Styles group. These commands can trans- form your image in some very interesting ways. Figure 22.16 shows various styles for a picture. FIGURE 22.16 Displaying a picture in a number of different styles. New Feature A new feature in Office 2010 is Artistic Effects. This command can apply a number of Photoshop-like effects to an image. To access this feature, right-click and image and choose Format Picture. Then, in the Format Picture dialog box, choose Artistic Effects. Each effect is somewhat customizable, so if you’re not happy with the default effect, try adjusting some options. You might be surprised by some of the image enhancements that are available. The best way to learn this fea- ture is to dig in and experiment. Even if you have no need for image enhancement, you might find that it’s a fun diversion when you need a break from working with numbers. n Inserting screenshots A new feature in Excel 2010 allows you to insert an image of any program currently running on your computer. To use the new screenshot feature 533

Part III: Creating Charts and Graphics 1. Make sure that the window you want to use displays the content that you want. 2. Choose Insert ➪ Illustrations ➪ Shapes. You’ll see a gallery that contains thumbnails of all windows open on your computer. 3. Click the image you want. Excel inserts it into your worksheet. When the image is selected, you can use any of the normal picture tools. Taking Pictures of Ranges One of Excel’s best-kept secrets is its ability to copy and paste “live” pictures of cells and charts. You can copy a cell or range and then paste a picture (as an object) of the cell or range on any worksheet or chart. If you change the contents of a cell that’s in a picture, the picture changes. The accompanying image shows a picture of a range after applying some picture effects. To “take a picture” of a range 1. Select the range. 2. Press Ctrl+C to copy the range. 3. Activate another cell. 4. Choose Home ➪ Clipboard ➪ Paste ➪ Linked Picture (I). The result is a live picture of the range you selected in Step 1. 534

Chapter 22: Enhancing Your Work with Pictures and Drawings If you use this feature frequently, you can save some time by adding the Excel Camera tool to your Quick access toolbar: 1. Right-click the Quick Access toolbar and choose Customize Quick Access Toolbar from the shortcut menu that appears. 2. On the Customization tab of the Excel Options dialog box, select Command Not in the Ribbon from the drop-down list on the left. 3. Select Camera from the list and click Add. 4. Click OK to close the Excel Options dialog box. After you add the Camera tool to your Quick Access toolbar, you can select a range of cells and click the Camera tool to take a “picture” of the range. Then click in the worksheet, and Excel places a live picture of the selected range on the worksheet’s draw layer. If you make changes to the original ranges, the changes are shown in the picture of the range. Note Keep in mind that the image is static. If the program from which it was taken updates its window, the image in your worksheet will not change. n In many cases, you don’t want the entire window — just a portion of it. The solution is to crop the inserted screenshot by choosing Picture Tools ➪ Format ➪ Size ➪ Crop. This command adds cropping marks to the corners of the image. Just drag the cropping marks to crop the image. Displaying a worksheet background image If you want to use a graphics image for a worksheet’s background (similar to wallpaper on the Windows desktop), choose Page Layout ➪ Page Setup ➪ Background and select a graphics file. The selected graphics file is tiled on the worksheet. Unfortunately, worksheet background images are for onscreen display only. These images do not appear when the worksheet is printed. Using the Equation Editor New Feature The final topic in this chapter deals with the new Equation Editor. Use this feature to insert a nicely formatted mathematical equation as a graphic object. n Figure 22.17 shows an example of an equation in a worksheet. Keep in mind that these equations do not perform calculations: They are for display purposes only. 535

Part III: Creating Charts and Graphics FIGURE 22.17 An equation created by the new Equation Editor. The best way to become familiar with the Equation Editor is to insert one of the pre-made equa- tions. Choose Insert ➪ Symbols ➪ Equation, and choose one of equations from the gallery. The equation is inserted in your worksheet. When you select an Equation object, you have access to two contextual tabs: l Drawing Tools: Used to format the container object l Equation Tools: Used to edit the equation The Equation Tools ➪ Design tab contains three groups of controls: l Tools: Used to insert a new equation, or control how the equation is displayed. Click the dialog box launcher in the bottom-right corner of the Tools group to display the Equation Options dialog box, where you can specify how the equation is copied and also define keyboard shortcuts (click Math AutoCorrect). l Symbols: Contains common mathematical symbols and operators that you can use in your equations. l Structures: Contains templates for various structures that are used in equations. Describing how to use the Equation tools is more difficult than actually using them. Generally, you add a structure, and then edit the various parts by adding text or symbols. You can put structures inside of structures, and there is no limit to the complexity of the equations. It might be a bit tricky at first, but it doesn’t take long before you understand how it works. Figure 22.18 shows two equations that illustrate an old calculus joke. 536

Chapter 22: Enhancing Your Work with Pictures and Drawings FIGURE 22.18 Two Equation objects on a worksheet. 537



Part IV Using Advanced Excel Features A IN THIS PART number of Excel features can probably be fairly called “advanced” features if for no better reason than the ways in which they expand the definitions of what a spreadsheet program can do. The chapters in this Chapter 23 Customizing the Excel User part cover some useful features that you may not have used Interface in the past but may find very valuable. Chapter 24 Using Custom Number Formats Chapter 25 Using Data Validation Chapter 26 Creating and Using Worksheet Outlines Chapter 27 Linking and Consolidating Worksheets Chapter 28 Excel and the Internet Chapter 29 Sharing Data with Other Office Applications Chapter 30 Using Excel in a Workgroup Chapter 31 Protecting Your Work Chapter 32 Making Your Worksheets Error-Free



CHAPTER Customizing the Excel User Interface A IN THIS CHAPTER software program’s user interface consists of all the ways that the user interacts with the software. In Excel, the user interface consists of About the Quick Access toolbar l The Ribbon l The Quick Access toolbar Adding frequently used commands to the Quick l Right-click shortcut menus Access toolbar l Keyboard shortcuts Adding commands that are otherwise not available This chapter describes how to make changes to two Excel user interface components: the Ribbon and the Quick Access toolbar. You might want to customize these elements to make Excel more suited to the way you use it. Customizing the Quick Access Toolbar The Quick Access toolbar is always visible, regardless of which Ribbon tab is selected. After you customize the Quick Access toolbar, your frequently used commands will always be one click away. Note The only situation in which the Quick Access toolbar is not visible is in Full Screen mode, which is enabled by choosing View ➪ Workbook Views ➪ Full Screen. To cancel Full Screen mode, right-click any cell and choose Close Full Screen, or just press Esc(ape). n 541

Part IV: Using Advanced Excel Features About the Quick Access toolbar By default, the Quick Access toolbar is located on the left side of Excel title bar, above the Ribbon (see Figure 23.1). It includes three tools: l Save: Saves the active workbook l Undo: Reverses the effect of the last action l Redo: Reverses the effect of the last undo If you prefer, you can move the Quick Access toolbar below the Ribbon. To do so, right-click the Quick Access toolbar and choose Show Quick Access Toolbar below the Ribbon. Moving the Quick Access toolbar below the Ribbon uses additional vertical space on your screen. In other words, you’ll be able to see one less row of your worksheet if you move the Quick Access Toolbar from its default location. Unlike traditional toolbars, the Quick Access Toolbar cannot be made free-floating so you can move it to a convenient location. It always appears either above or below the Ribbon. FIGURE 23.1 The default location for the Quick Access toolbar is on the left side of the Excel title bar. Commands on the Quick Access toolbar always appear as a small icon, with no text. When you hover your mouse pointer over an icon, you see the name of the command and a brief description. Customizing the Quick Access toolbar consists of adding new commands to it. If you find that you use some Excel commands frequently, you can make these commands easily accessible by adding them to your Quick Access toolbar. You can also rearrange the order of the icons. As far as I can tell, there is no limit to the number of commands that you can add. The Quick Access toolbar always displays only a single line of icons. If the number of icons exceeds the Excel window width, it displays an additional icon at the end: More Controls. Click the More Controls icon, and the hidden Quick Access toolbar icons appear in a pop-up window. 542

Chapter 23: Customizing the Excel User Interface Adding new commands to the Quick Access toolbar You can add a new command to the Quick Access toolbar in three ways: l Click the Quick Access toolbar drop-down control, which is located on the right side of the Quick Access toolbar (see Figure 23.2). The list contains a few commonly used com- mands. Select a command from the list, and Excel adds it to your Quick Access toolbar. l Right-click any control on the Ribbon and choose Add to Quick Access Toolbar. The con- trol is added to your Quick Access toolbar, after the last control. l Use the Quick Access Toolbar tab in the Excel Options dialog box. A quick way to access this dialog box is to right-click any Ribbon control and choose Customize Quick Access Toolbar. FIGURE 23.2 This drop-down list is one way to add a new command to the Quick Access toolbar. The remainder of this section discusses the Quick Access Toolbar tab of the Excel Options dialog box, shown in Figure 23.3. The left side of the dialog box displays a list of Excel commands, and the right side shows the com- mands currently on your Quick Access toolbar. Above the command list on the left is the Choose Commands From drop-down list from which you can filter the list. Select an item from the drop- down list, and the list displays only the commands for that item. In Figure 23.3, the list shows commands in the Popular Commands category. 543

Part IV: Using Advanced Excel Features FIGURE 23.3 Use the Quick Access Toolbar tab in the Excel Options dialog box to customize the Quick Access toolbar. Some of the items in the drop-down list are l Popular Commands: Displays commands that Excel users commonly use l Commands Not in the Ribbon: Displays a list of commands that you cannot access from the Ribbon l All Commands: Displays a complete list of Excel commands l Macros: Displays a list of all available macros l File Tab: Displays the commands available in the Backoffice menu l Home Tab: Displays all commands available when the Home tab is active In addition, the Choose Commands From drop-down list contains an item for every other tab. To add an item to your Quick Access toolbar, select it from the list on the left and then click Add. The command appears in the list on the right. Tip You can create a Quick Access toolbar configuration that’s specific to a particular workbook. In other words, the commands on the Quick Access toolbar appear only when a particular workbook is active. Start by activat- ing the workbook, and then display the Customize Quick Access Toolbar tab of the Excel Options dialog box. When you add a command to the Quick Access toolbar, use the drop-down list in the upper right to specify the workbook (only the active workbook is available as a choice). By default, customizations are For All Documents option. n 544

Chapter 23: Customizing the Excel User Interface Sometimes, you need to do some guessing to find a particular command. For example, the com- mand that displays the recent file list is named Open Recent File, not Recent Files. Note Some commands simply aren’t available. For example, I would like the Quick Access toolbar to display the command to toggle page break display on a worksheet. As far as I can tell, the only way to issue that command is to display the Advanced tab of the Excel Options dialog box, and then scroll down until you find the Show Page Breaks check box. n When you select Macros from the Choose Commands From drop-down, Excel lists all available macros. You can attach a macro to a Quick Access Toolbar icon so that when you click the icon, the macro is executed. If you add a macro to your Quick Access toolbar, you can click the Modify button to change the text and choose a different icon for the macro. When you finish making your Quick Access Toolbar customizations, click OK to close the Excel Options dialog box. The new icon will appear on the Quick Access toolbar. Tip The only times you ever need to use the Quick Access Toolbar tab in the Excel Options dialog box is when you want to add a command that’s not on the Ribbon, add a command that executes a macro, or rearrange the order of the icons. In all other situations, it’s much easier to locate the command in the Ribbon, right-click the command, and choose Add to Quick Access Toolbar. n Other Quick Access toolbar actions Other Quick Access toolbar actions include the following: l Rearranging the Quick Access toolbar icons: If you want to change the order of your Quick Access toolbar icons, you can do so from the Quick Access Toolbar tab of the Excel Options dialog box. Select the command and then use the Up and Down arrow buttons on the right to move the icon. l Removing Quick Access toolbar icons: The easiest way to remove an icon from your Quick Access toolbar is to right-click the icon and choose Remove from Quick Access Toolbar. You can also use the Quick Access Toolbar tab of the Excel Options dialog box. Just select the command in the list on the right and click Remove. l Resetting the Quick Access toolbar: If you want to return the Quick Access toolbar to its default state, display the Quick Access Toolbar tab of the Excel Options dialog box and click the Reset button. Then choose Rest Only Quick Access Toolbar. All your customiza- tions disappear, and the Quick Access toolbar then displays its three default commands. Caution You can’t undo resetting the Quick Access toolbar. Also, restoring defaults also resets any Ribbon customiza- tions that you made. n 545

Part IV: Using Advanced Excel Features Sharing User Interface Customizations In the Excel Options dialog box, the Quick Access Toolbar tab and the Customize Ribbon tab both have an Import/Export button. You can use this button to save and open files that contain user interface customizations. For example, you might create a new Ribbon tab and want to share it with your office mates. Click the Import/Export button, and you get two options: l Import Customization File: You are prompted to locate the file. Before you load a file, you are asked whether you want to replace all existing Ribbon and Quick Access toolbar customizations. l Export All Customization: You are prompted to provide a filename and location for the file. The information is stored in a file that has a *.exportedUI extension. Unfortunately, importing and exporting is not implemented very well. Excel does not allow you to save or load only the Quick Access toolbar customization or only the Ribbon customizations. Both types of customizations are exported and imported. Therefore, you cannot share your Quick Access toolbar customization without also shar- ing your Ribbon customizations. Customizing the Ribbon The Ribbon is Excel’s primary user interface. It consists of tabs along the top. When you click a tab, it displays a set of commands, and the commands are arranged in groups. New Feature The Ribbon was introduced in Excel 2007, but it could not be customized. Excel 2010, however, makes it fairly easy to modify the Ribbon in a number of ways. n Why customize the Ribbon? Most users will have no need to customize the Ribbon. If you find that you tend to use the same command over and over, though — and you are constantly clicking tabs to access these commands — then you might benefit for customizing the Ribbon in such a way that the com- mands you need are on the same tab. What can be customized You can customize the Ribbon in these ways: l Tabs l Add a new custom tab. l Delete custom tabs. 546

Chapter 23: Customizing the Excel User Interface l Add a new group to tab. l Change the order of the tabs. l Change the name of a tab. l Hide built-in tabs. l Groups l Add new custom groups. l Add commands to a custom group. l Remove commands from custom groups. l Remove groups from a tab. l Move a group to a different tab. l Change the order of the groups within a tab. l Change the name of a group. Note To restore all or part of the Ribbon to its default state, use the Reset button on the Customize Ribbon tab of the Excel Options dialog box. Click this button to display two options: Reset Only Selected Ribbon Tab, and Rest All Customizations. If you choose the latter, you will also lose any Quick Access toolbar customizations that you made. n That’s a fairly comprehensive list of customization options, but there are some actions that you cannot do: l Remove built-in tabs — but you can hide them. l Remove commands from built-in groups. l Change the order of commands in a built-in group. Note Unfortunately, you cannot customize the Ribbon (or Quick Access toolbar) by using VBA macros. However, developers can write RibbonX code and store it in workbook files. When the file is open, the Ribbon is modified to display new commands. Writing RibbonX is relatively complicated, and beyond the scope of this book. n How to customize the Ribbon Customizing the Ribbon is done via the Customize Ribbon panel of the Excel Options dialog box (see Figure 23.4). The quickest way to display this dialog box is to right-click anywhere on the Ribbon, and choose Customize The Ribbon. 547

Part IV: Using Advanced Excel Features FIGURE 23.4 The Customize Ribbon tab of the Excel Options dialog box. Customizing the Ribbon is very similar to customizing the Quick Access toolbar, which I describe earlier in this chapter. The only difference is that you need to decide where to put the command within the Ribbon. The general procedure is 1. Use the Choose Command From drop-down list on the left to display various groups of commands. 2. Locate the command in the list box on the left, and select it. 3. Use the Customize the Ribbon drop-down list on the right to choose a group of tabs. Main Tabs refer to the tabs that are always visible; Tool Tabs refer to the contextual tabs that appear when a particular object is selected. 4. In the list box on the right, select the tab and the group where you would like to put the command. You’ll need to click the plus-sign control to expand the tab name so that it displays its group names. 5. Click the Add button to add the selected command from the left to the selected group on the right. 548

Chapter 23: Customizing the Excel User Interface Use the New Tab button to create a new tab, and the New Group button to create a new group within a tab. New tabs and groups are given generic names, so you’ll probably want to give them more meaningful names. Use the Rename button to rename the selected tab or group. You can also rename built-in tabs and groups. To rearrange the order of tabs, groups, or commands, select the item and use the Move Up and Move Down buttons on the right. Note that you can move a group into a different tab. Note Although you cannot remove a built-in tab, you can hide the tab by clearing the check box next to its name. n Figure 23.5 shows a part of a customized Ribbon. In this case, I added two groups to the View tab (to the right of the Zoom group): Extra Commands (with four new commands), and Text to Speech (with two new commands). FIGURE 23.5 The View tab, with two new groups added. 549



CHAPTER Using Custom Number Formats hen you enter a number into a cell, you can display that number in a variety of different formats. Excel has quite a few built-in number IN THIS CHAPTER W formats, but you may find that none of them suit your needs. An overview of custom number formatting This chapter describes how to create custom number formats and provides many examples that you can use as-is, or adapt to your needs. How to create a custom number format A list of all custom number About Number Formatting format codes Examples of custom number By default, all cells use the General number format. This format is basically “what you type is what you get.” But if the cell isn’t wide enough to show the formats entire number, the General format rounds numbers with decimals and uses scientific notation for large numbers. In many cases, the General number for- mat works just fine, but most people prefer to specify a different number for- mat for consistency. The key thing to remember about number formatting is that it affects only how a value is displayed. The actual number remains intact, and any formulas that use a formatted number use the actual number. Note An exception to this rule occurs if you specify the Set Precision as Displayed option on the Advanced tab in the Excel Options dialog box. If that option is in effect, formulas use the values that are actually displayed in the cells. In gen- eral, using this option is not a good idea because it changes the underlying val- ues in your worksheet. n 551

Part IV: Using Advanced Excel Features One more thing to keep in mind: If you use the Find and Replace dialog box (Home ➪ Editing ➪ Find & Select ➪ Find), characters that are displayed as a result of number formatting (for example, a currency symbol) are not searchable by default. To be able to locate information based on for- matting, use the Search In Value option in the Find and Replace dialog box. Automatic number formatting Excel is smart enough to perform some formatting for you automatically. For example, if you enter 12.3% into a cell, Excel assumes that you want to use a percentage format and applies it automati- cally. If you use commas to separate thousands (such as 123,456), Excel applies comma formatting for you. And if you precede your value with a currency symbol, Excel formats the cell for currency. Note You have an option when it comes to entering values into cells formatted as percentages. Access the Excel Options and click the Advanced tab. If the Enable Automatic Percent Entry check box is selected (the default setting), you can simply enter a normal value into a cell that has been formatted to display as a percent (for example, enter 12.5 for 12.5%). If this check box isn’t selected, you must enter the value as a decimal (for example, .125 for 12.5%). n Excel automatically applies a built-in number format to a cell based on the following criteria: l If a number contains a slash (/), it may be converted to a date format or a fraction format. l If a number contains a hyphen (-), it may be converted to a date format. l If a number contains a colon (:) or is followed by a space and the letter A or P, it may be converted to a time format. l If a number contains the letter E (uppercase or lowercase), it may be converted to scien- tific notation or exponential format. Tip To avoid automatic number formatting when you enter a value, preformat the cell with the desired number format or precede your entry with an apostrophe. (The apostrophe makes the entry text, so number formatting is not applied to the cell.) n Formatting numbers by using the Ribbon The Number group on the Home tab of the Ribbon contains several controls for applying common number formats quickly. The Number Format drop-down control gives you quick access to 11 common number formats. In addition, the Number group contains some buttons. When you click one of these buttons, the selected cells take on the specified number format. Table 24.1 summa- rizes the formats that these buttons perform in the U.S. English version of Excel. 552

Chapter 24: Using Custom Number Formats Note Some of these buttons actually apply predefined styles to the selected cells. Access Excel’s styles by using the Style gallery, in the Styles group on the Home tab. n TABLE 24.1 Number-Formatting Buttons on the Ribbon Button Name Formatting Applied Accounting Number Format Adds a dollar sign to the left, separates thousands with a comma, and displays the value with two digits to the right of the decimal point. This is a drop-down control, so you can select other common currency symbols. Percent Style Displays the value as a percentage, with no decimal places. Comma Style Separates thousands with a comma and displays the value with two digits to the right of the decimal place. It’s like the Accounting number format, but without the currency symbol. Increase Decimal Increases the number of digits to the right of the decimal point by one. Decrease Decimal Decreases the number of digits to the right of the decimal point by one. Using shortcut keys to format numbers Another way to apply number formatting is to use shortcut keys. Table 24.2 summarizes the shortcut key combinations that you can use to apply common number formatting to the selected cells or range. Notice that these are the shifted versions of the number keys along the top of a typical keyboard. TABLE 24.2 Number-Formatting Keyboard Shortcuts Key Combination Formatting Applied Ctrl+Shift+~ General number format (that is, unformatted values). Ctrl+Shift+! Two decimal places, thousands separator, and a hyphen for negative values. Ctrl+Shift+@ Time format with the hour, minute, and AM or PM. Ctrl+Shift+# Date format with the day, month, and year. Ctrl+Shift+$ Currency format with two decimal places. (Negative numbers appear in parentheses.) Ctrl+Shift+% Percentage format with no decimal places. Ctrl+Shift+^ Scientific notation number format with two decimal places. 553

Part IV: Using Advanced Excel Features Using the Format Cells dialog box to format numbers For maximum control of number formatting, use the Number tab in the Format Cells dialog box. You can access this dialog box in any of several ways: l Click the dialog box launcher at the bottom right of the Home ➪ Number group. l Choose Home ➪ Number ➪ Number Format ➪ More Number Formats. l Press Ctrl+1. The Number tab in the Format Cells dialog box contains 12 categories of number formats from which to choose. When you select a category from the list box, the right side of the dialog box changes to display appropriate options. Here are the number-format categories, along with some general comments: l General: The default format; it displays numbers as integers, decimals, or in scientific notation if the value is too wide to fit into the cell. l Number: Specify the number of decimal places, whether to use your system thousands separator (for example, a comma) to separate thousands, and how to display negative numbers. l Currency: Specify the number of decimal places, choose a currency symbol, and display negative numbers. This format always uses the system thousands separator symbol (for example, a comma) to separate thousands. l Accounting: Differs from the Currency format in that the currency symbols always line up vertically, regardless of the number of digits displayed in the value. l Date: Choose from a variety of date formats and select the locale for your date formats. l Time: Choose from a number of time formats and select the locale for your time formats. l Percentage: Choose the number of decimal places; always displays a percent sign. l Fraction: Choose from among nine fraction formats. l Scientific: Displays numbers in exponential notation (with an E): 2.00E+05 = 200,000. You can choose the number of decimal places to display to the left of E. l Text: When applied to a value, causes Excel to treat the value as text (even if it looks like a value). This feature is useful for such items as numerical part numbers and credit card numbers. l Special: Contains additional number formats. The list varies, depending on the Locale you choose. For the English (United States) locale, the formatting options are Zip Code, Zip Code +4, Phone Number, and Social Security Number. l Custom: Define custom number formats not included in any of the other categories. 554

Chapter 24: Using Custom Number Formats Note If the cell displays a series of hash marks after you apply a number format (such as #########), it usually means that the column isn’t wide enough to display the value by using the number format that you selected. Either make the column wider (by dragging the right border of the column header) or change the number for- mat. A series of hash marks also can mean that the cell contains an invalid date or time. n Creating a Custom Number Format When you create a custom number format, it can be used to format any cells in the workbook. You can create as many custom number formats as you need. Figure 24.1 shows the Custom category in the Number tab of the Format Cells dialog box. Here, you can create number formats not included in any of the other categories. Excel gives you a great deal of flexibility in creating custom number formats. FIGURE 24.1 The Custom category of the Number tab in the Format Cells dialog box. Tip Custom number formats are stored with the workbook in which they are defined. To make the custom format available in a different workbook, you can just copy a cell that uses the custom format to the other workbook. n 555

Part IV: Using Advanced Excel Features You construct a number format by specifying a series of codes as a number format string. You enter this code sequence in the Type field after you select the Custom category on the Number tab of the Format Cells dialog box. Here’s an example of a simple number format code: 0.000 This code consists of placeholders and a decimal point; it tells Excel to display the value with three digits to the right of the decimal place. Here’s another example: 00000 This custom number format has five placeholders and displays the value with five digits (no deci- mal point). This format is good to use when the cell holds a five-digit zip code. (In fact, this is the code actually used by the Zip Code format in the Special category.) When you format the cell with this number format and then enter a Zip Code, such as 06604 (Bridgeport, CT), the value is dis- played with the leading zero. If you enter this number into a cell with the General number format, it displays 6604 (no leading zero). Scroll through the list of number formats in the Custom category in the Format Cells dialog box to see many more examples. In many cases, you can use one of these codes as a starting point, and you’ll need to customize it only slightly. Changing the Default Number Format for a Workbook As I mention earlier, the default number format is General. If you prefer a different default number for- mat, you have two choices: Preformat the cells with the number format of your choice, or change the number format for the Normal style. You can preformat specific cells, entire rows or columns, or even the entire worksheet. Rather than preformat an entire worksheet, however, a better solution is to change the number format for the Normal style. Unless you specify otherwise, all cells use the Normal style. Therefore, by chang- ing the number format for the Normal style you are essentially creating a new default number format for the workbook. Change the Normal style by displaying the Style gallery. Right-click the Normal style icon (in the Home ➪ Styles group) and choose Modify to display the Style dialog box. In the Style dialog box, click the Format button and then choose the new number format that you want to use for the Normal style. 556

Chapter 24: Using Custom Number Formats FIGURE 24.2 Examples of custom number formatting. Parts of a number format string A custom format string can have up to four sections, which enables you to specify different format codes for positive numbers, negative numbers, zero values, and text. You do so by separating the codes with a semicolon. The codes are arranged in the following order: Positive format; Negative format; Zero format; Text format If you don’t use all four sections of a format string, Excel interprets the format string as follows: l If you use only one section: The format string applies to all types of entries. l If you use two sections: The first section applies to positive values and zeros, and the second section applies to negative values. l If you use three sections: The first section applies to positive values, the second section applies to negative values, and the third section applies to zeros. l If you use all four sections: The last section applies to text stored in the cell. 557

Part IV: Using Advanced Excel Features The following is an example of a custom number format that specifies a different format for each of these types: [Green]General;[Red]General;[Black]General;[Blue]General This custom number format example takes advantage of the fact that colors have special codes. A cell formatted with this custom number format displays its contents in a different color, depending on the value. When a cell is formatted with this custom number format, a positive number is green, a negative number is red, a zero is black, and text is blue. Cross-Reference If you want to apply cell formatting automatically (such as text or background color) based on the cell’s con- tents, a much better solution is to use the Excel Conditional Formatting feature. Chapter 20 covers conditional formatting. n Custom number format codes Table 24.3 lists the formatting codes available for custom formats, along with brief descriptions. I use most of these codes in examples later in this chapter. TABLE 24.3 Codes Used to Create Custom Number Formats Code Comments General Displays the number in General format. # Digit placeholder. Displays only significant digits, and does not display insignificant zeros. 0 (zero) Digit placeholder. Displays insignificant zeros if a number has fewer digits than there are zeros in the format. ? Digit placeholder. Adds spaces for insignificant zeros on either side of the decimal point so that decimal points align when formatted with a fixed-width font. You can also use ? for fractions that have varying numbers of digits. . Decimal point. % Percentage. , Thousands separator. E- E+ e- e+ Scientific notation. $ - + / ( ) : Displays this character. space \ Displays the next character in the format. * Repeats the next character, to fill the column width. _ (underscore) Leaves a space equal to the width of the next character. 558

Chapter 24: Using Custom Number Formats Code Comments “text” Displays the text inside the double quotation marks. @ Text placeholder. [color] Displays the characters in the color specified. Can be any of the following text strings (not case sensitive): Black, Blue, Cyan, Green, Magenta, Red, White, or Yellow. [Color n] Displays the corresponding color in the color palette, where n is a number from 0 to 56. [condition Set your own criterion for each section of a number format. value] Table 24.4 lists the codes used to create custom formats for dates and times. TABLE 24.4 Codes Used in Creating Custom Formats for Dates and Times Code Comments m Displays the month as a number without leading zeros (1–12). mm Displays the month as a number with leading zeros (01–12). mmm Displays the month as an abbreviation (Jan–Dec). mmmm Displays the month as a full name (January–December). mmmmm Displays the first letter of the month (J–D). d Displays the day as a number without leading zeros (1–31). dd Displays the day as a number with leading zeros (01–31). ddd Displays the day as an abbreviation (Sun–Sat). dddd Displays the day as a full name (Sunday–Saturday). yy or yyyy Displays the year as a two-digit number (00–99) or as a four-digit number (1900–9999). h or hh Displays the hour as a number without leading zeros (0–23) or as a number with leading zeros (00–23). m or mm Displays the minute as a number without leading zeros (0–59) or as a number with lead- ing zeros (00–59). s or ss Displays the second as a number without leading zeros (0–59) or as a number with lead- ing zeros (00–59). [ ] Displays hours greater than 24 or minutes or seconds greater than 60. AM/PM Displays the hour using a 12-hour clock; if no AM/PM indicator is used, the hour uses a 24 hour clock. 559

Part IV: Using Advanced Excel Features Where Did Those Number Formats Come From? Excel may create custom number formats without you realizing it. When you use the Increase Decimal or Decrease Decimal button on the Home ➪ Number group of the Ribbon (or on the Mini toolbar), Excel creates new custom number formats, which appear on the Number tab in the Format Cells dialog box. For example, if you click the Increase Decimal button five times, the following custom number formats are created: 0.0 0.000 0.0000 0.000000 A format string for two decimal places is not created because that format string is built-in. Custom Number Format Examples The remainder of this chapter consists of useful examples of custom number formats. You can use most of these format codes as-is. Others may require slight modification to meet your needs. Scaling values You can use a custom number format to scale a number. For example, if you work with very large numbers, you may want to display the numbers in thousands (that is, display 1,200,000 as 1,200). The actual number, of course, will be used in calculations that involve that cell. The formatting affects only how it is displayed. Displaying values in thousands The following format string displays values without the last three digits to the left of the decimal place and no decimal places. In other words, the value appears as if it’s divided by 1,000 and rounded to no decimal places. #,###, A variation of this format string follows. A value with this number format appears as if it’s divided by 1,000 and rounded to two decimal places. #,###.00, Table 24.5 shows examples of these number formats: 560

Chapter 24: Using Custom Number Formats TABLE 24.5 Examples of Displaying Values in Thousands Value Number Format Display 123456 #,###, 123 1234565 #,###, 1,235 –323434 #,###, –323 123123.123 #,###, 123 499 #,###, (blank) 500 #,###, 1 123456 #,###.00, 123.46 1234565 #,###.00, 1,234.57 –323434 #,###.00, –323.43 123123.123 #,###.00, 123.12 499 #,###.00, .50 500 #,###.00, .50 Displaying values in hundreds The following format string displays values in hundreds, with two decimal places. A value with this number format appears as if it’s divided by 100 and rounded to two decimal places. 0”.”00 Table 24.6 shows examples of these number formats: TABLE 24.6 Examples of Displaying Values in Hundreds Value Number Format Display 546 0”.”00 5.46 100 0”.”00 1.00 9890 0”.”00 98.90 500 0”.”00 5.00 –500 0”.”00 –5.00 0 0”.”00 0.00 561

Part IV: Using Advanced Excel Features Displaying values in millions The following format string displays values in millions with no decimal places. A value with this number appears as if it’s divided by 1,000,000 and rounded to no decimal places. #,###,, A variation of this format string follows. A value with this number appears as if it’s divided by 1,000,000 and rounded to two decimal places. #,###.00,, Another variation follows. This adds the letter M to the end of the value. #,###,,M The following format string is a bit more complex. It adds the letter M to the end of the value — and also displays negative values in parentheses as well as displaying zeros. #,###.0,,”M”_);(#,###.0,,”M)”;0.0”M”_) Table 24.7 shows examples of these format strings. TABLE 24.7 Examples of Displaying Values in Millions Value Number Format Display 123456789 #,###,, 123 1.23457E+11 #,###,, 123,457 1000000 #,###,, 1 5000000 #,###,, 5 –5000000 #,###,, –5 0 #,###,, (blank) 123456789 #,###.00,, 123.46 1.23457E+11 #,###.00,, 123,457.00 1000000 #,###.00,, 1.00 5000000 #,###.00,, 5.00 –5000000 #,###.00,, –5.00 0 #,###.00,, .00 123456789 #,###,,”M” 123M 1.23457E+11 #,###,,”M” 123,457M 1000000 #,###,,”M” 1M 562