Step by Step: How to Create a Pivot Table in Excel


We already know it. You typed something like this into your search engine: pivot table in Excel. The good news is that you've come to the right place, because we're going to explain what a pivot table is and how to create it. 


If you're looking to boost your Excel skills, learning to create pivot tables is essential. This tool lets you organize and analyze large amounts of information efficiently, giving you valuable insights that can boost your work or business. 


In this new article of CoderBlog, we'll guide you through the steps to create a pivot table in Excel simply and effectively.


Before we begin, what is a pivot table?


Before learning to create a pivot table in Excel, it's essential to understand what they are and how they work. 


In a few words, a pivot table is a tool that lets you summarize and analyze large amounts of data quickly and efficiently. 


Imagine you're a sales manager and you have a spreadsheet with the sales data of your products in different regions over the last few months. With a pivot table, you can easily summarize this data to see which are your best-selling products in each region, as well as perform calculations on total and average sales by product or region.


Now that you know what a pivot table is, let's learn to make one.


Step 1: prepare the data


The first thing you need to create a pivot table in Excel is to have your data correctly organized in a spreadsheet. Make sure your data is clean and structured in rows and columns, with clear headers for each column. This will facilitate the process of creating the pivot table and ensure accurate results.


Continuing with the previous example, we'll have a column for the sale date, another for the product sold, one for the region and one for the sale amount.





Step 2: select the data


Once your data is ready, select the range of cells you want to include in your pivot table. You can do it easily by clicking and dragging the cursor over the data area. Make sure to include all the relevant rows and columns in your selection.


If we continue with our example, we'll select the range of cells that contains the monthly sales, including the date, the product, the region and the sale amount.





Step 3: create the pivot table


Now that you selected the data, it's time to create your pivot table in Excel. Go to the "Insert" tab in the Excel toolbar and click on "PivotTable". A dialog box will open where you can select the location of your pivot table (it can be a new spreadsheet or an existing one) and customize its design according to your preferences.





Step 4: organize the fields


Once you've created your pivot table, you'll see a side panel open with a list of fields available in your data. Drag and drop the fields you want to analyze into the rows, columns, values and filters areas as needed.


For example, if we continue with our sales manager one, we'll drag the date field to the row and the sale amount field to the values. This will automatically show us the sum of sales per day.





Step 5: customize your pivot table


Once you've organized your fields, you can further customize your pivot table by adjusting the design, format and calculation options. Experiment with different styles, summaries and calculation functions to obtain the desired results. 


For example, we can customize our sales pivot table by adding a region filter and a monthly subtotal to get a more detailed view of what was sold by month and region.





Step 6: refresh the data


One of the key advantages of the pivot table in Excel is its ability to update automatically when your underlying data changes. If you add, delete or modify data in your spreadsheet, simply click the "Refresh" button in the pivot table toolbar to reflect the changes in your analysis.





Step 7: analyze and visualize the data


Once you've set up your pivot table according to your needs, you'll be able to perform a detailed analysis of your data and visualize the results clearly and concisely. Use the filtering, sorting and slicing options to explore different aspects of your data and obtain valuable information for decision-making.





Step 8: keep learning


Do you want to be a top-level Excel professional? 


With the Advanced Excel course at Coderhouse, you'll learn to use all of Excel's advanced functions, including pivot tables. In addition, you'll have access to additional learning material, like video tutorials and practical exercises, to help you consolidate your knowledge. 


Don't wait any longer to master Excel and stand out in your professional career.


If you want to keep reading about this or other topics, we invite you to explore our blog.

If you'd like to keep exploring this topic, you can also read how to build an AI project portfolio to land a job.

Recommended Coderhouse courses

If you want to go deeper into data analysis and applied artificial intelligence, Coderhouse has programs for all levels:

• Data Analytics Course: to learn to analyze data, build dashboards and make decisions based on real information.

• AI Engineering Course: to incorporate AI and machine learning models into your data projects.

• Introduction to Artificial Intelligence Course: to understand the AI ecosystem and complement your data profile with knowledge about language models.