by contextures.com
With a running total in a pivot table, you can see how amounts accumulate over a period of time, or through another field, such as products, or customers.
In the screen shot below, the Quantity field shows a running total across the Months column. In March, the Year To Date total for Corner Cabin is 2595.
Now that fall is here, you might be back into a routine of weekly meal planning, and Excel is a great tool to use for that.
To help you get started, there is a weekly meal planner on my Contextures website, that you can download. Enter the meal and product information, based on your favourite meals.
Then, pick your meals for the upcoming week, and click a button to create a weekly shopping list.
If you’re working with dates in a pivot table, it’s easy to group them by years, months or days – those are options in the Group By dialog box.
However, sometimes you might need a different type of date grouping. In this example, we’d like to group the sales data into 4-week periods, to match our company’s sales calendar. Keep reading to see the steps to set that up, and make sure the period starts on the correct weekday.
When you group a date field, Excel automatically creates creates groups for all the dates before the start date and after the end date. These items start with a “<” or a “>” symbol, such as “<2012-01-01” and “>2013-12-31”
Usually, you will only see these dates in the filter drop downs.
Continue reading “Grouping Shows Items Before and After Date Range”
In Excel, you can use array formulas to find MIN IF and MAX IF. For example:
Continue reading “Find MIN IF and MAX IF From Excel Pivot Table”
If you add more than one field to the Rows or Columns area in a pivot table, some of the fields automatically get subtotals. In the screen shot below:
The Grouping dialog box isn’t available if you try to group items in a text field. Instead, you can manually group the items.
Continue reading “Group and Ungroup Text Items in Pivot Table”
To pull data from a cell in a pivot table, you can use a normal cell reference, such as =B5, or you can use the GetPivotData function, which is specially designed to extract data from a pivot table.
In a pivot table, you can show multiple subtotals for a field, such as Sum, Count and Average. However, there isn’t a setting that lets you show multiple Grand Totals.
With a simple workaround, you can replace the Grand Total with a new field, and show two or more Grand Total calculations. In the screen shot below, the Sum, Average and Max are being calculated.
This solution uses a new field in the pivot table’s source data, with a heading cell, and nothing else in the column.
That field is added to the pivot table, and subtotals are moved to the bottom of each group.
For written instructions, please visit my Contextures website: Pivot Table Grand Totals
To see the steps for showing multiple grand totals, please watch this short video tutorial.
_______________________
It’s easy to count things with a pivot table – just drop a field into the pivot table’s Values area, and change the summary function to count.
Sometimes you don’t even need to change the function to Count – it automatically uses the Count function if the field contains text cells or blank cells.
If you’re using a pivot table to count items, be careful to use a field in which there is an entry in every row in the source data. Excel doesn’t count empty cells when summarizing in a pivot table, so you might not get the result that you expected.
For example, if the source data has the District name missing in some records, we can use a pivot table to count those records. Put the District field in the Row area, and another copy of the District field in the Values area.
The pivot table correctly counts the records where there is a District name, but can’t count the records where the District name field is blank.
This 3-minute video shows the steps for correctly counting blank items from the source data for a pivot table.
Get the sample Excel file, and written steps, on the Count Blank Cells in Pivot Table page on my Contextures site.
To correctly count records where the District field is blank, use another field in the Values area. In this example, there is a date field in the source data, and it has an entry in every row.
When you add the Date field to the Values area, you can see that it counts correctly, and shows how many records have a blank District field.
Next, we can remove the District field from the Values area, and change the heading for the Count of Date field.
The pivot table shows the correct counts, and the headings are easy to understand.
_________________
