Pivot Table · Spreadsheet Data Analysis
Pivot Table
What is a pivot table?
- A cross-tab summary you build by dragging
- The source must have one record per row
- Change the question to see your data from a new angle
The first thing that changes once you start using pivot tables is how you build reports. Normally, every time a request comes in, like “Break it out by department” or “Now by quarter,” you build yet another table. A pivot table leaves the source data untouched and just changes the angle you view it from, so building one new table per request gives way to attaching multiple views to a single source. As a result, the time your team spends shifts from building tables to interpreting the numbers.
Besides sums, the values area can show count, average, max, min, percentage, and more, and date fields can be grouped by month, quarter, or year with a single right-click. However, a distinct count that excludes duplicates, such as “how many people attended,” isn’t available in a basic pivot table; you have to add the data to the Data Model when creating it to get that option. SUMIFS can do similar work, but the use cases differ. Formulas suit regular reports with a fixed layout, while pivot tables suit exploratory work where you keep changing the criteria. If you have to merge multiple files every month or your data runs past several hundred thousand rows, it’s time to move to Power Query or a BI tool.
In practice, pivot numbers are almost always wrong because of the source data, not the feature. If amounts received from outside are stored as text, those rows silently drop out of the total, and if you add rows to the source, new data will not appear unless the range was set as a table (Ctrl+T). Add a missed refresh, or a screenshot of a filtered view, and the total in the meeting materials can end up off from the finance close figure by tens of millions of KRW. What makes it worse is that a pivot shows a plausible-looking table without any error message, so before reporting you need a step that checks the grand total against the source total.
Jaryong KooMarketing PhD and authority in AI, data analytics, and personal branding
John KimAuthor of B2B Marketing Design and CEO of Nanum ConsultingIn 1991, Lotus Improv introduced the idea of reorganizing data along multiple axes. Microsoft popularized it by adding the pivot table to Excel 5.0 in 1994. Since then, most spreadsheet programs have offered the same feature.
This is the story of Company A, an auto parts manufacturer with 620 employees. A three-person talent development team manages an annual training budget of KRW 430 (≈ US$1) million, and every quarter it took a full four days to build the department-by-course summary tables for the management meeting. Training records were scattered across 12 Excel files kept by individual teams. (Hypothetical example)
- Mr. Kim, a manager on the talent development team, merged the 12 files into one sheet, stripped out the merged cells and 47 team subtotal rows, and rebuilt the source data so that date, department, course name, attendee, and amount sit in one row per record.
- After setting the range as a table and putting department in rows, month in columns, and the sum of amount in values, the first cross-tab appeared in 3 minutes. The assistant manager at the next desk said, "You mean you spent four days each time making this?"
- After grouping dates by quarter and switching the values to average per person, one thing finally stood out: the per-person cost of the Production Division’s leadership course was KRW 180,000 (≈ US$135) higher than that of the office staff group.
- Ms. Lee, a senior manager on the finance team, pointed out that the total was off by KRW 28 million (≈ US$20,900). On checking, some amounts in the settlement sheet from the outsourcing partner had been saved as text and dropped out of the sum. Once they were converted to numbers, the total matched the closing figure.
- The quarterly roll-up shrank from four days to half a day. When an executive said in the middle of a meeting, "Let’s see it by job grade too," Kim, the manager, changed the fields and had it on screen in 3 minutes.