Use Google Sheets' AI to Build a Production KPI Dashboard

What This Does

Google Sheets' built-in Gemini AI can write formulas, flag trends, and summarize your production data — turning the numbers you already track into a live dashboard that shows gaps before they become problems.

Before You Start

• You have a Google account (free)
• You have production data in Google Sheets (or are willing to type it in from your paper tally)
• You're logged into Google Sheets at sheets.google.com

Steps

1. Open or create your production tracking sheet

Set up columns if you don't have them already: Date | Shift | Line | Target Units | Actual Units | Downtime (min) | Downtime Reason | Defects | Notes

2. Open the Gemini AI sidebar

Click Extensions in the top menu → Gemini in Sheets (or look for the star/sparkle icon in the toolbar). If you don't see it, go to Extensions → Add-ons → Get add-ons and search "Gemini in Sheets."

3. Ask Gemini to write a formula for you

In the Gemini sidebar, type: "Write a formula for column J that calculates OEE from Actual Units in column E, Target Units in column D, and Downtime in column F (in minutes, out of a 480-minute shift)."

What you should see: Gemini writes the formula directly — you click "Insert" and it drops into your sheet.

4. Ask for a summary of your data

Highlight your last 2 weeks of data, then in the Gemini sidebar type: "Summarize the patterns in this production data. Which lines or shifts are missing target most often? What are the top downtime reasons?"

What you should see: A written analysis identifying your worst-performing lines or shifts by name — something that would take you 30 minutes to figure out manually.

5. Add conditional formatting to highlight gaps

Ask Gemini: "Add conditional formatting to column E (Actual Units) to highlight cells red when actual is more than 10% below target."

Real Example

Scenario: You've been tracking production on 3 lines for 2 weeks in a Google Sheet and need to report to your plant manager on Friday.

What you type: "Analyze this data: which of my 3 lines had the most downtime last week and what were the top 2 reasons? Summarize in 3 bullet points I can use in my report."

What you get: "Line 2 had the most downtime (94 min total) driven by conveyor belt jams (67 min) and operator breaks overrunning (27 min). Lines 1 and 3 were within 5% of target on all shifts."

Tips

• You don't need to know Excel formulas — just describe what you want to calculate in plain English
• If Gemini isn't available as an add-on at your company, paste your data directly into ChatGPT and ask the same questions — it works just as well
• Start with just 5 rows of real data to test formulas before applying them to hundreds of rows

Tool interfaces change — if a button has moved, look for similar AI/magic/smart options in the same menu area.