How to Create a Sustainability Dashboard in Excel with ChatGPT

Tracking your company's environmental and social impact is no longer a "nice-to-have" - it's a critical part of modern business. A sustainability dashboard gives you a clear, visual report card of your efforts, but building one can feel complex. This tutorial breaks down how to create a practical sustainability dashboard in Excel, a tool you already know, using ChatGPT to help you with the technical parts like formulas and chart suggestions.

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 a Sustainability Dashboard Matters

Before diving in, let's quickly touch on why this is a valuable exercise. A sustainability dashboard isn't just for show. It helps you:

• Spot Cost-Saving Opportunities: Visualizing energy consumption or waste output can immediately highlight areas where you can become more efficient and reduce costs.
• Demonstrate Value to Stakeholders: Customers, investors, and employees increasingly prefer companies that are transparent about their impact. A dashboard provides concrete proof of your commitment.
• Make Data-Driven Decisions: Instead of guessing, you can see clear trends. Are your waste-reduction initiatives actually working? Is your energy usage trending down? A dashboard answers these questions immediately.
• Boost Employee Engagement: Sharing progress on social and environmental goals can rally your team around a shared purpose, improving company culture and morale.

Step 1: Define Your Key Sustainability Metrics (KPIs)

You can't track what you don't define. The first step is to decide which metrics - or Key Performance Indicators (KPIs) - are most important for your business. Don't try to track everything at once. Start with a few focused and measurable metrics that align with your business operations and goals. Grouping them by Environmental, Social, and Governance (ESG) categories can help organize your thoughts.

Here are some common examples to get you started:

Environmental

• Energy Consumption: Total electricity, natural gas, etc., consumed (e.g., in kilowatt-hours, kWh).
• Water Usage: Total water consumed (e.g., in gallons or cubic meters).
• Carbon Emissions (CO2e): Greenhouse gas emissions from operations, often measured in metric tons. This can be complex, so you might start with emissions from just your electricity usage or business travel.
• Waste Generation: Total weight of waste produced (e.g., in pounds or kilograms).
• Recycling Rate: The percentage of total waste that is recycled instead of sent to a landfill.

Social

• Employee Turnover Rate: The percentage of employees who leave the company over a specific period.
• Safety Incidents: Number of workplace accidents or injuries.
• Diversity & Inclusion Metrics: Percentage of employees from underrepresented groups in the workforce or in leadership.
• Community Investment: Total hours volunteered by employees or dollars donated to charities.

For example, a small online retailer might decide to start by tracking just three things: Packaging Waste (lbs), Recycling Rate (%), and Carbon Emissions from Shipping (tons CO2e). Pick what's most relevant and achievable 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 2: Gather and Structure Your Data in Excel

A great dashboard is built on clean, well-organized data. Before you build any charts, create a dedicated tab in your Excel workbook to serve as your "data" sheet. A simple table format is best.

Create columns for the essential information. The simplest structure includes a date, the metric name, its value, and an optional target value for comparison. Consistency is your friend here! Always use the same units for the same metric (e.g., always use 'kWh' for energy, not 'kilowatt-hours' one month and 'kWh' the next).

Your data table might look something like this:

Keep adding new rows for each month or week as you gather your data. This simple, "long" format is much easier for Excel's charts and Pivot Tables to understand than a "wide" format with months spread across columns.

Step 3: Use ChatGPT as Your Excel Formula Assistant

This is where things get fun. Instead of searching through endless tutorials for the right Excel formula or chart type, you can simply ask ChatGPT. It acts as an incredibly helpful co-pilot, saving you time and frustration.

Before you build your visuals, you may need to calculate some metrics. This is a perfect task for ChatGPT.

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: Asking for a Formula

Let's say you want to calculate your recycling rate from raw weight data. You have total waste in one column and recycled waste in another. You can ask ChatGPT directly.

Your Prompt to ChatGPT:

I have an Excel sheet where Column A lists the month, Column B has 'Total Waste (lbs)', and Column C has 'Recycled Waste (lbs)'.

Can you give me an Excel formula for Column D that calculates the recycling rate as a percentage?

ChatGPT will likely respond with the exact formula and an explanation:

"Certainly! In cell D2, you can use the following formula and drag it down for the other rows:"

