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 your company's Google Workspace plan does not show Gemini in Sheets, an admin may have turned it off even though it comes included on Business plans. Paste your data directly into ChatGPT and ask the same questions instead. 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.