by contextures.com
While you’re working with pivot tables, in complex Excel workbooks, it’s a good habit to make backup copies frequently. With my free Excel backup tool, you can make backups while you work, just by clicking a button.
Continue reading “Back Up Pivot Table Workbooks With Free Excel Tool”
Pivot tables make it easy to work with your data, and see the summaries in different layouts. To add or remove fields from the layout, you can check or uncheck fields in the PivotTable Field list. In large pivot tables, with lots of fields, this macro makes it easy to remove specific fields from the Values area. Just select one or more fields, and run the macro!
Continue reading “Remove All Selected Pivot Table Value Fields With Macro”
With a report filter in your pivot table, you can choose a region name, or employee, to show the data for selection only. This macro automatically chooses each item in the report filter field, and prints the pivot table, showing that data. It’s a quick way to print out a set of monthly reports.
Continue reading “Excel Macro to Filter and Print Quick Pivot Table Reports”
If you make an Excel pivot chart to show monthly data, a line chart might have all the dates in a single line. This video shows how to group the dates, if needed, and how to show years in separate lines. It just takes a simple step – move the Years field to the pivot chart’s Legend (Series) area.
Continue reading “Show Years in Separate Lines in Excel Pivot Chart”
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”
