EXCEL tutorial charts and graph


Télécharger EXCEL tutorial charts and graph

Formation Excel en ligne par vidéo

205 leçons vidéos + 20 Livres PDF + 20 TP + Sous supervision + Certificat de réussite à la fin du cours



★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :



Excel 2010: Tables, Charts, and Graphics  (Course #6480/QAS6480) Table of Contents

                                                                                                                                       Page

Introduction

Chapter 1:      Tables – List Management Made Easy

I.   The Basics of Tables                                                                          1-1        

II.    Sorting and Filtering a Table                                                              1-12      

III.   Dealing with Duplicate Rows                                                                          1-24

IV.  Performing Table Calculations                                                                       1-27

Review Questions & Solutions                                                                               1-37

Chapter 2:      Grouping and Outlining Data

I.  Basic Data Grouping                                                                          2-1

II.Grouping Timesavers        2-14 Review Questions & Solutions       2-19

Chapter 3:      Templates

I.   Understanding Templates                                                                              3-1

II.    Creating a New Workbook from a Template                                                  3-2        

III.   Creating Templates                                                                            3-10

Review Questions & Solutions                                                                               3-18

Chapter 4:      Creating Basic Charts

I.   Charting 101                                                                                      4-1

II.    Basic Tasks with Charts                                                                                 4-6

III.   Practical Charting                                                                                           4-12

IV.  Chart Types                                                                                        4-23

Review Questions & Solutions                                                                               4-37

Chapter 5:       Formatting and Perfecting Charts

I. Chart Styles and Layouts       5-1  II. Adding Chart Elements       5-3

III. Selecting Chart Elements       5-13 IV. Formatting Chart Elements       5-16 V. Improving Your Charts       5-28

        VI. Advanced Charting                                                                                         5-39

Review Questions & Solutions                                                                               5-47

Chapter 6:      Inserting Graphics

I. Adding Pictures to a Worksheet                                                                        6-1        II. Excel’s Clip Art Library                                                                               6-16      

         III. Drawing Shapes                                                                                             6-19

Review Questions & Solutions                                                                               6-35

Glossary

Index

Table of Contents 

Introduction

Excel is one of the powerhouses of the Microsoft Office family.  It allows you to efficiently create, edit, and analyze data that is organized into lists or tables, known as spreadsheets. These spreadsheets allow you to easily create powerful, mathematical, financial, and statistical models. 

Professional Education Services offers a basic Excel 2010 course (Course #6470) that will cover everything you need to know about creating spreadsheets, formulas, and functions.  This Tables, Charts, and Graphics course is an intermediate level course that assumes you have either taken the basic course, or that you have a solid working knowledge of Excel spreadsheets. 

WHAT THIS COURSE COVERS:

This Excel 2010: Tables, Charts, and Graphics course will show you how to find and organize the data you have entered into your spreadsheets.  It will teach you the basics of tables and teach you how to sort, filter, and group the table data. It will also introduce standard templates and illustrate how to create custom templates.

Once you have a good knowledge of tables, you will then learn about Excel’s charting and graphics features.  With charts and graphics, you have the tools necessary to add some pizzazz to your spreadsheets.  At the completion of the course, you will know how to create, format, and perfect charts, as well as insert graphics and use Excel’s Clip Art Library and SmartArt.

WHAT THIS COURSE DOES NOT COVER:

Since this is an intermediate level Excel course, it does not cover the basics of working with rows and columns or entering data or formulas into a spreadsheet.  This course requires you have this knowledge prior to taking this course.

This course also does not go beyond tables, charts and graphics.  There are many other advanced Excel features that are covered in the Professional Education Services course Excel 2010: Advanced Data Analysis, Web Use, and Programming (course #6485).If you would like to extend your Excel knowledge beyond Tables, Charts, and Graphics, we recommend you take this advanced course.

Okay, let’s start adding some pizzazz to those worksheets…

Note: This course includes references to the website . Because this course is a component of a larger course, the samples listed in the website are not the same chapter number as you will find in this course. If you go to the Missing Manuals website, Chapter 1 in this course is equivalent to Chapter 14 in the website, Chapter 2 is equivalent to Chapter 15, etc. 

Introduction                                                        


Chapter 1: Tables – List Management Made Easy

Excel's grid-like main window gives you lots of freedom to organize your information. Tables of data can assume a variety of shapes and sizes—from complex worksheets that track expenses, to a simple list of dishes your guests are bringing to a potluck dinner.

Some tables are quite sophisticated, with multiple levels, subtotals, and summary information. (You'll learn about how to manage these multi-tiered creations in the next chapter.) But in many cases, your table consists of nothing more than a long list of data, with a single row at the top that provides descriptive column headings. These types of tables are so common that Excel provides a set of features designed exclusively for managing them. These tools let you control your tables in style—sorting, searching, and filtering your information with just a couple of mouse clicks. Excel even includes a group of functions expressly designed to analyze the information in tables. But before you can use any of these tools, you have to convert your garden-variety table into a structured table.

In this chapter, you'll learn more about what, exactly, a structured table is, how to create one, and how to make use of all its features and frills.

Note: Don't confuse structured tables with the variable data tables you used for what-if analysis. These tables have a similar moniker but nothing else in common.

I. The Basics of Tables

An Excel table is really nothing more than a way to store a bunch of information about a group of items. Each item occupies a separate row, and different kinds of information about the item reside side by side in adjacent columns. In database terminology, the rows are records, and the columns of information are fields. For example, the records could represent customers, and the fields could contain things like name, address, purchase history, and so on.

Excel tables have a number of advantages over ordinary worksheet data:

•    They grow and shrink dynamically. As you fill data into adjacent rows and columns, the table grows to include the new cells. And as a table changes size, any formulas that use the table adjust themselves accordingly. In other words, if you have a formula that calculates the sum of a column in a table, the range that the SUM() function uses expands when you add a new record to the table.

•    They have built-in smarts. You can quickly select rows and columns, apply a custom sort order, and search for important records.

•    They excel (ahem) at dealing with large amounts of information. If you need to manage vast amounts of information, you may find ordinary worksheet data a little cumbersome. If you put the same information in a table, you can simply apply custom filtering, which means you see only the records that interest you.

•    They can link to databases. Tables are perfectly useful in standalone worksheets. However, they can also double as indispensable tools for navigating information contained in a database.

A. CREATING A TABLE

Creating a table is easy. Here's how:

1.    Choose the row where you want your table to start.

If you're creating a new table, the worksheet's first row is a good place to begin. (You can always shift the table down later by putting your cursor in the top row, and then choosing Home?Cells?Insert?Insert Sheet Rows.) This first row of the table is where you enter any column titles you want to use, as explained in the next step.

Note: Be careful when placing content in the cells directly beneath your table. If your table expands too far down, you'll run up against these filled-up cells. Although you can use commands like Home?Cells?Insert?Insert Sheet Rows to add some extra space when things get crowded, it's always better to start off with plenty of breathing room.

2.    Enter the column titles for your table, one column title for each category you want to create.

To create the perfect table, you need to divide your data into categories. For example, if you're building a table of names and addresses, you probably want your columns to hold the standard info you see on every form ever created: First Name, Last Name, Street, City, and so on. The columns you create are the basis for all the searching, sorting, and filtering you do. For instance, if you have First Name and City columns, you can sort your contacts by first name or by city.

If you want, you can start to add entries underneath the column headings now (in the row directly below the column titles). Or just jump straight to the next step to create the table.

3.    Make sure you're currently positioned somewhere inside the table (anywhere in the column title row works well), and then choose Insert?Tables?Table.

Excel scans the nearby cells, and then selects all the cells that it thinks are part of your table. Once Excel determines the bounds of your table, the Create Table dialog box appears, as shown in Figure 1-1.

Figure 1-1 (On next page). The Create Table dialog box displays the cell references for the currently selected range. In this example, the selection includes only the headings (there's no data yet). You can change the range by typing in new information or by clicking the mini worksheet icon at the right end of the cell range box, which lets you select the range by dragging the appropriate cells in the worksheet.

4.    Make sure the "My table has headers" checkbox is turned on. This option tells Excel you're using the first row just for column headers. Then click OK.

Excel transforms your cells into a table, like the one shown in Figure 1-2. You can tell that your ordinary range of cells has become a genuine table by the presence of a few telltale signs. First, tables start out with automatic formatting that gives each row a shaded background (alternating between blue and gray). Second, the column headings appear in bold white letters on a dark background, and each one includes a drop-down arrow that you can use for quick filtering (a feature you'll explore on page 1-19).

If you create a table from a group of cells that don't include column titles, don't turn on the "My table has headers" checkbox. When you create the table, Excel adds a row of columns at the top with generic names like Column1, Column2, and so on. You can click these cells, and then edit the column titles, to be more descriptive.

Figure 1-2 (On next page). To quickly resize your table, look for the tiny triangle icon at the bottom-right corner (under the two-headed arrow in this figure), and then drag it to encompass more (or fewer) rows and columns.

Keep in mind that tables consist of exactly two elements: column headers (Figure 1-3) and rows. Tables don't support row headers (although there's no reason why you can't create a separate column and use that as a row title). Tables also have a fixed structure, which means that every row has exactly the same number of columns. You can create multiple tables on the same worksheet, but you're often better off placing them on separate worksheets so you can more easily manage them.

Figure 1-3. Here's one unsung frill in every table. When you can't see the column headers any longer (because you've scrolled down the page), the column buttons atop the worksheet grid change from letters (like A, B, C) to your custom headers (like Product ID, Model Name, and Price). This way, you never forget what column you're in, and you don't need to scroll up to use the sorting and filtering features that you can access through the drop-down column menus (page 1-19).

B. FORMATTING A TABLE

When you move to a cell in a table, Excel adds a new tab to the ribbon—the Table Tools | Design tab. This tab has four sections:

•    Properties, which lets you rename your table and resize it. (The table name is important only if you choose to use it in a formula, as described on page 1-29.)

•    Tools, which lets you use pivot tables, find duplicate rows (page 1-21), and convert your table back to ordinary cells.

•    External Table Data, which lets you work with external data (like records drawn from a database) using an Excel table. 

•    Table Style Options and Table Styles, which help you make your table look pretty.

Every table starts out with some basic formatting, and you can use the ribbon and the Format Cells dialog box to further change its appearance. However, Excel gives you an even better option—you can use table styles.

A table style is a collection of formatting settings that apply to an entire table. The nice part about table styles is that Excel remembers your style settings. If you add new rows to a table, Excel automatically adds the right cell formatting. Or, if you delete a row, Excel adjusts the formatting of all the cells underneath to make sure the banding (the alternating pattern of cell shading that makes each row easier to read) stays consistent.

When you first create a table, you start out with a fairly ordinary set of colors: a gray– blue combination that makes your table stand out from the rest of the worksheet. By choosing another table style, you can apply a different set of colors and borders to your table.

Note: Excel's standard table styles don't change the fonts in a table. To change fonts, you can change the theme, or select some cells, and then, from the ribbon's Home?Font section, pick the font you want.

To choose a new table style, head to the ribbon's Table Tools | Design?Table Styles section. You'll see a gallery of options, as shown in Figure 1-4. As you move over a table style, Excel uses its live preview feature to change the table, giving you a sneak peak at how your table would look with that style.

Figure 1-4 (On next page). Depending on your Excel window's width, in the ribbon, you may see the table style gallery. Or, if there's not enough room available, you see a Quick Styles button that you need to click to display a drop-down style gallery (as shown here).

Note: Notice that some table styles use banding, while others don't.

Table styles work like cell styles. Like cell styles, they let you standardize and reuse formatting. Table styles, however, include a whole package of settings that tell Excel how to format different portions of the table, including the headers, first and last columns, the summary row (page 1-31), and so on. To get a better feel for the variety of options in a table style, you can create your own by clicking the drop-down arrow in the Table Tools | Design?Table Styles section, and then choosing New Table Style. You'll see a New Table Quick Style dialog box that lets you go through the somewhat painstaking process of tweaking the formatting for each part of the table (Figure 1-5).

Note: You can't edit the built-in table styles. However, you can change the table styles you create. In the table gallery, just right-click a style, and then choose Modify.

Figure 1-5. To set the formatting for a part of the table, select it in the Table Element list, and then click Format. Excel opens a slimmed-down Format Cells dialog box that includes only the Font, Border, and Fill tabs. For example, to change the font for the whole table, select Whole Table, click Format, and then make your changes. If you think you've created the perfect table style, you can turn on the "Set as default table quick style" checkbox so Excel uses it whenever you create a new table in this workbook.

Most of the time, it's not worth creating your own table styles because it's simply too much work (and the prebuilt table styles give you a good selection of formatting choices). However, you'll notice that the built-in table styles have a limited set of colors. Excel limits them because table styles use colors from the current theme, which ensures that your table meshes well with the rest of your worksheet (assuming you've been sticking to theme colors elsewhere). To get different colors for your tables, you can change the theme by choosing from the Page Layout?Themes?Themes gallery. 

Along with the table style and theme settings, you have a few more options to finetune your table's appearance. Head over to the ribbon's Table Tools | Design?Table Style Options section, where you see a group of checkboxes, each of which lets you toggle on or off different table elements:

•    Header Row lets you show or hide the row with column titles at the top of the table. You'll rarely want to remove this option. Not only are the column headers informative, but they also include drop-down lists for quick filtering (page 1-19).

•    Total Row lets you show or hide the row with summary calculations at the bottom of your table. You'll learn how to configure this row on page 1-31.

•    First Column applies different formatting to the first column in your table, if it's defined in the table style.

•    Last Column applies different formatting to the last column in your table, if it's defined in the table style.

•    Banded Rows applies different formatting to each second row, if it's defined in the table style. Usually, the banded row appears with a background fill. Largetable lovers like to use banding because it makes it easier to scan a full row from right to left without losing your place.

•    Banded Columns applies different formatting to each second column, if it's defined in the table style. Folks use banded less than banded rows, because people usually read tables from side to side (not top to bottom).

Up to Speed: The Difference Between Excel Worksheets and Databases

An Excel table uses some of the same concepts as a database—namely, the idea of records and fields. However, databases and Excel worksheets are two very different entities.

For starters, databases—which programs like Microsoft Access and SQL Server let you create—have much stricter rules than Excel worksheets. Before you can add any data to a table in a database, you must carefully define the table. You need to specify not only the name of each field, but also the type of information the field can contain. Although Excel provides some of these so-called data validation features, the program isn't nearly as strict about it—validation is completely optional. Also, unlike Excel, most modern databases are relational, which means they contain multiple tables that have specific links to one another. For example, a relational database might tie together customers in one table and the orders they've made in another. In Excel, a worksheet can hold multiple tables of data, but there's no way to tie them together.

Most importantly, databases play a dramatically different role in the world of business. Typically, Excel is an end user program, which means ordinary mortals who generally know how to create an Excel file, design what it's going to look like, and then fill it up with data to use it. Ex-math majors, on the other hand, usually create databases, and they store information, behind-the-scenes, that non-programmer types end up using. For example, every time you use Google or search on Amazon for something to buy, you're actually seeing answers that have been stored in, and generated by, massive and powerful databases.

C. EDITING A TABLE

Once you've created a table, you can perform three basic editing tasks:

•    Edit a record. This part's easy. Just modify cell values as you would in any ordinary worksheet.

•    Delete a record. First, go to the row you want to delete (you can be in any column). Then choose Home?Cells?Delete?Delete Table Rows. Excel removes the row and shrinks the table automatically. For faster access that bypasses the ribbon altogether, just right-click a cell in the appropriate row, and then choose Delete?Table Rows.

•    Add a new record. To add a record, head to the bottom of the table, and then type a new set of values just underneath the last row in the table. Once you finish typing the first value, Excel expands the table automatically, as shown in Figure 1-6.

If you want to insert a row but don't want it to be at the bottom of the table, you can head to your chosen spot, and then choose Home?Cells?Insert?Insert Table Rows Above (or right-click, and then choose Insert?Table Rows Above). Excel inserts a new blank row immediately above the current row.

Figure 1-6. Top: Here, a new record is being added just under the current table. Bottom: Once you enter at least one column of information and move to another cell, Excel adds the new row to the table and formats it. Don't worry—if Excel expands a table against your wishes, you aren't completely powerless. To correct Excel's mistake, look for the lightning bolt icon that appears immediately next to the newly added column. This doohickey is a smart tag that lets you reverse the expansion. Click it once, and Excel displays a pop-up menu giving you two choices: Undo Table AutoExpansion (to return your table to its previous size) and Stop Automatically Expanding Tables (to turn off this behavior altogether).

Note: Notice that when you insert or remove rows, you're inserting or removing table rows, not worksheet rows. The operation affects only the cells in that table. For example, if you have a table with three columns and you delete a row, Excel removes three cells, and then shifts up any table row underneath. Any information in the same row that exists outside the table is unaffected.

You may also decide to change the structure of your table by adding or removing columns. Once again, you'll find this task is like inserting or removing columns in an ordinary worksheet. (The big difference, as shown in Figure 1-7, is that any rows or columns outside your table remain unaffected when you add new rows or columns.)

Figure 1-7. Excel makes an effort to leave the rest of your worksheet alone when you change your table's structure. For example, when expanding a table vertically or horizontally, Excel moves cells out of the way only when it absolutely needs more space. The example here shows the point. Compare the before (top) and after (bottom) pictures: Even though the table in the bottom figure has a new column, it hasn't affected the data underneath the table, which still occupies the same column. The same holds true when deleting columns.

To add a column to the left of a column you're currently in, select Home?Cells?Insert?Insert Table Columns to the Left. Excel automatically assigns a generic column title, like Column1, which you can then edit. If you want to add a column to the right side of the table, just start typing in the blank column immediately to the right of the table. When you've finished your entry, Excel automatically merges that column into the table, in the same way that it expands to include new rows.

To delete a column, move to one of its cells, and then choose Home?Cells?Delete?Delete Table Column.

Finally, you can always convert your snazzy table back to an ordinary collection of cells. Just click anywhere in the table, and then choose Table Tools | Design?Tools?Convert to Range. But then, of course, you don't get to play with your table toys anymore.

D. SELECTING PARTS OF A TABLE

Once you've created a table, Excel provides you with some nice time-saving tools. For example, Excel makes it easy to select a portion of a table, like an individual row or column. Here's how it works:

•    To select a column, position your mouse cursor over the column header. When it changes to a down-pointing arrow, click once to select all the values in the column. Click a second time to select all the values plus the column header.

•    To select a row, position your mouse cursor over the left edge of the row until it turns to a right-pointing arrow, and then click once.

•    To select the entire table, position your mouse at the top-left corner until it turns into an arrow that points down and to the right. Click once to select all the values in the table, and click twice to select all the values plus the column headers.

Figure 1-8 shows an example.

Figure 1-8. You can easily select an entire column in a table. Just position the mouse as shown here, and then click once.

Once you've selected a row, column, or the entire table, you can apply extra formatting, or use another Excel feature like validation or charting. However, changing a part of a table isn't exactly like changing a bunch of cells. For example, if you give 10 cells a hot pink background fill, that's all you get—10 hot pink cells. But if you give a column a hot pink background fill, your formatting change may initially affect 10 cells, but every time you add a new value in that column, it also gets the hot pink background. This behavior, in which Excel recognizes that you're changing parts of a table, and applies your change to new rows and columns automatically, is called stickiness.

II. Sorting and Filtering a Table

As you've seen, Excel tables make it easier to enter, edit, and manage large collections of information. Now it's time to meet two of the most useful table features:

•    Sorting lets you order the items in your table alphabetically or numerically according to the information in a column. By using the correct criteria, you can make sure the information you're interested in appears at the top of the column, and you can make it easier to find an item anywhere in your table.

•    Filtering lets you display only certain records in your table based on specific criteria you enter. Filtering lets you work with part of your data and temporarily hide the information you aren't interested in.

You can quickly apply sorting and filtering using the drop-down column headers that Excel adds to every table.

Note: Don't see a drop-down list at the top of your columns? A wrong ribbon click can inadvertently hide them. If you just see ordinary column headings (and you know you have a bona fide table), choose Data?Sort & Filter?Filter to get the drop-down lists back.

A. APPLYING A SIMPLE SORT ORDER

Before you can sort your data, you need to choose a sorting key—the piece of information Excel uses to order your records. For example, if you want to sort a table of products so the cheapest (or most expensive) products appear at the top of the table, the Price column would be the sorting key to use.

In addition to choosing a sorting key, you also need to decide whether you want to use ascending or descending order. Ascending order, which is most common, organizes numbers from smallest to largest, dates from oldest to most recent, and text in alphabetical order. (If you have more than one type of data in the same column—which is rarely a good idea—text appears first, followed by numbers and dates, then true or false values, and finally error values.) In descending order, the order is reversed.

Note: Remember, it's technically possible to have numbers in Excel that are stored as text. Simply prefix these values with an apostrophe ('). For example, you might may store a number as text when you're entering numeric content that doesn't convey a numeric value, like a Social Security number. Excel sorts these values alphabetically, which means it looks at the text string one character at a time until it finds a difference. Thus, even though 42 is less than 102 in a numeric sort, the text 42 is greater than 102 in an alphabetic sort, because the first character 4 comes after 1.

To apply a new sort order, choose the column you want to use for your sort key. Click the drop-down box at the right side of the column header, and then choose one of the menu commands that starts with the word "Sort." The exact wording depends on the type of data in the column, as follows:

•    If your column contains numbers, you see "Sort Smallest to Largest" and "Sort Largest to Smallest".

•    If your column contains text, you see "Sort A to Z" and "Sort Z to A" (see Figure 1-9).

•    If your column contains dates, you see "Sort Oldest to Newest" and "Sort Newest to Oldest."

When you choose an option, Excel immediately reorders the records, and then places a tiny arrow in the column header to indicate that you used this column for your sort. However, Excel doesn't keep re-sorting your data when you make changes or add new records (after all, it would be pretty distracting to have your records jump around unexpectedly). If you make some changes and want to reapply the sort, just go to the column header menu and choose the same sort option again.

If you click a second column, and then choose Sort Ascending or Sort Descending, the new sort order replaces your previous sort order. In other words, the column headers let you sort your records quickly, but you can't sort by more than one column at a time.

B. SORTING WITH MULTIPLE CRITERIA

Simple table sorting runs into trouble when you have duplicate values. Take the product table sorted by category in Figure 1-9, for example. All the products in the Communications category appear first, followed by products in the Deception category, and so on. However, Excel doesn't make any effort to sort products that are in the same category. For example, if you have a bunch of products in the Communications category, then they appear in whatever order they were in on your worksheet, which may not be what you want. In this case, you're better off using multiple sort criteria.

Figure 1-9 (On next page). A quick click is all it takes to order records in ascending order by their category names. You don't need to take any action to create these handy drop-down lists—Excel automatically provides them for every table.

With multiple sort criteria, Excel orders the table using more than one sorting key. The second sorting key springs into action only if there are duplicate values in the first sorting key. For example, if you sort by Category and Model Name, Excel first separates the records into alphabetically ordered category groups. It then sorts the products in each category in order of their model name.

To use multiple sort criteria, follow these steps.

1.    Move to any one of the cells inside your table, and then choose Home?Editing?Sort & Filter?Custom Sort.

Excel selects all the data in your table, and then displays the Sort dialog box (see Figure 1-10) where you can specify the sorting keys you want to use.

Tip: You can use the Home?Editing?Sort & Filter?Custom Sort command with any row-based data, including information that's not in a table. When you use it with nontable data, Excel automatically selects the range of cells it believes constitutes your table.

2.    Fill in the information for the first sort key in the Column, Sort On, and Order columns.

Figure 1-10 shows how it works.

Figure 1-10. To define a sorting key, you need to fill in the column you want to use (in this example, Category). Next, pick the information you want to use from that column, which is almost always the actual cell values (Values). Finally, you need to choose the order for arranging values, which depends on the type of data. For text values, as in this example, you can pick A to Z, Z to A, or Custom List.

3.    If you want to add another level of sorting, click Add Level, and then follow the instructions in step 2 to configure it.

You can repeat this step to add as many sorting levels as you want (Figure 1-11). Remember, it makes sense to add more levels of sorting only if there's a possibility of duplicate value in the levels you've added so far. For example, if you've sorted a bunch of names by last name, you want to sort by first name, because some people may share the same last name. However, it's probably not worth it to add a third sort on the middle initial, because very few people share the same first and last name.

Figure 1-11 (On next page). This example shows two sorting keys: the Category column and the Model Name column. The Category column may contain duplicate entries, which Excel sorts in turn according to the text in the Model Name column. When you're adding multiple sort keys, make sure they're in the right order. If you need to rearrange your sorting, select a sort key, and then click the arrow buttons to move it up the list (so it's applied first) or down the list (so it's applied later).

4.    Optionally, click the Options button to configure a few finer points about how your data is sorted.

For example, you can turn on case-sensitive sorting, which is ordinarily switched off. If you switch it on, travel appears before Travel.

5.    Click OK.

Excel sorts your entire table based on the criteria you've so carefully specified (Figure 1-12).

Power Users’ Clinic: Sorting with a Custom List

Most of the time, you'll want to stick with the standard sorting orders. For example, you'll put numbers in numeric order, dates in chronological order, and text in alphabetical order. But not always. For example, you may have good reason to arrange the categories in Figure 1-12 in a different order that puts more important categories at the top of the table. Or you may have text values that have special meaning and are almost always used in a specific nonalphabetical order, like the days of the week (Sunday, Monday, Tuesday, and so on) or calendar months (January, February, March, April, and so on).

You can deal with these scenarios with a custom list that specifies your sort order. In the Order column, choose Custom List. This choice opens the Custom List dialog box, where you can choose an existing list or create a new one by selecting NEW LIST, and then typing in your values. Figure 1-13 shows an example.

Custom list sorting works best when you have a relatively small number of values that never change. If you have dozens of different values, it's probably too tedious to type them all into a custom list.

Figure 1-12. The worksheet shows the following sort's result: alphabetically ordered categories, each of which contains a subgroup of products that are themselves in alphabetical order.

Figure 1-13. Using a custom list for your sort order, you can arrange your categories so that Travel always appears at the top, as shown here. Once you've finished entering a custom list, click Add to store the list for future use.

C. SORTING BY COLOR

One of Excel's weirdest sorting options is sort-by-color, which lets you arrange your records based on the color of each cell. This oddball feature may come in handy if you've specifically highlighted certain cells that you're interested in (for example, you've given them a hot pink background fill). But this feature is really designed to work in conjunction with conditional formatting. You can create a conditional format that changes the foreground or background color of the cells you're interested in. Then, you use color sorting to make sure those cells rise to the top.

You can do color-based sorting in two ways. The quickest option is picking out cells with one background color, and then sending them to the top. To sort this way, open the drop-down color list, choose Sort By Color, and then choose your color. Excel helps out by letting you pick from a list that has all the background colors that are applied to cells in the current column. (This list doesn't include the background color that's applied from the table style.)

If you want to pick out multiple colors, you need to create a custom sort, and create a separate sort key to pick out each color. So if you want to sort cells in the order red, green, yellow, and then no color, you need to add three sorting keys. Choose Data?Sort & Filter?Sort, and then add three rules, as shown in Figure 1-14. Using the Sort On column, you can choose to sort using the background color (choose Cell Color) or the font color (Font Color).

The custom sort feature also works with icon sets if you choose Cell Icon from the Sort On column. Same idea, but instead of picking a color that should rise to the top of the list, you pick one of the icons from your icon set.

Figure 1-14. In this table, rows with a red background appear at the top, followed by green and then yellow rows (followed by any rows that don't have a background color). If you want to send a color to the bottom of a table instead of the top, change On Top to On Bottom.

D. FILTERING WITH THE LIST OF VALUES

Sorting is great for ordering your data, but it may not be enough to tame large piles of data. You can try another useful technique, filtering, which lets you limit the table so it displays only the data that you want to see. Filtering may seem like a small convenience, but if your table contains hundreds or thousands of rows, filtering is vital for your day-today worksheet sanity.

Here are some situations where filtering becomes especially useful:

•    To pluck out important information, like the number of accounts that currently have a balance due. Filtering lets you see just the information you need, saving you hours of headaches.

•    To print a report that shows only the customers who live in a specific city.

•    To calculate information like sums and averages for products in a specific group. You can use a function like SUBTOTAL(), described on page 1-32, to perform calculations using only the cells that are currently visible.

In theory, you could use the search feature in the data form window to find the records that interest you. However, while the search feature's perfect for finding one or two matches in a sea of data, it's much less suited to finding a large number of rows, because there's no way you can see them all at once, or print them out on a single sheet of paper.

Automatic filtering, like sorting, uses the drop-down column headings. When you click the drop-down arrow, Excel shows a list of all the distinct values in that column. Figures 1-15 and 1-16 show how filtering works on the Category column.

To remove a filter, open the drop-down column menu, and then choose Clear Filter.

Figure 1-15 (On next page). Initially, each value has a checkmark next to it. Clear the checkmark to hide rows with that value. Or, if you want to home in on just a few items, clear the Select All checkmark to remove all the checkmarks, and then choose just the ones you want to see in your table. Either way, remember to click OK to make your choices take effect.

Figure 1-16 (On next page). If you select Communications and nothing else from the Category list in the product table example, the table displays only the five products in the Communications category. Excel takes several steps to make the situation more apparent. First, it adds a strange funnel icon to the Category header. Second, it switches the row number color from black to blue for the filtered records (in this case, rows 2 through 6). Third, Excel adds a status bar message that indicates the number of filtered matches and the total number of records in the list.

E. CREATING SMARTER FILTERS

The drop-down column lists give you an easy way to filter out specific rows. However, in many situations you'll want a little more intelligence in your filtering. For example, imagine you're filtering a list of products to focus on all those that top $100. You could scroll through the list of values, and remove the checkmark next to every price that's lower than $100. What a pain in the neck that would be.

Thankfully, Excel has more filtering features that can really help you out here. Based on the type of data in your column (text, a number, or date values), Excel adds a wide range of useful filter options to the drop-down column lists. You'll see how this all works in the following sections.

Filtering dates

You can filter dates that fall before or after another date, or you can use preset periods like last week, last month, next month, year-to-date, and so on.

To use date filtering, open the drop-down column list, and then choose Date Filters. Figure 1-17 shows what you see.

Figure 1-17. Shown here is the mind-boggling array of ready-made date filtering options you can apply to a column that contains dates. For example, choose Last Week to see just those dates that fall in the period Sunday to Saturday in the previous week.

Filtering numbers

For numbers, you can filter values that match exactly, numbers that are smaller or larger than a specified number, or numbers that are above or below average.

To use number filtering, open the drop-down column list, choose Number Filters, and then pick one of the filter options. For example, imagine you're trying to limit the product list to show expensive products. You can accomplish this quite quickly with a number filter. Just open the drop-down column list for the Price column, and then choose Number Filters?Greater Than Or Equal To. A dialog box appears where you can supply the $100 minimum (Figure 1-18).

Workaround Workshop: The Disappearing Cells

Table filtering's got one quirk. When you filter a table, Excel hides the rows that contain the filtered records. For example, in Figure 1-17 you'll notice that the row numbers jump straight from 8 to 36, indicating that all the rows in between are hidden. In fact, all Excel really does is shrink each of these rows to have a height of 0 so they're neatly out of sight. The problem? When Excel hides a row, it hides all the data in that row, even if the data isn't a part of the table.

That property means that if you place a formula in one of the cells to the right of the table, then this formula may disappear from your worksheet temporarily when you filter the table! This behavior is quite a bit different from what happens if you delete a row, in which case cells outside the table aren't affected.

If you frequently use filtering, you may want to circumvent this problem by putting your formulas underneath or above the table. Generally, putting the formulas above the table is the most convenient choice, because the cells don't move as the table expands or contracts.

Figure 1-18. This dialog box lets you complete the Greater Than Or Equal To filter. It matches all products that are $100 or more. You can use the bottom portion of the window (left blank in this example) to supply a second filter condition that either further restricts (choose And) or supplements your matches (choose Or).

Filtering text

For text, you can filter values that match exactly, or values that contain a piece of text.

To apply text filtering, open the drop-down column list, and then choose Text Filters.

If you're performing filtering with text fields, you can gain even more precise control using wildcards. The asterisk (*) matches any series of characters, while the question mark (?) matches a single character. So the filter expression Category equals T* matches any category that starts with the letter T. The filter expression Category equals T???? matches any five-letter category that starts with T.

III. Dealing with Duplicate Rows

Hard-core table types know that every once in a while, despite the utmost caution, a duplicate value slips into a table. Fortunately, Excel has tools that let you find duplicates, wherever they're hiding, and remove them.

A. HIGHLIGHTING DUPLICATES

It's not too hard to fish out these duplicates—one option is to use sorting (described earlier) on the column where you suspect a duplicate exists. Then, if you spot two identical values, you can delete one of the table rows. Of course, in order for this technique to work, you have to be ready to scroll through all the records and check each one. In a supremely long list, that job could take some time.

Fortunately, Excel has another solution—conditional formatting. You can use it to make repeating values stand out like sore thumbs. Here's how:

1.    Select the table column (as described on page 1-11) that you want to check for duplicate values.

For example, you could select the Product ID column to look for products that have the same ID value.

Note: You can highlight more than one column, but if you do, Excel highlights identical values that appear in more than one column. For example, if the same number appears in the Product ID column and in the Price column, Excel highlights it even though it isn't really a duplicate.

2.    Choose     Home?Styles?Conditional            Formatting?Highlight         Cells

Rules?Duplicate Values.

 When the Duplicate Values dialog box appears, choose the type of formatting you'd like to use to highlight repeated values. People often choose to change the background color.

3.    Click OK.

 Excel changes the background color of all values that appear more than once in the selected column (or columns), as shown in Figure 1-19. Conditional formatting keeps working even after you've applied it. So, if you add a new record that duplicates the value of an existing record in the column you're checking, Excel immediately highlights it. It's like having a duplicate value cop around at all times.

Tip: If you have an extremely large table, you may like to use color-based sorting (page 1-18) to bring the duplicate records to the top of the table. For example, if you highlighted duplicate Product ID values with a light red background fill, you click the Product ID column's drop-down arrow, choose Sort By Color, and then pick the same color (which automatically appears in the menu).

Figure 1-19. Conditional formatting helps smoke out a product with the same name— "Escape Vehicle (Water)"—that happens to be in two different places in the table.

B. REMOVING DUPLICATES AUTOMATICALLY

Once you've found duplicate records, it's up to you what to do with them. You can leave them in your table or delete them by hand. However, if you don't want to keep the duplicates, Excel has a quicker solution, thanks to a built-in feature that hunts for duplicates and automatically removes the offending rows. Here's how to use it:

1.    Move to any one of the table cells.

Although you can technically use the duplicate removal feature with any range of cells, it works best with tables because you don't need to select the full range of data yourself.

2.    Choose Data?Data Tools?Remove Duplicates.

Excel shows the Remove Duplicates dialog box, where you can choose which columns to search for duplicates.

3.    Decide how many columns need to match in order for Excel to consider the record a duplicate. Add a checkmark next to each column you want to inspect.

For example, you may decide that any record with the same Product ID is a duplicate. When Excel scans the table, it removes any subsequent record that has the same Product ID as an earlier record, even if the rest of the data is different.

On the other hand, you may decide that you want to match several columns to prevent the possibility for error. For example, there may be two different products that share the same Product ID due to a minor typo. To avoid this possibility, you could inspect several columns (as shown in Figure 1-20), so that records are removed only if every column value matches. (You can't do this maneuver with conditional formatting and the Highlight Duplicates rule. When you use conditional formatting, you're limited to finding duplicates in one column—if you include more than one column, Excel treats them as one big batch of cells.)

Figure 1-20. In order to be considered a duplicate in this operation, the record has to have the same Product ID, Model Name, and Category. (The Price can vary.) If you need to select all the columns in a hurry, use the Select All button.

4.    Click OK to remove the duplicates.

Excel scans your table looking for duplicates. If it finds any, it keeps the first copy and deletes those that appear later on in the table. When Excel is finished, it pops up a message box telling you how many rows it removed and how many still remain.

You don't get a chance to confirm the deletion process, but you can reverse it by using the Undo feature (hit Ctrl+Z) immediately after it finishes.

Remember, when using the Remove Duplicates feature, you have no way of knowing what records Excel's deleted. For that reason, people often use the Highlight Duplicates rule first to check out the duplicates and make sure they don't belong.

Note: The Remove Duplicates feature and the Highlight Duplicates rule don't work in exactly the same way. For instance, Remove Duplicates treats repeated empty cells (blank values) as duplicates, and removes them. The Highlight Duplicates feature ignores empty cells and doesn't highlight them.

IV. Performing Table Calculations

Excel tables have several nifty features that help you out when you're performing calculations on the data inside a table.

One example's the way that automatic expansion works when you're adding new columns that contain calculations. For example, say you've got a table with three columns: Product ID, Model Name, and Price. If you want to add a new column that tracks the discounted price of each product (say, 90 percent of the regular price), then a table's a great timesaver. Once you've created the new discount price column for one row (see Figure 1-21), Excel fills the same calculation into every other row (Figure 1-22). You wind up with a table that shows the discounted price of every product. No copying and pasting required.

Figure 1-21. This worksheet shows a new column with a formula that's in the process of being entered. See Figure 1-22 for what happens next.

Figure 1-22 (On next page). Once you finish the formula shown in Figure 1-21, and then hit Enter, Excel expands the table to include the new column, and then fills the formula down to include every row. And if you scroll to the bottom of the table and start adding a new row, Excel is intelligent enough to automatically copy this new formula into the new row. In other words, once you add a formula to a column, that formula is sticky.

Once you've added a new column with a calculation, the only thing left to do is to give it a good name. Click the column header cell (which will say something like Column1), and then type in something more relevant (like Discount Price).

A. DYNAMIC CALCULATIONS

One of Excel tables' nicest features is the way they handle calculations. You can build formulas that use the data in a table, and Excel adjusts them automatically as the table grows or shrinks.

For example, imagine creating a table that stretches from row 1 to row 5. The first row contains the column headers, followed by four product rows. You create the following formula to add the total price of all the items:

=SUM(C2:C5)

If you now add a new item (that is, a new row) to the table, Excel automatically updates your formula so it includes this new item:

=SUM(C2:C6)

The same magic happens if your table shrinks because you've deleted an item. You just need to make sure that your range includes the whole column when you first create the formula. For example, consider the following formula, which omits the first item in the table:

=SUM(C3:C5)

If you expand the table now, Excel doesn't modify the formula. Fortunately, the program's got your back: It'll remind you if you create a formula that includes only a portion of your table by showing a green triangle in the corner of the cell, which is Excel code for "Is this really what you want to do?" When you move to the cell, an exclamation mark icon appears. Click it, and you get a short explanation and a menu of error-fixing options, as shown in Figure 1-23.

Figure 1-23. Excel warns you if you create a function that leaves out part of the information in a table. For example, the formula in C44 mistakenly left out cell C42. Excel offers to remedy the problem—just choose Update Formula to Include Cells.

Filtering settings don't affect your formulas. If you filter the table so that only some products are visible, the SUM() function still produces the same result, which is the total price of all products. If you don't want this behavior, you can use the total row (page 131) or Excel's SUBTOTAL() function (page 1-32).

B. COLUMN NAMES

So far, the table calculation examples you've seen have ignored a super-convenient feature called column names. You've already learned how Excel lets you create your own named ranges to refer to frequently used parts of your worksheet. But even if you don't create any named ranges of your own, you'll find that Excel equips every table with some built-in names.

First of all, Excel creates column names that you can use when you write formulas inside your table. For example, look back at Figure 1-21, which uses this formula to discount the current price:

=C2*0.9

When Excel copies this formula down the table, it adjusts the formula automatically using the familiar magic of relative references, so that each discounted price refers to the product price in the same row. For example, in row 10 (technically, the ninth row of data), the formula becomes:

=C10*0.9

But there's a shortcut. Instead of referring to the specific cell, you can use the name of the column (which is whatever text you've placed in the column header) inside square brackets. That shortcut means you can write the formula like this:

=[Price]*0.9

The [Price] name automatically refers to the value of the Price column in the current row, no matter where in the table you stick it.

Column names aren't only major timesavers, they also make your worksheets much easier to understand. After all, who could mistake the following formula?

=[Price]*[Tax Rate]+[Shipping Charge]

Note: If you use Excel's point-and-click formula creation, Excel uses the column names rather than the cell reference.

C. TABLE NAMES

You can't use column names in formulas that are in other parts of your worksheet. After all, Excel would have no idea what row you're trying to use. However, Excel gives you another shortcut. You can refer to your entire table anywhere on your worksheet by name.

This raises one excellent question—namely, how does Excel decide what name your table should have? When you first create a table, Excel picks a rather unexciting name like Table1, Table2, and so on. To change this name, click anywhere inside your table, and then edit the text in the Table Tools | Design?Properties?Table Name box. For example, ProductList makes a good name.

On its own, your table name refers to the entire range of cells that contains the data for your table. That means it includes the entire table, minus the column headers. You may want to use these cells with a lookup function like VLOOKUP(). For example, if you want to get the price (from the third column) of the product named Persuasive Pencil, you can use this easy-to-read formula:

=VLOOKUP("Persuasive Pencil", ProductList, 3, FALSE)

Excel includes another treat that lets you dig deeper into your table. You can use the table name in conjunction with a column name to get the range of cells that holds the data for just one column. Here's an example:

=SUM(ProductList[Price])

This gets the ProductList table, pulls out the cells in the Price column, and then passes them to the familiar SUM() function, which generates a total. You can quickly and efficiently get a hold of parts of a table. Figure 1-24 shows you how Excel helps you create formulas like these with its Formula AutoComplete feature.

Figure 1-24. Once you type a table name followed by an open bracket, Excel shows a list of columns in that table. You can select one using the up and down arrow keys, and then insert it into your formula by pressing Tab.

D. THE TOTAL ROW

Excel tables make it easy to calculate totals, averages, standard deviations, and other common formulas by using a dedicated summary row. To show this row, just select Table Tools | Design?Table Style Options?Total Row. Excel adds an extra row at the bottom of the table.

When the total row first appears, it shows only one piece of information: the number of records currently displayed. If you want to show some other type of information, choose a column, and then click the total row cell at the bottom of that column. A drop-down list appears with preset options. Choose one, and the total row displays the calculation in that cell, as shown in Figure 1-25.

Of course, you can create your own formulas to show the same information as the total rows do. However, the total row requires no work. It also uses only the rows that are currently visible, ignoring all filtered rows. You may or may not want this behavior. (It's great if you want to calculate totals for a small subset of filtered data, but it's not so good if you want to create grand totals that include everything.) If you don't want this behavior, you can write your own summary formula using functions like SUM(), COUNT(), and AVERAGE().

Figure 1-25. The Total row lets you perform common calculations with a single mouse click. In this example, the Total row displays the average price of the records that match the filter condition.

E. THE SUBTOTAL() FUNCTION

The total row conveniently works only with the currently visible rows in a table. To see this phenomenon in action, simply click one of the cells in the total row. If you look in the formula bar, you see that these cells use the SUBTOTAL() function to perform their calculations. That's because the SUBTOTAL() function is the only Excel function that takes table filtering into account.

The SUBTOTAL() function is the perfect solution for all the calculations in the total row, including sums, averages, counts, and more. The trick is that the first argument of the SUBTOTAL() function is a numeric code that tells Excel what type of calculation it should perform, while the second argument is the range of cells for the entire table column, from the first row to the last.

SUBTOTAL(function_code, column_range)

Table 1-1 lists all the function codes you can use with SUBTOTAL(). Note that each calculation type actually has two function codes associated with it. Function codes above 100 ignore hidden rows (rows that you've hidden using the Home?Cells?Format?Hide & Unhide?Hide Rows command). These function codes are the ones the total row uses. Function codes under 100 don't ignore hidden rows, but they still ignore rows you've filtered out. Figure 1-26 shows the SUBTOTAL() function in action.

Table 1-1. Function codes for SUBTOTAL()

Function used for calculation

Code that ignores hidden rows

Code that includes hidden rows

AVERAGE()

101

1

COUNT()

102

2

COUNTA()

103

3

MAX()

104

4

MIN()

105

5

PRODUCT()

106

6

STDEV()

107

7

STDEVP()

108

8

SUM()

109

9

VAR()

110

10

VARP()

111

11

In Figure 1-26, compare the results of the SUBTOTAL() functions in cells C2 to C6 to the formulas in cells D2 to D6, which just use the regular functions, like AVERAGE(), SUM(), MAX(), MIN(), and so on. In this figure, the formulas show the totals after filtering's limited the table to items in the Communications category.

Figure 1-26. This worksheet puts the SUBTOTAL() function to work calculating averages, sums, and more. The formulas that use the SUBTOTAL() function are in cells C2 to C6, while the formulas that use the closest ordinary Excel function (and don't take filtering into account) are in cells D2 to D6.

F. THE DATABASE FUNCTIONS

Excel also includes functions exclusively for use with long tables. These are the database functions, a set of 11 functions that let you analyze groups of data.

The database functions are very similar to the basic statistical functions like SUM(), AVERAGE(), and COUNT(). In fact, the database functions have the exact same names, but with an initial letter D—so you find a DSUM(), DAVERAGE(), DCOUNT(), and so on. The database functions differ from their nondatabase counterparts in that they can selectively filter out rows. In other words, when you use DSUM(), you can specify a set of criteria that a record must match in order to be included in the sum. (The filtering that you apply with the AutoFilter feature makes no difference to the database functions. They don't ignore hidden rows.)

Table 1-2 lists the database functions, along with comparable statistical function.

Table 1-2. Database functions

Function

Similar to

Description

DAVERAGE()

AVERAGE()

Calculates the average in rows that meet the specified criteria.

DCOUNT()

COUNT()

Counts the number of rows that meet the specified criteria.

DCOUNTA()

COUNTA()

Calculates the number of non-blank values in rows that meet the specified criteria.

DGET()

No equivalent

Returns the value that meets the specified criteria. If more than one value matches, DGET() returns the #NUM! error.

If no records match, it returns the #VALUE! error.

DMAX()

MAX()

Returns the maximum value in rows that meet the specified criteria.

DMIN()

MIN()

Returns the minimum value in rows that meet the specified criteria.

DPRODUCT()

 PRODUCT()

Calculates the product produced by multiplying all values in rows that meet the specified criteria.

DSTDEV()

STDEV()

Calculates the standard deviation in rows that meet the specified criteria.

DSUM()

SUM()

Calculates the sum of values in rows that meet the specified criteria.

DVAR()

VAR()

Estimates the variance of a sample population in the rows that meet the specified criteria.

DVARP()

VARP()

Estimates the variance of an entire population in the rows that meet the specified criteria.

DGET() is the only function without a statistical counterpart. DGET() works a little like the VLOOKUP() and HLOOKUP() functions, and it returns a single value in a row that meets the specified criteria.

Each database function uses the exact same three parameters:

DFUNCTION(table_range, field, criteria)

•    The table_range is the range that the function uses. The table range should include the entire table, including the column you want to use for your calculation and the columns to use for your criteria. The table range has to include the column headers, because that's how the database functions identify each column and match it up with the criteria. That means you can't use the automatically generated table names you learned about earlier (page 1-29).

•    The field is the name of the column you want to use for the calculation. For example, if you're using DSUM(), the field is the numeric column you want to total. Excel scans the column headers until it finds the column that has the same name.

•    The criteria is a range of cells that specifies all the conditions that rows must meet to be included in the calculation. This range can be as large as you want, and you're free to define conditions for multiple columns, or multiple conditions that apply to the same column. If the range contains only empty cells, the database function operates on all the items in the table.

To use a database function successfully, you need to create a suitable range of cells that you can use for criteria. Excel expects this range of cells to be in a strict arrangement. Here are some rules you'll need to follow:

•    Each condition needs two cells. One cell specifies the name of the field, and the other specifies the filter condition. For example, you can enter "Category" for the field name and "Tools" for the filter condition.

•    The cell with the filter condition must be directly under the cell with the field name. If you put them side by side, the database function just gives you an error.

•    You can add as many conditions as you want. You can even use multiple criteria that impose different conditions on the same columns. However, you must place them in columns next to each other. You can't stack them on top of one another.

Filter conditions follow the same rules that you used for search criteria in the data form window. Thus, you can use comparison operators like less than (<) and greater than (>) to create conditions like <500 (all prices under $500) or <>Travel (all products not in the Travel category). If you don't specify a comparison operator, Excel acts as though you've chosen the equal sign. In addition, it allows partial matches with text criteria. For example, the category criteria T will match both Travel and Tools.

You have total freedom to decide where on the worksheet to place your filter cells. Figure 1-27 shows an example that puts the filter cells at the top of the worksheet. To find the average price of all the products that match these criteria, you use the following formula:

=DAVERAGE(A9:D51, "Price", C1:E2)

Figure 1-27. In this example, you could use cell E2 to add another condition specifying a minimum price. However, because this cell is currently blank, Excel doesn't apply a price filter. Instead, it filters by matching a category and setting a maximum price.

The database functions are nice because they force you to define your criteria on the worksheet. If you want to change the criteria, you simply need to modify the condition cells, which makes database functions a perfect solution for building dynamic reports.

CHAPTER 1 – REVIEW QUESTIONS

The following questions are designed to ensure that you have a complete understanding of the information presented in the chapter. They do not need to be submitted in order to receive CPE credit. They are included as an additional tool to enhance your learning experience.

We recommend that you answer each review question and then compare your response to the suggested solution before answering the final exam questions related to this chapter.

1.    In database terminology, what do rows and columns of information contain:

a)    both rows and columns contain fields of information

b)    both rows and columns contain records of information

c)    rows contain records and columns contain fields of information

d)    rows contain fields and columns contain records of information

2.    Excel does contain some prebuilt table styles but they are limited, so it is usually more efficient to create your own table style.

a)    true

b)    false

3.    Which of the following table elements is not included in the Table Style Options:

a)    Total Row

