One solution would be to set up four different criteria and four different DSUM() functions. For example, if the company in the preceding example has four regions, you might want to see separate totals for each region, broken down by quarter. The next level of database analysis involves applying a single question to multiple variables. To get more specific information, such as total East region sales in the second quarter, you just add the appropriate conditions to your criteria. You could either subtotal by region or set up your criteria to match all territories in the East region and use the DSUM() function to get the total. For example, suppose that each sales territory is part of a larger region, and you want to know the total sales in the East region. You can then apply subtotals and the table functions (also described in Chapter 13) to find answers to your questions. The next level of complexity involves more sophisticated lookup and retrieval systems, in which the criteria and extraction techniques discussed in Chapter 13, “ Analyzing Data with Tables,” are used. For example, if you have a database that lists the company sales reps and their territory sales, you could search for a specific rep to look up the sales in that rep’s territory. The simplest level involves the basic lookup and retrieval of information. Database analysis has several levels of complexity. To understand PivotTables, you need to see how they fit in with Excel’s other database analysis features. Instead, I focus on the extensive work you can do with built-in and custom PivotTable calculations. Because this is a book about Excel formulas and functions, I don’t go into tons of detail on building and customizing PivotTables. This chapter introduces you to PivotTables and shows you various ways to use them with your own data.

You can then manipulate the layout of the table to see different views of your data. This tool enables you to summarize hundreds of records in a concise tabular format. To help you, Excel offers a powerful data analysis tool called a PivotTable. Analyzing that much data can be a nightmare without the right kinds of tools. Tables and external databases can contain hundreds or even thousands of records.

Excel getpivotdata function true