How to build pivot tables with Gemini in Google Sheets and check totals

Gemini in Google Sheets can create a pivot table from a plain-English request typed into its side panel, such as "Create a pivot table that shows the sum of sales by region," which is the example Google itself gives in the Sheets help center. The result is a regular Sheets pivot table, placed on a new sheet or in a range you name, that you can open with the Edit button and change by hand like any other. Google's help page also states that Gemini "may suggest inaccurate or inappropriate information," so the analyst who runs the request still owns the numbers that come out of it.

The practical workflow has three parts. You prepare a clean source range with one header per column, you write a prompt that names the rows, the columns, the value field and the aggregation, and then you check the pivot against a few independent totals before anyone uses it in a report. The worked example below uses twelve illustrative orders and gives the exact figures the pivot table should show, computed in Python, so you can compare them with what Gemini produces in your own file.

Requirements and limits in this piece come from Google's help center and Google Workspace Updates announcements, checked on November 9, 2026. Plan names, usage limits and regional availability change often, so confirm them in the Admin console or on your own account before rolling the workflow out to a team.

··········

GEMINI IN GOOGLE SHEETS: PLANS, ADMIN SETTINGS AND DOCUMENTED LIMITS.

The help center article on collaborating with Gemini in Sheets says the feature "requires an eligible Google Workspace or Google AI plan" and lists pivot tables among the actions Gemini can perform, alongside charts, formulas, conditional formatting, filters, sorting and dropdowns. Edits are shown first as an action preview card, and nothing changes in the sheet until you click Apply. An Undo option reverses the last Gemini action, and Google notes that it stays available only until you make further changes.

The April 22, 2026 Workspace Updates post that introduced the improved Gemini in Sheets experience, which builds and edits whole spreadsheets including pivot tables, names the eligible editions and add-ons and sets out two restrictions that affect teams outside the United States.

........

Item	What Google documents
Workspace editions	Business Standard and Plus, Enterprise Standard and Plus
Add-ons	AI Expanded Access, AI Ultra Access, Google AI Pro for Education
Personal plans	Google AI Pro and Ultra
Admin setting	Gemini for Workspace must be enabled in Sheets
Language and region	Improved experience launched for US and English only
Usage limits	Per-user limits apply after July 15, 2026
File format	Works best with native Sheets files; save Excel files as Sheets first
Source data	Each column of a pivot source needs a header

........

Google's earlier announcement of Gemini actions in Sheets, dated May 13, 2025, listed specific restrictions for pivot tables created this way: no calculated columns and no pivot groups, with edits applied to one table range at a time. Later updates added analysis across multiple tables, so treat the 2025 list as the baseline and test whether your request for a calculated field or a date group goes through. When it does not, you can still add both manually in the pivot table editor, as described in the review section below.

Two documented behaviors affect the audit trail. Conversations with Gemini in Sheets are not saved to Gemini Activity, and the conversation history is lost when you reload the browser, close and reopen the spreadsheet, or go offline. Keep the prompt you used in a note or a cell comment if the pivot feeds a recurring report, because the side panel will not remember it.

··········

PREPARING THE SOURCE RANGE FOR A GEMINI PIVOT TABLE.

The Sheets help page on pivot tables says each column of the selected source data needs a header, and a clean header row also gives Gemini the field names it needs to read a prompt correctly. Short, unambiguous labels such as "Order date", "Region", "Product", "Units" and "Revenue" make it easy to name fields in a prompt and to check later that the right column was summarized.

Three preparations prevent most pivot errors. Store dates as real date values, because the option to group by month, Create pivot date group, works on date-formatted cells. Store amounts as numbers without currency symbols typed as text. Remove subtotal and total rows from the source, since a pivot will add them a second time.

The illustrative dataset below has twelve orders across three regions, three products and two months. It is invented for this example and contains no real company data.

........

