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?
- A table of data whose first row holds headings, such as date, category and amount.
What are the steps?
- 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.
- Start the pivot. Choose Pivot table, labelled Summarize the selection in a pivot table. The New pivot table dialog opens.
- Choose where it goes. Under Place it on, choose A new sheet or An existing sheet and give a cell address for the second.
- Add fields. Drag fields from the list into Rows, Columns, Values and Filter by. An empty zone says Add a field from the list.
- 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.
- 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.
- Create it. Choose Create pivot table. Grand total rows and columns appear, and Refresh when the source changes keeps it current.
- 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?
- If the source has changed, the pivot shows The source has changed since the last refresh. Refresh it before you copy numbers out.
- Use Filter by to limit a pivot to some items of a field, with All items or Some items, without touching the source data.
- Edit fields reopens the layout, and Delete pivot table removes only the summary, never your data.
- Blank values are listed as (no value), which helps you spot rows with missing entries.
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
Edit Excel online Sort and filter a spreadsheet Extract PDF tables to Excel DataGuru
More word, excel and powerpoint how-to guides
How to track changes in a Word document and accept or reject them How to add, reply to and resolve comments in a Word file How to add page numbers, a header and a footer to a Word document
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.
Related DocFila tools
Keep going with the document workflow that matches this page. Open the tool you need, then finish the file in DocFila.