A Pivot table is a table created using excel on top of your data that enables you to slice and dice data according to your criteria. A pivot table can summarize data for you and helps you create meaningful reports. A Pivot table doesn’t change the original data set and only creates a view on top of your original data. If you are looking for how to create pivot table in Excel online then you are at right place.
How to Create Pivot Table in Excel?
Let’s take a pivot table example data, create/copy paste the data from above excel or you can download the excel by clicking this link pivot excel. This data contains the students’ name and their marks in three subjects – English, Math, and Science. This excel also has student’s class name and age. Now, looking at the data set, one wonders – what are the total marks for each student. We can easily use a sumifs function in excel and find it out. Now, imagine 1000 such rows. If you have 1000 rows of data then it might be difficult for you to sum up or to find the right data. The pivot table can create a summarized view for you in seconds.
Pivot Table Definition
In a pivot table, you can store data and make it representable as a chart. With its chart, you can report or explore the trend depending on the data and facts. These are used when you have large data in long rows and columns where you can store values. The best way to tell what is a pivot table excel is, it creates a group of data in various ways so that you can draw a helpful conclusion more easily and frequently.
Step 1: Select the data set.
Step 2: Click on ‘Insert’ next to ‘Home’ in top ribbon.
Step 3: Click on ‘Pivot table’.
Suggested Read: Pivot Chart
Step 4: Window pops-up. Click ‘Ok’
Step 5: Excel opens a new worksheet and presents you with pivot table options.
Step 6: On the right-hand side, there is field chooser. On the top right-hand side is Field Name. You should choose the fields and drag them into rows and column.
Step 7: Hold field ‘Student Name’, drag and drop the same into ‘Rows’; drag and drop ‘Marks’ field into ‘∑ Values’ box in the bottom right corner.
Step 8: By default ‘Sum of Marks’ will start displaying in this box.
Step 9: Look at the table on the left; it now presents the sum of marks per students.
Step 10: You can Close the box if it is not in use by clicking anywhere on excel outside the data.
Now that you have created a basic pivot table. Let’s see commonly used functions. Let’s see if we can sort this data based on marks.
Step 11: Right-click on any grid B4, B5, B6 or B7 and click on Sort. Excel will give you two options.
Step 12: Click on ‘Sort Largest to Smallest’. You will see the pivot table sorted by Marks. The pivot table tells us that Johanna has scored the highest and Vikas has scored the last rank in an entire school.
Let’s try to put some filters. Now, we want to see only the students in Class A or Class B. Pivot tables provide you with interesting features filter.
Step 13: Click anywhere inside the pivot table. The pivot playground will open on the right. The first box below the ‘Field Name’ filters.
Step 14: Drag and drop ‘Class’ from ‘Field Name’ into Filters. You will see changes in A1 and B1 fields inside the pivot table. Notice there is a drop down in field B1.
Suggested Read: Excel Charts
Step 15: Once you click the drop-down you will see ‘A’ and ‘B’, the class names in the original data set. Isn’t it cool? Select Class ‘A’ from drop down and the pivot table refreshes automatically to display only two student’s reports studying in Class A.
Let’s say we want to create a report which provides the summary of student mark in each subject separately as well (apart from sum). Simple – drag and drop ‘Subject’ field into ‘Columns’ box. You now have a perfect report to be presented.
For your information, you can choose multiple filters. Drag and Drop ‘Gender’ into filters and start playing. Good luck!!
How to use a pivot table in excel
Using a pivot table can be hard sometimes, because of the pivot table creation process is a little lengthy and confusing.
- If you want to compare the total profit earned by the product’s sale then take a datasheet containing the data of product’s sale monthly wise as product 1, product 2, and product 3. Now you need to find from which product you have earned more profit. As you have monthly sales then the data is stored in thousands of rows. Hence, manual sorting can create a problem. For this, you need to use a pivot table in excel that let you calculate the total sum in a few minutes.
Suggested Read: Excel Compare Two Columns
- Pivot table also helps in calculating various things automatically that a simple excel table cannot calculate. Things like counting the rows that have some common feature.
- With a pivot table, you can set the empty data with any default value. In simple excel tables, it takes lots of time to fill the empty cell manually.
Apart from this, the pivot table can be used for various other ways that help in saving time and effort.
You might have noticed that the dataset which you have taken above is not sorted. To make it sorted, you can use Excel Sort function. Now you can learn how to create pivot table in excel here and start practicing the pivot table in excel 2007 with examples from today. It is a powerful tool that helps in calculating, summarizing, and analyzing the data and shows you the patterns and trends of your data. Do let us know if you have a specific use case for pivot tables and we will be happy to write an article on the same. You can also mention your query below and our team member will get back with the answer.