=C2/B2

"After entering the formula, make sure to format Column D as a Percentage by selecting the column and choosing 'Percentage' from the Home tab in the Number format section."

Example: Asking for Chart Recommendations

Not sure how to visualize your data? Describe your goal to ChatGPT.

Your Prompt to ChatGPT:

What's the best chart type in Excel to track my monthly 'Energy Consumption (kWh)' against my monthly 'Target' for the last year? I want to see how we're trending and if we're hitting our goals.

ChatGPT will provide a thoughtful recommendation:

"The best chart for this is a Combination Chart. You can use a column chart to represent the actual 'Energy Consumption (kWh)' each month and a line chart to represent the 'Target'. This makes it incredibly easy to see when your consumption was over or under the target and to visualize the trend over time."

This simple piece of advice helps you choose the most impactful visualization, saving you from trial and error.

Step 4: Build Your Dashboard Visualizations

Now, create a new, blank sheet in your Excel file and name it "Dashboard." This is where your charts will live. Using your data sheet and the advice from ChatGPT, you can build your visuals one by one. Copy and paste each completed chart onto your dashboard sheet.

Here are a few common chart types and how to build them.

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

KPI Progress Card (Donut Chart)

This is perfect for showing progress towards a goal, like your overall recycling rate.

1. On your data sheet, create a small helper table with two values: your current recycling rate (e.g., 48%) and the remaining percentage (100% - 48% = 52%).
2. Select those two values.
3. Go to Insert > Chart > Pie Chart > Donut.
4. Once the chart is created, right-click and format it. Adjust the colors (e.g., team brand color for progress, light grey for the rest) and doughnut hole size.
5. Remove the legend and title. Insert a Text Box in the middle of the donut and link it to the cell containing your 48% value, so it updates automatically.

Monthly Trend (Line or Combination Chart)

As ChatGPT suggested, a combo chart is great for tracking performance against a target.

1. Organize your data with columns for 'Month', 'Actual Waste', and 'Target Waste'.
2. Select your data.
3. Go to Insert > Recommended Charts > All Charts > Combo.
4. Set the 'Actual Waste' to be a Clustered Column chart and the 'Target Waste' to be a Line chart.
5. Click OK. Your chart is ready to be formatted and moved to your dashboard.

Comparisons (Bar Chart)

A simple bar chart is perfect for comparing metrics across categories, such as a breakdown of waste types.

1. Set up your data with 'Waste Type' in one column and 'Weight (lbs)' in another.
2. Select the data.
3. Go to Insert > Chart > Bar Chart.
4. Clean up the design - add clear axis labels, a title, and adjust colors to make it easy to read.

Step 5: Arrange and Enhance Your Dashboard

With a few charts built, it’s time to arrange them on your dashboard sheet. The goal is to tell a story at a glance.

• Organize information logically. Place your most important, high-level KPIs (like your donut chart-style goal trackers) at the top. Charts showing trends and comparisons can go below.
• Keep design clean. Use a consistent color scheme and font. White space is important, a cluttered dashboard is confusing. Remove unnecessary lines, backgrounds, and borders from your charts.
• Add clear titles. Give every chart a descriptive title, like "Monthly Energy Use vs. Target" instead of just "Chart 1".
• Consider adding Slicers. If you built your charts from a Pivot Table, you can add Slicers (Insert > Slicer) to make your dashboard interactive. This will let users filter the entire dashboard by month, year, or location with a single click. You can ask ChatGPT: "How do I add a 'Year' slicer to control three different Pivot Charts on my Excel dashboard?"

Final Thoughts

Creating a sustainability dashboard in Excel doesn't have to be an overwhelming task reserved for data analysts. By starting with clear KPIs, structuring your data logically, and using a tool like ChatGPT to guide you through formulas and chart selections, you can build a powerful tool for tracking and improving your company's impact.

While this manual process in Excel is a fantastic starting point, it still requires ongoing effort to export data, update your sheets, and refresh the charts. To automate this entire process, we built Graphed . You can connect your data sources directly and ask in plain English - "create a dashboard showing my monthly carbon emissions and recycling rate" - and get a shareable, real-time dashboard that updates automatically, saving you hours of spreadsheet work.