How to Create a Sustainability Dashboard in Google Sheets with ChatGPT

Building a sustainability dashboard from scratch might sound like a job for a data analyst, but it's simpler than you think. You can create a powerful and insightful reporting tool using just Google Sheets, with ChatGPT acting as your personal formula expert to do the heavy lifting. This guide will walk you through setting up your data, prompting ChatGPT for the right formulas, and visualizing your sustainability KPIs.

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

Why Track Sustainability in a Dashboard?

Monitoring your sustainability efforts isn't just about being good to the planet, it's good for business. A well-structured dashboard transforms raw data from utility bills and waste reports into clear business intelligence. It helps you:

• Make Informed Decisions: See exactly where your energy consumption is highest or which waste reduction initiatives are working so you can allocate resources effectively.
• Identify Trends: Spot seasonal spikes in water usage or track your carbon footprint reduction over time. A chart makes a trend instantly recognizable.
• Communicate Progress: Easily share your company's performance and impact with stakeholders, team members, and customers in a format that's easy to understand.

In short, it moves your sustainability program from guesswork to a data-driven strategy.

Step 1: Define Your Key Sustainability Metrics (KPIs)

Before you open a single spreadsheet, you need to decide what you’re going to measure. If you try to track everything, you'll end up tracking nothing. Start small with a few critical metrics that are relevant and accessible for your business.

Your data sources could be things like utility bills, transport logs, waste management invoices, or supplier information. Here are some common categories and KPIs to consider:

Environmental KPIs

• Energy Consumption: Measured in kilowatt-hours (kWh) from electricity bills.
• Water Usage: Measured in gallons or cubic meters from water bills.
• Waste Generation: Total waste produced, measured in kilograms (kg) or pounds (lbs). You can break this down further into "Waste to Landfill" and "Recycled Waste."
• Recycling Rate: The percentage of your total waste that is recycled. The formula is (Recycled Waste / Total Waste) * 100.
• Carbon Emissions (tCO2e): Measuring your carbon footprint is often more complex, but you can start with Scope 1 (direct emissions from owned sources) and Scope 2 (indirect emissions from purchased electricity) based on utility data and standard emission factors.

Social & Governance KPIs

• Employee Volunteer Hours: Total hours your team has dedicated to community initiatives.
• Diversity Metrics: Percentage of employees from underrepresented groups.
• Supplier Audits: Percentage of key suppliers who have passed your company's sustainability or ethical audit.

For this tutorial, we will focus on the environmental classics: Energy Consumption, Water Usage, Waste to Landfill, and Recycled Waste.

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: Structure Your Google Sheet for Success

A well-organized spreadsheet is the foundation of a great dashboard. The most effective structure is to separate your raw data from your visual dashboard. This keeps things clean and makes your formulas much easier to manage.

Create a new Google Sheet and set up two tabs at the bottom:

1. Raw Data: This is where you will manually enter all your data.
2. Dashboard: This tab will pull data from the "Raw Data" sheet to display your charts and summary tables.

Setting up the 'Raw Data' Tab

In the "Raw Data" tab, create a simple table with the following columns. Sticking to a consistent format like this is vital for easy analysis.

• Column A: Date
• Column B: Metric
• Column C: Value
• Column D: Unit
• Column E: Notes (Optional, for adding context)

Your filled-out table should look something like this. The key is that every data point gets its own row.

Step 3: Prompt ChatGPT to Write Your Formulas

Now for the exciting part. We'll use your "Dashboard" tab to summarize the data, but instead of fumbling with formulas yourself, we'll ask ChatGPT for help. The key to success here is writing clear, specific prompts.

Always tell ChatGPT:

• The name of your sheet (e.g., 'Raw Data').
• The exact columns your data is in (e.g., Column A is 'Date', Column B is 'Metric').
• What you want to calculate.

Example 1: Total Energy Consumption (Year-to-Date)

Let’s start simple. We want a single number showing our total energy use this year.

Your Prompt for ChatGPT:

"I have a Google Sheet with a tab named 'Raw Data'. Column B contains the metric name, like 'Energy Consumption', and Column C contains the numeric value. Write a Google Sheets formula to sum all values in Column C only where Column B says 'Energy Consumption'."

