One of the most used tools in the office when we work on spreadsheets is the pivot table, which allows you to gather data in an orderly way and perform calculations quickly and effectively. With a Pivot Table it is possible to organize data, list unique values, create reports, filter data and analyze them without the need to write complicated formulas. Those who have been working with Excel for a long time most probably already know how to create a pivot table on the fly, but if we are novice users or we work recently in an office, we can recover the necessary knowledge in this guide, where we will show you how to create a pivot table on Excel . If in the home environment we prefer to use Google Sheets (the spreadsheet offered for free by Google), in this guide we will show you how to make a pivot table on this service, so that you do not have to necessarily buy Microsoft Office for the calculations and tables made in home or for educational purposes.
READ ALSO: How to create a pivot table and what it is used for
How to make pivot table on Excel
To begin the creation of the pivot table, we insert all the data that we will have to use within an empty spreadsheet in Excel, possibly using separate columns and rows for each data group (a list of expenses or financial income for example, divided by each month of the year). In order for the table to be as precise as possible, we always add headings at the top of the columns, so as to make the creation of the table very simple.After inserting the data into the cells, we select all the data by holding down the left mouse button on the item at the top left and including all the cells, then go to the top menu Insert and click on the PivotTable button.
A small window will open, where we will only have to confirm the selection of cells and where to save the pivot table (whether in the same spreadsheet or in another). After viewing the data, we click OK to generate the pivot table; a side window will open on the right, where we will have to choose the fields of the pivot table and choose the filters, columns, rows and values to insert into the table. Most of the time it is sufficient to check the items present in the upper part of the composition window to see the pivot table already formed (and preset to make the total of all the columns).
If the data for the table is sorted enough, we will not even have to intervene in the other Columns and Rows fields, since they will already be correctly filled in (in case of problems we can always intervene, so as to modify the structure of the pivot table that we are creating).
If we want to perform subtractions, multiplications, divisions or other types of calculation, go to the Values section at the bottom right, click on one of the items present and select Value field settings, so that we can choose which type of calculation to perform.
From this same window, click on Number format and choose what format to give to the data in the table (if we are doing a calculation with Euros, we choose Currency ).
In a similar way we can quickly generate pivot tables by going to Insert and click on the Pivot Tables recommended button; in this way Excel will carry out most of the adaptations, leaving only the insertion of the calculation fields and the modification of the values entered inside it.
How to make a pivot table on Google Sheets
Even on Google Sheets (or Google Sheets) we can create pivot tables in an extremely simple and immediate way, without even having to install a program on our PC (therefore we can use the service on any computer, even corporate).First of all, let's go to the Google Sheets page, log in with a Google account in our possession (also the Gmail account, YouTube or the account created for the Google Play Store on an Android smartphone is fine), then click on the model identified as Empty .
Once the spreadsheet is open, we insert our data into the rows and columns, taking care to create a header for each column created.
Now select all the data entered with the left mouse button, then click on Data -> PivotTable to start the creation of the table.
A small window will open, where we will have to choose whether to place the pivot table within the same spreadsheet or in another sheet created for the occasion; after making your choice, click Create .
The table editor will now open, in which we will only have to choose the elements to be inserted: then click on the Add button next to each data to fill in the pivot table with the data in our possession. For the example shown, we will have to add to the Rows field every month, while to the Values field we will have to enter the income and expenses (using the Add key twice).
The pivot table will be filled in completely automatically with the row of totals and the values already expressed in Euro; if we want to use another mathematical function instead of the sum, just act on the scrolling menus under the items Summarize by .
If we want to take advantage of Google Sheets and the other work tools offered by Google even without an Internet connection, we continue reading on our Google Drive Offline guide to open documents without the Internet .
Conclusions
As we have seen, creating a pivot table on Excel and Google Sheets is really very simple: this explains why they are among the most used calculation tables in offices and public administration (but we can use them without problems for any type of calculation, even when we are at home).If we want to become real experts in using Microsoft Excel, we recommend you read our guides on How to make calculations on Excel and how to use Excel formulas to count numbers and cells (COUNT and SUM).
If, on the other hand, we are looking for ready-made models to manage expenses, finances and more with Excel, we invite you to read the article on the best Excel models to download for free to manage expenses, finances and much more .