b)    First Column

c)    Last Row

d)    Banded Columns

4.    When does it make sense to add additional levels of sorting beyond your primary sort:

a)    always

b)    only if there is a possibility of duplicate values in the levels you have already created

c)    whenever you have four or more columns

d)    never

5.    The custom sort feature can be used to sort which of the following defined items:

a)    specified text

b)    icon sets

c)    background colors

d)    all of the above

6.    The Remove Duplicates and Highlight Duplicates features will both ignore empty cells.

a)    true

b)    false

7.    When using the SUBTOTAL() function, which codes ignore hidden rows:

a)    function codes under 100

b)    function codes over 100

c)    any function codes you specify to ignore hidden rows

d)    all function codes ignore hidden rows

8.    Which database function estimates the variance of an entire population in the rows that meet the specified criteria:

a)    DGET()

b)    DCOUNT()

c)    DVAR()

d)    DVARP()

 

CHAPTER 1 – SOLUTIONS AND SUGGESTED RESPONSES

1.    A: Incorrect.  In database terminology, the rows are the records, and the columns are the fields of information; both are not referred to as fields of information.

B: Incorrect.  In database terminology, the rows are the records, and the columns are the fields of information; both are not referred to as records of information.

C: Correct.  In database terminology, the rows are records, and the columns are fields of information.  Rows (records) contain things such as customers, and columns (fields) contain things such as address.

D: Incorrect.  In database terminology, the rows are the records, and the columns are the fields of information; both are not referred to as fields of information.

(See page 1-1 of the course material.)

2.    A: True is incorrect.  Most of the time, it is not worth creating your own table style because it is simply too much work and the prebuilt table styles have a good selection of formatting choices.

B: False is correct.  The prebuilt table styles offer plenty of choices.  They are somewhat limited in the colors, but not in formatting choices.  So it is much more efficient to use these rather than create your own table style.

(See page 1-7 of the course material.)

3.    A: Incorrect.  Total Row is included within Table Style Options and lets you show or hide the summary calculations at the bottom of the table in the total row.

B: Incorrect.  First Column is included within Table Style Options and lets you apply different formatting to the first column in a table.

C: Correct.  Last Row is not an option within Table Style Options.  There is an option for Last Column, but not Last Row.

D: Incorrect.  Banded Columns is included within Table Style Options and lets you apply different formatting to each second column.

(See page 1-8 of the course material.)

4.    A: Incorrect.  Excel does allow you to create as many sorting levels as you would like; but just because you can, doesn’t mean you should.  Only add these if you think you will encounter duplicate values in your primary sort.

B: Correct.  It only makes sense to add additional levels of sorting when you feel you will encounter duplicate values in your primary sort.  You can add as many levels as you want, but don’t use them unless the need arises.

C: Incorrect.  It doesn’t matter how many columns you have, but rather the content of the column data.  Whenever you have the possibility of duplicate values beyond your primary sort, you will want to add multiple sorting criteria.  

D: Incorrect.  There is definitely a purpose for multiple sorting criteria.  Whenever there is the possibility of encountering duplicate values in your primary sort, you will want to add additional sorting criteria.

(See page 1-15 of the course material.)

5.    A: Incorrect.  The custom sort feature can be used whenever you want to designate a unique set of text (i.e., months of the year or days of the week), but it also works with icon sets and background colors, so individually, this cannot be the correct answer.

B: Incorrect.  The custom sort feature does work with icon sets, but it can also be used whenever you want to designate a specified text and background colors, so individually, this cannot be the correct answer.

C: Incorrect.  The custom sort feature does allow you to sort background colors, but it can also be used whenever you want to designate specified text and icon sets, so individually, this cannot be the correct answer.

D: Correct.  The custom sort feature allows you to designate specific text to sort by, plus it can sort multiple background colors and icon sets, so “all of the above” can be the only correct answer.  

(See page 1-18 of the course material.)

6.    A: True is incorrect.  The Remove Duplicates feature treats repeated empty cells as duplicates and will remove them, but the Highlight Duplicates feature ignores empty cells and will not highlight them.

B: False is correct.  The Remove Duplicates feature treats repeated empty cells as duplicates and will remove them, but the Highlight Duplicates feature ignores empty cells and will not highlight them.

(See page 1-26 of the course material.)

7.    A: Incorrect.  Function codes under 100 don’t ignore hidden rows, but they will ignore rows that you have filtered out.

B: Correct.  Function codes over 100 ignore hidden rows.  These function codes are the ones the total row uses.

C: Incorrect.  Function codes over 100 always ignore hidden rows and function codes under 100 do not.  This is not something you can specify or change.

D:  Incorrect.  Not all function codes ignore hidden rows; only those over 100 do.

(See page 1-32 of the course material.)

8.    A: Incorrect.  DGET will simply return the value that meets the specified criteria.  It will not estimate the variance of a population.

B: Incorrect.  DCOUNT() will count the number of rows that meet the specified criteria, but it will not estimate the variance of a population.

C: Incorrect.  DVAR() will estimate the variance of a sample population (not the entire population) in the rows that meet the specified criteria.

D: Correct.  DVARP() will estimate the variance of the entire population in the rows that meet the specified criteria.

(See page 1-34 of the course material.)


Chapter 2: Grouping and Outlining Data

As you saw in the previous chapter, Excel's tables are great tools for managing big collections of data made up of long, uniform columns (like lists of customers or products). But tables don't work so well if you need to show more complex information, especially if it's split into separate groups, each with its own subtotals.

Imagine a company sales report that lists a year's worth of quarterly results for each of its regions around the world. If you try to cram all this data into one long list—including subtotals for each region—you're likely to end up with a spreadsheet that looks like a numerical version of Twister. You'd be better off breaking out each region into a separate group of cells, and then tying everything together with another batch of cells that sums it all up.

Excel's grouping and outlining features are perfect for dealing with multilayered information like this. They help you quickly and easily expand and collapse big chunks of data and, in the process, make calculating summary information much easier. These tricks are remarkably easy to implement, but they rank as one of Excel's best-kept productivity secrets.

I. Basic Data Grouping

When you want to simplify your worksheets, you first have to learn how to group data. Grouping data lets you tie related columns or rows into a single unit. Once you've put columns or rows into a group, you can collapse the group, temporarily hiding it and leaving more space for the rest of your data. At its simplest, grouping is just a quick way to help you easily view what you want to see, when you want to see it. It's most handy when you want to show all the key summary information but none of the numbers that you need to crunch to arrive at these results.

Note: Excel's grouping settings also affect how your worksheet prints out—collapsed rows or columns don't appear in your printout.

Up to Speed: Comparing Grouping and Tables

Overall, grouping isn't as powerful as Excel's tables (covered in Chapter 1). Whereas tables include features for sorting, filtering, and searching, groups simply try to make it easier to work with data tables that use one or more levels of subtotals.

Of course, there's no reason you can't use both grouping and tables. You may group one or more columns in a big table so that you can see just the columns you want (all the columns that track address components, for instance).

This approach works well if you're grouping multiple columns, but it doesn't work as well if you're grouping rows. Excel tables grow dynamically, so you could have a problem. If you add a new row at the bottom of a table (or a new column on either side), the table automatically expands to incorporate the new information. Groups don't have the same behavior. As a result, if your table grows, some of it may slip out beyond an edge of the group.

A. CREATING A GROUP

To see how grouping can simplify complex worksheets, check out the sales report data shown in Figure 2-1. In this example, the information fits easily into the viewable area of the Excel window, but in a real-world company, you could easily end up needing to extend the worksheet with more columns and rows.

Figure 2-1. This worksheet shows the products sold at a retail store, with the numbers broken down in two ways: by quarter, and by the type of merchandise (regular priced, on-sale, and clearance). The data is sub-totaled so you can tell how much the company sold in a particular category over the whole year (cells B7:D7), how much it sold in a quarter in all categories (cells E3:E6), and how much it sold in total (cell E7).

You could add a variety of columns (representing different types of promotions, different discounts from regular prices, or different departments in the store). Or, you may add more rows to cover sales quarters from more than one year, or to track monthly, weekly, or even daily sales totals. In either case, the data would become fairly unwieldy, forcing you to scroll up and down the worksheet, and from side to side. By grouping columns (or rows) together, you can pop them in and out of view with a single mouse click.

Note: You probably already know that you can also use hiding to temporarily remove rows and columns from sight. But grouping is a much nicer approach—you can more easily pop data out of and back into view without going hunting through the menu. It also makes it more obvious to the person reading the worksheet that there's more data tucked out of sight. People more often use hiding to remove gunk that no one ever needs to see. Technically, grouping uses Excel's hiding ability behind the scenes to make grouped cells disappear temporarily.

Here are the steps you need to follow in order to group several columns together using the sales report example:

1.    Select the columns or rows you want to group.

In most cases, you'll want to group the detail columns that provide fine-grained information (like columns B, C, and D). You shouldn't include in your group any subtotals (like column E) that summarize your information, because you're likely to want to see those subtotals.

To make your selection, drag over the column headers so that you've selected the entire column. This way, Excel can tell right away that you want to group columns. In the sales report example, you'd choose columns B, C, and D.

2.    Choose Data?Outline?Group.

If Excel isn't sure which groups of cells you want to group, it shows a Group dialog box that lets you specify whether you're grouping columns or rows. (If you selected cells B3:D3, Excel would consider it an ambiguous selection because the selection could represent an attempt to group the columns or rows.) If the Group dialog box appears, make your selection, and then click OK.

When you group your columns, the worksheet doesn't change, although a new margin area appears at the top of the worksheet, as shown in Figure 2-2. This margin lets you collapse and expand your groups (see Figure 2-3). To collapse a group, click the minus sign (–), which then changes into a plus sign (+). To expand a collapsed group, click the plus sign (+).

Figure 2-2 (On next page). Here, columns B, C, and D have been grouped together.

Figure 2-3 (On next page). After clicking the minus icon in the worksheet's upper margin (as shown in Figure 2-2), you can quickly collapse this group, hiding columns B, C, and D. When you hide columns, Excel still uses them in calculations (like those in column E).

To remove a group after you've established it, expand the group, select all the columns, and then choose Data?Outline?Ungroup. If you select only some of the columns, Excel removes the selected columns from the group, but leaves the rest of the group intact—as long as at least two grouped columns remain. (If you remove a column from the middle of a group, you're actually left with two groups, one with the columns on the left, and one with the columns on the right.)

Tip: You can also ungroup a set of columns while the group's collapsed. Just select a range of columns that includes the hidden columns, and then use the Data?Outline?Ungroup command. Even though you've removed the group, the columns remain hidden and trapped out of sight! You can expose these columns after you've removed the group only if you select a range of columns that includes the hidden columns, and then choose Home?Cells?Format?Hide & Unhide?Unhide Columns.

You can use the same technique to group rows. In the sales report, you may want to group all the detail rows that give the quarter-by-quarter sales numbers, so that you can quickly collapse them and just see the totals. To do so, select rows 3 to 6 by clicking the first row number, and then drag down over all the rows you want to select. Then, select Data?Outline?Group. This time, a margin appears just to the left of the worksheet, letting you quickly hide the grouped rows. You can also have groups of rows and groups of columns, as shown in Figure 2-4.

Figure 2-4. The trick to mastering Excel's grouping abilities is understanding how the grouping bars work. The grouping bars show you the range of cells that are bound together in a single group. The minus sign (–) icon always appears just outside the group (either over the column to the left, or under the last row). That's because this icon has to remain visible after you've collapsed the group so that you can expand it.

Note: If you insert a new row between two grouped rows using the Home?Cells?Insert?Insert Sheet Rows command, Excel automatically places the new row into the group. The same's true if you insert a new column between two grouped columns using Home?Cells?Insert?Insert Sheet Columns.

You can add as many or as few groups as you like. If you have some data with far too many columns, then you can put all the extra columns into a single group, or into several separate groups. What's the difference? With one group, you can hide all the extra information with a single mouse click. With multiple groups, you'll need to do more clicking—but you have the ability to choose exactly what you want to see.

Keep in mind that if you create adjacent groups of columns, you always need to leave at least one column between each group. If you create two groups next to each other (like columns A to C and columns D to F), Excel automatically merges them into a single group. (This little quirk exists because Excel needs a free column to display the expand/collapse icon.) The same holds true for groups of rows.

Tip: If you decide to use multiple groups, it makes sense to group related rows or columns. For example, you may group all the columns that have address information into one group.

B. NESTING GROUPS WITHIN GROUPS

As you've seen, grouping lets you temporarily shrink the size of a large table by removing specific rows or columns. Grouping becomes even more useful when you create a worksheet that has multiple tables of information, like the one shown in Figure 2-5.

Figure 2-5. This worksheet includes two separate groups with data. Currently, the lower group is collapsed, so that you see only the totals. Thanks to grouping, you can fill a worksheet with dozens of separate tables with detailed information, but you can still see the summary information for them all when you collapse the groups.

Note: Don't get confused by thinking about Excel's structured tables, which you saw in Chapter 1. With grouping, you can use any combination of cells. Here, the data falls into a tabular structure with easily identifiable rows and columns, but it isn't an official Excel table.

Not only can you create separate groups for different tables, you can also put one group inside another. This approach gives you multiple viewing levels. Consider Figure 2-6 and Figure 2-7, which show two views of the same worksheet. This worksheet includes four tables, each of which has two levels of grouping. By collapsing the first-level group for a table, you can hide everything except the summary information (listed in the Total line). By collapsing the second-level group, you can hide the entire table.

Figure 2-6. All four tables are partially collapsed, and each has its second-level groups hidden so that they show just the total sales information for each region.

Figure 2-7. You see only two tables. The West Division is completely visible, because both its levels are expanded. The East Division is partially visible because its secondlevel group is collapsed. The North and South Division tables aren't visible at all because the first-level group for each table is collapsed.

Note: The only drawback is that the more levels of grouping you add, the more room Excel needs in the margins at the side of the worksheet in order to display all the grouping lines.

Technically, when you add more than one level of grouping, you're adding collapsible outline views to your worksheet, which let you quickly move back and forth between a bird's-eye view of your data and an up-close glimpse of multiple rows.

Timesaving Tip: Collapsing and Expanding Multiple Groups at Once

If you create a worksheet with multiple levels of grouping, you can find yourself doing a lot of clicking to expose all the information you're interested in (or to hide all the details you don't care about). Fortunately, Excel includes a helpful shortcut that lets you expand or collapse multiple groups at once: grouping buttons (shown in Figure 2-8).

Each grouping button is labeled with a number. If you click number 1, Excel collapses all the column or row groups. If you click number 2, Excel collapses all the groups except the top level. If you click number 3, Excel collapses all the groups except the first two levels, and so on. In the sales worksheet shown in Figure 2-7, you can click 3 to show the whole worksheet, 2 to show just the subtotals, or 1 to hide all the sales tables.

Excel displays a different number of grouping buttons, depending on your worksheet's levels of grouping. The largest numbered button you'll see is 8, because Excel allows a maximum of seven levels of nested groups.

Figure 2-8. Excel provides two sets of grouping buttons: one for row grouping (just above the row numbers), and one for column grouping (just to the left of the column letters).

C. SUMMARIZING YOUR DATA

Understanding how to quickly collapse and expand tables is all well and good, but before long, you'll want to add up the totals you've collected in these accordion-style tables. In the subtotaled sales report shown in Figure 2-7, the perfect way to complete this worksheet is to add a final table that sums up all the Total rows listed in the last row of each division.

To create a grand summary, you need to employ just a few more formulas. These formulas add the separate subtotals (contained in columns B, C, D, and E) to arrive at a final series of grand totals. The following formula would calculate the total sales in all divisions and all quarters for regular merchandise:

=B7+B15+B23+B31

Note: You could, of course, also calculate any of these subtotals by using the SUM() function.

You'll notice that no matter how you expand or collapse the groups in your worksheet, the result of these formulas is always the same. That's because, whether you're using straight addition (of multiple cells) or the SUM() function, Excel takes into account visible and hidden cells. If you're using grouping, however, it may occur to you that it would be handy if you could perform a calculation that deals only with the visible cells. That way, you could choose the portions of a worksheet you want to consider, and the formula could recalculate itself automatically to give you the corresponding summary information.

Good news: Excel gives you the power to build this sort of formula. All you need is the awesome SUBTOTAL() function. As you may recall from Chapter 1, the SUBTOTAL() function is a useful tool for calculating totals in filtered lists. But it's just as helpful when you apply it to outlines because it ignores collapsed rows and columns.

As explained on page 1-32, the first argument (known as the calculation code) you use with the SUBTOTAL() function tells Excel the type of math you want to do (summing, averaging, counting, and so on). If you want to ignore the hidden cells in collapsed groups, you have to use the calculation codes above 100. If you want to perform a sum operation, then you need to use the code 109. (For a full list of the SUBTOTAL() calculation codes, see page 1-33.)

Once you've chosen the calculation code, you need to specify the cells or range of cells you want to add. Here's an example that rewrites the earlier formula to sum only visible cells:

=SUBTOTAL(109,B7,B15,B23,B31)

Figure 2-9 shows the difference between using the SUBTOTAL() function and the SUM() function.

Figure 2-9. This worksheet calculates summary information using two different approaches. The formulas in row 37 use the SUM() function, while the formulas in row 38 use the SUBTOTAL() function. In the SUBTOTAL() calculation, Excel doesn't use the subtotals for the completely hidden tables, giving a different result than that provided by the SUM() function.

D. COMBINING DATA FROM MULTIPLE TABLES

Instead of writing your summary formulas by hand, you can generate a summary table automatically that takes advantage of Excel's ability to consolidate data. Consolidation works if you have more than one table with precisely the same layout. You can use consolidation to take the different tables shown in the sales report (West Division, East Division, and so on) and calculate summary information. Excel creates a new table that has the same structure but combines the data from the other tables. You can choose how Excel combines the data, including whether it totals, averages or multiplies the numbers, and so on.

Note: Data consolidation works with any sort of tabular data. You don't need to create the structured tables you learned about in Chapter 1.

Data consolidation works with any worksheet (with or without grouping); you can even use it to analyze data in identically structured tables from different worksheets.

Tip: For best results, you should consolidate data with the exact same layout only. Although you can coax Excel into combining differently sized ranges of data, it's all too easy to confuse yourself about what is and isn't combined. To make life easier, only consolidate ranges that have numeric data or identical labels (like column or row titles).

Leave out the overall table title, because there's no way to consolidate it.

To consolidate the sales report data, follow these steps:

1.    Move to the location where you want to insert the summary table.

Excel inserts the summary table, starting at the current cell. Make sure you've scrolled down past all your data, so you don't overwrite important information.

2.    Choose Data?Data Tools?Consolidate.

The Consolidate dialog box appears, as shown in Figure 2-10.

3.    From the Function pop-up menu, choose how you want to combine numbers.

In the sales report, Sum is the best choice to calculate total sales. However, you may want to create separate tables that pick out the best or worst sales using Max and Min.

4.    Click inside the Reference text box. Now, drag to select the first table you want to consolidate in the worksheet.

If Excel's main window isn't already visible, click the icon at the right end of the Reference text box to collapse the Consolidate dialog box. You have to click this icon again to restore the window when you're done selecting the cells you want.

