How to Create a Company Dashboard in Google Sheets with ChatGPT

Building a company dashboard shouldn't require a data science degree, but wrestling with Google Sheets formulas can make it feel that way. Using ChatGPT as your personal data analyst, you can skip the learning curve and go straight from raw data to insightful charts. This guide will walk you through how to use simple prompts to create a powerful company dashboard right inside Google Sheets.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

First Things First: Why Google Sheets and ChatGPT?

Google Sheets is a popular choice for business reporting for good reason: it's free, cloud-based, and built for collaboration. The main hurdle for many is moving beyond basic tables to create dynamic summaries and visualizations. Functions like QUERY, SUMIFS, and IMPORTRANGE are incredibly powerful but can be intimidating to learn.

This is where ChatGPT comes in. Instead of spending hours watching YouTube tutorials to find the right formula, you can simply describe what you need in plain English. Think of it as a translator converting your business questions into the precise syntax Google Sheets understands. This combination allows you to leverage the accessibility of Sheets with the power of an expert, on-demand data assistant.

Laying the Foundation: Prepping Your Data for Success

Before you write a single prompt, a little prep work goes a long way. The quality of your dashboard depends entirely on the quality and structure of your data. A well-organized dataset makes creating charts and summaries a breeze.

Step 1: Define Your Goals and Key Performance Indicators (KPIs)

A dashboard without clear goals is just a collection of charts. Ask yourself: What questions do I need this dashboard to answer? The answer will determine your Key Performance Indicators (KPIs). KPIs are the critical metrics that measure your company's progress toward its goals. Here are a few examples to get you started:

• Sales Team: Monthly Recurring Revenue (MRR), Average Deal Size, Win Rate, Sales Cycle Length.
• Marketing Team: Website Traffic, Conversion Rate, Leads by Source, Cost Per Acquisition (CPA).
• E-commerce Business: Average Order Value (AOV), Customer Lifetime Value (LTV), Cart Abandonment Rate, Revenue by Product.

Choose 3-5 high-level KPIs to start. You can always add more later.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 2: Gather and Structure Your Data

Dashboards need data, which typically comes from exports (CSVs) from your various platforms like your CRM, ad platforms, or e-commerce store. Once you have your data, structure is everything. Your data should be in a tabular format, often called "tidy data," where:

• Each row represents a single record (e.g., one sale, one website session, one lead).
• Each column represents a single variable (e.g., 'Date', 'Amount', 'Sales Rep', 'Source').
• Column headers are simple and descriptive.

For best results, keep your raw data on a separate tab in your Google Sheet. Create two tabs: one named "Raw Data" and one named "Dashboard." Paste your tidy data into the "Raw Data" tab. This keeps your calculations and your source data neatly separated, making your dashboard easier to manage and update.

Here’s an example of a well-structured sales dataset:

This clean structure is what allows you to start asking meaningful questions.

Building Your Dashboard with ChatGPT Prompts

With your data organized, you’re ready to let ChatGPT do the heavy lifting. We’ll build out a dashboard step-by-step using specific prompts.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

1. Calculating Core KPI Metrics

Let's start by calculating some high-level KPIs to display at the top of our dashboard. These are your "at a glance" numbers. Your prompts to ChatGPT should be specific, mentioning your sheet and column names for the best results.

Let's assume your sales data is on a sheet called 'Raw Data', with columns for 'Deal Status' and 'Amount'.

Goal: Calculate Total Revenue

Here's a sample prompt to get the formula for calculating total revenue from deals marked as "Won."

PROMPT: "I have a Google Sheet with a tab called 'Raw Data'. Column D contains the 'Deal Status' (either 'Won' or 'Lost'), and Column E contains the 'Amount'. What formula should I use to calculate the sum of 'Amount' only for deals where the status is 'Won'?"

ChatGPT will likely give you a SUMIF formula:

=SUMIF('Raw Data'!D:D, "Won", 'Raw Data'!E:E)

Simply copy this formula, paste it into a cell on your "Dashboard" tab, and label it clearly as "Total Revenue."

Goal: Calculate the Win Rate

Next, let's figure out our sales win rate (Won Deals / Total Deals). This requires two calculations, which you can ask for in one prompt.

