Excel のピボットテーブルで日付をグループ化できない
Excel はフィールドがすべて実際の日付のときだけ、月・四半期・年でグループ化できます。「その選択範囲はグループ化できません」と出たら、文字列の日付、意図しない空白、日付以外の値を確認します。変換してピボットを更新してから、右クリックで「グループ化」します。Google Sheets には同じダイアログがないことが多く、補助列 =DATE(YEAR(A2),MONTH(A2),1) を使います。
目次
要点
Excel はフィールドがすべて実際の日付のときだけ、月・四半期・年でグループ化できます。「その選択範囲はグループ化できません」と出たら、文字列の日付、意図しない空白、日付以外の値を確認します。変換してピボットを更新してから、右クリックで「グループ化」します。Google Sheets には同じダイアログがないことが多く、補助列 =DATE(YEAR(A2),MONTH(A2),1) を使います。
=ISNUMBER(A2)
=ISTEXT(A2)確認のポイント
実際の日付は数値です。2026年11月12日の確認では =DATE(2024,1,8) のシリアルは 45299 でした。文字 2024-01-08 を文字列として入れると ISTEXT は TRUE、ISNUMBER は FALSE、DATEVALUE は同じく 45299 です。Sheets の ISDATE はこの文字列でも TRUE になるため、Excel でグループ化できるかの判定には使えません。
注意点と互換性
変換したあと、ピボットを更新または作り直してからグループ化します。すでに月でグループ化していて日付以外が混ざった場合は、先にグループ解除します。補助列も元の日付が読めないと失敗するので、先に ISNUMBER と DATEVALUE を確認してください。
英語の詳細例・数式・著者メモ
Excel How-toTested in: The serial number and text-versus-date checks below were evaluated in Google Sheets on 12 November 2026. Excel 365 menu names are the current desktop labels; I did not click Group in Excel this session.
The broader problem of text that looks like a date is in dates stored as text. This page is about the error you see when a pivot will not group that column.
What Group needs
In Excel, right-click a date in the pivot and choose Group, then pick Months, Quarters, or Years. That command works on true date values — numbers that Excel formats as dates. =DATE(2024,1,8) is the serial 45299. If even one row in the source is not a date, grouping is refused for the whole field.
Blanks can also block grouping if they sit inside the source range. A leftover “n/a” or a note in the date column will do the same. Filters do not help: grouping looks at the field in the source, not only the rows you are displaying.
How to test the source column
Pick a suspicious cell, for example A2, and check it:
=ISNUMBER(A2)
=ISTEXT(A2)A real date should be TRUE on ISNUMBER and FALSE on ISTEXT. In the test workbook I stored the characters 2024-01-08 as text in L4. On 12 November 2026, =ISTEXT(L4) was TRUE and =ISNUMBER(L4) was FALSE. That cell would break grouping if it were in a pivot source.
Google Sheets also has ISDATE. On this text cell, =ISDATE(L4) returned TRUE, because Sheets can recognize the string as date-like even though it is still text. Do not use ISDATE alone as a “this will group in Excel” test. ISNUMBER is the one that matches how Excel treats dates.
To convert a text date:
=DATEVALUE(L4)That formula returned 45299, and =TEXT(DATE(2024,1,8),"yyyy-mm-dd") returned 2024-01-08. Copy the converted values and paste them as values over the source, or add a helper column of DATEVALUE results and build the pivot on that column. If DATEVALUE returns #VALUE!, the text is not in a date order your locale understands; the cleanup steps in dates stored as text cover those formats.
After you convert, refresh or rebuild
A pivot that was built on the text column will not start grouping just because you fixed the cells. Refresh the pivot so the cache picks up the numbers, then try Group again. If Group is still disabled, remove the date field from the pivot and drag it back, or create a new pivot on the cleaned range.
If the field was already grouped (for example by month) and you added a non-date row, ungroup first, fix the source, refresh, then group again.
In Google Sheets, there is often no equivalent Group dialog. Put a month start in a helper column:
=DATE(YEAR(A2),MONTH(A2),1)and use that helper in the pivot’s Rows. The helper will also fail if A2 is unreadable text, so run the same ISNUMBER / DATEVALUE checks first.
Once dates group, a chart of those months can still plot serial numbers if the axis is formatted as a number; that is a separate fix, in Excel chart dates showing as numbers.