Excel2010 ebook

Chapter 32: Making Your Worksheets Error-Free Figure 32.8 shows an example of a worksheet displayed in two windows. The window on the top shows Normal view (formula results), and the window on the bottom displays the formulas. Choosing View ➪ Window ➪ View Side by Side, which allows synchronized scrolling, is also use- ful for viewing two windows. (See Chapter 4 for more information about this command.) FIGURE 32.8 Displaying formulas (bottom window) and their results (top window). Tracing cell relationships To understand how to trace cell relationships, you need to familiarize yourself with the following two concepts: l Cell precedents: Applicable only to cells that contain a formula, a formula cell’s prece- dents are all the cells that contribute to the formula’s result. A direct precedent is a cell that you use directly in the formula. An indirect precedent is a cell that isn’t used directly in the formula but is used by a cell that you refer to in the formula. l Cell dependents: These formula cells depend upon a particular cell. A cell’s dependents consist of all formula cells that use the cell. Again, the formula cell can be a direct depen- dent or an indirect dependent. For example, consider this simple formula entered into cell A4: =SUM(A1:A3) 663

Part IV: Using Advanced Excel Features Cell A4 has three precedent cells (A1, A2, and A3), which are all direct precedents. Cells A1, A2, and A3 each have a dependent cell (cell A4), and they’re all direct dependents. Identifying cell precedents for a formula cell often sheds light on why the formula isn’t working cor- rectly. Conversely, knowing which formula cells depend on a particular cell is also helpful. For exam- ple, if you’re about to delete a formula, you may want to check whether it has any dependents. Identifying precedents You can identify cells used by a formula in the active cell in a number of ways: l Press F2. The cells that are used directly by the formula are outlined in color, and the color corresponds to the cell reference in the formula. This technique is limited to identi- fying cells on the same sheet as the formula. l Display the Go to Special dialog box. (Choose Home ➪ Editing ➪ Find & Select ➪ Go to Special.) Select the Precedents option and then select either Direct Only (for direct precedents only) or All Levels (for direct and indirect precedents). Click OK, and Excel selects the precedent cells for the formula. This technique is limited to identifying cells on the same sheet as the formula. l Press Ctrl+[. This selects all direct precedent cells on the active sheet. l Press Ctrl+Shift+{. This selects all precedent cells (direct and indirect) on the active sheet. l Choose Formulas ➪ Formula Auditing ➪ Trace Precedents. Excel will draw arrows to indicate the cell’s precedents. Click this button multiple times to see additional levels of precedents. Choose Formulas ➪ Formula Auditing ➪ Remove Arrows to hide the arrows. Figure 32.9 shows a worksheet with precedent arrows drawn to indicate the precedents for the formula in cell C13. FIGURE 32.9 This worksheet displays arrows that indicate cell precedents for the formula in cell C13. 664

Chapter 32: Making Your Worksheets Error-Free Identifying dependents You can identify formula cells that use a particular cell in a number of ways: l Display the Go to Special dialog box. Select the Dependents option and then select either Direct Only (for direct dependents only) or All Levels (for direct and indirect dependents). Click OK. Excel selects the cells that depend upon the active cell. This tech- nique is limited to identifying cells on the active sheet only. l Press Ctrl+]. This selects all direct dependent cells on the active sheet. l Press Ctrl+Shift+}. This selects all dependent cells (direct and indirect) on the active sheet. l Choose Formulas ➪ Formula Auditing ➪ Trace Dependents. Excel will draw arrows to indicate the cell’s dependents. Click this button multiple times to see additional levels of dependents. Choose Formulas ➪ Formula Auditing ➪ Remove Arrows to hide the arrows. Tracing error values If a formula displays an error value, Excel can help you identify the cell that is causing that error value. An error in one cell is often the result of an error in a precedent cell. Activate a cell that con- tains an error value and then choose Formulas ➪ Formula Auditing ➪ Error Checking ➪ Trace Error. Excel draws arrows to indicate the error source. Fixing circular reference errors If you accidentally create a circular reference formula, Excel displays a warning message — Circular Reference — with the cell address, in the status bar, and also draws arrows on the worksheet to help you identify the problem. If you can’t figure out the source of the problem, choose Formulas ➪ Formula Auditing ➪ Error Checking ➪ Circular References. This command displays a list of all cells that are involved in the circular references. Start by selecting the first cell listed and then work your way down the list until you figure out the problem. Using background error-checking feature Some people may find it helpful to take advantage of the Excel automatic error-checking feature. This feature is enabled or disabled via the Enable Background Error Checking check box, found on the Formulas tab of the Excel Options dialog box, shown in Figure 32.10. In addition, you can use the check boxes in the Error Checking Rules section to specify which types of errors to check. When error checking is turned on, Excel continually evaluates the formulas in your worksheet. If a potential error is identified, Excel places a small triangle in the upper-left corner of the cell. When the cell is activated, a Smart Tag appears. Clicking this Smart Tag provides you with options. Figure 32.11 shows the options that appear when you click the Smart Tag in a cell that contains a #DIV/0! error. The options vary, depending on the type of error. 665

Part IV: Using Advanced Excel Features FIGURE 32.10 Excel can check your formulas for potential errors. FIGURE 32.11 After you click an error, Smart Tag gives you a list of options. In many cases, you will choose to ignore an error by selecting the Ignore Error option. Selecting this option eliminates the cell from subsequent error checks. However, all previously ignored errors can be reset so that they appear again. (Use the Reset Ignored Errors button in the Formulas tab of the Excel Options dialog box.) 666

Chapter 32: Making Your Worksheets Error-Free You can choose Formulas ➪ Formula Auditing ➪ Error Checking to display a dialog box that describes each potential error cell in sequence, much like using a spell-checking command. This command is available even if you disable background error checking. Figure 32.12 shows the Error Checking dialog box. This dialog box is modeless: that is, you can still access your worksheet when the Error Checking dialog box is displayed. Caution The error-checking feature isn’t perfect. In fact, it’s not even close to perfect. In other words, you can’t assume that you have an error-free worksheet simply because Excel doesn’t identify any potential errors! Also, be aware that this error-checking feature won’t catch a very common type of error: namely, overwriting a for- mula cell with a value. n FIGURE 32.12 Use the Error Checking dialog box to cycle through potential errors identified by Excel. Using the Excel Formula Evaluator Formula Evaluator lets you see the various parts of a nested formula evaluated in the order that the formula is calculated. To use Formula Evaluator, select the cell that contains the formula and then choose Formula ➪ Formula Auditing ➪ Evaluate Formula to display the Evaluate Formula dialog box (see Figure 32.13). FIGURE 32.13 The Evaluate Formula dialog box shows a formula being calculated one step at a time. 667

