How to Draw Target Line in Excel Graph (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

While working in Microsoft Excel with the sales-related worksheets, sometimes we need to draw a target line of sales for earning a profit for a company. Drawing a target line in an Excel graph is an easy task. This is a time-saving task also. Today, in this article, we’ll learn three quick and suitable steps to draw a target line in an Excel graph effectively with appropriate illustrations.


Introduction to Target Line in Excel Graph

To indicate how performance compares to a goal, one may add a target or goal line to an Excel bar graph.  This line travels horizontally for horizontal bar graphs and vertically for vertical bar graphs. A person or organization can use target lines as benchmarks to visually assess the effects of their activities and determine whether any revisions are necessary.

For instance, if a sales team’s daily sales quota is 160, the goal line can be drawn on the Y-axis starting from that number. This lets them know if they achieved their daily sales goal.


Let’s say, we have a dataset that contains information about several Sales representatives of XYZ group. The Name of the Sales representatives, their sales in several months, and the sales target are given in columns B, C, D, and E respectively. From our dataset, we will set the target of sales as $65,000.00, and make a target line in an Excel graph. To do that, We will use the Insert ribbon as well as the Chart Design ribbon. Here’s an overview of the dataset for our today’s task.

draw target line in excel graph


Step 1: Creating a Dataset with Proper Parameters in Excel

In this portion, we will create a dataset to draw a target line in an Excel graph. We will make a dataset that contains the sales in several months by the sales representatives. We set $65,000.00 as the target sales for the sales representatives. So, our dataset becomes.

draw target line in excel graph


Step 2: Creating Graph from Dataset

Now, using Insert ribbon, we will import a graph from our dataset. This is an easy task. This is a time-saving task also. Let’s follow the instructions below to create a graph in Excel!

  • First of all, select the range of data to draw a graph. From our dataset, we select C4 to E14 for the convenience of our work.

draw target line in excel graph

  • After selecting the data range, from your Insert ribbon, go to,

Insert → Charts → 2-D Column

  • As a result, you will be able to create a graph using the dataset which has been given in the below screenshot.


Step 3: Drawing the Target Line in Excel Graph

The crucial thing in today’s article is to draw a target line in the Excel graph. We can easily draw a target line in the Excel chart using our dataset. Let’s follow the instructions below to learn!

  • Drawing a target line, from your Chart Design ribbon, go to,

Chart Design → Type → Change Chart Type

draw target line in excel graph

  • Hence, a Change Chart Type dialog box will appear in front of you. From the Change Chart Type dialog box, Firstly, select the Combo Secondly, select the Line option from the Clustered Column drop-down list.

  • After that, press the OK option.

draw target line in excel graph

  • After completing the above process. You will be able to draw a target line in an Excel graph which has been given in the below screenshot.

draw target line in excel graph


Things to Remember

👉 #N/A! error arises when the formula or a function in the formula fails to find the referenced data.

👉 #DIV/0! error happens when a value is divided by zero(0) or the cell reference is blank.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

I hope all of the suitable steps mentioned above to draw a target line in an Excel graph will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo