Excel에서 피벗 테이블 만들기 (단계별)

Tested in: The sample sales table, the SUMIFS / SUMPRODUCT checks, a Region-by-Product pivot, and the GETPIVOTDATA formulas below were entered and evaluated in Google Sheets on 12 November 2026. Excel 365 menu names are the current desktop labels; I did not re-run the clicks in Excel this session.

When a pivot table is worth it

A pivot table is for slicing a flat list — one row per sale, ticket, or invoice — into totals you can rearrange without writing a new SUMIFS for every question. The eight-row example below is small enough that you could add the totals by hand. The point is to have a source you can check, then use the same field layout on a sheet with a few thousand rows.

If you only need a single filtered view and will not keep changing the question, FILTER vs QUERY or a set of COUNTIFS / SUMPRODUCT formulas may be simpler. Use a pivot when you expect to drag fields around, add a slicer, or hand the workbook to someone who should not have to edit formulas.

A drop-down on the source data, covered in How to Add a Drop-Down List in Excel, helps here: Region and Product stay spelled one way, and the pivot does not split “West” and “west” into two rows.

Example data

Eight sales in A1:F9. Dates are real date values, not text that looks like a date. Revenue is the amount we will sum.

Before building the pivot I checked the same numbers with ordinary formulas in Google Sheets on 12 November 2026:

=SUMIFS(F2:F9,B2:B9,"East")
=SUMIFS(F2:F9,B2:B9,"West")
=SUM(F2:F9)
=SUMPRODUCT((B2:B9="East")*(D2:D9="Widget")*(F2:F9))
=SUMIFS(F2:F9,B2:B9,"West",D2:D9,"Widget")
=SUMIFS(F2:F9,C2:C9,"Maya Chen")

Those returned 9450 (East), 15000 (West), 24450 (grand total), 7200 (East Widget), 12600 (West Widget), and 12600 (Maya Chen). After the pivot existed, GETPIVOTDATA matched every one of those figures.

Step 1: Get the source range clean

Use a single header row, no blank rows in the middle, and one kind of value in each column. Revenue must be numeric. If a total at the bottom is already in the list, leave it out of the pivot range or you will double-count.

Turning the list into an Excel Table (Insert > Table, or Ctrl+T) is the most reliable way to have new sales included later. Leave a name such as Sales on the Table, and build the pivot from that name rather than $A$1:$F$9. In Sheets there is no exact equivalent; you widen the source range or start from a named range that you keep updated.

If dates later group incorrectly, they are often text. That problem is covered in dates stored as text. On this sample, =ISDATE(A2) returned TRUE.

Step 2: Insert the pivot table

In Excel 365

1. Click any cell in the list (A2 is fine).
2. Open Insert > PivotTable. If Excel offers From Table/Range, choose that.
3. Confirm the Table name or the range $A$1:$F$9.
4. Choose New Worksheet. Putting the pivot next to the data works, but a new sheet leaves room for slicers and for rearranging fields.
5. Click OK.

In Google Sheets

1. Select A1:F9, or click a cell in the range.
2. Open Insert > Pivot table.
3. Choose New sheet, or an existing sheet if you already set space aside.
4. Click Create. The field picker appears on the right.

Step 3: Arrange the fields

For a Region-by-Product revenue matrix:

• Rows: Region
• Columns: Product
• Values: Revenue, summarized by Sum

Leave Date and Rep out of this first view. You can drag Rep into Rows under Region later, or into Filters, without rebuilding the table.

On the 12 November 2026 Sheets pivot, that layout produced this matrix (the Grand Total label follows the spreadsheet language; the numbers are what matter):

East, West, Widget, Bolt, and the grand total all match the SUMIFS checks above. If your Values column says Count and the totals look like 3 and 5 instead of 9450 and 15000, open the value field and switch it to Sum. A single blank or a comment typed into the Revenue column is usually what triggered Count.

Refresh, source range, and layout

New rows below the original range do not appear until the pivot knows about them. In Excel, right-click the pivot and choose Refresh, or use PivotTable Analyze > Refresh. If you did not use a Table, also use Change Data Source and widen the range. In Sheets, click the pivot and edit the source range in the side panel, then confirm the values update.

A few layout changes are worth making once the numbers look right. In Excel, Design > Report Layout > Show in Tabular Form puts each row field in its own column, which is easier to copy out. Repeat All Item Labels fills the blanks under a grouped region. Turn off subtotals you do not need, and keep Grand Totals on until you have checked the figures.

Slicers (PivotTable Analyze > Insert Slicer) are a cleaner filter than hiding items in the drop-down, especially if someone else will use the file. In Sheets, use the Filters well in the pivot editor, or a slicer if your account offers one.

Excel can group a date field by month or quarter from the right-click Group menu. Sheets will not always do that grouping for you. A helper column with =DATE(YEAR(A2),MONTH(A2),1) on the source, then that column in Rows, is the straightforward equivalent. The monthly SUMIFS checks on this sample were January 7800, February 5400, March 8850, and April 2400.

Pull a number out with GETPIVOTDATA

If you write =K3 to grab East Widget, the formula breaks the moment you move Product off the columns. GETPIVOTDATA asks for the field names instead of a cell.

In Google Sheets, with the pivot starting at I1, these all evaluated as shown on 12 November 2026:

=GETPIVOTDATA("Revenue",I1)
=GETPIVOTDATA("Revenue",I1,"Region","East")
=GETPIVOTDATA("Revenue",I1,"Region","East","Product","Widget")
=GETPIVOTDATA("Revenue",I1,"Region","West","Product","Bolt")
=GETPIVOTDATA("Revenue",I1,"Product","Widget")

Results: 24450, 9450, 7200, 2400, and 19800. The first argument can also be "Sum of Revenue" in this workbook; both names returned the same grand total. The second argument only identifies the pivot. Pointing it at the East label (I3) and asking only for the data field still returned the grand total 24450, not East’s 9450. To get East, you have to name the Region item.

Excel inserts GETPIVOTDATA automatically when you type = and click a pivot value. The inserted formula usually uses the caption "Sum of Revenue" and an absolute reference to a cell in the table. If you would rather have ordinary cell references, turn off Generate GetPivotData on the PivotTable Analyze tab. Keep it on for any number that should survive a layout change.

Maya Chen’s share of revenue, from the same source, was =12600/24450, which evaluated to 0.5153374233128835. That kind of ratio belongs in a regular cell that reads the pivot through GETPIVOTDATA, not in a hand-typed copy of the total.

Things that usually go wrong

The source includes a pre-existing Grand Total row, so every pivot total is twice what SUMIFS says. Or the range stopped at row 9 after you added row 10, and Refresh appears to do nothing because the new sale is still outside the source.

Values show Count because Revenue has a blank or the word “n/a” in one cell. Filters still applied from an earlier experiment hide a region, and the grand total no longer matches =SUM(F2:F9). “East ” with a trailing space becomes its own row; trim the source rather than renaming the extra item in the pivot.

Do not format the pivot as if it were the only copy of the truth and then type over a value. Type in the source, then refresh. If you need to highlight large numbers, do it with conditional formatting on a copy of the values, or on the source, rather than painting cells that a refresh will rebuild.

Once the matrix looks right, the same Region and monthly totals are a good column or line chart source. Chart the summary, not every detail row plus the grand total at once.