1. See which transactions make up a value in the PivotTable
Let me explain with an example. Let's say we have the following PivotTable.
Now let's say you want to see all transactions behind the £65,219 that are the sales Maria is having for the East region. Quite simply, we just need to double-click in the cell where €65,219 is located and voila! Excel will create a new sheet with all the transactions that make up the total of €65,219.
2. Summarize data by average or any other function
By default Excel summarizes data as Sum or Count depending on the data type. But what if we wanted to display the average or the maximum value. To do this, being in a cell in the PivotTable, right-click and select Summarize values by, and then select Average or any other preferred option.
Having the following result:
3. Fast "slice and dice" of the data
To modify a view of your data in a PivotTable is really easy. Simply drag Fields we had in columns to rows or vice versa. Or move them to filters or values, or remove them, or place different ones.
All this can be done quickly from the menu PivotTable Fields.
4. Display values as a percentage of the total, or of the row/column total
As we have seen in number 2 Excel summarizes data by default as Sum or Count depending on the data type. But what if we wanted to show the proportion of total or of the row/column total. To do this, being in a cell in the PivotTable, click on the right button and select Show Values As, and then select % of the Grand total or any other.
With the following result:
5. Add more dimensions to your reports with calculated fields
PivotTables allow us to use calculated fields which are columns that derive their value from the transaction between some of the other existing columns in the PivotTable.
Imagine that in our example, we need to know the ratio Profit/Loss and Net Sales. To do this, we do not need to add any more columns to our source data. Simply use a calculated field that defines the % Profit = (Profit/Loss) / Net Sales.
To do this within the tab PivotTable Tools, select Analyze> Fields, Items, & Sets> Calculated Field
Once done, the dialog box Insert Calculated Field is displayed.
Here we name the new calculated field, in my case '% Profit' and select how it is calculated, in my case select Profit/Loss divided by Net Sales.
The new field is automatically added to the PivotTable, so if you want to show only the new calculated field you will have to remove the old fields. There will also be a need to format the values, if for example they are percentages as it is in my case.
The result is as follows:
To see more of PivotTables click here.
No comments:
Post a Comment