by contextures.com
This example is based on the Work Orders workbook, used in my book, Beginning PivotTables in Excel 2007.
If you’re working through the book, this video shows the steps outlined starting on page 46.
In the video, we create a pivot table that’s based on a formatted Excel Table.
Then, the Values field is changed from SUM to AVERAGE, and the numbers are formatted so they’re easy to read and understand.
___________________
In a pivot table, there’s no built-in way to group the pivot table data by both weeks and months at the same time. If you want to show sales data by week, you can group the date field in seven-day intervals.
The 7 day grouping works well, but if you try to add grouping by months, the Number of Days option is disabled. If you select Month in the Grouping dialog box, the days will lose their 7 day grouping.
Since you can’t group by both week and month, you can use a workaround instead. You could create a column in the source data, and then calculate one of the grouping levels there. Then, you could add that field to the pivot table.
For example, you can add a column that calculates the month name for each sales order date, by using the TEXT function:
=TEXT(A2,"mmm")
Add the Month field to the pivot table, above the weeks.
Another option is to add a column to the source data, with a formula to calculate the week number:
=WEEKNUM(A2).
With the OrderDate field in the Row Labels area, group the dates by months. In the Row Labels area, add the WeekNum field below the OrderDate field, to summarize the data by month and week number.
Note: If a week begins in one month and ends in another, it will appear under both months.
________________
In an Excel pivot table, you might want to hide one or more of the items in a Row field or Column field. To do that, you can click the drop down arrow for the Row or Column Labels, then remove the check mark for items you want to remove.
For example, to hide the data for 7-Feb-10, you’d click on the check mark to remove it.
Instead of searching through a long list of items in a drop down list, you can use a quick command to hide the selected items.
You can use a similar technique to hide most of the items in the Row Labels or Column Labels.
To see the steps for quickly hiding or keeping the selected items in an Excel 2007 Pivot Table, you can watch this short video tutorial.
____________
You can create a calculated field in a pivot table, to perform calculations based on the Sum of a pivot table field. For example, the formula below would calculate a bonus based on the total number of units sold for each product.
Continue reading “Problem With Pivot Table Calculated Field Totals”
There are many built-in PivotTable styles in Excel 2007, but you might not see one that has the exact formatting you need. For example, you might like the heading colors in the Pivot Style Medium 9, but you’d like a different color in the subtotal row.
Instead of using one of the built-in PivotTable styles, you can create a custom PivotTable style with the formatting you prefer. You can either duplicate an existing Pivot Table style, and modify the duplicate, or you can create a new style from scratch.
If you find a PivotTable style that’s close to what you need, you can duplicate that style, and modify the duplicate. Follow these steps to create a custom style, based on an existing style:
Next, follow the steps in the Modify the PivotTable Style section (below), to name and modify the new style.
If you don’t see anything similar to what you need, you can create a new style from scratch. Follow these steps to create a new style.
Next, follow the steps in the Modify the PivotTable Style section (below), to name and modify the new style.
After duplicating or creating a PivotTable style, follow these steps to name the style and set the formatting.
The new PivotTable style that you created will not be automatically applied to the active pivot table. Follow these steps to apply your custom style.
__________
In a pivot table, you usually sort the data by the values in a column, such as the Grand Total column. By sorting, you can highlight the highest or lowest values, by moving them to the top of the pivot table.
You can also sort a pivot table row by its values, left to right. This moves the largest or smallest amounts to the left of the pivot table, so you can focus on them.
For example, in the pivot table shown below, you can sort the Chocolate Chip row, so sales per city are sorted in descending order. Currently, the cities columns are in alphabetical order, with Boston at the left.
The values in the Chocolate Chip row are sorted largest to smallest, from left to right. The City column order has changed, and Chicago, which has the highest Chocolate Chip sales, is at the left.
Rows for other products may not be in descending order, because the column order has been set by the values in the Chocolate Chip row.
______________
When you add a field to the Values area of an Excel Pivot Table, it automatically shows the Sum or Count for all the items in that field. Here we can see the total labor cost for each Service Type.
If you add more than one field to the Row Labels or Column Labels area of the Pivot Table, a subtotal is automatically created for each field except the last one (the Inner Field). The subtotal, by default, uses the same summary function as the Value field.
In our example, the District field has been added to the Row Labels area. Service Type is now an Outer Field, and has a subtotal for each service. District is the Inner Field, and does not have subtotals.
Instead of using the default summary function for subtotals, you can select a different function. To change the setting:
For the Inner Field in the Row Labels or Column Labels area, the default subtotals are not displayed. So, in the pivot table shown above, there’s no subtotal for the District field.
However, if you create Custom Subtotals for an Inner Field, those subtotals appear at the end of the Pivot Table. In this example, Custom subtotals for Count and Max were created for the District field. You can see the District subtotals at the bottom of the Pivot Table, just above the Grand Total.
You can read more about pivot table subtotals, and the steps for changing them, on the Contextures website: Excel Pivot Table Subtotals.
To see the steps for changing the pivot table subtotals, and creating multiple subtotals, you can watch this short video.
___________
If you rearrange the fields in a pivot chart layout, the related pivot table changes too. Unfortunately, there’s no setting you can change if you want the pivot chart and pivot table to work independently.
As a workaround, you can create a second pivot table, based on the first one, and arrange it as you’d like. Then, when you change the pivot chart, only the original pivot table is affected. You can hide the first pivot table that’s connected to the pivot chart and use the second pivot table as for printing reports.
If you require several charts based on the same pivot table, but with different layouts, create multiple pivot tables based on the original pivot table. Create one pivot chart from each of the secondary pivot tables, and rearranging one pivot chart won’t affect the others.
___________
If you format a column of dates in your pivot table source data, to show as year and month (yyyy-mm), they won’t automatically summarize by year and month in the pivot table.
For example, in the source data shown below, there are several orders for January 2010, and three different dates are visible.
If you format those records as yyyy-mm, it looks like all the dates are the same.
However, when you add the OrderDate field to the Row Labels area of a pivot table, several items appear as 2010-01, instead of only one item.
The first instance of 2010-01 shows 3 orders, so that would be the Jan 2, 2010 orders. Below it is an item with a single order, and that’s a summary of the Jan 3, 2010 orders.
Even though the dates are formatted to look the same in the source data, the underlying dates are still recognized by the pivot table. Those underlying dates are used for the pivot table summary. If you click a date in the Row Labels, you see the underlying date in the formula bar. Because the dates are different, each date is listed individually.
Instead of formatting, you can group the dates in the pivot table to combine the data by month and year.
The orders will be grouped by year and month, showing a total of 38 for January 2010.
Grouping can cause some problems in a pivot table, such as preventing you from using calculated items. If you don’t want to using grouping, you could add a new column to the source data, and enter a formula that converts the dates to text. Then, all the dates that have the same year and month will calculate to the same text string.
In our example, the order dates are in column A.
The orders are grouped by year and month, showing a total of 38 for January 2010.
_________________
With Excel Pivot Table filters, you can limit the results to a specific part of your data. For example, you can view only the sales from the previous month, or see the products with more than $500 in sales.
If you’re interested in analyzing the best or worst results in your data, use the Top 10 filter feature in a pivot table. Although it’s called “Top 10”, you can see the Top or Bottom Items by value. You can also choose to see the values that make up a specific Percent of the total, or the products that add up to a specific SUM.
For written instructions for this Excel pivot tables feature, see Excel Pivot Table Filters – Top 10.
To see the steps in action, watch this short Excel video tutorial for the Top 10 Filter feature.
___________