Skip to content

How to summarize spreadsheet data with a pivot table

Select a range with headings in DocFila's spreadsheet editor, choose Pivot table, drag fields into Rows, Columns and Values and add a pivot chart.

Open DocFila free

The short answer

Select the data with headings in the first row, choose Pivot table, set where it goes, then add fields to Rows, Columns and Values. DocFila totals them as Sum, Count, Average, Largest or Smallest, and you can group dates by month, quarter or year.

Works on: The workbook grid in the desktop apps and the web Workspace, for .xlsx files. A pivot table made in another app keeps its last calculated cells and its pivot data unchanged.

What do you need?

What are the steps?

  1. Select the source. Select the range including its heading row. If you see Select a range with headings in its first row, then choose Pivot table, extend the selection upwards.
  2. Start the pivot. Choose Pivot table, labelled Summarize the selection in a pivot table. The New pivot table dialog opens.
  3. Choose where it goes. Under Place it on, choose A new sheet or An existing sheet and give a cell address for the second.
  4. Add fields. Drag fields from the list into Rows, Columns, Values and Filter by. An empty zone says Add a field from the list.
  5. Choose the calculation. For a value, pick Sum, Count of entries, Distinct count, Average value, Smallest value or Largest value. Captions read like Sum of Amount.
  6. Group dates. For a date field choose By month, By quarter or By year instead of Each date, so a long list becomes a short summary.
  7. Create it. Choose Create pivot table. Grand total rows and columns appear, and Refresh when the source changes keeps it current.
  8. Chart it. Choose Pivot chart to draw the summary; the chart notes that it Follows the pivot table of the range.

What is worth knowing?

What DocFila does not do here

Pivot tables are for summarizing; they are not a data model, and there are no calculated fields shared across several sources. Pivot tables created in other apps are kept as written and are not rebuilt, so edit those in the app that made them.

Where to go next

More word, excel and powerpoint how-to guides

Questions

Does a pivot table change my source data?

No. It reads the range and writes a separate summary on a new or existing sheet. Deleting the pivot table removes the summary only, and your original rows are untouched.

Can I summarize by month?

Yes. For a date field, pick By month, By quarter or By year instead of Each date. The pivot then groups rows into those periods and totals the value field for each.

Will the pivot update when my data changes?

With Refresh when the source changes switched on it follows the data. Otherwise the pivot reports that the source has changed since the last refresh, and you refresh it yourself.

Try it in DocFila

Free to start, no card needed.