Part IV: Using Advanced Excel Features Click the Evaluate button to show the result of calculating the expressions within the formula. Each click of the button performs another calculation. This feature may seem a bit complicated at first, but if you spend some time working with it, you’ll understand how it works and see the value. Excel provides another way to evaluate a part of a formula: 1. Select the cell that contains the formula. 2. Press F2 to get into Cell Edit mode. 3. Use your mouse to highlight the portion of the formula you want to evaluate. Or, press Shift and use the navigation keys. 4. Press F9. The highlighted portion of the formula displays the calculated result. You can evaluate other parts of the formula or press Esc(ape) to cancel and return your formula to its previous state. Caution Be careful when using this technique because if you press Enter (rather than Esc), the formula will be modified to use the calculated values. n Searching and Replacing Excel has a powerful search-and-replace feature that makes it easy to locate information in a work- sheet or across multiple worksheets in a workbook. As an option, you can also search for text and replace it with other text. To access the Find and Replace dialog box, start by selecting the range that you want to search. If you select any single cell, Excel searches the entire sheet. Choose Home ➪ Editing ➪ Find & Select ➪ Find (or, click Ctrl+F). You’ll see the Find and Replace dialog box shown in Figure 32.14. If you’re simply looking for information in the worksheet, click the Find tab. If you want to replace existing text with new text, use the Replace tab. Also note that you can use the Options button to display (or hide) additional options. The dialog box shown in the figure displays these additional options. FIGURE 32.14 Use the Find and Replace dialog box to locate information in a worksheet or workbook. 668

Chapter 32: Making Your Worksheets Error-Free Searching for information Enter the information to search for in the Find What text box and then specify any of the following options. l Within drop-down list: Specify where to search (the current sheet or the entire workbook). l Search drop-down list: Specify the direction (by rows or by columns). l Look In drop-down list: Specify what cell parts to search (formulas, values, or comments). l Match Case check box: Specify whether the search should be case sensitive. l Match Entire Cell Contents check box: Specify whether the entire cell contents must be matched. l Format button: Click to search for cells that have a particular formatting (see the upcom- ing “Searching for formatting” section). Click Find Next to locate the matching cells one at a time or click Find All to locate all matches. If you use the Find All button, the Find and Replace dialog box expands to display the addresses of all matching cells in a list (see Figure 32.15). When you select an entry in this list, Excel scrolls the worksheet so that you can view it in context. Tip After using Find All, press Ctrl+A to select all the found cells. n FIGURE 32.15 Displaying the result of a search in the Find and Replace dialog box. 669

Part IV: Using Advanced Excel Features Note Because the Find and Replace dialog box is modeless, you can access the worksheet and make changes without the need to dismiss the dialog box. n Replacing information To replace text with other text, use the Replace tab in the Find and Replace dialog box. Enter the text to be replaced in the Find What field and then enter the new text in the Replace With field. Specify other options as described in the previous section. Click Find Next to locate the first matching item and then click Replace to do the replacement. When you click the Replace button, Excel then locates the next matching item. To override the replacement, click Find Next. To replace all items without verification, click Replace All. If the replacement didn’t occur as you planned, you can use the Undo button on the Quick Access tool- bar (or press Ctrl+Z). Tip To delete information, enter the text to be deleted in the Find What field but leave the Replace With field empty. n Searching for formatting From the Find and Replace dialog box, you can also locate cells that contain a particular type of formatting. As an option, you can replace that formatting with another type of formatting. For example, assume that you want to locate all cells that are formatted as bold and then change that formatting to bold and italic. Follow these steps: 1. Choose Home ➪ Editing ➪ Find & Select ➪ Replace to display the Find and Replace dialog box (or, press Ctrl+H). 2. Make sure that the Replace tab is displayed. 3. If the Find What and Replace With fields are not empty, delete their contents. 4. Click the top Format button to display the Find Format dialog box. This dialog box resembles the standard Format Cells dialog box. 5. In the Find Format dialog box, click the Font tab. 6. Select Bold in the Font Style list and then click OK. 7. Click the bottom Format button to display the Replace Format dialog box. 8. In the Replace Format dialog box, click the Font tab. 9. Select Bold Italic from the Font Style list and then click OK. At this point, the Find and Replace dialog box resembles Figure 32.16. Notice that it displays previews of the formatting that will be found and replaced. 670

Chapter 32: Making Your Worksheets Error-Free 10. In the Find and Replace dialog box, click Replace All. Excel locates all cells that have bold formatting and changes the formatting to bold italic. You can also find formatting based on a particular cell. In the Find Format dialog box, click the Choose Format from Cell button and then click the cell that contains the formatting you’re looking for. FIGURE 32.16 Use the Find and Replace dialog box to change formatting. Caution The Find and Replace dialog box cannot find background color formatting in tables that was applied using table styles, or formatting that is applied based on conditional formatting. n Spell Checking Your Worksheets If you use a word processing program, you probably take advantage of its spell checker feature. Spelling mistakes can be just as embarrassing when they appear in a spreadsheet. Fortunately, Microsoft includes a spell checker with Excel. To access the spell checker, choose Review ➪ Proofing ➪ Spelling, or press F7. To check the spell- ing in just a particular range, select the range before you activate the spell checker. If the spell checker finds any words it does not recognize as correct, it displays the Spelling dialog box, shown in Figure 32.17. Note The spell checker checks cell contents, text in graphic objects and charts, and page headers and footers. Even the contents of hidden rows and columns are checked. n 671

Part IV: Using Advanced Excel Features FIGURE 32.17 Use the Spelling dialog box to locate and correct spelling errors in your worksheets. The Spelling dialog box works similarly to other spell checkers with which you may be familiar. If Excel encounters a word that isn’t in the current dictionary or that is misspelled, it offers a list of suggestions. You can respond by clicking one of these buttons: l Ignore Once: Ignore the word and continues the spell check. l Ignore All: Ignore the word and all subsequent occurrences of it. l Add to Dictionary: Add the word to the dictionary. l Change: Change the word to the selected word in the Suggestions list. l Change All: Change the word to the selected word in the Suggestions list and change all subsequent occurrences of it without asking. l AutoCorrect: Add the misspelled word and its correct spelling (which you select from the list) to the AutoCorrect list. Using AutoCorrect AutoCorrect is a handy feature that automatically corrects common typing mistakes. You can also add words to the list that Excel corrects automatically. The AutoCorrect dialog box appears in Figure 32.18. To access this feature, choose File ➪ Options. In the Excel Options dialog box, click the Proofing tab and then click the AutoCorrect Options button. This dialog box has several options: l Correct TWo INitial CApitals: Automatically corrects words with two initial uppercase letters. For example, BUdget is converted to Budget. This mistake is common among fast typists. You can click the Exceptions button to specify a list of exceptions to this rule. l Capitalize First Letter of Sentences: Capitalizes the first letter in a sentence. All other letters are unchanged. 672

