Year to Date in Google Sheets Pivot Tables: How It Works

To show year-to-date (YTD) figures in a Google Sheets pivot table, add a helper column to your source data that marks dates from January 1 of the current year through today, include that column in the pivot table’s source range, then filter it to YTD. Grouping dates by year or month alone does not create a YTD period: it can include future dates or dates from earlier years unless you filter the data.

What year to date means in a pivot table

Year to date is the period beginning on the first day of the relevant year and ending on the date through which you want to report. For a calendar-year report, that normally means January 1 through today. If today is November 3, 2026, for example, current-year YTD includes dates from January 1 through November 3, 2026, inclusive.

A pivot table summarizes the records in its source range; it does not automatically know that a reader means “YTD” when they ask for a year or month grouping. A Year grouping summarizes an entire year’s available records, while a Month grouping organizes records by month. Neither necessarily limits the results to the current year or stops at today. A YTD marker makes that date boundary explicit and can update as the calendar advances.

Prepare the source data and date column

Start with a simple, rectangular data table: one header row, one record per row, and columns for the date and the values you want to summarize. For example, a sales sheet might have Date in column A, Region in column B, and Amount in column C. Avoid merged cells and blank rows inside the data. Make sure the dates are actual date values rather than text that only looks like a date; text dates can sort incorrectly and may not group as expected.

If the date column includes times, a helper formula that checks for dates earlier than tomorrow will include all records from today, including afternoon timestamps. If you intend to report only through a date earlier than today, replace the current-date boundary with the reporting date you have chosen. For financial or operational reporting, confirm that the date column uses the intended time zone and business definition of a day.

Create a dynamic YTD helper column

With dates in A2:A and a new helper column headed YTD in D1, enter this formula in D2, assuming each source record’s date is in A2:

=IF(AND(A2>=DATE(YEAR(TODAY()),1,1),A2<TODAY()+1),"YTD","")

Fill the formula down alongside the source records. It labels dates on or after January 1 of the current year and before tomorrow as YTD; earlier dates, future dates, and blank dates receive an empty result. The use of a less-than-tomorrow boundary includes today even when the date cell also contains a time. For the example date of November 3, 2026, a row dated November 3, 2026 is marked YTD, while a row dated November 4 is not.

TODAY() is a Google Sheets function that returns the current date, so the boundary moves over time rather than requiring you to edit the formula each day. The formula is evaluated per row. Make sure the helper formula reaches newly added records; formulas do not automatically appear in every future row unless you arrange for that, such as by copying the formula down in advance or using an array-based setup appropriate to your sheet.

Build the pivot table and apply the YTD filter

Include the helper column and all fields you need in the source range. Select the data, then choose Insert > Pivot table. Choose a destination, such as a new sheet, and create the table.
In the Pivot table editor, add the fields you want to analyze under Rows or Columns. For example, add Region under Rows.
Add Amount under Values. For sales totals, set its summary to SUM if Sheets has not selected that summary automatically. Check that the source values are numeric.
Under Filters, add the YTD field. Use the filter options to keep the YTD value and exclude blank values. The pivot table will then summarize only records marked YTD.

The labels and available controls appear in the Pivot table editor beside the pivot table. If you do not see the YTD field, check that the source range includes column D and that D1 contains a header. If you add source columns later, update the range in the editor so those fields are available to the pivot table.

Group dates by year, quarter, or month

Date grouping is useful alongside a YTD filter, but it serves a different purpose. In a pivot table containing date values, right-click a date and choose Create pivot date group, then select a suitable grouping option, such as year, quarter, month, or day when offered. This organizes dates into time periods; it does not by itself mean “from January 1 through today.” Available choices can depend on the selected field and the data.

For a useful YTD trend, you could filter to YTD, place Month in Rows, and sum Amount in Values. If the pivot table groups months without separating years, make sure the YTD filter is active; otherwise, January from multiple years could be combined into one January total. For a multi-year comparison, include Year as a separate row or column field so that the years remain distinguishable.

Calendar-year YTD versus fiscal-year reporting

The example formula uses a calendar year, starting January 1. Organizations with a fiscal year that starts in another month need a different start-date calculation. For instance, a fiscal year beginning April 1 has a start date that depends on whether today is before or after April 1. Set the start date to match the organization’s reporting policy, and verify how the organization labels fiscal years before building the pivot table.

There is also a meaningful difference between current YTD and comparable-period reporting. Current YTD might mean January 1 through today, while a year-over-year comparison usually means that same elapsed period in the previous year. Filtering the pivot table to current-year YTD alone does not create the prior-year comparison. To compare periods, add an appropriate period or year label to the source data and include it in the pivot layout, taking care to apply the same cutoff rule to each year.

Check totals, refresh coverage, and common problems

No YTD rows appear: Check that the dates are real date values, the formula is filled down, the YTD header is present, and the pivot source range includes the helper column.
Dates from previous years appear: The pivot table may be grouped by month or year without the helper filter. Confirm that the YTD filter excludes blank helper values.
Today’s records are missing: Look for timestamps, text-formatted dates, or a formula that uses an earlier cutoff. The less-than-tomorrow test in the example includes all times today.
Totals seem too high: Check for duplicate source records, incorrect numeric fields, or a pivot summary that counts records instead of summing amounts. Verify a few source rows against the result.
New records are absent: Confirm that the pivot table source range covers the added rows and that the helper formula exists in them. A pivot table cannot summarize records outside its source range.

Google Sheets pivot tables recalculate from their source data, but that does not fix a source range that stops before new records or a helper column left blank in new rows. Recheck the range and helper coverage when extending a dataset. For a simple validation, compare the pivot total with a direct total of the same YTD-filtered records, and investigate any difference rather than assuming the pivot is correct.

When a pivot table is not the best option

A pivot table is well suited to interactive breakdowns by region, product, or month. If you only need one YTD total, a pivot table may be more structure than necessary; a conditional sum formula can be simpler. If you need a fixed reporting cutoff, use that chosen date instead of relying on the live current date. If others will use the sheet, label the report period clearly—for example, “Calendar YTD through November 3, 2026”—so the meaning is visible even when the table is copied or exported.

Google Sheets is browser-based, and its pivot-table workflow is not the same as Excel’s PivotTable interface. Excel has its own date grouping and filtering controls, but the helper-column logic is portable: define the date interval in the source data, then filter or summarize by that explicit marker. Do not assume that a menu name or click sequence from Excel exists in Sheets.

Frequently Asked Questions

Does Google Sheets have a built-in YTD option for pivot tables?
No dedicated YTD grouping is needed for the method above. Grouping dates by year or month organizes dates, while a helper column defines the YTD range and lets you filter the pivot table to it.

Will the YTD pivot table update automatically tomorrow?
The formula uses TODAY(), so its date boundary can advance as the sheet recalculates. The helper formula must also cover the relevant source rows, and the pivot source range must include them.

Can I include today’s transactions if the date cells contain times?
Yes. The example tests whether the date-time is earlier than tomorrow, so timestamps throughout today qualify. It assumes the values in the date column are valid Sheets date-time values.

How do I show YTD by month?
Filter the pivot table’s YTD helper field to YTD, add a date field grouped by month under Rows, and summarize the desired measure under Values. Keep the YTD filter active so earlier years do not get mixed into the monthly totals.

Can the same method use a fiscal year?
Yes, but change the start-date logic to match the fiscal year’s first day. The calendar-year formula shown here intentionally starts on January 1 and should not be used unchanged for a different fiscal calendar.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.