How to Make a Goal Chart in Excel

Tracking progress against your goals is a lot more motivating when you can see it visually. While typing numbers into a spreadsheet works, turning those numbers into a dynamic goal chart makes your progress tangible and easy to understand at a glance. This guide will walk you through a few simple but powerful methods to create goal charts directly in Excel, transforming your static data into compelling visuals.

Good Charts Start With Good Data Setup

Before you can build any chart, you need to structure your data properly. Don't worry, this is the easiest part. For most goal charts, a simple four-column setup is all you need. This structure makes creating different types of visuals straightforward.

Let's use a common example: tracking monthly sales revenue. Your data might look like this:

• Metric: What are you tracking? (e.g., Monthly Sales Revenue)

• Goal: What is your target? (e.g., $50,000)

• Actual: Where are you right now? (e.g., $37,500)

• % Complete: The calculation of Actual ÷ Goal.

• % Remaining: The calculation of 1 - % Complete.

In your Excel sheet, an active setup using formulas would look like this:

In cell C2 (for % Complete), you'd enter the formula:

=B2/A2

And in cell D2 (for % Remaining), you'd use:

=1-C2

Make sure to format cells C2 and D2 as percentages. This simple table that updates automatically is the foundation for the visual charts we're about to build.

Method 1: The Classic Thermometer Chart

The thermometer chart is a fantastic and intuitive way to show progress towards a single goal. It visually "fills up" as you get closer to your target, which makes it incredibly simple for anyone to understand.

Step 1: Insert a Basic Column Chart

First, click on just the cell containing your % Complete value (in our example setup, that's cell C2). Only select this one cell. Then, navigate to the Insert tab in Excel, click the Column/Bar chart icon, and choose a simple 2-D Clustered Column chart. You'll get a very plain-looking single bar chart.

Step 2: Format the Bar

Right-click on the blue bar in your new chart and select Format Data Series. A new menu will appear on the right side of your screen. Look for the setting called Gap Width and change its value to 0%. This will make the bar expand to fill the entire width of the chart area, making it look much more like the body of a thermometer.

Step 3: Set the Axis Maximum to 100%

Next, we need to make sure the thermometer's scale is correct. Right-click on the vertical axis on the left (the one with the percentage values) and choose Format Axis. Under Bounds, you need to set the Maximum value to 1.0 (which represents 100%). This ensures your goal always corresponds to the very top of the chart.

Step 4: Add a Border and Clean Up the Chart

To really sell the "thermometer" look, give the chart a distinct border. Right-click the bar again, go to Format Data Series, and click the paint bucket icon for Fill & Line. Under Border, choose a Solid line and pick a dark color like gray or black. You can also adjust the width to make it stand out more.

Finally, remove the unnecessary elements to clean it up. Click on the chart title and delete it. Do the same for the horizontal axis labels and the gridlines. This keeps the focus squarely on your progress.

Method 2: The Modern Donut Chart (Progress Ring)

For a sleek, dashboard-friendly look, a donut chart is an excellent choice. It works much like the progress rings you see on smartwatches, providing a clean, circular visual of your progress.

Step 1: Set Up and Select Your Data

This chart uses both the % Complete and % Remaining values. Drag your mouse to select those two pieces of data from your table (cells C2 and D2 in our example). Hold down the Control or Command key to select non-adjacent cells if needed.

Step 2: Insert the Donut Chart

Go to the Insert tab, click the Pie Chart icon, and select Doughnut from the bottom of the list. Excel will instantly generate a two-color donut chart.

Step 3: Format the Chart Slices

Out of the box, the colors won't mean much. The goal here is to make the "remaining" portion visually fade into the background. Right-click on the slice that represents % Remaining (it's usually the smaller slice at first, shown in orange) and choose Format Data Point. Change its fill color to a light, neutral gray.

Next, right-click the % Complete slice (the blue part) and change its fill color to something more vibrant - a nice green for positive progress or your company's brand color.

Step 4: Add a Dynamic Percentage Label in the Middle

The empty center of the donut chart is the perfect spot for a data label. To do this, go to the Insert tab, select Text Box, and draw a box in the middle of the chart. Do not type directly into the text box. Instead, click on the text box border, then go to the Excel formula bar at the top of the screen. Type an equals sign (=) and then click on the cell that contains your % Complete value (C2). Press Enter.

Now, the text box is dynamically linked to your data! When your progress updates, the number in the center of the donut chart will update automatically. You can style the text in the box (make it bold, change the font size) to make it easy to read.

Just like with the thermometer, finish by deleting the chart title and the legend for a cleaner appearance.

Method 3: The Target vs. Actual Bar Chart

What if you're tracking multiple goals at once, like the performance of an entire sales team? A target vs. actual chart is perfect for this. It clearly shows each person or category's performance side-by-side with their goal.

Step 1: Organize Your Data for Comparison

For this chart, you'll need a slightly different data structure. Let's imagine you're a sales manager tracking four reps:

Step 2: Create a Combo Chart

Highlight your entire data table, including headers. Again, go to the Insert tab and click the Column/Bar chart icon, but this time select Combo Chart. By default, both the Target and Actuals will be shown as clustered columns. Our goal is to make the "Target" a subtle marker instead of a full bar.

Step 3: Customize the Chart Series

In the "Create Combo Chart" window, you'll see your two data series - "Sales Target" and "Actual Sales."

• Keep the Actual Sales series as a Clustered Column.

• Change the chart type for the Sales Target series to a Line with Markers.

This will transform the "Target" bars into a line. We're close, but a connecting line between unrelated sales reps doesn't make sense. We just want the marker.

Step 4: Format the Target Line into a Marker

Click OK to create the chart. Now, right-click on the "Sales Target" line on your chart and choose Format Data Series. In the menu on the right, under the Fill & Line icon, select No line to make the connecting line disappear.

Next, go to the Marker section within the same menu. Under Marker Options, choose "Built-in." Change the Type to the horizontal bar shape (it looks like a dash). Finally, increase the Size to something significant, like 20 or 30, so it appears as a thick line representing the goal.

Now you have clean, clear bars showing actual performance with a simple, elegant line marking the target for each rep. It's a professional-looking chart that tells a clear story.

Tips for Better Goal Charts

• Keep It Simple: Avoid 3D effects, shadows, and other fancy formatting. The purpose of a goal chart is clarity. Excessive formatting just adds noise and makes it harder to read the data.

• Use Color Meaningfully: A simple color scheme works best. You can use conditional formatting in your donut or thermometer chart to have the color turn green when you hit your goal, yellow when you're close, and red if you're behind.

• Add Data Labels: For viewers who want the exact number, adding data labels directly onto your charts can be helpful. Right-click any chart element (like a bar or donut slice) and select "Add Data Labels."

• Make Your Title Dynamic: Just like with the text box, you can dynamically link your chart title. Click on the chart title, type = in the formula bar, and click on the cell containing your metric's name (e.g., "Monthly Sales Revenue"). Now your chart will always have an accurate and relevant title.

Final Thoughts

Creating goal charts in Excel is a powerful skill that can turn you from someone who just collects data into someone who tells a story with it. By transforming raw numbers into visual progress trackers like thermometer, donut, or target vs. actual charts, you can keep yourself and your team motivated and focused.

Of course, this process can become time-consuming, especially when your data lives across different platforms like Google Analytics, Shopify, and your CRM. We built Graphed to solve exactly this problem. Instead of exporting CSVs and manually building charts, our platform connects directly to your data sources. You can simply ask, "Show me a comparison of actual sales from Shopify versus our monthly goal for the team," and Graphed instantly builds a real-time, interactive dashboard for you.