We may not have the course you’re looking for. If you enquire or give us a call on +44 1344 203 999 and speak to our training experts, we may still be able to help with your training requirements.
Training Outcomes Within Your Budget!
We ensure quality, budget-alignment, and timely delivery by our expert instructors.
Creating Pivot Tables in Excel can revolutionise how you analyse and interpret data. Whether you’re a Data Analyst, business professional, or student, knowing How to Create a Pivot Table in Excel is a valuable skill that can streamline your Data Analysis process.
So wait no more; t comprehensive blog will provide you insights on How to Create a Pivot Table in Excel and you will learn to leverage its powerful features to gain insights from your data. So, if you’re ready to unlock Excel's full potential for your Data Analysis needs, let’s dive into the world of Pivot Tables!
Table of Contents
1) What is a Pivot Table in Excel?
2) How to Create a Pivot Table in Excel? Six easy steps
3) How to refresh a Pivot Table in Excel?
4) How to move a Pivot Table to a new location?
5) Conclusion
What is a Pivot Table in Excel?
To study trends based on your data, you can use a Pivot Table in Excel. It is a summary of your data wrapped in a chart. Pivot Table Excel interprets a lot of statistics on your screen. It enables you to group your data in many ways to reach fruitful conclusions more quickly.
The "Pivot" in Pivot Table Excel refers to the ability to spin the data within the table to see it from various angles. So, when you perform a pivot, you are not increasing, decreasing, or altering your data. Instead, you are rearranging the data to make it easier to extract information that will be beneficial with the help of MS Excel Pivot Table.
What is the use of Pivot Table in Excel?
Don't worry if you are still unsure what Pivot Tables are used for. It is one of those technologies that is much simpler to implement once you have a hold of it.
The goal of Pivot Tables is to provide simple methods for quickly summarising vast volumes of data. They can be used to understand, present, or evaluate numerical data in depth and to find and respond to unpredicted questions more clearly.
Here are seven situations where a Pivot Table acts as a solution:
How to Create a Pivot Table in Excel? Six easy steps
The following steps will help you understand How to Create a Pivot Table in Excel:
Step 1: Fill out a set of rows and columns with your data
Every Pivot Table in basic Excel begins with a fundamental Excel table containing all your data. Enter your values into the designated rows and columns to build the table. Use the top row or column to group your values according to what they stand for.
For example, if you want to create a table that records the blog post-performance data, you should have five columns that bring out the categorised data. Follow the steps below to understand the process.
Step 2: To make your Pivot Table, mark the cells
After entering the data in your Excel spreadsheet, mark the cells you want to compile in a Pivot Table. Click on “Insert” and then select the “Pivot Table” icon. To make a Pivot Table, highlight the cells you want to use.
You may choose whether to launch this Pivot Table in a new worksheet or keep it in the current worksheet. Additionally, it will specify your cell range in the choice box. At the bottom of your Excel worksheet, you can navigate any newly opened sheets. After selection, click OK.
Note: If you are using the latest version of MS Excel, then the tables option under the “Insert” option, you will find a separate section of tables. Then, click on the “Tables” icon to select Pivot Table.
Step 3: Sort your data by a specific property
After creating the Pivot Table, right-click the values to be sorted in the data and select the sorting order you desire to use. This order can be ascending or descending in nature and is generally used for checking on a specific property within the Pivot Table.
Step 4: Add a field by dragging it into the "Row Labels" section
When Step 2 is finished, Excel will produce a blank Pivot Table for you. The next step is to drag and drop a field into the Row Labels section with a label that matches the names of the columns in your spreadsheet. By doing so, you can choose the specific identifier—blog post title, product name, etc.—by which the Pivot Table will arrange your data.
To organise blog data by post title, you must click and drag the “Top pages” field to the “Row Labels” area.
Step 5: Add a field by dragging it into the “Values Area”
The next step is to add some values by dragging a field into the Values box once you have decided how to organise your data.
Let's use the blogging data example again. So, if you want to sum up views of blog posts by title, drag the "Views" field into the Values box to achieve this.
Step 6: Adjust your calculations as needed
According to what you want to calculate, you can easily modify the default calculation to anything else, such as average, maximum, or minimum.
Finally, you can adjust your calculations as per your requirements. Follow the steps in the succeeding images:
To access the menu, click on the tiny upside-down triangle next to your value and choose Value Field Settings. Follow the image below:
Drag and drop the fields into the value box and then to the filter box to find the actual position of the rows and columns. Follow the image below:
Now, you can find the total of the sum of clicks, the sum of Click Through Rate (CTR), along with the position and impressions of the blog post. Follow the image below:
Finally, when you have categorised your data as per your requirement, save your work and use it as and when needed. The above steps will give you a clear idea of How to Create A Pivot Table in Excel.
Build your career as a Data Analyst. Check out our Data Analysis Training Using MS Excel Course today!
How to refresh a Pivot Table in Excel?
To refresh a Pivot Table on Excel, you can follow these steps:
1) Click on any cell within the Pivot Table.
2) Go to the “PivotTable” or “Data” ribbon on the menu.
3) Look for the “Refresh” or “Refresh All” button on the Data ribbon to refresh the data. Additionally, you can look for the “Refresh All” drop down menu on the PivotTable ribbon which will also list “Refresh” pertaining to a single cell.
You can also set the Pivot Table to automatically refresh when you open the workbook at specific time intervals. To do this, go to the “PivotTable Options” or “Options” tab in the Excel ribbon. Under the “Data” tab, you’ll find the “Refresh data when opening the file” and “Refresh data every X minutes” options.
How to move a Pivot Table to a new location?
To move a Pivot Table on Excel to a different location, you can follow these steps:
1) Click on any cell within the Pivot Table to select it.
2) Go to the “PivotTable Analyze” or “Options” tab (depending on the Excel version), and select the “Move PivotTable” option.
3) In the “Move PivotTable” dialog box, select “New Worksheet” or “Existing Worksheet”, depending on where you want to move the Pivot Table to.
4) Click on the “OK” button to move the Pivot Table to the new location.
Note that when you move a Pivot Table, the data source and Pivot Table settings remain unchanged.
Conclusion
The fundamentals of creating a Pivot Table have now been covered. We hope this blog gave you a detailed idea on How to Create a Pivot Table in Excel, the actions possible with the Pivot Table and find the solutions you are looking for.
To learn in detail to create advanced formulas, macros, and much more, you can sign up for our Microsoft Excel Expert MO201 Course now!
Frequently Asked Questions
Excel functions such as VLOOKUP, SUMIFS, COUNTIFS, and AVERAGEIFS complement the use of Pivot Tables for more advanced analyses. These functions allow users to perform specific calculations and retrieve data from large datasets, enhancing the capabilities of Pivot Tables.
To maintain accuracy in a Pivot Table over time, users can regularly update the data source by refreshing the Pivot Table. They can also automate data updates using features like Power Query or Excel Tables, ensuring that the Pivot Table reflects the most current data.
The Knowledge Academy takes global learning to new heights, offering over 30,000 online courses across 490+ locations in 220 countries. This expansive reach ensures accessibility and convenience for learners worldwide.
Alongside our diverse Online Course Catalogue, encompassing 17 major categories, we go the extra mile by providing a plethora of free educational Online Resources like News updates, Blogs, videos, webinars, and interview questions. Tailoring learning experiences further, professionals can maximise value with customisable Course Bundles of TKA.
The Knowledge Academy’s Knowledge Pass, a prepaid voucher, adds another layer of flexibility, allowing course bookings over a 12-month period. Join us on a journey where education knows no bounds.
The Knowledge Academy offers various Microsoft Excel Training & Certification Courses, including Microsoft Excel Masterclass, Business Analytica with Excel and Excel Training with Gantt Charts. These courses cater to different skill levels, providing comprehensive insights into How to Create a Project Plan in Excel.
Our Office Applications blogs cover a range of topics related to Microsoft Excel, offering valuable resources, best practices, and industry insights. Whether you are a beginner or looking to advance your Excel skills, The Knowledge Academy's diverse courses and informative blogs have you covered.
Upcoming Office Applications Resources Batches & Dates
Date
Mon 18th Nov 2024
Mon 2nd Dec 2024
Mon 9th Dec 2024
Mon 16th Dec 2024
Mon 13th Jan 2025
Mon 3rd Feb 2025
Mon 10th Mar 2025
Mon 7th Apr 2025
Mon 19th May 2025
Mon 9th Jun 2025
Mon 14th Jul 2025
Mon 4th Aug 2025
Mon 8th Sep 2025
Mon 6th Oct 2025
Mon 10th Nov 2025
Mon 1st Dec 2025