A PivotTable is a summary of a large dataset that typically includes totals, average, minimum, maximum, and so on.
Let's say you have sales data for different regions, with a pivot table, you can summarize the data by region and find the average, etc.
Pivot tables allow us to analyze, summarize and visualize only relevant data in our reports.
Use a pivot table in Excel
Before you start using pivot tables in Microsoft Excel, you need to make sure that the data in your source does not contain blank lines.
This is not to say that you can't have some blank cells, but a whole blank row will cause problems. In the spreadsheet you can see below, there is a blank row, this would be a problem.
You should prepare the spreadsheet to make sure that contains the immediate data you will use.
To quickly remove them:
- Go to Home > Search > Go to Special > Empty spaces > Delete lines.
- Now click on one of the cells in the source data and click on the » Inserisci «.
- Once there, find the group " Table »And click on» Pivot table ". The wizard for creating a pivot table should appear.
Remember that the data preselection shows the range in the section above the wizard. You can change it if you like, but as long as the base data has an adjacent range, it should be correct.
That's why you need to make sure that there are no blank lines before starting the process.
- Then leave the PivotTable Placement option on the » New worksheet »Default and do click su OK.
- Excel will open a new worksheet and place it there pivot table . It might not seem like a big deal. but you have created your pivot table.
At this moment the board is empty. You should also see something new on the right edge of this worksheet. In that place is where you will find the available fields of the PivotTable and the four areas where you can place them.
In case you don't see it
Click inside the pivot table on the left side of this worksheet. If you still don't see the fields in the pivot table, you should check the group " Show »In the details» Analyze "To make sure that" Field list »Is selected.
Make sure the background is dark gray by clicking » Field list ". This way you can use pivot tables in Microsoft Excel.
The most common problems when using pivot tables in Excel
The errors that we can find in the use of dynamic tables in Excel can be many and due to different problems. Here we will mention a couple of the most common problems:
Use numbers as texts
Sometimes it happens that when data is exported externally, the numbers that arrive in the file are in text format.
Regardless of whether they are seen as numbers, the Excel program interprets them as if they were texts and for this reason it does not perform the task of adding. It should be noted that if you wish, you can name your spreadsheet so that you can identify it respectively.
Use older versions of Excel
It is recommended to use the latest versions of Excel, both for making pivot tables and for any other type of work.
Older versions, like 2003, have a different type of format, so you may get an error when trying to use a file created in newer versions of Excel.
