• Powerful and sophisticated data-analysis tools in Excel. 

• Extract meaning (Information) from large amounts of Data. (Example of raw material of Tea).

• Do things that would be impossible or difficult to do any other way.

It comes from an analogy between the way PivotTables work and the way you investigate a physical object.

Imagine that you have been handed a complex device and asked to figure out what it does. You don’t just look at it from one angle; rather you turn it in your hands, examining it from all possible perspectives to be sure you do not miss any important clues.

PivotTables work the same way, enabling you to turn or pivot the raw data and examine it from various perspectives to extract the information you need.

Then you also have the option of creating a PivotChart, a graphical representation of the information in a PivotTable.

The tables and charts have not themselves

changed much, but the procedures you use to create and work with them have been

streamlined and simplified.

Some of the older techniques are still supported (old wizard to use Multiple Consolidation Ranges option = Card 7) & (Classic PivotTable Layout = Card 45)

1-Open the workbook that contains the raw data (table of data or data range).

2-Make sure that the cell pointer is on any cell of the data.

3-Click the PivotTable button on the Insert ribbon.

4-Excel displays the Create PivotTable dialog box, make sure that the options are selected: 

• Select a table or range

• New Worksheet

& verify that the correct range is entered.

5-Then click the OK.

Define what data will be in the PivotTable.

In the PivotTable Field List, click the items  that you need, to place a check mark next to it.

1-Arranging ITEMS in the 4 areas boxes of the Field List.

2-Adjust the settings of ITEMS present in the Value area box (as required) by clicking the down arrow next to the items and select Value Field Settings.

3-Modify the report by Sorting and Filtering through the table itself (e.g. down arrow of rows heading & Columns heading and Report Filter).

1-Make sure the cell pointer is on a cell in the table.

2-Press Alt+D followed by P to open the PivotTable and PivotChart Wizard.

3-Make sure that the options are selected as shown:

• Select Microsoft Office Excel List or Database

• Select PivotTable

4-Then click the Next button to specify the range where the data is located.

5-Click the Next to specify where to place the PivotTable, either on a new worksheet or an existing worksheet.

6-Click Finish to create the PivotTable report.

Standard Excel chart created from the data in a PivotTable report.

PivotChart is like any other Excel chart and can be manipulated and formatted in the same way.

The few differences are : 

1. PivotChart and PivotChart Filter Pane (To filter the Chart & the Table at the same time).

Row/Column orientation    Unlike a standard chart, you cannot switch the row/column orientation of a PivotChart report by using the Select Data Sourcedialog box. However, you can pivot the Row and Column labels of the associated PivotTable report to achieve the same effect.

Chart types     You can change a PivotChart report to any chart type except an xy (scatter), stock, or bubble chart.

Source data     Standard charts are linked directly to worksheet cells. PivotChart reports are based on the data source of the associated PivotTable report. Unlike a standard chart, you cannot change the chart data range in the Select Data Sourcedialog box of a PivotChart report.

Formatting     Most formatting — including chart elements that you add, layout, and style — is preserved when you refresh a PivotChart report. However, trendlines, data labels, error bars, and other changes to data sets are not preserved. Standard charts do not lose this formatting once it is applied.

Although you cannot directly resize the data labels in a PivotChart report, you can increase the font size of the text to effectively resize the labels

1-Create a PivotTable.

2-Make sure the PivotTable is active.

3-Click the PivotChart button on the Options ribbon. Excel displays the Insert Chart dialog box, select any template and click OK to create the chart.

4-Excel displays the PivotChart and PivotChart Filter Pane (To filter the Chart & the Table at the same time).

Click the arrow underneath the PivotTable button on the Insert ribbon and then select PivotChart from the menu.

You’ll then follow the usual procedures for creating a PivotTable, but when Excel creates the PivotTable it will automatically create a PivotChart as well.

◆ Excel workbook

1. Same Workbook

2. Another Workbook

3. Multiple Consolidation Range

4. Based on Another PivotTable report

◆ Databases and other sources

e.g. Access database

The data should be organized as a standard Excel list, or table:

• The first row contains the field or column names.

• The second and subsequent rows contain the data.

• There are no blank rows, although individual blank cells may be present.

• Select the data before you display the dialog box. 

• Type the address of the data into the Table/Range field of the dialog box.

• Use the Select button in the dialog box to select the data range.

• Create a named range for the data (Prefered).

1. Select the data range.

2. Click the Define Name button in the Defined Names section of the Formulas ribbon. The New Name dialog box is displayed.

3. Type the name for the range in the Name field. You should use something descriptive such as SalesData or SurveyResults. It’s best to avoid spaces, too; use an underscore if needed to separate words.

4. Click OK.

Then, when you are creating a PivotTable, simply enter the range name in the Table/Range field.

Using named ranges is more convenient than typing the address or selecting the data each time you want to refer to them.

If you expand the range, to include additional data, the PivotTable report will automatically include the new data when it is refreshed.

If you have applied any autofilters or subtotals to your source data, they are ignored when you create a PivotTable from the data.

If you want to create a PivotTable based on the filtered data, you must copy the data to a new list and use that as the basis for the PivotTable report.

Open both workbooks, the one where you want to place the PivotTable should be the active one. Then :

1. Click PivotTable on the Insert ribbon. 

2. Make sure that the Select a Table or Range option is selected.

3. Click the Select button at the right end of the Table/Range field. 

4. Activate the workbook that contains the data. The collapsed dialog box remains visible.

5. Select the data range for the PivotTable. Its address, including the workbook name,

will be entered in the Create PivotTable dialog box.

6. Click the Select button in the dialog box to accept the selection and expand the

dialog box.

7. Click OK to return to the original workbook with the PivotTable inserted.

 When a PivotTable is linked to data in an external workbook, you can update it only if the linked data workbook is available.

If this file has been moved, renamed, or deleted, you will not be able to update the PivotTable.

Excel displays an error message when you

attempt to do so. The original PivotTable data remains in place, however.

Regardless of whether the data are in the same workbook as the PivotTable, in an

external workbook, or in another external data source, PivotTables do not refresh automatically.

Changes to the data will not be reflected in the PivotTable unless you refresh the data.

Recalculating the workbook (F9) does not refresh PivotTable data.

You can refresh data by one of two ways:

• Right-click the PivotTable and select Refresh Data.

• Click the Refresh button on the Options ribbon (available only when the PivotTable

is active).

Perhaps you have a huge amount of data and the workbook they are in is slow and

cumbersome. By putting the PivotTable in a separate workbook you’ll be able to view

and manipulate the PivotTable summary without the extra overhead of all those data.

Or perhaps you want to summarize data that are located in several different

workbooks. You can create a summary workbook that contains several PivotTable

reports, each linked to its own external data workbook.

• Link your PivotTable to the external data without importing them into Excel.

• Import the external data into Excel and then treat them as an Excel list.

1. Display the Create PivotTable dialog box.

2. Select the Use an External Data Source option.

3. Click the Choose Connection button. Excel displays the Existing Connections dialog box.

4. Use the drop-down Show list at the top

of the dialog box to control which data connections are displayed: those defined in the current workbook, those available on the network, and those available on your computer.

5. At this point you can do one of two things:

• Click the desired data connection; then click Open.

• Click Browse to locate a data connection that is not listed.

At this point one of two things will happen, depending on the nature of the data source. If the data source contains a single table of data, Excel will create the blank PivotTable and display the PivotTable field list, and you can proceed with the PivotTable design.

If, however, the data source contains more than one table, Excel will display the Select

Table dialog box listing the available tables. You must click the

table that you want to base your PivotTable on; then click OK to create the blank

PivotTable and display the PivotTable Field List.

1. Click the Existing Connections button.

2. Click OK and continue the process until the Import Data dialog box is displayed.

3. Select either the PivotTable Report or the PivotChart and PivotTable Report option.

4. Specify whether the PivotTable should go in the current worksheet or a new worksheet.

5. Click OK.

On the Data ribbon, click the From Other Sources button to display a list of the various types of connections you can create from within Excel. Then, follow the prompts to establish the connection.

When you are finished, the connection will be available for you to use in Excel.

Depending on the configuration of your system and the database drivers installed, you can access data in a wide variety of database formats.

I will show you how to create a PivotTable report based on data in a Microsoft Access database. Although some of the details will be different for other database formats, the general principles are the same.

Typically the external database contains a variety of tables and queries and all of these will be available to you.

Be sure that you know which element

in the external database you need.

Note that you do not have to have Access open to create the PivotTable.

After you have Excel open with a blank worksheet displayed, follow these steps:

1. Click the PivotTable button on the Insert ribbon.

2. Select the Use an External Data Source option.

3. Click the Choose Connection button to open the Existing Connections dialog box.

4. Click the Browse for More button to open the Select Data Source dialog box.

5. Navigate to and select the file Northwind.mdb (Access example file).

6. Click OK to display the Select Table dialog box.

7. In the list, select Product Sales for 1997 (query in the access example file).

8. Click OK to return to the Create PivotTable dialog box.

9. Click OK to close the Create PivotTable dialog box and create the blank PivotTable.

Many kinds of external data are protected against unauthorized access. You may need

a user name and a password to access the data. If this is the case, you will be

prompted for this information during the process.

To import data, you always start by clicking the Get External Data on the Data ribbon.

Briefly, the choices on this menu are:

