PivotTable is used to analyze, summarize, and report our records in the data list. Excel PivotTable is a good way to compare data. It is used extensively to create reporting screens. PivotTable’s have become a more powerful and indispensable Excel feature in reporting with their features developed with the new Excel version. PivotTables are created based on data on a data table. The data generated by a PivotTable is called source data. In pivot processes, the tool takes a data table as input, processes it, and creates a new table value with these values. It provides flexibility in showing details with its expansion and contraction capabilities.
Making a PivotTable
Let’s learn how to create a PivotTable step by step.
- First of all, you need to select data field. You must include column headings in the selection field. You should select all rows (all of the data). If the data starts from the top row, a selection can be made from the column headings.
- Click the PivotTable button on the Insert tab.
- Then, the source data table area and the place where the PivotTable will be created is determined. These options are the new or existing page. In the Table/Range box, the data field where we will create the PivotTable is shown. If the data field is selected from another location or file, this box is clicked, and a selection can be made from the file. This field can be in a named area. When the new worksheet is selected, a new sheet is opened next to the datasheet in the form of Sheet1, and the PivotTable is created there.
Half Way There
- Then, you’ll see the screen for designing the PivotTable. On this screen, you can find the list of source data as a field. As we explained with different figures before, the row, column, and data fields of the PivotTable we want are selected at this stage. In this process, the selected data fields are dragged to the row, column, value, and filter fields of the PivotTable to be created. The selection process to be made should be all that we need for the PivotTable. For example, if we need the sum of salesperson, brand, and amount fields, these fields are selected. Usually, numerical data such as quantity and amount are dragged into the Values field.
- You can select the desired fields in the filters section. These fields then filter the resulting table.
- As a result of the above processes, we simply created a PivotTable. According to the choices made, the table is located as a new sheet on the left side of the current datasheet. Subsequently, different view options, different calculations (such as sum, count, average) can be applied. You can also add graphs.
PivotTable in Excel
Let’s see what we can do with the PivotTable.
- You can create reports by making groupings such as monthly or yearly.
- Also, you may group your data in rows or columns.
- You can move rows to columns or columns to rows or summarize to view different summaries of the source data.
- Also, you may edit the scope of annotation by expanding or constricting processes.
- With PivotTable design options, you can add different perspectives to your tables.
- You can sort. You can arrange numerical, text, and date data as desired.
- It provides to see the detail. In other words, you can open the details of the grouped and collected data.
- You can format. There are several formatting possibilities that make the table look good.
- You can create graphics. It provides more information visually by creating a graph of the created PivotTable.
- By finding the Grand Total and Subtotal values, you can decide whether to show them or not.
The Things That You Should Be Careful
- Columns should have headings, and these headings should explain the data in the relevant column as clearly as possible.
- In the process of creating the source table, it is useful to consider how and for what purpose you can convert these data into summary tables. For this reason, creating as detailed fields (columns) as possible will increase the questions you can ask on the basis of data and thus allow you to get new ideas.
- Columns must always have the same data type. In particular, the type differences in the key columns used for grouping in the summarization process will cause the process to be performed incorrectly.
- It is important to follow a standard in data entries. For example, you should not use the email expression as e-mail in another line, and the definitions should remain the same as possible. This is also valid for the use of periods and commas in numerical values.
FAQ About How to Make a PivotTable in Excel
With PivotChart, you can add visual elements to your data.
First, select a cell in your table, find and analyze PivotTable tools, and select the PivotChart. Click OK after selecting a chart.
Click anywhere to view the PivotTable Tools. Then click the subtotals in the design tab and choose the option you want.
Find PivotTable Tools by selecting any cell. Then click Analyze and Select. After clicking the Entire PivotTable, press the Delete key.
With Slicers, you can find buttons that you can click to filter tables for PivotTables.
Conclusion on How to Make a PivotTable in Excel
In this article, we told you what a PivotTable is, how to make a PivotTable, and what you can do with a PivotTable. If you find this content too advanced for your current need then you should probably start with something easier. Try out how to use Excel, for example, to pin down the basics.