Blog post

How to use Pivot Tables in business

By Chris Onslow 02 Oct 2023

Pivot tables are a powerful tool in business for analyzing and summarizing large amounts of data. Here are the steps to use pivot tables effectively: 

1. Identify the data: Determine the dataset you want to analyze. This could be sales data, customer information, financial data, or any other relevant information.

2. Organize the data: Ensure that your data is well-structured and organized. Each column should represent a specific attribute or variable, and each row should contain a unique record.

3. Select the data range: Highlight the entire dataset, including the column headers, by clicking and dragging the cursor over the range of cells.

4. Create a pivot table: Go to the "Insert" tab in Excel (or any other spreadsheet software) and click on "PivotTable." A dialog box will appear, allowing you to choose the location of the pivot table (either a new worksheet or an existing one).

5. Choose the fields: In the pivot table field list, you will see all the column headers from your dataset. Drag and drop the relevant fields into the "Rows," "Columns," and "Values" areas. For example, if you want to analyze sales by region and product category, drag the "Region" field to the "Rows" area and the "Product Category" field to the "Columns" area. Then, drag the "Sales" field to the "Values" area.

6. Customize the pivot table: You can further customize the pivot table by right-clicking on any cell and selecting options such as sorting, filtering, and formatting. You can also change the summary function (e.g., sum, average, count) for each value field.

7. Analyze the data: Once the pivot table is created, it will automatically summarize and aggregate the data based on the selected fields. You can easily change the layout and rearrange the fields to gain different insights. For example, you can switch the rows and columns to view sales by product category and region.

8. Apply filters: Pivot tables allow you to filter the data based on specific criteria. You can apply filters to individual fields or use the "Report Filter" area to filter the entire pivot table. This helps in drilling down into specific subsets of data for deeper analysis.

9. Refresh the pivot table: If your dataset changes or new data is added, you need to refresh the pivot table to update the analysis. This can be done by right-clicking on the pivot table and selecting "Refresh" or by using the refresh button in the pivot table toolbar.

10. Create pivot table reports: Once you have analyzed the data, you can create reports or charts based on the pivot table. This allows you to present the summarized information in a visually appealing and easy-to-understand format.

By using pivot tables, businesses can quickly analyze and gain insights from large datasets, make data-driven decisions, and identify trends and patterns that can drive business growth and efficiency.

To view our Pivot Tables training course please click here


How AI can help your training and your business

AI is a hot topic for 2020. Read on to find out how AI can help your business and your staff training going forward, and how to understand AI better.


What are the benefits of employee-led learning?

How much control should you give your employees when it comes to their learning? Read on to find out the benefits of employee-led learning and how to implement it effectively.


Why mental health awareness training is crucial for businesses

With the right awareness of mental health, you can build positive team morale, boost productivity, retain loyal team members and help your business to thrive and grow. Read on to find training for you and your staff


5 important reasons why you should invest in Human Resources & Recruitment training

Human Resources is one of the most important departments in every business with a team of employees. Read on to find out the top 5 benefits of HR & Recruitment training, and how to find the best HR and Recruitment training courses.


The top 5 benefits of Office 365 for business

If you're already using Microsoft Office, but perhaps looking to upgrade to cloud-based, you may be wondering why Office 365 is a better choice? Read on to find out the top 5 benefits of Office 365 and why it's the best choice for businesses in 2020.


How to make your training budget go further

A huge part of our mission is to save you money while providing you with the best training possible. Read on to find out how to save money on training, without sacrificing anything. You may be surprised by some of these suggestions but we promise it’s all true!


5 most useful things to learn about Microsoft Excel

From the simplest spreadsheets through to complicated systems, everyone in your business should know how to use Microsoft Excel on a basic level. We provide you an overview of the most useful things to learn about Microsoft Excel and advice on how to learn them effectively.


The Secrets to Successful Group Training

Discover the secrets to successful group training and help to organise a training course at your offices which gets great results every time.