• From Access—Import data from an Access database (*.mdb, *.mde, *.accdb,

*.accde files).

• From Web—Define a Web Query that imports data from a Web page.

• From Text—Import data from a text file (*.txt, *.csv, *.prn).

• From Other Sources—Import data from SQL Server, from Analysis Services, from XML files, and other sources.

Although the details of these various data-importing methods vary, the end result is the same: the data will be present in your workbook as a list. For the purposes of creating a PivotTable report, you can treat the data like any other Excel list, as was covered earlier.

Creating a PivotTable based on two or more data tables or lists.

The lists can be in the same workbook or different workbooks.

Excel 2007 interface does not support creating PivotTables based on multiple consolidation ranges, but the old PivotTable and PivotChart Wizard, retained from

earlier versions of Excel, still supports this.

Each list must have the same format, meaning that the column labels in the first row and the row labels in the first column must be the same in all lists.

If the individual lists contain total rows or columns, these must not be included

when you build the PivotTable report.

To start the Wizard, press Alt+D followed by P.

Select the Multiple Consolidation Ranges option.

You have the following options (Page Field is the old term for a report filter):

• Create a single page field for me—Creates one page field with an item for each source range plus an item that consolidates all the ranges.

• I will create the page fields—Enables you to create your own page fields, up to a maximum of four, with each page consolidating different aspects of the data.

Basically, creating multiple page fields gives you greater data-filtering capabilities. (The

differences between these two options will become apparent when you learn more.)

When you opt to create a single-page field, the next step of the wizard is to select the multiple ranges that will be used for the PivotTable report (The range should include only the data).

When you have specified all the ranges, click the Next button to go to the final wizard step in which you specify where to place the PivotTable report.

The report is created automatically; you do

not have to add fields to the various areas to define the report.

Second, there is a dropdown arrow next to the Page1 label that enables you to select which of the data ranges to include in the report.

Third, the Grand Total column in the report.

You will specify the following:

• How many page fields there will be (0-4)

• Which data range(s) are associated with each page field

• Descriptive names for the fields

If you select zero page fields, the resulting PivotTable report will lack any page-field filtering abilities. This type of report is actually simpler than the standard single-page field report. It’s appropriate, however,

when you do not need or want an extra level of filtering capability in the PivotTable.

If you select one page field, the resulting PivotTable will be almost identical to the standard single-page field report. You will have a few more customization options, such as the option to assign names to fields, but the filtering capabilities of the final PivotTable will be essentially the same.

It’s when you create two or more page fields that things get interesting.

1. When you specify that you will create the page fields, the next wizard step to select the data ranges and select how

many page fields you want. If you select 0, there is nothing more to do and you can click Next to proceed to the next step of the wizard.

If you select 1, 2, 3, or 4 page fields, the corresponding number of text boxes become active in the lower part of the dialog box.

2. Your next steps to associate specific data ranges with specific fields and

assign descriptive names to them.

a-In the All Ranges list, select a range (one of the consolidation ranges you added earlier) to be included in a filter.

b-In the Field one box, type a descriptive name for this filter. If a name has already

been assigned to a range and you want to assign it to this range as well, you can

select it from the drop-down list.

c-If you are using more than one field, enter a name for this filter in the Field two,

Field three, and Field four boxes.

d-Return to Step a to add another range.

e-Repeat until all desired filters have been defined.

f-Click Next to go to the final step of the wizard.

When the final PivotTable report is created, you will see that there is one page field item at the top of the report for each page field that you specified in the wizard. Each of these has a drop-down list that enables you to access the filters associated with that field.

Page 55 to 56

What’s new is the drop-down arrow next to the Page1 label. This enables you to select

which of the data ranges to include.

You can select all the ranges or any single range to specify which data are summarized in the PivotTable report.

Page 57-60

Very Important Example

• If you are using the Excel 2007 interface, simply create the new PivotTable as usual,

selecting the existing PivotTable as the input data range.

• If you are using the PivotTable and PivotChart Wizard, select the Another PivotTable or

PivotChart Report option in Step 1 of the wizard. (This option will be available only if

the workbook contains another PivotTable.) When you click Next, you’ll see a list of all

available PivotTables and PivotCharts. Simply select the desired source and proceed as usual.

In many cases the second PivotTable will, at least initially, look exactly like the source PivotTable. But by customizing the second PivotTable you can create two different views of the same data, which is useful in some situations. For example, the first PivotTable may summarize the original data but still be a rather complex table. By creating a second PivotTable that uses the first one for its data, you could create

a more condensed summary that is easier to read. Also, basing a second PivotTable on an existing PivotTable uses less memory than basing the two PivotTables directly on the raw data. This may be a consideration when you are working with a large data set.

The Field List displays all of the data fields that are available in the data source that the PivotTable is based on.

When you check a field, how does Excel know which area to place it in?

If the field contains numeric data it is placed in the Values area, and if it contains any other kind of data—text or a date, for example—it is placed in the Row Labels area.You can move a field from one area to another as needed.

To hide the Field List while the PivotTable is active, click the Field List button on the Options ribbon.

The Field List option (Defere layout update), located at the bottom of the dialog box, lets you defer updates to the PivotTable. If this option is turned off (the default), changes you make in the Field List are reflected immediately in the PivotTable. If you have a large, complex PivotTable, particularly if it is based on external data, such updates can take a significant amount of time. You can speed things up by selecting this option, making the required Field List changes, and then clicking the Update button to make all the required updates at one time.

Deferring layout updates can reduce delays when working with large, complex PivotTables.

This layout lets you rearrange fields in the PivotTable report by dragging them on the report itself rather than by making changes in the Field List.

Note that using classic layout view does not preclude your using the Field List to arrange and change the PivotTable report—it just provides you with additional options.

To switch to classic layout:

1. Click the Options button on the Options ribbon to open the PivotTable Options

dialog box.

2. In the dialog box, click the Display tab.

3. Select the Classic PivotTable Layout option and click OK.

4. To design the report, drag fields from the Field List and drop them on the corresponding area of the report itself.

1. Make the PivotTable active.

2. Click the Options button on the Options toolbar to open the PivotTable Options

dialog box.

3. Type the new name in the Name field.

4. Click OK.

• Merge and center cells with labels—In some PivotTables, the same label, such as

Quarter or Month, is repeated in the outer row or column cells. If you select this option, the repeated labels will be merged into a single label that spans the columns or months.

• When in compact form indent row labels XXX characters—Specifies by how much

row labels would be indented when the PivotTable is in compact form.

• Display fields in report filter area—Determines how multiple fields are displayed in the report filter area. Select Down, Then Over to add new fields to an existing column before moving to a new column. Select Over, Then Down to add new fields to an existing row before moving to a new row.

• Report filter fields per column—Specifies how many fields are displayed in a row or

column (depending on the setting of the Display fields in report filter area option) before starting a new row or column.

• For error values show—If you want PivotTable cells with an error to display text that you specify instead of the error message, select this option and enter the text in the adjacent box.

• For empty cells show—If you want blank PivotTable cells to display text that you specify, select this option and enter the text in the adjacent box.

• Autofit column widths on update—If this option is selected, Excel will, when the

PivotTable is updated, automatically adjust the width of columns in the PivotTable to fit

the widest text or number. Otherwise the original column widths will be retained.

• Preserve cell formatting on update—If this option is selected, the cell formatting and layout of the PivotTable will be preserved when certain operations, such as updating, are performed on the table.

• Show grand total for rows—Select this option to display a grand total at the right end of each table row.

• Show grand totals for columns—Select this option to display a grand total at the bottom of each table column.

• Subtotal filtered page items—If this option is selected, subtotals include table values that are not displayed because of an applied filter. If this option is not selected, subtotals include only displayed table values.

• Mark totals with—If this option is selected, subtotals that include both displayed and non-displayed items (as per the Subtotal filtered page items option) are marked with an asterisk.

• Allow multiple filters per field—Select this option if you want subtotals and grand

totals to include items that are hidden by filtering.

• Use Custom Lists when sorting—Select this option to enable the use of custom lists

when sorting data. Please refer to Excel online help for information about using custom lists for sorting.

• Show expand/collapse buttons—When a PivotTable report has two or more levels of

row or column labels, the higher level will, if this option is selected, display expand (+)

or collapse (-) buttons that let you show or hide the lower levels of detail. Hiding these buttons may be desirable when you are printing or viewing (as opposed to manipulating) the PivotTable report.

• Show contextual tooltips—When this option is selected, Excel displays a contextual tooltip when you hover the mouse pointer over certain PivotTable elements. The tooltip shows information such as the name of the field, its value, and the row and column it is in.

• Show properties in tooltips—Select this option if you want tooltips to include property information for an item. This option is available only if the data source supports properties.

• Display field captions and filter drop-downs—If this option is selected, the field captions and filter drop-downs are displayed on the report. You may want to hide these elements for viewing or printing the PivotTable.

• Classic PivotTable layout—Select this option to use classic PivotTable layout.

• Show items with no data on rows—Select this option if you want to display row items

that have no values. This option will be available only if the PivotTable is based on an OLAP data source.

• Show items with no data on columns—Select this option if you want to display column items that have no values. This option will be available only if the PivotTable is based on an OLAP data source.

• Display item labels when no fields are in the values area—Select this option if you

want to display item labels when there are no fields in the value area. This option

