by contextures.com
When you add a value to an Excel pivot table, it automatically summarizes the values by Sum or Count. Later, you can change the setting, to use a different summary function. In this example, you’ll see how to use the Average function. You might see errors, and there are differences between a normal pivot table, and a pivot table where data was added to the Data Model.
Continue reading “Excel Pivot Table Values Problems with Average Function”
You probably use a few shortcuts when you work with Excel pivot tables. But do you use this pivot table double-click trick, to see the pivot field settings dialog box? Continue reading “Excel Pivot Table Shortcut for Field Settings”
The Pivot Table Top 10 Filter feature lets you focus on what’s working well. Create a report that shows sales from the top regions, or show the best-selling products. You can use this feature to show the opposite too. Which products are selling poorly? Which cities or regions aren’t doing well? These two short videos show quick ways to analyse sales data with pivot table top 10 filters.
Continue reading “How to Use Pivot Table Top 10 Filters to Analyse Sales Data”
After you create a pivot chart, you can change the number format for axis labels. This video shows the steps. Change just the pivot chart, or change both the pivot table and pivot chart number format.
You don’t need fancy formulas in a pivot table, if you want to show the difference between values. Use this built-in feature instead! With a couple of clicks, you’ll see the difference between sales in East and West regions. Or, see the percent difference in sales for this month and last month.
Continue reading “Show Difference in Values in Excel Pivot Table”
In some Excel pivot tables, you might want to prevent selection of the “(All)” option in a Report Filter. There’s no built-in option to do that. Instead, you can use this macro to stop people from choosing the ALL option.
Continue reading “Prevent Selection of ALL in Pivot Table Report Filter”
When you try to refresh an Excel pivot table, you might see an error message that warns you about an overlap problem: “A PivotTable report cannot overlap another PivotTable report.” To help you find the pivot table that is causing the problem, use this macro. It lists all the pivot tables where there are two or more on the same worksheet.
Continue reading “How to Find Pivot Table Refresh Overlap Problem”
When you create a new pivot chart in Excel, its number formatting is copied from the pivot table that it’s based on. See how to change the pivot chart number formatting, without affecting the pivot table’s number formatting. If you have several pivot charts to change, use a macro to make the job easier.
Continue reading “How to Use Different Number Format in Excel Pivot Chart”
Most pivot tables show numbers, so you can see a detailed summary of the source data. To add a visual element to the pivot table, add data bars that are similar to a bar chart. This works well in a simple pivot table, with only one or two columns of data.
Continue reading “Show Sales Amounts as Data Bars in Excel Pivot Table”
Usually, it’s easy to sort an Excel pivot table – just select one of the sort options – A to Z or Z to A – in the heading drop downs. However, you might run into a pivot table sorting problem, where the wrong item stays at the top. See why that happens, and how you can fix the pivot sort problem.
Continue reading “Pivot Table Sorting Problem Wrong Item at Top”
