OmniDocWhat is a pivot table? The functions and functions are clearly explained in one article
What is a PivotTable, what can it do, and what is the difference between it and a PivotChart or an ordinary formula summary. After reading, you will be able to judge whether a piece of data is suitable for summary using a pivot table, and why the results do not match the source data.
Key points of this article
- Pivot table groups a detailed table by certain fields and sums, counts or averages another field. The original data will not be changed.
- Pivot tables are composed of four areas: rows, columns, values, and filters. You can view the same data from different angles by dragging the fields.
- A pivot chart is a graphical display of the results of a pivot table. It relies on the pivot table and is not an independent data source.
- The source data must have a clear header, only one type of information in each column, and no merged cells, otherwise the summary results will be prone to errors.
- After the source data is updated, the pivot table needs to be refreshed before it changes. This is where it is easily confused with ordinary formulas.
Many people open a sales record with thousands of lines and want to know how much they sold in each region and each month, but they can only read it line by line, or manually type in formulas and add them up one by one. Pivot tables are tools designed for this type of problem. It can quickly classify and summarize detailed data, allowing you to view the same data from different angles.
This article explains what a PivotTable is, its functions and functions, what is the relationship between PivotCharts and it, and under what circumstances is it appropriate to use it. The specific set-up steps are not covered here, and understanding the concept itself does not require any specific software.
What is a pivot table? One sentence conclusion
Pivot table is an interactive summary tool: it groups detailed data according to the fields you select, and then performs calculations such as sum, count, and average on the specified fields to generate a new summary table. The source data remains unchanged, and if the field combination is changed, the summary perspective will change accordingly.
If your situation is as follows, you can refer to this table:
| your situation | Recommended practices | reason |
|---|---|---|
| The data has many rows and needs to be summarized by category | Use PivotTable | It automatically groups and calculates by field, no need to manually calculate row by row |
| Want to see the two dimensions of "region" and "month" at the same time | Put the two fields into rows and columns respectively | Pivot tables generate cross-summarized matrices |
| You only need to calculate a total or a total under a condition | Use the summation formula directly | Simple structure, no need to establish summary area |
| Data is scattered PDF tables or scans | First organize the table into a neat detailed list | Pivot tables require that the source data be a structured table |
| Need to show others trends or proportions | Create a pivot chart based on a pivot table | The chart will follow the results of the pivot table |
# which parts does it consist of
The core of the PivotTable report are "fields" and "areas". Each column of the original table is a field, such as "date", "region", "product" and "sales". The PivotTable puts fields into four areas, each for a different purpose:
- Row area : each value in the field is divided into a row, which is used to group groups, such as putting "region" on the row. - column area : each value in the field becomes a column and is used for cross-grouping, such as putting "month" in the column. - value area : the number field to be calculated, and select the sum, count, average and other calculation methods, such as summation of "sales". - filter area : it is used to filter data as a whole, such as only looking at the records of a certain year or a certain salesperson.
By understanding these four areas, you understand most of the functions of the PivotTable. For the same data, as long as you adjust which area the field is placed, you can get a completely different summary result.
how does it work
The PivotTable does not modify the original data, it generates a summary view on top of the original data. Its operation logic is roughly as follows: first, find out all the unrepeated values of the row or column fields, then calculate the corresponding numerical fields of each group in a specified way, and finally arrange them into a table.
This means that the original table should be as clean as possible. Each row is a record, each column is a type of information, the header is clear, do not insert subtotal rows or blank rows in the middle of the data. If there are both numbers and text in the same column, or if there are merged cells, items may be left out or errors may be made when grouping.
places easy to confuse
- PivotTable and PivotChart are not the same thing. * PivotTable is a summary result in the form of a table, and a PivotChart is a chart generated from a PivotTable. The chart relies on the PivotTable. If you delete the PivotTable, the chart loses its data source.
- PivotTable is not a formula, and the result is not automatically updated with the source data. * after modifying the numbers in the original table, the PivotTable usually needs to be manually refreshed before it changes. If you forget to refresh, you will see that the numbers do not match.
- * the summary of the PivotTable is not equal to de-duplication. To sum up "sales" is to add up all the sales in the same group. If you want to count how many different customers there are, you need to use the statistical quantity calculation method, and first confirm that the customer name in the source data is the same, otherwise the same customer may be counted as several.
# Core comparison
There are several common ways to summarize data, which are suitable for different scenarios:
| Mode | Suitable for the scene | Output form | Pay attention |
|---|---|---|---|
| PivotTable report | Multi-dimensional grouping and summary, often look at it from a different point of view. | Interactive summary table | The source data needs to be refreshed after the change. |
| Data perspective | Show trends, proportions, comparisons to others | Chart | Depending on the PivotTable, modifying the field will synchronously affect the chart |
| Handwritten summary formula | A small amount of calculation under fixed conditions | Single or several numerical values | The formula becomes longer for a long time and is difficult to maintain. |
| General screening | Only look at a certain part of the record | Filtered rows of the original table | If the summary is not generated, the total cannot be obtained directly. |
If you just want to know the total sales for a given month, ordinary screening plus summation is enough; if you want to compare by region, month, and product at the same time, the PivotTable is easier.
# how to use it
After understanding the concept, you can prepare the data and use the PivotTable as follows:
- Check that the source data is a regular schedule: one field per column, the header is clear, and the cells and middle subtotal rows are not merged.
two. If the data is in a PDF table, you can export it to Excel using the PDF editor of the OmniDoc desktop client, and then check whether the exported table is complete and whether the number is recognized as a number.
- Select the parts list in Excel, insert the PivotTable, put the category field in the row or column, put the number field in the value area, and choose the appropriate calculation method.
- When you need to show, generate a PivotChart based on the PivotTable, and use a chart to show the trend or percentage.
- After the source data is updated, refresh the PivotTable and check whether several key numbers are consistent with the original table.
If your original data is a PDF report or a scanned copy of a paper form, you can identify it with OCR and then sort it out. The identification results need to be checked item by item, especially the amount and date.
# which method is suitable for you?
If you often need to switch angles from the same data over and over again, such as by month, by region, by person, the PivotTable is worth taking the time to learn. If you have a small amount of data and only need one or two totals, handwritten formulas or general screening are more straightforward. If what you want to do is a presentation to management or customers, it is recommended that you use a PivotTable to calculate the data first, and then present it in a perspective, each doing its own job.
# frequently asked questions (FAQ)
1. What's the difference between a PivotTable report and a classified summary?
Classified summaries usually insert subtotal rows directly into the original table, change the structure of the original table, and redo it in a different grouping way. The PivotTable report generates an independent summary view, and the new summary can be obtained by changing the fields without changing the original table.
2. Will the PivotTable change the original data?
No, I won't. The PivotTable simply reads the source data and generates summary results, leaving the numbers and text in the source table intact. You can safely delete and adjust the PivotTable without affecting the original data.
3. Why does the PivotTable result not match the source data?
There are three common reasons: the source data is not refreshed after it is updated; some numbers are saved as text and do not participate in the calculation; and the data range does not contain new rows. Check these three points one by one, and you can usually find the problem.
4. Can you read the results of a PivotTable without learning Excel?
Sure. The result of the PivotTable is essentially a table with row and column headings. If you understand the corresponding relationship between the title and the number, you can read the general meaning. What you really need to pay attention to is where the field is placed and whether it is summed or counted.
# Summary
PivotTable is a summary tool that groups by field and flexibly changes the perspective. It does not change the original data and can generate a PivotChart based on the results. By understanding its four areas, the structural requirements of the source data, and the characteristics that the PivotTable needs to be refreshed, you can determine when to use it. After the concept is clear, it will be easier to learn the specific establishment steps.
