How to Create a Small Business Dashboard in Google Sheets with ChatGPT
Trying to understand your business performance can feel like piecing together a puzzle with parts scattered across a dozen different browser tabs. You have sales data in Shopify, website traffic in Google Analytics, and ad results on Facebook. A simple, at-a-glance view of your key metrics is all you need, but building one seems complicated. This article will show you how to create a useful small business dashboard right inside Google Sheets, using ChatGPT to do all the heavy lifting with formulas and chart setups, even if you’ve never written a formula in your life.
Why build your small business dashboard in Google Sheets?
Before diving into the "how," let's talk about the "why." You don't need expensive, complex software to get a clear picture of your business health. Google Sheets is a surprisingly powerful tool for building a dashboard for a few key reasons:
It's free and accessible. If you have a Google account, you have Google Sheets. There’s no software to install and you can access your dashboard from any device.
It's familiar. Most of us have some experience with spreadsheets, so the environment isn't intimidating. It works just like Excel.
It's highly customizable. You have complete control over what your dashboard looks like, which metrics you feature, and how you arrange the information.
It's easy to share. You can share a link with your team, investors, or clients to give them a real-time view of performance, controlling whether they can view or edit it.
At its core, a dashboard is just a one-page visual story about your business, told through your most important numbers or Key Performance Indicators (KPIs). It turns dense rows of data into easy-to-digest charts and scorecards, making it simple to spot trends and make informed decisions.
Step 1: Get Your Data in One Place
The single most important step in creating a useful dashboard is organizing your data. A dashboard is only as good as the data feeding it, so starting with a clean, consistent source of truth is essential. The best way to do this is with a simple two-tab system in a new Google Sheet.
Create two tabs: "Raw Data" and "Dashboard"
Your first tab, which you should name "Raw Data," will be the engine of your dashboard. This is where you'll paste all the raw information you export from different platforms. Your second tab, named "Dashboard," will be the clean, beautiful, and visual front end that only displays the final charts and KPIs.
For your "Raw Data" sheet to work, it needs to be structured in a simple, database-like format. This means each row is a record and each column represents a specific attribute. For a small business, a simple structure might look like this:
Column A: Date
Column B: Source/Channel (e.g., "Facebook Ads," "Google Organic," "Email Campaign")
Column C: Sessions/Traffic
Column D: Leads/Signups
Column E: Customers/Sales Count
Column F: Revenue
This process of exporting CSV files from each platform and pasting them into one sheet can be manual, but a little time spent here pays off immensely. The key is consistency. Always use "Facebook Ads" instead of switching between "Facebook," "FB," and "social." This clean data structure makes it incredibly easy for formulas — and ChatGPT — to understand what you're working with.
Step 2: Use ChatGPT as Your Google Sheets Guru
This is where the magic happens. You don't need to know complex formulas like SUMIFS, VLOOKUP, or QUERY. Your job is to describe what you want to calculate or visualize in plain English, and ChatGPT’s job is to be the expert who spits out the exact formula you need.
Think of ChatGPT as an on-demand data analyst who never gets tired of your questions. You can ask it to write formulas, suggest the best chart type for your data, explain how a function works, and even troubleshoot why a formula isn't working as expected. You just have to tell it what you want to achieve.
Step 3: Build Your Dashboard Blocks with ChatGPT
Let's walk through building out your "Dashboard" tab by creating a few common components. We'll use our sample data structure from the "Raw Data" tab (Date in A, Source in B, Sessions in C, Leads in D, Customers in E, Revenue in F).
Calculate your core metrics ("Scorecards")
Scorecards are the big, at-a-glance numbers that sit at the top of most dashboards. Let's create a few.
KPI 1: Total Revenue
Go to your empty "Dashboard" tab. Let's say you want to display "Total Revenue" in cell B2. Click in the cell and then open up ChatGPT. Type in a prompt like this:
I have a Google Sheet with a tab named 'Raw Data'. In that tab, column F contains all my revenue data. Give me a simple Google Sheets formula to calculate the total revenue.
ChatGPT will almost instantly give you the formula:
=SUM('Raw Data'!F:F)
Copy that formula, paste it into cell B2 on your "Dashboard" tab, and hit Enter. That’s it! You now have a live summary number. Add the label "Total Revenue" in cell A2 to keep it organized.
KPI 2: Revenue by Source
Now let's get a bit more specific. What if you want to see how much revenue each marketing channel drove? You can create a small table for this on your Dashboard. In cell A5, type "Facebook Ads." In cell A6, type "Google Organic." Now, let's get the formula. Ask ChatGPT:
In my 'Raw Data' tab, column B has the traffic source (e.g., 'Facebook Ads') and column F has revenue. On my 'Dashboard' tab, cell A5 contains the text 'Facebook Ads'. Give me a Google Sheets formula that sums the revenue in column F only for the rows where the source in column B matches cell A5 on my Dashboard.
ChatGPT will give you a SUMIF formula:
=SUMIF('Raw Data'!B:B, A5, 'Raw Data'!F:F)
Paste this into cell B5, next to your "Facebook Ads" label. The amazing part is you can now drag the little blue square in the corner of that cell down to cell B6. The formula will automatically update to look at cell A6 ("Google Organic") and calculate the revenue from that source, too. You just built a summary table without writing any code.
Visualize Your Data with Charts
Dashboards are all about visual trends. Numbers are great, but charts tell a story. ChatGPT can also act as your visualization expert, recommending the right chart types and a good way to structure the data for them.
Chart 1: Revenue Trend Over Time
You want to see if your revenue is growing month over month. This is a perfect job for a line chart. But first, you'll need a summary table that groups your daily data into monthly totals. Don’t worry - ChatGPT can help with that too.
Ask ChatGPT:
In my 'Raw Data' tab, I have a 'Date' in column A and 'Revenue' in column F. I want to create a line chart showing total revenue per month. What's the best way to create a summary table for this in Google Sheets, and which chart should I use?
ChatGPT will likely suggest a few options, including using a Pivot Table or formulas like SUMIFS with month functions. It will guide you through creating a small table that might look like this: "Jan," "Feb," "Mar" in one column and their corresponding total revenues next to them. Follow its steps to generate the summary table on your "Dashboard" sheet. Once you have that simple table (e.g., in cells E1:F12), just highlight it, go to Insert > Chart in Google Sheets, and select the Line Chart option. Google Sheets is smart enough to handle the rest.
Chart 2: Leads by Source Breakdown
Want to see which channels are driving the most leads? A pie chart is an excellent way to show parts of a whole.
Ask ChatGPT a similar prompt:
I want to create a pie chart showing my percentage of leads from each marketing source. In my 'Raw Data' tab, 'Source' is in column B and 'Leads' is in column D. How should I set up the data and create the chart?
Just like before, it will tell you to first create a summary table that sums up all leads for each unique source. Get the formula from ChatGPT, create the summary table right on your dashboard, highlight the data, and select Insert > Chart > Pie Chart.Don't be afraid to ask follow-up questions to customize it: "How do I add percentage labels to each slice?" or "How can I change the color of the Facebook slice?"
Step 4: Design Your Dashboard Layout
With your key metrics and charts created, the final step is to arrange them in a logical and visually appealing way. Here are a few quick tips for a professional-looking dashboard:
Follow the "F" pattern. People naturally read screens in an "F" shape. Put your highest-level summary numbers (like Total Revenue) at the top-left corner where the eye goes first.
Tell a story. Arrange your charts to follow a logical flow. Perhaps start with high-level outcomes (Revenue) and then drill down into the contributing factors (Leads by Source).
Use consistent colors and labels. If "Facebook Ads" is blue in one chart, make it blue in all of them. Title every chart and number clearly so anyone can understand it without asking a question.
Embrace white space. Don't cram everything together. Leaving some empty space makes the dashboard easier to read and less overwhelming.
Tips for Writing Better ChatGPT Prompts
The quality of your output depends on the quality of your prompt. You don’t have to be a tech wizard, but keeping a few things in mind yields better results.
Be Specific About Your Environment. Start prompts by stating you're working in "Google Sheets." Mention your tab names ('Raw Data') and column letters (column A, column B). This context eliminates guesswork for the AI.
Describe Your Goal Clearly. Instead of "sales formula," say what you showed before: "Give me a formula that sums the revenue in column F only for rows where column B says 'Facebook Ads'." The clearer the goal, the better the formula.
Ask for Explanations. If you're curious, end your prompt with "...and please explain how this formula works in simple terms." It's a fantastic way to slowly learn what these powerful functions actually do.
Refine and Iterate. Don't worry if the first answer isn't perfect. Tell ChatGPT what went wrong ("That formula gave me an error, I think because some cells are blank") and it will adjust its approach. Treat it like a conversation.
Final Thoughts
Creating a business dashboard in Google Sheets might sound technical, but it's simpler than ever with an AI assistant like ChatGPT. By framing your analytics needs as simple questions, you can bypass the steep learning curve of spreadsheet formulas and focus on bringing your key business metrics into one clear, consolidated view. It’s a powerful way to turn raw data into strategic insights without any specialized technical skills.
While using Google Sheets and ChatGPT is a fantastic and free way to get started, the process still requires manual work - downloading CSVs, cleaning data, and pasting it all together. To simplify this, we built Graphed to do the work for you. We connect directly to your data sources like Shopify, Google Analytics, and ad platforms, creating a fully automated, real-time dashboard. Instead of wrangling spreadsheets, you can just ask in plain English, "build me a dashboard comparing my campaign performance across Facebook and Google," and get a live, interactive report in seconds, giving you back time to focus on actually growing your business.