When you have a large amount of data, you may want to rearrange the data in your PivotTable to make it easier to work with. You can also add or change the fields in a PivotTable.
Select a cell anywhere in the PivotTable.
Select Analyze > Field List.
In the PivotTable Fields pane, select the fields you want to show in your PivotTable.
Typically, non-numeric fields are added to Rows, numeric fields are added to Values, and Online Analytical Processing (OLAP) date and time hierarchies are added to Columns.
To rearrange fields, drag the fields to the areas you want them to be.
Filters are the top-level report filters, above the PivotTable.
Columns are shown at the top of the PivotTable.
Note: Depending on the hierarchy of the fields, columns may be nested inside higher-level columns.
Rows are shown on the left side of the PivotTable.
Note: Depending on the hierarchy of the fields, rows may be nested inside higher-level rows.
Values are the summarized numeric values in the PivotTable.
Note: If you have more than one field in an area, you can rearrange their order by dragging them. To delete a field from the PivotTable, drag the field out of its area. Any changes to the data in the PivotTable do not change the source data.
Select any cell on the PivotTable.
Select Analyze > Refresh.
Or, press Alt + F5.
Now the word pivot, we typically don't really use as we talk about pivot tables but here's where the word actually comes from.
Imagine this list right here, if we could pivot this from the upper left-hand corner.
Imagine a diagonal line from upper left to lower right, we could somehow twist this and then put these words down the left-hand side in column A, then these across row 4.
Pivoting is not a single action and as I suggested, most people don't even use the word as they talk about pivot tables, but if we would like to pivot this data we're simply going to reverse the locations or switch the locations of Customer and Region.
And so in the PivotTable Fields list we're gonna switch Customer and Region. And in no particular order.
So I'm going to drag Customer down and into the ROWS area and the interim view has some merit possibly, sometimes.
And now I'm going to drag Region from the ROWS area into the COLUMNS area.
We've pivoted the data.
As you manipulate data here, sometimes the layouts are gonna be not so attractive.
Keep in mind, Ctrl + Z will let us go back and you can press it repeatedly.
At all times we are not harming the source data.
There's always a sense of freedom there too when you say, well gee, could I really mess this up, could it look awful?
Well, that's possible, but you're not in any way altering or threatening your original source data.
Now we can have two fields in the ROWS area or in the COLUMNS area, much more likely in the ROWS area.
Maybe now we'd like to see some relationship between Customer and Region and Product.
Let's bring Region into the ROWS area and then Product into COLUMNS.
I'll drag Product into the COLUMNS area.
Now it's a busier pivot table, it's got more data.
Maybe for presentation that's a bit much but on the printed page it's not a whole lot, and if you're simply analyzing the data you're not overwhelming yourself.
Keep in mind, there are audiences of course who do want to see this much information.
If you do change your source data, if you want your pivot table to be up to date, you must refresh it.
One of two ways: Analyze tab Refresh, or Alt + F5, that's the keystroke shortcut.
Now we don't have to be situated on this cell but just keep an eye on the 199, I'll click above it.
We're going to update this pivot table with Refresh.
And now we see the 199 has become 200.
You gotta keep track of that and be on top of that idea.
The pivot table is now refreshed.
Learning doesn't stop here. Discover more expert led tutorials at LinkedIn Learning. Start your free trial today, at linkedin.com/learning.
Learn from recognized industry experts, and get the business, tech, and creative skills that are most in demand.Benefits
Get unlimited access to over 4,000 video courses.
Receive personal recommendations based on your LinkedIn profile.
Stream courses from your computer or mobile device.
Take courses for every level – beginner to advanced.
Practice while you learn with quizzes, exercise files, and coding windows.
Choose a plan for yourself or your entire team.