applies only to PivotTables that were created in earlier versions of Excel.

• Show calculated numbers from OLAP server—Relevant only when the PivotTable is based on OLAP data. If selected, calculated items in a dimension are displayed.

• Field list—Select whether items in the files list are sorted in A-Z order or are displayed

in the order specified by the data source.

• Print expand collapse buttons when displayed on the PivotTable—If this option is selected, expand/collapse buttons are printed or not depending on whether they are displayed on the PivotTable, according to the setting of the Show expand/collapse buttons option on the Display tab. If this option is not selected, expand/collapse buttons are never printed.

• Repeat row labels on each printed page—If a PivotTable has more than one row field, selecting this option tells Excel to repeat the outer row labels on the second and subsequent pages when the report is printed. This is not the same as printing regular row and column labels on each page, which you can do with the Set print titles option.

• Set print titles—When this option is selected, row and column labels are repeated on each page of a multipage printed report.

• Save source data with file—This option is relevant only for PivotTables that are based

on external data. If selected, the data from the external source is saved as part of the

workbook. This enables you to open the workbook and work on the PivotTable even

when a connection to the data source is not available.

• Enable show details—Select this option to enable drilling to detail in the PivotTable

report. 

• Refresh data when opening the file—If this option is selected, the PivotTable is automatically refreshed from the data source each time the file is opened.

• Number of items to retain per field—This option is relevant only for PivotTables that

are based on external data. It specifies the number of data items that are cached in the workbook for each field. Possible settings are:

• Automatic—The default number of items for each field.

• None—No unique items for each field.

• Max—The maximum number of items for each field.

Styles can control the following aspects of a

PivotTable’s appearance:

• The font used for the table as a whole or for specific parts of the table, including type

face, size, bold/italic, and color.

• The background color for the whole table, parts of the table, or alternating rows or

columns.

• The borders, both vertical and horizontal, used within the table and on its outer edges.

When you display the style menu, Click New PivotTable Style to define a new custom style.

When you have defined one or more custom PivotTable styles they are displayed at the top of the style menu in the Custom section.

Excel offers three report layouts that control the overall layout of the report. You select a layout using the Report Layout button on the Design ribbon. The layouts are:

• Compact Form—This layout arranges the PivotTable report to take up as little space as possible. It is useful when you are trying to fit a wide PivotTable report onto a single sheet of paper when printing or to make the entire table visible on-screen at one time.

• Outline Form—This layout displays the table like an outline. Subsidiary row fields are displayed indented under the parent field. Rows are not separated by borders.

• Tabular Form—This layout displays the table in tabular form. Subsidiary row fields get their own column to the right of the parent field, and top-level row fields are separated by a horizontal border.

Anything you can change for other parts of a worksheet can also be changed for a PivotTable report. There are a few things you need to keep in mind when formatting a PivotTable.

1. Make sure that the formatting you apply is preserved when the PivotTable is refreshed or its layout changes. This requires selecting the Preserve Cell

Formatting on Update option on the Layout & Format tab of the PivotTable Options dialog box.

2. Make sure that the formatting is applied to parts of the PivotTable that are not visible. For example, you may have used a page field to filter the PivotTable to display

a subset of the data. Before applying the formatting changes, select (All) in the pagefield drop-down list to display all the data.

3. Use the correct technique to select the part of the PivotTable that you want to format. Clicking a field heading to select all associated cells.

 PivotTables have a lot of power hidden within them.

They are waiting for you to use them to organize and display your PivotTable data in the precise way you need.

You add a field to the Row Labels area when you want the PivotTable to display data organized by rows based on the values in that field.

e.g. Suppose there is a field (Gender) and you add it to Row Labels Area : 

The PivotTable now has two rows : Male and Female (not counting the default Grand Total row). This is because the Gender field contains either the value Male or the value Female for every subject. There are two possible values for Gender; hence, there are two rows in the PivotTable. A label at the top, Gender in this example, displays the name of the field. The drop-down button next to the field name is used to filter the PivotTable. 

If you add two fields to the Row Labels area, one will become the inner row field and the other will become the outer row field. Data are organized first according 

to the values in the outer row field; then within each group of outer row fields, they are organized by the values in the inner row field.

 To create inner and outer row fields, add both of the fields to the Row Labels area. The one that you add first becomes the outer row field, and the one that you add second becomes the inner row field (although this arrangement can be changed at any time). 

Why would you select one field for the inner and another for the outer row field? The answer lies in the subtotals. Excel automatically creates subtotals for each outer row field. Therefore, you choose your inner and outer row fields based on which you need subtotals for.

When you have two or more fields within an area, their initial position—that is, inner or outer—is determined by the order in which they were added. They are listed in the Field List in this order.

The upper one is the outer.

The lower one is the inner.

You can change the order of the fields in an area and thus their inner/outer relationships.

You can use as many fields in the Row Labels area as you want. When you go beyond three or perhaps four fields, PivotTables tend to be a bit confusing, but your data analysis may require this number of fields.

The Column Labels area works the same way as the Row Labels area except that you get a separate column, rather than a row, for each data value.

A PivotTable can use multiple column fields as well. They work much like multiple row

fields. Even though column fields are still referred to as inner and outer, just like multiple row fields, including the creation of subtotal columns for outer fields.

Report filter fields—called page fields

in earlier versions of Excel—are powerful tools and are essential for many advanced tasks.

A report filter enables you to filter the entire report based on the data in the field.

As you gain experience with PivotTables, you will develop a feeling for whether

it is better to drop a field on the Page area or on the Row or Column area. There is no right way to do things; it all depends on your data and what you want to get out of them.

A PivotTable report is made up of fields, and each field has a group of settings associated with it. These settings control how (and whether) the field displays its number format, the summary calculation used, and a few other things.

To change field settings, you must display the PivotTable Field dialog box using one of

these methods:

• In one of the areas of the Field List, click the arrow next to the field name and select

Field Settings (or Value Field Settings in the case of a value field) from the popup menu.

• In the PivotTable report itself, right-click a cell associated with the field and select Field Settings (or Value Field Settings in the case of a value field) from the popup menu.

Field settings are different depending on the type of field you are working with: a value field, a row/column field, or a report filter field.

This dialog box contains the following elements:

• Source Name—Lists the name of the data source field on which this PivotTable field is

based. You cannot change this.

• Custom Name—Shows the display name of the field. Edit this name to change the way it is displayed in the PivotTable report.

• Number Format—Click this button to display the Format Cells dialog box where you can select the number format for this field.

• Summarize by—Enables you to select how the field summarizes the data (Sum, Count,

Average, and so on).

• Show values as—Use this tab to set advanced data field options.

The Summarize by setting gives you great flexibility in the way the PivotTable presents the data. By default, a value field is set to Sum if the field contains number data. This means that the numbers displayed in the PivotTable will be the sums of the corresponding data items. If a value field contains text data, the default is Count. The other summary options available are:

• Count—The number of data items.

• Average—The average of the data values.

• Max—The largest data value.

• Min—The smallest data value.

• Product—The result of multiplying all the data values together.

• Count nums—The number of data items that are numeric.

• StdDev—The sample standard deviation of the data values.

• StdDevp—The population standard deviation of the data values.

• Var—The sample variance of the data values.

• Varp—The population variance of the data values.

Obviously, most of these summary functions are applicable only to numeric data.

Field settings for row and column fields are essentially identical to each other, with the

only real difference being where on the report the field is placed. They are, however, somewhat different from the settings for value fields.

This dialog box offers the following options:

• Source Name—Lists the name of the data source field on which this PivotTable field is

based. You cannot change this.

• Custom Name—The display name of the field. Edit this name to change the way it is

displayed in the PivotTable report.

• Subtotals & Filters tab—Lets you set options for field subtotals.

• Layout & Print tab—Lets you set options for field layout and printing

• Include new items in manual filter—Determines whether new items that are added to the data source are automatically included or excluded by a filter that has been applied to the PivotTable report.

By default, rows and columns are subtotaled automatically as a sum of data items. This is what you get if the Automatic option is selected under Subtotals. You can also select None to have no subtotals or Custom to use the subtotal calculation selected in the list. (Of course, most of these subtotal options don’t really provide totals, but that’s the term Excel uses for all of them.) You can click more than one custom subtotal and the PivotTable displays

a separate row/column for each one. Click again to deselect. The following custom

subtotal options are available:

• Sum—The sum of the data items.

• Count—The number of data items.

• Average—The average of the data items.

• Max—The largest data item.

• Min—The smallest data item.

• Product—The result of multiplying the data items together.

• Count Nums—The number of data items with a numeric value.

• StdDev—The sample standard deviation.

• StdDevp—The population standard deviation.

• Var—The sample variance.

• Varp—The population variance.

• Include new items in manual filter—Determines whether new items that are added to the data source are automatically included or excluded by a filter that has been applied to the PivotTable report.

The first two options let you choose between Outline form and Tabular form for the field. If you select Outline Form, you then can select to display labels from the next field in the same column (compact form). These options are similar to the Outline Form, Tabular Form, and Compact Form options that are available from the Report Layout button on the Design tab, but apply to a single field rather than to the whole report.

These options affect only the report appearance, not the data it contains.

The other options on the Layout & Print tab are:

• Insert blank line after each item label—If selected, a blank line is inserted after each

item label.

• Show items with no data—If selected, row items for which there is no data are displayed in the report.

