How to create a Pivot Table in Excel in seconds (without previous knowledge)
QuickRows is an Excel add-in that offers you an alternative solution to creating a Pivot Table, in seconds and without the need of having previous knowledge.
If you are reading this article it is because you are an entry-level Excel user and you want to learn how to create Pivot Tables quickly in order to analyze your data.
The Pivot Table is one of the most powerful tools in Excel, but it requires the understanding of multiple concepts that can be confusing, among others: data segmentation, filters, type of totalization (count, sum, average, etc) or ‘dynamic cache’. Therefore, mastering this tool requires time and dedication to watch tutorials or read blogs and documentation to solve your doubts.
QuickRows offers you an alternative solution to create Pivot Tables in seconds, without the need to have previous knowledge. In the following video you can see how to do it, with just one click:
What is a Pivot Table and how does it work?
A PivotTable is an advanced tool for calculating, summarizing and analyzing data that allows you to see comparisons, patterns and trends in them.
Some of the most advanced users consider it to be one of the most powerful tools in Excel, allowing you to analyze large amounts of information quickly and easily.
According to an article on the ‘Ayuda Excel’ platform, the PivotTable is so fast because Excel generates a copy of the source data (called a ‘pivot cache’) to work with instead of the original data.
When you make changes to the Pivot Table, Excel does not use the source data but the pivot cache. For this reason it is essential to update the pivot table every time you enter new records or fields.
How do I create a PivotTable with QuickRows?
QuickRows allows you to generate pivot tables immediately, acting directly on the data source. This gives the user greater convenience and speed. Not to mention that it is a much simpler process.
We will use an example to show the speed and simplicity with which data can be analyzed.
Let’s suppose that we have a table with the data of the invoices that have been generated in a company over a year, with the following information (see image below):
- Name of the person responsible for the purchase
- Product purchased
- Cost per unit of product
- Number of units purchased
- Total cost
As a first example, we are interested in knowing the amount of money spent in each department of the company. To do this, we select any cell belonging to the column ‘Department’ and press the “Ctrl + 2” key combination . This is the result:
We have obtained a table with the cost data per product unit (Unitary), number of units purchased (Quantity) and total cost (Amount) for each department. Also, a slicer has been generated automatically (see details below), which allows us to filter the data for each department.
If we were only interested in totalizing the ‘Amount’, it would be enough to select also a cell of that column before pressing “Ctrl+2”. The result would be similar to the previous one, but more precise:
How to take a Pivot Table to the next level?
The actions we have executed so far are just the tip of the iceberg of the potential of a PivotTable generated with QuickRows. Here are four ways to take better advantage of it and analyze your data in more depth in a simple way:
- Use a matrix table
- Use slicers
- Use special filters
Uses a matrix table (with two or more dimensions)
Let’s see how to add a second PivotTable with QuickRows, continuing with the example we used previously.
This time we will create a matrix table, using the columns ‘Name’ and ‘Date’. To do this, we select any cell belonging to the two columns and hold down the “Ctrl” key.
The first column we select is totaled horizontally and the second column is totaled vertically. When we finish selecting the columns, we press “Ctrl+2”. This is the result:
In the image above we see both Pivot Tables. The first one totals the column ‘Department’ and the second one the columns ‘Date’ and ‘Name’ combined. Notice that at no time did we have to convert dates to months or apply numeric formatting.
Slicers provide buttons that you can select to filter the Pivot Tables data. In addition to quick filtering, the slicers also indicate the status of the current filtering, making it easier to understand what is displayed.