Prepare Source Data for Pivot Tables In MS Excel

A pivot table is a tool for summarizing data that is derived from larger tables. A database, an Excel spreadsheet, or any other data that is or could be transformed into a table-like shape might be these larger tables. A pivot table's data summary may include sums, averages, or other statistics that the pivot table meaningfully groups together. Given that a pivot is defined in the dictionary as "a central point, pin, or shaft on which a mechanism spins or oscillates," the term "pivot table" actually provides a good indication of the significance of pivot tables and the function they play in analysis. When it comes to conducting data analysis, this idea is crucial. You can often find all the data you've been given on a subject in a database or dataset. To derive knowledge from this raw data is the entire purpose of any analysis. A table containing thousands of rows, however, cannot be fully understood by simply looking at it and scrolling up and down. Drawing insight frequently requires you to exclude certain data points and manipulate how they show their information, for instance through summary statistics. Data analysts use summary statistics to express a lot of information as simply as possible by condensing a group of observations.

Pivot Table in Excel

When we want to collect count, sum, and values either in tabular form or in the form of 2-column groupings, we may use an excel pivot table to categorize, sort, filter, and summarise any length of the data table. To insert a pivot table that will automatically locate a table or range, choose the Pivot table option from the Insert menu tab. By taking into account the necessary columns, we may also design a customizable table.

Structure of Pivot Table

Let's understand the structure of the pivot table with the help of an example. In a class where the student took a test and received either correct or incorrect marks. They, therefore, process data that has a score and indicate whether it is correct or not. Let's say a teacher at the school wishes to know the total number of correct and incorrect marks. The raw data picture is shown below.

We can, of course, manually count those values, but doing so would take a long time for a lot of data. But there is a simple way to accomplish this that is which below.

Step 1: Enter all the data in the excel sheet that is already done above as you can see in the above picture.

Step 2: Select all the data and then go to the INSERT option and then you will see here the option of PIVOT TABLE.

Step 3: After choosing the pivot table a box will open in which you have to enter the range in the location box. Enter the range that is mentioned below in the image.

Step 4: Now you will the pivot table with pivot the table field that is shown in the below image and then you have to select student, marks, and status in the pivot table field.

Step 5: After the selection, you will observe that your pivot table will be like in this form that is shown below in the image. This pivot table has both correct and incorrect data for each student.

Step 6: Click on the option that is shown below.

Step 7: After clicking on the option, now you will see a field where you have to first select status then you have to select only correct, and then click on ok.

Step 8: After clicking on OK you will see the pivot table which has only correct score data now.

Step 9: Now again click on the option that is shown below in the image.

Step 10: After clicking on the option, now you will see a field where you have to first Change the options from student to status.

The changed option can be shown below,

Step 11: Now choose only the incorrect option.

Step 12: Now you will observe here a pivot table that has only incorrect marks data.