How to build a Google Sheets dashboard for a small business
A dashboard is useful only if it answers the questions you actually ask every week and updates without hours of copying. Here is what to put on it, how to feed it with data and the mistakes that make most homemade dashboards stop being used.
Start with questions, not charts
The usual way a dashboard fails is simple: someone opens a blank sheet and adds charts because they look good. A month later nobody opens the file. A better start is to write down the five or six questions you ask about the business every week or month, then build one block of the dashboard for each question.
- How much did we sell this week compared with last week and the same week last month?
- Which products or services bring the most money, and which are slowing down?
- How much cash do we have, and what has to be paid soon?
- Where do new customers come from, and how many of them buy?
- Who owes us money, and for how long?
If a number does not help you decide something, it does not belong on the main tab. It can live on a detail tab for the rare day you need it.
Metrics that fit most small businesses
The exact set depends on your business, but most owners end up with a version of these:
| Area | Metrics | Typical source |
|---|---|---|
| Sales | Revenue, number of orders, average order value, revenue by product or service | Online store or point-of-sale export, invoices |
| Profit | Gross margin, main costs by category, profit by month | Accounting export or an expense log |
| Cash | Money in, money out, balance, upcoming payments | Bank export or a manual cash log |
| Customers | New versus returning customers, leads, share of leads that buy | CRM, form responses, booking system |
| Operations | Orders in progress, overdue tasks, stock running low | Task list, inventory sheet |
Show each metric next to something you can compare it with: the previous period, the same period last year or your plan. A number on its own says very little.
Where the data comes from
A dashboard is only as reliable as the data feeding it. In practice small businesses use one or more of these sources:
- Manual entry. A simple tab or a Google Form where staff log sales, expenses or leads. Drop-down lists and data validation keep the entries clean.
- Exports from other systems. Most online stores, payment systems, accounting tools and CRMs can export CSV files. You paste or import the export into a raw data tab.
- Other Google Sheets. IMPORTRANGE pulls data from another spreadsheet, for example a separate log kept at each location.
- Automated imports. Apps Script, the scripting built into Google Sheets, can pull data from a service's API or from files in Google Drive on a schedule. Some services also offer ready-made connectors for a monthly fee per the provider's plan.
Whatever the source, keep raw data on its own tab as a plain table: one row per record, one column per field, a real date in every row, no merged cells and no totals typed in the middle. The dashboard tab only reads from it and never gets typed into.
How the dashboard updates
There are three common levels. You can start at the first and move up later without rebuilding the dashboard.
- Paste and refresh. Once a week you paste a fresh export into the raw tab. Formulas such as SUMIFS and QUERY, plus pivot tables, recalculate everything, and the charts follow.
- Live links. Data entered through forms or other sheets appears in the dashboard as soon as it is saved, with no copying at all.
- Scheduled scripts. An Apps Script runs on a timer, for example every morning, pulls new data, appends it to the raw tab and can email you a short summary.
Drop-down filters and slicers on the dashboard let you switch between periods, locations or salespeople without making a separate copy of the file for each view.
Common mistakes
- Calculations living next to raw data. Sorting or inserting a row quietly shifts formulas, and nobody notices until a number looks odd in a meeting.
- Numbers stored as text. Imported amounts with currency symbols or spaces are often read as text and silently left out of totals.
- Too many charts. A dozen charts on one screen are hard to read. Four to six blocks that answer your main questions are usually enough.
- Fixed ranges. Formulas that stop at row 500 miss everything added later. Use whole columns or ranges that grow with the data.
- Everyone can edit everything. Protect the formula ranges and the dashboard tab, and give most people view or comment access.
- Sensitive data in a widely shared file. Keep customer contacts and salaries on restricted tabs or in a separate file with fewer people on it.
- No owner. If nobody is responsible for pasting the weekly export, the dashboard goes stale within a month.
Google Sheets or Excel
Both can do the job. The choice mostly depends on how your team works and how much data you have.
| Google Sheets | Excel | |
|---|---|---|
| Sharing | Several people edit at once in a browser, access by link or email | Works best with one main owner; co-editing goes through OneDrive or SharePoint |
| Automation | Apps Script, timed triggers, Google Forms, imports from other sheets | VBA macros, Power Query for cleaning and combining exports |
| Data size | Comfortable for small and medium data, gets slow on very large files | Handles larger datasets better, especially with Power Query |
| Offline work | Limited | The desktop app works fully offline |
If your team already works in Google Workspace and several people enter data, Sheets is usually simpler. If you work with large exports, need heavy data cleaning or your accountant lives in Excel, Excel may fit better. You can also mix them: keep data entry in Sheets and do the occasional deep analysis in Excel.
Getting a dashboard built
If you would rather not build it yourself, I set up Google Sheets and Excel dashboards for small businesses. A tracker with formulas and data checks starts from $25, a report or dashboard with charts from $50, and script automation for imports, emails or scheduled updates from $80. Most files are ready in 1–2 days once I have a sample of your data and the list of questions you want answered. You get the file with a short guide on what to fill in and where to look.
I can do this for you
Excel and Google Sheets expert for hire. An Excel or Google Sheets file with formulas that do the calculations for you.
from $25Timeline: 1–2 days
FAQ
Do I need to pay for extra software to have a Google Sheets dashboard?
A Google account is enough for Sheets itself, including charts, pivot tables and Apps Script. Extra costs appear only if you add third-party connectors or need a business Workspace plan for your team, and those come with a monthly fee per the provider's plan.
How often should a small business dashboard update?
As often as you make decisions based on it. Weekly is enough for most owners. Daily updates make sense for sales, cash or stock only if you actually act on them every day.
Can the dashboard send me a summary so I don't have to open it?
Yes. An Apps Script can email you a few key numbers or a PDF of the dashboard on a schedule, for example every Monday morning, so the important figures reach you without logging in.
My data lives in four different places. Is a dashboard still possible?
Yes. Each source gets its own raw tab in the same column format, and the dashboard reads from all of them. Bringing different export formats into one shape takes the most time, so start with the two sources that matter most and add the rest later.