by contextures.com
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”
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.
_______________________