Chapter 32: Making Your Worksheets Error-Free l Capitalize Names of Days: Capitalizes the days of the week. If you enter monday, Excel converts it to Monday. l Correct Accidental Use of cAPS LOCK key: Corrects errors caused if you accidentally pressed the CapsLock key while typing. l Replace Text as You Type: AutoCorrect automatically changes incorrect words as you type them. FIGURE 32.18 Use the AutoCorrect dialog box to control the spelling corrections Excel makes automatically. Excel includes a long list of AutoCorrect entries for commonly misspelled words. In addition, it has AutoCorrect entries for some symbols. For example, (c) is replaced with ©, and (r) is replaced with ®. You can also add your own AutoCorrect entries. For example, if you find that you fre- quently misspell the word January as Janruary, you can create an AutoCorrect entry so that it’s changed automatically. To create a new AutoCorrect entry, enter the misspelled word in the Replace box and the correctly spelled word in the With field. You can also delete entries that you no longer need. Tip You also can use the AutoCorrect feature to create shortcuts for commonly used words or phrases. For exam- ple, if you work for a company named Consolidated Data Processing Corporation, you can create an AutoCorrect entry for an abbreviation, such as cdp. Then, whenever you type cdp, Excel automatically changes it to Consolidated Data Processing Corporation. Just make sure that you don’t use a combination of characters that might normally appear in your text. n 673

Part IV: Using Advanced Excel Features Note In some cases, you may want to override the AutoCorrect feature. For example, you may need to literally enter (c) rather than a copyright symbol. Just click the Undo button on the Quick Access toolbar or press Ctrl+Z. n You can use the AutoFormat as You Type tab of the AutoCorrect dialog box to control a few other automatic settings in Excel. Use the Smart Tags tab to make Excel show Smart Tags — similar to hyperlinks — for certain types of data in your worksheets. The types of Smart Tags Excel recognizes vary depending on the types of software that are installed on your system. The Math AutoCorrect tab contains shortcuts used to enter symbols when working in the Equation Editor (see Chapter 22). 674

Part V Analyzing Data with Excel E IN THIS PART xcel is a superb data analysis tool — if you know how to extract the information you really need. In this part, you’ll learn how to obtain and analyze data in Excel. As you’ll see, many of the data-analysis capabili- Chapter 33 Getting Data from External ties in Excel are both surprisingly powerful and easy to use. Database Files Chapter 34 Understanding Pivot Tables Chapter 35 Analyzing Data with Pivot Tables Chapter 36 Performing Spreadsheet What-If Analysis Chapter 37 Analyzing Data Using Goal Seeking and Solver Chapter 38 Analyzing Data with the Analysis ToolPak



CHAPTER Getting Data from External Database Files xcel has some great analysis and presentation tools, but these tools require data. In many cases, the data that you need is available in an IN THIS CHAPTER E external database. For example, your company may have a database Understanding external that contains customer information, sales data, and so on. This chapter is an database files introduction to retrieving data from external database files for use in Excel. Getting started using Microsoft Query Understanding External Working with external data Database Files When you work with an Excel workbook, the entire workbook must be loaded into memory before you can begin working. Although loading all the data provides you with immediate access to the entire file and all the data it contains, it also means that you can’t work with extremely large amounts of data. Although Excel 2010 supports more than a million rows, actually using that many rows can slow your system to a crawl — even if your system has plenty of memory. Note Using the 64-bit version of Excel allows you to work with much larger work- books. But for extremely large data sets, it’s usually more efficient to work with a subset of the data. n When you access an external database file using Excel, you can perform a query to load just a subset of the data into your workbook. 677

Part V: Analyzing Data with Excel Accessing external database files from Excel is useful in the following situations: l You need to work with a subset of a very large database. l The subset of data changes over time. It’s very easy to refresh a query and replace the old data with updated information. l The database is in a format that Excel can’t import, or the database may be too large to import. l The database contains multiple tables with relationships between those tables. If you need to work with data in an external database, you may prefer to use Excel rather than the tools available in database programs. The advantage? After you bring the data into Excel, you can manipulate and format it by using familiar tools such as formulas, charts, and pivot tables. Of course, real database programs, such as Access, have advantages, too. For example, creating a complex database report in Access may be easier than creating it in Excel. Understanding Some Database Terminology People who spend their days working with databases seem to have their own special language. The following terms can help you hold your own among a group of database experts: l External database: A collection of data stored in one or more files (not Excel files). A database contains one or more tables, and tables are composed of records and fields. l Field: A component of a database table, it corresponds to a column in Excel. l ODBC: Open DataBase Connectivity is a standard developed by Microsoft that uses drivers to access database files in different formats. Microsoft Query comes with drivers for Access, dBASE, FoxPro, Paradox, SQL Server, Excel workbooks, and ASCII text files. ODBC drivers for other databases are available from Microsoft and from third-party providers. l OLAP Cube: A multidimensional aggregate data source, often created from various other sources. OLAP is an acronym for OnLine Analytical Processing. l Query: Search a database for records that meet specific criteria. This term is also used as a noun; you can write a query, for example. l Record: In a database table, a single element that corresponds to a row. l Refresh: Rerun a query to get the latest data. It’s applicable when the database contains infor- mation that is subject to change, as in a multiuser environment. l Relational database: A database stored in more than one table or file. At least one common field (sometimes called the key field) connects the tables. l Result set: The data returned by a query, usually a subset of the original database. Query returns the result set to your Excel workbook or to a pivot table. (I discuss pivot tables in Chapter 34.) l SQL: An acronym for Structured Query Language (pronounced see-quel). Microsoft Query (discussed in this chapter) uses SQL to search data stored in ODBC databases. l Table: A record- and field-oriented collection of data. A database consists of one or more tables. 678

Chapter 33: Getting Data from External Database Files Importing Access Tables Microsoft Access is included with some versions of Office 2010. An Access database contains one or more tables of data, and you can import an Access table directly into a worksheet by choosing Data ➪ Get External Data ➪ From Access. When you choose this command, Excel displays the Select Data Source dialog box, which you use to locate the Access file. After you locate the Access database file, Excel displays the Select Table dialog box, from which you select the table (or view) to import. Figure 33.1 shows the tables and views available for the Microsoft Northwind Traders database, which is a sample Access database that you can download from the Microsoft Web site. Select the table that contains the data you need, click OK, and Excel displays its Import Data dialog box, shown in Figure 33.2. Use this dialog box to specify the loca- tion and whether you want a normal table or a pivot table. See Chapter 34 for information about pivot tables. FIGURE 33.1 Selecting an Access table to import. Note Importing an Access table in this manner is all or none. You’re not given an opportunity to query the database for specific records. n Figure 33.3 shows part of the Customers table from the Northwind Traders database. Excel con- verts the data to a table. You can use all of Excel’s tools to work with the data. Note, however, that you cannot make changes and send the changed data back to the Access database. 679

Part V: Analyzing Data with Excel FIGURE 33.2 Use this dialog box to specify the location of the imported table. FIGURE 33.3 An Access table, imported into Excel. Retrieving Data with Query: An Example This section discusses Microsoft Query, an application that ships with Excel. Use Query to import a subset of a database. Note To perform queries using external databases, Microsoft Query must be installed on your system. If Query isn’t installed, you’re prompted to install it when you first choose Data ➪ Get External Data ➪ From Other Sources ➪ From Microsoft Query. n The best way to become familiar with Microsoft Query is to walk through an example. In the following sections, you learn how to use Query to open a database file and import a specified set of records. 680

