All the Excel Pivot Tables are an advanced tool that presents a comprehensive summary of statistical data. They can be of great help to us when calculating, summarizing and analyzing data, being able to establish comparisons, as well as detect trends and patterns.
They are known as "pivot tables" because they do not have a fixed structure. They can be organized in different ways, according to our needs at each moment, to find the information we are looking for. But before that, we are going to explain the origin and meaning of this concept within the spreadsheets of Excel.
What is a pivot table?
To fully understand the concept of pivot tables, we must imagine them as a kind of flexible reports that we will be able to modify. For example, scrolling rows and columns on the screen, selecting the type of calculation to perform, etc. In addition, we will be able to do all this without the need to use formulas of any kind.
These qualities make pivot tables one of the best basic data analysis tools we can have in Excel. A method for find quick answers to any type of question that we can ask ourselves about any series of data.
Within the history of Microsoft Office, Excel's pivot tables were first introduced in 1995, with the fifth version of the spreadsheet program. It must be said that there was already a precedent: a software called improvise that had been developed by the company Lotus in the year 1991 in which, without a doubt, the developers of Excel were inspired.
The following versions of Excel were introducing new improvements in the dynamic tables and even in the Excel 2000 version it was complemented with the function of dynamic graphics. From then until today, the success of this idea seems unquestionable: a fabulous tool for summarizing and analyzing vast amounts of data.
How to create pivot tables in Excel
Creating pivot tables in Excel is a relatively simple task. As a previous step, we have to take a normal table, organized in rows and columns. Then, these are the steps we must follow:
- First we need to click on any of the source data cells.
- Then we go to the tab "Insert".
- Then we go to the group "Boards" and in it we click on the button "Dynamic table".
- Next, the Create PivotTable dialog box appears, in which we accept the default values.
After this, a simple but empty pivot table will be automatically created (see the image above). The next task is to configure your options.
We now go to the right side of the Excel window. There we find the panel "Pivot Table Fields", which comes with a list of all the fields we can select for our report. You have to drag the ones that interest you to one of the four areas shown at the bottom of the panel.
Once the fields have been inserted, the results of the applied calculations will be reflected in the cells of our table, without the need to introduce functions. Depending on the fields that we are adding or removing, the final result of the cells will be different (every time we want to see the changes reflected, we must click on the button "Update all").
That is the true "magic" of pivot tables in Excel: its versatility and its easy handling.
Pivot tables from other sources
In the previous example we have explained the process of creating a pivot table from an already existing data series, but it can also be done taking data from other sites. For that, we have to go to step 2 of the previous list (the one with the "Insert" button) and choose between the options that Excel gives us:
- Get from an external data source, which can be any other file on our computer.
- Get from data model. This option is used when you want to create a PivotTable from multiple tables or when you are working with very large data sets.
- Get from Power BI, only available if our team has the option to connect to databases in the cloud.
Finally, we must mention the existence of the option of "Create recommended pivot tables", which, in certain circumstances, can be very useful for Excel users. To access this option we have to go to step number three of the section "How to create pivot tables in Excel", in the "Tables" group, select "Recommended pivot tables".
At this point, it is the program itself that shows us a dialog box with various options based on the information in the original table. It is up to us to choose the table or tables that suit us best for our objective. It is not a widely used option, but it can be very practical in certain cases.