PROMPT: "Building on the last formula, I also need to calculate the win rate. I need two separate formulas: one to count the total number of 'Won' deals in Column D, and another to count all deals (both 'Won' and 'Lost'). Then, tell me how to divide them to get the win rate as a percentage."

ChatGPT will guide you:

• For Won Deals: =COUNTIF('Raw Data'!D:D, "Won")
• For Total Deals: =COUNTA('Raw Data'!D:D) (assuming only 'Won' or 'Lost')
• For Win Rate: divide the first cell by the second and format as percentage, e.g., =B2/B3

2. Creating Summary Tables with Pivot Tables

Now that you have your headline numbers, you’ll want to see a breakdown. Creating a pivot table from raw data is the perfect way to summarize information, like seeing which sales rep is bringing in the most revenue. The problem? Pivot tables have confused people for decades.

This is where ChatGPT shines as an educator.

Goal: See Total Revenue per Sales Rep

PROMPT: "Explain step-by-step how to create a pivot table in Google Sheets. My data is in the 'Raw Data' tab. I want to see the total revenue ('Amount' in Column E) broken down by each sales representative ('Sales Rep' in Column B)."

ChatGPT will provide a clear, actionable guide that looks something like this:

1. Select your data range in the 'Raw Data' tab (e.g., A1:E100).
2. Go to the Insert menu and select Pivot table.
3. Choose to insert it on an 'Existing sheet' and select a cell on your 'Dashboard' tab.
4. In the Pivot table editor that appears on the right:

Following these steps, you'll have a clean summary table showing exactly how much revenue each person has generated.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

3. Visualizing Your Data with Charts

Dashboards are all about visual representation. Now you can use your new summary tables to create line charts, bar charts, and pie charts. Again, you don't need to hunt for the right menus and settings, just ask ChatGPT.

Goal: Visualize Monthly Revenue as a Line Chart

First, you would create a pivot table (using the method above) that summarizes revenue by 'Date', grouped by month. Once you have that simple table showing Month and Total Revenue, you can prompt ChatGPT:

PROMPT: "I now have a pivot table on my 'Dashboard' tab. Column A is the month and Column B is the total revenue for that month. Tell me how to create a line chart in Google Sheets to visualize this trend."

ChatGPT will walk you through the process:

1. Highlight the data in your pivot table (the months and their corresponding revenue figures).
2. Go to the Insert menu and choose Chart.
3. In the Chart editor sidebar, under Setup, select Line chart as the chart type.

Voila! You now have a visual trend line showing revenue over time. You can move this chart to a central position on your dashboard.

Pro Tips for Better Results

Getting useful results from ChatGPT is a skill. The more context you provide, the better the output.

• Be Specific: Always mention tab names, column letters, and column headers. Instead of saying "my sales figures," say "Column E, which is labeled 'Amount'."
• Provide Context: Explain your end goal. "I'm trying to figure out which marketing channel is most effective" is better than "sum this column for me."
• Iterate and Refine: Don't be afraid to tell ChatGPT it's wrong. If a formula gives you an error, copy the error and ask it to troubleshoot. "That formula returned a #REF! error. My data is in A1 through G200. Can you fix it?"
• Ask for Dashboard Design Advice: You can even get guidance on layout. Try a prompt like, "What are some best practices for organizing a sales dashboard? Where should I place KPI scorecards versus detailed charts?"

Final Thoughts

Combining the universal accessibility of Google Sheets with the power of ChatGPT unlocks a whole new level of data analysis for anyone, regardless of technical background. By defining your goals, structuring your data correctly, and using clear, descriptive prompts, you can transform a static spreadsheet into a dynamic and insightful company dashboard.

Turning manual reporting into an automated process is a huge time-saver. That's why we created Graphed. Instead of exporting CSVs and building formulas from scratch, our platform connects directly to your data sources like Google Analytics, Shopify, and Salesforce. You can ask questions in plain English - like "create a dashboard showing my sales pipeline from HubSpot" or "compare Facebook Ads spend vs. revenue" - and get live, interactive dashboards built for you in seconds. It allows your entire team to explore data and get immediate answers without an ounce of spreadsheet work.