Chapter 33: Getting Data from External Database Files The database file The database file used in this example is a single-table Access file that consists of 31,680 records. This table contains the following fields: l Sort: A numeric field that holds record sequence numbers. l Division: A text field that specifies the company division (Asia, Europe, N. America, Pacific Rim, or S. America). l Department: A text field that specifies the department within the division. Each division is organized into the following departments: Accounting, Advertising, Data Processing, Human Resources, Operations, Public Relations, R&D, Sales, Security, Shipping, and Training. l Category: A text field that specifies the budget category. The four categories are Compensation, Equipment, Facility, and Supplies & Services. l Item: A text field that specifies the budget item. Each budget category has different budget items. For example, the Compensation category includes the following items: Benefits, Bonuses, Commissions, Conferences, Entertainment, Payroll Taxes, Salaries, and Training. l Month: A text field that specifies the month (abbreviated as Jan, Feb, and so on). l Year: A numeric field that stores the year (either 2008 or 2009). l Budget: A numeric field that stores the budgeted amount. l Actual: A numeric field that stores the actual amount spent. l Variance: A numeric field that stores the difference between the Budget and Actual. The task The objective of this exercise is to create a report that shows the first quarter 2009 (January through March) Compensation expenditures of the Training Department in the North American Division. In other words, the query will extract records that meet all the following criteria: l Division: N. America l Department: Training lCategory:Compensation l Month: Jan, Feb, or Mar l Year: 2009 681

Part V: Analyzing Data with Excel One approach to this task is to import the entire Access file into a worksheet using the method described earlier in this chapter (see “Importing Access Tables”). When the table is imported, you can filter it to display only the rows that meet the specified criteria. This approach works because this particular table isn’t very large. However, in some cases, the number of records in the table may exceed the number of rows in a worksheet. The advantage of using Query is that it imports only the data that’s required. And, after you import the data, you can refresh the query at any time to bring in updated data. Selecting a data source Begin with an empty worksheet. Choose Data ➪ Get External Data ➪ From Other Sources ➪ From Microsoft Query, which displays the Choose Data Source dialog box, as shown in Figure 33.4. This dialog box contains three tabs: l Databases: Lists the data sources that are known to Query. This tab may be empty, depending on which data sources are defined on your system. l Queries: Contains a list of stored queries. Again, this tab may or may not be empty. l OLAP Cubes: Lists OLAP databases available for query. FIGURE 33.4 The Choose Data Source dialog box. Your system may have some data sources already defined. If so, they appear in the list on the Databases tab. To set up a new data source, use the New Data Source option. For this example, choose New Data Source and click OK. The Create New Data Source dialog box, which has four parts, appears: 1. Enter a descriptive name for the data source. For this example, use the name Budget Database. 2. Select a driver for the data source by selecting from the list of installed drivers. For this example, choose Microsoft Access Driver (*.mdb, *.accdb). 682

Chapter 33: Getting Data from External Database Files 3. Click the Connect button to display another dialog box that asks for information specific to the driver that you selected in Step 2. In this example, you need to click the Select button and then locate the budget data.accdb file. Click OK to return to the previous dialog box and click OK again to return to the Create New Data Source dia- log box. 4. Select the default data table that you want to use. For this example, the database file contains a single table named budget. If the database requires a password, you can also specify that the password be saved with the data source definition. Figure 33.5 shows the Create New Data Source dialog box at this point. 5. After you supply all the information in the Create New Data Source dialog box, click OK. You’re returned to the Choose Data Source dialog box, which now displays the name of the data source that you created. FIGURE 33.5 The Create New Data Source dialog box. Note You have to go through these steps only once for each data source. The next time that you need to access this data source, the Budget Database (and any other database sources that you’ve defined) appears in the Choose Data Source dialog box. n Note The preceding steps are general steps that work with all supported database types. In some situations, you may prefer to open the database file directly and not create a named data source that will appear in the Choose Data Source dialog box. For example, if you won’t be using the database again, you can open the file directly and not have to bother creating a new named data source. If you’re using an Access file, you can select MS Access Database from the Databases tab in the Choose Data Source dialog box. Then, you can specify the file, and you’re taken directly to Microsoft Query. n 683

Part V: Analyzing Data with Excel Using Query with Excel Files You may have noticed that one of the options in the Choose Data Source dialog box is Excel Files. If you choose this option, you can use Query to bring in data stored in Excel workbooks. You can put any number of data tables in a workbook, but each one must have a range name because that’s how Query identifies the tables. Using the Query Wizard The Choose Data Source dialog box has a check box at the bottom that lets you specify whether to use the Query Wizard to create your query. (Refer to Figure 33.4.) The Query Wizard walks you through the steps used to create your query; if you use the Query Wizard, you don’t have to deal directly with Query. I highly recommend using the Query Wizard, and the examples in this chap- ter use this tool. In the Choose Data Source dialog box, do the following: 1. Select your data source (Budget Database, for this example). 2. Make sure that the Query Wizard check box is selected. 3. Click OK to start the Query Wizard. Query Wizard: Choosing the columns In the first step of the Query Wizard, select the database columns that you want to appear in your query. Select one or more columns and click the > button to add them (see Figure 33.6). To select all fields, click the table name (budget) and then click the > button. FIGURE 33.6 In the first step of Query Wizard, you select the columns to use in your query. 684

Chapter 33: Getting Data from External Database Files If you want to see the data for a particular column, select the column and click the Preview Now button. If you accidentally add a column that you don’t need, select it in the right panel and click the < button to remove it. For this example, add all the fields and then click the Next button. Query Wizard: Filtering data In the second step of the Query Wizard , specify your record selection criteria — how you want to filter the data. This step is optional. If you want to retrieve all the data, just click the Next button to proceed. Figure 33.7 shows the Filter Data dialog box of the Query Wizard. FIGURE 33.7 In the second step of the Query Wizard, you specify how you want to filter the data. For the example, not all records are needed. Recall that you’re interested only in the records in which all the following applies: l The Division is N. America. l The Department is Training. l The Category is Compensation. l The Year is 2009. l The Month is Jan, Feb, or Mar. The criteria are entered by column. In this case, you have five criteria (one for each of five columns): l From the Column to Filter list, select Division. In the right panel, select equals from the first drop-down list and then select N. America from the second drop-down list. l From the Column to Filter list, select Department. In the right panel, select equals from the first drop-down list and then select Training from the second drop-down list. 685

