Microsoft Excel is one of the programs of the Microsoft Office suite. It is an excellent spreadsheet that allows people to organize, compute, and record data. Professionals and students use Excel to summarize and calculate various forms of data. You can also merge the sheets and tabs in Excel. Features like tables, charts, and graphs help in organizing information in an efficient manner. Tables, especially pivot tables, are very helpful in displaying data. It helps in interpreting the data easily and performing various mathematical operations. Many people do not know how to create a pivot table. Therefore, let us understand how to create a pivot table in Excel.
Table of Contents
How to create a pivot table in Excel
First, let us understand the meaning and use of a pivot table. Excel consists of spreadsheets wherein there are rows and columns to enter data into. One can enter an extensive amount of data in a single spreadsheet. After entering the data, one can perform various calculations to interpret the data. A pivot table is useful in displaying a summary of the interpretations. A pivot table can include the average, median, deviation, and other statistical values of the original data. Thus, a pivot table helps in providing an overview of the original, detailed data.
Pivot tables are easy to understand. Therefore, the pivot table is a handy feature for professionals who use Excel. However, many people do not know how to create a pivot table in Excel. In schools, teachers often teach only the basic functions of Excel. Thus, people who work with a lot of data have to learn the features of Excel on their own. Creating a pivot table is not very difficult. Anyone with a basic knowledge of Excel can learn to do so. Read on to see a step-by-step guide on how to create a pivot table in Excel.
Enter your data in the Excel spreadsheet
The first step you need to perform is to enter all your data in a new Excel spreadsheet. Without entering your data, you cannot create a pivot table. Generally, people use a pivot table for a large set of data. Scrolling through a large set of data is tedious and time-consuming. Pivot tables highlight the important features of the data and display its summary. Therefore, pivot tables are useful when there is extensive data. Fill up the rows and columns with the different categories of data. Make sure to name the headings and double-check your data.
There can be different categories of data in your spreadsheet. Some of the common categories include serial numbers, date, amount, product, country, sales, units, profit, etc. Once you enter the data and create the rows and columns, you can sort your data. Sorting the data helps in displaying it according to your relevance. For example, if you want to see the highest profits first, you can customize your data by sorting it. On the home menu, you can click on the Sort button in the Data tab and sort your data accordingly. This concludes the first step on how to create a pivot table in Excel.
Inserting the pivot table
Once you enter your data in the spreadsheet, you can proceed to the next step. Now, you need to insert the pivot table. There are two ways of doing so. Let us consider both methods.
- First, highlight the cells that you want to include in the pivot table. Next, go to Insert and click on Pivot Table in the Tables tab.
- Alternatively, you can directly click on the Pivot Table option in the Insert menu and manually enter the cells that you want to include in the pivot table.
A dialog box will appear once you click on the Pivot table button. In the dialog box, you will see the range of data. You will also see the option of where to insert the pivot table. By default, Excel places the pivot table in a New Worksheet. You can change the option to Existing Worksheet if you wish to do so. Click on OK after finalizing your options. A pivot table will appear on your screen. Thus, this is the second step on how to create a pivot table in Excel.
Drag fields to adjust the layout
A blank pivot table is now visible on your screen. In the next step of how to create a pivot table in Excel, we will adjust the table’s layout. A Pivot Table Fields pane will appear along with the blank pivot table. In this box, there is a list of all the fields of the original spreadsheet. You can sort the fields into any of the four categories: Filter, Rows, Columns, and Values. To sort, you need to simply drag a field name and drop it into its respective category.
In the Filter category, drag the field which you want to use as a filter on the pivot table. Next, drag the fields which you want to display in the Rows and Columns category respectively. Finally, drag the numeric data to the Values category. Click on Update and your pivot table will be ready! In case you are not sure about how you want the layout to be, you have another option. On the Tables tab, you will see a Recommend Pivot Tables option. Click on that to see how you can display your pivot table in various distinct layouts.
Using Filter on the pivot table
Now that your pivot table is ready, you can format and organize it according to your preference. The pivot table will display the data according to the filter. If you adjust the filter, the corresponding data will show up on the table. Thus, the filter feature on the pivot table directs you to specific data.
When you assign different fields to the pivot table in the layout, you can pick one field as a filter. Now, on the pivot table, you can click on Filter and select the item of the field that you want to view. The table will now show you the details according to the new filter. Once you learn how to create a pivot table in Excel, you can customize your tables and add different filters.
Using Sort on the pivot table
Once you know how to create a pivot table in Excel, you can tweak it according to your preferences. You can change the format of the pivot table by using different options. One of the options you can use is the Sort feature. Using the Sort feature, you can arrange the data in an ascending or descending order. You can also customize your own sorting criteria. This helps in keeping the data more organized and systematic.
It is very easy to use the Sort feature in a pivot table. Click on any cell in the row or column that you want to sort. Next, right-click to see the drop-down menu appear on the screen. In the menu, click on Sort. Select how you want to sort your data. If you click on More Sort Options, you can have additional sorting options. Thus, you can sort the data in the pivot table according to your requirement. This will make the data easy to understand at first glance.
Changing the Summary of the Value Field
By default, the pivot table shows the sum of the value field in the summary at the bottom. For example, your value field shows the sales made by your company for each month. In this case, the summary at the bottom will show the total sales made by your company in the year. However, you do not want the sum of sales as the summary of the pivot table. Instead, you want the summary to display the average sales. You can make this change easily by following some simple steps.
Following the given example, let us see how to change the summary of the pivot table. First of all, click on any cell in the Sales column. Now, right-click to see the drop-down menu with its various options. In the drop-down menu, click on Value Field Settings. A dialog box will appear in which you will see various calculation options. In this example, we want the average sales. Therefore, we will select Average from the list of different operations. Click on OK and the summary will change to show the average sales instead of total sales.
There are different calculation options available in the Value Field Settings. Some of the most common calculations are Count, Sum, Min, Max, Average, and Product. You can choose the relevant calculation as the summary of your pivot table. Once you understand how to create a pivot table in Excel, you can experiment with different summary settings.
Two-dimensional pivot table
We are now aware of how to create a pivot table in Excel. However, we can go one step ahead and also create a two-dimensional pivot table! A two-dimensional pivot table will provide a comprehensive view of all data. It also makes comparisons and analyses of data easier. Creating a two-dimensional pivot table is not a difficult task. In fact, it is mostly similar to how we create a simple pivot table. Let us understand how to create a two-dimensional pivot table.
As usual, the first step is to enter data into the spreadsheet. Next, go to Insert and click on Pivot Table in the Tables pane. The Pivot Table dialog box will appear. Click on OK to proceed to the next step. A blank pivot table will appear on your spreadsheet. Now, you can pick out the layout of the pivot table. In a two-dimensional pivot table, both rows and columns will have particular categories. Therefore, you will drag one field into the Rows section and another field into the Columns section. Similarly, drag respective fields to Filters and Values.
Once you are done, the two-dimensional pivot table will appear on the spreadsheet. If your data has several distinct categories, a two-dimensional pivot table is useful. In a two-dimensional pivot table, you can easily display two or more related categories. This helps in providing a clear picture of the data. Professionals use two-dimensional pivot tables to display complex data.
Pivot charts and additional features
Learning how to create a pivot table in Excel is just the first step! You can also create a pivot chart of your data. A chart is an attractive way of displaying data. It is easy to look at and easy to interpret. Your pivot table can have its own chart to display the data in an attractive manner. Therefore, a pivot chart displays the data of the pivot table in a visually appealing manner.
After creating a pivot table, you can follow some simple steps to create a pivot chart. For that, click on any cell of the pivot table. Next, click on the Pivot Chart option in the Charts pane. An Insert Chart dialog box will appear on your screen. Click on OK. The pivot chart is now visible on the screen. You have the option to filter and sort the chart as well. If you change the data on the pivot table, it will automatically change in the pivot chart and vice versa. Thus, the pivot chart is nothing but a graphic representation of the pivot table.
You also have the option to format the pivot charts. The pivot chart can be a bar graph, a pie chart, or any other type of chart. You can format the size, shape, and colors of the pivot chart. Therefore, we can say that a pivot chart is an extremely useful feature of the pivot table.
Importance of pivot table
A pivot table is an extremely valuable feature of Microsoft Excel. It is useful for organizing large sets of data. Professionals use pivot tables and pivot charts in their presentations very regularly. These tools make interpretation and analysis of data easier and less time-consuming. Therefore, it is important to have a basic understanding of how to create a pivot table in Excel.
