by contextures.com
On Saturday, it’s the 38th anniversary of Microsoft Excel, which was released on September 30th, 1985. Eight years later, sometime in 1993, Pivot Tables were added, in Excel 5.0! Happy 30th Anniversary to Excel Pivot Tables.
Continue reading “30 Years of Excel Pivot Tables-Looking Back”
By default, the Pivot Table shows only the items for which there is data. In the example shown in this video, not all colours were sold to each customer. You may wish to see all the items for each customer, even those items with no data.
Continue reading “Show All Pivot Table Items To Compare Sales Easily-No Data”
If you build an Excel pivot table, and no data is entered for some items, there will be blank cells in the pivot table values area. See how to change those blanks to zero, or a text string, such as “N/A”
Continue reading “Show Zero in Empty Pivot Table Value Cells”
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.
Continue reading “Show Excel Pivot Table Subtotals Top or Bottom”
You can create custom PivotTable Styles in an Excel workbook, to fine tune the appearance of your pivot tables. Later, if you want to use those custom styles in a different Excel file, you’ll have to manually copy them, using the steps shown below.
Continue reading “Copy PivotTable Format to Different Excel File”
If you make a pivot chart in Excel, you might wonder why all the columns are the same colour. The video below shows a quick fix for that pivot chart problem, and create columns in different colours. Continue reading “Fix Pivot Chart All Columns One Colour”
After you set up a pivot table, you might want to discourage people from filtering the pivot fields, which could hide some of the data. This video shows a couple of ways hide the drop down arrows in the pivot table headings.
Continue reading “Hide Drop-Down Arrows in Excel Pivot Table”
A pivot table calculated field always uses the SUM of other values, even if those values are displayed with another summary function, such as COUNT. That can cause results that look wrong, but the video below shows a workaround to fix the problem.
Continue reading “Pivot Table Calculated Field Counting Problem”
The step-by-step video below shows how to unpivot data in Excel, using Power Query. This creates better source data that you can use to build flexible pivot tables. And this technique doesn’t change your original data!
Continue reading “How to Unpivot Excel Data with Power Query”
Pivot tables are a quick way to summarize your data, and they have another advantage too. After you build the pivot table, it’s easy to change the pivot table layout, to get a different type of summary. Watch the two videos below, to see a few examples.
Pivot tables help you organize and understand your data. They make it easy to summarize tons of information quickly, and you can change the layout to see different types of summaries.
So don’t get stuck in a rut with your Excel pivot tables, by using the same layout, over and over again. Try new things!
After you create a new pivot table, try out different layouts to see which one works best. With just a few clicks, you can tweak the layout of your pivot table to showcase your data in a whole new light.
To help you get started, here are two videos that show examples of pivot tables in action. Try something different today!
In the video below, you’ll see the steps to create a quick pivot table with Excel’s Recommended Pivot Tables feature.
Then, make simple changes to the layout, to get a different view of your data.
Video Timeline
If you have 2 or more fields in the Values area of the pivot table, Excel automatically creates a new field in the layout, called “Values”.
Watch this short video, to see how use the Values button, to move the data fields in an Excel pivot table. With these steps, you can change the pivot table layout from vertical to horizontal, or change from horizontal to vertical.
In the screen shot below, the pivot table is in Outline Form layout, and the Value fields have been moved to the Rows area.
Video Timeline
You can get the sample files for these videos, and more about pivot table layout changes on my Contextures website.
____________________
____________________