Part V: Analyzing Data with Excel l From the Column to Filter list, select Category. In the right panel, select equals from the first drop-down list and then select Compensation from the second drop-down list. l From the Column to Filter list, select Year. In the right panel, select equals from the first drop-down list and then select 2009 from the second drop-down list. l From the Column to Filter list, select Month. In the right panel, select equals from the first drop-down list and then select Jan from the second drop-down list. And because this col- umn is filtered by multiple values, select the Or option button and then select equals and Feb from the drop-down lists in the second row. Finally, select equals and Mar from the drop-down lists in the third row. To review the criteria that you entered, select the column from the Column to Filter list. The Query Wizard displays the criteria that you entered for the selected column. Notice that columns that are used in the query display in bold. After you enter all the criteria, click Next. Query Wizard: Sort order The third step of the query lets you specify how you want the records to be sorted (see Figure 33.8). This step is optional, and you can click Next to move to the next step if you don’t want the data sorted or if you prefer to sort it after it’s returned to your worksheet. For this example, sort by Category in ascending order. You can specify as many sort fields as you like. Click Next to move on to the next step. Query Wizard: Finish The final step of the Query Wizard, shown in Figure 33.9, lets you save the query so that you can reuse it. To save the query, click Save Query and then enter a filename. Select an option that corresponds to what you want to do with the returned data. Normally, you want to return the data to Excel. If you know how to use the Microsoft Query application, you can return the data to Query and examine it or even modify the selection criteria. FIGURE 33.8 In the third step of the Query Wizard, you specify the sort order. 686

Chapter 33: Getting Data from External Database Files For this example, select the Return Data to Microsoft Excel option button and then click Finish. FIGURE 33.9 The final step of the Query Wizard. Specifying a location for the data Figure 33.10 shows the Import Data dialog box, which appears after you click the Finish button in the Query Wizard. For this example, select the Table option button and place the data beginning in cell A1 of the existing worksheet. Click OK to import the data. Note If you choose PivotTable Report, or PivotChart and PivotTable Report, you can specify the layout for a pivot table (see Chapters 34 and 35). In such a case, the database is used as the source for the pivot table, and the original data table does not appear in your workbook. n FIGURE 33.10 Specifying what to do with the data. Figure 33.11 shows the data that is returned to a worksheet. 687

Part V: Analyzing Data with Excel FIGURE 33.11 The results of the query. Working with Data Returned by Query Excel stores the data that Query returns in either a worksheet or a pivot table cache. When Excel stores data in a worksheet, it stores the data in a table that’s a specially named range known as an external data range; Excel creates the name for this range automatically. In this example, the external data range is named Table_Query_from_Budget_Database. You can manipulate data returned from a query just like any other worksheet range. For example, you can sort the data, format it, or create formulas that use the data. The following sections describe what you can do with the data that Excel receives from Query and stores in a worksheet. Adjusting the external data range properties You can adjust various properties of the external data range by using the External Data Properties dialog box (see Figure 33.12). To display this dialog box, the cell pointer must be within the external data range. Open this dialog box by using either of these methods: l Right-click and choose Table ➪ External Data Properties from the shortcut menu. l Choose Data ➪ Connections ➪ Properties. 688

Chapter 33: Getting Data from External Database Files For more settings (applicable for advanced users), click the Properties icon, which is directly to the right of the Name field in the External Data Properties dialog box. Excel displays the Connection Properties dialog box. FIGURE 33.12 The External Data Properties dialog box enables you to specify various options for an external data range. Refreshing a query After performing a query, you can save the workbook file and then retrieve it later. The file con- tains the data that you originally retrieved from the external database. The external database may have changed, however, in the interim. Fortunately, Excel saves the query definition with the workbook. Simply move the cell pointer anywhere within the external data table in the worksheet and then use one of the following methods to refresh the query: l Right-click and choose Refresh from the shortcut menu. l Choose Data ➪ Connections ➪ Refresh All. l Click Refresh in the Workbook Connections dialog box (displayed by choosing Data ➪ Connections ➪ Connections). Excel uses your original query to bring in the current data from the external database. Tip If you find that refreshing the query causes undesirable results, use the Undo button to “unrefresh” the data. n Note A single workbook can hold as many external data ranges as you need. Excel gives each query a unique name, and you can work with each query independently. Excel automatically keeps track of the query that produces each external data range. n 689

Part V: Analyzing Data with Excel Caution After performing a query, you may want to copy or move the external data range, which you can do by using the normal copy, cut, and paste techniques. However, make sure that you copy or cut the entire external data range: Otherwise, the underlying query is not copied, and the copied data can’t be refreshed. n Deleting a query If you decide that you no longer need the data returned by a query, you can delete it by selecting the entire external data range and pressing Delete. Excel displays a warning and asks you to verify your intentions. Your data source definition remains intact, so you can always re-specify your original query. Changing your query If you bring the query results into your worksheet and discover that you don’t have what you want, you can modify the query. Move the cell pointer anywhere within the external data table in the work- sheet. Right-click and choose Table ➪ Edit Query from the shortcut menu. You need to edit the query using Microsoft Query. See the next section to learn how to work with Query directly. Using Query without the Wizard When you choose Data ➪ Get External Data ➪ From Other Sources ➪ From Microsoft Query, the Choose Data Source dialog box gives you the option of whether to use Query Wizard to create your query. If you choose not to use Query Wizard, Microsoft Query is launched in a new win- dow. You also work directly with Query if you choose to edit a query that was created with Query Wizard. Note Microsoft Query is a relatively old application, and its user interface hasn’t been updated to match the other Office programs. It works fine. It just looks old-fashioned. n Creating a query manually Before you can create a query, you must display the Criteria pane. In Query, open the View menu and place a check next to the Criteria menu item. The Criteria pane appears in the middle of the window. Figure 33.13 shows Microsoft Query, after selecting the Budget Database from the Choose Data Source dialog box. 690

Chapter 33: Getting Data from External Database Files FIGURE 33.13 Display the Criteria pane as shown here so that you’ll be able to create your query. Tables pane Criteria pane Data pane The Query window has three panes, which are split horizontally: l Tables pane: The top pane, which holds the selected data tables for the database. Each data table window has a list of the fields in the table. l Criteria pane: The middle pane, which holds the criteria that determine the rows that the query returns. l Data pane: The bottom pane, which holds the data that passes the criteria. Creating a query consists of the following steps: 1. Drag fields from the Tables pane to the Data pane. You can drag as many fields as you want. These fields are the columns that the query will return. You can also double-click a field instead of dragging it. 2. Enter criteria in the Criteria pane. When you activate this pane, the first row (labeled Criteria Field) displays a drop-down list that contains all the field names. Select a field and enter the criteria below it. Query updates the Data pane automatically, treating each row like an OR operator. 3. Choose File ➪ Return Data to Microsoft Excel to execute the query and place the data in a worksheet or pivot table. 691

Part V: Analyzing Data with Excel Figure 33.14 shows how the query for the example presented earlier in this chapter appears in Query. Recall that the goal is to retrieve records in which all of the following applies: l The Division is N. America. l The Department is Training. l The Category is Compensation. l The Year is 2009. l The Month is Jan, Feb, or Mar. FIGURE 33.14 Add the fields and criteria to complete your query. Tip Double-clicking a criteria box to display the Edit Criteria dialog box enables you to select an operator and value. n Using multiple database tables The example in this chapter uses only one database table. Some databases, however, use multiple tables. These databases are relational databases because a common field links the tables. Query lets you use any number of tables in your queries. 692

