Let’s dive into pivot tables in Excel, a very cool feature that is not easy to implement, until you learnt it. I believe it is the first entrance in ManejandoDatos related to Excel. If you want, I encaurage you to sign in in the webucator website.
What is a pivot table?
A pivot table is an interactive table that can be used to analyze, organize, and summarize large amounts of data. Pivot tables make it easy to rearrange data, quickly displaying it in different ways. Pivot tables usually have four types of fields:
- Page field (Report Filter). Use the Page field to filter data by a specific field.
- Column field (Column Labels). Use the Column field to display a field from the source data as column headings.
- Row field (Row Labels). Use the Row field to display a field from the source data as row headings.
- Data field (Values). The Data field is the field from the source data that you want to summarize or analyze.
Creating a Pivot table in Excel
Before inserting a pivot table, make sure the data you want to analyze is in an organized table. This is important, because you have to take the next things into account:
- Header row. Your data must include a header row because the top row of your table will serve as the field names.
- No empty rows or columns. If your data includes empty rows or columns, Excel will not treat all your data as one table.
- No subtotals. Remove all before creating your pivot table.
The way to insert a pivot table in Excel is: first, you need to select any cell in the set of data you want to analyze.Later, on the Insert tab, in the Tables group, click the PivotTable command.
You will get a DialogBox where you can introduce where your data is!
As you can see, I am using the table of ManejandoMondo, the free Excel that you can get to have montly statictics about your workouts.
Next, you need to drag the fields where you want them. In the next image, I want two filters, by year and by Activity Type. I want report by month, and the values I included: Calories, number of workouts and distance:
You can use other aggregation functions, not only sum or count but average, min, max, …. or even change the format of the numbers.
More features you can do with your Pivot tables in Excel
Also, you can add a Slicer to filter your pivot table (Segment data) by one or more rows. But also, you can add a Pivot graph if you want to visualize your data in a chart.
As you can guess, these features are very powerful, and are not difficult to manage.
In the following image I have added two slices:
Therefore, I can manage better with my data like this:
ManejandoDatos and Excel
Excel is a tool that I normally use a lot, but not for data analysis, although I should do it more often. Excel is very common in companies because it is easy to use but maybe it is not the right tool if you need to deal with a large dataset.
In my job I normally move data from Excel to databases. For that purpose, I use several tools, from VBA macros to Python.