Order date	Region	Product	Units	Revenue
2026-09-03	West	Laptops	4	5200
2026-09-08	East	Monitors	10	2300
2026-09-15	West	Monitors	6	1440
2026-09-22	Central	Laptops	3	3900
2026-09-29	East	Laptops	5	6250
2026-10-02	Central	Monitors	8	1920
2026-10-07	West	Laptops	2	2600
2026-10-13	East	Docks	12	1800
2026-10-20	Central	Docks	7	1050
2026-10-24	West	Docks	9	1350
2026-10-27	East	Monitors	4	960
2026-10-30	Central	Laptops	1	1300

........

In the sheet, the headers sit in row 1 and the data in rows 2 to 13 of columns A to E. Total revenue is 30,070 and total units are 71, two control figures that every correct pivot built on this range must reproduce.

··········

WRITING THE PIVOT TABLE PROMPT FOR GEMINI IN SHEETS.

Open the side panel with Ask Gemini at the top right of the spreadsheet. A prompt that names every part of the pivot leaves less to interpretation than a short request. Google's own examples are brief ("create a pivot table showing total sales by region"), and that works for a single dimension, but a two-dimensional summary is safer when the prompt spells out rows, columns, values, aggregation and destination.

Create a pivot table from A1:E13 on a new sheet.
Rows: Region. Columns: Order date grouped by month.
Values: SUM of Revenue. Show grand totals for rows and columns.

When the action preview card appears, read the fields it lists before clicking Apply. Check that the value is Revenue rather than Units, that the aggregation is SUM rather than COUNTA or AVERAGE, and that the source range covers all twelve rows. If Gemini cannot group the dates by month in the same step, apply the pivot and then right-click any date in the pivot table and choose Create pivot date group, then select the month option, which is the manual route the help center documents.

A second prompt can extend the same table once the first version checks out. Asking for each region's share of total revenue maps to the Show as setting under Values in the pivot editor, and asking for a filter maps to the Filters section, where the help center describes Filter by condition and Filter by values.

In the same pivot table, add Revenue again and show it as a percentage of the grand total.

··········

EXPECTED RESULT: THE CORRECT REVENUE PIVOT FOR THE EXAMPLE.

The figures below were computed in Python with pandas from the twelve illustrative orders. A correct pivot for the first prompt shows exactly these values; any difference points to a wrong field, a wrong aggregation or a range that missed rows.

........

Region	Sep 2026	Oct 2026	Grand Total
Central	3900	4270	8170
East	8550	2760	11310
West	6640	3950	10590
Grand Total	19090	10980	30070

........

For the second prompt, the share of grand total is 27.2% for Central, 37.6% for East and 35.2% for West, rounded to one decimal. These three percentages add up to 100%, and a pivot whose shares do not is using a different denominator, such as the column total for each month instead of the grand total.

You can reproduce the expected table outside Sheets with the code below, after downloading the source range as orders.csv with File, Download, Comma Separated Values (.csv).

import pandas as pd

df = pd.read_csv("orders.csv", parse_dates=["Order date"])
df["Month"] = df["Order date"].dt.strftime("%b %Y")
pivot = pd.pivot_table(df, index="Region", columns="Month", values="Revenue",
                       aggfunc="sum", fill_value=0, margins=True, margins_name="Grand Total")
pivot = pivot[["Sep 2026", "Oct 2026", "Grand Total"]]
print(pivot)
print((df.groupby("Region")["Revenue"].sum() / df["Revenue"].sum() * 100).round(1))

··········

CHECKING A GEMINI PIVOT TABLE WITH SUMIFS AND CONTROL TOTALS.

A pivot table checks itself only when its totals are compared with figures calculated another way. Three formulas typed in empty cells next to the source data give that independent view, and each should match the pivot exactly.

=SUM(E2:E13)
=SUMIFS(E2:E13, B2:B13, "West")
=SUMIFS(E2:E13, B2:B13, "East", A2:A13, ">="&DATE(2026,9,1), A2:A13, "<"&DATE(2026,10,1))