• Insert page break after each item—If selected, each item starts on a new page when the report is printed.

Be aware that certain of the layout options, such as Insert blank line, may not seem to

have an effect on the report unless you have multiple row fields and you are setting properties for the outer field.

When you open the Field Settings dialog box for a report filter field, it looks like the PivotTable Field dialog box for a row or column field. In fact, the field settings

are precisely the same for report filter fields and for row and column fields. However, most of these settings will have no effect on a filter field, so what’s the point?

The point lies in the fact that you can move fields between the row or column area and the filter area. When you move a field from the filter area to the row area, then these settings will come into play. When you move a field from the row area to the filter area, the settings become irrelevant. To simplify things, the same set of options is used for fields in both locations.

For example, when you change the Subtotals option for a report filter field you may wonder why the PivotTable report does not change. This setting takes effect only for row and column fields. If you pivot the table by moving the field to the Row or Column area, you’ll see the effect of the Subtotals option you selected.

This tab lets you access some advanced options that control some of the most sophisticated capabilities of PivotTable reports.

The default setting in this part of the dialog box is Normal in the Show Values As dropdown list. With this option, which you’ll probably use most of the time, the field is summarized by means of the calculation that you select in the Summarize by list — for example, a straightforward sum, count, or average. If you pull down the Show

values as list, however, you’ll see several different ways in which the field data can be summarized. Just to give one example, a data item could be displayed as the percentage of the total of all data items in that row.

Double-clicking a field button brings up the PivotTable Field dialog box for that field,

but double-clicking a data cell activates drill-down, which displays the underlying data for the cell in a new worksheet. If you do this by mistake you’ll have to delete the drilldown worksheet and return to the sheet where the PivotTable is located.

Simple because they do not require Base field or Base item selections—are as follows:

• % of Row—The data value as a percentage of the total for that row.

• % of Column—The data value as a percentage of the total for that column.

• % of Total—The data value as a percentage of the total for the entire report.

When you select Running Total in the Show values as list, you must also select a base

field. The resulting display will be a running total of the base field; in other words, the display for the current item will be the actual data value for the current item added to the total values for all preceding data items.

The remaining Show data as options require you to specify both a base field and a base item:

• The Base field is one of the fields in the PivotTable. This includes fields that have been added to the PivotTable as well as those that have not (that is, those that are in the Field List but not the PivotTable itself).

• The Base item is a value for the column field. It can also be either of the special

values (previous) or (next), which use the previous or next item, respectively, for the

calculation.

The calculations available to you are:

• Difference from—Displays the difference between the raw value and the value of the base field/base item data.

• % of—Displays the raw value as a percentage of the value of the base field/base item data.

• % difference from—Displays, as a percentage, the difference between the raw value and the value of the base field/base item data.

The final option available in the Show data as list is Index. It calculates the display value as follows:

((value in cell) x (Grand Table Total)) / ((Grand Row Total) x (Grand Column

Total))

The options available here are very powerful but are a bit difficult to understand. Be careful when using them to make sure your result is actually what you want it to be.

In a PivotTable report, row and column fields give you the option of applying value or label filters to the report data. Rather than just filtering based on values in the field, these filters let you define your own criteria.

To apply a value or label filter, click the down arrow next to the field name in the

PivotTable then select either Value Filters or Label Filters from the menu.

Note that for labels, Greater Than, Between, and so on are interpreted in terms of alphabetical order. Other filter

choices, such as Begins With and Contains, are self-explanatory.

You can clear a value or label filter from a field by selecting Clear Filter from the corresponding menu.

This section shows you how to use the value filter and sort options to change the way a

PivotTable report is displayed.

page 143

A calculated field acts like any other field in your PivotTable. Its name appears in the Field List and you can add it to the PivotTable just as you would any other field. It exists only in the PivotTable, however, and only for the duration of the

PivotTable. In other words, it is not part of the data source.

Suppose your data source contains the total annual sales for each of your company’s sales reps. The annual bonus for each rep is calculated as 2 percent of his or her total annual sales, but the data source doesn’t contain the bonus amount. You can create a calculated field in your PivotTable to do this. It calculates total annual sales times 2 percent for each rep and lets you use this field in your PivotTable.

1. Click anywhere in the PivotTable report to make sure it is active.

2. Click the Formulas button on the Options ribbon.

3. Select Calculated Field from the menu. Excel will display the Insert Calculated Field

dialog box.

4. Enter the name for the calculated field in the Name box. It’s a good idea to use a

descriptive name.

5. Enter the formula for the calculation in the Formula box:

• The formula must start with an equal sign.

• It can contain numbers, parentheses in pairs, and the operators + (addition), - (subtraction), * (multiplication), / (division), and ^ (exponentiation).

• To add an existing field to the formula, click the field name in the Fields list and then click Insert Field.

6. When the formula is complete, click the OK button to add the calculated field to the

Field List.

7. Click OK to close the dialog box. The new calculated field will now be included in the Field List and also automatically added to the PivotTable’s Values area.

A calculated field can be based on any fields in the data source as well as on other calculated fields that have already been defined. For example, look at this formula for a calculated field:

=(ProductTotal+ServiceTotal)*.1

The formula adds the values in the ProductTotal and ServiceTotal fields and multiplies the sum by 0.1. ProductTotal and ServiceTotal can each be a field in the data source or another calculated field.

You also use the Insert Calculated Field dialog box to add and delete calculated fields.

Display the dialog box as described previously; then select an existing calculated field in the Name list. Then perform one of these actions:

• To delete the selected calculated field, click the Delete button.

• To modify the formula of the selected calculated field, edit the formula, click the Modify button to save your changes, and then click the OK button.

Sorry, no calculated fields for you.

If your PivotTable is based on an external Online Analytical Processing (OLAP) data

source, you will not be able to create calculated fields or items.

As you have seen, a calculated field performs a calculation on the data in an existing field.

A calculated item, on the other hand, performs a calculation on one or more items within a field. So what’s the difference? An item is an individual data value in a field. In your data source, you might have a field named Department and within that field you might have the items Accounting, Design, Maintenance, and so on.

Make sure the PivotTable is active. Click a cell that belongs to the field that the calculated item will be based on. The calculated item will be inserted in the row below where you click. Then click the Formulas button on the Options toolbar and

select Calculated Item from the menu. Excel displays the Insert Calculated Item dialog box. Note that the title of the dialog box indicates the field you are using.

NOTE:

If the Calculated Item menu command is not available, it is probably because you

clicked, in the PivotTable, a field that cannot be used to create a calculated item.

If the items in a field are grouped, you

should ungroup them before creating a calculated item:

1. Right-click the group.

2. Click Ungroup on the popup menu.

You can regroup the items after the calculated item has been created, if you wish.

The Fields list in the lower part of the dialog box lists the PivotTable fields. When you select a field in this list, the Items list displays the names of the items for that field. This can be confusing because the dialog box lets you insert (or try to insert) things in the formula that cannot be used. For example, you will never use the Insert Field button because calculated items are not based on fields but on items. Likewise, you cannot insert items from fields other than the selected one (the one you clicked in the PivotTable before displaying this dialog box). Fortunately Excel prevents you from entering incorrect items in the formula by displaying an error message, either when you try to add the item or when

you try to close the dialog box.

They are similar in many ways to the

procedure for creating a calculated field.

1. In the Name box, enter the name for the calculated item. This is the name that will be displayed in the PivotTable.

2. Enter the formula for the item in the Formula box:

• The formula must start with an equals sign.

• It can contain numbers, parentheses in pairs, and the operators + (addition), - (subtraction), * (multiplication), / (division), and ^ (exponentiation).

• To add an item to the formula, click the item name in the Items list and then click

Insert Item.

3. When the formula is complete, click the OK button to add the calculated item to the

PivotTable.

Page 154

In a situation like this, where a calculated item is displayed in more than one cell, you can modify the calculated item for individual cells. Move the Excel pointer onto any one of the calculated item cells in the PivotTable, cells B10 to F10. The formula bar shows that each cell contains the formula you specified for the calculated item:

=.5*(May+Apr)*1.2

Suppose you know that while this projection formula is accurate overall, you would get a more accurate projection for the Pants category if you were to use a factor of 1.4 instead of 1.2. All you need to do is move the pointer to cell D10, where the calculated projection for Pants is located, press F2 to edit the formula, and make the desired change. However, this

kind of ad hoc change to a PivotTable is generally considered to be bad practice because it is so hard to trace.

Viewing detail for data items is sometimes referred to as drilling down or expanding to

detail. When you drill down, Excel displays the raw data records that underlie the selected value item. To use this feature, you must ensure that the Enable Show Details option is turned on in the PivotTable Options dialog box.

To drill down, simply double-click the value cell of interest. Excel places the underlying

raw data in a new worksheet and displays them.

To remove detail data, simply delete the worksheet they are on (click Delete on the Home tab and then select Delete Sheet).

To control how much detail is shown for a field, right-click the field button, select

Expand/Collapse from the menu, and select the Expand or Collapse from the next menu.

Expanding and collapsing detail for a field item has different effects depending on the field position in the PivotTable. If the PivotTable has two or more row fields and the field of interest is not the inner field, collapsing detail for a field works by collapsing any fields that are more “inner.” I am speaking here of row fields but it works the same way for column fields.

In other situations the Collapse command has the following effects:

• If the field is the inner field, hiding its details has the same effect as collapsing its parent field (as in the previous example). To show the details again you must execute the Expand command on the parent field.

• If the field is the only row field, the Collapse command has no effect.

Note that the effect of collapsing/expanding a field seems to be the same as using the

expand/collapse (+ and -) buttons in the PivotTable report. The difference is that the + and - buttons expand or collapse individual items, while the expand/collapse commands on the menu operate on all items for that field.

When you select Expand for an inner or only field, Excel displays a list of available fields, including those that are already part of the PivotTable and any that have not been added to the PivotTable. It does not, however, list fields that are already added to the region (row or column) where the field of interest is located.

When you select a field from this list and click OK, Excel adds the field as an inner field to the Row Labels area:

• If the field was already part of the PivotTable, the effect is the same as that of pivoting the table; the field is moved from its current location (for example, the Column Labels area) to the new location as the inner field in the Row Labels area.

• If the field was not already part of the PivotTable it is simply added as the inner field.

Excel gives you the ability to group items in a PivotTable report, providing another level of analysis that can be very useful in some situations. Suppose, for example, that your raw data are about individual people and one of the data items is Age. This value will range from, say, 18 to 65.

Using the Group command you can define three groups:

Group 1: 18 to 35

Group 2: 36 to 49

Group 3: 50 to 65

The resulting PivotTable summarizes data according to the groups you define. You can also group non-numeric data. Suppose your sales data include the city of the branch that is reporting, and you want to analyze by region. You can define groups that contain specified cities, such as:

Northeast: Boston, Hartford, New York

South: Atlanta, Miami, Charleston

Midwest: Chicago, Toledo, Omaha

For any field, you group or ungroup items by selecting the field in the PivotTable report and using the commands in the Group section of the Options ribbon.

The details for grouping depend on the kind of data being grouped.

To group a numeric field, click the field button then click the Group Field button on the Options ribbon. Excel opens the Grouping dialog box.

Note the following:

• The Starting At and Ending At boxes are automatically filled in with the lowest and

highest values that are present in this field—18 and 65 in this example.

• The By box contains 10 by default—this is the size of each group.

You can change any of these values to suit your needs; then click OK to create the groups.

Suppose, however, you want the groups to be by decade—20–29, 30–39, and so on, rather than 18–27, 28–37, and so on.

Then you set the Starting At and Ending At options manually:

1. Turn both the Starting At and Ending At options off.

2. Enter 20 in the Starting At box.

3. Enter 59 in the Ending At box.

The PivotTable now displays six groups:

• One group labeled <20 for all ages below the Starting At value.

• Four groups for the age groups 20–29, 30–39, and so on.

• One group labeled >60 for all data above the Ending At value.

When a field contains dates, you can define groups based on essentially any measurement ranging from seconds up to years. Remember, in Excel the term date refers to data that can specify a date, a time, or both.

You can create a single grouping, such as by grouping dates by weeks, or you can create more than one level of grouping, such as by grouping dates by years and then within years by quarters.

Starting At and Ending At options are

turned on by default and, as with numeric data, the starting and ending dates (or times) are determined automatically by Excel. In most instances you will leave these unchanged.

The By list contains all the intervals by which you can group: seconds through years. Click an interval to select it; click again to deselect. You can select one or more intervals.

Depending on the interval or intervals selected, the Number Of option may be available. When it is, you enter a value to determine the size of the grouping. For example, if you select Days as the interval you can enter 5 to create groups of five days.

You can use the Starting At and Ending At options to change the way groups are formed. They work like these same options for numeric data. By turning off the Starting At option you create a group for all dates that are before the specified date, and by turning off the Ending At option you create a group for all dates that are after the specified date. These groups will not appear, however, if there are no dates

before or after the cutoffs.

The category other items refers to any data that are not numbers or dates. States, department names, product descriptions, colors, and flavors are just a few examples of this kind of data (sometimes called category data). To group this kind of data, click each individual item while holding down Ctrl. When all the items to be grouped have been selected, click

the Group Selection button on the Options toolbar. Repeat for additional groups.

This section shows you how to group category data as well as how to create subtotals for a group, something that Excel does not do automatically.

Page 168 

You can create a PivotChart from an existing PivotTable or you can create the PivotChart

at the same time you create the PivotTable.

To be more precise, you create the PivotTable and PivotChart at the same time. To do so, click the arrow below the PivotTable button on the Insert ribbon and select PivotChart to open the Create PivotTable with PivotChart dialog box.

Specify the data source and the location for the PivotTable and click OK—the PivotTable is created as usual and the PivotChart is created in the same worksheet. Both are initially blank, and the PivotChart Filter Pane is displayed.

1. Place the cell pointer on any cell in the data range.

2. Click the arrow below the PivotTable button on the Insert ribbon.

3. Select PivotChart from the menu to open the Create PivotTable with PivotChart

dialog box.

4. Click OK to close the dialog box and create the blank PivotTable and PivotChart.

5. Close the PivotChart Filter Pane by clicking the X in the top right corner.

6. Click the blank PivotTable to make sure it is active.

7. Add the Camping field to the Values area.

8. Add the Date field to the Row Labels area.

9. Add the Region field to the Column Labels area.

After you create a PivotChart, you must always remember that it is linked to the

PivotTable report. Changes that you make to the report will be reflected in the chart. For example, if you change the number display format of a value field in the report, that format change will carry over to the labels in the PivotChart.

To change a PivotChart to a static chart, one that is no longer linked to the data

source, delete the underlying PivotTable report. To delete the PivotTable:

1. Drag over the PivotTable to select all its cells.

2. Press Delete.

• Plot area—Where the actual data are displayed.

• Data series—The chart elements corresponding to one related group of numbers. In a PivotChart, this refers to a column of numbers, although in regular (non-pivot) charts it can also refer to a row.

• Category axis—Lists the values of the data categories.

• Data series axis—Identifies the individual data series. Relevant only for 3-D charts.

• Value axis—Displays a scale of values for the data points.

• Chart title—The title of the chart.

• Axis labels—Titles for the individual axes.

• Legend—Identifies the data series by color and/or pattern.

Some of these chart elements are options and are not present in all charts, namely the chart and axis titles and the legend.

When a PivotChart is active, Excel displays four ribbons that you use to work with the

chart. Three of these ribbons, Design, Layout, and Format, are related to all Excel charts, not just PivotCharts. You use them to do things such as change the chart type, apply chart styles, work with axis titles, and change fonts used on the chart. Because these tools are not PivotChart-specific, they are for the most part not covered here.

The fourth ribbon is the Analyze ribbon, and it is displayed only when a PivotChart is

active.

• Active Field—Lists the name of the currently active field (if there is one).

• Expand/Collapse Entire Field—Used to show or hide detail in the chart. This has the same effect has expanding or collapsing fields in the PivotTable report.

• Refresh—Displays a menu from which you can refresh the PivotTable and PivotChart, refresh all PivotTables in the workbook, view refresh status, or cancel the refresh.

• Clear—Displays a menu from which you can select one of the following commands:

• Clear All—Removes all fields from the PivotTable (and PivotChart), the PivotTable is then ready to add new fields from the Pivot Table Field List.

• Clear Filters—Removes any filters that have been applied to the PivotTable and

PivotChart.

• Field List—Click to hide or display the Field List.

• PivotChart Filter—Click to hide or display the PivotChart Filter dialog box.

Excel is set to create a new PivotChart as a column chart. After the chart has been created, you can change its type to almost any one of Excel chart types, and you can also use the program’s capability to define your own custom types. (One restriction is that the XY (Scatter), Bubble, and Stock types cannot be used for PivotCharts.)

To change the chart type of a PivotChart:

1. Right-click the chart and select Change Chart Type from the popup menu.(or Change Chart Type button on the Design ribbon)

2. Click the desired chart type in the list on the left.

3. Click the desired subtype in the gallery on the right. (Some custom charts do not offer subtypes.)

4. Click OK to accept the selected type and apply it to the PivotChart.

You can change the default Excel chart type from stacked column to another type by

displaying the Chart Type dialog box, selecting the desired chart type and subtype,

and clicking the Set as Default Chart button.

Excel follows certain rules when creating a PivotChart. These rules determine how the

PivotTable data are arranged in the chart—which categories are placed on which axis, how data series are defined, and so on. You want to have a good understanding of these rules to create PivotCharts with the structure you want.

When you create a PivotChart from a PivotTable that contains one row field and one value field, you get a chart that each data value in the row field becomes an item on the chart’s horizontal (category) axis.

Suppose you had created the PivotTable with a column field instead of a row field.

Now there is only one item on the horizontal axis and the different column fields are represented by different colored

bars.In Excel chart terminology, each column field is a data series in the chart.

You see from this example that a PivotChart plots row fields as categories and column fields as data series (legend fields).

Row fields become items on the horizontal axis, column fields become data series.

When a PivotChart is active, you can still move data fields between the various areas

using the Field List. However, the areas in the Field List and the menu that Excel

displays when you click a field name are slightly different when a PivotChart is active.

The Move to Column Labels command is replaced by the Move to Legend Fields

command, and the Move to Row Labels command is replaced by the Move to Axis

Fields command. This makes perfect sense because, in the PivotChart, column fields

are represented as legend fields (data series) row fields are represented as axis fields. The end result is exactly the same.