Figure 2-10. This worksheet contains two detailed sales tables that are about to be consolidated. Both ranges have been added to the Consolidate dialog box (they're listed in the "All references" list), and the "Top row" and "Left column" checkboxes have been turned on so that Excel can find and use the headings on either side of the table.

5.    Once you've selected the appropriate cells, click the Consolidate dialog box's Add button.

The range appears in the "All references" list.

6.    Return to step 4 to select the next table you want to consolidate.

Repeat steps 4 and 5 for each table you want to consolidate.

7.    If your selection includes labels (like row or column titles), select the "Top row" or "Left column" checkbox to tell Excel where the labels are.

If you don't tell Excel where the labels are, it ignores these cells, and the corresponding cells in the summary table wind up blank. But if you use these checkboxes to tell Excel where the labels are, Excel simply copies the labels directly to the summary table.

Note: If the labels don't match exactly in the ranges you're using, you may need to clear the "Top row" and "Left column" checkboxes to perform your consolidation. Otherwise, depending on the placement of these labels, Excel may refuse to consolidate your data.

8.    Click OK to generate the summary table.

When creating the consolidated data, Excel copies headings and calculates numbers, but it doesn't copy any of the source formatting. Figure 2-11 shows the result.

Figure 2-11 (On next page). Here, the newly created consolidated data table is at the bottom of the worksheet. In this case, someone used the Sum option, which means that the consolidated data shows the totals you get by adding the values from the separate tables. Cell B18 has the consolidated data for regularly priced merchandise sold in the first quarter in both the West and East Divisions (cells B3 and B11).

Data consolidation's big disadvantage is that it generates a table filled with numbers, rather than formulas. Microsoft's engineers probably designed the consolidation feature this way so you can consolidate data from multiple files and not worry about losing the information if the source files move or their structure changes.

But this behavior means that if you modify any of the sales figures, the summary table doesn't update. Instead, you need to generate a completely new summary table by using the Data?Data Tools?Consolidate command. Fortunately, the second time around should be quite a bit faster, because Excel keeps track of all the ranges you selected for consolidation, so you don't need to define them again.

II. Grouping Timesavers

So far, you've learned how to tame an intimidating worksheet and neatly organize it into groups. Why stop there? This section covers a few other tools that, along with the grouping tools you've just mastered, will make your life easier. These tools include automatic outlining and subtotaling (both of which can create data groups automatically).

A. AUTO OUTLINE

Adding groups to a large worksheet can be tedious. You can leap over that tedium in a single bound with Auto Outline, a feature that gets Excel to examine your worksheet, and then create all the column and row groups you need automatically.

Like most automatic features, Auto Outline is a great solution when it works, and it's absolutely no help the rest of the time. Automatic outlining is an all-or-nothing affair, giving you either a grouped worksheet or the unhelpful error message, "Cannot create an outline". You also don't have any ability to configure how many levels of nesting Excel uses, how many groups it creates, and whether it creates column groups, row groups, or both.

With a bit of planning, however, you can set up your worksheets so they're Auto Outlinefriendly. Here are the key points. Excel makes all its guesses about how to implement Auto Outline based on any formulas it finds that give it clues about the structure of your worksheet. If you have a formula that uses SUM() to total the cells in multiple columns in the same row, Excel assumes that it can place these columns in a single group so that you can collapse the details and leave just the totals visible. If Excel finds a formula that uses the data in the rows above it, the program assumes that a row group's in order. In other words, Auto Outline requires formulas to determine the grouping levels it should add. If you don't use any summary formulas at all, or if your worksheet uses a table, the Auto Outline feature doesn't work.

Note: If you don't have any formulas at all on your worksheet, you're guaranteed that Excel can't create an outline.

To use automatic outlining, select the portion of the worksheet that you want to outline, and then choose Data?Outline?Group?Auto Outline. Generally, it's easiest to select the whole worksheet (click the square just outside the top-left corner of the worksheet) to apply grouping to all your data at once.

If you don't like the results, you can't undo the outlining operation, but you can remove all the groups from your worksheet by choosing Data?Outline?Group?Clear Outline.

B. AUTOMATIC SUBTOTALING

Auto Outline isn't the only outlining trick that Excel has up its sleeve. The Subtotal tool lets you create groups and subtotals all in one click—a feature that can save you scads of time.

To use automatic subtotaling, you need to have a long table of repetitive data (product catalogs, sales transactions, calorie-counting meals on the Atkins plan). You can't use more than one table—instead, the goal is to get Excel to break your table into summarized subtables for you. Each table gets its own subtotal (for any or all of the columns that you choose) and all the subtotals get added together for your very own Grand Total.

To subtotal and outline your data simultaneously, follow these steps:

1.    Sort your data so that the rows you want to group are together in the list.

Before subtotaling your data, you may need to sort it into the right order. If you have a list of products and you want to subtotal the product information by category, you need to sort the table by category before you begin. This step ensures that all the products in each category are grouped together. For detailed information about sorting, see page 1-12.

2.    Once you have the list in the correct order, select the range of cells that contains the list.

Usually, the easiest option is to select each of the columns in your list by dragging across the column headers.

3.    Choose Data?Outline?Subtotal.

The Subtotals dialog box appears.

4.    In the "At each change in" pop-up menu, choose the field under which you want the subtotal to appear. The menu provides a list with all the column titles in your selection.

If, say, you wanted to group together (and create subtotals for) products that have the same Category label (as shown in Figure 2-12), you'd choose Category. Excel inserts a subtotal row each time the category label changes. (In the figure, the Communication products subtotal first, the Deception products next, and so on.) In order for this process to work, the list must be sorted—as you did in step 1—so that all records with the same category are already next to each other.

5.    In the "Use function" pop-up menu, choose the function you want to use to calculate the subtotal information.

This list includes everything supported by the SUBTOTAL() function, including averages, counts, and subtotals. Unfortunately, you can't total different columns using different functions.

Figure 2-12 (On next page). This worksheet has a long list of product data that's about to be subtotaled. Excel creates the subtotals for each group of products that has a different label in the Category column. The subtotal row includes the average price of the products in each group.

6.    The "Add subtotal to" list includes all the column names in your selection. Put a checkmark next to each column you want to generate a subtotal for.

To calculate the subtotals, Excel uses the function you chose in step 5.

7.    If you want to start each group on a new page, turn on the "Page break between groups" checkbox.

This option works well if you have large groups, and you want to separate them in a printout.

8.    If you want to display the summary information at the end of group, choose "Summary below data." Otherwise, the totals appear at the beginning of the group.

If you're dividing products into category groups, choose "Summary below data" to make sure you'll see the listing of products, followed by the subtotal row.

9.    Click OK to group and subtotal the data.

Excel inserts a row in between each group, where it adds the formulas that calculate the subtotals for the group (Figure 2-13). All of the formulas use the SUBTOTAL() function. Excel also calculates grand totals for all the subtotals that it's generated.

Figure 2-13. The product list, with subtotaling applied. Rows 7 and 13 contain the newly calculated subtotals. Excel has also added a grand total at the bottom of the list (which isn't shown in this figure).

If you want to remove subtotals, you have two choices. The easiest approach is to choose Data?Outline?Subtotals to open the Subtotals dialog box, and then click the Remove All button. Alternatively, you can replace the existing subtotals with new subtotals. To do this, choose different options in the Subtotal dialog box, and make sure the "Replace current subtotals" checkbox is turned on.

CHAPTER 2 – REVIEW QUESTIONS

The following questions are designed to ensure that you have a complete understanding of the information presented in the chapter. They do not need to be submitted in order to receive CPE credit. They are included as an additional tool to enhance your learning experience.

We recommend that you answer each review question and then compare your response to the suggested solution before answering the final exam questions related to this chapter.

1.    Data grouping allows you to sort, filter, and search similar to Excel’s tables.

a)    true

b)    false

2.    How many sets of grouping buttons does Excel provide:

a)    2

b)    4

c)    10

d)    unlimited

3.    Which of the following statements is incorrect regarding data consolidation used to combine data from different tables:

a)    data consolidation works with any sort of tabular data

b)    data consolidation works when there is more than one table with precisely the same layout

c)    data consolidation can only be performed on tables without grouping

d)    data consolidation can be used to analyze data in identically structured tables even from different worksheets

4.    With Excel 2010, if you don’t have any formulas in your worksheet, you will not be able to use the Auto Outline function.

a)    true

b)    false

CHAPTER 2 – SOLUTIONS AND SUGGESTED RESPONSES

1.    A: True is incorrect.  Data grouping isn’t as powerful as Excel’s tables.  Groups enable you to work more efficiently with data tables, but they do not allow for sorting, filtering, and searching.

B: False is Correct.  Groups basically just make it easier to work with data tables that use one or more levels of subtotals, but they do not let you sort, filter, or search like you can with Excel’s tables.

(See page 2-1 of the course material.)

2.    A: Correct.  Excel provides two sets of grouping buttons – one for row grouping and one for column grouping.

B: Incorrect.  Excel provides only two sets of grouping buttons – one for row grouping and one for column grouping, not four.

C: Incorrect.  Excel provides only two sets of grouping buttons – one for row grouping and one for column grouping, not ten.

D: Incorrect.  There is no limit to the number of groups you can create for a spreadsheet, but Excel provides only two sets of grouping buttons.

(See page 2-9 of the course material.)

3.    A: Incorrect.  It is true that data consolidation works with any sort of tabular data.

B: Incorrect.  It is true that data consolidation will work any time there is more than one table with exactly the same layout.

C: Correct.  Data consolidation can work with any worksheet, even those that contain groupings.

D: Incorrect.  It is true that data consolidation can be used to analyze data in different worksheets as long as the tables are structured identically.

(See page 2-11 of the course material.)

4.    A: True is correct.  Auto Outline requires formulas to determine the grouping levels that it should add.  If you don’t have any formulas in your worksheet, Excel cannot create an outline.

B: False is incorrect.  Auto Outline does require formulas in order to operate.  If you do not have any formulas, you will get the error message, “Cannot create an outline.”  Auto Outline is only useful in specific situations.  You have no control over how many groups to create, the number of levels to include, or whether it uses row or column groups.

(See page 2-15 of the course material.)


Chapter 3: Templates

Spreadsheets are rarely one-of-a-kind creations. After you've built the perfect sales forecast, expense report, or personal budget, you'll probably want to reuse your hard work instead of starting from scratch. One approach is to save an extra copy of your workbook and just change the data for each new spreadsheet you want to create. That works fine, but it's not terribly convenient.

Excel provides a more streamlined option with templates, which are spreadsheet blueprints that you can use to create new files. Templates don't necessarily hold any data (although they can if you want them to). Instead, their main role in life is letting you format them to your heart's content—adding things like column titles, fancy shading, and complex formulas—so that every time you want a worksheet that looks like the template, all you have to do is select the template and voilà! A new file appears, containing all the design elements you created in the original template.

For example, you could create a monthly expense report template containing all the appropriate formulas and formatting you need, and use it to create a fresh report each month. If you ever need to change your report's formatting or calculations, then you simply modify the template, and all future expense reports will use the new version.

In this chapter, you'll learn about Excel templates, and how to get the most out of them. You'll begin by exploring how you can use Excel's existing set of templates, which provide a helpful starting point for all kinds of common spreadsheets. Then you'll learn how to create your own equally professional templates.

I. Understanding Templates

Before you begin using and creating templates, it's important to understand what they really are. Many template novices assume that templates are a special sort of file that's similar to Excel spreadsheets. However, the crafty individuals who created Excel actually designed template files so that they're exactly the same as spreadsheet files. (Whether this feature is a brilliant masterstroke of simple, elegant design or the product of terminally overworked programmers is up to you to decide.)

If you use a tool like Windows Explorer to look in a folder that contains spreadsheet files and template files, you'll notice that templates have different files extensions. Whereas ordinary Excel files have the extension .xlsx (or .xlsm if they contain macro code), templates have the extension .xltx (or .xltm with macros). For example, could be a template file for creating invoices, while would be an actual invoice. However, you can put the exact same type of data in both files: elaborately formatted worksheets, formulas, numbers, text, and so on.

Note: Astute readers will notice that there's just a single letter of difference between the file format extensions. In Excel spreadsheets, the s stands for spreadsheet (.xlsx), whereas in template files the t stands for template (.xltx).

Based on this similarity, you might wonder why you should bother using templates at all. The difference is in how Excel handles templates. Here are some of the differences:

•    Templates are usually stored in a special folder, so that the latest version of the template's always easily accessible.

•    It's almost impossible to accidentally overwrite a template file. That's because Excel automatically creates a new worksheet when you select a template (more on how to actually use templates in a moment).

•    Spreadsheet experts often create templates brimming with advanced Excel features (things like data validation tools and fancy formula design). These features help you prevent errors (like entering incorrect data or entering it in the wrong place) and provide additional guidance to others who use the template.

Finally, it's easy to distinguish template files (the blueprints) from real spreadsheets because of the different file extension they use. The icon also looks slightly different.

Most organizations and businesses maintain a group of templates that define things like standard layout and formulas for common types of spreadsheets like invoices and expense reports. Some organizations host these templates in a central location on a network file server or web server (the two best options), or just distribute them to each employee who needs them.

So how do you put templates to work? The trick is to understand your options when creating them.

II. Creating a New Workbook from a Template

So far, every example worksheet in this book has started from scratch, with nothing more than Excel's empty grid of cells. This approach is a great way to learn the nutsand-bolts of how Excel works, but it's not always necessary. In many cases, you can find a template that matches the worksheet you want to create. If so, you'll save yourself a good deal of formatting and formula-writing work.

To use a template, begin by selecting File?New. Excel switches to backstage view, as shown in Figure 3-1. But here's the trick: instead of creating an ordinary, blank workbook (by choosing "Blank workbook", and then clicking Create), you click one of the other options to begin hunting for a suitable template.

Here are your choices:

•    "Recent templates" shows a list of templates you've used recently. This gives you a handy way to jump straight to your favorite template. But at first this list is empty, because you haven't used any templates.

Figure 3-1. Using Excel's backstage view, you can create a new, empty workbook or create a workbook based on a template.

•    "Sample templates" shows a small set of templates that come with Excel. This category has very few templates to choose from, but you'll find classics like Expense Report and Personal Monthly Budget.

•    "My templates" lets you choose from one of the custom templates that you've created and saved on your computer, or one of the templates you've downloaded from Office Online.

•    "New from existing" displays a dialog box that lets you choose a spreadsheet file. When you click OK, Excel opens a duplicate version of the selected file, giving it a new name. When you save the spreadsheet, Excel prompts you to supply a new name or to use the one it generated (a variation based on the name of the original workbook). Either way, you end up saving a new copy of the original file.

Note: The "New from existing workbook" option is conceptually similar to using a template, in that it creates a new workbook based on an existing workbook file. However, it's a better idea to create a workbook using a template for several reasons. First of all, because templates are stored in a central place, you don't need to hunt for the file you want to use. Additionally, if the template is properly fine-tuned, you don't have to bother removing old data.

•    The categories under the heading " Templates" let you browse the vast treasure trove of freely downloadable templates that Microsoft provides on its website. You'll find hundreds of handy templates here, organized into logical categories like Agendas, Invoices, and Reports. As you'll see shortly, you can also use the search box to hunt for online templates by keyword (Downloading Templates (Method 2: The Office Online Website)).

These options are all you need to start using templates. Still feeling template intimidated? The next section walks you through all the steps you need to use a template.

A. DOWNLOADING TEMPLATES (METHOD 1: FROM BACKSTAGE VIEW)

Excel's backstage view lets you choose from Microsoft's expansive online catalog of templates without leaving the comfort of Excel. Not only does this ability give you a way to get the latest template innovations, it also lets you dig up specialty templates like a secret Santa gift exchange list, a baseball scorecard, and a baby shower planner. Best of all, the whole process is so seamless you don't even notice you're downloading a template from the Web.

Note: Obviously, if you're using a computer that doesn't have an Internet connection, you're limited to the templates installed on your computer. However, you're not completely cut off from the rest of the word. You can surf to the Office Online website when you're connected, and then use your web browser to search and download Excel template files that seem interesting. You'll learn how in the next section.

Here's how it all goes down:

1.    Choose File?New.

Excel switches to backstage view and shows a list of template options.

2.    Choose one of the categories under the " Templates" heading. If none of the categories seems quite right, choose "More categories" to see some more exotic options.

A  list of templates appears (Figure 3-2).

Figure 3-2. Browsing by category is the fastest way to find a template that interests you. For example, under the Budget category you'll find templates for personal budgets (family budgets, wedding costs, and even a gardening budget) and templates for business budgets (marketing plan, business trips, operating expenses, and so on).

3.    If you find a template that seems right, click it once to preview it.

The preview information appears at the far right of the window. The information includes the template's file size, its star rating (as ranked by other Office fans), and a preview of what it looks like.

Note: The template rating plays the same role as customer reviews on — other folks who have downloaded the template can give it a score of up to five stars (but they can't write a description).

Note that you can't rate a template from inside Excel. Instead, you need to surf to the Office Online site to submit a star rating. See page 3-7 for details.

4.    If you like the template, click Download to create a new workbook based on it.

A  progress indicator appears while the template downloads to your computer. Because templates are quite small, it rarely takes more than a minute to download one. If you have a high-speed connection, you'll probably have the template in two or three seconds.

Note: When you download a template, Microsoft uses some fancy tricks to inspect your current installation of Windows and Excel to make sure you're not running pirated software. So, if you bought your copy of Excel for $7.99 from a street vendor in Chinatown, you probably can't download new templates.

After Excel downloads the template, it creates a new workbook based on that template (Figure 3-3). You can fill your data in this workbook, and, when you save it, Excel prompts you to choose a new file name. Excel suggests a name based on the name of the template. For example, if you select the loan amortization template, Excel suggests a name like "Loan Amortization " (you can, of course, change this name to anything you want).

Figure 3-3. When you choose the loan amortization template (from the Schedules category), Excel automatically creates a complete worksheet with all the calculations you need, showing each payment you'll need to make over the life of the loan. You need to enter only the loan amount, interest rate, duration, payment frequency, and start date (cells D5 to D9); Excel calculates the rest automatically using its financial functions.

B. DOWNLOADING TEMPLATES (METHOD 2: THE OFFICE ONLINE WEBSITE)

Instead of getting all your templates served to you right in Excel, you can download them the old-fashioned way, from the Office Online website where they live. You may make the trip to Office Online for a few reasons:

•    You want to download a template to use on another computer. Perhaps your home computer lacks Internet access, and you're using a friend's computer to get the template goodness you crave.

•    You want to give template feedback (so yours can be one of the thousands of votes that make up a typical template ranking). You can also submit feedback with requests for new templates you'd like to see added to the collection.

•    You want to search for all Office Online templates without worrying about what program uses them. Maybe you've decided you want a way to print out a calendar for 2011, but you don't know whether Excel or Word has the better template.

Timesaving Tip: Searching for a Template

The process described in the last section lets you find a useful template by digging through various categories. Excel also gives you another choice—you can search for a template by keyword. This approach is great if you want to quickly locate a template for a specific function or industry (you could search for "invoice" or "real estate"). Keywords aren't as useful if you just want to browse what's available. Of course, it's up to you to decide which way to go. In fact, many Excel gurus use both, depending on the task at hand.

To search by keyword, type your search word(s) into the "Search for templates" text box (Figure 3-4), and then press Enter. You can enter as many words as you want. Excel finds templates that contain any of the words you specify (it gives preference to templates that have all the search words). Try using a word that you think may appear in the template name (like "calendar") or a phrase in one of Microsoft's online categories (like "personal finance").

Figure 3-4 (On next page). In this example, a search for the word "sudoku" turns up an Excel template for solving Sudoku number puzzles.

•    You want to browse the other news and features on the Office Online website. If it's September, you may find quick links to some popular back-to-school lessonplanning templates.

•    You're just happier in a web browser.

Here's how to download a template from the Office Online website:

1.    Open a web browser, and then surf to

It's best to use Internet Explorer, because it supports the ActiveX standard, which Office Online uses for its download-a-template feature. If you use a different browser that doesn't support ActiveX (like Firefox), you'll get a warning message explaining the issue when you try to download a template. You'll then be forced to download a compressed template file, which you must unzip on your own before you can use it. (The Office Online website provides more information about what to do, but you can save the headache altogether by using Internet Explorer.)

2.    Scroll down the web page to the Browse Templates section. Click a category link that interests you.

The top portion of the page is reserved for new and featured templates, some of which Microsoft created in response to requests (send in your request by clicking the "Suggest a template" link on the left side of the web page); other templates vary by season. For example, in the beginning of May, you'll find quick links to Mother's Day card and gift label templates. If you don't want to browse by category, you can perform a keyword search using the search box at the top of the page (and skip ahead to step 4).

3.    Depending on the category you've chosen, you may see another list of subcategories. If you do, just click the appropriate subcategory link.

You'll end up at a list that shows all the matching templates. The list shows the name, creator, and rating of each template.

Note: The Templates web page provides templates for all Office applications, including Word, Excel, Access, and InfoPath (a program that lets companies create souped-up data entry forms). Of course, you can't use a Word template in Excel, and vice versa. To tell which program a given template requires, look at the icon immediately to the left of the name. If you see the familiar Excel application icon (a green square with an "X" symbol), you can use this template to create new workbooks. Some templates (like greeting cards) are likely to be for Word, while others (like financial forms and worksheets) are usually for Excel.

4.    Choose a template file from the list by clicking its name.

A page appears with detailed template information (Figure 3-5). This information includes the size of the template, the required software version (most templates work equally well on all Excel versions since Excel 2000), and a preview graphic that shows what the template looks like.

Figure 3-5. Once you've chosen a template, you'll see a detailed page with information about the size of the file and the time it takes to download it—if you connect to the Internet using a telephone line and a meager 56K modem. (Template downloading is almost always very fast, regardless of your connection.) You'll also see an image that shows a surprisingly detailed preview of the template, complete with sample data.

5.    If you're still happy with the template, click Download. Or, if you haven't found the right template, then you can click your browser's back button to navigate back to the template list.

You may be presented with a Microsoft license agreement. (This essentially states that you won't sue Microsoft if the template makes your computer explode. Don't worry; it won't.) To continue, click Accept.

While the template is downloading, a pop-up browser window appears with a progress indicator. Once the template downloads (usually, just a fraction of a second later), a Save As dialog box will appear. Excel points the dialog box to your personal template folder (page 3-11), a location on the current computer where you can store all the templates you download and the templates you create.

6.    Click Save to save the template in your templates folder.

Optionally, you can give the template a new name or move to a different folder before you save it. For example, on page 3-14 you'll learn how to store templates in the workgroup templates folder, a location that lets multiple people use the same templates.

What happens once the template downloads depends on whether you have the right program installed. If you're downloading an Excel template and you have Excel installed, then Internet Explorer launches Excel, and Excel creates a new workbook based on the template you chose, just as if you'd picked it from the backstage view. If you don't have Excel installed, you'll need to take the template file and email it or copy it to an Excel-enabled computer before you can use it.

III. Creating Templates

Ready-to-use templates are a fantastic innovation because they provide fine-tuned worksheets without forcing you to write a single formula. Of course, these templates also have a major drawback: No matter how clever Microsoft programmers are, they can't predict your every need.

For example, the Travel Services Invoice provides a generic worksheet that a travel agency may use to bill its customers. But what if you need to group different types of expenses separately, apply different discount rates to various groups, include a late fee, and tack on a few miscellaneous charges to pad your company's bottom line? If that's the case, you'd need to add your own formulas, restrictions, and formatting to the template. In that case, starting over from scratch and creating a template with the exact features you need is probably easier.

Fortunately, creating custom templates isn't difficult at all.

Tip: You can use one of Excel's templates as a starting point for your own templates. Just follow the process described earlier, edit the template (by adding information, changing formatting, and so on), and then save it as a new template. However, you'll generally find it easier (at least at first) to create your own templates from scratch.

A. UNDERSTANDING CUSTOM TEMPLATES

Earlier in this chapter, you learned how to create a new workbook by choosing a template in backstage view. In order to understand how to create your own templates, you need to know how this feature works—namely, how does Excel find the templates that are installed on your computer?

The answer is a little tricky, because Excel actually has three types of templates. They include:

Installed templates. These templates are part of the Office package and are included automatically when you install Excel. You can find them (depending on where you installed Office) in a folder like C:\Program Files\Microsoft Office\Templates\1033. (1033 is the language ID for U.S. English.)

Note: This list includes only the templates on your computer. It doesn't include the templates from Office Online because those are stored on Microsoft's web servers.

Under the Hood: Tracking Down Templates

It's a good idea to take note of where your Templates folder is. This information helps when you want to share your templates with other people (or take their templates and copy them to your computer). Even if you aren't planning on sharing templates, it still makes sense to pay attention to their location, so you can back them up for safekeeping.

The Templates folder location varies depending on the current user name (the account under which you've logged into Windows). For example, if you've logged yourself in under the user account billjones on a Windows Vista or Windows 7 computer, you'll probably find the templates in C:\Users\billjones\AppData\Roaming\Microsoft\Templates. In Windows XP, the path is subtly different—probablyC:\Documents and Settings\billjones\Application Data\Microsoft\Templates. Incidentally, the templates folder includes user-defined templates for all installed Office programs, including Word.



If you're having trouble tracking down your templates folder, there's a handy trick that can help you locate it. In Excel, choose File?Save As, and then select the "Excel Template (*.xltx)" file type. Excel will send you straight to the Templates folder.

•    Local templates. These templates are the custom ones you create. (They're also known as "My templates.") Excel stores them in a special folder on your computer (more on that later). When you first install Excel, this category is empty, because you haven't made any custom templates yet.

•    Workgroup templates. These templates are also custom templates. The only difference is that they're stored in a shared location where more than one person can access them. This way, other people using the same computer (or connected via a network) can make use of your hard work. When you first install Excel, the workgroup template folder doesn't exist yet. If you want to use this feature, you need to set it up yourself; see page 3-14 for details.

You can't change the list of installed templates. Even if you save your own custom files to the folder where the installed templates are stored, Excel steadfastly ignores them. For that reason, you should never try to add or remove an installed template.

On the other hand, you can freely add custom templates to the local and workgroup folders. Best of all, Excel automatically checks these folders. If Excel finds two custom templates in the local template folder, and three more in the workgroup template folder, then it shows all five in the "My templates" category. You can use these templates to create new spreadsheets in exactly the same way you use a built-in template.

So, all you need to know to integrate your custom templates with Excel's is where to save your files. As it turns out, Excel can take you there automatically, as you'll see in the next section.

Word to the Wise: Creating Bulletproof Templates

By now, you probably realize that templates aren't just a way to eliminate repetitive work when you need to create similar spreadsheets. They're also a way to let ordinary people—those, like you, who aren't familiar with Excel's dark arts—to record information, fill out forms, and analyze data. These people need a little guidance, and templates are there to help them.

Unfortunately, Excel isn't always particularly forgiving. Even if you craft the perfect template, an Excel novice can accidentally delete or overwrite a formula just by pressing the wrong key. And it's almost as easy to put the wrong information in a cell (for example, by entering a date incorrectly so that it's interpreted as text). Furthermore, a template is no help at all when the person using it doesn't know where to start typing or what the different headings really mean. All these problems can happen (and regularly do happen), even if you think your template is a small miracle of straightforward design and organization.

When you want to create a truly bulletproof template, you can use two more Excel features:

•    Data validation prevents people from entering the wrong type of data in a cell (or warns them when they do). It also lets you set up a handy drop-down list of values that the person editing the worksheet can choose from.

•    Worksheet protection prevents people from changing certain cells, and (optionally) stops them from viewing the formulas inside.

Microsoft designed both these features for ordinary workbooks, but they make good sense in templates too.

B. BUILDING A CUSTOM TEMPLATE

To create a custom template, you simply need to build a new workbook, add any headings, formatting, and formulas you desire, and then save it as a template. You can start this process from scratch by opening a new, blank workbook, or you can refine an existing built-in template. Either way, you should follow the same process of perfecting your workbook until it's ready for template status. Here are some tips:

•    Clear out the junk. Your template should be a blank form politely waiting for input. Clear away all the data on your template, unless it's generic content. For example, you can leave your company name or the worksheet title, but it probably doesn't make sense to have sample numbers.

•    Assume formulas won't change. The ideal template is one anyone can use, even Excel novices who are too timid to edit a formula. If you have a formula that contains some data that might change (for example, the sales commission, interest rate, late fee, and so on), don't type it directly into your formulas. Instead, put it in a separate cell, and use a cell reference within the formula. That way, the person using the template can easily modify the calculation just by editing the cell.

•    Don't be afraid to use lists and outlining. These features are too complicated for many mere mortals (those who haven't read this book, for example), but they make spreadsheets easier to use and more powerful. By putting these advanced frills into the template, you ensure that people can use them in their spreadsheets without having to learn how to apply them on their own. Charts and pictures, which you'll learn more about in Part Four, are also good template additions.

•    Turn off worksheet gridlines. Many templates don't use Excel's gridlines. That way, it's easier to see custom borders and shading, which you can use to draw attention to the important cells. To turn off gridlines, select View?Show? Gridlines.

•    Add the finishing touches. Once you have the basics—titles, captions, formulas, and so on—it's time to create a distinct look. You can add borders, change fonts, and inject color. (Just remember not to go overboard with cell shading, or the output may be impossible to read on a black-and-white printer.) You may also want to tweak the paper size and orientation to ensure a good printout.

•    Delete extra worksheets and assign good names to the remaining worksheets. Every workbook starts with three worksheets, named Sheet1, Sheet2, and Sheet3. The typical template has only one worksheet, and it's named appropriately (such as Expense Form).

•    Consider adding custom macros to make a really slick spreadsheet. For a real treat, you can build a toolbar with custom macros (and even attach them to custom buttons) that perform related tasks.

Once you've perfected your template, you're ready to save it. Follow these steps:

1.    Select File?Save As.

The Save As dialog box appears.

2.    In the "Save as type" drop-down list box at the bottom of the window, choose

"Template (*.xltx)".

Excel automatically browses to the Templates folder, which is where the templates you create are stored on your computer. Typically, this folder is one like C:\Users\UserName\AppData\Roaming\Microsoft\Templates (where UserName i s the name of the Windows account you used to log in).

3.    Type the template name, and then click Save.

The saved template file automatically appears in the "My templates" category. You can use it to build new spreadsheets. Just select File?New, and then choose "My templates". A dialog box appears with all the custom templates you've created or downloaded from Office Online, as shown in Figure 3-6.

Figure 3-6. This dialog box displays all your home-made templates. If you want to get rid of a template, right-click it in this window, and then choose Delete.

Note: Of course, your templates don't need to appear in the "My templates" list. You can save a template file anywhere on your computer, and open it later by browsing to the right folder, and then choosing the template file. However, this approach has a couple of drawbacks. When you open a template file directly, Excel doesn't automatically create a new workbook. It's easy to overwrite the template by accident. Also, if your templates aren't in the "My templates" list, you may have trouble remembering where you put them.

C. SHARING TEMPLATES WITH OTHERS

As you've already seen, once you place a template in the Templates folder, it appears in Excel's backstage view, where you can use it to create new workbooks. However, you may be disappointed to find out that you're the only one who can benefit from all your hard work. Coworkers using other computers won't be able to access your templates. In fact, your template won't even be available to other people who use the same computer if they log in with a different user name and password. That's because the Templates folder is a user-specific setting. You may use the folder C:\Users\billjones\AppData\Roaming\Microsoft\Templates, but if Sarah Cheng logs onto the same computer, then Excel bothers to check only C:\Users\sarahcheng\AppData\Roaming\Microsoft\Templates.

You can use several strategies to get around this problem.

•    You can give a copy of your template to everyone who wants to use it. Choose any way you like to transfer the template (by email, on a floppy disk or CD, over a network drive, and so on), but make sure you tell folks to put it in their own local Templates folder, as explained earlier.

•    You can create a workgroup templates folder. In this case, multiple people will use the same workgroup template folder, and they'll all be able to use the template files you've put there.

You can use the second option to share files between more than one user account on the same computer or, for even better results, on a network drive that a whole team of people can use. Unfortunately, Excel nearly throws a wrench into the whole process because it doesn't let you specify which folder to use for workgroup templates. However, Microsoft Word gives you a back door to the answer. Because Excel and Word both use the same template system, if you configure the workgroup templates folder in Word, it also takes effect in Excel. (Don't bother asking why Word provides this service and Excel doesn't—it's just one of those enduring Microsoft mysteries.)

Tip: If you're unlucky enough to not have Word installed, you still have one more painful option. You can write a custom Excel macro that sets the location of the workgroup templates folder. The line of code you need is Application.NetworkTemplatesPath = "[path goes here]", but you'll have to learn a fair bit about the intricacies of VBA (Visual Basic for Applications) programming before you can actually use it. 

Once you accept the fact that you need to rely on Word, the process actually becomes quite easy. Here's what you need to do:

1.    Decide which folder you want to use as a shared folder.

You may want to create a new folder now. If you do, the easiest approach is to use a tool like Windows Explorer.

2.    Start Word, if it's not already running. In Word, choose File?Options.

The Word Options dialog box appears. The Word Options dialog box looks fairly similar to the Excel Options dialog box.

3.    From the list on the left, choose the Advanced section.

4.    Scroll down to the General heading, and then click File Locations.

This action opens the File Locations dialog box (Figure 3-7), which is the place where you tell Office applications where they should look for certain types of files.

You can actually make three interesting changes here:

•    If you modify the Documents folder, Excel and Word start off in that folder the first time you choose to save or load a file after launching the application.

•    If you change the "User templates" folder, you can specify where Excel and Office look for user-created templates stored on your computer.

•    Finally, if you set the "Workgroup templates" folder, you can tell Word and Excel where to look for shared templates. This option is the one that interests you.

Figure 3-7. If you haven't configured the workgroup templates folder before, you'll notice that it starts off blank. That's because Office has no idea where to put shared templates until you give it the right instructions.

Note: Remember, when Excel looks for templates, it searches both the user templates and the workgroup templates folders. Then it shows all the templates it found in the "My templates" list.

5.    Select the "Workgroup templates" entry in the list, and then click Modify.

A Modify Location dialog box appears. This dialog box looks more or less the same as the standard dialog boxes you use to open and save files.

6.    Browse to the folder you want to use, and then click OK.

Remember, you aren't limited to your local computer. Feel free to jump to a network drive, or even browse your network places to find a specific server.

7.    Click OK again to close the Word Options dialog box.

Now, the change has been made. Remember, in order for template sharing to work, everyone needs to perform this same set of steps to configure their copies of Excel to look in the same shared folder.

Template sharing is a simple idea that can become incredibly useful in a company environment. Instead of sending template files whizzing back and forth in emails, trying to keep a group of overworked employees in sync each time the template changes, you simply need to modify the templates in the shared location. That way, everybody always has the latest versions available, and there are no distribution headaches.

CHAPTER 3 – REVIEW QUESTIONS

The following questions are designed to ensure that you have a complete understanding of the information presented in the chapter. They do not need to be submitted in order to receive CPE credit. They are included as an additional tool to enhance your learning experience.

We recommend that you answer each review question and then compare your response to the suggested solution before answering the final exam questions related to this chapter.

1.    Which of the following is a benefit to using Excel templates:

a)    it is almost impossible to overwrite a template file

b)    templates often contain many advanced features

c)    it is easy to distinguish template files from real spreadsheets because they have a different file extension

d)    all of the above

2.    The Templates web page provides templates for all Office applications, and you are able to easily interchange them; for example, use a Word template in Excel.

a)    true

b)    false

3.    Which of the following is not one of the Excel template types:

a)    installed templates

b)    local templates

c)    advanced templates

d)    workgroup templates

4.    Which of the following is a valid tip for creating a custom template:

a)    avoid lists and outlining

b)    delete extra worksheets

c)    don’t assume formulas won’t change

d)    turn on worksheet gridlines

CHAPTER 3 – SOLUTIONS AND SUGGESTED RESPONSES

1. A: Incorrect.  There are several benefits to using Excel templates over standard spreadsheets, and the fact that they are difficult to overwrite is just one of them.

B: Incorrect.  There are several benefits to using Excel templates over standard spreadsheets, and the fact that they come brimming with advanced features is just one of them.

C: Incorrect.  There are several benefits to using Excel templates over standard spreadsheets, and the fact that they are easy to distinguish because of their unique file extension is just one of them.

D: Correct.  Excel templates are spreadsheets that have been predesigned with several beneficial features.  The spreadsheet experts at Microsoft have filled them with advanced features, they are nearly impossible to accidentally overwrite, and they carry a unique, easily recognizable file extension, so “all of the above” is the only correct answer.

(See page 3-2 of the course material.)

2.    A: True is incorrect.  The Templates web page does provide templates for all Office applications, including Word, Excel, Access, and InfoPath, but you cannot interchange them.  Excel templates can only be used in Excel, and Word templates can only be used in Word.

B: False is correct.  While the Templates web page does provide templates for all Office applications, they are program specific and cannot be interchanged among the different programs.  There is an icon to the left of the template name which indicates which program it is to be used with.

(See page 3-9 of the course material.)

3.    A: Incorrect.  Excel does have installed templates that are part of the Office package and are included automatically when you install Excel.

B: Incorrect.  Local templates are custom templates you create yourself.  This category will be empty until you create your own templates, but it is an Excel template type.

C: Correct.  There is no such thing as advanced templates.  Custom templates can be as creative as your imagination will allow, so no need for “advanced” templates.

D: Incorrect.  Workgroup templates are custom templates that are stored in a shared location so a variety of people can access them.

(See page 3-11 of the course material.)

4.    A: Incorrect.  When creating a template, you should not be afraid to use lists and outlining.  These features can be complicated, but they make spreadsheets easier to use and more powerful.

B: Correct.  Every workbook starts with three worksheets, named Sheet1, Sheet2, and Sheet3.  A typical template only requires one worksheet.  To make an effective template, you will want to delete unwanted worksheets and give applicable names to the remaining worksheets.

C: Incorrect.  To create the ideal template, you want it to be one that anyone can use; even Excel novices who may not know how to edit a formula.  If you have data that may change a formula, put it in a cell reference instead of directly in a formula.

D: Incorrect.  Many templates don’t use gridlines and these can be a distraction, so you are better off turning off worksheet gridlines in a template.

(See page 3-13 of the course material.)


Chapter 4: Creating Basic Charts

As you become more skilled with Excel, you'll realize that entering numbers, organizing your layout, and formatting cells aren't the most important parts of spreadsheet creation. Instead, the real work lies in analyzing your data—in other words, figuring out a way to tell the story that lies behind your numbers. Excel's charting tools may be just what you need.

