Section 4: Excel/Google Sheets, data analysis and reporting

Scenario 39: Preparing a weekly KPI summary from multiple tabs

Level: Intermediate · Tools: Google Sheets, Gemini in Google Sheets and Google Sheets AI function

Who and in what situation

Hakan, an operations analyst at an e-commerce brand in İstanbul, presents order, return, customer service and web traffic tabs to management each week. Each tab uses a different date format and KPI name.

How it is done today

Hakan copies totals, calculates ratios manually and prepares a one-page chart. If one tab has a different week filter, he rebuilds the report.

How to do it with AI

  1. He opens the Ask Gemini panel and selects the tabs to use from the source drop-down menu in the prompt box. Google supports using multiple tabs in a single prompt. [70]
  2. He requests a summary of orders, return rate, response time and traffic for the same date range, and asks it to specify the source tab for each KPI.
  3. He generates the summary table and chart, adds them to the sheet using Insert and compares each total against the filter in the source tab. [70]
  4. If a short category label is needed, he uses =AI() and an optional range in AI function, then selects Generate and insert. [78]

Example prompt

Compare {the order, return, customer service and traffic tabs} for the same {week range}. Specify each tab’s date column and record coverage. Create a weekly summary with columns for KPI name, value, source tab and verification note. If the coverage differs, write a warning instead of a total.

Evidence and real-world example

No published independent case study was found for this e-commerce report. Google’s official documentation covers multiple tabs, data analysis, charts and the workflow for generating content with a range in AI function. [70] [78]

Points to consider

AI function is limited to text and does not automatically access the entire spreadsheet or Drive files. [78] Google states that Workspace data is not used to train generative AI models outside Workspace without permission; nevertheless, check sharing permissions. [83] Do not report KPI totals or ratio denominators without human review.

Sources for this scenario

  1. Collaborate with Gemini in Google Sheets (support.google.com)
  2. Use the AI function in Google Sheets (support.google.com)
  3. How Gemini in Workspace protects your data (support.google.com)