PIVOT TABLES SCRIPT
In this blog we'll cover everything you need to know about pivot tables.
First, we will cover the basics of analysing data using pivot tables, then we go to the visualization of pivot tables, with slicers, timelines, bar charts, and finally we will see how we can manipulate advanced pivot tables and do things like grouping, calculate new fields and more, then get to work.
DATA ANALYSIS WITH PIVOT TABLES
Here we have an excel spreadsheet, which is available in the following link.
As we can see, we are with a data of sales of
natural products, in itself it is about products that are exported.
What we want to know is the total sales or
revenue, which product or products had the best sales
* What
is out total revenue? * What products sell best?
And many other things, which will help us make
better business decisions.
Table Format
First we are going to format table to our data, being within the range of our table, press Crtl + T and give OK, if we want we can name our table, also, if you want to master the use of table format I invite you to watch the followign blog https://jaimeisrael87.blogspot.com/2023/02/beneficios-y-caracteristicas-de-formato.html.
This is typically one of the best practices,
from here we are going to enter a pivot table.
Insert PivotTable
We are going to Insert and then only select PivotTable with one click.
Here it shows us a window in which it tells us that we can insert the pivot table in a new spreadsheet or in the existing one, we select in a new spreadsheet and press OK.
Being here we find the fields of our pivot
table, in itself we head them of our table is shown here, below these is where
we can do our analysis.
For example, if we want to see the income of
each product, we simply go to the header, select the income and drag them to
this sum of values field, as you can see it presents us with the sum of sales
of all products, but we want to break them down by product, so we will put it
in the row field, as you can visualize,
It will begin to autocomplete in this area.
Now suppose we want to change the format of
these values.
We can right-click, select number format, then
simply go to number, here we can use the comma separator, we can also reduce
decimals since you want to see only whole numbers, press Accept.
Now if we can visualize the numbers of a business way, we can see that they are in total of my products around 182.
Add variables to columns
Similar to rows, we have the field of columns
where we can drag data, for example, the continent field, and if we want to
delete any, simply select and drag out.
Order values from highest to lowest
Now, how to do, if we want this table to be ordered from highest to lowest? Well, for this it is as simple as clicking right, we are going to order, and select from highest to lowest, which facilitates the visualization and we can quickly identify which product is the one that presents us with the most income.
View values as proportions
Similarly, if we want the proportions for the
percentage of the total we can also right click,
Can we get the proportions?
I have to show values like, here we have enough
options for us, we select the one that suits us, in my case the percentage of
the total, this could serve us for a pie chart.
Which product are basically the best average sale ?
What if we want to know which products are sold
at the highest price,
Can we find the average sale price?
Which is basically the average sale?
Now, in the second part, which are the graphs
or visualizations, suppose we want to see the sales of the product by date.
For this, we go back to our pivot table and remove the averages we had, and place the income, in addition to that we want to see the dates as we mentioned, we can take the date field and place it in columns, but as you can see, it is not the best way to interpret.
Filters
So we are
going to remove the date and what we can do here is drag the date field and
place it in filters, now this will allow you to filter specifically by a
specific date, for example, I select a date then press ok.
Timelines
But if we are not convinced, we will leave that filter configuration, and instead, what we will do is go to PivotTable Analysis and insert a timeline, select the date field, and press ok. Now we just accommodate our window.
With this we can select specific times that we want, such as a specific month, a range or something similar, here instead of months we can configure quarters and select which quarter to display.
Silcers of data
Graphics
As the final part of the visualizations are the graphics, so first let's remove both filters by pressing delete. Again, within analyse pivot table, I click on pivot chart, select the default chart, and press OK.
ADVANCED PIVOT TABLES
Now let's continue with step 3 which are
advanced pivot table features.
Create new variable - profit
Here we can see the clean pivot table, and we
want to know the profit by product, but we notice that in our table data we do
not have the profit variable, so we have to create this variable, and we can
create it using calculated fields, for them we will only go to analyse pivot
table, we click on this icon, Click on calculated field.
In this window within name, we write profit and
in formula, simply our income minus expenses, for them we select income and by
double clicking we place it inside formulas, as you can see, minus costs,
likewise double click, we insert it. Finally, we click on Accept.
Now we can have the sum of profits per product.
Visualize a especific data from our pivot table
If we want to visualize a specific data from
our data table, for example, we can double click on that data, and we will be
presented with all the data in a new pivot table based on that selected field.
We return to our pivot table.
Grouping option from pivot table
Now we want to find out the earnings by dates, then I simply delete products and
instead add date, and instead of displaying by month we want to see by quarter,
fortunately we can use the grouping option, for this we right click, select
group.
Here we have quite a few grouping options, for example, we select months and quarters, here we can always collapse and expand, by right-clicking and selecting expand or collapse.
Add a new data from our data table
Finally let's see what happens if we add more data in our initial list, for this we go to the control page, we can go to the end by typing Crtl + down arrow, here I can copy a row and paste it to add a new sample product Melon.
This is why we have not updated our pivot table, for this we are going to analyze pivot table and click refresh and now if we can see the new product that we have added to our data table, So it's important to update that way every time we add or remove data from our table.
What we have learned is the traditional way of
working with pivot tables, but there is one more tool that many still do not
know.
ANALYZE DATA
Let's go back to our data table and we do not
go to the beginning, within table design we can see in the right corner, there
is a data analysis button, there we simply click on it, basically interprets
our data using artificial intelligence to give us several options as we can
visualize.
The pivot table is quite a powerful tool so
feel free to use it as needed.
If you liked it do not forget to give
"like", subscribe and activate the campaign, see you in my next blog.
In the followign video you can see an example of how to manage pivot table