A PivotChart created from a PivotTable that contains two row fields.

The outer row field are represented on the lower level of the horizontal axis. The

inner row field are represented

on the upper level of the horizontal axis.

Try to change the order of inner and outer field.

Try to move one field to the column area.

When a PivotTable has more than one column field, the PivotChart handles it a bit differently from when there are multiple row fields.

When there are two column fields, Excel

creates a data series for each combination of field values.

e.g. PivotTable report with two column fields. One field has two values: M

and F. The other field has three values: Yes, No, and Unsure.When you create a PivotChart from this report, Excel creates six data series: M-Yes, M-No, M-Unsure, and so on. The single row field, Age, is represented by categories on the horizontal axis, as you would expect. 

When your PivotTable gets relatively complex, you can take advantage of Excel’s three dimensional chart types, which have features for clearly displaying more fields.

The extra visual dimension that 3-D charts provide is often just what you need to create a clear graphical presentation of your PivotTable data.

Page 189

When a PivotChart is active, you can display the PivotChart Filter Pane by clicking the PivotChart Filter button on the Analyze ribbon.The Filter Pane lists the axis fields (that is, row fields), legend fields (column fields), and value fields. It also lists report filter fields if any are defined for the PivotTable.

The filtering and sorting capabilities that the Filter Pane provides are exactly the same as those available in the PivotTable report itself. When you are working on a PivotChart, using the Filter Pane may be more convenient. Click the arrow next to an axis, legend, or report filter field and you’ll see the menu of sorting and filtering.

1,048,576 rows and 16,384 columns

Consider sales records for a large online retailer, inventory for a major manufacturing concern, or demographic data kept by an insurance company. These and other data sources are well beyond the capabilities of Excel.

PivotTables and PivotCharts themselves also have some limitations:

• 256 page fields

• 256 value fields

• 256 data series in a PivotChart

These limitations would seem to put the analysis of large data sets beyond the realm of Excel and PivotTables.

The fact is that huge data sets place severe demands on any analysis

tool and can result in processing times of several hours or more. To deal with this challenge, programmers have developed a set of tools called Online Analytical Processing, or OLAP. OLAP is designed to work with hierarchical raw data, organizing and summarizing them in a multidimensional form. When your analysis program, such as Excel, accesses the

data it is actually accessing the OLAP summaries and not the raw data themselves.

In other words, OLAP does most of the analysis grunt work so that the final analysis program—Excel or whatever—doesn’t have to.

Flat data, sometimes called non-relational data is that all the information is contained in a single table—one set of rows (records) and columns (fields).

Each record contains full information for a particular part.

So information may be present more than once.This causes several problems. First it is an inefficient use of storage space to keep the same information in more than one location. Second, updating a supplier’s information—for example, if the address changes—will require changes in multiple locations and introduce the possibility

of errors. Finally, there is the possibility of completely deleting a supplier from the database when you do not want to, if, for example, all of that supplier’s parts are deleted. To avoid these problems, relational databases were devised.

A relational database keeps related data in separate tables. Records in the two tables are linked by a key field that defines which record(s) in one table are associated with which record(s) in the other table.

The problems described earlier for a flat database have been solved:

• Each supplier’s information is present only once.

• Modifying a supplier’s information requires a change in only one location, the single record for that supplier.

• Deleting part records cannot delete supplier information.

Relational databases are the mainstay of almost all modern data storage systems. Despite their great flexibility and power, they do not, however, solve all problems. Particularly when it comes to detailed analysis of large amounts of data, relational databases do not really simplify or speed up the process. Multidimensional data is the preferred solution for these challenges.

Multidimensional data have more than one dimension. It is not used in the same sense as in geometry, as, for example, in a three-dimensional Excel chart.

It will perhaps be easiest to understand if you work through the same data from flat to relational to multidimensional.

Flat data: Each record includes a field named that contains a unique numeric ID for each record. This is called the primary key and is used in all database tables.

One way to make this database more usable is to convert it to a relational structure with separate tables (called primary tables because they do not depend on any other tables) and a master table  links to all these primary tables and contains the Sales data for example. The Sales table is a dependent table because it gets some of its information by means of relational links to the primary tables. 

You can see that the data in most of the fields of the Sales table actually consist of links to data in the other tables. Note that any database program displays the actual data and not the link number.

As you may have guessed, a relational database does not consist of only primary and dependent tables; it also requires that you define the links, or relationships, between the tables (A database program always provides a method for defining these relationships and usually also provides a way to display them - Excel does not have the ability to work directly

with relational databases).

At this point the relational Sales database is complete and ready to use.

However, for certain types of analysis with a really large database, this sort of structure is not ideal. Here’s where the idea of multidimensional data comes into play.

e.g. Database includes a table for salesmen and another for customers. But perhaps you want to analyze the relationship between salesmen and customers. You may think that certain salespeople do better with certain customers. Perhaps Gomez does really well selling to Acme Metal Works but not so well with East End Inc. This kind of analysis is possible with the relational database just described, but it can be slow and cumbersome with large data sets. By adding another dimension to the data you can enable faster and more robust data analysis.

But what will this dimension be? Look at the existing data—each sales record has a

Customer value and a Salesman value, currently separate. What if you combine them? In other words, instead of having a Salesman value of Gomez and a Customer value of Acme Metal Works, the record had a Salesman_Customer value of Gomez-Acme Metal Works.

This will be the new dimension.

The database will still be relational. All you are doing is inserting an extra level, or dimension, of data between the dependent Sales table and the primary Customer and Salesmen tables. This new table, called Salesman_Customers, is also a dependent table because it links to the Customers and Salesmen tables. The Sales table links to the new Salesmen_Customers table rather than to the Customers and Salesmen tables individually. To make these changes you must first remove the Customer_ID and Salesman_ID fields from the Sales table and add the Salesman_Customer_ID field.

Next you need the Salesman_Customer table. This table will have three fields:

• Salesman_Customer_ID—The table’s primary key, which will be used by the Sales table to link to salesman/customer combinations.

• Salesman_ID—This table will link to the primary Salesmen table.

• Customer_ID—This table will link to the primary Customers table.

Finally, the database needs some new relationships, as follows:

• A link between the Salesman_Customer_ID field in the Sales table to the Salesman_Customer_ID field in the Salesman_Customer table.

• A link between the Customer_ID field in the Salesman_Customer table to the

Customer_ID field in the Customers table.

• A link between the Salesman_ID field in the Salesman_Customer table to the

Salesman_ID field in the Salesmen table.

The new Salesman_Customer table now contains one record for every possible combination of the salesmen (x) with the 

customers (y), for a total of (X*Y) records.

The added dimension, namely the

Salesman_Customer table, sits between the dependent Sales table and the primary

Customers and Salesmen tables.

At this point you have created a new dimension that condenses the salesman and customer data into a single table. There’s more you can do, if the sales table contain three pieces of information: the month of the sale, the year, and the day of the week, you can condense it into a new dimension. The result is a new table called DayMonthYear that contains one record for each possible combination of day of the week, month, and year.The Sales table

links to this new table through a field named DayMonthYear_ID, and the new table in turn links to the three primary tables DayofWeek, Months, and Years.

The procedures are essentially the same as for the customer and salesman data.

Multidimensional data can be kept in an Access database. But a multidimensional data structure is often used to enable efficient queries and analysis in huge databases that are beyond the capabilities of Access, databases with tens of millions of records. Such huge databases are usually managed with specialized data-warehouse applications such as

Microsoft SQL Server Analysis Server or Oracle OLAP Server. These applications are maintained by IT specialists and if you need to work with their data, these specialists will give you information about accessing them.

A dimension is the highest level grouping. Dimensions are used to group data into hierarchical (parent/child) relationships. Dimensions commonly used in data analysis include people, location, time, products, and similar categories.

Within each dimension are two or more levels. A level represents a data element that is part of the specified dimension. For example, you could define a dimension called People with two levels, Customers and Salesmen.

Each level contains one or more items called members. The Customers level contains the members Acme Metal Works, S&Q Manufacturing, and so on. Likewise, the Salesmen level contains the members Jackson, Anderson, and so on.

A measure is a summary of a data value.  A sum, average, or count in a PivotTable cell, for example, is a measure. In the current example, the measure would probably be the sum of the Sales values. A cube file can have one or more measures.

The term cube is applied to the files used to store dimensions, levels, members, and measures. The term comes from the fact that the structure of multidimensional data is sometimes pictured as a three-dimensional cube, with each physical dimension of the cube representing a conceptual dimension in the data. This visualization works only for data with three dimensions, of course, but the image and the name have stuck.

The important thing about cube files is that they contain already summarized data rather than raw data. This relates to the ability to analyze huge datasets without overloading your system or experiencing long waits. There’s nothing magical about this; the fact is that much of the analysis had already been made when the cube file was created. Your program, in this case Excel, needs only to retrieve the

summarized data from the cube file to populate the PivotTable.

Another important thing to be aware of is that you can define multiple cube files for a given data source. When you or someone else creates a cube file, you specify what it will contain— what the dimensions will be as well as the levels and members. A large and complex database is likely to need several cube files, and you must use the one that contains the summary you need.

Finally, a cube file enables you work with your PivotTable when the database itself is

unavailable or you are offline.

If your IT department has provided you with access to an online cube, you can create a PivotTable from it by following the procedures in this section. You will need to know the name of the server and your logon information (if the server requires a logon).