Charts depict data visually, so you can quickly spot overall trends. They're a fabulous way to help you find the meaning hidden in large amounts of data. You can create many different types of charts in Excel, including pie charts that present polling results, line charts that plot rising or declining assets over time, and three-dimensional area charts that show relationships between environmental conditions in a scientific experiment.

Excel's charting tools are enormously flexible: You can generate a simple chart with standard options in a couple of mouse clicks, or you can painstakingly customize every aspect of your chart's appearance (including colors, scale, titles, and even 3-D perspective). This chapter takes the first approach and explains how to generate straightforward charts, which you'll examine in detail. You'll also learn which chart types are out there. In the next chapter, you'll learn how to fine-tune your charts for maximum effect.

Note: All charts are not created equal. Depending on the chart type you use, the scale you choose, and the data you include, your chart may suggest different conclusions. The true chart artist knows how to craft a chart to draw out the most important information. As you become more skilled with charts, you'll acquire these instincts, too.

I. Charting 101

Excel provides a dizzying number of chart types, but they all share a few things. In this section, you'll learn about basic Excel charting concepts that apply to almost all types of charts; you'll also create a few basic charts. At the end of this chapter, you'll take a chart-by-chart tour of each and every one of Excel's many chart types.

To create a chart, Excel needs to translate your numbers into a graphical representation. The process of drawing numbers on a graph is called plotting. Before you plot your information on a chart, you should make sure your data's laid out properly. Here are some tips:

•    Structure your data in a simple grid of rows and columns.

•    Don't include blank cells between rows or columns.

•    Include titles, if you'd like them to appear in your chart. You can use category titles for each column of data (placed in the first row, atop each column) and an overall chart title (placed just above the category-title row).

Tip: You can also label each row by placing titles in the far-left column, if it makes sense. If you're comparing the sales numbers for different products, list the name of each product in the first column on the left, with the sales figures in the following columns.

If you follow these guidelines, you can expect to create the sort of chart shown in Figure 4-1.

Figure 4-1. This worksheet shows a table of data and a simple column chart based on Excel's standard chart settings. Nothing fancy, but it gets the job done.

To create the chart shown in Figure 4-1, Excel performs a few straightforward steps (you'll learn the specifics of how to actually create this chart in the next section). First, it extracts the text for the chart title from cell A1. Next, it examines the range of data (from $14,000 to $64,000) and uses it to set the value—or Y-axis—scale. You'll notice that the scale starts at $0, and stretches up to $80,000 in order to give your data a little room to breathe. (You could configure these numbers manually, but Excel automatically makes commonsense guesses like these by looking at the data you're asking it to chart.) After setting the vertical scale, Excel adds the labels along the bottom axis (also known as the X-axis or category axis), and draws the columns of appropriate height.

A. EMBEDDED AND STANDALONE CHARTS

The chart in Figure 4-1 is an embedded chart. Embedded charts appear in a worksheet, in a floating box alongside your data. You can move the chart by dragging the box around your worksheet, although depending on where you put it, you may obscure some of your data.

Your other option is to create a standalone chart, which looks the same but occupies an entire worksheet. That means that your chart data and your chart are placed on separate worksheets. 

Usually, you'll use an embedded chart if you want to create printouts that combine both your worksheet data and one or more charts. On the other hand, if you want to print the charts separately, it's more convenient to use standalone charts. That way, you can print an entire workbook at once and have the charts and the data on separate pages.

Note: If you use embedded charts, you still have the option of printing just the chart, sized so that it fills a full sheet of paper. Simply select the chart, and then choose File?Print. If you create a standalone chart, you don't have a choice—Excel always prints your chart on a separate page.

B. CREATING A CHART WITH THE RIBBON

So how do you create a chart like the one shown in Figure 4-1? Easy—all it takes is a couple of clicks in the ribbon. Here's how it works:

1.    Select the range of cells that includes the data you want to chart, including the column and row headings and any chart title.

If you were using the data shown in Figure 4-1, you'd select cells A1 to B7.

For speedier chart building, just position your cursor somewhere inside the data you want to chart. Excel then automatically selects the range of cells that it thinks you want. Of course, it never hurts to remove the possibility for error by explicitly selecting what you want to use before you get started.

Tip: And for even easier charting, start by creating an Excel table (Chapter 1) to hold the data you want to chart. Then, if you position yourself somewhere inside the table and create a new chart, Excel automatically selects all the data. It also automatically updates the chart if you add new rows or remove existing data.

2.    Head to the ribbon's Insert?Charts section. You'll see a separate button for each type of chart (including column charts, line charts, pie charts, and so on). Click the type you want.

When you choose a chart type, you get a drop-down list of subtypes (Figure 4-2).

The different chart types are explained in more detail later in this chapter. For now, it's best to stick to some of the more easily understood choices, like Bar, Column, or Pie. Remember, the chart choices are just the starting point, as you'll still be able to configure a wide range of details that control things like the titles, colors, and overall organization of your chart.

3.    Click the subtype you want.

Excel inserts a new embedded chart alongside your data, using the standard options (which you can fine-tune later).

Note: If you don't want to make any choices, you can actually build a chart with one key press. Just highlight your data and press F11. This step creates a column chart on a new worksheet. Although you can't undo this operation, you can always delete the new chart worksheet and start over.

Figure 4-2. Under each chart choice are yet more subtypes, which add to the fun. If you select the Column type (shown here), you'll get subtypes for two- and three-dimensional column charts, and variants that use cone and pyramid shapes. If you hover over one of these subtypes, a box appears with a brief description of the chart.

Power Users’ Clinic: Browsing Excel’s Chart Gallery

Excel pros sometimes find that the ribbon approach is a bit awkward when you're trying to find a less commonly used chart type. In this situation, you may prefer to look at the full list of chart types and subtypes. To do so, head to the ribbon's Insert?Charts section, and then click the dialog launcher (the square-with-an-arrow icon in the bottomright corner). You see the Insert Chart dialog box (Figure 4-3).

The Insert Chart dialog box doesn't just let you create charts. You can also designate the default chart type (the one that's used if you select some cells, and then press F11 to create a chart in a single bound). To designate a default chart, select it, and then click "Set as Default Chart". Lastly, the Insert Chart dialog box lets you use a custom chart template that you've previously prepared, as described on page 5-26.

Figure 4-3. The gallery on the Insert Chart dialog box's right side has a thumbnail of every chart subtype, grouped by type. You can scroll through them all, or you can choose a type from the list on the left to jump straight to a specific section. When you find what you want, click OK to create it.

C. THE CHART TOOLS RIBBON TABS

When you select a chart, Excel adds three new tabs to the ribbon under the Chart Tools heading. These tabs let you control the details of your charts, and they are:

•    Design. This tab lets you change the chart type and the linked data that the chart uses. It also lets you choose a chart style and layout, two ways to improve the appearance of a chart that you'll consider in Chapter 5.

•    Layout. This tab lets you configure individual parts of the chart. You can add shapes, pictures, and text labels, and you can configure the chart's gridlines, axes, and background.

•    Format. This tab lets you format individual chart elements, so you can transform ordinary items into eye candy. You can adjust the font, fill, and borders uses for chart titles and shapes, among other things.

In this chapter, you'll spend most of your time using the Chart Tools | Design tab. In the next chapter, you'll begin fine-tuning your charts, and you'll branch out to the other two tabs.

II. Basic Tasks with Charts

Unlike the orderly rows of numbers and labels that fill most worksheets, charts float above your data, locked inside special box-like containers. To take advantage of these chart boxes, you need to understand a little more about how they work.

A. MOVING AND RESIZING A CHART

When you insert a chart into an existing worksheet, it becomes a floating object, hovering above your worksheet. Depending on where Excel puts it, it may temporarily obscure your data. The chart box doesn't damage your data in any way, but it can end up hiding your worksheet's numbers and text (both onscreen and in your printouts).

You have to learn to grab hold of these floating boxes and place them where you really want them. The process is pretty straightforward:

1.    Click the chart once to select it.

You'll notice that when you select a chart, Excel highlights the worksheet data the chart uses, as shown in Figure 4-4. At the same time, three new tabs appear in the ribbon, under the Chart Tools heading.

Figure 4-4. You'll know you've selected the chart when three new charting tabs appear in the ribbon under the Chart Tools heading.

2.    Hover over the chart border until the mouse pointer changes to a four-way arrow.

Figure 4-5 shows what you're looking for.

Figure 4-5. The four-way arrow is a signal that you can click here to move the chart. If you move to one of the corners, you'll see an angled two-way arrow, which lets you resize the chart.

3.    Click and drag with your mouse to move or resize the chart.

Using the four-way arrow, you can drag the chart anywhere on your worksheet, releasing the mouse button when it's in the right spot.

Using the two-way arrow, you can drag the border to make the chart larger or smaller. Once you make a chart box larger, you may also want to resize the individual components inside the chart to better use the available space. Page 513 tells you how to select chart elements and resize them.

Tip: To remove a chart in one fell swoop, just select it with the mouse, and then press Delete.

4.    When you're finished, click a cell anywhere in the worksheet to go back to your data.

At this point, life returns to normal, and the Chart Tools tabs disappear.

Tip: You can resize a chart in another, slightly more circuitous way. You can set the Height and Width boxes in the Chart Tools | Format?Size section of the ribbon. Although this isn't as quick as dragging the chart edge, it lets you set the size exactly, which is indispensable if you have several charts on the same worksheet and you need to make sure they're all the same size.

Under the Hood: How Excel Anchors Charts

Although charts appear to float above the worksheet, they're actually anchored to the cells underneath. Each corner of the chart is anchored to one cell (these anchor points change, of course, if you move the chart around). This fact becomes important if you decide to insert or delete rows or columns anywhere in your worksheet.

For example, consider the chart shown in Figure 4-1. Its top edge is bound to row 2, and its bottom edge is bound to row 12. Similarly, its left edge is bound to column C, and its right edge to column I. That means if you insert a new row above row 2, the whole chart shifts down one row. If you insert a column to the left of column C, the whole chart shifts one column to the right.

Even more interesting is what happens if you insert rows or columns in the area that the chart overlaps. For example, if you insert a new row between the current row 10 and row 11, the chart stretches, becoming one row taller. Similarly, if you delete column D, the chart compresses, becoming one column thinner.

If it bugs you, you can change this sizing behavior. First, select the chart, and then head to the ribbon's Chart Tools | Format?Size section. Click the dialog launcher (the squarewith-an-arrow icon in the bottom-right corner). When the Size and Properties dialog box appears, choose Properties in the list on the left. You'll see three "Object positioning" options. The standard behavior is "Move and size with cells", but you can also create a chart that moves around the worksheet but never resizes itself ("Move but don't size with cells") and a chart that's completely fixed in size and position ("Don't move or size with cells").

B. CREATING A STANDALONE CHART

Even without your input, Excel usually makes commonsense choices, so you can often build a chart without needing to tweak any of these options.

You have two options for placing charts in a workbook. You can create an embedded chart, which appears in an existing worksheet (usually next to the appropriate data), or you can create a standalone chart, which appears in a new worksheet of its own (Figure 4-6).

Figure 4-6. A standalone chart lives in a separate worksheet that doesn't have any other data and doesn't include the familiar grid of cells.

Ordinarily, when you pick a chart type from the ribbon, Excel creates an embedded chart. However, you can easily switch your chart over to a new worksheet if you're running out of room—just follow these steps:

1.    Right-click the chart, and then choose Move Chart (or, select the chart, and then choose Chart Tools | Design?Location?Move Chart).

The Move Chart dialog box appears (Figure 4-7).

Figure 4-7. Using the Move Chart dialog box, you can transfer the chart to a standalone worksheet (as shown here) or shuffle it over to another worksheet and keep it as an embedded worksheet. (If you want the latter option, it's just as easy to select the chart, and then use a cut-and-paste operation to move it to a new worksheet.)

2.    Choose "New sheet", and then enter the name for the new worksheet.

3.    Click OK.

Excel creates the new worksheet and places the chart on it. The new worksheet goes in front of the worksheet that contains the chart data. (You can always move the worksheet to a new position in your workbook by dragging the worksheet tab.)

Note: You can move or resize only embedded charts—the ones that appear in floating boxes inside other worksheets. If you've created a standalone chart, you can't move or resize it. Instead, it automatically shrinks or enlarges itself to match the Excel window's display area.

C. EDITING AND ADDING TO CHART DATA

Every chart remains linked to the source data you used to create it. When you alter the data in your worksheet, Excel refreshes the chart with the new information automatically. As long as Excel is set to automatic recalculations (and it almost always is), there's no such thing as an out-of-date chart. 

Note: Excel has no restriction on linking multiple charts to the same data. So you can create two types of charts (like a pie and a column chart) that show the same data. You can even create one chart that plots all the data and another chart that just uses a portion of the same information.

However, there's one tricky point. Any range you define for use in a chart is static, which means it doesn't grow as your data grows. That means that if you add a new row at the bottom of the range, it doesn't appear on the chart because it's outside of the chart range.

If you do want to insert additional data to a range of data used in a chart, you have several options:

•    You can use the Home?Cells?Insert?Insert Sheet Rows command. If you do, Excel notices the change, and automatically expands the range to include the newly inserted row. However, this command works only if you add a row into the middle of your data. If you try to tack a row onto the end, Excel still ignores it, and you'll need to use the solution described in the next bullet point.

•    After you insert new rows, you can modify the chart range to include the new data. This approach is the most common, and it's quite painless. First, select your chart by clicking the edge of the chart box, or a blank space in the chart. Excel highlights the linked worksheet data with a colored border. Click this colored border, and drag it until it includes all the new data you want. When you release the mouse button, Excel refreshes the chart with the new information.

Excel is smart enough to adjust your chart range in some situations. If you drag your chart data to a new place on your worksheet, Excel updates the chart to match automatically.

D. CHANGING THE CHART TYPE

When you create a chart, you choose a specific chart type (page 4-3). However, in many situations you may want to try several chart types with the same data to see which visualization tells your story better. Excel makes this sort of experimentation easy. All you need to do is click your chart to select it, and then make a different choice from the ribbon's Insert?Charts section. You can use this technique to transform a column chart into a pie chart.

Power Users’ Clinic: Charting a Table

You can use the Excel table feature (discussed in Chapter 1) with charts. Tables and charts make a perfect match. Tables grow and shrink dynamically in size as you add or delete records. If a chart's bound to a table, the chart updates itself as you add new information or remove old data.

You've already learned how to build a new chart using an existing table. (Just move inside the table, and then make a selection from the ribbon's Insert?Charts section.) But even if you've already created the chart with an ordinary range of cells, you can still use a table—all you need to do is convert the linked range to a table.

In the sales report example shown in Figure 4-1, here's what you'd need to do:

1.    Select the range of cells that contain all the data, not including the chart's title (cells A2 to B7).

2.    Select Insert?Tables?Table.

Now, as you add new items to the table, Excel adds them to the chart immediately.

When you chart a table, you also gain the ability to use other features, like easier sorting and filtering. You can use sorting to determine the order that items appear within a chart (which is occasionally useful), and you can use filtering to hide rows and to chart only a portion of the data (which is often indispensable). If you apply a filter condition that shows only the three best performing regions, the chart updates itself so that it shows only this data. You'll find this technique particularly handy when you're creating charts that use multiple series, as described later in this chapter.

For more information about filtering and the ever-impressive table feature, refer to Chapter 1.

You can also choose Chart Tools | Design?Type?Change Chart Type to make a choice from the Change Chart Type dialog box, which looks just like the Insert Chart dialog box shown in Figure 4-3.

E. PRINTING CHARTS

How you print a chart depends on the type of chart you've created. You can print embedded charts either with worksheet data or on their own. Standalone charts, which occupy separate worksheets, always print on separate pages.

Embedded charts

You can print embedded charts in two ways. The first approach is to print your worksheet exactly as it appears in the Excel window, with its mix of data and floating charts. In this case, you need to take special care to make sure your charts aren't split over a page break or positioned over some data you want to read in the printout. You can check for both issues using Page Layout view (choose View?Workbook Views?Page Layout View) or the smaller print preview that's shown in backstage view when you're about to print your worksheet (make sure the chart isn't selected, and then choose File?Print).

You can also print an embedded chart on a separate page, which is surprisingly easy. Just click the chart to select it, and then choose File?Print. When you do so, Excel's standard choice is to print your chart using landscape orientation, so that the long edge of the page is along the bottom, and the chart's wider than it is tall. Landscape is usually the best way to align a chart, especially if it holds a large amount of data, so Excel automatically uses landscape orientation no matter what page orientation you've configured for your worksheet. Of course, you can change this as you would with any other printout; just choose Portrait Orientation in the list of print settings before you click the big Print button.

Excel also includes two print options that are specific to charts. To see these options, click the Page Setup link at the bottom of the list of print settings. When the Page Setup dialog box appears, choose the Chart tab. You'll see an option to print a chart using lower print quality ("Draft quality"), and in black and white instead of color ("Print in black and white").

Standalone charts

If you're using a standalone chart, your chart always prints out on a separate page, sized to fit the whole page. To print out just the chart page alone (rather than the whole workbook), switch to the chart's worksheet, and then choose File?Print. Excel automatically sets all chart worksheets to Landscape orientation, which orients the page so that the long edge runs horizontally across the bottom. If this layout isn't what you want, change the orientation setting to Portrait Orientation before you print.

If you want to print the entire workbook, choose File?Print from any worksheet. Then, change the first print setting from Print Active Sheets to Print Entire Workbook.

III. Practical Charting

Figure 4-1 showed you how to chart a list that contains two columns you want to graph— one with text labels and one with numeric data. But, in real life, you'll probably need to deal with many types of data that occupy many different configurations on your worksheet.

Consider all the possible variations on the simple sales chart shown in Figure 4-1. You may need to compare the sales figures but, rather than showing region-to-region comparisons, you want to show how well (or poorly) each of your firm's products sold. Or perhaps you want to chart the quarterly performance of different stores over a five-year period, or determine the relationship between sales and profitability. All these charts require a slightly different arrangement of data. In the next section, you'll get a quick introduction to all these possibilities, using just the simple column chart and line chart.

A. CHARTS WITH MULTIPLE SERIES OF NUMBERS

A series is the sequence of numbers that you plot on a graph. In the simple chart example (Figure 4-1), there's one series of numbers, which represents the sales figures for a company's different regions. Of course, a real chart usually adds extra layers of detail. You may want to compare the sales figures from several different years. In this case, you'd add a separate column to your worksheet data for each year. Then you'd add each column to your chart as a separate series.

It doesn't take any extra expertise to create a chart that uses multiple series—you just select the right range of cells, and then pick a chart option from the ribbon, just as you would for a chart that has a single series. Different types of charts handle multiple series in different ways. The clustered column chart creates a separate bar for each value in a row, as shown in Figure 4-8. A line chart, on the other hand, shows a separate line for each series (as shown in the next section). For more possibilities, take a look at the Chart Types section that starts on page 4-23.

Figure 4-8. This chart has three series of sales figures (one for each year) and five sets of columns (one for each region). Each of the five sets has three bars, one for each data series. The regions are labeled on the category axis, but you'll need to consult the legend to determine which year each column represents.

Tip: You can add multiple series to an existing chart without starting over from scratch. First, select the chart to highlight the linked data. Then, click the rightmost edge, and drag it to the right to expand the range so that it includes the new columns (which, of course, you've already added to your worksheet).

B. CONTROLLING THE DATA EXCEL PLOTS ON THE X-AXIS

Excel's charting tool has a dirty little secret. You may not realize it right away, but sooner or later, whether it's your first chart or your 40th, you'll stumble onto the fact that Excel makes a fairly important decision for you about what data shows up in your chart's Xaxis. Unfortunately, this decision may not be what you want. Fortunately, you can change it.

Up to Speed: Data in Different Scales

Remember when your mother told you not to compare apples and oranges? The same rule applies with charts. When you add multiple series, each series should use the same scale. In other words, the points for each series should be plotted (placed on the chart) using the same measurement system.

The worksheet in Figure 4-8 works perfectly well because the different series of sales figures all use the same unit—dollars. But if one series recorded sales totals in dollars and another recorded them in Euros (or even worse, recorded totally different data like the number of units sold), the chart would be inconsistent.

Excel doesn't complain if your series use different scales—in fact, it has no way of noticing that anything's amiss. And if you don't notice either, you'll create a misleading chart.

Your chart may imply a comparison that isn't accurate or, if the scale is radically different, the chart can get so stretched that it starts to lose detail. If you have sales figures from $50,000 to $100,000 and units sold from 1 to 100, the scale stretches from 1 to 100,000, and the differences in sales totals or units sold are too small to show up at all. 

What's the solution? Don't mix different scales. Ideally, convert values to the same scale (in this case, use the currency exchange rate to turn Euros into U.S. dollars before you create the chart). Or just create two charts, one for each data series. But if you really want to compare the changes in different types of data across the same categories, there's a way. Page 5-44 shows you how to build combination charts that fuse together two incompatible sets of data in a logical way.

But what causes the situation in the first place? Excel creates your charts according to the way the data's organized in your worksheet. A simple example shows you the effect.

The worksheet in Figure 4-9 looks at sales based on two factors: the year when the sales were recorded, and the region where the sales were made. In technical charting terms, the regions form the category axis, while the sales figures form the value axis. In other words, Excel creates a separate series for each year. But it makes just as much sense to organize the table in a different way, by making the year the category axis and creating a separate series for each region! Figure 4-9 contrasts these two different ways of looking at the same data, and shows how they affect the way Excel groups your data in a column chart.

The column chart example is fairly innocent. Although you may prefer one way of looking at the data over the other, they're relatively similar. However, most Excel charts aren't as forgiving. The line chart's a classic example.

In a line chart, each line represents a different series. If you list the sales years on the category axis (as shown on the left side of Figure 4-10), you end up with a separate line for each region that shows how the region has performed over time. But if you invert the table (shown on the right side), you end up with a chart that doesn't make much sense at all: a series of lines that connect different regions in each year. Figure 4-10 shows the problem.

Figure 4-9. This worksheet shows the same data charted in two different ways. In the first table (left), the category axis lists the sales years, which are used to group the regions. In the second table (right), the category axis lists the regions, which are used to group the years.

Figure 4-10. The chart on the left is pretty straightforward. The chart on the right shows a line for each year, which makes sense if you concentrate on what's being depicted, but mostly shows how people can use computers to complicate things.

Clearly, when you create a line chart, you need to make sure the chart ends up using the data in a way that makes the most sense. So, how does Excel decide how to plot the data? Essentially, Excel makes a best guess about your data. If you have more rows than columns, Excel assumes that the first column represents the category axis. If you have more columns than rows (or if you have the same number of rows and columns), Excel assumes that the first row represents the category axis, as in Figure 4-10.

Fortunately, you have the power to override Excel's choice if you need to. Just select your chart, and then choose Chart Tools | Design?Data?Switch Row/Column. If you try this action on the charts in Figure 4-10, you reverse the results. Thus, the chart on the left would group the data into yearly series, and the chart on the right would group the data into regional series. To return them to normal, you can select each chart, and then click Switch Row/Column again.

Up to Speed: The Difference Between a Column and a Line

With simple column charts, life is easy. It doesn’t matter too much what data you choose to use for your category axis because your choice simply changes the way data’s grouped. Other chart types that follow the same principle include pie charts (which only allow one series), bar charts (like column charts, but oriented horizontally instead of vertically), and donut charts (where each series is a separate ring).

The same isn't true for line charts and most other types of Excel charts. The category axis you use for a line chart is important because the values in each series are connected (in this case, with a line). This line suggests some sort of "movement" or transition as values move from one category to another. That means it makes sense to use a line to connect different dates in a region (showing how sales have changed over time), but it probably doesn't make sense to use a line to connect different regions for each date. Technically, this latter scenario (shown on the right side of Figure 4-10) should show how yearly sales vary as you move from region to region, but it's just too counterintuitive for anyone to interpret it properly.

As a general rule of thumb, use time or date values for the category axis. You should do this especially for chart types like line and area, which usually show how things change over time.

C. DATA THAT USES A DATE OR TIME SCALE

As the previous example shows, using time or date values for the category axis makes a lot of sense for charting progress or spotting long-term trends. However, the example does cheat a little. Even though any sentient human knows that the labels Sales-03, Sales-04, and Sales-05 represent consecutive years, Excel is oblivious to what these labels actually mean. You could chart a bunch of years that are far from sequential (like Sales02, Sales04, and Sales08) and Excel would obediently (and misleadingly) place each value on the category axis, spaced out evenly.

This snafu doesn't present a problem in the previous example, but it's an issue if you need to chart years that aren't spread out evenly. Fortunately, Excel offers an easy solution. Instead of entering text labels, you can enter actual dates or times. Because Excel stores dates and times as numbers, it can scale the chart accordingly (this process is sometimes called category axis scaling). Best of all, Excel automatically notices when you're using real dates, and kicks into action, making the appropriate adjustments, as shown in Figure 4-11.

Figure 4-11. The top chart uses category axis scaling to properly space out dates, even when values are missing. The bottom chart doesn't.

What's happening in Figure 4-11 is worth examining in a bit of detail. The pictured worksheet shows two charts that show the exact same data: a series of monthly sales figures from two regions (covering the time period between January 2007 and December 2008). The diamonds and triangles on the line charts indicate the data points for which sales data is available. The twist is that a big chunk of data (the months between August 2007 and June 2008) is missing. To make sure Excel handles this omission correctly, you have to enter real date values (rather than text labels) for the category axis. If you take that step, the chart Excel creates automatically uses a continuous timescale, as shown in the top chart. (As you can see by looking at the data points, no values fall in the middle of the series.)

On the other hand, if you enter the labels as text (as was done when creating the bottom chart), you'll see an incorrect result: The data from August 2007 and June 2008 are placed close together—even though they record months that are almost a year apart.

Optionally, you have the ability to tell Excel to disregard any values you've used in your column or row labels, thereby spacing the dates out evenly, as though they're ordinary text labels. That's how the incorrect chart in Figure 4-11 was created. (Why you'd want to do it is another question, but someone, somewhere, is probably in desperate need of this feature.) To change how Excel scales the category axis, select the chart, and then choose Chart Tools | Layout?Axes?Axes?Primary Horizontal Axis?More Primary Horizontal Axis Options to show the Format Axis dialog box. Next, change the Axis Type setting, as shown in Figure 4-12.

Figure 4-12. Under the Axis Type heading, pick one of the following: "Text axis" (treat the category values as labels), "Date axis" (treat the category values as date vales), or "Automatically select based on the data" (let Excel decide based on what it thinks is best). Chapter 5 has more information about the other options in this dialog box.

Category axis scaling works with more than just dates. You can scale any category axis values, as long as they're numeric, which is particularly useful if you're trying to determine the relationship between two different values. If you wanted to determine the relationship between students' IQs and their test scores, you could use the numeric IQ for the category axis, and use the test scores for the value axis. If you want to create a chart like this that compares two sets of numbers, you must use a scatter chart instead of a line chart. Scatter charts look similar to line charts. Page 4-30 covers scatter charts in more detail.

D. NONCONTIGUOUS CHART RANGES

So far, all the chart examples have assumed the data you want to chart is placed in a single, tightly packed table. But what if your information is actually scattered across your worksheet? This scenario may seem unlikely, but it actually happens quite often when you need to chart only part of the data in a table. Say you want to create a chart using two or three columns, and these columns aren't next to each other. In this case, you need to take a few extra steps when you create your chart.

