In an office job it can happen that you have to manage data (monthly income and expenses, work reports etc.) and therefore you have to manage a large amount of figures, from which to extract a column with the calculations performed completely automatically. There is a way to speed up the work and it is called a pivot table : thanks to this functionality we will be able to speed up the calculations by inserting them into a precise and programmed table, in which we will only have to make sure that we enter the data correctly (the spreadsheet will do the rest by automatically applying the required formulas to the rows and columns containing numbers). If we don't know how to create a pivot table, in this guide we will show you the steps to be performed both on Microsoft Excel (included in Office) and on LibreOffice ; in the latter case we can create pivot tables without spending anything on the purchase of a license.
READ ALSO -> How to make a table on Excel
Before continuing it is important to point out that, in order for the pivot table to be created without problems, we must enter the data into the spreadsheet in a precise and scrupulous way : the headers must be present at the top of each column and row, they must not empty lines must be present and the data entered must be as homogeneous as possible (for example in a calculation column we only have to insert digits if we want the table to work). Following these simple rules it will become really easy and fast to create a pivot table with Microsoft Excel.
How to create pivot table on Microsoft Excel
Excel is undoubtedly one of the most used programs for creating pivot tables. If we have the Office suite installed on our system, we open the Start menu at the bottom left and type on the Excel keyboard, so we can open the program.
Once started we click on Empty Workbook to open a new spreadsheet. Now we can enter the data that we have to process in the various fields, making sure to arrange them in order by column and by row (thus facilitating the work when creating the table).
Once all the data have been entered, select them by dragging the mouse (keeping the first cell pressed at the top left and moving the mouse) then click on the Insert menu at the top and confirm on the PivotTable button, present at the top left.
A confirmation window will open, in which to confirm the intervals already selected and choose where to generate the final report; in most cases just click OK without touching anything else. The data will disappear to make way for a warning message and a sidebar on the right.
To start composing the table, click on the boxes present just below the search bar to enable the various fields (which will correspond to the names of the columns inserted as a header), then reorder them by dragging it into the sectors under the heading Drag the fields to the areas below .
Basically the program will automatically sum the entered numeric fields, but if we wanted to change the formula or the mathematical operation to be applied, simply go to the lower right on the Values box, click on one of the available numeric fields (in our case we will use Input field ) and select the Value field settings item. From this window we can choose the mathematical operation to be applied to all the data entered in the table: we can do the average, insert a maximum and minimum value, subtract etc.
Once the configuration is complete, simply click OK ; if we have done all the steps well we will see a pivot table with all the data and a new row with the sums automatically applied appear in the spreadsheet (alternatively we can also find the result of the formula we have chosen to apply).
To modify other options of the newly created pivot table, just right-click anywhere in the table and select the Pivot table options item.
How to create pivot table on LibreOffice Calc
We want to create a pivot table at home but we don't have a license for Microsoft Office "> LibreOffice, which has a good spreadsheet inside the suite.
If we have not already done so, download the LibreOffice installer to our PC (this suite is available for both Windows and Mac, as well as being integrated in the vast majority of GNU / Linux distributions). Once downloaded, install it without fear, since the installation proceeds without particular difficulties or strange windows to consider (we can also always press Next to speed up the operation). Installed the suite on Windows we click on the bottom left on the Start menu and type Calc, so we can open the relevant search result ie LibreOffice Calc .
We will find ourselves in front of a full-blown spreadsheet: therefore we begin to insert the data that will make up our pivot table as already seen in the part relating to Excel. At the end of the data entry, we select all the fields with numeric values and headers (by dragging the mouse to create a selection) and click on the Insert pivot table button on the top of the function bar.
A confirmation window will appear: we click OK, so on show the new window for pivot table settings.
In the window we move the fields available on the right (in our case Month, Income and Expense ) in one of the sectors in the center or on the left, in particular we insert the fields that contain numbers in the sector Data fields and the fields that contain names or claims in the sector Row fields or Column fields . When ready we click OK to generate the pivot table.
Basically the program allows you to make the sum, but if we wanted to change the mathematical operation to be performed we must click on the Functions button, present on the thin bar that appears on the right side of the program.
We can immediately use those already indicated inside or click on the drop-down menu recently used and select All or one of the categories of functions integrated in Calc.
READ ALSO -> How to make a table on Word