Chapter 33: Getting Data from External Database Files Note When you add tables to a query, the Tables pane in Query connects the linked fields with a line between the tables. If no links exist, you can create a link yourself by dragging a field from one table to the corresponding field in the other table. n Adding and editing records in external database tables To add, delete, and edit data when you’re using Query, make sure that a check mark appears next to the Records ➪ Allow Editing menu item. Of course, you’ll need the proper permissions, and you can’t edit a database file that’s set up as read-only. Caution Be careful with this feature because your changes are saved to disk as soon as you move the cell pointer out of the record that you’re editing. You do not need to choose File ➪ Save. n Formatting data If you don’t like the data’s appearance in the Data pane, you can change the font used by choosing Format ➪ Font. Be aware that selective formatting isn’t allowed (unlike in Excel); changing the font affects all the data in the Data pane. Tip If you need to view the data in the Data pane in a different order, choose Records ➪ Sort (or click the Sort Ascending or Sort Descending toolbar icon). n Learning More about Query This chapter isn’t intended to cover every aspect of Microsoft Query; rather, it discusses the basic features that are used most often. In fact, if you use the Query Wizard, you may never need to interact with Query itself. But if you do need to use Query, you can experiment and consult the online Help to learn more. As with anything related to Excel, the best way to master Query is to use it — preferably with data that’s meaningful to you. 693



CHAPTER Introducing Pivot Tables he Pivot Table feature is perhaps the most technologically sophisti- cated component in Excel. With only a few mouse clicks, you can IN THIS CHAPTER T slice and dice a data table in dozens of different ways and produce An introduction to pivot tables just about any type of summary you can think of. Types of data appropriate for a If you haven’t yet discovered the power of pivot tables, this chapter provides pivot table an introduction, and Chapter 35 continues with many examples that demon- strate how easy it is to create powerful data summaries using pivot tables. Pivot table terminology How to create pivot tables About Pivot Tables Pivot table examples that answer specific questions about data A pivot table is essentially a dynamic summary report generated from a data- base. The database can reside in a worksheet (in the form of a table) or in an external data file. A pivot table can help transform endless rows and columns of numbers into a meaningful presentation of the data. For example, a pivot table can create frequency distributions and cross- tabulations of several different data dimensions. In addition, you can display subtotals and any level of detail that you want. Perhaps the most innovative aspect of a pivot table is its interactivity. After you create a pivot table, you can rearrange the information in almost any way imaginable and even insert special formulas that perform new calculations. You even can create post hoc groupings of summary items (for example, combine Northern Region totals with Western Region totals). And the icing on the cake: With a few mouse clicks, you can apply formatting to a pivot table to convert it into an attrac- tive report. 695

Part V: Analyzing Data with Excel One minor drawback to using a pivot table is that unlike a formula-based summary report, a pivot table does not update automatically when you change information in the source data. This draw- back doesn’t pose a serious problem, however, because a single click of the Refresh button forces a pivot table to update itself with the latest data. Pivot tables were introduced in Excel 97. Unfortunately, many users overlook this feature because they think it’s too complicated. The pivot table feature was improved significantly in Excel 2007, and you’ll find a few new twists in Excel 2010. Creating and working with pivot tables is easier than ever. A pivot table example The best way to understand the concept of a pivot table is to see one. Start with Figure 34.1, which shows a portion of the data used in creating the pivot table in this chapter. FIGURE 34.1 This table is used to create a pivot table. This table consists of a month’s worth of new account information for a three-branch bank. The table contains 712 rows, and each row represents a new account. The table has the following columns: 696

Chapter 34: Introducing Pivot Tables l The date the account was opened l The day of the week the account was opened l The opening amount l The account type (CD, checking, savings, or IRA) l Who opened the account (a teller or a new-account representative) l The branch at which it was opened (Central, Westside, or North County) l The type of customer (an existing customer or a new customer) The bank accounts database contains quite a bit of information. In its current form, though, the data doesn’t reveal much. To make the data more useful, you need to summarize it. Summarizing a database is essentially the process of answering questions about the data. Following are a few ques- tions that may be of interest to the bank’s management: l What is the daily total new deposit amount for each branch? l Which day of the week accounts for the most deposits? l How many accounts were opened at each branch, broken down by account type? l What’s the dollar distribution of the different account types? l What types of accounts do tellers open most often? l How does the Central branch compare with the other two branches? l In which branch do tellers open the most checking accounts for new customers? You can, of course, spend time sorting the data and creating formulas to answer these questions. But almost always, a pivot table is a better choice. Creating a pivot table takes only a few seconds, doesn’t require a single formula, and produces a nice-looking report. In addition, pivot tables are much less prone to error than creating formulas. (Later in this chapter, you’ll see several pivot tables that answer the preceding questions.) Figure 34.2 shows a pivot table created from the bank data. This pivot table shows the amount of new deposits, broken down by branch and account type. This particular summary represents one of dozens of summaries that you can produce from this data. Figure 34.3 shows another pivot table generated from the bank data. This pivot table uses a drop- down Report Filter for the Customer item (in row 1). In the figure, the pivot table displays the data only for Existing customers. (The user can also select New or All from the drop-down control.) Notice the change in the orientation of the table? For this pivot table, branches appear as column labels, and account types appear as row labels. This change, which took about five seconds to make, is another example of the flexibility of a pivot table. 697

Part V: Analyzing Data with Excel FIGURE 34.2 A simple pivot table. FIGURE 34.3 A pivot table that uses a report filter. Data appropriate for a pivot table A pivot table requires that your data is in the form of a rectangular database. You can store the database in either a worksheet range (which can be a table or just a normal range) or an external database file. And although Excel can generate a pivot table from any database, not all databases benefit. Generally speaking, fields in a database table consist of two types: l Data: Contains a value or data to be summarized. For the bank account example, the Amount field is a data field. l Category: Describes the data. For the bank account data, the Date, AcctType, OpenedBy, Branch, and Customer fields are category fields because they describe the data in the Amount field. Note A database table that’s appropriate for a pivot table is said to be “normalized.” In other words, each record (or row) contains information that describes the data. n 698