Imagine you have a table that records the monthly sales of 10 different regional offices. However, you want to create a chart that compares only two of these offices. Your chart will use the category information in column A (which contains the month in which the sales were recorded), along with the values in column C and column D (which contain the total amount of sales for the two regions in which you're interested).

The easiest way to create this chart is to start by selecting the noncontiguous range that contains your data. Here's what you need to do:

1.    First, use the mouse to select the data in column A.

Excel surrounds the data with a marquee. Don't click anywhere else yet.

2.    Then, hold down the Ctrl key while you drag to select the data in columns C and D.

Because you're holding down the Ctrl key, column A remains selected (see Figure 413).

Figure 4-13. This worksheet shows a noncontiguous selection that ignores the numbers from region 1. When you create the chart, Excel includes only two series in the chart: one for region 2, and one for region 3.

3.    Now choose Insert?Charts, and then pick the appropriate chart type.

Excel creates the chart as usual, but uses only the data you selected in steps 1 and 2, leaving out all other columns.

This approach works most of the time. However, if you have trouble, or if the columns you want to select are spaced really far apart, then you can explicitly configure the range of cells for any chart. To do so, follow these steps:

1.    Create a chart normally, by selecting part of the data, and then, from the Insert?Chart section of the ribbon, choosing a chart type.

2.    Select the chart, and then choose Chart Tools | Design?Data?Select Data.

The Select Data Source dialog box appears (Figure 4-14). 

Figure 4-14. This dialog box shows a handy secret about Excel charting. Excel not only records the whole range of cells that contain the chart data (as shown in the "Chart data range" text box), it also lets you see how it breaks that data up into a category axis and one or more series (as shown in the Legend Entries (Series) list).

3.    Remove any data series you don't want and add any new data series you do want.

To remove a series, select it in the Legend Entries (Series) list, and then click Remove.

To add a new series, click Add, and then specify the appropriate cell references for the series name and the series values.

You can also click Switch Row/Column to change the data Excel uses as the category axis (page 4-14) and you can adjust some more advanced settings, like the way Excel deals with blank values, and the order in which it plots series (as explained in the following sections).

E.  CHANGING THE ORDER OF YOUR DATA SERIES

If your table has more than one series, Excel charts it in the order it appears on your worksheet (from left to right if your series are arranged in columns, or from top to bottom if they're arranged in rows). In a basic line chart, it doesn't matter which series Excel charts first—the end result is still the same. But in some charts, it does make a difference. One example is a stacked chart (Figure 4-16), in which Excel plots each new series on top of the previous one. Another example is a 3-D chart, where Excel plots each data series behind the previous one.

You can easily change your data series' order. Select your chart, and then choose Chart Tools | Design?Data?Select Data. Now select one of the series in the Legend Entries (Series) list, and then click the up or down arrow buttons to move it. Excel plots the series from top to bottom.

F.  CHANGING THE WAY EXCEL PLOTS BLANK VALUES

When Excel creates a chart, its standard operating procedure is to ignore all empty cells. The value of 0 doesn't count as an empty cell and neither does text (Excel plots any cells that contains text as a 0).

So what's the difference between an ignored cell and a cell that contains the number 0? In some types of charts, there's no difference. In a bar or pie chart, the result is the same—you don't see a bar or a pie slice for that data. However, in some charts, there is a difference. In a line chart a 0 value is plotted on the chart, but an empty cell causes a break in the line. In other words, the line stops just before the missing data, and then starts again at the next point. This broken line indicates missing information.

If you don't like this behavior (perhaps because your empty cells really do represent 0 values), you can change it. Select your chart, and then choose Chart Tools | Design?Data?Select Data to get to the Select Data Source dialog box. Then, click the Hidden and Empty Cells button, which pops open a dialog box with three choices:

•    Gaps. Excel leaves a gap where the information should be. In a line chart, this breaks the line (making it segmented). This option is the standard choice.

•    Zero. Excel treats all blank cells as though they contain the number 0.

•    Span with line. Excel treats all blank cells as missing information and tries to guess what should go in between. If a line chart goes from 10 to 20 with a blank cell in between, Excel interpolates the data point 15 and plots it.

You can also switch on or off the "Show data in hidden rows and columns" setting to tell Excel whether it should include hidden cells when creating a chart. This setting determines how Excel deals with data when you use filtering in a table, or when you explicitly hide rows or columns using the Home?Cells?Format?Hide & Unhide menu.

Ordinarily, Excel treats these missing values just like blank values, and ignores them.

IV. Chart Types

Although there's a lot to be said for simple column charts—they can illuminate trends in almost any spreadsheet—there's nothing quite as impressive as successfully pulling off the exotic bubble chart. This section covers the wide range of charts that Excel offers. If you can use these specialized chart types when they make sense, you can convey more information and make your point more effectively.

Note: The following sections explain all of the Excel chart types. To experiment on your own, try out the downloadable examples, which you can find on the Missing CD page at The examples include worksheets that show most chart types. Remember, to change a chart from one type to another, just select it, and then make a new choice from the ribbon's Insert?Charts section, or use the Chart Tools | Design?Type?Change Chart Type command.

A. COLUMN

By now, column charts probably seem like old hat. But column charts actually come in several different variations (technically known as subtypes). The main difference between the basic column chart and these subtypes is how they deal with data tables that have multiple series. The quickest way to understand the difference is to look at Figure 4-15, which shows a sample table of data, and Figure 4-16, which charts it using several different types of column charts.

Figure 4-15. This simple table of data records the number of female and male students in several rooms at a university. The category is the room name, and there are two data series: the numbers of male students, and the numbers of female students. This data is perfect for a column chart, but different subtypes emphasize different aspects of the data, as you can see in Figure 4-16.

Note: In order to learn about a chart subtype, you need to know its name. The name appears when you hover over the subtype thumbnail, either in the Insert?Charts list (Figure 4-2) or the Insert Chart dialog box (Figure 4-3).

Here's a quick summary of your column chart choices:

•    Clustered Column. In a clustered column, each value's shown in its own separate column. To form a cluster, the columns are grouped together according to category.

•    Stacked Column. In a stacked column, each category has only one column. To create this column, Excel adds together the values from every series for each category. However, the column is subdivided (and color-coded), so you can see the contribution each series makes.

•    100% Stacked Column. The 100% stacked column is like a stacked column in that it uses a single bar for each category, and subdivides that bar to show the proportion from each series. The difference is that a stacked column always stretches to fill the full height of the chart. That means stacked columns are designed to focus exclusively on the percentage distribution of results, not the total numbers.

Figure 4-16. The Clustered Column makes it easy to compare the gender of students in each room, but makes it somewhat more difficult to compare different rooms. The Stacked Column is an elegant way to compress the data, and it lets you compare the total number of students in each room without losing the gender information. The 100% Stacked Column makes each column the same height, so it's useless for comparing total student numbers but perfect for comparing how the gender breakup varies depending on the room. (Notice the scale also changes to reflect that you're comparing percentage values.) Finally, the 3-D chart shows you all the data at once by placing the male student counts in front of the female student counts.

•    3-D Clustered Column, Stacked Column in 3-D, and 100% Stacked Column in 3-D. Excel's got a 3-D version for each of the three basic types of column charts, including clustered, stacked, and 100% stacked. The only difference between the 3-D versions and the plain-vanilla column charts is that the 3-D charts are drawn with a three-dimensional special effect, that's either cool or distracting, depending on your perspective.

•    3-D Column. While all the other 3-D column charts simply use a 3-D effect for added pizzazz, this true 3-D column chart actually uses the third dimension by placing each new series behind the previous series. That means, if you have three series, you end up with three layers in your chart. Assuming the chart is tilted just right, you can see all these layers at once, although it's possible that some bars may become obscured, particularly if you have several series.

Along with the familiar column and three-dimensional column charts, Excel also provides a few more exotic versions that use cylinders, cones, and pyramids instead of ordinary rectangles. Other than their different shapes, these chart types work just like regular column charts. As with column and bar charts, you can specify how cylinder, cone, and pyramid charts should deal with multiple series. Your options include clustering, stacking, 100% stacking, and layering (true 3-D). See Figure 4-17 for an example.

Figure 4-17. Though a cone chart looks a little different, it's really just a column chart in disguise.

B.  BAR

The venerable bar chart is the oldest form of data presentation. Invented sometime in the 1700s, it predates the column and pie chart. Bar charts look and behave almost exactly the same as column charts—the only difference being that their bars stretch horizontally from left to right, unlike columns, which rise from bottom to top.

Excel provides almost the same set of subtypes for bar charts as it does for column charts. The only difference is that there's no true three-dimensional (or layered) bar chart, although there are clustered, stacked, and 100% stacked bar charts with a threedimensional effect. Some bar charts also use cylinder, cone, and pyramid shapes.

Tip: Many people use bar charts because they leave more room for category labels. If you have too many columns in a column chart, Excel has a hard time fitting all the column labels into the available space.

C.  LINE

People almost always use line charts to show changes over time. Line charts emphasize trends by connecting each point in a series with a line. The category axis represents a time scale or a set of regularly spaced labels.

Tip: If you need to draw smooth trendlines, you don't want to use a line chart. That's because a line chart connects every point exactly, leading to jagged zigzagging lines. Instead, use a scatter chart (page 4-30) without a line, and add one or more trendlines afterward, as explained in the next chapter (page 5-32).

Excel provides several subtypes for line charts:

•    Line. The classic line chart, which draws a line connecting all the points in the series. The individual points aren't highlighted.

•    Stacked Line. In a stacked line chart, Excel displays the first series just as it would in the normal line chart, but the second line consists of the values of the first and second series added together. If you have a third series, it displays the total values of the first three series, and so on. People sometimes use stacked line charts to track things like a company's cumulative sales (across several different departments or product lines), as Figure 4-18, bottom, shows. (Stacked area charts are another alternative, as shown in Figure 4-20.)Stacked line charts aren't as common as stacked bar and column charts.

Figure 4-18 (On next page). Here are two different line chart variations—both of which show the same information, although you'd never be able to tell that from looking at them quickly. Top: This chart is a regular line chart that compares the sales for three different regions over time. Bottom: This chart is a stacked line chart, which plots each subsequent line by adding the numbers from the earlier lines. That makes the stacked line chart a great vehicle for showing cumulative totals. For example, sales in Region 3 for April of 2007 appear to top $150,000. That's because the Region 3 line is stacked. It shows a total made up from three components—$72,000 (Region 1), $54,000 (Region 2), and $34,300 (Region 3). In this example, the stacked line chart clearly shows that sales spiked early on, but have risen overall, which isn't clear in the top chart. However, the stacked line chart also obscures the differences between the regions. You'd never guess that Region 3 is the underperforming region because this chart reflects the total of all three regions.

Note: Lines can never cross in a stacked line chart, because Excel adds each series to the one (or ones) before it. You can change which line is stacked at the top by changing the order of the series. To do this, either rearrange your table of data on the worksheet (Excel places the rightmost column on top) or refer to page 4-22, which describes how you can change the order of your series manually.

•    100% Stacked Line. A 100% stacked line chart works the same as a stacked line chart in that it adds the value of each series to the values of all the preceding series. The difference is that the last series always becomes a straight line across the top, and the other lines are scaled accordingly so that they show percentages. The 100% stacked line chart is rarely useful, but if you do use it, you'll probably want to put totals in the last series.

•    Line with Markers, Stacked Line with Markers, and 100% Stacked Line with Markers. These subtypes are the same as the three previous line chart subtypes, except they add markers (squares, triangles, and so on) to highlight each data point in the series.

•    3-D Line. This option draws ordinary lines without markers but adds a little thickness to each line with a 3-D effect.

D.  PIE

Pie charts show the breakdown of a series proportionally, using "slices" of a circle. Pie charts are one of the simplest types of charts, and one of the most recognizable.

Here are the pie chart subtypes you can choose from:

•    Pie. The basic pie chart everyone knows and loves, which shows the breakup of a single series of data.

•    Exploded Pie. The name sounds like a Vaudeville gag, but the exploded pie chart simply separates each piece of a pie with a small amount of white space. Usually, Excel charting mavens prefer to explode just a single slice of a pie for emphasis. This technique uses the ordinary pie subtype, as explained in the next chapter.

•    Pie of Pie. With this subtype, you can break out one slice of a pie into its own, smaller pie (which is itself broken down into slices). This chart is great for emphasizing specific data; it's shown in the next chapter.

•    Bar of Pie. The bar of pie subtype is almost the same as the pie of pie subtype. The only difference is that the breakup of the combined slice is shown in a separate stacked bar, instead of a separate pie.

•    Pie in 3-D and Exploded Pie in 3-D. This option is the pie and exploded pie types in three dimensions, tilted slightly away from the viewer for a more dramatic appearance. The differences are purely cosmetic.

Note: Pie charts can show only one series of data. If you create a pie chart for a table that has multiple data series, you'll see just the information from the first series. The only solution is to create separate pie charts for each series (or try a more advanced chart type, like a donut, described on page 4-34).

E.  AREA

An area chart is very similar to a line chart. The difference is that the space between the line and the bottom (category) axis is completely filled in. Because of this difference, the area chart tends to emphasize the sheer magnitude of values rather than their change over time (see Figure 4-19).

Figure 4-19 (On next page). This example compares a traditional line chart (top) against the area chart (bottom). As you can see, the area chart makes a more dramatic point about the rising sales in Region 2. However, it also obscures the results in Region 1.

Area charts exist in all the same flavors as line charts, including stacked and 100% stacked. You can also use subtypes that have a 3-D effect, or you can create a true 3-D chart that layers the series behind one another.

Stacked area charts make a lot of sense. In fact, they're easier to interpret than stacked line charts because you can easily get a feeling for how much contribution each series makes to the total by judging the thickness of the area. If you're not convinced, compare the stacked charts in Figure 4-18 (bottom) and Figure 4-20. In the area chart, it's much clearer that Region 3 is making a fairly trivial contribution to the overall total.

Figure 4-20. You can create an area chart that doesn't obscure any data, but it needs to be a stacked (as shown here) or 3-D area chart. The stacked area chart shows the combined total of all regions, but it still lets you pick out the most important series. For example, it's clear that Region 3 (the narrow sliver on top of the stack) contributes relatively little to the total, while Region 1 and Region 2 are more important.

F. XY (SCATTER)

XY Scatter charts show the relationship between two different sets of numbers. Scatter charts are common in scientific, medical, and statistical spreadsheets. They're particularly useful when you don't want to connect every dot with a straight line. Instead, scatter charts let you use a smooth "best fit" trendline, or omit the line altogether. If you plot multiple series, the chart uses a different symbol (like squares, triangles, and circles) for each series, ensuring that you can tell the difference between the points.

Why would you want to plot data points without drawing a line? For one thing, you may need to draw conclusions from an inexact or incomplete set of scientific or statistical data. Scientific types may use a scatter chart to determine the relationship between a person's age and his reflex reaction time. However, no matter how disciplined the experimenters, they can't test every different age. In addition, their data will include natural variations from the overall trend. (In other words, if the trend is for older people to have gradually slowing reactions, you're still likely to run across a few exceptionally speedy older folks.) In this case, the best approach is to include no line, or use a smooth "best fit" line that indicates the overall trend, as shown in Figure 4-21.

Figure 4-21. This XY Scatter chart shows the relationship between a person's age and his reflex reaction time.

Excel offers several scatter chart subtypes, including:

•    Scatter with Only Markers. This scatter chart uses data markers to show where each value falls. It adds no lines.

•    Scatter with Smooth Lines and Markers. This scatter chart adds a smooth line that connects all the data points. However, the points are connected in the order they appear in the chart, which isn't necessarily the correct order. You're better off adding a trendline to your chart, as explained in the next chapter.

•    Scatter with Straight Lines and Markers. This subtype is similar to the scatter chart with smoothed lines, except it draws lines straight from one point to the next. A line chart works like this, and this subtype makes sense only if you have your values in a set order (from lowest to largest or from the earliest date to the latest).

•    Scatter with Smooth Lines and Scatter with Straight Lines. These subtypes are identical to the scatter with smooth lines and markers and the scatter with straight lines and markers. The only difference is they don't show data markers for each point. Instead, all you see is the line.

G. STOCK

A stock chart displays specialized charts for stocks. Usually, these charts show how a stock value changes over a series of days. The twist is that the chart can display information about the daytime high and the daytime low of the stock, along with its opening and closing value. Excel uses all this information to draw a vertical bar from the stock's low point to its high point on a given day. If you're really ambitious, you can even add volume information (which records the number of shares traded on a given day).

Stock charts are more rigid than most other chart types. In order to use a stock chart, you need to create a column of numbers for each required value. The type of columns you need and their order depends on the stock chart subtype that you select. Here are your choices:

•    High-Low-Close

•    Open-High-Low-Close

•    Volume-High-Low-Close

•    Volume-Open-High-Low-Close

In each case, the order of terms indicates the order of columns you should use in your chart. If you select Volume-High-Low-Close, then the leftmost column should contain the volume information, followed by another column with the stock's daytime high, and so on. (Technically, you can use the Chart Tools | Design?Data?Select Data command to specify each series, even if it's not in the place Excel expects it to be. However, this maneuver is tricky to get right, so it's easiest to just follow the order indicated by the chart type name.) No matter which subtype you use, a stock chart shows only values for a single stock.

Tip: The simplest stock chart (High-Low-Close) is also occasionally useful for charting variances in scientific experiments or statistical studies. You could also use a stock chart to show high and low temperature readings. Of course, you still need to follow the rigid stock chart format when ordering your columns.

Figure 4-22 shows an example of a Volume-High-Low-Close.

Figure 4-22. The Volume-High-Low-Close chart shows a combination of related information. The columns at the bottom show the number of shares traded (using the value scale on the left). The lines above these columns show the stock price (using the value scale on the right). For each day, a bar that spans from the day's low to the day's high represents the stock price. The closing price is marked with a tick in the middle of the bar. If you like, you can add a trendline to show the movement of the stock price using the techniques explained in the next chapter.

H. SURFACE

A surface chart shows a 3-D surface that looks a little like a topographic map, complete with hills and valleys. Surface charts are different from most other charts in that they show the relationship of three values. Two category axes (X and Y) determine a data point's position. The value determines the height of the data point (technically known as the Z-axis). All of the points link to create a surface.

Surface charts are neat to look at, but ordinary people almost never create them, as they're definitely overkill for tracking your weekly workout sessions. For one thing, to make a good surface chart, you need a lot of data. (The more points you have, the smoother the surface becomes.) Your data points also need to have a clear relationship with both the X and Y axes (or the surface you create is just a meaningless jumble). Usually, rocket-scientist types use surface charts for highly abstract mathematical and statistical applications. Figure 4-23 shows an example.

Figure 4-23. A surface chart usually represents scientific data or mathematical models. Here, the surface chart represents a heat index table. Heat index tables show the perceived temperature under different conditions of humidity. If it's 70 degrees with 90 percent humidity, the dampness makes it feel like 66.9. On the other hand, if it's 85 with 90 percent humidity, it feels to a human observer like a balmy 102.

I. DONUT

The donut chart (Figure 4-24) is actually an advanced variation on that other classic food-themed chart, the pie chart. But while a pie chart can accommodate only one series of data, the donut can hold as many series as you want. Each series is contained in a separate ring. The rings are one inside the other, so they all fit into a single compact circle.

The donut chart's ideal for comparing the breakdown of two different sets of data. However, the data on the outside ring tends to become emphasized, so make sure this series is the most important. To change the order of the series, see page 4-22.

Donuts have two subtypes: standard and exploded. (An exploded donut doesn't suggest a guilty snack that's met an untimely demise. Instead, it's a donut chart where the pieces in the topmost ring are slightly separated.)

Although the donut chart can hold as many series as you want, if you add more than two or three, the chart may appear overly complicated. No matter what you do, the center of the donut is never filled in (unless you decide to add some text there using Excel's drawing tools, which are covered in Chapter 6).

Figure 4-24. Here, a donut chart compares the sales in two different years. The inner ring shows the sales for 2003, broken down by region. The outer ring shows the sales for 2004. Donut charts sometimes need a bit of customizing before they look right. Here, the chart has labels that detail the percentage of each slice, and the year represented by each ring. (You can learn how to add these labels using Excel's drawing tools in Chapter

6.)

Note: Think twice before you use a donut chart in a presentation. Most Excel gurus avoid this chart, because it's notoriously difficult to explain.

J.   BUBBLE

The bubble chart is an innovative variation on the scatter chart. It plots only a single series, and it never draws a line. Each point is marked with a circle—either an ordinary circle or a 3-D sphere, depending on the subtype you choose. The extra frill is that the bubble sizes change based on a second set of related values. The larger the value, the larger the data point bubble. In any bubble chart, the largest bubble is always the same size. Excel scales the other bubbles down accordingly. Figure 4-25 shows an example.

Figure 4-25. Each bubble's position represents two values: the month (the category axis) and the number of units sold (the value axis). Each bubble's size reflects the profit generated by the units sold.

K.  RADAR

The radar chart (Figure 4-26) is a true oddity, and it's typically used only in specialized statistical applications. In a radar chart, each category becomes a spoke, and every spoke radiates out from a center point. Each series has one point on each spoke, and a line connects all the points in the series, forming a closed shape. All these spokes and lines make the chart look something like the radar on an old-time submarine.

You have three radar subtypes: the standard radar chart; a radar chart with data markers indicating each point; and a filled radar, where each series appears as a filled shape, somewhat like an area chart. No matter what subtype you use, choosing data for a radar chart isn't easy.

Figure 4-26. This filled radar chart compares the products sold in sales offices in two cities. Because all the categories (in this case, the various products) are joined into a closed shape, the radar chart acts somewhat like an area chart, so you can judge the significance of values by looking at the size and shape of the area. It's easy to see that bicycles are selling well with customers in the New York store, while trucks lead the way in Paris. If the two series had similar results, you couldn't effectively use a filled radar chart, because some of the data would be obscured.

CHAPTER 4 – REVIEW QUESTIONS

The following questions are designed to ensure that you have a complete understanding of the information presented in the chapter. They do not need to be submitted in order to receive CPE credit. They are included as an additional tool to enhance your learning experience.

We recommend that you answer each review question and then compare your response to the suggested solution before answering the final exam questions related to this chapter.

1.    Which of the following is a step you should perform prior to plotting information on a chart:

a)    structure the data in a simple grid of rows and columns

b)    eliminate blank cells between rows and columns

c)    include any titles you would like to appear on the chart

d)    all of the above

2.    Which Chart Tool lets you add shapes, figures, and text labels to a chart:

a)    Design

b)    Layout

c)    Format

d)    all of the above

3.    How many charts does Excel allow you to create off one data set:

a)    1

b)    2

c)    5

d)    unlimited

4.    Creating a chart with one series of numbers is simple, but it requires quite a bit more expertise to create a chart that uses multiple series.

a)    true

b)    false

5.    When a zero is entered into a cell, Excel treats it as an empty cell.

a)    true

b)    false

6.    Which chart type was invented sometime in the 1700s:

a)    line chart

b)    bar chart

c)    column chart

d)    all of the above

7.    Which type of chart is common for scientific, medical, and statistical spreadsheets:

a)    stacked area charts

b)    bar charts

c)    surface charts

d)    XY scatter charts

8.    In a bubble chart, each category becomes a spoke, and every spoke radiates out from the center, then a line connects all the points forming a closed shape.

a)    true

b)    false

 

CHAPTER 4 – SOLUTIONS AND SUGGESTED RESPONSES

1.    A: Incorrect.  Before plotting information on a chart, you would want to put the desired data in rows and columns, but the other choices are also correct.

B: Incorrect.  Before plotting information on a chart, you would want to eliminate any blank cells, but the other choices are also correct.

C: Incorrect. Before plotting information on a chart, you would want to include any necessary titles, but the other choices are also correct.

D: Correct.  Before you plot information on a chart, you would want to have the data in rows and columns, eliminate any blank cells, and include any desired titles, therefore, all of the above is the only correct answer.

(See page 4-1 of the course material.)

2.    A: Incorrect.  The Design tab lets you change the chart type and the linked data that the chart uses, but it does not allow you to add shapes, figures, and text labels.

B: Correct.  You can add shapes, pictures, and text labels to individual parts of a chart using the Layout tab.  The Layout tab also allows you to configure the chart’s gridlines, axes, and background.

C: Incorrect.  The Format tab lets you format individual chart elements such as font, fill, and borders.

D: Incorrect.  The Layout tab is the only Chart Tool that allows you to add shapes, pictures, and text labels to a chart.

(See page 4-5 of the course material.)

3.    A: Incorrect.  Excel has no restriction on linking multiple charts to the same data.

B: Incorrect.  Excel has no restriction on linking multiple charts to the same data.

C: Incorrect.  Excel has no restriction on linking multiple charts to the same data.

D: Correct.  Excel has no restriction on linking multiple charts to the same data, so you can create as many charts as you like from one data set.  You can create different types of charts from the same data and also create charts off a subset of the data.

(See page 4-10 of the course material.)

4.    A: True is incorrect.  It doesn’t take any more expertise to create a chart that uses multiple series than it does to create a chart with just one series of numbers.   You simply designate the range of cells you want and then pick the chart options from the ribbon, the same as you would with a single series.

B: False is correct.  A series is a sequence of numbers that you plot on a graph.  It is just as simple to create the chart with multiple series as it is with a single series.  Different types of charts handle multiple series in different ways, but they are no more difficult to create.

(See page 4-13 of the course material.)

5.    A: True is incorrect.  The value of zero doesn’t count as an empty cell; it counts as a cell containing the number zero.

B: False is correct.  A zero entered into a cell counts as a numbered cell with the value of zero.  Excel ignores empty cells, but it does not ignore a cell with a zero in it.

(See page 4-22 of the course material.)

6.    A: Incorrect.  The bar chart, not the line chart, is the oldest form of data presentation and was invented sometime in the 1700s.

B: Correct.  The bar chart was invented in the 1700s and is the oldest form of data presentation.  It predates the column chart and the pie chart.

C: Incorrect.  The bar chart, not the column chart, is the oldest form of data presentation and was invented sometime in the 1700s.

D: Incorrect.  The bar chart is the oldest form of data presentation and the only one invented sometime in the 1700s, so “all of the above” cannot be correct.

(See page 4-25 of the course material.)

7.    A: Incorrect.  XY scatter charts are the ones most commonly used for scientific, medical, and statistical data.  Stacked area charts are similar to line charts but are easier to interpret due to the thickness of the area, but connecting the dot type of charts are not as appropriate to scientific data.

B: Incorrect.  XY scatter charts are the ones most commonly used for scientific, medical, and statistical data.  Bar charts are similar to column charts, and these types of charts are not as appropriate to scientific or medical data.

C: Incorrect.  XY scatter charts are the ones most commonly used for scientific, medical, and statistical data.  Surface charts are most useful in showing the relationship between three variables, not with scientific and medical data.

D: Correct.  XY scatter charts are the ones most commonly used for scientific, medical, and statistical data.  These charts let you use a smooth “best fit” trendline or even omit the line altogether, so they work well with this type of data.

(See page 4-30 of the course material.)

8.    A: True is incorrect.  This is a description of a radar chart, not a bubble chart.  A bubble chart is a variation of the scatter chart.  It plots only a single series, and it never draws a line.

B: False is correct.  A radar chart, not a bubble chart, identifies one category for every spoke which radiates out from a center point.  Each series has one point on each spoke, and then a line connects all the points in the series, forming a closed shape.  This is typically used in specialized statistical calculations.

(See page 4-36 of the course material.)  


Chapter 5: Formatting and Perfecting Charts

In the previous chapter, you learned how Excel charts work and how you can transform ordinary tables of information into graphical representations, complete with columns, bars, lines, and even bubbles. But creating the right chart is only half the battle. The next step is refining your charts so they convey their point more effectively. Often this step means tweaking the chart's formatting, inserting labels, and fine-tuning the scale. But if you're really ambitious, you'll want to tackle more advanced professional charting techniques, like trendlines, overlays, and combination charts. These techniques let you turn plain-vanilla charts into polished graphics—like the ones you see in magazines, annual reports, and brochures.

In this chapter, you'll start by looking at how you can use basic formatting techniques to change the color and font of different chart components. Then you'll learn how to set a chart's scale, unleash 3-D views, and make your data stand out no matter what type of chart you use.

I. Chart Styles and Layouts

Excel provides a set of chart styles that you can use to give the plainest chart a makeover.

Like cell styles, chart styles draw from the colors, fonts, and shape effects that are part of each theme. If you use the Trek theme, your chart styles draw upon a palette of earthy tones, while the Verve theme gives you a much more vivid set of colors. If you use cell styles, table styles, and chart styles, the fonts and colors are consistent everywhere. You can also swap in a new palette for all these elements just by choosing a new theme.

Tip: Before you choose a chart style, it helps to pick the theme you want to use so you can see the real, final result. To change the theme, make a selection from the Page Layout?Themes?Themes list.

A. CHART STYLES

Chart styles give you a way to apply shake-and-bake formatting to ordinary charts. Excel includes a wide range of chart styles that vary from simple, flat charts with minor accents (like colored borders) to showier styles that include bevel effects and shadows. You can quickly create plain or opulent charts, depending on your needs.

Before you use a chart style, it's important to understand what it changes (and what it doesn't). Every chart style includes settings that determine:

•    The background color.

•    The colors for each series (which may be displayed as bars, lines, points, or something else). Some colorful chart styles use all the colors from the current theme. Other chart styles take a single color from the current theme and use multiple shades of that color for a more refined look.

•    Shape effects, like softly curved or beveled edges, shadows, and glow.

•    Marker styles (for line and XY scatter charts) that distinguish the points in one series from those in another.

Note: Some chart styles use a heavy black background with bold colors. This sort of style isn't designed for worksheets because it can tie the best color printer in knots. But these high-contrast styles look good on computer monitors and projection screens, so use them if you want to cut and paste your chart into a PowerPoint presentation.

On the other hand, chart styles don't change the font Excel uses for the chart title and labels; instead Excel bases these elements on the current theme. Chart styles also don't change the layout of the chart or the chart settings Excel uses for the legend, scale, axis titles, error bars, and so on. (You haven't yet seen how to tweak all these details, but you'll learn about them later in this chapter.)

To choose a style, select the chart, and then head to the ribbon's Chart Tools | Design?Chart Styles section, which gives you a gallery of choices (Figure 5-1).

Note: If you've resized your Excel window to be very small, the chart styles don't fit in the ribbon and you see a Quick Styles button instead. Click this button to see the full list of styles.

A chart can use only one style at a time, so if you choose a new style, Excel wipes out the existing formatting.

Figure 5-1. Click any of the Chart Style options to dress your chart up in different colors. The scroll bars (circled) let you move through the style options not currently shown, or click the down arrow to see a drop-down window with all your choices.

Note: One key difference between chart styles and other types of styles (like cell styles and table styles) is that you can't create your own chart styles. However, you can choose a chart style to use as a starting point, further customize it, and then save it as a template so you can reuse it again and again. You'll learn how this feature works on page 5-26.

B. CHART LAYOUTS

Chart styles make it easy for you to change the colors and visual styling in a chart. Chart layouts are complementary—they let you control the presence and placement of various chart elements, like the chart and axis titles, and the legend.

As you'll learn in the next section, Excel lets you tweak each of these ingredients separately. However, you can choose a prebuilt layout to do it all in one shot. To try it out, head to the ribbon, and then make a choice from the Chart Tools | Design?Chart Layouts section. As with styles, the list of available charts depends on the chart type. Figure 5-2 shows an example.

Note: To make chart layouts as practical as possible, the creators of Excel reviewed thousands of professional charts and identified the most common arrangements. Most Excel pros still want to customize the various parts of their chart by hand. However, a chart layout can provide a great starting point.

II. Adding Chart Elements

You build every chart out of small components, like titles, gridlines, axes, a legend, and the bars, points, or exotic shapes that actually represent the data. And Excel lets you manipulate each of these details separately. That means you can independently change the format of a label, the outline of a bar, the number of gridlines, and the font and color of just about everything.

Figure 5-2 (On next page). This worksheet shows two copies of the same chart, each with a different layout. The chart at the top includes heavy gridlines, axis titles, and a legend on the right. The chart below has a chart title and places the legend at the top. It also has no gridlines and instead displays the series value above each column.

Figure 5-3 shows the different elements that make up a chart. They include:

•    Title. The title labels the whole chart. In addition, you can add titles to the axes. If you do, then you can select these titles separately.

•    Legend. The legend identifies each data series on the chart with a different color. A legend's useful only when the chart contains more than one series.

•    Horizontal and Vertical Axes. An axis runs along each edge of the chart and determines the scale. In a typical two-dimensional chart, you have two axes: the category axis (typically on the bottom of the chart, running horizontally), and the value axis (typically on the left, running vertically).

•    Plot Area. The plot area is the chart's background, where the gridlines are drawn. In a standard chart, the plot area is plain white, which you can customize.

•    Chart Area. The chart area is the white space around the chart. It includes the space that's above, below, and to either side of the plot area.

•    Gridlines. The gridlines are the lines that run across the plot area. Visually, you line the data up with the gridlines to get an idea of the value of each data point. Every chart starts out with horizontal gridlines, but you can remove them or add vertical gridlines. You can tell Excel how many gridlines to draw, and even how to format them.

•    Data Series. The data series is a single set of data plotted on the chart across the category axis. In a line chart, for example, the data series is a single line. If a chart has multiple series, you'll often find it useful to format them separately to make them easier to differentiate or to emphasize the most important one.

•    Data Point. A data point is a single value in a data series. In a line chart, a data point's a single dot, and in a column chart, a data point is a single column. If you want to call attention to an exceptionally important value, you can format a data point so it looks different from the rest of the series.

Figure 5-3. Before you can begin tweaking your chart's formatting, you need to know the names of the different elements you'll find on a chart, all of which are labeled here.

Not all charts include all these elements. As you learned on page 5-3, the layout you pick determines whether you begin with a chart title, a legend, gridlines in the background, and so on. However, in many cases you'll want to pick and choose exactly the elements you want. Excel lets you do this with the buttons on the ribbon's Chart Tools | Layout tab. The following sections show you how.

A. ADDING TITLES

It doesn't matter how spectacular your chart looks if it's hard to figure out what the data represents. To clearly explain what's going on, you need to make sure you have the right titles and labels.

An ordinary chart can include a main title (like "Increase in Rabbit Population vs. Decrease in Carrot Supplies") and titles on each axis (like "Number of Rabbits" and "Pounds of Carrots"). To show or hide the main title, make a selection from the Chart Tools | Layout?Labels?Chart Title list. Your options include:

•    Above Chart puts a title box at the very top and resizes the chart smaller to make room.

•    Centered Overlay Title keeps the chart as is but superimposes the title over the top. Assuming you can find a spot with no data, you get a more compact display.

•    None hides the title altogether.

Once you select one of those options, you see the title box; click inside it, and then type in new text, as shown in Figure 5-4.

Figure 5-4. You can type in whatever text you'd like for a chart title. If you select part of the text, a mini bar appears (sadly, of the alcohol-free variety), with formatting options for changing the font, size, color, and alignment. These commands are the same as in the Home?Font section of the ribbon, but it's way more convenient to reach them here.

You can just as easily add a title to each axis using the Chart Tools | Layout?Labels?Axis Titles?Primary Horizontal Axis Title and Chart Tools | Layout?Labels?Axis Titles?Primary Vertical Axis Title lists. You'll find options for showing your title, hiding it, and (in the case of a vertical axis), showing a title that's rotated to run neatly along the side of your chart.

Note: As with almost all chart elements, you can also format titles by adding a border, a shadow effect, and a fancy background fill. To get these options, right-click the title, and then choose Format Chart Title. You'll learn more about these options throughout this chapter.

B.  ADDING A LEGEND

Titles help explain a chart's overall purpose. Usually, titles indicate what a chart is comparing or analyzing. You may add a chart title like "Patio Furniture Sales" and the axis labels "Gross Revenue" and "Month of Sale" to a chart that shows how patio furniture sales pick up in the summertime. However, the category labels don't help you single out important data. They also don't let you point out multiple series (like the sales results in two different stores). You can fix this problem by adding additional labels or a legend. A legend is a separate box off to the side of the chart that contains one entry for each data series in a chart. The legend indicates the series name, and it adds a little sample of the line style or fill style that you've used to draw that series on the chart.

Excel automatically adds a legend to most charts. If you don't already have a legend, you can choose a layout that includes one, or you can make a selection from the Chart Tools | Layout?Labels?Legend list. Different selections let you position the legend in different corners of the chart, although true Excel pros just drag the legend box to get it exactly where they want it.

Legends aren't always an asset when you need to build slick, streamlined charts. They introduce two main problems:

•    Legends can be distracting. In order to identify a series, the person looking at the chart needs to glance away from the chart to the legend, and turn back to the chart again.

•    Legends can be confusing. Even if you have only a few data series, the average reader may find it hard to figure out which series corresponds with each entry in the legend. This problem becomes more serious if you print your chart out on a printer that doesn't have the same range of colors as your computer monitor, in which case different colored lines or blocks may begin to resemble each other.

If you don't want to use a legend for these reasons, you can use data labels instead, as described in the next section.

C.  ADDING DATA LABELS TO A SERIES

Data labels are labels that you attach to every data point in a series. This text floats just above the point, column, or pie slice that it describes, clearly identifying each piece of information. Data labels have unrivalled explaining power—they can identify everything. The only possible drawback is that adding data labels to a chart that's already dense with data may lead to an overcrowded jumble of information.

To apply data labels, choose a position from the Chart Tools | Layout?Labels?Data Labels list. If you choose Chart Tools | Layout?Labels?Data Labels?Center on a column chart, each bar's value appears as a number that's centered vertically inside the bar. On the other hand, if you choose Chart Tools | Layout?Labels?Data Labels?Outside End, the numbers appear just above the top of each column, which is usually more readable (Figure 5-5).

Tip: No matter how you choose to label or distinguish a series, you're best off if you don't add too many of these elements to the same chart. Adding too many labels makes for a confusing overall effect, and it blunts the effect of any comparison.

Figure 5-5. Here, you can see how a value label adds information to a column chart. Even without the labels, you could still get this information by eyeing where the bar measures up to on the value axis on the left, but the labels make it a whole lot easier to get the information with a single glance. The labels have been customized slightly via the Format Data Labels dialog box to shrink their font size and add a simple box border with a shadow effect.

If you're in an adventurous mood, you can create even more advanced labels by choosing Chart Tools | Layout?Labels?Data Labels?More Data Label Options. The Format Data Labels dialog box appears, with a number of options for customizing data labels (Figure 5-6).

Using the Format Data Labels dialog box, you can choose the data label's position (just like you could from the Chart Tools | Layout?Labels?Data Labels list). But the options under the Label Contains heading are more interesting, as they let you chose the information that appears in the label. Ordinarily, the information is simply the value of the data point. However, you can also apply a combination of values. Your exact options depend on the type of chart you've created, but here are all the possible choices:

•    Series name. The series name identifies the series each data point comes from. Because most series have multiple data points, using this option means the same text repeats again and again. In a line chart that compares sales between two stores, using this option would put the label "Store 1" above each data point for the first store, which is probably overkill.

•    Category name. The category name adds the information from the category axis. If you're using a line chart to compare how sales fluctuated month by month, then this option adds the month above every data point. Assuming you have more than one line in your line chart, this option creates duplicate labels, which crowds out the important information in your form. For that reason, category labels don't work very well with most charts, although you can use them to replace the legend in a pie or donut chart.

•    Value. Value labels insert the data that corresponds with a data point. This data is the actual information in the corresponding cell in your worksheet. If you're plotting changing sales, this data is the dollar amount of sales for a given month. Value labels are probably the most frequently used type of label.

Figure 5-6. The Format Data Labels dialog box is divided into several sections of settings. (You can see one setting at a time by picking from the list on the left.) For now, you're interested in the Label Options section. You'll learn how to use the other formatting settings, which apply to all chart elements, later in this chapter.

•    Percentage. Percentage labels apply only to pie charts and donut charts. They're similar to value labels, except they divide the value against the total of all values to find a percentage.

•    Bubble size. Bubble size labels apply only to bubble charts. They add the value from the cell that Excel used to calculate the bubble size next to each bubble. Bubble labels are quite useful in bubble charts because bubble sizes don't correspond to any axis, so you can't understand exactly what numeric value a bubble represents just by looking at the chart. Instead, you can judge relative values only by comparing the size of one bubble to another.

Note: In some charts (including XY scatter charts and bubble charts), the checkboxes "Category name" and "Value" are renamed as "X Value" and "Y Value", although they have the same effect as "Category name" and "Value."

