Graphed LogoGraphed
Login

How to Create a Sustainability Dashboard in Google Sheets

Cody Schneider•

Tracking your company's environmental impact is no longer a “nice-to-have” - it's a core part of building a modern, resilient business. You don't need expensive, complex software to get started. This article will walk you through building a practical and effective sustainability dashboard using a tool you already know: Google Sheets.

What is a Sustainability Dashboard?

A sustainability dashboard is a tool that visually tracks, analyzes, and displays key environmental, social, and governance (ESG) metrics. Think of it as a central hub for your sustainability performance, helping you move from abstract goals to measurable progress. It allows you to monitor your carbon footprint, resource consumption, and waste management in one clear, understandable place.

Having this data consolidated helps in several ways:

  • Better Decision-Making: Spot trends, identify high-impact areas for improvement, and allocate resources more effectively. See a spike in energy use last month? The dashboard will make it obvious.

  • Stakeholder Communication: Easily share progress with customers, investors, and employees. A well-designed dashboard tells a powerful story of your commitment to sustainability.

  • Increased Accountability: When metrics are clearly visible, teams can take ownership of their impact and work towards tangible goals.

Step 1: Choose Your Key Sustainability Metrics (KPIs)

Before you build anything, you need to decide what to measure. The sheer number of potential ESG metrics can be overwhelming, so the key is to start small and focus on what's most relevant and measurable for your business. You can always expand later.

Here are a few foundational metrics to consider, broken down by category:

Environmental Metrics

These are often the easiest to start with because they are tied to tangible resources you can track through bills and invoices.

  • Energy Consumption: Measured in kilowatt-hours (kWh), this is found on your electricity bills. It's a direct indicator of your operational energy footprint.

  • Water Usage: Measured in gallons or cubic meters, this data comes from your water bills.

  • Waste Generation: Track the total weight (in lbs or kg) of waste sent to landfill and the total weight of materials recycled. You can find this on invoices from your waste management provider.

  • Carbon Emissions (CO2e): This is the ultimate environmental metric. For simplicity, start with your Scope 2 emissions - the indirect emissions from your purchased electricity. You can calculate this by converting your kWh of energy consumption into kilograms of carbon dioxide equivalent (kg CO2e) using an emissions factor.

Social Metrics

While often more qualitative, some social metrics can be tracked effectively.

  • Employee Turnover Rate: A simple percentage tracking how many employees leave over a period. It's a key indicator of employee satisfaction and company culture.

  • Diversity & Inclusion (D&I) Data: Track metrics like the percentage of women in leadership roles or the diversity breakdown of new hires.

For this tutorial, we will focus on building a dashboard for the environmental metrics (Energy, Waste, and Carbon Emissions), as the data is generally more accessible for small businesses.

Step 2: Structure Your Google Sheet for Success

Organization is everything. A well-structured Google Sheet will save you countless headaches. Instead of cramming everything into one tab, we’ll create a multi-tab system that separates data input from the final visualization.

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

  1. Dashboard: This will be the final, presentation-ready dashboard. It will contain only charts, graphs, and key numbers. No raw data lives here.

  2. Raw_Data: This is where you will manually enter all your data over time. Think of it as a logbook.

  3. Calculations: This "helper" tab will hold our pivot tables and summary calculations. It will act as the engine that powers the charts on our Dashboard tab.

This separation makes your workbook clean, scalable, and much less likely to break when you add new data.

Step 3: Collect and Log Your Sustainability Data

With your sheet structured, it’s time to gather your data. This is often the most time-consuming part, but it gets easier once you establish a rhythm.

Head to your Raw_Data tab and set up the following columns: Date, Category, Metric, Amount, Unit.

Here’s how to fill it out:

  • Date: The end date of the period you're measuring (e.g., 1/31/2024 for January). Recording data monthly is a great starting point.

  • Category: The high-level group (e.g., Energy, Waste).

  • Metric: The specific thing you are measuring (e.g., Electricity Usage, Waste to Landfill, Recycled Materials).

  • Amount: The numerical value (e.g., 1200).

  • Unit: The unit of measurement (e.g., kWh, lbs).

Calculating Your Carbon Emissions

To calculate your Scope 2 emissions from electricity, you'll need an emissions factor. These factors vary by region based on the local energy grid's mix of renewables and fossil fuels. The U.S. Environmental Protection Agency (EPA) provides a tool called POWER Profiler to find emissions factors for your specific region.

