In an Excel pivot table, if you try to group a date or number field, you might see an error message. The message box shows a yellow warning label, and the text says, “Cannot group that selection”.
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.
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.
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.
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.
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.
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.
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.