Chapter 34: Introducing Pivot Tables Why “Pivot?” Are you curious about the term “pivot?” Pivot, as a verb, means to rotate or revolve. If you think of your data as a physical object, a pivot table lets you rotate the data summary and look at it from different angles or perspectives. A pivot table allows you to move fields around easily, nest fields within each other, and even create ad hoc groups of items. If you were handed a strange object and asked to identify it, you’d probably look at it from several differ- ent angles in an attempt to figure it out. Working with a pivot table is similar to investigating a strange object. In this case, the object happens to be your data. A pivot table invites experimentation, so feel free to rotate and manipulate the pivot table until you’re satisfied. You may be surprised at what you discover. A single database table can have any number of data fields and category fields. When you create a pivot table, you usually want to summarize one or more of the data fields. Conversely, the values in the category fields appear in the pivot table as rows, columns, or filters. Exceptions exist, however, and you may find the Excel Pivot Table feature useful even for data- bases that don’t contain actual numerical data fields. Chapter 35 has an example of a pivot table created from non-numeric data. Figure 34.4 shows an example of an Excel range that is not appropriate for a pivot table. You might recognize this data from the outline example in Chapter 26. Although the range contains descriptive information about each value, it does not consist of normalized data. In fact, this range actually resembles a pivot table summary, but it is much less flexible. FIGURE 34.4 This range is not appropriate for a pivot table. Figure 34.5 shows the same data, but normalized. This range contains 78 rows of data — one for each of the six monthly sales values for the 13 states. Notice that each row contains category informa- tion for the sales value. This table is an ideal candidate for a pivot table, and contains all information necessary to summarize the information by region or quarter. 699

Part V: Analyzing Data with Excel FIGURE 34.5 This range contains normalized data and is appropriate for a pivot table. Figure 34.6 shows a pivot table created from the normalized data. As you can see, it’s virtually identical to the non-normalized data shown in Figure 34.4. FIGURE 34.6 A pivot table created from normalized data. 700

Chapter 34: Introducing Pivot Tables Creating a Pivot Table In this section, I describe the basic steps required to create a pivot table, using the bank account data described earlier in this chapter. Creating a pivot table is an interactive process. It’s not at all uncommon to experiment with various layouts until you find one that you’re satisfied with. If you’re unfamiliar with the elements of a pivot table, see the upcoming sidebar, “Pivot Table Terminology.” Specifying the data If your data is in a worksheet range, select any cell in that range and then choose Insert ➪ Tables ➪ PivotTable, which displays the dialog box shown in Figure 34.7. Excel attempts to guess the range, based on the location of the active cell. If you’re creating a pivot table from an external data source, you need to select that option and then click Choose Connection to specify the data source. Tip If you’re creating a pivot table from data in a worksheet, it’s a good idea to first create a table for the range (choose Insert ➪ Tables ➪ Table). Then, if you expand the table by adding new rows of data, Excel will refresh the pivot table without the need to manually indicate the new data range. n FIGURE 34.7 In the Create PivotTable dialog box, you tell Excel where the data is and where you want the pivot table. Specifying the location for the pivot table Use the bottom section of the Create PivotTable dialog box to indicate the location for your pivot table. The default location is on a new worksheet, but you can specify any range on any worksheet, including the worksheet that contains the data. 701

Part V: Analyzing Data with Excel Click OK, and Excel creates an empty pivot table and displays its PivotTable Field List task pane, as shown in Figure 34.8. FIGURE 34.8 Use the PivotTable Field List to build the pivot table. Tip The PivotTable Field List is typically docked on the right side of the Excel window. Drag its title bar to move it anywhere you like. Also, if you click a cell outside the pivot table, the PivotTable Field List is hidden. n Laying out the pivot table Next, set up the actual layout of the pivot table. You can do so by using either of any techniques: l Drag the field names (at the top) to one of the four boxes at the bottom of the PivotTable Field List. l Place a check mark next to the item at the top of the PivotTable Field List. Excel will place the field into one of the four boxes at the bottom. l Right-click a field name at the top of the PivotTable Field List and choose its location from the shortcut menu. 702

Chapter 34: Introducing Pivot Tables Note In versions prior to Excel 2007, you could drag items from the field list directly into the appropriate area of the pivot table. This feature is still available, but it’s turned off by default. To enable this feature, choose PivotTable Tools ➪ Options ➪ PivotTable Options ➪ Options to display the PivotTable Options dialog box. Click the Display tab and then select the Classic PivotTable Layout check box. n The following steps create the pivot table presented earlier in this chapter (see “A pivot table exam- ple”). For this example, I drag the items from the top of the PivotTable Field List to the areas in the bottom of the PivotTable Field List. 1. Drag the Amount field into the Values area. At this point, the pivot table displays the total of all the values in the Amount column. 2. Drag the AcctType field into the Row Labels area. Now the pivot table shows the total amount for each of the account types. 3. Drag the Branch field into the Column Labels area. The pivot table shows the amount for each account type, cross-tabulated by branch (see Figure 34.9). The pivot table updates itself automatically with every change you make in the PivotTable Field List. FIGURE 34.9 After a few simple steps, the pivot table shows a summary of the data. Formatting the pivot table Notice that the pivot table uses General number formatting. To change the number format for all data, right-click any value and choose Number Format from the shortcut menu. Then use the Format Cells dialog box to change the number format for the displayed data. 703

Part V: Analyzing Data with Excel You can apply any of several built-in styles to a pivot table. Select any cell in the pivot table and then choose PivotTable Tools ➪ Design ➪ PivotTable Styles to select a style. You also can use the controls from the PivotTable ➪ Design ➪ Layout group to control various ele- ments in the pivot table. For example, you can choose to hide the grand totals if you prefer. The PivotTable Tools ➪ Options ➪ Show group contains additional options that affect the appear- ance of your pivot table. For example, you use the Show Field Headers button to toggle the display of the field headings. Still more pivot table options are available from the PivotTable Options dialog box, shown in Figure 34.10. To display this dialog box, choose PivotTable Tools ➪ Options ➪ PivotTable ➪ Options. Or, right-click any cell in the pivot table and choose Table Options from the shortcut menu. Pivot Table Terminology Understanding the terminology associated with pivot tables is the first step in mastering this feature. Refer to the accompanying figure to get your bearings. l Column labels: A field that has a column orientation in the pivot table. Each item in the field occupies a column. In the figure, Customer represents a column field that contains two items (Existing and New). You can have nested column fields. l Grand totals: A row or column that displays totals for all cells in a row or column in a pivot table. You can specify that grand totals be calculated for rows, columns, or both (or neither). The pivot table in the figure shows grand totals for both rows and columns. l Group: A collection of items treated as a single item. You can group items manually or auto- matically (group dates into months, for example). The pivot table in the figure does not have any defined groups. 704

Chapter 34: Introducing Pivot Tables l Item: An element in a field that appears as a row or column header in a pivot table. In the figure, Existing and New are items for the Customer field. The Branch field has three items: Central, North County, and Westside. AcctType has four items: CD, Checking, IRA, and Savings. l Refresh: Recalculates the pivot table after making changes to the source data. l Row labels: A field that has a row orientation in the pivot table. Each item in the field occupies a row. You can have nested row fields. In the figure, Branch and AcctType both represent row fields. l Source data: The data used to create a pivot table. It can reside in a worksheet or an external database. l Subtotals: A row or column that displays subtotals for detail cells in a row or column in a pivot table. The pivot table in the figure displays subtotals for each branch. l Table Filter: A field that has a page orientation in the pivot table — similar to a slice of a three-dimensional cube. You can display only one item (or all items) in a page field at one time. In the figure, OpenedBy represents a page field that displays All (that is, not filtered). In previous version of Excel, a table filter was known as a Page field. l Values area: The cells in a pivot table that contain the summary data. Excel offers several ways to summarize the data (sum, average, count, and so on). FIGURE 34.10 The PivotTable Options dialog box. 705