Once you have a factor (e.g., 0.85 lbs CO2e/kWh), the formula is straightforward:

Monthly kWh Usage * Emissions Factor = Monthly CO2e

For example: 1200 kWh * 0.85 = 1020 lbs CO2e. You would add this as a new row in your Raw_Data tab.

Step 4: Use Pivot Tables to Summarize Your Data

Now, let’s turn that long list of raw data into clean, summarized tables that we can use to build charts. This is where the Calculations tab comes into play.

Click over to your Calculations tab. We are going to create a pivot table to summarize data monthly.

  1. Select all the data in your Raw_Data tab (click cell A1, then Ctrl+A or Cmd+A).

  2. Go to the menu and click Insert > Pivot Table.

  3. A pop-up will ask where to put it. Choose ‘Existing sheet’ and type Calculations!A1 into the box to place it in your helper tab.

  4. The Pivot Table editor will appear on the right. Configure it as follows:

    • Under Rows, add Date. Then, right-click any of the dates in the pivot table and choose Create pivot date group > Year-Month.

    • Under Columns, add Metric.

    • Under Values, add Amount, summarized by SUM.

Your pivot table will instantly transform your raw data log into a beautifully organized summary.

Step 5: Create Visualizations for Your Dashboard

This is the fun part! Let’s create some charts and Scorecard KPIs on our Calculations tab, which we will later move to the Dashboard tab.

Create each chart based on the data in your new pivot table.

KPI Scorecard: Total CO2e Emissions

A scorecard shows a single, crucial number. Let's create one for total emissions year-to-date.

  1. Find an empty cell on your Calculations tab.

  2. Type in a simple formula to sum up your CO2e lbs column from the pivot table. If your CO2e emissions are in column B of the pivot table, it might look like this: =SUM(B2:B13)

  3. Give it a label in the cell above, like "Total CO2e (lbs)." Format the number to be large and bold.

Line Chart: Energy Consumption Over Time

A line chart is perfect for showing trends.

  1. Select the date column (e.g., A1:A13) and the Electricity Usage column (e.g., C1:C13) in your pivot table. Hold Ctrl or Cmd to select non-adjacent columns.

  2. Go to the menu: Insert > Chart.

  3. Google Sheets will likely default to a line chart, but if not, select it in the chart editor under 'Chart type'.

  4. Customize the title to something clear, like “Monthly Energy Consumption (kWh).”

Column Chart: Landfill Waste vs. Recycling

A column chart is great for comparing categories.

  1. Highlight the date column, the Waste to Landfill column, and the Recycled Materials column in your pivot table.

  2. Go to Insert > Chart.

  3. Select a Stacked column chart from the editor for a nice comparison.

  4. Customize the title and colors as needed to make it easy to read.

Step 6: Assemble Your Final Dashboard

Once you’ve created your charts, it’s time to move them to their final home on the Dashboard tab.

  1. Go back to the Calculations tab. Click the three little dots on the top-right corner of your first chart.

  2. Select Copy chart.

  3. Navigate to your Dashboard tab, and press Ctrl+V or Cmd+V to paste it. The chart remains linked to the source data.

  4. Repeat this for all your charts and your scorecard KPIs.

  5. Arrange your charts in a logical, clean layout. You might put your main KPIs at the top, followed by the trend charts. Use the gridlines to align everything neatly.

  6. Add a title at the top of the sheet, such as "Company Sustainability Dashboard - 2024".

You can even go further by changing the background colors, using merged cells to create header sections, and adding text boxes to explain what a specific chart is showing. The final product should be a clear, one-glance view of your company’s environmental performance.

Final Thoughts

Building a sustainability dashboard in Google Sheets puts you in control of your environmental data. By structuring your data, using pivot tables to summarize it, and creating clear charts, you can transform simple inputs into powerful insights that drive real change. It's a scalable system that can grow with your sustainability initiatives over time.

As you gather more data across a growing number of platforms - from utility providers to your ad platforms - the manual work of downloading CSVs and updating your Raw_Data tab can become a chore. We built Graphed to solve exactly this problem. Instead of wrestling with spreadsheets, we let you connect all your data sources in just a few clicks. From there, you can just ask in plain English, "create a dashboard showing my carbon footprint versus ad spend this quarter," and get a live, interactive dashboard built for you in seconds, no pivot tables needed.