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 excel pivot table online then you are at right place.
How to Create Excel Pivot Table?
For this exercise, please create/copy paste the data from below 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 sum 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.
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 ‘S 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: Close the box if it is not in use.
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 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.
Step 16: 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.
Step 17: FYI, you can choose multiple filters. Drag and Drop ‘Gender’ into filters and start playing. Good luck!!
This is how to create an excel pivot table. In the next post, you can see how to use excel Vlookup function.