When you use multiple items, you can also choose a character from the Separator list box to specify how to separate each piece of text in the full label (with a comma, space, semicolon, new line, or a character you specify). And if you want to display a mini square with the legend color next to the label, then choose "Include legend key in label" (although most people don't bother with this feature).

Figure 5-7 shows more advanced data labels at work.

Figure 5-7. Here's how you can combine percentage and category information to make a pie chart more readable and eliminate the legend altogether.

Note: Wondering what your chart will look like? As you make changes, Excel updates the chart on the worksheet using its handy live preview feature. Just move the Format Data Labels dialog box out of the way to get a sneak peak before you confirm your choices.

D. ADDING INDIVIDUAL DATA LABELS

In simple charts, data series labels work well. But in more complex charts, data series labels can be more trouble than they're worth because they lead to chart overcrowding, particularly with line charts or any chart that has multiple series. The solution is to add labels to only a few data points in a series—those that are most important. Figure 58 shows the difference.

Figure 5-8. Data point labels work particularly well with line charts and scatter charts because both chart types are dense with information. The two examples here underscore that fact. Top: Here, a single data point label indicates the point where the sales changed dramatically for the Region 1 office. Bottom: Here's the mess that results if you add data labels to the whole Region 1 and Region 2 series. No amount of formatting can clear up this confusion.

To add an individual data label, follow these steps:

1.    Click the precise data point that you want to identify. This point could be a slice in a pie chart, a column in a column chart, or a point in a line chart.

Selecting a data point is a little tricky. You need to click twice—the first click selects the whole series, and the second click selects just the data point you want. You'll see the handles appear around the specific column or point to indicate you've selected it, as shown in Figure 5-9.

2.    When you have the right data point selected, choose an option from the Chart Tools | Layout?Labels?Data Labels list.

These options work the same way they do when you format the whole series, except now they apply only to the currently selected value.

To remove a data label, click to select it, and then press Delete. If you want to add several data labels, you're best off adding all the data labels (as described in the previous section), and then deleting the ones you don't want.

Figure 5-9. Top: To select a single data point, click it twice. The first click selects the whole Sales-05 series. Bottom: The second click gets just the data point you want, which is the Sales-05 data in the Western sales office. In some cases, you may not be able to tell which data point's the one you want, especially if you're creating a dense scatter chart. When that happens, just hover over the data point briefly to see a tooltip with the category, series, and value information.

Tip: If a data label doesn't have exactly what you want, you can click inside it and edit the text, just as you do with a chart title.

As with data series labels, right-clicking a data label gives you a choice of formatting options (choose Format Data Labels). It's almost always a good idea to format your labels so they stand out and don't crowd other information on the chart. Page 5-16 describes more about formatting chart elements.

Tip: Instead of using data labels, you can add arrows and text boxes anywhere on a chart to call out important information. To do so, you need Excel's drawing features, explained in the next chapter.

E. ADDING A DATA TABLE

Trying to pack as much information as possible into a chart—without cluttering it up—is a real art form. Some charting aficionados use labels, titles, and formatting to highlight key chart details, and then use the data on the worksheet itself to offer a more detailed analysis. However, Excel also provides a meeting point between chart and worksheet that works with column charts, line charts, and area charts. It's called the data table.

Excel's data table feature places your worksheet data under your chart, but lined up by category. You can best understand how this feature works by looking at a simple example, like the one in Figure 5-10.

To add a data table, select your chart, and then choose Chart Tools | Layout?Labels?Data Table?Show Data Table. Or, if you want each series in the data table to have a small square next to it with the same color as the matching data series, then choose Chart Tools | Layout?Labels?Data Table?Show Data Table with Legend Keys. This way, you might not need a legend at all.

Figure 5-10. This data table removes the need for a legend or data point labels. However, keep in mind that data tables don't work well with large amounts of data.

III. Selecting Chart Elements

Some chart elements, like titles and axes, are obvious. Others are a little more subtle. But it's important to realize that everything you add to a chart is its own distinct ingredient, and you can tweak each ingredient independently—even the data labels that identify important values (page 5-10). That means you can select them, move them, delete them, and so on. Understanding this principle is the secret to creating charts that stand out from the crowd.

So far, you've covered how to add the most popular elements to your chart, using either a readymade layout or the lists in the Chart Tools | Layout tab. But you haven't learned about everything you can do with a chart element once you've created it. In this section (and those that follow), you'll learn how to grab hold of the element you want and further fine-tune it.

If you want to manipulate your chart components, you have to first learn how to select them. The easiest way to do so is to click directly on the element you want to change, as shown in Figure 5-11.

Figure 5-11. When you select a part of a chart, a rectangle appears around the item, with a circle (known as a handle) in each corner. In this example, the chart title is selected.

Some elements—like titles and legends—are easy to select this way. Others, like gridlines and axes, are a little trickier because you need to click in exactly the right place. If you end up selecting the wrong element, it's sometimes easiest to just use the arrow keys to select another one. Each time you press an arrow key, Excel selects the next chart element. So, if you keep pressing an arrow key, you'll eventually cycle through all the elements that you can select in the current chart.

You can also select chart elements with the ribbon's Chart Tools | Format?Current Selection section, as shown in Figure 5-12. Using this list, you can select any of the chart elements described earlier (page 5-4), except individual data points.

Figure 5-12. Click the drop-down arrow in the box at the top of this section (Chart Tools | Format?Current Selection) to show a list of all the elements in the current chart. When you choose an item from the list, Excel automatically highlights the corresponding part of the chart.

Tip: To check which element you've selected, just right-click it. The last item in the shortcut menu makes everything clear. If the context menu starts with Format Axis, you know that you've selected a chart axis. If it says Format Gridlines, you've got yourself a gridline. Or, you can check the Chart Tools | Format?Current Selection section of the ribbon.

So what do you do with a chart element once you've selected it? You can perform three basic tasks, although not every chart element supports every task. The tasks include:

•    Deleting an element. To remove a selected element from your chart, just press Delete. You can delete any chart element, including titles, legends, data series, gridlines, the background, and even an axis. It's possible to restore these elements after you've deleted them by choosing a new layout (page 5-3) or using the Undo command (Ctrl+Z) immediately after.

•    Moving an element. You can move a chart element by dragging it, just like the way you move the whole embedded chart. You can easily move some elements, like labels, legends, and the whole chart. Other chart elements—including axes, gridlines, and data points—can't go anywhere.

•    Resizing an element. You can resize an element by dragging one of the resizing handles (the circles that appear at each corner of the element box when you select it). Resizing is mainly useful with legends and the overall chart—there aren't many other chart elements you can resize.

IV. Formatting Chart Elements

Often, you don't select a chart element to delete or move it, but rather to format it with a different border, font, or color. In this case, simply right-click the element, and then choose the format option from the pop-up menu (or, select it, and then choose Chart Tools | Format?Current Selection?Format Selection from the ribbon). The following sections get you started.

A. COLORING THE BACKGROUND

Now you're ready to start creating spiffy-looking, customized charts. The background color is a good starting point. Initially, this color is a plain white, but it's easy enough to change if you want to add a personal touch. Just follow these steps:

1.    Select the plot area.

To select the plot area, click the empty space between gridlines, or choose Chart Tools | Format?Current Selection?Plot Area.

2.    Right-click the plot area, and then choose Format Plot Area, or choose Chart Tools | Format?Current Selection?Format Selection.

A Format Plot Area dialog box appears (Figure 5-13). Here you can set the background color (Fill), add a border around it (Border Color and Border Styles), and use advanced Shadow and three-dimensional effects (Shadow and 3-D Format). For now, focus on the Fill tab.

Figure 5-13. It's worth getting familiar with all the formatting options in the Format Plot Area dialog box, because you'll see many of the same options again when you format other chart elements, like columns, gridlines, and just about everything else.

3.    Choose "Solid fill", and then click the paint can button to choose from a list of colors.

For the most flexible formatting, choose one of the theme colors (which appear under the heading Theme Colors). That way, if you choose a new theme, then the chart switches itself to one of the new theme colors. Don't worry about the fancy fills for now—you'll take them on in the next section.

4.    When you've finished making your changes, click OK.

Excel applies the results of your artistry.

Note: Remember, if you don't have a color printer, you need to think about how colors convert when you print them in black and white. In some cases, the contrast may end up being unacceptably poor, leading to charts that are difficult to read. As a general rule, the less powerful your printer, the less you should use graphically rich details like tiles, background images, and gradients—unless, of course, you're planning to view your worksheet only onscreen.

The neat thing about this sequence of steps is that you can use exactly the same process to format any chart element. That means you now know enough to give a solid fill to a chart title, the gridlines, the columns in a column chart, and so on. And once you learn your way around the rest of the formatting options, you'll be able to really spiff up your chart.

Tip: If you run rampant changing a chart element and you just want to return it to the way it used to be, select it, and then choose Chart Tools | Format?Current Selection?Reset to Match Style. Excel removes your custom formatting and leaves you with the standard formatting that's based on the chart style.

B. FANCY FILLS

Coloring the background of a chart is nice, if a little quaint. In the 21st century, charting mavens are more likely to add richer details like textured backgrounds or gradient fills. Excel gives you these options and more. And although textured fills don't always make sense, they can often add pizzazz when used in the background of a simple chart. You can apply fancy fills to the chart background (the plot area) or individual chart items, like the columns in a column chart. Figure 5-14 shows some of your fill choices.

To apply a fancy fill, start by selecting the chart element, and then choosing Chart Tools | Format?Current Selection?Format Selection to get to the appropriate formatting window (like Format Plot Area). Then, choose the Fill section. What you do next depends on the type of fill you want.

Figure 5-14. Depending on the type of fill you use, you can subtly change the message your chart conveys. These figures show a picture fill (top left), texture fill (top right), and gradient fill (bottom), each with the same numbers and chart type. Fancy fills are particularly useful if you want to use your charts in a presentation program like PowerPoint. 

Gradient fills

A gradient is a blend between two colors. You may use a black-and-white gradient that gradually fades from black in the top-left corner to white in the bottom-right corner. More complex gradients fade from one color to another to another, giving a 1960s-era tie-dye effect. To set a gradient, in the formatting window's Fill section, choose the "Gradient fill" choice.

You can use two basic strategies to choose your gradient: the colors and the shading pattern. For the utmost simplicity, you can use a prebuilt set of color and shading options. You choose this option by clicking the "Preset colors" button, and then clicking one of the thumbnail previews that appears in the drop-down list. Each one has a picturesque name like Late Sunset or Ocean. (As you make a choice here, Excel updates your chart to show the results you'll get if you apply the new fill.) The box below explains your custom gradient options.

Texture fills

A texture is a detailed pattern that's tiled over the whole chart element. The difference between a texture and an ordinary pattern is that patterns are typically simple combinations of lines and shading, while a texture uses an image that may have greater, more photographic detail.

Power Users’ Clinic: Custom Gradients

If you're an unrelenting micromanager, you may want to choose your own gradient colors. But be forewarned—Excel lets you design gradient fills in truly ridiculous detail. Here's the recipe you need to follow to cook up your own custom gradient.

If you're just starting out, begin with one of the choices from the "Preset colors" list. You can then modify it.

1.    From the Type list, choose a gradient type.

The type determines how the fill's shaded. A normal linear gradient shades colors from one side (like the top) to the other (the bottom). The more exotic radial shades colors in concentric rings, starting from a single point.

2.    From the Direction list, choose an option.

The directions you can use depend on the type you chose in the previous step. If you're using a linear fill, you can choose whether the gradient starts at the top, bottom, left, or right. (For each option, a tiny thumbnail preview shows you what it looks like.) If you're using a linear fill, you can also set an angle in the Angle text box to tilt the gradient just a bit, so that it's not completely horizontal or vertical.

3.    In the Gradient Stops section, click the Add and Remove buttons until you have the right number of stops.

A gradient stop is a color in a gradient. In a simple gradient (like a blue-to-white shading), you have two colors, which means you have two stops, one for each color. If you have a more complex gradient (like a tacky yellow-green-red number), you need three gradient stops, one to put in each color. Of course, your gradient stops don't need to be different colors; you could use different tints or shades of the same color.

4.    Modify each gradient stop to suit.

Select a gradient stop from the list. When you pick one, you can adjust the color and the stop position. The stop position is a percentage that determines when the gradient switches from one color another. Say you create three gradient stops, with the colors yellow, green, and red. You may choose gradient stops of 0%, 50%, and 100% to space them out evenly. That way, the fill is yellow at the starting point (0%), green in the midpoint (50%), and red at the end (100%).

5.    Click Close.

To choose a texture for a fill, click "Picture or texture fill". You can then choose one of the ready-made textures from the drop-down texture list (Figure 5-15).

Further down the window, you see options that let you control exactly how Excel tiles your texture. You can size your texture to be larger or smaller by adjusting the scale percentages, and you can play with the offset settings to alter how the tiles of texture overlap. Finally, you can use the Mirror list to flip the texture around. But in truth, all these options are excessive frills, and you'll rarely need to touch any of them once you have a texture you like.

If the ready-made list of textures doesn't have what you want, you can choose your own texture from a picture you have in a separate file, which is the next section's topic.

Figure 5-15. When you choose a texture, you have choices like Woven Mat, Canvas, and Blue Tissue Paper. When you apply the texture, it actually looks like its name. Most textures are great for presentations, although potentially problematic for all but the most capable printers.

Note: You'll notice that the Fill section has a slider bar that you can use to set the degree of transparency you want. You can make a fill partially transparent so that other elements show through. You can see a chart background through a partially transparent chart column. However, transparency is difficult to get right, and it often makes ordinary charts harder to read. But if your boss is out of the office and you need to fill the next hour, go ahead and experiment!

Picture fills

A picture is a graphical image that goes behind your chart and stretches itself to fit. Excel doesn't provide any ready-made pictures. Instead, you'll need to browse to a graphics file on your computer (a .bmp, .jpg, or .gif file). This option works well if you need a themed chart—like a beach scene behind a chart about holiday travel choices. If you just want to add a company logo somewhere on your chart, you're better off using the drawing tools described in the next chapter to place the logo exactly where you want it. 

To use a picture fill, choose the "Picture or texture fill" option, and then click the File button to browse for the picture you want to use. Once you've picked the right picture, you can adjust the other options in the Fill section. Start by making sure the "Tile picture as texture" checkbox isn't selected—if it is, Excel tiles your picture just like the textures you saw in the previous section. (On the other hand, if that's the effect you're looking for, click away.)

Ordinarily, Excel stretches a picture over the surface of the chart. However, if you want your picture to fill just a part of the chart, you can adjust the different offset percentages (Top, Left, Right, and Bottom).

Note: If you just want to add an unstretched image or two somewhere on your chart, you shouldn't use a picture fill. Instead, add a picture object, as described in the next chapter.

C. FANCY BORDERS AND LINES

Now that you've tweaked the background fill to be slick and sophisticated (or wild and crazy), you're ready to modify other details. Along with the fill, the border is the next most commonly modified detail. You can add a border around any chart element, and your border can sport a variety of colors, line thicknesses, and line styles (like dashed, dotted, double, and so on).

To set a line from a format window (like Format Plot Area), follow these steps:

1.    Choose the Border Color or Line Color section.

Both these sections are basically the same. The difference is that the name changes depending on the chart element you're tweaking. Some chart elements (like the plot area) can have a border, so they have a Border Color section. Other elements (like the gridlines) contain lines, so they have a Line Color section.

2.    Choose the type of line you want. Usually, it's "Solid line".

Obviously, "No line" removes the line altogether. Your only other option is "Gradient line", which lets you create a line that's painted with a gradient fill of colors (page 518). The reason this odd feature exists may never be entirely clear.

3.    Choose the Border Styles or Line Style section.

Here's where you really get the chance to customize your border or line. Once again, some chart elements get a Border Styles section, while others have an almost identical Line Style section.

4.    Set the line settings, and then click Close when you're finished.

Figure 5-16 shows your options.

Figure 5-16. Using the Border Styles section, you can set a width (the thickness of the line), choose a fancy double or triple-edged border (from the "Compound type" list), choose between solid and different types of dotted or dashed lines (from the "Dash type" list), and even set what the line corners look like (the "Join type") and what the line ends look like (the "Cap type"). The "Cap type" doesn't have an effect on borders unless you're using dashed lines, because that's the only situation where the line is broken, and so has visible ends.

Note: Most of the time, you probably won't bother putting borders around chart elements. However, you can use the options in the Line Style section to configure the gridlines that appear behind your chart data. Just select the gridlines (you can use the Chart Tools | Format?Current Selection list if you're having trouble clicking the right spot), and then choose Chart Tools | Format?Current Selection?Format Selection. You'll see the familiar Border Color and Border Styles sections that let you change the line color, thickness, and dash style.

Power Users’ Clinic: Using Shadows

The various formatting windows have another option. You can choose the Shadow section to apply a shadow effect to a title, data series, or some other part of a chart. (A shadow, of course, is a faint shaded region behind a graphical shape or piece of text. Usually, you use a shadow to make something look more three-dimensional, so it seems to float above the rest of the background.) And although the idea sounds a little strange, a soft shadow can make an ordinary chart seem more professional (Figure 5-17).

To apply a shadow, choose the Shadow section, and then, in the Presets list, choose one of the ready-made options. This list includes thumbnails for a wide range of different shadow effects with different degrees of fuzziness and shadow placement. If you're a hard-core artist, you can choose a different shadow color, and you can tweak all the other aspects of your shadow using the sliders that control transparency, shadow size, blurriness, distance from the shadowed chart element, and so on.

Figure 5-17. Here, a shadow makes this line stand out from the chart background. Shadows work best with simple charts, like this one. If you had several different series on the same chart and gave each line its own shadow, the effect would be dizzying.

D. FORMATTING DATA SERIES AND DATA POINTS

Adding labels is one way to distinguish important points on your chart. You can also use color, borders, and patterns. These techniques can't provide any additional information (like the value of the data point), but they're a great way to emphasize important information without cluttering up your chart. (Figure 5-18 shows two examples.)

Figure 5-18. These figures show two examples of formatting at work. Top: Here's a line chart where the two lines are carefully distinguished from one another with different shaped markers and line styles. To get this effect, format each series separately. Bottom: Here's a column chart where a few columns are emphasized with a gradient fill color. For this result, format data points individually.



You could have several reasons for formatting a data series or data point:

•    You want to draw attention to specific data.

•    You want to make sure that you distinguish between different series.

•    You want to make sure your printout's legible, and that you can identify all the important information, even if your printout's black and white.

You already know the basic steps to format a data series because they're almost identical to the steps you use to format other parts of the chart. Start by selecting the area you want:

•    If you select a data series, your changes affect all the data points in that series. Usually, you'll use this approach to help distinguish between different series.

•    If you select a single data point (using the two-click technique explained in Figure 5-9), you affect only that one data point. Usually, you'll use this approach to highlight important values.

Tip: Your use of color and fills is limited only by your imagination, but excessive formatting can be distracting, so it's best to add extra flourishes only when they help you make a point. You could use different colors in a bar chart to help highlight the meaning of the results on a company's annual sales chart. Red-colored bars could represent losses, while black bars could show profits.

Then, use the familiar Chart Tools | Format?Current Selection?Format Selection command. You'll see a Format Data Series or Format Data Point dialog box that looks a lot like the Format Plot Area dialog box you started out with on page 5-16. Now, though, the formatting window includes a Format Series section where you can adjust some additional chart-specific details. You'll explore most of these options as you build better charts through the rest of this chapter.

Note: Use the Format Data Series or Format Data Point dialog box to implement any of these ideas.

Here are some formatting ideas:

•    If you're using a column, bar, or area chart, you can adjust the fill colors and even use gradients and textures to make different series stand out. Go straight to the Fill section.

•    If you're using a column or bar chart, you can adjust the width of the bars and the spacing in between. (In the hands of a crafty charter, this technique lets you build an overlay chart, as described on page 5-41.)

•    If you're using a line or XY scatter chart, you can use the Line Color and Line Style section to change what the line looks like for each series.

•    If you're using a line or XY scatter chart, you can use the Marker Options (Figure 5-19) and Marker Fill sections to choose the style (square, triangle, cross, and so on) and size of each data point that's placed on the line.

Tip: If you format a data point, and then format the series that contains that data point, the new formatting for the series takes over. Therefore, you need to reapply your data point formatting if you want a specific value to stand out from the crowd. To save time, you can use the helpful Redo feature to apply changes over and over again. First, format a data point the way you want it. Then, select a second data point, and press Ctrl+Y to reapply your formatting to the new data point. This technique can save you loads of time.

Figure 5-19. It rarely makes sense to format an individual data point in a line or scatter chart. Instead, you'll usually format the whole series so that different lines stand out from one another. If you have more than two or three lines, you may need to get creative with different line styles and marker shapes to make sure that you really can tell them apart at a glance.

E. REUSING YOUR FAVORITE CHARTS WITH TEMPLATES

You can put a lot of work into creating the perfect chart. After you've slaved over your creation, it would be nice to have a way to reuse the formatting again in another workbook. Fortunately, Excel makes it possible through a template feature that lets you store your chart settings. Each chart template stores all the chart-formatting settings you've made, but none of the data.

Here's how it works. Once you've finished polishing up your chart, complete with all the formatting choices, choose Chart Tools | Design?Type?Save Template. Excel then prompts you to save a chart template file (which it identifies with the .crtx file extension). Choose a descriptive file name, like "Psychedelic Pie Chart," and then click Save.

By default, Excel offers to save the chart template in a Chart subfolder inside your personal template folder (page 3-11). Don't change this folder—the Chart Template folder's the only place Excel looks for templates, so if you place it somewhere else, you can't reuse it. (Of course, nothing's stopping you from copying the chart template file, perhaps to get it into the Charts folder on someone else's computer so they can benefit from all your hard work.)

To reuse your chart template, you need to pick it from the Create Chart or Change Chart Type dialog box. To create a new chart using your template, head to the ribbon's Insert?Charts section, and then click the dialog launcher (the icon of a square with an arrow in it) in the bottom-right corner. This shows the Create Chart dialog box, giving you a full selection of chart types. If you pick the Templates section at the top, you see all the chart templates that are stored in the Templates folder, complete with a mini preview (Figure 5-20).

Figure 5-20. This computer has two custom chart templates to choose from. You can hover over either one to see the descriptive file name (minus the .crtx file extension).

When you select your template, and then click OK, Excel creates a new chart with the same formatting but using the data that's selected on your worksheet. Obviously, these options may not all apply to a new chart you create based on your template. Maybe your template includes formatting information for four series, but your new chart has only three. In this case, Excel just ignores any formatting information it isn't using.

Note: The formatting in the chart template is just a starting point. If you want to reuse some of the formatting but not all of it, you're free to use any of the formatting techniques you learned about in this chapter to further refine your new chart.

V. Improving Your Charts

So far, you've learned the key techniques you need to make sure your charts tell the right story. However, Excel lets you do plenty more, including adding trendlines, data tables, and error bars, and tweaking 3-D perspective and shapes. In the following sections, you'll learn even more about how to make the perfect chart.

A. CONTROLLING A CHART'S SCALE

Many people don't think twice about the scale they use when they create a chart— instead, they let Excel set it automatically based on the values their chart has been built from. There's nothing wrong with this laissez-faire approach, but if you know how to take control of your chart's scale, you can make important data stand out and make it easier for people looking at your chart to spot relative differences in data and understand overall trends.

Usually, you'll be most interested in changing the scale of the value axis that runs on the left side of most charts. You can modify the scale of the value axis on most charts, including column charts, line charts, scatter charts, and area charts. (In a bar chart, the value axis actually runs horizontally along the bottom of the chart, although you can modify the scale in the same way as you do with these other chart types.) Pie and donut charts don't show a value scale at all.

Note: It's worth noting that quite a few unsavory individuals try to skew charts with crafty scale tricks. People often show two similar charts next to each other (for example, sales in 2006 and sales in 2008), and use a smaller scale in the second one to make it look like nothing's changed. Once you finish this section, you'll have a good idea how to spot these frauds. Some companies even have policies that enforce strict scale usage!

To change the scale, right-click the value axis, and then choose Format Axis. Or, if you find it hard to select the part of the chart you want, choose the value axis from the list in the ribbon's Chart Tools | Format?Current Selection section. Then, choose Chart Tools

| Format?Current Selection?Format Selection.

When the Format Axis dialog box appears, choose the Axis Options section (shown in Figure 5-21). You have the choice of letting Excel automatically set the scale based on your data, entering the values you think are appropriate.

Note: When you set a scale value to Auto, Excel calculates it based on the current chart size and your current data. If you add more data, change the data values, or resize the chart (in which case there's more room to show intermediate values on the axis), Excel may modify the scale. But when you use Fixed, your numbers are hard-wired into the chart, and Excel never changes them (although you may, later).

Figure 5-21. The Format Axis dialog box lets you change the scale used on your chart. There's an "Auto" option next to each value. If selected (as in this example), Excel chooses the scale value (and the value it chooses appears in the text box on the right). In this example, the scale currently stretches from 0 to 180,000, with a major tick mark shown every 20,000 units. If you want to take control of the scale yourself, click Fixed instead of Auto (next to the value you want to change), and then edit the number in the corresponding text box.

Several settings determine the scale of your chart. These settings include:

•    Minimum and Maximum. These values set the range of your scale. The axis starts at the minimum value (at the bottom of your chart), and ends at the maximum (at the top). Usually, Excel sets these values so that the minimum is 0 and the maximum is just a little bit above your largest data point. However, if your data points are very large and have only minor differences, you'll be able to help your chart's viewers make comparisons more easily if you compress the scale by having it start at a higher minimum value.

•    Major unit. The major unit setting determines how many units the scale is divided into. If you have a scale from 0 to 1,000 and a major unit of 100, gridlines and axis labels appear every 100 units. Altogether, that makes for 11 labels.

•    Minor unit. The minor unit setting determines how many tick marks are on the scale. (Tick marks are tiny lines added to the axis to help you judge the scale.) Usually the minor unit is less than the major unit. You may use a major unit of 100 and a minor unit of 10 in a chart that stretches from 0 to 1,000 (which generates 11 gridlines and 10 tick marks between each gridline). Five to ten major units on each axis, and five to ten minor units for every major unit, usually

make for a readable chart.

Note: When you first create a standard chart, minor tick marks are turned off, so the minor unit setting doesn't have any effect. To set whether Excel shows major and minor tick marks, choose an option from the "Major tick mark type" and "Minor tick mark type" lists. Anything other than None does the trick. (The various options just determine exactly what the tick marks look like—for example, whether they're on the inside of the grid, the outside, or if they cross completely.)

Along with the settings just listed, you may also want to tweak the "Horizontal axis crosses" setting at the bottom of the dialog box. This number controls where the category axis line crosses the value axis. Usually, this line's right at the bottom of the chart, at the minimum value. However, you have two other choices. You can choose "Maximum axis value" to place the category axis at the top of the chart. The scale remains the same (meaning the minimum value's still at the bottom of the chart and the maximum value's at the top).

More interestingly, you can choose "Axis value," and then type in the exact value where the category axis should appear. This choice lets you put the axis somewhere in the middle of your chart. For example, you may want to plot a chart of test scores and draw the axis at a point that would indicate the minimum passing mark. Note that in a column chart, when a column has a value that's less than the axis, it points "downward," as you can see in Figure 5-22 (bottom).

Using these basic ingredients, you have a good deal of control over your chart's appearance. Figure 5-22 compares a few options that show how different scale choices can transform a chart, with the help of a little formatting.

The Format Axis dialog box also provides a few specialized options that aren't as commonly used but are still quite interesting. They include:

•    Values in reverse order. This option turns your chart upside down. It places the category axis at the top of the chart (instead of the bottom), and changes the value scale so that it increases as it stretches down the side of the chart.

•    Logarithmic scale. A logarithmic scale doesn't increase gradually. Instead, every major unit represents an increase by a power of 10. The values 0, 10, 20, 30, 40, 50 make up a typical scale. The values 0, 10, 100, 1000, 10000 make up a logarithmic scale. Logarithmic scales lend themselves to scientific and mathematical applications to help you see certain types of relationships and patterns in your data.

•    Display units. You can use this option to shorten the text labels on your axis. This option's particularly useful if you have large dollar amounts. Imagine that your value axis stretches from $100,000 to $300,000. If you choose Thousands as your display unit, Excel adds the label Thousands along the value axis and changes the scale values to three digit numbers like $100 and $300. The bottom example in Figure 5-22 shows this space-saving trick at work.

Figure 5-22. These charts show the same data prepared three different ways. Top: Here's a column chart the way Excel creates it, with the addition of minor tick marks in the value axis. This chart's problem is that the difference between the column values doesn't come across as very substantial—but, in fact, it is. The middle and bottom examples solve this problem by setting the minimum value of the axis to $100,000, which shrinks the scale and emphasizes the differences between the columns' values. Middle: This chart also includes data labels that show each column's value. Bottom: This chart's data labels have been removed from the X-axis, and category labels have been added to the whole data series so the region's name shows up just below the bar. This example is further enhanced with gradient fills, dotted gridlines, and a vertical scale set to use thousands.

Note: If you're using a numeric or date-based category axis, you can format the scale of the category axis in the same way you format the scale of the value axis. You may want this option when you're creating an XY scatter chart or a line chart. If your category axis just displays labels, you can still format it, but you have fewer options. You can't change the scale, but you can reverse the order of categories, add tick marks, hide labels, and format the axis line's look.

B. ADDING A TRENDLINE

One of the main reasons that people create charts is to reveal patterns hidden inside the data. A gift card company may look at a historical record of sales to make an educated guess about the upcoming holiday season. Or a researcher might look at a set of scientific data to find out if potatoes really can cure the common cold. In both these examples, what's most important is spotting the trends that lurk inside most data collections.

One of the easiest ways to spot a trend is to add a trendline to your chart. A trendline's similar to an ordinary line in a line chart that connects all the data points in a series. The difference is that a trendline assumes the data isn't distributed in a perfectly uniform pattern. Instead of exactly connecting every point in a series, a trendline shows a line that best represents all the data on the graph, which means that minor exceptions, experimental error, and ordinary variances don't distract Excel from finding the overall pattern. Figure 5-23 shows an example.

Figure 5-23. This example shows the comparison between age and reaction time from the last chapter (Figure 4-21). The difference here is that the trendline has been extended beyond the actual data to predict results for people who are older than 80.

The other important point about trendlines is that they can predict values you don't have. The gift card company can use a trendline to get a good estimate of future sales, while a scientific experimenter can make educated guesses about data that wasn't recorded.

People most often use trendlines in XY scatter charts. Trendlines also makes sense in a column chart, and they can work in line, bar, and area charts in specialized circumstances.

To add a trendline, follow these steps:

1.    Select your chart.

If you're using an XY scatter chart (the most common choice), make sure you're using a subtype that doesn't draw lines. Otherwise, the trendline and the chart line may cause confusion. To change the subtype of a chart, right-click the chart, and then choose Change Chart Type.

2.    If your chart has more than one series, choose the series you want to use to create the trendline.

If your chart just has a single series, then you don't need to go to this trouble—just select the entire chart.

3.    Choose the type of relationship that you expect to find in your data from the Chart Tools | Layout?Analysis?Trendline list. To get even more types, choose Chart Tools | Layout?Analysis?More Trendline Options.

You can choose one of several types of trendlines:

•    A Linear trend varies regularly and is the simplest possible relationship. If you're driving at a constant speed, the distance traveled increases linearly as the time increases.

•    A Power and Exponential trend starts off slowly and quickly ramps up. Logarithmic is the inverse—it starts off rapidly, and then levels out. These types of trends are more complex, but they're more common than linear trends in natural phenomena. Two example exponential relationships are the change in a population size with successive generations, and the distance traveled in a car if you continuously accelerate.

•    A Linear Forecast trend is similar to a Linear trend, but it extends a little bit further (the equivalent of two data points) to make a guess at where this relationship is going.

•    A Polynomial trend attempts to fit the data by creating an equation with a combination of different terms. This trend is the best choice if multiple factors are involved and your data doesn't fit a smooth line or curve.

If there's more than one series in your chart, Excel asks you which one you want to use for the trendline. (You can repeat this process to add a trendline for each series.)

After you add a trendline, you can right-click it, and then choose Format Trendline to change its line color, thickness, and style.

A standard trendline fits the data you have. However, you can also extend a trendline forward or backward to fill in values you don't know. This process of estimating data that you don't have (based on data that you do have) is extrapolation. A closely related concept is interpolation, which estimates unknown data values between known existing values. If a gift card company has information about sales from 2007 to 2010, you'd use extrapolation to predict sales in 2011. To make an educated guess at what sales were like in March 2008 (a month in which your firm lost its sales data), you'd use interpolation. All trendlines necessarily use interpolation, since there's always, in effect, "missing" data points between the data points you provide.

To extrapolate values in a trend, choose Chart Tools | Layout? Analysis? Trendline?More Trendline Options (if you're creating a trendline), or right-click the trendline, and then choose Format Trendline (if you want to extend the line after you've created it). Look for the Forecast box, and then specify the number of units that you want to add going forward or backward. Figure 5-23 shows an example.

Note: Don't always trust trendlines. It's quite possible that a relationship holds true only over a limited set of values. If you use a rising sales trendline as the basis for guessing future results, Excel's guesses don't, of course, take into account unexpected developments, like limited inventory or rising production costs. Similarly, if you extend the age versus reaction time comparison in Figure 5-23 too far, you'll wind up with ages and values that don't make sense (like a reaction time of 0 seconds, or a reaction time for a 300-year-old).

C. ADDING ERROR BARS TO SCIENTIFIC DATA

In a typical scientific experiment, you have two important sets of information: the actual results and an estimate that indicates how reliable these results are. This "reliability" number is the uncertainty. The uncertainty doesn't compensate for human error, faulty equipment, or invalid assumptions. Instead, it accounts for the limited accuracy of measurements. Think of the typical bathroom scale, which can give you your weight only to the nearest pound. That means there's an uncertainty of 0.5 pounds because any given measurement could be off by that amount. If a scientific experimenter weighs in at 150 pounds, he would record that measurement as 150±0.5 (150 pounds plus or minus 0.5 pounds). Any other calculations based on weight need to take this potential inaccuracy into account.

Because every type of measurement has a different range of accuracy, there's always a certain degree of imprecision that you need to watch out for before you make a dramatic conclusion. In a scientific chart, you can indicate the uncertainty using error bars, as in Figure 5-25. If you plot 150±0.5 on a chart, you should end up with a point at 150 and an error bar that stretches from the point up to 150.5 and down to 149.5.

To add scientific error bars to a chart, follow these steps:

1.    Click to select the appropriate data series on the chart.

If you have more than one data series, each series can have its own error bar settings.

2.    Make a selection from the Chart Tools | Layout?Analysis?Error Bars list.

You can use a standard choice to apply an automatic error bar setting (like one that assumes a five-percent error). If you find a good fit, your job is finished. But usually you'll need to choose exactly what you want by picking Chart Tools | Layout?Analysis?More Error Bars Options. Doing so shows the Format Error Bars dialog box (Figure 5-24).

3.    Choose how large your error bars should be.

Two simple and useful choices are "Fixed value" (which lets you specify the same measurement of uncertainty for every value), and Percentage (which lets you specify a percentage uncertainty). You can also supply a different fixed uncertainty for each value. In this case, add a new column with this information in your worksheet, choose the Custom option, and then click Specify Value to specify the range of cells with the uncertainty information (you need one uncertainty value for each data point). Statistics fans can also use two more-advanced options, Standard deviation and Standard error. For definitions of these two weighty concepts, consult your favorite statistics textbook.

Figure 5-24. You can set error bars to use a fixed value (as in this example), a fixed percentage of the value, or you can specify a range of cells in the worksheet that contains the uncertainty value for each measurement.

4.    Click Close to add error bars to your chart.

Figure 5-25 shows a chart with error bars added.

Figure 5-25. This graph attempts to show a link between age and reaction time, with the error bars indicating that the reaction time couldn't be measured precisely. The error bars tell us that even though the trendline doesn't fit the data exactly, it lies within the range of measurement error. In this example, you can't distinguish all of the error bars, because sometimes the points and error bars overlap (consider the two data points around age 40). On the other hand, it's easy to see the single data point at age 50 and the error bars above and below it.

D. FORMATTING 3-D CHARTS

As you learned in Chapter 4, many charts provide subtypes drawn in three dimensions. Some 3-D chart types are no different from their plainer 2-D relatives. In these charts, a 3-D effect simply gives the chart a more interesting appearance. But in true 3-D charts, it adds information by layering data from the front of the chart to the back, with each series appearing behind the other. (The column chart has seven subtypes. The first three are ordinary 2-D charts, the second three use a 3-D effect, and the last one's the only true 3D chart. For more details about what subtype each chart supports, see the “Chart Types” section that starts on page 4-23.)

In true 3-D charts, you may need to take special care to make sure that data in the background doesn't become obstructed. A few tricks can help, like reordering the series and simplifying the chart so it isn't cluttered with extraneous data. You may also want to rotate or tilt the chart so that you have a different vantage point on the data.

To rotate or tilt a chart, follow these steps:

1.    Right-click the chart, and then select 3-D Rotation.

The Format Chart Area dialog box appears with the 3-D Rotation section selected, as shown in Figure 5-26.

Figure 5-26. The 3-D Rotation section of the Format Chart Area dialog box lets you twist and turn your 3-D chart. As you make your changes, Excel updates the chart on your worksheet to give you a preview of what your settings will look like.

2.    Use the arrow buttons to tilt the chart in various ways, changing the rotation and perspective.

You can also edit the individual settings by hand:

•    The X value lets you turn the chart from side to side.

•    The Y value lets you tilt the chart up and down.

•    The Perspective value lets you make the chart seem closer or farther away.

You also have a few more-specialized settings you can use to fine-tune your chart:

•    Turn on the "Right angle axes" checkbox if you're tired of turning your chart this way and that, trying to find a good vantage point. When you switch this setting on, the Perspective value has no effect. Instead, Excel "straightens" out the chart by squaring it up, face-forward.

•    Turn off the Autoscale checkbox if you want to set the height of the chart using the Height box. The Height box specifies a percentage that's compared against the length of the bottom of the chart (the X-axis). In other words, a Height of 100 percent means the chart will be just as high as the base is wide. A height of 200 percent means the chart will be twice as high as it is wide. Keep in mind that Excel compares the height against the width of the base, not the width of the entire chart. If you rotate the chart, the perspective makes it appear as though the base isn't as wide as it really is. In this situation, Excel still makes the chart just as tall as if you hadn't rotated it.

•    Use the Depth box to set how deep the chart is (from front to back). As with the Height box, you use a percentage that's compared to the base of the chart. That means a depth of 200 percent makes a chart twice as deep as the base is wide. Sometimes, a deeper chart creates a more dramatic 3-D effect.

•    Click the Default Rotation button to return your chart to the just-slightly tilted way it began life.

3.    Click Close when you're finished.

E. CHANGING THE SHAPE OF A 3-D COLUMN

Excel provides several subtypes of column and bar charts that use a 3-D effect. Excel also provides the Cylinder, Cone, and Pyramid chart types, which are always threedimensional (see the description of chart types, beginning in “Column,” for more information). It doesn't really make much difference whether you use columns, pyramids, or cones—the overall effect is pretty much the same. However, you create a much more dramatic effect by putting more than one shape in a single chart. If you want to compare two series, then you could represent one with columns and the other with cones, as shown in Figure 5-27.

Figure 5-27. To make a dramatic point, you can combine more than one shape in a chart. In this example, cylinders represent the cat population and pyramids represent the corresponding dog figures. Creating this chart is refreshingly easy—you just need to change the standard choice in the Shape tab of the Format Data Series dialog box.

To try this out, follow these steps:

1.    Create a column chart with two or more data series.

Make sure you pick one of the chart subtypes that use a 3-D effect.

2.    Right-click one of the data series, and then choose Format Data Series.

The Format Data Series dialog box launches.

3.    Choose the Shape section. Click one of the pictures to change your columns into cylinders, cones, pyramids, or something else. Then click Close.

Repeat steps 2 and 3 for any other data series that you want to change.

VI. Advanced Charting

At this point, you've seen a wide range of tips and tricks for improving almost any Excel chart. But it's worth learning a few advanced charting techniques. Most of these work only with specific chart types, like exploding pie slices and overlaying bar charts. You'll also learn about one of the most interesting chart tricks known to Excel-kind: using a combination chart to fuse together two different chart types into one object.

A. EXPLODING SLICES IN A PIE

Data labeling and formatting help make individual slices of a pie chart stand out. However, to really accentuate important information in a pie or donut chart, you can separate a piece from the rest of the pie by exploding the slice (to use the rather dramatic technical term). You've already seen this feature at work with the pie or donut chart subtypes that explode all the pieces. This technique shows you how to explode just a single slice, as shown in Figure 5-28.

Figure 5-28. This example shows a pie with one exploded piece, representing the sales in the Eastern division.

To explode only one slice of a pie chart, just click the slice, and then drag it away from the pie. You can pull it as far away as you want, and you can repeat this process to explode several pieces (although the emphasis works best when you remove just a single slice). Be careful that you don't select the whole data series before you start dragging, or you'll end up exploding the whole pie.

Tip: You can also help separate your slices by adding a thin white border around each slice. Right-click any slice, and then choose Format Data Series. Then, in the Border Color section, choose to create a solid white line. You can use the options in the Border Styles section to make the line thicker so the slices stand out more.

B. GROUPING SLICES IN A PIE

An even more interesting pie chart feature breaks down a single slice into more detail. You may want to create a pie chart that shows your personal budget by category (including food, living expenses, clothes, and so on). You could then further subdivide a single slice, like food, to show what portion goes to groceries, restaurants, the local hot dog vendor, and ice cream splurges. Adding all this information into a single pie would result in too many slices, making the chart less effective.

Creating a pie chart with a single slice breakdown is pretty easy, but it's not the most intuitive operation in the world. First, you need to set up your data correctly. Your table needs to include all the information you're putting into the chart in a single series.

Imagine you want to create a pie with these slices:

Food

$13,911

House

$18,342

Clothes

$4,790

Fun

$7,980

Computer

$34,010

Now, assume that the slice you want to subdivide is the Computer slice. That means you need to remove the Computer entry and replace this information in the chart with more detailed information. Here's what you might end up with:

Food

$13,911

House

$18,342

Clothes

$4,790

Fun

$7,980

Computer Software

$7,500

Computer Hardware

$6,500

Missing Manual Books

$20,010

Notice that the Computer Software, Computer Hardware, and Missing Manual Books entries add up to the whole computer slice ($34,010), which has been removed from the chart.

Note: The only limitation to breaking down an individual slice is that you can perform this trick on only one slice.

Now that you've organized your data in the right way, create a new pie chart, and choose either the "Pie of Pie" or "Bar of Pie" subtypes. Excel automatically chooses some slices from the bottom of your chart to group in a separate pie or bar chart. Unless Excel makes a lucky guess, this selection doesn't include the same slices that you want. Fortunately, it's easy to change the separation just by dragging.

If you have a "Bar of Pie" chart, you can add a slice to the bar by dragging it from the main pie to the column. If you want to take a slice that's in the column and put it back in the pie, drag it from the column back to the main pie. Continue this process until you've grouped the Computer Software, Computer Hardware, and Missing Manual Books slices into the standalone bar.

Figure 5-29 shows a perfected "Pie of Pie" chart.

Figure 5-29. This example shows a pie chart of expenses, with the Other slice broken down further into a second pie chart. Excel pie charts often need a fair bit of tweaking before they look respectable. Usually, you'll want to remove the legend and use category labels, as shown here. If you drag the label a short distance away from the midpoint of the slice, Excel adds a line pointing from the label to the slice. These lines often help to make a pie chart more readable.

C. GAPS, WIDTHS, AND OVERLAYS IN A COLUMN CHART

Column and bar charts have their own options for fine-tuning. You can adjust how far each column group's spaced from the next and how much space appears between each column in a group. With a little imagination, you can use these settings to create an innovative overlay chart, which layers two different sets of data on top of each other.

To see these extended column chart settings, right-click a data series in the chart, and then select Format Data Series. Then, select Series Options, which is the first section in the list. You get two settings:

•    Series Overlap is the amount of overlap between columns in the same category. This setting takes effect only if you have more than one series. The standard option is 0, which means that clustered columns touch each other but don't overlap. With a value of 100, the columns in the same category overlap completely, while negative values put a space between the columns.

•    Gap Width is the space between each category. In a chart with one series, this setting is the space between each column; in a chart with multiple series, it's the space between each group of columns. The standard value is 150, which leaves a space that's roughly equal to the width of 1.5 columns.

Note: In a 3-D column chart, you'll find an extra setting: Gap Depth. This is the space between the columns at the front and the columns that appear behind them.

One interesting trick is to use the overlap setting to compare two sets of data. People often use this technique to compare projections against actual results. Here are the steps you need to follow:

1.    Create a bar or column chart with two series.

Figure 5-30 shows one possible example of a bar chart that compares projected sales against actual sales.

2.    Decide which series should be on top of the other.

The series that's on top needs to be transparent, so the other series can show through. In Figure 5-30, the projected value should be placed on top.

3.    Find this topmost series in the chart. Right-click it, and then choose Format Data Series.

The Format Data Series dialog box launches.

4.    Select the Series Options section, and then set the Series Overlap to 100.

This setting ensures that two series are layered one on top of the other.

5.    Select the Fill section, and then choose "No fill".

This tells Excel to make this data series transparent, with only the border visible.

6.    Click Close.

Next, you want to make sure the right series is on top.

7.    Right-click the chart, and then choose Select Data. Make sure the series that's supposed to be on top is at the bottom of the list.

To move a series, select it, and then click the down arrow button. Excel draws the series from top to bottom in this list, so the one it draws last ends up in front.

8.    Now select the other series, right-click it, and then choose Format Data Series. Select the Border Color section, and then choose "No line".

You can also choose any fill color or fill effect. You may need to try several combinations in order to have enough contrast for the overlay chart to really work.

9.    Click Close.

Figure 5-30. This example shows an overlay bar chart. The projected sales values have dotted borders and a transparent fill. The actual sales numbers show a gradient fill. Using this model, you can clearly see that the Southern and Northern locations exceeded their expectations, while the Western region fell far short of its predictions, even though it had the second-best overall sales.

D. CREATING COMBINATION CHARTS

Sometimes, you want to use a chart to compare trends in different but related data. Imagine you create two charts, one to show how many hours you've worked in the last few months, and the other to show how much money you've spent. After you create these two charts, you start to wonder if there's a link—in other words, do you spend more money when you have a greater workload? Unfortunately, because these two measurements use different units (one records the number of hours, while the other counts the number of dollars), you can't put them on the same chart—or can you?

Combination charts are a well-kept Excel secret. Using combination charts, you can compare trends across different sets of data, even if the units are wildly different. Combination charts are also useful when you need to compare more than one piece of information to tell the complete story. A chart that shows quarterly sales could make it look like your company is meeting wild success. But using a combination chart, you can contrast the sales against another factor (like changing currency exchange rates, or increased business expenses brought about by an irrational 80-percent-off coupon campaign) to tell a different tale.

Tip: There's a screencast—an animated online tutorial—that shows the process of creating a combination chart, online at It was created using Excel 2007, but the process is the same for Excel 2010. Surf to the Missing CD page for Excel 2007: The Missing Manual to check it out.

You make a combination chart by creating a new secondary value axis. This axis appears on the right side of the chart. In other words, you'll end up with two value axes, one that applies to the first series of data, and the other that applies to the second series. Figure 5-31 shows how this process works.

Figure 5-31. These charts both compare the number of customer inquiries with the amount of sales made in each month. Top: Both lines use the same scale, which actually doesn't make much sense because the units tracked in each data series are different. Bottom: But once a secondary axis is added at the right side, the numbers are a bit easier to compare. For an even better combo chart solution, see Figure 5-32.

To create a combination chart, follow these steps:

1.    Create a chart that includes both sets of data.

Initially, the smaller set of data doesn't appear correctly, because the scale is wrong. To solve this problem, you need to plot this data against another axis.

2.    Right-click the data series that doesn't fit, and then choose Format Data Series.

If the scale difference is really extreme, you may have trouble selecting the data series you want to change, because it may be squashed against the bottom chart axis. In this case, select the series by name from the Chart Tools | Format?Current Selection list.

3.    Select the Series Option tab, choose Secondary Axis under the heading Plot Series On, and then click Close.

Excel creates a new scale on the right side of your chart and uses this scale to plot the data series. Excel automatically chooses the best scale, although you're free to change it by formatting the axis.

Combination charts don't just let you compare different units of data. They also help you fuse together two different types of charts. You could plot one series using a scatter chart, and then add columns to represent the values in the second series. When done right, this combo produces an attractive chart with a series of columns and a line above them, which you see commonly in shareholder reports and marketing documents.

Tip: Some chart types just don't mix. To mix properly, the arrangement of axes should be the same in both chart types. Thus, a combination of a column chart and line chart works great, but a combined line chart and pie chart doesn't make much sense.

To create a combination chart that uses more than one chart type, right-click the series, and then choose Change Series Chart Type. You can now choose a new chart type and subtype, as usual. The difference is that this change is applied only to the series that you've selected. Figure 5-32 shows a good example.

Figure 5-32. This example compares the inquiry and sales numbers using two different chart types. The inquiry numbers are depicted as columns, and the sales figures use a line chart. This technique is a great way to highlight the fact that you're comparing different types of data.

CHAPTER 5 – REVIEW QUESTIONS

The following questions are designed to ensure that you have a complete understanding of the information presented in the chapter. They do not need to be submitted in order to receive CPE credit. They are included as an additional tool to enhance your learning experience.

We recommend that you answer each review question and then compare your response to the suggested solution before answering the final exam questions related to this chapter.

1.    Which of the following settings is not predetermined in a chart style:

a)    background color

b)    shape effects