ChatGPT's likely Formula Output:

=SUMIF('Raw Data'!B:B, "Energy Consumption", 'Raw Data'!C:C)

Go to your "Dashboard" tab, pick a cell (say, B2), and label the cell next to it "Total Energy (kWh)". Paste the formula in B2. It will now automatically calculate the total.

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

Example 2: Calculating Recycling Rate

Now for something a little more complex. To get our recycling rate, we need to find the total recycled waste and divide it by the total overall waste (landfill + recycled). We can ask ChatGPT to string this all together.

Your Prompt for ChatGPT:

"In my 'Raw Data' sheet, metrics are in Column B and values in Column C. I need a formula to calculate my recycling rate. Find the sum for 'Recycled Waste', then find the sum for 'Waste to Landfill'. The final formula should be: (Sum of 'Recycled Waste') / (Sum of 'Recycled Waste' + Sum of 'Waste to Landfill'). Display it as a percentage."

ChatGPT's likely Formula Output:

=(SUMIF('Raw Data'!B:B, "Recycled Waste", 'Raw Data'!C:C)) / (SUMIF('Raw Data'!B:B, "Recycled Waste", 'Raw Data'!C:C) + SUMIF('Raw Data'!B:B, "Waste to Landfill", 'Raw Data'!C:C))

Paste this into another cell on your "Dashboard" tab and format it as a percentage by going to Format > Number > Percent in the Google Sheets menu. Now you have a live KPI!

Example 3: Building a Monthly Summary Table

Individual KPIs are great, but the real insights come from seeing trends over time. Let’s build a table that summarizes energy use by month.

Your Prompt for ChatGPT:

"I need a Google Sheets formula using the QUERY function. My data is in the 'Raw Data' tab. Column A has dates, Column B has the metric name, and Column C has values. Create a table that shows the total 'Energy Consumption' grouped by month. The table should have two columns: 'Month' and 'Total Energy'."

ChatGPT's likely Formula Output:

=QUERY('Raw Data'!A:C, "SELECT MONTH(A), SUM(C) WHERE B = 'Energy Consumption' GROUP BY MONTH(A) LABEL MONTH(A) 'Month', SUM(C) 'Total Energy'", 1)

This powerful QUERY function looks at your raw data, selects only the rows for "Energy Consumption," groups them by month, sums the values, and provides a neat summary table for you.

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 4: Visualize Your Data with Charts

With your summary data calculated, converting it into charts is easy.

Monthly Energy Trend Line Chart

1. Highlight the monthly energy data generated by your QUERY formula in the previous step.
2. Go to the Google Sheets menu and select Insert > Chart.
3. Google Sheets is smart and will likely suggest a line chart, which is perfect for showing trends over time. If not, you can select it from the "Chart type" dropdown in the chart editor.
4. Use the customization options in the chart editor to add a title like "Monthly Energy Consumption (kWh)" and clean up the axis labels.

Drag this chart to a prime position on your dashboard. Repeat this process for other metrics like water usage or waste generation.

Other Useful Chart Types

• Pie or Donut Chart: Perfect for displaying your recycling rate or the composition of your total waste.
• Scorecard Chart: Use this to display a single, large KPI number, like your total annual CO2 emissions or your current recycling rate. Find it under "Chart type."
• Bar Chart: Great for comparing metrics across different locations or categories.

Final Thoughts

Building a custom sustainability dashboard no longer requires intricate spreadsheet knowledge. By setting up a logical data sheet and using ChatGPT to generate the right formulas, you can create a clear, automated report right in Google Sheets that helps you track progress and make better strategic decisions.

While this Google Sheets method is incredibly powerful, the process still relies on manually entering data and managing spreadsheet formulas. At Graphed, we remove those steps entirely. We believe getting insights shouldn't involve CSV downloads or complex functions. By connecting your data sources directly to our platform, you can simply ask questions in plain English - like "show me a dashboard comparing our energy usage to our carbon footprint over the last year” - and get a live, interactive dashboard built for you instantly.