Tutorial de Excel que explica cómo crear tablas dinámicas en Excel, se explica para qué sirven, los elementos que la forman y cómo adaptarla a tus necesidades de forma rápida y sencilla.
00:00Hi everyone, in this video, we're going to see how to create pivot tables in a clear and simple way.
00:11Pivot tables are used to analyze the data we have in an Excel sheet, so we need to start with an Excel sheet with different data.
00:20In this case, we have sales made in stores located in different cities that are classified into three different areas.
00:27North, South, East, the payment method used for the sale, and the category of the sale.
00:35So, the first thing we need to do is go to the Insert tab and select Pivot Table on the left.
00:42This window appears. The first thing it asks us to do is select a table or range.
00:49So, we select all our data and then it allows us to choose where we want to place the report.
00:58In this case, we're going to place it in this same spreadsheet, so we'll click on Existing Spreadsheet.
01:05We click on Accept and we'll see how a rectangle appears in this area, which is where our pivot table will be displayed.
01:11On the right-hand side, a window appears divided into two areas.
01:19An upper area with the different fields of our table, which would be City, Area, Sales, Payment Method, and Category, and four small squares at the bottom.
01:29We must drag the fields we need to each of them.
01:37Then, we must drag the fields we want to form, to be displayed as rows or columns, to the columns and rows.
01:44We must drag the cells we want to total for each row or column to the values area, that is, the values we want to display according to the rows and columns we have chosen.
01:57And the filters area is used to restrict the information we are displaying.
02:05So, let's see this with an example.
02:08Let's imagine that we want to show the total sales by category and by city.
02:12Then, we could drag the city to the columns area or to the rows area, since we want the sales by row or by city and by category, we drag it, for example, to the rows.
02:28We would drag the category to the columns and the fields that we want it to show us, which would be the sales, we drag it to the values area.
02:36We see how our dynamic table has already been created in which each of the cities appears in the rows and it adds up the sales for each of the categories.
02:53At the bottom, a row appears in which the sum for each of the categories is shown and in the last column, the total is shown, showing the sum for each of the cities.
03:06We can filter these categories by selecting this icon and deselecting the parts that we want to show or not.
03:15And in the rows area, we could also filter.
03:20So, when we filter the data, the icon changes and this icon appears in which we see that it is showing us all the data.
03:29We are going to leave it with all the data visible.
03:32Let's imagine that we also want to filter this content by the payment method.
03:41So, we want to see either the cash payment method or the card payment method, which would be a filter.
03:46We drag it to the filter area and see that the payment method appears here and we can select a single payment method to display the data or we can select all if we want all the data to be displayed.
04:02Let's imagine that in addition to showing the cities, we also want to show the area to which the sale belongs.
04:14If we place it at the bottom, below city, we see that it is placed in the same way.
04:28That is, at the top is the city, and just below the area, the area with the amount appears, which in this case would be the same, but if we change it, it groups it in a different way.
04:38For the Levante area, the city of Alicante and Valencia are placed below.
04:45For the northern area, Santander and Vigo are placed, and for the southern area, Granada and Seville.
04:52We see how in each row of the area, it gives us the sum of the two cities that it groups.
04:56By default, the values area calculates the sum, but let's see how we can also modify this according to our needs.
05:08If we left-click on Values and click on Values Field Settings, this window opens.
05:13We see that in Sum Values by we have selected Sum, but we could choose any of these options, for example, Average.
05:24We see how our data would change.
05:28We return to Settings, select Sum again in the next tab, Show Values as in this case.
05:34In this case it is without calculation, that is, it does not show us the values in any other way.
05:39But if we expand we see that we have other options and we can choose any of them.
05:45Let's choose, for example, Percentage of the Total.
05:51We click on Accept and see how instead of the value in Euros of each of the sales, it shows us the percentage of the total.
05:59So in this lower right corner we see 100%.
06:02Let's change it again.
06:06Let's show the values without calculation and let's see one last option that we can configure which is the number format.
06:16In this case it is with a general format.
06:19But since these are sales, we're going to format them as currency.
06:24We click on Accept, Accept.
06:27And we'll see how our sales adapt to the Euro, which is what we're using as currency.
06:32These tables can be modified and given a different design style.
06:41To do this, we simply go to Format as Table and choose the one that interests us the most.
06:46I hope you found it useful.
06:51You can download this file from our website to practice at home.
06:56If you found it useful, give it a like and subscribe to our channel to stay up to date with our news.
Sé la primera persona en añadir un comentario