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.

If you want to see from different points of view, you can click on this icon and change it to another view as an example the vertical or if you want you can return to the default view, similarly you can also drag the sale and place where you want.

Add variables to values and rows

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. 

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?

VISUALS PIVOT TABLES

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

For these filtering topics we can also use another great tool such as segmentation.  For example, suppose we want to add another variable that will be the continent, for this we could add it in the column area or instead we could go to Analyze PivotTable and select Segmentation (slicer). 

In this example, it will be for continent, so we select and give ok. 

And this is basically another way to filter our data,  I can select one, or several with this icon and also select all by pressing this icon.

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.

The good thing about these graphs is that they are dynamic based on what we add or remove from our pivot table, for example, we add the continent in columns, and we can see how it is updated automatically, within the graph we can also filter for example I remove a continent and press OK, and we see how everything is updated.

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.

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. 

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

CONCLUTION

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