Category: Group and Total

In an Excel pivot table, you can use Excel’s built-in custom calculations, for a different view of the data.

For example, in the video below, I set up a pivot table to show what % of a company’s  monthly sales were Binders. Also, what %  of Binder sales was for each colour – red, blue, and black.

Note: You can get the sample Excel file that I used for this video on the Pivot Table Show Values As page, on my Contextures site.

Binder Sales – % By Colour

In the screen shot below, the pivot table has 4 fields in the worksheet layout:

• Item and Colour in the Row area
• Month in the Column area
• Sum of Units sold, in the Values area

Following the steps listed in the section below, I used pivot table custom calculations, to show additional sales details:

• the % for each colour‘s sales – Black, Blue and Red
• compared to the item‘s total sales
• in each month – Jan and Feb

Show % of Item Sales

In the pivot table screen shot below, I’ve added a 2nd copy of the Units field to the values area.

• Tip: To add a 2nd copy of a pivot field, drag it from the PivotTable Field list, onto the Values area in the field list

For the 2nd copy of the Units field, I followed the steps below, to change that field’s calculation settings

• Right-click one of the Units value cells
• In the pop-up menu, click Show Values As
• In the next pop-up menu, click % of Parent Row Total

In the pivot table, the second Units field changed, to show:

• the % for each colour‘s sales
• compared to the item‘s total
• in each month.

For example,

• 195 Binders were sold in January
• 51% (100 units) of those January Binders were Black colour

More Pivot Table Info

For more Pivot Table Subtotal tips, videos and examples, visit the following links:

Show Percent of Subtotal in Pivot Table

Pivot Table Show Values As % of Parent Total

Show Percent Of Subtotal In Pivot Table

______________________

When you create a new pivot table, and add multiple fields to the row or column area, subtotals are automatically created. For Row fields the automatic subtotals usually appear at the top, and you can move them to the bottom, if that’s your preference. There are a few limitations though, which you can see in the video and the written notes below.

Continue reading “Show Excel Pivot Table Subtotals Top or Bottom”

Excel automatically adds grand totals to a pivot table, if there are multiple items in the row area, or in the column area. See how you can change the automatic grand total headings (sometimes), and quickly remove grand totals if you don’t need them.

Continue reading “Pivot Table Grand Totals Tips and Tricks”

When you set up a pivot table, and put fields into the Rows Area or Columns area, Excel groups the items, and calculates the totals for each group. For example, see count of products for each Unit Price. Each item should only be listed once in the pivot table, but sometimes you might see duplicates.

Continue reading “Duplicate Numbers in Pivot Table Items Problem”

Does your business have a fiscal year that starts in a month other than January? In a pivot table, there’s no built-in way to group or sort the dates by their fiscal  month number. See how to calculate those numbers in the source data, and then sort the data by fiscal month in a pivot table, with the month name included. Continue reading “Sort by Fiscal Month in a Pivot Table”

Excel is a great tool for working with a list of data, and calculating sums and counts. Use functions like COUNTIF and COUNTIFS, to get counts based on one or more criteria. But don’t forget about pivot tables, when you need a quick count or sum. With a few clicks, a pivot table will quickly show how many of each item are in a list.

Continue reading “Quickly Count Duplicates with Excel Pivot Table”

When you try to group dates in an Excel  pivot table, or other pivot table items, you might get a pivot table error, “Cannot group that selection.” In Excel 2010, and earlier versions, that error was usually caused by blank cells in your source data, or text in the number or date columns. If you’re using Excel 2013 or later, there’s another reason that might prevent you from grouping pivot table items. Continue reading “Excel Pivot Table Error Cannot Group That Selection”

In a pivot table, subtotals are automatically added to the outer fields, when you add more fields below them. The innermost field doesn’t show subtotals, but you can force them to appear, by creating pivot table custom subtotals. Learn more about pivot table subtotals on my Contextures website.

Continue reading “Pivot Table Custom Subtotals in Excel”

When a pivot table has grand totals, Excel automatically names those totals. I’ll show you some examples, with details on which grand total headings you can change, and which ones you can’t.

Continue reading “Excel Pivot Table Grand Total Headings”

Last week, someone asked me how they could get rid of the extra items that appear in the filter, when you group a date field. Maybe you’ve seen them too – they start with a less than or greater than symbol, and show the first and last dates in your pivot table data.

You can see an example in the screen shot below.

Continue reading “Grouping Dates Add Extra Items in Pivot Table Filter”