← Back to Blog
Excel

Free Financial Dashboard in Google Sheets

Ankit KumarAnkit KumarPublished August 30, 202412 min read

Why Build in Google Sheets?

Expensive BI tools like Tableau, Power BI, or Zoho Analytics are powerful — but they require setup time, technical skill, and ongoing subscription cost. For most SMEs, a well-built Google Sheets dashboard delivers 90% of the value at 10% of the cost.

The dashboard we'll build:

  • Monthly P&L summary with trend charts
  • Revenue breakdown by category
  • Gross and net margin tracker
  • Cash position and burn rate
  • AR and AP aging summary
  • KPI scorecards with color coding

Building time: 2–3 hours. Ongoing update time: 20–30 minutes/month.

Table of Contents

  1. Sheet 1: Data Input ("Raw Data" tab)
  2. Sheet 2: Monthly P&L ("P&L" tab)
  3. Sheet 3: Dashboard ("Dashboard" tab)
  4. Advanced Tips
  5. Why Most Dashboards Stop Getting Used
  6. FAQ
  7. Conclusion

Sheet 1: Data Input ("Raw Data" tab)

All data goes in one place. Structure:

Revenue table: Month | Category | Amount Expense table: Month | Category | Amount AR aging summary: Month | Current | 1-30 days | 31-60 days | 61-90 days | 90+ days Cash position: Month | Opening Cash | Receipts | Payments | Closing Cash

This tab is the only one you update monthly. All other tabs pull from here.

Sheet 2: Monthly P&L ("P&L" tab)

Use SUMIFS to aggregate from Raw Data:

Monthly revenue: =SUMIFS(Revenue[Amount], Revenue[Month], A2, Revenue[Category], "*")

Category breakdown: =SUMIFS(Revenue[Amount], Revenue[Month], A2, Revenue[Category], "Product Sales")

Build 12 months across columns, line items down rows. Calculate:

  • Gross Profit = Revenue - COGS
  • Gross Margin % = Gross Profit / Revenue (format as %)
  • EBITDA = Gross Profit - OpEx
  • Net Profit = EBITDA - Depreciation - Interest - Tax

Sheet 3: Dashboard ("Dashboard" tab)

This is the one-page view you share with stakeholders.

Section 1: KPI Row (top of page) Current month values for: Revenue | Gross Margin | Net Margin | Cash Balance | DSO With: value, vs. prior month change, vs. target

Conditional formatting:

  • Green: above target by >5%
  • Yellow: within 5% of target
  • Red: below target by >5%

Section 2: Revenue Chart (monthly bar chart) Data: P&L tab revenue row, last 12 months. Add a target line as a line series.

Section 3: Margin Trend (line chart) Gross margin % and net margin % over 12 months. Shows if margins are compressing.

Section 4: Cost Breakdown (donut chart) Current month expenses by category. Immediately shows if any category is unusually high.

Section 5: Cash Flow Waterfall (bar chart) Opening cash → add receipts → subtract payments → closing cash. Shows cash movement visually.

Section 6: AR Aging (stacked bar) AR aging buckets over 6 months. A growing 61-90 days bucket is visible immediately.

Advanced Tips

Auto-Importing from Zoho Books

Zoho Books allows CSV export of P&L and Balance Sheet. Use Google Sheets IMPORTDATA or a manual monthly import:

  1. Export P&L from Zoho as CSV
  2. Open in Google Sheets
  3. Copy the relevant rows into your Raw Data tab
  4. Dashboard updates automatically

Sharing with Stakeholders

  • Share the Dashboard tab only: File → Share → specify recipients, "Viewer" access
  • Create a shareable link to the Dashboard tab specifically
  • Schedule a monthly email from Google Sheets: Extensions → AppSheet (or use a Google Apps Script)

Adding Commentary

Next to the KPI row, add a "Commentary" column for each month: "Revenue: $280,000 (down 8% vs. target; 2 enterprise deals slipped to Q3)"

This makes the dashboard self-explanatory without a call.

Why Most Dashboards Stop Getting Used

The failure mode for a financial dashboard is rarely "it wasn't sophisticated enough" — it's almost always that the monthly update step became a chore and quietly stopped happening. A few habits keep a dashboard alive past month three:

  • Keep the Raw Data tab genuinely simple to update — if updating it takes more than 20-30 minutes, it's a candidate to skip during a busy month, and one skipped month often becomes a pattern
  • Show budget vs. actual, not just actuals — a number alone doesn't tell anyone whether it's good or bad news, which is the entire point of a dashboard someone is supposed to act on
  • Limit the dashboard to 5-8 metrics — beyond that, most people stop reading it every month even if the data stays current

If your data currently lives in Zoho Books, this dashboard pairs naturally with Zoho Books automations that reduce the manual pull each month.

Conclusion

A functional financial dashboard doesn't require expensive tools or a data science team. The Google Sheets approach above gives you everything you need to make informed financial decisions — and takes an afternoon to build.

If you'd like our pre-built Google Sheets financial dashboard template, reach out at hello@financebridge.tech. We'll send it across with the formula structure already set up.

#google sheets#dashboard#financial reporting#excel#free tool

Frequently Asked Questions

Can this handle multi-entity businesses?
Yes — add an "Entity" column to your data tables and use SUMIFS to filter by entity. Create separate P&L tabs per entity and a consolidated P&L that sums across all.
Is this secure enough for financial data?
For internal use with appropriate sharing permissions, yes. Don't share links publicly. Use "Restricted" sharing (only people explicitly added can view). For very sensitive data, consider Google Workspace with domain restrictions.
What if I'm on Excel instead of Google Sheets?
All of this works in Excel. The charts look slightly better in Excel, Power Query makes data import easier, and the performance is better for large datasets. The main trade-off is collaboration (Google Sheets wins) and external sharing.
How many metrics should actually be on the dashboard tab?
5-8 is the practical range. Below 5, the dashboard misses important signals; above 8, most people stop actually reading it every month, which defeats the purpose. Revenue, gross margin, net margin, cash balance, and DSO cover the core of most businesses' needs — add 1-3 more only if genuinely decision-relevant.
What's the most common reason a financial dashboard stops getting used after a few months?
The monthly update step becomes a chore that gets skipped, and a dashboard with stale data quickly loses trust and gets ignored entirely. Keeping the Raw Data tab genuinely simple to update — even if that means less sophistication elsewhere — is what actually determines whether a dashboard survives past month three.
Should the dashboard show budget vs. actual, or just actuals?
Budget vs. actual, wherever a target exists. A revenue number alone tells you what happened; a revenue number next to its target tells you whether that's good or bad news — which is the entire point of a dashboard someone is supposed to act on, not just observe.