The section assumes that the online cube is based on Microsoft SQL Server Analysis Services. If another database system is in use, the procedures will be somewhat different but should follow the same general pattern.

1. Click the Insert PivotTable button on the Insert ribbon to open the Create PivotTable

dialog box.

2. Select the Use External Data Source option.

3. Click the Choose Connection button to open the Existing Connections dialog box.

4. Click the Browse for More button to open the Select Data Source dialog box.

5. In the Select Data Source dialog box, click the New Source button to open the Data Connection Wizard.

6. In the first step select Microsoft SQL Server Analysis Services and then click Next.

7. In the next step enter the server name and your logon credentials; then click Next.

8. In the next step select the database and cube. Then click Next.

9. The final screen lets you change the data connection file name, the description, and the Friendly Name (the name that is displayed in connection lists), or you can just click Finish to return to the Create PivotTable dialog box.

10. Click OK to close the Create Pivot Table dialog box and create the blank PivotTable. You will see the cube fields displayed in the field list.

Field List for PivotTables created from OLAP data looks different from non-OLAP data.

First of all, what you see here will depend on how the cube was defined.

There is a hierarchical arrangement that reflects the dimensions and levels within each dimension.

Each dimension—for example, Customer, Product, and Promotion—is in effect a field and can be added to the PivotTable to define its structure. Each dimension has an adjacent + symbol. Click that symbol to expand the dimension to show its levels. These are not fields, and you can’t drag them to the PivotTable. They are listed to show you what each dimension contains. When you add a dimension to the PivotTable, its levels go along automatically.

Several value fields are present as well—Store Cost, Store Sales, and Unit Sales. These are what was specified as the summary data item in the cube when it was created.

1. Click the OLAP-based PivotTable to make it active.

2. Click the OLAP Tools button on the Options ribbon and select Offline OLAP from the menu. Excel opens the Offline OLAP Settings dialog box.

3. Click the Create Offline Data File button to start the Create Cube File wizard.

4. Click Next to continue.

5. The next step displays the dimensions that are available in the data source. Select the dimensions that you want included in your offline cube by clicking the adjacent box. To select specific levels within a dimension, click the adjacent + symbol to display a dimension’s levels. When selecting levels, keep the following in mind:

• Omit lower levels that you do not need in order to reduce the size of the offline cube file.

• Make sure you include any levels that have grouped items.

• You cannot skip intermediate levels within a dimension.

• If a dimension does not display a + button, it means that you cannot pick and choose levels but must include or exclude the entire dimension.

6. Click Next to move to Step 3 of the wizard. Here you select the measures that will be available for use as value fields. Select Measures to include them all, or expand the Measures branch to select specific measures.

7. Click Next to go to the final step. Enter a name and location for the cube file, and then click Finish.

At this point the offline cube will be created, a process that may take a while if large amounts of data are involved. The PivotTable will now be linked to the offline cube. You’ll be able to work with the data even without a connection to the database, and many operations such as pivoting or filtering may well work a lot faster. Of course, dimensions, levels, and measures from the original data source that you did not include in the offline cube will no longer be available.

There are some differences in a PivotTable created from OLAP data is the same as any other PivotTable.

You cannot drill down in the data of most OLAP-based PivotTables by double-clicking a data cell. In fact, the database administrators can define various actions that a cube supports. Thus, double-clicking may drill-down, it may open a URL, or it may initiate some other custom action. This is all based on the cube and database setup and not something you can change in Excel.

You can’t change the calculations used by the measures, such as by changing a sum to an average. The summary calculation is specified and performed when the cube is

created and you can’t change it from within Excel.

On the Options ribbon, the Formulas commands are not available because a PivotTable based on OLAP data does not permit the use of formulas.

Levels in the data may not be visible in the PivotTable. In fact, those levels are present in the table but initially hidden. You use the Expand/Collapse commands to control the display of levels. At present, it seems that the PivotTable has to be in classic

PivotTable layout for this to work properly, but this is hopefully something that will be

fixed.

Suppose that you want to write a formula that references a number in a PivotTable, and that number happens to be in cell G15. Well, you can simply use the cell reference G15 (or perhaps $G$15), right? Unfortunately, things aren’t that simple. Why not? Just think of some of the things you can do with a PivotTable: pivoting it, of course, as well as showing or hiding detail and changing the sort order. These and other manipulations can cause a particular number to change its position. The summary data that were in cell G15 may now be in cell H22!

Obviously, you can’t reliably retrieve data from a PivotTable using the standard Excel cell references. What to do? Enter the GETPIVOTDATA function.

The GETPIVOTDATA function is designed specifically to retrieve data from a PivotTable based not on the data’s cell address but rather on its logical position in the PivotTable.

Let’s look at the syntax for this function:

GETPIVOTDATA(data_field, pivot_table, field1, item1, field2, item2,...)

• data_field is the name of the value field that you want to retrieve (the field that you added to the Values area of the PivotTable).

• pivot_table is a reference to any cell or range of cells in the PivotTable.

• field1 and item1 are respectively the name of the first field and the first data value associated with the data you want to retrieve.

• field2 and item2 are respectively the name of the second field and the second data value associated with the data you want to retrieve (You can have as many as 126 field and item pairs).

All arguments to GETPIVOTDATA except for the cell reference must be enclosed in quotes.

A restriction on using the GETPIVOTDATA function is that the PivotTable cell it

references must not be excluded from the table by a filter. In this situation, the

GETPIVOTDATA function will return #REF. Unlike with previous versions of Excel,

however, the GETPIVOTDATA function works even if the cell it references is hidden (for example, by collapsing detail).

Suppose you want to retrieve the sum of sales in the Accessories category for the month of Jan and use that figure elsewhere in the worksheet, outside of the PivotTable. The proper function would be:

=GETPIVOTDATA(“Sales”,$A$3,”Month”,”Jan”,”Category”,”Accessories”)

• The “Sales” argument is used because the name of the value field in this PivotTable is Sales. That is, when the PivotTable was created, the Sales field was added to the Values area of the PivotTable.

• The $A$3 argument identifies a cell in the PivotTable. It could be any other cell in the

PivotTable.

• The “Month” and “Jan” arguments go together. They specify that you want to retrieve a value where the Month field contains the value Jan.

• The “Category” and “Accessories” arguments also go together. They specify that you want to retrieve a value where the Category field contains the value Accessories.

If you include only a single field/item pair in the argument list, the function returns the

corresponding total. For example, the function

=GETPIVOTDATA(“Sales”,$A$3,”Month”,”Jan”)

returns the total for all data where Month=Jan

If you omit any mention of field and item, the GETPIVOTDATA function returns the overall total for the specified data item.

=GETPIVOTDATA(“Sales”,$A$3) returns the  overall total of Sales items.

• If the pivot_table argument refers to a range that contains two or more PivotTable

reports, data are returned from whichever PivotTable was most recently created.

• Calculated fields, calculated items, and custom calculations can all be returned by

GETPIVOTDATA.

• If the pivot_table argument refers to a cell or range where no PivotTable is located, the function returns #REF.

• If the field and item arguments refer to data that do not exist in the PivotTable, the

function returns #REF.