On the example data the first formula returns 30,070, which must equal the pivot's grand total. The second returns 10,590, the West row total. The third returns 8,550, the East value for September, and confirms that the month grouping assigned each order to the right month. If the grand total came out at 60,140, exactly twice the correct figure, the source range would almost certainly include a total row that the pivot summed along with the orders.

Averages need a separate check. A request for "average price per unit by product" can be read in two ways: the average of row-level prices, or total revenue divided by total units. For Laptops in the example, the average of the five row prices is 1,290.00, while revenue of 19,250 divided by 15 units gives 1,283.33. For Monitors the two readings give 237.50 and 236.43. The weighted figure is normally the one a sales report needs, and in a pivot table it comes from a calculated field rather than from the AVERAGE aggregation. The formula below returns the weighted Laptops price for comparison.

=SUMIFS(E2:E13, C2:C13, "Laptops") / SUMIFS(D2:D13, C2:C13, "Laptops")

To add the calculated field by hand, the help center describes the steps: click Edit under the pivot table, click Add next to Values, choose Calculated field, enter a formula such as ='Revenue'/'Units', and set Summarize by to Custom. The editor shows a green checkmark when the formula syntax is valid.

··········

WHAT TO REVIEW BEFORE SHARING A GEMINI PIVOT TABLE.

The review list follows from how Sheets pivot tables behave. The pivot refreshes when its source cells change, so new orders added below row 13 are not included until the source range is extended; use Select data range in the pivot editor to widen it. High-confidence suggested pivot tables can be inserted automatically when you create a pivot through the Insert menu, and the help center explains how to turn that off under Tools, Suggestion controls, Enable Pivot table suggestions, which avoids confusion about which pivot Gemini built.

Charts behave differently from pivots. Google's help page says a chart generated by Gemini does not link or respond to changes in the original data set, with BigQuery Connected Sheets charts as the exception. If a chart sits on top of the pivot, rebuild it from the pivot range with Insert, Chart so it updates with the data. For broader analysis and charting with the side panel, our earlier piece on Gemini for data analysis and chart generation in Google Sheets covers those actions, and how Gemini analyzes Google Sheets data describes the analytical depth of the native integration.

Sharing settings decide who can see the content Gemini generates. Google states that content from Gemini may be visible to users outside your domain if the file has out-of-domain users with access or was created by one. Check the file's sharing list before running prompts on customer or payroll data, and follow your company's data classification rules for what may be sent to Gemini at all.

When the source data lives in BigQuery rather than in a sheet, Connected Sheets is the documented route: the help center says pivot fields from an underlying table require Connected Sheets, and Gemini runs read-only queries there. Our coverage of conversational analytics in Connected Sheets and the guide on writing SQL with Gemini in BigQuery and checking the bytes it scans cover the cost and permission questions that apply to that setup.

··········

GEMINI PIVOT TABLES IN SHEETS: WHAT DEPENDS ON YOUR PLAN AND YOUR DATA.

Google documents that Gemini in Sheets can create pivot tables from a prompt, that every edit appears as a preview card you must apply, and that the result is a standard pivot table you can edit by hand. The improved experience announced in April 2026 is tied to Business Standard and Plus, Enterprise Standard and Plus, specific AI add-ons and Google AI Pro or Ultra, launched in US English, and has been subject to per-user usage limits since July 15, 2026. The numeric value of those limits does not appear in the help pages checked for this piece.

The accuracy of each pivot depends on the source range and the prompt more than on the tool. A header on every column, real dates, numeric amounts, no total rows and a prompt that names the value, the aggregation and the grouping remove the usual causes of wrong totals. A SUM of the source, one SUMIFS per row or cell you plan to quote, and a weighted calculation for any average are enough to confirm the figures before the pivot reaches a dashboard or a meeting.

··········

FOLLOW US FOR MORE.

·····

DATA STUDIOS

·····

datastudios.org