OmniDocGroup Pivot Table Data by Month or Columns
Learn how to pivot table by month by converting dates and grouping them, then how to add a second field across columns, with checks that confirm your totals.
Monthly totals are one of a common reasons people build a pivot table, and they are also where many pivots go wrong. You drag a date field into the rows, and instead of twelve months you get hundreds of individual days. If you are trying to figure out how to pivot table by month, the fix is usually a grouping setting, but that setting only works when your dates are stored correctly. This guide covers both halves of the job: grouping dates into months, and adding a second field so you can compare two breakdowns at once.
The pivot itself is built in your spreadsheet program, not in OmniDoc. OmniDoc's role comes in when the data you need starts somewhere else, such as a table inside a PDF report. The PDF editor can export content to Word, Excel or PowerPoint formats, and it can run OCR on scanned PDFs first. From there, the grouping and checking steps work the same way in any spreadsheet program.
How do I pivot a table by month? The short answer
Make sure the date column contains true dates, then group that date field by months in the pivot, and add years if your data spans more than one year.
| Your situation | What to do | Why |
|---|---|---|
| Dates are stored as text | Convert them to real dates before you pivot | Grouping only works on true date values |
| You want monthly totals | Group the date field by Months | Collapses daily rows into twelve month buckets |
| Data covers several years | Group by Years and Months together | Keeps January 2023 separate from January 2024 |
| You need two breakdowns | Put the second category in Columns | Creates a cross-tab, such as region by month |
| The table is inside a PDF | Export it to an .xlsx file, then pivot | A pivot needs cell data, not a page layout |
Before you start
Check these items before you build the pivot. Each one prevents a problem that is hard to spot once the table is built.
- Confirm the dates are real dates. Text dates often look correct but cannot be grouped. In most spreadsheet programs, real dates align to the right of their cell by default, and text aligns to the left. If the column looks mixed, convert it first.
- Make sure every row has a header. Each column needs a single label in the top row. Blank headers or merged cells above the data cause the pivot to read the wrong fields.
- Remove blank rows and subtotal lines. A subtotal row inside the data gets counted as a regular record and inflates your totals.
- Use one continuous range. Keep all rows of the dataset in one block so the pivot picks up the full range. Rows added below the range later will not appear until you update the source.
- Compare the source to the original. If the numbers came from a PDF, keep the original open so you can check values against it after export.
Grouping dates by month and adding a second column, step by step
- Set up the source range. Select the data including its header row, then insert a pivot table from that selection in your spreadsheet program. This keeps every field available to the pivot.
- Drag the date field into the Rows area. At this point each distinct date appears as its own row, which is the starting point for grouping.
- Group the date labels by month. Right-click any date label in the pivot and look for a grouping option. Choose Months. If your data covers more than one year, select Years as well, and the pivot will show year and month together.
- Drag the second category field into Columns. For example, drag Region into Columns. Each region becomes its own column, and each month stays as a row.
- Add the value field to Values. Drag the amount or quantity field into the Values area. Confirm the calculation is Sum, since the default for some fields is Count.
- Adjust the layout if needed. If you want the category field in rows and the month in columns instead, swap them. Choose whichever arrangement makes the comparison easier to read.
- Save a copy before you share it. Grouping is a view on top of your data, so keep the original range intact and save the pivot in a separate file or sheet.
Check the result
A pivot can look right and still be wrong. Run these checks before you use the numbers.
| Check | How to see it | If it's wrong |
|---|---|---|
| Monthly totals add up | Add the month totals and compare them with the grand total | Some dates are text or blank, so they fall outside the groups. Fix the source column and refresh |
| Months are in calendar order | Read the row labels from top to bottom | Grouping may not have applied, so reapply it to the date field |
| Years stay separate | Look for a separate block for each year | Add Years to the grouping, or the same month from two years merges |
| No unexpected "(blank)" row | Scan the row labels for a blank entry | A date cell is empty or unreadable. Find it in the source and correct it |
| Values match the source | Spot-check three or four rows against the original | A data type or copy error occurred. Re-export or re-enter those rows |
If your data came from a scanned PDF, OCR can misread digits, especially in dense tables. Check any figure that matters against the scanned page before you rely on the total. Machine-read text should always be reviewed for important content.
Getting table data out of a PDF
When the table lives in a PDF, the pivot is only as good as the export. Open the PDF in the OmniDoc desktop client and use the PDF editor to export it to Excel format. If the file is scanned, run OCR first so the cells contain text you can use. Open the exported file, convert any date columns to real dates, and then follow the steps above. Pages that include merged header cells or multi-line rows may need manual cleanup before the pivot reads them correctly.
❓ FAQ
1. Why won't my dates group by month?
A common cause is that the dates are stored as text, which looks like a date but cannot be grouped. Convert the column to real dates, then refresh the pivot and apply the grouping again.
2. Can I group by month and year at the same time?
Yes. When you group the date field, select both Years and Months. The pivot then shows each year as a block with its months listed underneath, so the same month from different years stays separate.
3. Does grouping change my original data?
No. Grouping only changes how the pivot displays the rows. Your source range stays the same, so you can regroup, change the grouping, or remove it without editing the data.
4. Can I pivot a table I copied from a PDF?
You can, but copying from a PDF often pastes values into the wrong columns or merges cells. Exporting the table to a spreadsheet file with the PDF editor usually gives a cleaner starting point. Check the converted cells against the original before building the pivot.
📝 Summary
Group dates by month in a pivot table only after the date column holds real date values, and add years when the data spans multiple years. For a two-way breakdown, place a second category in Columns and check that the monthly totals match the grand total. If the source is a PDF, export it to a spreadsheet file first so the pivot has clean cells to work with.