Part V: Analyzing Data with Excel Modifying the pivot table After you create a pivot table, changing it is easy. For example, you can add further summary information by using the PivotTable Field List. Figure 34.11 shows the pivot table after I dragged a second field (OpenedBy) to the Row Labels section in the PivotTable Field List. FIGURE 34.11 Two fields are used for row labels. The following are some tips on other pivot table modifications you can make: l To remove a field from the pivot table, select it in the bottom part of the PivotTable Field List and then drag it away. l If an area has more than one field, you can change the order in which the fields are listed by dragging the field names. Doing so affects the appearance of the pivot table. l To temporarily remove a field from the pivot table, remove the check mark from the field name in the top part of the PivotTable Field List. The pivot table is redisplayed without that field. Place the check mark back on the field name, and it appears in its previous section. l If you add a field to the Report Filter section, the field items appear in a drop-down list, which allows you to filter the displayed data by one or more items. Figure 34.12 shows an example. I dragged the Date field to the Report Filter area. The report is now showing the data only for a single day (which I selected from the drop-down list in cell B1). 706

Chapter 34: Introducing Pivot Tables Pivot Table Calculations Pivot table data is most frequently summarized using a sum. However, you can display your data using a number of different summary techniques. Select any cell in the Values area of your pivot table and then choose PivotTable Tools ➪ Options ➪ Active Field ➪ Field Settings to display the Value Field Settings dialog box. This dialog box has two tabs: Summarize Values By and Show Values As. Use the Summarize Values By tab to select a different summary function. Your choices are Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev, StdDevp, Var, and Varp. To display your values in a different form, use the drop-down control on the Show Values As tab. You have many options to choose from, including as a percentage of the total or subtotal. FIGURE 34.12 The pivot table is filtered by date. 707

Part V: Analyzing Data with Excel Copying a Pivot Table A pivot table is very flexible, but it does have some limitations. For example, you can’t add new rows or columns, change any of the calculated values, or enter formulas within the pivot table. If you want to manipulate a pivot table in ways not normally permitted, make a copy of it. To copy a pivot table, select the entire table and choose Home ➪ Clipboard ➪ Copy (or, press Ctrl+C). Then select a new worksheet and choose Home Clipboard ➪ Paste ➪ Paste Values. The pivot table formatting is not copied — even if you repeat the operation and use the Formats option in the Paste Special dialog box. To copy the pivot table and its formatting, use the Office Clipboard to paste. If the Office Clipboard is not displayed, click the dialog box launcher in the bottom right of the Home ➪ Clipboard group. The contents of the pivot table are copied to the new location so that you can do whatever you like to them. Note that the copied information is not a pivot table, and it is no longer linked to the source data. If the source data changes, your copied pivot table will not reflect these changes. More Pivot Table Examples To demonstrate the flexibility of this feature, I created some additional pivot tables. The examples use the bank account data and answer the questions posed earlier in this chapter (see “A pivot table example”). Question 1 What is the daily total new deposit amount for each branch? Figure 34.13 shows the pivot table that answers this question. l The Branch field is in the Column Labels section. l The Date field is in the Row Labels section. l The Amount field is in the Values section and is summarized by Sum. Note that the pivot table can also be sorted by any column. For example, you can sort the Grand Total column in descending order to find out which day of the month had the largest amount of new funds. To sort, just right-click any cell in the column to sort and choose Sort from the short- cut menu. 708

Chapter 34: Introducing Pivot Tables FIGURE 34.13 This pivot table shows daily totals for each branch. Question 2 Which day of the week accounts for the most deposits? Figure 34.14 shows the pivot table that answers this question. l The Weekday field is in the Row Labels section. l The Amount field is in the Values section and is summarized by Sum. I added conditional formatting data bars to make it easier to see how the days compare. Cross-Reference See Chapter 20 for more information about conditional formatting. n 709

Part V: Analyzing Data with Excel FIGURE 34.14 This pivot table shows totals by day of the week. Question 3 How many accounts were opened at each branch, broken down by account type? Figure 34.15 shows a pivot table that answers this question. l The AcctType field is in the Column Labels section. l The Branch field is in the Row Labels section. l The Amount field is in the Values section and is summarized by Count. The most common summary function used in pivot tables is Sum. In this case, I changed the sum- mary function to Count. To change the summary function to Count, right-click any cell in the Value area and choose Summarize Data By ➪ Count from the shortcut menu. FIGURE 34.15 This pivot table uses the Count function to summarize the data. Question 4 What’s the dollar distribution of the different account types? Figure 34.16 shows a pivot table that answers this question. For example, 253 of the new accounts were for an amount of $5,000 or less. 710

Chapter 34: Introducing Pivot Tables FIGURE 34.16 This pivot table counts the number of accounts that fall into each value range. This pivot table is unusual because it uses only one field: Amount. l The Amount field is in the Row Labels section (grouped). l The Amount field is also in the Values section and is summarized by Count. l A third instance of the Amount field is the Values section, summarized by Percent of Total. When I initially added the Amount field to the Row Labels section, the pivot table showed a row for each unique dollar amount. I right-clicked one of the Row Labels and chose Group from the shortcut menu. Then I used the Grouping dialog box to set up bins of $5,000 increments. The second instance of the Amount field (in the Values section) is summarized by Count. I right- clicked a value and chose Summarize Data By ➪ Count from the shortcut menu. I added another instance of Amount to the Values section, and I set it up to display the percentage. I right-clicked a value in column C and chose Show Values As ➪ % of Grand Total. This option is also available in the Show Values As tab of the Value Field Settings dialog box. Question 5 What types of accounts do tellers open most often? Figure 34.17 shows that the most common account opened by tellers is a Checking account. l The AcctType field is in the Row Labels section. l The OpenedBy field is in the Report Filters section. 711

Part V: Analyzing Data with Excel l The Amount field is in the Values section (summarized by Count). l A second instance of the Amount field is in the Values section (summarized by % of Total). This pivot table uses the OpenedBy field as a Report Filter and is showing the data only for Tellers. I sorted the data so that the largest value is at the top, and I also used conditional formatting to display data bars for the percentages. Cross-Reference See Chapter 20 for more information about conditional formatting. n FIGURE 34.17 This pivot table uses a Report Filter to show only the Teller data. Question 6 How does the Central branch compare with the other two branches? Figure 34.18 shows a pivot table that sheds some light on this rather vague question. It simply shows how the Central branch compares with the other two branches combined. l The AcctType field is in the Row Labels section. l The Branch field is in the Column Labels section. l The Amount field is in the Values section. I grouped the North County and Westside branches together and named the group Other. The pivot table shows the amount, by account type. I also created a pivot chart for good measure. See Chapter 35 for more information about pivot charts. 712