c)    chart title fonts

d)    marker styles

2.    Which of the following is a single value in a data series:

a)    legend

b)    plot

c)    gridline

d)    data point

3.    If you don’t want to use a legend on your chart because it is distracting, what could you use instead:

a)    title

b)    data label

c)    data series

d)    notation

4.    Which of the following labels does not work well with most charts because, if the chart contains more than one line, it will create duplicate labels:

a)    category name labels

b)    value labels

c)    percentage labels

d)    bubble size labels

5.    A texture fill has more photographic detail than an ordinary pattern fill.

a)    true

b)    false

6.    Since colors and fills are limited only by the imagination, it is best to use as much flare and color as possible when charting.

a)    true

b)    false

7.    Which chart scale setting determines the number of tick marks on the scale:

a)    minimum and maximum

b)    display units

c)    minor unit

d)    major unit

8.    The difference between a trendline and an ordinary line on a line chart is that the trendline assumes the data is not distributed in a perfectly uniform pattern.

a)    true

b)    false

9.    If you want to break down sections of a pie chart with grouping, how many slices of a single pie can you break down:

a)    1

b)    2

c)    5

d)    unlimited

10.  To make an effective combination chart, the arrangement of axes should be the same in both chart types.

a)    true

b)    false

CHAPTER 5 – SOLUTIONS AND SUGGESTED RESPONSES

1.    A: Incorrect.  Background color is set in the chart style.

B: Incorrect.  Shape effects, like curved and beveled edges, shadows, and glows are determined in the chart style.

C: Correct.  Chart styles do not change the font used for the chart title or any labels.

D: Incorrect.  Marker styles that distinguish the points in one series from another are determined in the chart style.

(See page 5-1 to 5-2 of the course material.)

2.    A: Incorrect.  The legend is the element that identifies each data series on the chart with a different color.

B: Incorrect.  The plot area is the chart’s background; the area where the gridlines are drawn.

C: Incorrect.  The gridlines are the lines that run across the plot area.

D: Correct.  The data point is a single value in a data series.  In a line chart, a data point is a single dot, and in a column chart, a data point is a single column.

(See page 5-5 of the course material.)

3.    A: Incorrect.  A title is one label you put on the entire chart or one you could add to an axis, but it would not take the place of a legend.

B: Correct.  Data labels are labels that are attached to every data point in a series.  This text floats just above every data point and clearly identifies each piece of information.  If you felt a legend was too distracting to embed in the chart, you could explain the necessary information on a data label instead.

C: Incorrect.  A data series is a single set of data plotted on a chart across the category axis.  It cannot be a replacement for a legend.

D: Incorrect.   A notation is not an element that exists in Excel.

(See page 5-7 of the course material.)

4.    A: Correct.  The category name label adds the information from the category axis.  However, this will create duplicate labels for every line on a chart which can crowd out important information.  Therefore, they do not work well with most charts, but they could be effective with a pie or donut chart.

B: Incorrect.  Value labels insert data that corresponds with a data point.  This is probably the most frequently used type of label.

C: Incorrect.  Percentage labels are similar to value labels, but they take the value and divide it to create a percentage.  They are only useful with pie and donut charts.

D: Incorrect.  Bubble size labels add the value that Excel used to calculate the bubble size next to each bubble.  They are only used with bubble charts.

(See page 5-9 of the course material.)

5.    A: True is correct.  A texture fill is a detailed pattern that is tiled over the entire chart element.  Texture fills have greater, more photographic detail than ordinary patterns that are typically simple lines and shading.

B: False is incorrect.  It is true that a texture fill has more detail and is more photographic than a simple pattern of lines and shadings.

(See page 5-19 of the course material.)

6.    A: True is incorrect.  Excessive formatting can be distracting.  While it is possible to add unlimited amounts of flare and color to a chart, it is best to just add flourishes when you want to make a point.

B: False is correct.  You can definitely overdo fills by adding too much color and flare in places where it is not appropriate.  This can be distracting and make a chart difficult to read. Although unlimited, fills should be used only when they are appropriate to help make the point you are trying to get across.

(See page 5-25 of the course material.)

7.    A: Incorrect.  The minimum and maximum values set the range for the scale.  The axis will start at the minimum value and end at the maximum value. This does not affect the number of tick marks on the axis.

B: Incorrect.  The display units setting affects the text label on the axis.  Excel can add a text label and then shorten the number of digits on each entry.  For example, you could use the display unit of “thousands,” and then the scale values could just be 1-5, instead of 1,000-5,000.

C: Correct.  The minor unit setting determines how many tick marks are on the scale.  These are the tiny lines on the axis that help you judge the scale.  The minor unit tick marks are within the major unit values.

D: Incorrect.  The major unit setting determines how many units the scale is divided into.  Then minor unit tick marks can be added within those values.

(See page 5-30 of the course material.)

8.    A: True is Correct.  A trendline is the easiest way to spot a trend in specified data.  It is similar to an ordinary line on a line chart, but it assumes the data is not distributed in a perfectly uniform pattern. Rather than exactly connecting every point, it shows a line that best represents the data.

B: False is Incorrect.  A typical line on a line chart connects all the points in a series.  A trendline does not try to connect each point exactly, but instead accounts for minor exceptions, experimental error, and ordinary variances and displays a line that best represents the data on the graph.

(See page 5-32 of the course material.)    

9.    A: Correct.  Grouping allows you to break down a single slice of a pie into more detail, but you are limited to only one slice of a pie when you do this trick.

B: Incorrect.  Breaking down a single slice of a pie to give more detail can be an effective tool, but you can only perform this break down on one slice in a pie, not 2 slices.

C: Incorrect.  Breaking down a single slice of a pie to give more detail can be an effective tool, but you can only perform this break down on one slice in a pie, not 5 slices.

D: Incorrect.  Breaking down a single slice of a pie to give more detail can be an effective tool, but you can only perform this break down on one slice in a pie.

(See page 5-41 of the course material.)

10.  A: True is correct.  Some chart types just don’t mix.  In order to mix properly, the arrangement of axes should be the same in both chart types.  Thus, the combination of a column chart and line chart works great, but you would not want to combine a pie chart and a line chart.

B: False is incorrect.  Combining different chart types will not make much sense.  To make an effective chart combination, you will want to be sure the axes are the same, such as a line chart with a bar chart.

(See page 5-46 of the course material.)


Chapter 6: Inserting Graphics

Most Excel fans concentrate on numbers, formulas, and charts when they create worksheets. But Excel has another dimension—graphics. In fact, Excel includes a slew of drawing tools that may seem like they belong more in an artsy illustration program than the rigid confines of a spreadsheet. Some of these drawing features are shameless frills that just take up space on the Excel ribbon. Others are genuinely useful, letting you add useful touches and highlight important information with real pizzazz.

Excel's drawing capabilities fall into the following four categories:

•    Picture Handling. These features are what most people think of when they think about graphics. Picture handling lets you take the picture files that you have on your computer and insert them into an Excel worksheet. For instance, if you've created the perfect company logo in another program, you may want to place it in a blank spot on your worksheet.

•    Clip Art. Clip art graphics are usually cartoonish, themed pictures (like a stack of dollar bills or a drawing of a globe). While you probably don't need this stuff in most worksheets, Excel's clip art features are still quite impressive. Instead of limiting you to a small selection of preinstalled image files, Excel lets you search an online collection of thousands of images using subject keywords.

•    Shape Drawing (and fancy text). Excel's shape drawing tools let you create images directly on your worksheet. These shapes include arrows, circles, stars, banners, floating text boxes, and captions with zany gradients and 3-D effects. Shape drawing is occasionally useful when you want to highlight information on your worksheet, but it's most practical when you use it with charts.

•    SmartArt. SmartArt lets you add complete diagrams that are made up of a combination of shapes and text. You can use SmartArt to create organizational charts, simple lists and flow charts, and pyramid-style diagrams. Under the hood, SmartArt uses the shape drawing features to create its diagrams, which means you can use all the same graphical flourishes and fancy effects.

This chapter covers all four kinds of drawing in Excel.

I. Adding Pictures to a Worksheet

In the previous two chapters on charts, you learned how Excel places charts in special floating boxes that hover above your worksheets. Pictures work in a similar way—they're distinct, floating objects that you can place anywhere. And just like charts, a picture box may hide data underneath it, but it'll never disturb the data.

Common examples of graphics in worksheets include minor embellishments, like a company logo next to the title, or an exclamation mark icon that highlights a worst-case scenario. You shouldn't go overboard with pictures because they tend to clutter up the real data. A few careful touches, however, can go a long way to making your spreadsheet more readable and more memorable.

A. INSERTING A PICTURE

To insert a picture file that exists on your computer, follow these steps:

1.    Choose Insert?Illustrations?Picture.

The Insert Picture dialog box appears (Figure 6-1). It's essentially the same as the trusty Open dialog box you use to open old workbooks, except the "Files of type" list is set to show all the types of image files you can use in Excel. You can change this option to show only the file type that you're interested in (for example, bitmap files, JPEG files, GIF files, or something else).

2.    Browse to the picture you want to insert, select it, and then click OK.

Excel lets you use a wide range of image file formats, including files with the following extensions: .bmp, .gif, .jpeg, .tif, .png, .wmf, and .emf.

Note: Excel includes an odd feature that lets you insert a snapshot of a currently running program instead of a picture file. To try this out, choose

Insert?Illustrations?Screenshot, and then pick the window you want from the list of thumbnails. This quirky feature is interesting, but few people are likely to use it—and those who really need screen captures probably won't be satisfied with the way Excel rudely chops off the edges and corner of the window frame. (For a more professional result, you can use dedicated screen capture software like WinSnap—we do.)

When you insert the picture, Excel places it in a new floating box. Figure 6-2 shows the result.

Figure 6-1 (On next page). Depending on what mix of content exists in the current folder, the Insert Picture dialog box may or may not show thumbnail previews for each picture it finds. If you don't see the previews but you'd like to have them, click the Views button (circled), and then choose Tiles.

Figure 6-2. This worksheet has two picture objects: a logo in the top-left corner, and a book cover image on the right. The picture boxes that Excel creates are similar to chart boxes, except Excel doesn't draw a border around a picture unless you insist. Whenever you select the picture, the ribbon sports a new tab named Picture Tools | Format.

Note: When you insert a picture this way, Excel copies all the picture data into your worksheet. Even if you change or delete the original picture file, it doesn't have any effect on your worksheet.

Frequently Asked Questions: Inserting Really Big Pictures

I want to insert a few really big pictures that I took with my digital camera. But I don't want to have a workbook that's 30 MB!

Inserting big pictures into a workbook is often a bad idea. If you do so, you'll have trouble emailing your workbook to other people or just moving it around. Sometimes you can slim down pictures using the Compress Pictures command described on page 6-10. But slimming down often isn't enough.

Fortunately, Excel has another picture trick up its sleeve. Rather than inserting a full picture, you can insert a link to your picture file. This way, your Excel workbook file stays small (and if anyone changes the picture, you'll see the difference the next time you open your workbook). The obvious disadvantage is that if you move, rename, or delete the image file, it disappears from your spreadsheet. (It's generally a good idea to keep the picture in the same folder as your Excel workbook.)

To insert a picture link, choose Insert?Illustrations?Picture, and then browse to the picture file, just as you normally would. But instead of clicking the Insert button, click the down-pointing arrow on the Insert button's right side. This click pops open a menu with extra choices. Choose Link to File to add a linked picture.

You have another option. You can choose Insert and Link to insert a picture normally and add the link to the original file. This way, if the picture changes, your workbook reflects the new look, but if the picture file disappears (say you mistakenly delete it), your workbook still has its own internal copy. The drawback of Insert and Link is that your workbook still stores a copy of the picture file, so it's still just as large as if you inserted the picture in the usual way.

B. POSITIONING AND RESIZING A PICTURE

The easiest way to move a picture once you've inserted it is to click anywhere on the picture surface, and then drag it to a new location.

Tip: Sometimes you want to move your picture just a small amount so that it lines up perfectly with some other part of your worksheet. To get really fine-grained control over picture positioning, click the picture to select it, and then use the arrow keys. For example, if you press the left arrow key, Excel nudges the picture ever so slightly to the left (one pixel, to be exact).

You can nearly as easily change a picture's size. First, click the picture once so that the resizing handles appear. These handles look like small circles and squares, and they show up at each corner and in the middle of each side. Drag one of these handles to one side to expand or shrink the picture.

Depending on which handle you drag, the type of resizing Excel performs changes:

•    Use the squares that appear in the middle of each side to stretch the picture in one direction (possibly stretching it beyond all recognition at the same time).

•    Use the circles that appear in each corner to resize the picture bigger or smaller without changing its proportions. See the box below for an explanation of the difference.

Up to Speed: Resizing Without Distortion

Excel doesn't impose any limits when it comes to resizing pictures, and, if you're not careful, you can completely mangle your image. Watch out for two problems when resizing a typical image.

First, you need to resist the urge to expand or compress your image dramatically. When you enlarge an image, Excel needs to use interpolation to guess the information it should add. (Interpolation is the process by which Excel looks at the existing pixels in your picture, and then uses them to calculate extra pixels that it should add between. If Excel finds a blue dot next to a yellow dot, then it may add a blue-yellow dot in between.) If you expand an image too much, Excel needs to make far too many guesses, and you end up with a poor-quality image (usually the edges of lines and shapes appear blocky or jagged).

Similarly, when you shrink an image, Excel needs to decide what information to discard. In order to minimize the damage, Excel tries to smooth out the new picture. But if you shrink an image too much, you end up with a picture that looks blurry or fuzzy.

To avoid either of these problems, take a careful look at your worksheet (and print it out) after you make your changes to make sure the images remain acceptable.

The second issue to be aware of is aspect ratio—the ratio of an image's width to its height. A company logo may be twice as wide as it is tall, giving it an aspect ratio of 2:1. When you resize the picture, you need to keep this sense of proportion in mind. If you change the height of the logo without adjusting its width correspondingly, the image becomes distorted. To avoid these problems, just use the resizing circles at the corners of the image (rather than the squares that appear in the middle of each side). These resizing handles let you change the size of the image without altering its aspect ratio. Instead, the height and width change in lockstep, keeping the right proportions.

If your image is what's known as a vector graphic—created by a program like Adobe Illustrator that uses formulas rather than bit-by-bit information to draw its pictures— you're in luck. You can resize vector images without causing any distortion. Excel's clipart and shape drawing features (both of which you'll use later in this chapter) use fully resizable vector graphics.

If you're not too handy with a mouse, or if you just want to size your picture with exact precision (possibly because your worksheet contains several pictures and you want them all to be consistent), head to the ribbon's Picture Tools | Format?Size section. You'll see two text boxes, which provide the current height and width of the picture box. (The units depend on the computer, but inches and centimeters are two common possibilities.) You can click either of these text boxes, and then type in a new value by hand.

Note: Excel gives you one more way to set the size of a picture by hand. From the Picture Tools | Format?Size section, you can click the dialog launcher (the tiny arrowin-a-square icon at the bottom-right corner). This opens the Size and Properties window and shows the Size tab. The Size tab not only lets you set the size (using exact measurements or percentages), it also lets you fill in some cropping settings (page 612), and it lets you rotate the picture by entering an angle in the Rotation box. (Try 180° to flip a picture on its head.)

Pictures, like charts, are anchored to specific cells. Excel may move and resize the picture when you insert, remove, or resize rows and columns. The difference between picture boxes and chart boxes is that you can explicitly control how Excel moves and sizes the picture, to make sure it isn't inadvertently moved when you want it to remain firmly in one spot.

To change the way a picture's positioned, follow these steps:

1.    Select the picture.

The Picture Tools | Format tab appears.

2.    In the ribbon's Picture Tools | Format?Size section, click the dialog box launcher (the tiny arrow-in-a-rectangle icon).

The Size and Properties dialog box appears.

3.    Choose the Properties tab.

4.    Under the "Object positioning" section, choose one of the options.

The "Move and size with cells" option gives pictures the same behavior as charts. If you insert new rows above the picture, the whole picture shifts down. If you insert rows under a picture, the bottom edge stretches down. This behavior usually isn't what you want because stretching a picture could distort it.

The "Move but don't size with cells" option anchors the top-left corner of the picture. If you insert new rows above the picture, it shifts down. If you insert rows under a picture, Excel doesn't stretch the picture. When you first insert a new picture, Excel uses this option—which makes the most sense if you have a picture you want to position near some related data (but you don't want the picture dimensions to get mangled when you add or remove content).

The "Don't move or size with cells" option doesn't anchor the picture at all. In this case, Excel doesn't move the picture or resize it no matter where you insert or delete rows. This option makes the most sense if you want a picture to stay put, regardless of wherever the content moves. This choice is typically a good one for a graphical header or company logo.

Tip: The Properties tab has another useful option: You can turn off the "Print object" checkbox in order to tell Excel to leave the picture out of your printouts. This option makes sense if you want to include rich graphics that don't look right on your black-andwhite printer (or just waste too much ink).

5. Click OK.

Ta da! Marvel at the glory of your picture.

Power Users’ Clinic: Transfer Pictures Quickly with Copy and Paste

You can also insert a picture by copying it from within another program, and then pasting it into Excel. After you've copied the image in the program where you're viewing it, select Home?Clipboard?Paste?Paste Special in Excel. When you use the Paste Special command, a Paste Special dialog box appears, with a list of different choices. Choose the option that reflects the image format ("Bitmap" if you're pasting a bitmap file), and then click OK.

You must use Paste Special instead of Paste so that you insert the right type of content. Depending on the program you're copying from, Excel may paste the picture as a bitmap or as a linked or embedded object. If you use the Paste command, Excel decides which option it thinks is best. If you use Paste Special, you get to decide.

Either way, the picture looks the same in your spreadsheet. The difference is what happens when you select the picture. If it's an ordinary picture, you can manipulate it using Excel's picture tools. If it's a linked object, you can double-click it to edit it with the program that created it. But if you open the worksheet on a computer that doesn't have the required program, you can't edit it. One quick way to tell whether you have an object or a picture is to right-click the object. If you see the option Format Picture, you've selected a picture. If you see the command Format Object instead, you've selected an object.

So which choice is best? It you don't intend to change an image, it's always best to paste it as picture data. This choice also ensures you can share your workbook files with other people without any complications. On the other hand, if you decide that you absolutely need the ability to modify the image using the original program, you can paste a linked object instead.

C.  PICTURE TOUCH-UP

Once you get your picture into Excel, you may decide you want to polish it up by changing colors or applying special effects. Depending on the result you're after, you could use a dedicated graphics program (and if you want more features than Excel provides, that's the best choice). But you'll probably be surprised to see Excel's sophisticated built-in picture-tweaking features. The ribbon's Picture Tools | Format tab, which appears whenever you select a picture, is the starting point for these features.

So what can you do with the Picture Tools | Format tab? First, you should explore the Adjust section, which lets you adjust colors, contrast, and other details. Here are the buttons you can use:

•    Brightness lets you increase or decrease the overall brightness of your image. As you increase brightness, all colors get brighter. As you decrease brightness, all colors get darker. As a side effect, increasing or decreasing brightness often reduces the contrast. Brightness is always adjusted in percentages, so -30% makes the image 30% darker, and +30% makes it that much lighter. If you aren't happy picking one of the standard percentages from the list (which go in increments of 10 from -40% to 40%), you can choose Picture Correction Options (at the bottom of the list) to pop open a dialog box where you can enter an exact percentage.

•    Contrast lets you increase or decrease the contrast. The contrast is a measure of how much difference exists between the brightest and darkest colors. As you increase contrast, bright colors get brighter and dark colors get darker. As you decrease contrast, all colors start to converge toward a middle-of-the-road gray. As with brightness, contrast is set in percentages. You can choose one of the ready-made percentages (which go in increments of 10 from -40% to 40%), or you can choose Picture Correction Options to pop open a dialog box where you

can enter an exact percentage.

Tip: Choose a 0% brightness or contrast to remove your previous brightness or contrast setting and return the image to normal.

•    Recolor lets you adjust the color in the selected picture. When you click this button, you'll see a gallery with thumbnails showing different possibilities. At the top of the list, under the Color Modes heading, you see four preset options: Grayscale, which changes every color to a shade of gray (and gives you a good idea of what a picture will look like on a black-and-white printer); Sepia, which does much the same thing as Grayscale but adds a slight brown tone reminiscent of old photographs; Washout, which fades the picture colors (and helps save ink when you print the worksheet); and Black and White, which changes every color to either black or white (a process that ruins all but the simplest of pictures).

Underneath these basic options are other options that tint your picture using one of the accent colors from the current theme. Use the choices under Dark Variations to apply a dark tint of a given color, or use the choices under Light Variations to lighten the picture as you apply the new tone. If none of these options float your boat, take a look at the More Dark Variations command at the bottom of the list, which lets you pick any color you want.

•    Recolor?Set Transparent Color lets you make certain portions of an image transparent. If there's worksheet data underneath the transparent regions, it shows through. When you click Set Transparent Color, the mouse pointer changes into a crosshairs symbol, which looks like a small plus sign (+). Next, click the color in the image that should become transparent. If you want a white background to be invisible, click a white portion of the image. Figure 6-3 shows an example.

Usually, you'll use Set Transparent Color to make a background transparent. You may want a company logo to blend right into the background color you've defined on a cell. In this case, when you create the company logo picture in your graphics program, you should make sure that you choose a color for its background that's not used anywhere else in the logo. That way, when you make that color transparent, it doesn't affect any other portion of the picture.

Figure 6-3. Using the Set Transparent Color feature, you can make any color in an image become transparent. Here are two versions of a graphic, one with no transparent color (left), and one where the background white color is transparent (right). As you can see, in the transparent version, the shaded content of the cell clearly shows through. If there were any data in those cells, it would also show through.

•    Compress Pictures lets you shrink your workbook file by discarding extra picture details you don't need. When you click this button, the Compress Pictures dialog box appears, with several options for reducing the data size of your pictures. The next section describes these options in more detail.

•    Change Picture replaces the currently selected picture by popping a different image into the current picture box. When you click this button, Excel opens the Insert Picture dialog box (covered on page 6-2) so you can choose the new picture file that you want to use. Any picture changes you made to the previous image (like changing the contrast or colors) are lost.

•    Reset Picture restores the picture to its original form. The picture looks the exact way it did when you first imported it, and Excel discards any color changes, cropping (page 6-12), and other modifications you've made. Excel also returns the picture to its natural size, although it stays in its current location.