Excel makes entering the GETPIVOTDATA function really easy for most situations. All you need to do is to enter an operator in a cell (an equal sign at the beginning of the formula or +-/* as part of a formula) and then click the cell in the PivotTable whose data you want.

This cell can be an individual data cell or a total cell. Excel will then automatically enter

the correct GETPIVOTDATA function in the formula.

There may be times when you want to reference a cell in a PivotTable report by its actual cell address rather than by generating a GETPIVOTDATA function. For example, if you are writing some formulas outside of the PivotTable to perform calculations on its data, you may want relative cell addresses to adjust automatically when you copy the formula to

other cells. You can set this up simply by typing the address into your formula rather than by clicking the cell. You can also turn off the PivotTable feature that automatically generates GETPIVOTDATA functions when a cell is clicked. To do so, you must add a button to one of your toolbars as follows:

1. Click the Office button at the top left of the Excel screen.

2. Click the Excel Options.

3. Select Formulas from the list of option categories.

4. In the Working With Formulas section, turn off the Use GetPivotData Functions for

PivotTable references.

5. Click OK.

With this option turned off, if you click a PivotTable cell while entering a formula, a regular cell reference is generated instead of a GETPIVOTDATA function.

The GETPIVOTDATA function does not use any reference to the report filter or page fields in a PivotTable report.

The report filter fields control which data are summarized in the PivotTable, while the GETPIVOTDATA function returns a specific piece of those data. Changing the report filtering may change the value returned by

the GETPIVOTDATA function, of course, and as long as you understand this you can use the GETPIVOTDATA function correctly.

You can use the GETPIVOTDATA function on PivotTable reports based on OLAP data. One difference is that in the function arguments, the item can specify the source name of the dimension as well as the item name itself. Also, arguments are enclosed in brackets.

For example, look at this GETPIVOTDATA function that retrieves data from an OLAP-based PivotTable:

=GETPIVOTDATA(“[Measures].[Sum Of Amount]”, $A$3,”[Date]”,”[Date].[All].[2001]”,”[Person]”, “[Person].[All].[Acme Metal Works].[Anderson]”)

In this example, Date and Person are dimension names and not level names. Because the syntax of GETPIVOTDATA can be rather complex when working with OLAP data, I recommend that you always use the shortcut and let Excel generate the function arguments for you.

One of the nice aspects of the GETPIVOTDATA function is that the result it returns does not change when you show additional levels of detail by expanding one or more fields.

This is true of all PivotTables, whether they are based on OLAP data or not. Of course,

the opposite is not true. If you create a GETPIVOTDATA function that refers to a cell in a PivotTable report and hide that cell with the Collapse command, the function will return #REF.

Your goal is to create a standard Excel chart (not a PivotChart).

The strategy you follow has two parts. The first is to use the GETPIVOTDATA function to pull the required numbers out of the PivotTable and place them in a regular Excel table.

The second and easier part is to create a chart from this new table.

1. Open the workbook containing the PivotTable that you created in example 9.

2. Decide on a location for the new table. It can be in a new worksheet or the one that contains the PivotTable.

3. Enter a title for the table, Max River Heights at Power Plant, in a cell.

4. In the cell below the title, enter Date.

5. In the cell to the right of the Date label, enter Max Height (M).

6. In Column below the Date label, enter the seven dates covered, 7/1/2005 to 7/7/2005.

7. Format all the text you entered as bold.

The next step is to enter a GETPIVOTDATA function in the cells of the table that you just created to refer to the proper cells in the PivotTable report. For example, consider the cell in the new table just to the right of the 7/1/2005 label. You want this cell to display the maximum height for the Power Plant for that date, located in cell C5 in the PivotTable.

Here’s what to do:

1. Place the cell pointer on the cell just to the right of the 7/1/2005 label in your new table (not in the PivotTable).

2. Enter an equal sign (=).

3. Click the source cell in the PivotTable. Excel enters the appropriate GETPIVOTDATA function in the cell.

4. Press Enter to complete entry of the formula.

5. Repeat these steps to enter the appropriate GETPIVOTDATA function in the remaining cells of the new table.

The final steps are quite simple and require only that you use the Chart Wizard to make a chart from the new data table you just created.

1. Select the entire data table, excluding the cell with the title in it.

2. Click the Column button in the Charts section of the Insert ribbon.

3. Select the 2-D column sub-type at the top left of the menu.

You started with raw data in a worksheet. You created a PivotTable based on those data and then created an Excel table

that summarized certain aspects of the PivotTable data. Finally, you created a chart based on your summary table. If the original data were to change in any way, all you would need to do is refresh the PivotTable to have the changes reflected in all these elements.

You can copy a PivotTable and paste it in a new location, even in a new workbook, and the copy will continue to function just like the original. You’ll be able to refresh data, pivot the table, and so on. If you copy the PivotTable to a new workbook, and the original data are in another Excel workbook, the data reference in the copied PivotTable will still reference the original data location. The copy is partially independent from the original—you can apply a filter or expand/collapse data in one and the other will not be affected.

You can use this ability to copy a PivotTable and retain full functionality to create a master PivotTable workbook that contains multiple PivotTable reports copied from multiple workbooks.

PivotTables based on external data, including OLAP cubes, can be copied in the same way. Another reason to create a copy of a PivotTable is that it enables you to pivot or filter the copy differently from the original and display both versions at the same time.

1. Click any cell in the PivotTable.

2. Click the Select button on the Options ribbon, and then select Entire Table.

3. Press Ctrl+C or click the Copy button on the Home ribbon.

4. Click the cell in which you want to place the top left of the PivotTable. The cell can

be in the same worksheet, in another worksheet in the same workbook, or in another workbook.

5. Press Ctrl+V or select Paste from the Home ribbon.

If the PivotTable is in its final form, you can use Paste Special to copy the displayed data only. The result is plain data, not a PivotTable, just as if you had typed the data in. You cannot refresh or pivot the table. To copy a PivotTable as data, follow these steps:

1. Click any cell in the PivotTable.

2. Click the Select button on the Options ribbon, and then select Entire Table.

3. Press Ctrl+C or click the Copy button on the Home ribbon.

4. Display the Paste menu from the Home ribbon.

5. Select Paste Values.

Of course, you can also use Paste Special to copy any part of a PivotTable, such as a single cell or an entire column or row.

1. Click any cell in the PivotTable.

2. Click the Move PivotTable button on the Options ribbon. Excel displays the Move

PivotTable dialog box.

3. Select the destination:

• New Worksheet—Excel creates a new worksheet and places the PivotTable at

cell A1.

• Existing Worksheet—Excel moves the PivotTable to the location in an existing

worksheet that you specify in the Location box.

4. Click OK.

Excel offers a wide range of data analysis tools in addition to PivotTables. It’s a good idea to know about these techniques so that when you are faced with a data analysis task, you can choose the best technique.

As powerful as PivotTables are, they are not always the best choice—in fact, sometimes they are too powerful and something simpler will serve you better.

The Excel subtotal tool makes it easy to generate subtotals based on values in the data. Sure, you can create a PivotTable report for this purpose.

If you want to use subtotals, the data must be sorted on the field on which you want to

subtotal.

1. Place the cell pointer on any cell in the column (field to sort) in the data table.

2. Click the A-Z button in the Sort & Filter section of the Data ribbon.

3. Place the cell pointer on any cell in the data table.

4. Click the Subtotal button on the Data menu to display the Subtotal dialog box.

5. In the At Each Change In list, select the field on which the subtotals will be based (the field sorted ).

6. In the Use Function list, select the function to be used to calculate the subtotals. You can choose between Sum  and several other measures.

7. In the Add Subtotal To list, place a checkmark next to the field or fields you want subtotaled.

8. Select the following as desired:

• Replace current subtotals—If this option is selected, any subtotals already in the

data table will be replaced with the new ones. If it is not selected, the new subtotals

will be included in the data table along with any existing ones. If there are no subtotals

in the data table, this option has no effect.

• Page break between groups—Excel inserts a page break after each group. (This is relevant for printing only.)

• Summary below data—If this option is selected, each subtotal will be displayed

below the group of records it is subtotaling, and the grand total will be displayed in the last row. If this option is not selected, each subtotal will be displayed above the group of records it is subtotaling, and the grand total will be displayed in the first row.

9. Click OK.

• Average—The average of the values (sum divided by number of values).

• Count—The number of values, not including text data or blank cells.

• Count Numbers—The number of non-blank cells, including both text and number data.

• Max—The largest value.

• Min—The smallest value.

• Product—The product of the values.

• StDev—The standard deviation of the values, estimated for the sample.

• StDevP—The standard deviation of the values, estimated for the population.

• Sum—The sum of the values.

• Var—The variance of the values, estimated for the sample.

• VarP—The variance of the values, estimated for the population.

To remove all subtotals from a data range, click the Remove All button in the Subtotal

dialog box.

You are not limited to creating one level of subtotals for your data. You can nest them,

subtotaling by one field and then, within those groupings, by another.

Page 233

The outline section, and the controls in this area enable you to hide and display different levels of detail.

Three controls are available in the outline area:

• The Hide Detail button is displayed when the rows in a group are visible. Clicking the

Hide Detail button hides the rows.

• The Show Detail button is displayed when the rows in a group are hidden. Clicking the

Show Detail button displays the rows.

• The Level buttons each represent a level of organization in the list. Click a Level button to show all the detail for the level of the button and hide all detail below.

Subtotals are easy to use and the fact that the subtotals are displayed along with the

data may be an advantage in some situations. However, the use of subtotals is

dependent on the data being organized in a certain way, and restricts the ways in

which they can be sorted. Also, there is no reliable way to get data out of a data table

that includes subtotals—in other words, there is no equivalent of the GETPIVOTDATA function. Subtotals certainly have their uses but are not capable of performing most of the robust types of analysis for which PivotTables are designed.

Database is any table of data with column headings identifying the fields, just the kind of data that is commonly analyzed with PivotTables.

The database functions perform the same calculations as other Excel functions, such as sum, average, and standard deviation. What sets them apart is that they include only values that meet one or more criteria.

The database function names all start with D. The remainder of the name describes the function and is the same as the name of the equivalent non-database function.

• DAVERAGE returns the average of selected database entries.

• DCOUNT counts the cells that contain numbers in a database.

• DCOUNTA counts nonblank cells in a database.

• DMAX returns the maximum value from selected database entries.

• DMIN returns the minimum value from selected database entries.

• DPRODUCT multiplies the values in a particular field of records that match the criteria in a database.

• DSTDEV estimates the standard deviation based on a sample of selected database

entries.

• DSTDEVP calculates the standard deviation based on the entire population of selected database entries.

• DSUM adds the numbers in the field column of records in the database that match the criteria.

• DVAR estimates variance based on a sample from selected database entries.

• DVARP calculates variance based on the entire population of selected database entries.

The functions all take the same arguments:

DXXXXX(Database, Field, Criteria)

where:

• Database is the worksheet range containing the data, including the first row of column or field names. It can be a range address such as A1:J150 or an assigned range name.

• Field is the name of the field or column whose values will be summarized by the

function.

• Criteria is the worksheet range where the criteria for the database function is located.

The criteria tell the function which rows, or records, to include in its calculation.

For example, to calculate the total number of titles for the Drama category, the criteria would in effect tell the DSUM function to include only those records where the Category field contains the value Drama.

At a minimum, the criteria range contains two cells in one column. The top cell contains the name of the field that the criterion applies to, and the lower cell contains the criterion itself.