Skip to content

Excel guide · PivotTable

How to create a pivot table in Excel

A pivot table (Excel calls it a PivotTable) totals a long sales or expense list by region, month or product in a few clicks. Below is how to create one in Excel step by step; then we show how to build the same table in Nexell with a single sentence.

Free account · no card · 30 credits a month · Not ready to sign up? Paste some text and try it

Step by step

How to create a pivot table in Excel

  1. 1

    Prepare the list

    Give every column a one-row header and leave no blank rows or merged cells in the list. Convert the list with Insert > Table: rows you add later join the pivot table when you refresh it.

  2. 2

    Insert the PivotTable

    Select a cell in the list and click PivotTable on the Insert tab. Check the range Excel picked, choose New Worksheet and click OK.

  3. 3

    Drag the fields

    In the PivotTable Fields pane drop Region into Rows, Category into Columns and Amount into Values. A number field is summed automatically.

  4. 4

    Group and refresh

    To total dates by month, right-click a date in the pivot table, choose Group and select Months. A pivot table does not update when the source list changes: right-click it and choose Refresh.

Example

The same pivot table in Nexell: two sentences

We pasted a quarter of sales into the tool and typed two requests.

1Pasted list

2In Nexell

  1. Text turned into a table: 5 columns, 12 rows. Dates came in as real dates and amounts as numbers.
  2. format as a tableData will be formatted as an Excel table, filterable and banded
  3. pivot table: region in rows, category in columns, sum of amountPivot table: Region in rows, Category in columns · Sum of Amount, added as a native PivotTable on its own sheet

The pivot table does not change the list; it is added to the file as a separate sheet.

3In Excel

D8fx85820
The pivot table sheet of the downloaded Excel file (PivotTable)
ABCD
1Sum of Amount by Region × Category
2
3Sum of AmountCategory
4RegionFurnitureStationeryGrand Total
5Central23,950.004,130.0028,080.00
6East20,700.006,440.0027,140.00
7West20,600.0010,000.0030,600.00
8Grand Total65,250.0020,570.0085,820.00
DataSum of AmountChart

This is Excel's own PivotTable: you can keep changing its fields in the PivotTable Fields pane. Its source is the table on the Data sheet; add rows to the table, right-click the pivot table and choose Refresh, and the new rows are totaled too. Cost: 3 credits (the conversion plus two requests); downloading was free.

Illustrative example with made-up figures; the layout shown is what Nexell writes into the downloaded file.

Sales dashboard in Excel

Sales dashboard in Excel

KPI cards, charts and a region slicer. Every number is a real Excel formula.

Why Nexell

What Nexell does differently

A real PivotTable

The pivot table goes into the file as Excel's own refreshable PivotTable, not as fixed numbers, so you keep changing its fields in Excel.

Pick fields by sentence

Write “pivot table: region in rows, category in columns”. The preview says which field goes to rows and which to values.

Starts from a messy list

A PDF, CSV or email list is turned into a clean table first, so amounts become numbers you can total.

Limits

What to know before you start

  • Dates are not grouped by month in a short list. With more than 31 different days in the date column, dates are grouped by year and month; in a shorter list each date becomes its own column. In that case right-click a date in Excel and choose Group > Months.
  • If you do not say “pivot table”, you get a cross-tab. “Region in rows, month in columns” turns the list into a cross-tab with totals; it holds fixed values and does not refresh. For a refreshable PivotTable, say “pivot table” or “pivot” in the sentence.
  • Privacy. Personal data in text is masked before it is sent to the AI service (what is sent).

FAQ

Excel pivot tables: common questions

What is a pivot table?

It is an Excel tool that groups the records in a list by the fields you choose and sums, counts or averages them. The source list does not change; the pivot table sits in a separate area and is recalculated from the source when you refresh it.

How do I update a pivot table in Excel?

Right-click the pivot table and choose Refresh; to refresh every pivot table in the workbook use Refresh All on the Data tab. If the source is an Excel table, new rows are included; if it is a fixed range, widen it with Change Data Source on the PivotTable Analyze tab.

How do I filter a pivot table?

Drag a field into the Filters box of the PivotTable Fields pane, or click the drop-down arrow beside the row or column headings. For button-style filtering, choose Insert Slicer on the PivotTable Analyze tab.

Can I refresh the pivot table Nexell builds in Excel?

Yes. When your sentence says “pivot table” or “pivot”, the file gets Excel's own PivotTable on a separate sheet, with the Data sheet as its source. Change the values, right-click and choose Refresh, and the pivot table is recalculated.

Build the pivot table with one sentence.

Create a free account, bring your data and type what you need.

Try it free