Complete Guide to Creating Pivot Tables

A pivot table is a powerful tool available in major spreadsheet programs, such as Microsoft Excel and LibreOffice. This tool allows you to create automatic summaries of numerical data, starting from a set of information contained in a spreadsheet. Pivot tables are particularly useful for analyzing large amounts of data efficiently, allowing you to summarize, sort, count, and calculate information in a few clicks.

This tool is indispensable for professionals, analysts, and anyone working with structured data, as it allows you to transform complex data into easily understandable and usable information. Pivot tables are useful for creating detailed reports, sales analysis, budgets, and much more.

By following the instructions carefully, you will be able to structure your data and obtain automatic summaries quickly and accurately.

Prerequisites

To follow this guide and create a pivot table, you will need the following tools:

• A computer with Windows, macOS, or Linux operating system.
• A spreadsheet program like Microsoft Excel or LibreOffice Calc.
• A spreadsheet with structured data ready for processing.

How to create a pivot table in Excel

At the end of this guide, you will be able to create a pivot table in Excel using your data.

• Open or create the sheet with the data.
• Select the data range you intend to process (or click inside any data cell to process the entire worksheet).
• Press the Insert option at the top and then the Pivot Table button.
• In the screen that opens, check the box next to the Select table or range option.
• Make sure the Table/Range box contains all the cells of the table (or the data range chosen).
• Check the box next to the New worksheet option.
• Press the OK button.
• If you want, you might check the existing worksheet box to add it to the sheet you are using, having to select the cells where to place it by clicking on the empty bar and choosing. Once chosen, click on the button next to the selected cells in the page window and voilà.
• Select the parameters to include for calculations from the right column.

Creating a pivot table from multiple sheets

• Create, in a new Excel sheet, a data model that incorporates multiple sheets.
• Start Excel, create a new sheet through the appropriate on-screen option (Empty Workbook).
• Select the Data tab and press the Get Data > From File > From Workbook option.
• In the screen shown (Navigation Tool), check the box Select multiple items and choose which sheets you are interested in linking to the new workbook, to generate the pivot table.
• Press then the Load button, to complete the operation and create the new query.
• Move to the Insert tab, click the Pivot Table button and, in the screen shown, select the External Data Source option.
• Press then the Select Connection button, move to the Tables tab and then click on the Tables in the Workbook Data Model option.
• If the data from the various sheets do not have linked data, it may be necessary to create relationships between the data, otherwise it would be impossible for the pivot table to generate a report. This operation can be performed via the Relationships button that you find in the Data tab at the top.
• Choose then whether to create the pivot table in the existing worksheet or in a new one, selecting the appropriate option and then click the OK button to complete the pivot table creation procedure.

Working on the pivot table

• Identify the PivotTable Fields area located to the right of the Excel screen.
• Select with the mouse one of the available fields (e.g., Reference Month) and drag it into the most appropriate area of the table (e.g., Columns).
• Repeat the operation for all the fields you want to include in the pivot table.
• Adjust the layout and calculations as needed.
• If you want to filter the data, use the filter options available in the pivot table.

Verify the pivot table

• Check that the pivot table displays the data correctly and that the calculations are accurate.
• Make sure the data range is correct and that no errors are present in the source data.
• Update the pivot table if the source data changes.

Troubleshooting common issues

• Missing or incorrect data: If the pivot table shows missing or incorrect data, check the source data for any errors or omissions. Make sure all data is included in the selected range.
• Incorrect calculations: If the calculations are not as desired, modify the summary values settings. Right-click on the summary values cell and select Summarize Values By to choose the correct arithmetic operation.
• Update problems: If the pivot table does not update automatically, verify that the data range is correct and that there are no errors in the source data. You can also force the update by clicking the Refresh button.

Creating a chart from a pivot table

To visualize the data of the pivot table in a more intuitive way, you can create a chart. Follow these steps:

• Select the pivot table.
• Go to the Insert tab.
• Choose the desired chart type (e.g., bar chart, column chart, pie chart).
• Excel will automatically create a chart based on the pivot table data.
• Customize the chart as desired, modifying titles, colors, and other options.

Now that you have carefully followed all the steps of this guide, you are ready to create pivot tables independently and accurately. Remember that the key to success lies in the correct structure of the starting data and the accurate choice of fields to include in the table.

To consolidate what you have learned, we invite you to put the knowledge you have acquired into practice immediately. Open your preferred spreadsheet software and start experimenting with your data. Every pivot table you create will bring you closer and closer to complete mastery of this powerful and versatile tool.

• Start with simple data to familiarize yourself with the process.
• Try combining multiple worksheets for more complex pivot tables.
• Explore the different data summary and display options.
• Create charts based on your pivot tables for immediate visual representation.

Good work and keep practicing to refine your skills!