Note: The modifications you make to a picture affect only how it appears on the worksheet (and in your printouts). Behind the scenes, Excel actually stores the full-size original picture data (unless you've used the Compress Pictures command). If at any point you realize that you've applied an edit you don't want, you can revert to the original version of the picture by clicking Reset Picture. However, this action resets all the changes you've made. You can't roll back just a single change this way (instead, use Ctrl+Z to undo a change right after you make it).

D.  COMPRESSING PICTURES

Pictures increase the size of a spreadsheet file, and if you create a worksheet with dozens of graphics, the file's size can grow significantly. Most of the time, you won't worry too much about the size of your Excel files. However, if you plan to send it through email or put it on an old-fashioned diskette, then you may need to pare it down in size. You can cut down the picture data, for instance. In a spreadsheet with a number of highquality bitmap pictures, the images can take up a significant amount of disk space. (On the other hand, vector drawings like clip art and shapes don't use much space at all.)

Note: Don't compress pictures if you want to change them later. Why?

Because compressing a picture discards the original picture information. If you shrink a picture, compress it, and then enlarge the picture back to its original size, you end up with a lower-quality image.

To compress a picture, follow these steps:

1.    Select the picture in your worksheet that you want to compress.

If you want to compress more than one picture at once, hold down Ctrl while you click each picture. If you want to compress all the pictures in your file, the Compress Pictures dialog box (in the next step) has a shortcut you can use, so just select one picture for now.

2.    Choose Picture Tools | Format?Adjust?Compress Pictures.

The Compress Pictures dialog box appears (see Figure 6-4).

Figure 6-4. The Compress Pictures dialog box lets you choose just how compressed your pictures will be. The Print setting (used here) keeps 220 pixels for every square inch of print space.

3.    If you want to change all the pictures in your workbook, clear the check box next to the "Apply only to this picture" setting.

This option is useful in a workbook that has plenty of big pictures, because it slims them all down with a single step.

4.    If you want to remove the unused portion of a cropped picture, turn on the

"Delete cropped areas of pictures" checkbox.

Cropping lets you cut out a smaller part of a larger picture. (There's more on cropping in the next section.) When cropping a picture, Excel ordinarily keeps the full-size original in case you want to change the cropping later on. To throw out that extra information and save space, use this option.

5.    Choose an option in the "Target output" section.

When you compress a picture, Excel resamples the picture based on its current size. In other words, if you've reduced the size of a picture (by dragging the picture handles), Excel saves only enough information for the current, smaller version of the picture.

Exactly how much information Excel stores depends on the resolution option.

•    Print tells Excel to keep enough information for a decent printout at the current size. However, you may notice a little blurriness if you resize the picture larger later on.

•    Screen tells Excel to store a lower-quality picture that's sufficient for screen display (in a PowerPoint presentation or an online web page, for instance) but produces a poorer printout.

•    E-mail tells Excel to store an even smaller picture, which makes sense if you're planning to send your workbook in an email and you need to pare it down as much as possible to avoid bloating your recipient's Inbox.

•    Use document resolution tells Excel to use the standard image compression setting for this document. By default, this is 220 dpi (the same as Print). However, you can change the document resolution at any time using the Excel Options window.

Note: To change the default document resolution, choose File?Options. Then, click the Advanced category (on the left) and scroll down until you see the Image Size and Quality section. Finally, choose a new resolution from the "Set default target output to" list.

6.    Click OK to close the Compress Pictures dialog box and apply your new compression settings.

You can now save your new, leaner spreadsheet with the File?Save command.

Once you compress your pictures, there's no turning back. (Of course, if you keep the original version of a picture file somewhere else on your computer, you can always re-insert it later if you need it.)

E. CROPPING AND SHAPING A PICTURE

Ordinarily, Excel puts the whole picture you select in your worksheet. However, in some cases you may decide that you want to highlight just a small part of the picture and forget about all the rest. In this situation, you can clip your picture down to size using another program, or you can crop it right inside Excel.

To crop a picture in Excel, follow these steps:

1.    Select the picture.

2.    Choose Picture Tools | Format?Size?Crop.

After you click this button, cropping handles appear on each corner of the picture and in the middle of each side. (If your picture is dark, you may need to look carefully to see the cropping handles, because they're black.)

3.    To crop your picture, click one of the cropping handles and drag it inward.

As you drag a cropping handle, Excel hides the outlying part of the picture (Figure 6-5).

4.    Once you're finished, choose Picture Tools | Format?Size?Crop again to turn off the cropping handles.

Excel keeps the picture data you've cropped out in case you want to return the picture to its original size (either by recropping the picture or using the Picture Tools | Format?Adjust?Reset Picture command). If you're sure you don't need this option, then you may want to use the Compress Pictures option (explained on page 6-9) to discard this extra data and cut down on the size of your workbook file. This idea is a good one if you've cropped a tiny bit out of a very large picture (in which case your workbook file's storing a lot of excess picture).

Figure 6-5 (On next page). Top: You can use just one cropping handle to remove part of the picture on one side, or you can drag all the cropping handles in until you're left with just the part of the picture you want to see. Bottom: The final result.

After you finish cropping a picture, you may also want to apply a different edge effect to shape it. You can choose from a huge gallery of simple shapes by choosing Picture Tools | Format?Picture Styles?Picture Shape. Figure 6-6 shows one possible example.

Figure 6-6. Top: This picture began its life as a group portrait. Bottom: Excel's cropping and shaping features get to the heart of the matter.

F. PICTURE BORDERS, EFFECTS, AND STYLES

If Excel's picture coloring, cropping, and shaping features don't keep you busy, you'll be happy to learn that the graphical fun doesn't stop there. Along with the features you've already seen, Excel also lets you apply a picture border and a picture effect.

When picking a border, it's up to you to pick the color, thickness, and style. You set all these details by selecting the picture in question, and then using the Picture Tools | Format?Picture Styles?Picture Border list. Here's what to do:

•    If you don't want to use basic black, pick a color from the Picture Border list. It's always a good idea to use theme colors so your pictures blend in with the scenery.

•    To make your border appear, choose a thickness from the Picture

Border?Weight submenu. 1/4pt is sleek; 6pt is thick and heavy.

•    If you don't want a solid border, choose another line style from the Picture Border?Dashes submenu. You'll see a variety of different types of dashed and dotted lines.

•    To get rid of a border you don't like anymore, choose Picture Border?No Outline.

Picture effects are more exotic, but just as easy to discover. To get picture effects, you need to use the Picture Tools | Format?Picture Styles?Picture Effects list. You'll see submenus for applying shadow, reflection, 3-D rotation, a soft edge, or a glowing edge. Each of these submenus has a gallery of common options with thumbnail previews. Figure 6-7 shows one example.

Figure 6-7. A 3-D rotation is a dramatic way to make the statement, "I have too much free time."

Tip: The best way to learn about all Excel's wacky picture effects is to experiment. As you move your mouse over the different picture effects, Excel's live preview changes the picture on your worksheet accordingly. To get a better look, click to apply the change, and then hit Ctrl+Z to undo it if it isn't to your liking.

If you're in a hurry, you don't need to fiddle with the picture shaping, border, and effect settings separately. Instead, you can choose a preset style that applies a combination of these settings from the style gallery in the ribbon's Picture Tools | Format?Picture Styles section. You'll find an option that makes your picture look like a postcard, a scrapbook clipping, or a wavy piece of glass that's fallen on its side (which is the current frontrunner for the "Feature Least Likely to Ever Appear in a Real Spreadsheet" award).

II. Excel's Clip Art Library

One of the challenges in using pictures is finding the right image. If you need to use a company logo, you probably have that on hand already. However, if you're looking for a picture on a specific subject—say a drawing of French fries for your analysis of fried versus steamed carbohydrate calories—it can take hours of web surfing to find an image that fits your requirements and is legal to use.

Excel answers this challenge with an innovative online library of clip art that, at last count, contained a staggering 150,000 images. Best of all, anyone who owns Excel can search this clip art library and use any of its images for free.

To insert an image from the clip art library, follow these steps:

1.    Select Insert?Illustrations?Clip Art.

A Clip Art pane appears on the right side of the Excel window (see Figure 6-8). As described in the next steps, you use it to search for images.

Note: The Clip Art pane provides a few extra options at the bottom of the window. You can click the "Find more at " link to browse Microsoft's online clip art using your web browser instead of searching from within Excel.

2.    In the "Results should be" list, you can choose the media types you want to search for.

The Illustrations category includes common vector graphic file types like Windows Metafiles (.wmf and .emf). Vector graphics are images that are stored as a series of instructions (as in "draw blue square, add green circle…"). The key benefit to vector graphics is that you can resize them without losing quality.

Figure 6-8. The Clip Art pane is your doorway to the vast resources of clip art on your computer and on Microsoft's Office Online website. You can search for images using topic keywords, and then drag the pictures into your worksheet.

The Photographs category includes common bitmap file types like Bitmaps, JPEGs, and GIFs. Bitmap graphics are stored as a series of individual points, or pixels, arranged in a rectangle. Bitmaps require more disk space than vector graphics, and you can't resize them without some compromise in quality. However, they can provide photorealistic detail.

The Videos and Audio categories include animated files and audio recordings.

Tip: The majority of Microsoft's catalog consists of ordinary clip art. However, if your search returns movie or audio files (like when you search with the keyword "scream"), you should probably remove these categories from your search and try again. Unless you want to startle your coworkers, there's really no point to dropping a sound object into your worksheet.

3. In the "Search for" text box, enter a few words that describe the images you want to find.

Microsoft stores a list of descriptive words for every image in its clip art library. When you perform a search, Excel tries to match the words you enter against the description for each image. If it finds the words in the description, it shows you the picture as a match.

You can include one word or a combination of words. If you use the search words mad monkey, you'll find only pictures that have the words mad and monkey in their description. On the other hand, if you search for monkey, you'll end up with a much larger list of results.

For even more results, you can search for pictures that have either one of two or more words in their description. Just use a comma to separate the words. The search mad, monkey gives you all pictures that have the word mad in their description, and all pictures that have the word monkey in their description.

If you're searching for a file in one of your computer's clip art folders, and you know the file name, you can use that as a search term. If you don't know the exact file name, you can use wildcards like ? (for any single character) and * (for any series of characters). You'd type in to locate file names like or . Or enter car*.jpg to locate file names like or . If you want to use the asterisk (*), you must place it at the end of the search string, but before the file extension. It can't appear at the beginning or in the middle of the string

(* or car*d.jpg don't fly).

Tip: Make sure you keep the "Include content" setting switched on if you want to perform a search that includes the huge library of art on Microsoft's Office Online website.

4.    Click Go to start the search.

The results appear as a list of thumbnail images in the Clip Art task. Because Excel needs to download results from the Internet, it can take a little bit of time before all your results appear. You can click Stop at any point to cancel a search that's in progress.

5.    If you see an image you want to use, double-click it to insert it into your worksheet (Figure 6-9).

Once you add a picture to your worksheet, you can treat it like you would any other picture, so go ahead and resize it, crop it, change the contrast, and so on. When you save your worksheet, Excel adds the picture data to your file, so you don't need to download it again.

Figure 6-9. In this example, a search using the word "dollar" has just finished. The search turned up dozens of matching images, which it shows as thumbnail-sized images, including the collection of currency that has been added to the worksheet as a picture object.

III. Drawing Shapes

If the stock graphics provided in the clip art collection don't satisfy your inner art critic, you can create your own pictures. Excel's drawing features make this process a lot easier than you might expect. In fact, you can create everything from simple shapes to complex art without leaving your worksheet.

The starting point for all drawing activity is the ribbon's Insert?Illustrations?Shapes section, which is filled with potential shapes (Figure 6-10).

Figure 6-10. Depending on how large your Excel window is, you may see a few shapes in the Insert?Shapes section. But if you click the drop-down arrow, you get a complete list, sub-divided into logical sections.

Before you can really get started drawing anything, you should understand the basic shape categories. They include:

•    Lines. This category includes straight lines, curved lines, and arrows.

•    Rectangles. Albeit ordinary, rectangles are great for storing bits of text or just wrapping themselves around groups of other shapes.

•    Basic Shapes. This category includes geometric shapes like the square, circle, rectangle, octagon, and more. Leave it to Microsoft to also include not-so-basic shapes like rings, lightning bolts, suns, moons, and even a happy face.

•    Block Arrows. This category includes a variety of one-way and two-way arrows, as well as shapes with arrows attached to them.

•    Equation Shapes. This category includes large mathematical symbols, like the multiplication, division, and equal signs.

•    Flowchart. This category includes shapes that are often used in flowcharts, like the rectangle (which represents a step in a process) and the diamond (which represents a decision).

•    Stars and Banners. This category includes the common five-pointed star and other starburst shapes. It also includes different types of banners, like award strips and unfurled scrolls. These shapes look best if you put some text inside them.

•    Callouts. Callouts are designed to add information to a worksheet. Most Excel callouts are shapes with a connected line. The line points at something important, and the shape contains any descriptive text you want to write.

Note: Most Office programs feature these shapes. Once you learn to use them in Excel, you can also use them in the same way in Word or PowerPoint. This fact also explains the existence of some of the shapes that don't make much sense in Excel spreadsheets—they're really intended for other Office applications.

A. DRAWING A SHAPE

Excel lets you draw a wide range of shapes, from simple lines and circles, to banners and three-dimensional arrows. To insert a new shape, follow these steps:

1.    Find the shape you want in the Insert?Illustrations?Shapes section, and then click it.

2.    Click your worksheet in the spot where you want the shape to appear, and then drag to make the shape as big as you want.

Usually, Excel inserts the image as soon as you release the mouse button. However, some shape types, like the curved line or freeform line, have an extended drawing mode. With these shapes, every time you click the worksheet, Excel adds a new curved line segment. When you want to finish the drawing, you need to double-click the last point.

Once the shape appears, Excel selects it, and then adds the Drawing Tools | Format tab to the ribbon.

Note: The Drawing Tools | Format tab is a lot like the Picture Tools | Format tab you learned about earlier. It includes similar buttons for applying borders and effects, and arranging and resizing your shape.

3.    Pick a color for your shape from the Drawing Tools | Format?Shape

Styles?Shape Fill list.

This color fills the inside of all shapes except for lines. You can also choose No Fill to make the shape transparent so that other shapes (and your worksheet data) show through. You can use a circle with no fill to point out some important data on your worksheet, for example.

Along with the standard color choices, you can also use a fancy texture, an existing picture, or a gradient. In fact, shapes offer exactly the same options that you saw when you colored in chart elements (page 5-17).

4.    Pick a border color, thickness, and dash style from the Drawing Tools | Format?Shape Styles?Shape Outline list.

To pick a shape border, you follow the same process you did to add a border to an ordinary picture from a picture file (page 6-15).

5.    If you want a fancy shape effect, like shadow or 3-D rotation, choose the effect from the Drawing Tools | Format?Shape Styles?Shape Effect list.

The effects that you can use with shapes are mostly the same as the effects you can use with pictures (although they usually make more sense with shapes). They include:

•    Shadow adds diffused gray shading behind your shape, which makes it look like it's floating over the page.

•    Reflection adds a faint copy of part of the image just under the bottom edge, as though it's being mirrored in a pool of water or piece of shiny glass.

•    Glow adds a blurry edge in a color you choose.

•    Soft Edges adds a blurry edge that softens your border.

•    Bevel shapes the surface of the image so that part of its surface appears raised or indented.

•    3-D Rotation turns the image around in three dimensions. This trick works best with images that have some depth to them—the thick block shape is a better choice than the flat square.

•    Preset lets you choose from some ready-made options that combine more than one effect.

Figure 6-11. This eye-catching arrow sports a thick white border, gradient fill, and shadow.

Tip: If you don't want to pick a separate fill color, border, and effect, you can use one of Excel's preset styles. Just make your choice from the gallery of options (each of which has a tiny thumbnail preview) in the Drawing Tools | Format?Shape Styles section of the ribbon.

6.    Now that you have perfected your shape, you can drag it to the position you want, and then resize it.

When you select a drawing, Excel not only displays the usual resize handles, but it also gives you one or more yellow diamonds and a green circle, as shown in Figure 6-12. You can drag the green circle to rotate the image. You can use the yellow diamonds to change the proportions on the shape. You can change the amount of curve in a curved banner, the width of each point in a star, or the length of a line in a callout. As you drag, Excel superimposes a light copy of the shape to indicate how the shape will change.

Figure 6-12. Look for a drawing's yellow diamonds, which let you alter the shape in all kinds of weird and wonderful ways. Different shapes support different types of alterations.

Power Users’ Clinic: Hard-Core Shape Manipulation

If you're feeling very punk rock, you can make dramatic changes to a shape in Excel. In fact, Excel lets you edit a shape like it's a diagram in an illustration program. To do so, select the shape, and then choose Drawing Tools | Format?Insert Shapes?Edit Shape?Convert to Freeform. Although your shape still looks the same, this action changes it to a collection of straight lines, curves, and points.

You can now modify each point. First, choose Drawing Tools | Format?Insert Shapes?Edit Shape?Edit Points to show the points on the shape. Then you can drag a point to move it somewhere else. As you move the point, Excel "pulls" the rest of the shape along with it. Drag a few points and you can take an ordinary shape like an arrow and transform it into a strangely distorted blob.

Most people find that this feature is an effective way to ruin a perfectly good shape. But your art skills could make the difference in improving a design.

B. ADDING TEXT TO A SHAPE

You can add text to almost any shape. It doesn't matter whether you've got a circle, a box, an arrow, a banner, a starburst, or even something weird. Shapes that don't have any interior space, like lines, are the only exceptions.

When you add text to a shape, the text wraps itself to fit neatly inside. Figure 6-13 shows a few examples.

Figure 6-13. All these shapes share one thing in common: they contain descriptive text. Used creatively, text and shapes can add a little life to your worksheets.

To add text to a shape, follow these steps:

1.    Right-click the shape, and then choose Edit Text.

Your cursor moves inside the shape, and a box appears around the current text, if there is any.

You can also just click the shape, and then start typing, without bothering to choose Edit Text.

Tip: If you want to add floating text that isn't inside a shape, choose Insert?Text?Text Box, draw the text box somewhere on your worksheet, and then start typing. Or, use one of fancy presets from the Insert?Text?WordArt gallery if you want to add a text box that already has some wild formatting in place.

2.    Type the text you want to use.

3.    If you want to format your text, use the mini formatting bar or the buttons on  the Drawing Tools | Format?WordArt Styles section of the ribbon.

To use the mini bar to make basic formatting changes, select the part of the text you want to change, and then choose a new font, size, color, and so on from the small toolbar that appears just above.

To use the WordArt feature to apply eye-catching effects, select the whole shape, and then head to the ribbon's Drawing Tools | Format?WordArt Styles section. You'll find buttons that let you independently apply a fancy fill, border, and various effects. You've already used these features with shapes and pictures. Or, you can choose a ready-made combination of formatting settings from the Drawing Tools | Format?WordArt Styles?Quick Styles list. Figure 6-14 shows a shape with some WordArt-enhanced text.

Figure 6-14. This text uses a dazzling reflection effect to distract spreadsheet readers from abysmal sales numbers elsewhere in the worksheet.

Note: There's a little-known trick you can use to put a cell reference into the text of shape. Any time you're typing text into a shape (or in a floating text box), click the formula bar. You can then type in a simple cell reference (like =B3 to show the value that's in cell B3) or a more complicated formula, complete with all the usual Excel functions. This handy-dandy trick lets you pull the current information out of your worksheet, avoiding duplication and making sure your graphics are always up-to-date.

C. SELECTING AND ARRANGING SHAPES

If you add enough shapes, you may start to run into trouble manipulating and layering all these different objects. Here are some potential headaches you could face:

Some shapes are difficult to select. If you don't click exactly on a line, you end up selecting the worksheet cell underneath the line.

Some shapes may obscure other shapes. What if you want to put a starburst shape inside a circle? Depending on the order in which you've added the shapes, when you move the starburst over the circle, it could actually disappear underneath the circle.

Excel has a handy tool to help you out. It's called the Selection and Visibility pane (shown in Figure 6-15), and you call it into action by choosing Page

Layout?Arrange?Selection Pane. Or, if you have a shape that's currently selected, you can get the same feature using the Drawing Tools | Format?Arrange?Selection Pane command.

Figure 6-15. The Selection and Visibility pane lists all the floating objects you've added to your worksheet. These objects include shapes, pictures, text boxes, and charts. The name is determined by the shape type, and the sequence in which you added it. The 10th text item you add may have a name like TextBox 10. For pictures, you see the word "Picture," and for charts you see whatever name you set in the ribbon's Chart Tools | Layout?Properties?Chart Name box.

The Selection and Visibility pane lets you do two things: select difficult-to-reach objects, and change the way they're layered.

To select an object, simply click it in the list. This method works even if your shape is buried underneath another shape and therefore impossible to click with the mouse. Once you've selected your shape, you can move it, resize it, or format it using the ribbon.

Tip: To quickly jump from the currently selected shape to the next shape, just press Tab. The resizing handles appear around the newly selected shape. You can also use Shift+Tab to move back to the previously selected shape.

Sometimes, you'll want to select several shapes at once. To do so, just hold down the Ctrl key while you click each shape in the list. Once you've selected several shapes, you can move or format them as a group. You still use the ribbon in the same way, but now your changes affect every selected shape.

Tip: If you plan to use a group of shapes as a single unit, you can group them together. When you do so, Excel treats them like one shape object when you select or move them. To group shapes, select them all, and then choose Drawing Tools | Format?Arrange?Group?Group. The only disadvantage to having a grouped shape is that you can't modify the individual shapes separately unless you first choose Drawing Tools | Format?Arrange?Group?Ungroup to remove the grouping.

The Selection and Visibility pane also shines when you need to change the layering in your worksheet (the way that different images overlap one another). Technically, each image on your worksheet exists in its own private layer. Whenever you add a new shape, Excel creates a new layer at the top of your worksheet, and then puts the new shape in this layer. That means new objects are layered on top of older ones—which may not be what you want.

To change the way Excel layers objects, you need to change the order of items in the Selection and Visibility pane list. Objects at the top of the list appear on top of other objects further down the list (Figure 6-16). To move an item, select it, and then click the up or down arrow button. Figure 6-17 explains how to create transparent shapes—good for when you want the cells beneath your shapes to remain visible.

Figure 6-16. In this example, the apple (Picture 5) appears on top of the happy face (Smiley Face 3) because it's higher in the list.

Figure 6-17. The starburst shown here is transparent, so the data and shapes underneath show through. To create this effect, select the shape and choose Drawing Tools | Format?Shape Styles?Shape Fill?No Fill.

Note: You can also change the layering order using the Bring to Front and Send to Back buttons in the ribbon's Drawing Tools | Format?Arrange section. However, the Selection and Visibility pane is much easier to use.

If you have an extraordinarily complex worksheet that's dense with shape objects, you may find that it helps to temporarily hide the ones that you aren't interested in. You can do this in two ways:

•    To hide everything, click the Hide All button in the Selection and Visibility pane. Now, whenever you click to select an image in the list, it appears. When you're done making your changes and you're ready to see all your shapes again, click Show All.

•    To hide just a few items, click the eye icon next to each item in the Selection and Visibility pane list. Click the eye again to make the item reappear (or use the Show All button to show everything).

Timesaving Tip: Lining Up Shapes

When creating a complex piece of Excel art (like a diagram that's entirely made up of shapes), you need a way to line up shapes with exact precision. Simply dragging each shape into place with the mouse may be too difficult or just take too long. Happily, Excel has an automatic alignment feature that can really help you out.

To use Excel's alignment feature, begin by selecting all the shapes that you want to line up. (Hold down the Ctrl key, and then select each one on your worksheet or in the Selection and Visibility pane.) Then, make a choice from the Drawing Tools | Format?Arrange?Align section of the toolbar. You can line up shapes along their left, right, top, or bottom edges, or center them so their mid-points line up. And if you've selected more than two objects, you can use the Distribute Horizontally and Distribute Vertically commands to space them out evenly, with a consistent amount of space between each shape.

D. CONNECTING SHAPES

On the one hand, you may have noticed that the shapes in the Lines category are perfect for connecting other shapes. The creators of the Office shape-drawing model also recognized this fact, and they made it easier for you to snap your lines into place. On the other hand, you may be wondering if there's really any point to connecting two shapes. Why not just drag your line anywhere on the border of a nearby shape? After all, even if you don't hit a connection point, it still looks like your line is connected to the shape. Connections' real benefit appears when you move the connected object. Imagine you have a line that links together two squares. If you've used connections, when you drag one of the squares to a new place, the line follows. If you haven't used connections, you have to move the square, and then resize the line every time.

Here's how to connect shapes. Every shape has predefined connection points, which are ideal places where you may want to connect a line. A basic rectangle has four connection points—one in the middle of each side—and a typical circle has about eight, arranged in even intervals along the border. To use the connection point feature, you simply click to select a line, and then drag one end over another shape. As you get close, Excel shows you all the connection points using small red squares (see Figure 618). When you drop the line in one of these places, you've created a connection.

Figure 6-18. As you drag the line with the arrow over this callout (page 6-21), Excel shows the possible connectors.

Connections let you take separate shapes and build more impressive diagrams. When you use connections, you may also want to consider using grouping (page 6-27). And if you don't want to connect everything on your own, you may be interested in the SmartArt feature (discussed on page 6-31), which gives you premade diagrams that include numerous shapes and connecting lines.

Gem in the Rough: Drawing Graphic Objects in Charts

You may find that shapes are more trouble than they're worth, because they can quickly gunk up a worksheet. But graphic objects become extremely useful in one area: your charts. With the right shapes, you can break out of Excel's limiting rules for labeling and highlighting data and add eye-catching arrows and shapes.

Excel lets you draw on a chart object in the same way that you draw on a worksheet— using the tools found on the Insert?Illustrations section of the ribbon. Best of all, once you draw a shape in a chart box, it stays locked into that box. That means if you move the chart, the shape follows along, remaining in the appropriate position. Figure619 shows some of these techniques.

Here are a few ways that Excel's drawing features can enhance your charts:

•    Use arrows to point to important places on a chart. This technique works well if you need to highlight a single data point.

•    Use circles or squares around an important region on the chart. This technique works well if you need to highlight a section containing multiple data points.

•    Use callouts to add descriptive text explaining why a chart line takes a sudden dive or turns upward suddenly.

•    Add picture objects, like logos or a themed background (for example, show a picture of a beach in a chart that tracks favorite vacation destinations).

Figure 6-19. This chart features half a dozen graphical elements. A logo floats in the top-right corner, while a starburst announces the results in the bottom-right. A callout points to a sudden change in the data, and a combination of a textbox, arrow, and ring highlights where the two lines cross.

E. SMARTART

SmartArt is a feature that lets you create business graphics and place them in your Excel worksheet. Figure 6-20 shows a few examples ofSmartArt diagrams.

Figure 6-20 (On next page). You can use SmartArt graphics to show a company's organizational structure (top left), a sequence of steps (top right), a pyramid of good eating choices (bottom), and much more.

SmartArt and Excel have a slightly awkward relationship. Although the SmartArt graphics are unarguably attractive (and easy to build), they don't make sense in most Excel workbooks. After all, most people expect to use Excel to record reams of numbers, and analyze them with number-crunching formulas and sophisticated charts. Diagrams make more sense in the company report (a Word document) or a budget presentation (a PowerPoint document). Truthfully, you're more likely to use SmartArt in both these programs than in Excel.

Furthermore, SmartArt diagrams have the same fundamental limitation that all shapes have in Excel—you can't use cell references as part of your text. That means if you need to have summary numbers or the result of a complex calculation in a SmartArt diagram, you need to copy the values yourself (and remember to update them when the value changes in the worksheet).

Even with all these considerations, SmartArt can still help you create a professionalcaliber diagram in a hurry.

Here's how to create a SmartArt diagram:

1.    Choose Insert?Illustrations?SmartArt.

The Choose a SmartArt Graphic dialog box appears (Figure 6-21).

Figure 6-21. You can choose from a gallery of about 80 diagram types.

2.    Choose the diagram you want to use, and then click OK.

The Text pane appears where you can enter the text that's presented in the diagram (Figure 6-22). If the Text pane doesn't appear automatically, choose SmartArt Tools | Design?Create Graphic?Text Pane.

3.    Type the text for your diagram.

The Text pane works like a bulleted list. You press Enter to create more lines, each of which is called a point. You hit Tab to indent your point one level, which makes it a subpoint. (Press Shift+Tab to turn a subpoint back into a normal point.)

Each point is always a separate shape. However, different diagrams have different ways of presenting points and subpoints. In some diagrams (like the process diagrams), the subpoints become bulleted points inside each shape. In others (like the organizational diagrams), the subpoints become their own distinct shapes, as shown in Figure 6-22.

Figure 6-22. You don't need to fiddle with individual shapes to create a SmartArt diagram. Instead, you type in everything you need in a special Text pane (shown here on the right). Excel uses this text to create the diagram.

4.    When you're finished, drag your diagram into place, and then resize it as you see fit.

You can click anywhere on your worksheet to return to Excel.

Once you've created a diagram, you can format and fine-tune it much the way you format shapes. First, select your SmartArt graphic, at which point two new tabs appear in the ribbon: SmartArt Tools | Design and SmartArt Tools | Format.

Here are some tricks you may want to try out:

•    Make a choice from the SmartArt Tools | Design?Layouts gallery to switch to a different type of diagram. Excel automatically transfers the text you entered to the new diagram.

•    Make a choice from the SmartArt Tools | Design?SmartArt Styles gallery to apply different color, border, and shape effects.

•    Click one of the shapes inside the SmartArt graphic (like a single text box). You can then format it differently so it stands out from the rest using the Smart Tools | Format tab. Among the changes you can make, include changing the type of shape, applying WordArt effects, and moving or resizing the shape.

•    Choose Smart Art Tools | Design?Reset?Reset Graphic to clear your formatting and return everything to normal. The list of points and subpoints that you've typed in remains.

CHAPTER 6 – REVIEW QUESTIONS

The following questions are designed to ensure that you have a complete understanding of the information presented in the chapter. They do not need to be submitted in order to receive CPE credit. They are included as an additional tool to enhance your learning experience.

We recommend that you answer each review question and then compare your response to the suggested solution before answering the final exam questions related to this chapter.

1.    Which of the following drawing capabilities lets you create images directly on your worksheet:

a)    Picture Handling

b)    Clip Art

c)    Shape Drawing

d)    SmartArt

2.    Resizing pictures to fit on a spreadsheet can cause aspect ratio problems, but vector images can be easily resized without causing distortion.

a)    true

b)    false

3.    If you will need to do extensive editing on an image you paste into an Excel spreadsheet, it is best to paste the image as a picture rather than as an object.

a)    true

b)    false

4.    When compressing an image in Excel, which resolution option tells Excel to use the standard image compression setting for a particular document:

a)    Print

b)    Screen

c)    Email

d)    Use document resolution

5.    In which drawing shapes category will you be able to find a diamond:

a)    Basic Shapes

b)    Equation Shapes

c)    Flowchart

d)    all of the above

6.    SmartArt graphics are available to use in Excel, but they don’t make much sense to use in a workbook.

a)    true 

b)    false

CHAPTER 6 – SOLUTIONS AND SUGGESTED RESPONSES

1.    A: Incorrect.  Picture Handling lets you take the picture files that you have on your computer and insert them into an Excel worksheet, but it does not let you create the images right in the worksheet.

B: Incorrect.  Clip art lets you drop cartoonish, themed pictures into the worksheet, but it does not let you create images directly on the worksheet.

C: Correct.  Excel’s Shape Drawing tools let you create images directly on the worksheet.  This can include arrows, circles, stars, banners, floating text boxes, and 3-D effects.

D: Incorrect.  SmartArt lets you add complete diagrams that are made up of a combination of shapes and text, but it does not let you create images directly on the worksheet.

(See page 6-1 of the course material.)

2.    A: True is correct.  Excel does not impose any limits when it comes to resizing pictures to fit on a spreadsheet, but resizing a typical image can often get mangled and distorted.  Vector images, such as clip art, shape drawings, and graphics created by Adobe Illustrator, are fully resizable vector graphics and will not get distorted when resizing.

B: False is incorrect.  Typical images can easily get distorted when resized causing aspect ratio problems, but vector images use bit-by-bit information to draw images, so they can be resized to fit without causing distortion.

(See page 6-5 of the course material.)

3.    A: True is incorrect.  Whether you paste the image as a picture or an object, it will look the same in your spreadsheet, but how you manipulate it will be different.  If you know you will need to do extensive editing on the image, then pasting it as an object will allow you to link to the program it was created in so you can do that editing.

B: False is correct.  Pasting an image as a picture is always best if you don’t intend to change the image.  However, if you do intend to edit the image, you will need to paste it as an object so it keeps its link to the original program that created it, and then you will be able to perform any necessary edits.

(See page 6-7 of the course material.)

4.    A: Incorrect.  The Print option tells Excel to keep enough information for a decent printout, therefore, it does not tell it to use the standard settings for that document.

B: Incorrect.  The Screen option tells Excel to store a low quality picture that is sufficient for screen display, therefore, it does not tell it to use the standard settings for that document.

C: Incorrect.  The Email option tells Excel to store the lowest quality document so that it will email the best, so it does not tell it to use the standard settings for that document.

D: Correct.  The Use document resolution setting tells Excel to use the standard image compression setting for that document.  By default, this is 220 dpi, but it can be changed by the user, if desired.

(See page 6-11 of the course material.)

5.    A: Incorrect.  The Basic Shapes category includes geometric shapes like a square, circle, and rectangle.  It can also include some crazy shapes like lightning bolts, suns, and moons, but the diamond will be found in the Flowchart category because a diamond represents a decision on a flowchart.

B: Incorrect.  The Equation Shapes category includes large mathematical symbols, like multiplication, division, and equal signs. The diamond will be found in the Flowchart category because a diamond represents a decision on a flowchart.

C: Correct. The Flowchart category includes shapes that are often used in flowcharts, and since the diamond represents a decision on a flowchart, this is where you will find the diamond shape.

D: Incorrect.  The diamond can only be found in the Flowchart category, not the Basic Shapes or Equation Shapes. The diamond represents a decision on a flowchart.

(See page 6-20 of the course material.)

6.    A: True is correct.  Most people expect to use Excel to record reams of numbers and analyze them with formulas and charts.  Attractive SmartArt diagrams don’t lend themselves well to this type of thinking. SmartArt graphics are also limited in that they can’t contain cell references, so summary numbers or results cannot be calculated in them.

B: False is incorrect.  Even though SmartArt is available to use in Excel, that does not make it a good application.  SmartArt makes more sense in a Word document, such as a company report, or in a PowerPoint budget presentation, than it does in an Excel workbook.

(See page 6-32 of the course material.)


Glossary

Ascending:  To order text or numbers from the lowest entry to the highest.

Banding:  A pattern of alternating shading that separates one row or column from the next.

Cells:  The component of a spreadsheet table that contains a single entry.

Chart:  A graphic representation of a dependent variable.

Column: A vertical row or list.

Descending:  To order text or numbers from the highest entry to the lowest.

Embedded:  To be fixed or incorporated into the surrounding area.

Extrapolation:  The process of estimating data that you don’t have, based on data that you do.

Field:  A column of information on a worksheet.

Grid:  The cells contained on a worksheet.

Gridlines:   The lines that run across the plot area.

Grouping:  The process of selecting multiple worksheets to perform operations on all at once.

Icon:  A picture or symbol that stands for an object.

Interpolation:  The process of estimating unknown data values between known existing values.

Layout:  The arrangement of chart data that allows you to control the presence and placement of various chart elements.

Legend:  The area of a chart that identifies each data series with a different color.

Plotting:  The process of drawing numbers on a graph.

Record:  A row of information on the spreadsheet.

Row:  A horizontal row or list.

Spreadsheet:  A program that allows easy entry and manipulation of figures, equations, and text; synonymous with worksheet.

Glossary                                                           1

Template:  A spreadsheet that serves as a pattern for future spreadsheets.

Vector graphics: A computer image that is stored in memory as lines rather than a series of dots, allowing it to be rotated or proportionally scaled.

Worksheet:  A program that allows easy entry and manipulation of figures, equations, and text; synonymous with spreadsheet.

Glossary                                                           2

Index


A

area chart, 4-28, 4-29, 4-30, 4-36

auto outline, 2-15

automatic subtotaling, 2-15

B

backstage view, 3-2, 3-3, 3-4, 3-10, 

3-11, 3-14 bar chart, 4-25 bubble chart, 4-23, 4-35

C

chart area, 5-4, 5-37

clip art, 6-1, 6-16, 6-17, 6-18

clustered column, 4-23, 4-24, 4-25

column chart, 4-2, 4-4, 4-10, 4-11, 4-13,

4-15, 4-23, 4-25, 4-26 combination charts, 5-44

conditional formatting, 1-18, 1-24, 1-26

consolidate dialog box, 2-12, 2-13

custom list dialog box, 1-16

D

data labels, 5-7, 5-8, 5-10, 5-11, 5-13, 

5-31 data point, 5-5, 5-7, 5-8, 5-9, 5-11, 5-12,

5-13, 5-24, 5-25, 5-26, 5-29, 5-35, 

5-36

data series, 5-5, 5-25, 5-39, 5-40, 5-42,

5-43, 5-45 data validation, 3-12 database functions, 1-34

donut chart, 4-34, 4-35

E

embedded chart, 4-2

external table data, 1-5

F

file locations dialog box, 3-15

filtering, 1-1, 1-2, 1-3, 1-4, 1-7, 1-12, 

1-19, 1-21, 1-22, 1-23, 1-24, 1-32, 

1-33, 1-34

G

gridlines, 5-3, 5-4, 5-5, 5-14, 5-15, 5-16,

5-17, 5-21, 5-23, 5-29, 5-30, 5-31 grouping buttons, 2-9

H

horizontal axis, 5-6

 I

insert chart dialog box, 4-4, 4-5, 4-11, 

4-23

installed templates, 3-11

L

legend, 5-7

line chart, 4-13, 4-15, 4-16, 4-17, 4-19,

4-22, 4-26, 4-27, 4-28, 4-31 linear forecast trend, 5-33 linear trend, 5-33

local templates, 3-11

M

move chart dialog box, 4-9

P

pie chart, 4-11, 4-22, 4-25, 4-28, 4-34

plot area, 5-4, 5-16, 5-17, 5-21, 5-25

polynomial trend, 5-33

power and exponential trend, 5-33

practical charting, 4-12


Index                                                                1

                                          R                                             

             radar chart, 4-36         

                                          S                                             

select data source dialog box, 4-21, 

4-22 shadows, 5-1, 5-2 Smart Art, 6-34

sorting key, 1-12, 1-14, 1-15

stacked column, 4-24, 4-25

standalone chart, 4-2

stock chart, 4-31, 4-32

SUBTOTAL(), 1-19, 1-29, 1-32, 1-33, 

2-10, 2-11, 2-16, 2-17 SUM(), 2-10, 2-11, 2-15

surface chart, 4-33

T

3-D column, 4-25 3-D line, 4-27

table styles, 1-5, 1-6

title, 5-2, 5-3, 5-4, 5-5, 5-6, 5-7, 5-12, 

5-14, 5-17, 5-23 trendline, 5-32

V

vector graphics, 6-5, 6-16, 6-17 vertical axis, 5-6

W

workgroup templates, 3-11, 3-16

X

XY scatter chart, 4-31

Index                                                                2



3