How to Create Pivot Tables in Excel

Introduction to Excel

Excel, a fundamental tool of Microsoft Office, is renowned for its ability to manage and analyze data efficiently. One of its most powerful and useful functions is the pivot table, a feature in Excel where you click and drag fields to assemble the desired analysis and allows users to transform large volumes of raw data into organized and accessible information. This functionality not only simplifies the analysis of complex data but also offers flexibility in visualizing and summarizing information, becoming indispensable for data-based decision-making.

Creating Pivot Tables in Excel

Preparing the Data

Ensure that your data is organized in a table with unique headers for each column and each row representing a distinct record. This will facilitate further analysis.

Inserting the Pivot Table

Select your data set.
In the "Insert" tab of Excel, select "Pivot Table".
Excel automatically suggests the data range. Check if it is correct and decide where you want to insert the pivot table: in a new worksheet or in the existing one.

Structuring the Pivot Table

With the pivot table created, use the "PivotTable Field List" panel to:

Drag fields to the "Rows" and "Columns" areas.
Define the data summary in "Values".
Use filters to segment the data.

Customizing and Analyzing

The pivot table offers great flexibility for:

Changing the type of data summarization as needed.
Applying filters to focus on specific information.
Using conditional formatting to highlight important data.

Updating the Pivot Table

To reflect changes in the original data, right-click on the pivot table and select "Refresh".

Practical Examples

Below we have a table related to the sales of the first week of January 2024 from the brand's network in the following places: Ceará, Goiás, Minas Gerais, and São Paulo.

Based on the table, answer the following questions:

1- What was the total revenue per unit?

2- Which salesperson sold the most?

3- On which day of the week was the profit highest?

Before we start answering the questions, let's transfer this information to a pivot table. For this, it is necessary to select the entire table, click on “Insert” and then on “Pivot Table”.

After that, click on “From Table/Range”.

Then, click on “New Sheet”.

Upon opening your new sheet, you will notice a new tool “Pivot Table” in the right corner written in green, featuring both the tool to refresh the data source and to refresh everything on the left side, style variations for your table, among others.

Another novelty is the gray area on the right side of the screen “Pivot Table Fields”, with the categories of our table on the left side and the Filters, Rows, Columns, and Values areas on the opposite side.

The “Filters” area is used to filter and segment the data, while the Columns and Rows areas will distribute the information in columns and rows in your table. Finally, the “Values” area is where data consolidation is done to answer your questions and can be used to bring results, such as the Average and Sum functions.

To answer the question “What was the total revenue per unit?”, we will drag the “Unit” field to the “Rows” area and the “Price” field to the “Values” area, as shown in the image below.

By doing this, you will have the following table:

You can present this information with a chart by clicking on “Insert”, then on the chart icon of your choice. In the example below, the “Stacked Bars” chart variation was used.

Moving on to the second question, we will drag the “Salesperson” field to the “Rows” area and the “Price” field to “Values”, thus generating the chart below.

Lastly, the third question will be answered in such a way that the “Dates” field is in the “Rows” area and the “Price” field in “Values”, thus obtaining the chart below.

Centralizing Data in Excel

Mastering the art of creating pivot tables in Excel is just the beginning. The real transformation in your data analysis begins when you integrate and centralize information from various sources. With Kondado , you can take your Excel skills to the next level, accessing data from over 80 different sources directly in your spreadsheets. No credit card is required to start, and you can try our platform today for free. Try Kondado now and revolutionize your data analysis in Excel!

Conclusion

Pivot tables are essential for those who need to analyze and present data effectively. With this guide, you have a solid foundation to start exploring the possibilities that pivot tables offer, making them a valuable part of your data analysis.