A common pivot table question is "How can I create a pivot table from data that's on separate sheets in my workbook? Sometime people have a workbook set up with a separate sheet for each region, or for each salesperson. Eventually, they want to pull all the data together, and create a summary report in a pivot table, from multiple sheets.
In a pivot table, you might want to see all the orders that were shipped on a specific date. To do that, you'd move the Ship Date field to the Page area, and select a date from the drop down list.
Sometimes though, you'd like to show the orders shipped in a date range, instead of just a single date. For example, you might like to show orders with ship dates in the upcoming week, so you can do some planning.
To accomplish this, you could manually hide all the dates in the Ship Date page field, except the dates for next week. However, that might take quite a while if there are lots of dates.
Another option is to add a new field to the source data, to test the ship dates, then add it to the pivot table, as a filter. This might slow things down a bit, if your source data table is very large.
We'll look at both options in Excel 2003 -- the steps are slightly different in Excel 2007.
Manually Hide the Dates
In Excel 2003, there are no check boxes beside the items in a Page field's drop down list, to allow you to select multiple items. To hide some of the items in a pivot table's Page field, temporarily move the field to the Row area, and select the items there, then drag the date field back to the Page area.
Or, without moving the field in the Page area, you can change the field's settings.
- Double-click on the Ship Date field button.
- In the PivotTable Field dialog box, click on dates in the Hide Items list.
- Click OK to close the dialog box.
Add a New Field to the Source Data
Manually hiding the dates might work well if you only need to do this occasionally, and the list of dates isn't too long. Otherwise, the best solution might be to add a column to the pivot table's source data.
In this case, we'll add a column named ShipSoon, and use a formula to test if the ship date is within the next 10 days.
- In the source data table, add a column with the heading ShipSoon. In this example, the new column is to the right of column A.
- In the first data row of the new column, enter a formula that checks the ShipDate in that row. Our first ship date is in cell A2, and the formula will test the date, to see if it's within 10 days of today's date.
- Copy the formula down to the last row of data.
Each row will show TRUE or FALSE as the result of the formula.
Update the Pivot Table
Next, you'll update the pivot table, and add the new field.
- Refresh the pivot table, and add the ShipSoon field to the Page area.
- From the ShipSoon Page field dropdown list, select TRUE.
Refresh the Pivot Table
Remember to refresh the pivot table each day to see the current calculations for the ShipSoon field.
- To manually refresh the pivot table, right-click on a cell in the pivot table, then click on Refresh Data.
Or, you can set the pivot table to automatically refresh when you open the Excel file.
- Right-click on a cell in the pivot table
- Click Table Options
- In the Data Options section, add a check mark to Refresh on Open
- Click OK to close the dialog box.
If you select a cell in a pivot table and click the Chart Wizard button on the toolbar, a default pivot chart is created, on a new chart sheet. Sometimes you might want to step through the Chart Wizard, so you can set the options as you create the pivot chart.
Use the Chart Wizard
If you follow these steps, you can use the Chart Wizard, instead of creating the default pivot chart.
- Select an empty cell on the worksheet, away from the pivot table and any other data.
- On the Excel toolbar, click the Chart Wizard button.
- In Step 1 of the Chart Wizard, select a Chart type and Chart sub-type, then click Next.
- In Step 2 of the Chart Wizard, click in the Data range box, and select any cell in the pivot table. The entire pivot table will be automatically selected, and its address will appear in the Data range box.
- Complete the remaining Chart Wizard steps, selecting the options you want, then click Finish to create the pivot chart.
Note: If you select a pivot table as the data range, you can't return to Step 2 of the Chart Wizard. To use a different data range, create a new chart.
When you add a field to the pivot table Values area, it's automatically given a custom name, such as Sum of Units.
You might want to change the custom name to Units, so it's easier to read and makes the column narrower.
However, if you select the cell and type Units, you'll get an error message: "PivotTable field name already exists."
Use a Slightly Different Name
If you try to create a custom name that's the same as a field name in the source data, you'll see the error message. In this example, because one of the fields in the source data is named Units, you can't use Units as a custom name in the pivot table.
However, you can avoid this problem, by adding a space character to the end of the custom name, and it will be accepted.
Or, if you have multiple value fields in the pivot table, select all the captions, and use the Replace All command to fix all the captions at once.
- In the Find What box, type "Sum of"
- Leave the Replace With box empty
- Click Replace All.
This will leave a space at the start of the caption, so it's different than the original field name, and will be accepted.
When you add field to the Row or Column area of a pivot table, subtotals automatically appear in the outer fields. For example, in the following pivot table, Region and Employee are the outer fields, and show subtotals. Here's how you can turn off pivot table subtotals for some fields.
When you're analyzing data in an Excel pivot table, you might want to see the detail behind one of the numbers. To extract the data, you can double-click a data cell and a new worksheet is created, with the related records.
This is a nice feature, but you'll end up with extra sheets in your workbook, and will need to clean things up occasionally.
Filter the Source Data
If the pivot table source data is in the same workbook, you can use the following macro, written by Héctor Miguel Orozco Díaz. It filters the source data, based on the pivot items connected to the double-clicked cell.
For example, if you double-click the cell circled in screenshot below:
the source data is filtered for Class_A, Month_3, Store_1, Code_A cost.
This lets you focus on the detail records, without creating new worksheets.
Download the Sample File
In a pivot table, you might have a few row labels or column labels that contain the text “(blank)”. This happens if data is missing in the source data. For example, in the source data, there might be a few sales orders that don’t have a Store number entered.
Sometimes there are blank cells in a pivot table's source data. If you try to count blank cells in Pivot Table source data fields, you might run into a problem. Here are the steps to follow, to show the count of blanks.
In one of the Excel newsgroups last week, someone asked how they could update several pivot tables at the same time. They were tired of selecting each pivot table separately, and clicking the Refresh button.
In an Excel workbook you can refresh all the pivot tables and queries if you click the Refresh All button. That button is on the External Data button, which appears automatically if you click in a cell that's part of an external data range.
However, if you don't have any external ranges in your workbook, you can add the Refresh All button to the PivotTable toolbar, so it's easy to find and click.
The quickest way to add this button is to click the Toolbar Options button at the end of the PivotTable toolbar. If the toolbar is floating, you'll see a large triangle to the left of the Close button.
If the toolbar is docked, there's a small triangle in the bar at the end of the toolbar.
Click the Add or Remove Buttons command, then click PivotTable
You'll see a list of all the standard commands that can be added to the PivotTable toolbar.
Click on Refresh All, to add it to the toolbar.
Now, when you want to update all the pivot tables, just click the Refresh All button.
If you create a pivot chart in Excel, with dates along the axis, you might want to change the format of those dates. Here are the steps for formatting Pivot Chart dates.