Structure your Google Sheet as one clean table, connect it to Looker Studio, add scorecards, a time-series chart, a breakdown table and a date filter, then share the report by link. It refreshes automatically as the sheet changes and costs nothing.
Tools you need
The 10 steps
Clean the sheet
One header row, one record per row, one type per column (dates as dates, numbers as numbers). Put the data on its own tab called data. Remove merged cells and totals rows.
Add a few helper columns
Month (=TEXT(A2,"yyyy-mm")), a category label and any calculated value you will chart. Fill them down.
Connect Looker Studio
In Looker Studio create a blank report, add a data source, choose Google Sheets, pick the file and the data tab, and confirm the field types.
Add scorecards
Insert scorecards for the three numbers that matter (for example revenue, orders, average order value). Set the aggregation and number format.
Add a time-series chart
Dimension: date. Metric: the main number. Set the date granularity to week or month.
Add a breakdown table
Dimension: category or city. Metrics: count and sum. Sort by the main metric and add a bar inside the table for readability.
Add controls
Add a date-range control and a drop-down filter for category. Every chart on the page responds to them.
Design it
One title, a one-line description of what the data is and when it refreshes, consistent colours, and no more than six charts on the page.
Share
Click Share and allow anyone with the link to view. Test the link in a private window. Embed it on a site with the Embed option if needed.
Document
Add a text box with the data source, the refresh behaviour (Looker Studio re-reads the sheet, with a short cache) and one recommendation from the numbers.
Make it yours
- Connect GA4 and Search Console for a website dashboard.
- Use a Google Form as the input so a team can add rows from their phones.
- Blend two sheets (sales and targets) to show progress to goal.
Troubleshooting
| Problem | Fix |
|---|---|
| Numbers show as text | Fix the column type in Sheets, then refresh fields in the data source. |
| Dates are wrong | Use a real date format in Sheets and set the field type to Date in Looker Studio. |
| Chart is empty after a filter | The filter field has blanks; fill or exclude them. |
Frequently asked questions
Is Looker Studio free?
Yes. Looker Studio is free with a Google account, including sharing and embedding.
Does the dashboard update automatically?
Yes. It reads the sheet when opened, with a short data cache you can refresh manually.
Can I use Excel instead of Google Sheets?
Upload the Excel file to Google Drive and convert it to Sheets, or use Power BI Desktop for a local dashboard.
Learn this with mentors
Data and business analytics programmes with admissions open: live classes, mentors and project work.
