Get a Target Line Across a Column Chart in Google Sheets

Have you ever wondered how to add a goal or target line across a column chart in Google Sheets? Well, it’s actually quite simple! In this article, I’m going to show you step-by-step how to format your data and create a column chart with a target line. So let’s dive in and make your charts stand out!

Understand the Target/Goal Line in Column Chart in Google Sheets

Before we get started, let’s quickly understand what a target or goal line is in a column chart. When you have set a fixed target or goal for multiple categories, it’s a good idea to visualize that data with a column chart. By drawing a straight target line across the chart, you can easily compare the performance of different categories and determine if they have met your expectations.

As you can see in the example above, each column represents the sales volume for a salesperson, and the horizontal straight line is the target set for them. This makes it easy to identify underperforming salespersons, such as “Bobby,” “Michael,” and “Carlos.”

How to Add a Target Line Across a Column Chart in Google Sheets

Now that you understand the concept, let’s get into the nitty-gritty of adding a target line to your column chart. There are two methods we’ll explore: creating a basic column chart with a horizontal target line and creating a dynamic column chart with target data points.

Create a Basic Column Chart with Horizontal Target Line in Sheets

1. Enter your sample data, including the target value in each row.
2. Select the data range, including the target values.
3. Open the chart editor by going to Insert > Chart.
4. In the chart editor panel, select “Combo chart” as the chart type.
5. Customize the chart by selecting the series “Sales volume” and setting it to “Column.” Then select the series “Target” and set it to “Line.” To improve the visual appearance, change the data point shape to “Square.”

Following these steps will give you a column chart with a target line!

Get a Dynamic Column Chart with Target Data Points in Sheets

If you prefer a more dynamic approach, where the chart updates based on your selected values, you can use formulas to populate the source data.

1. Follow all the steps mentioned above for creating a basic column chart with a target line.
2. Instead of manually entering the data, use the OFFSET and MATCH combination formula to extract the data dynamically from a range.
3. Place the formula in a cell to extract the values based on a selected value from a drop-down menu.

Here’s an example of the formula you can use:

=OFFSET(H2:M,0,MATCH(F2,H1:M1,0)-1,COUNT(H2:H),2)

By using this formula, you can populate your column chart with the corresponding data points based on your selection from the drop-down menu.

To create the drop-down menu:

1. Go to the Data menu and select Data validation.
2. Configure the settings to create a drop-down menu with the months as menu items.

And voila! You now have a dynamic column chart with target data points that automatically updates based on your selection.

That’s all there is to it! Adding a target line to your column chart is a powerful way to visualize your data and make informed decisions. Whether you prefer a basic target line or a more dynamic approach, Google Sheets has got you covered. So go ahead, create stunning charts, and impress your audience!

For more exciting tips and tricks on Google Sheets, visit Crawlan.com.