How to Create Target Line and Dynamic Chart Title in Excel

Share Now

If you want to add target line in your chart and want to make it dynamic, this article will guide you step by step, how to add target line with check box.

You will also learn how to create dynamic chart title, so that you don’t have to settle for static chart titles. When the data changes the title changes.

Dynamic Chart Title
Click Here to download the Sample workbook of Dynamic Line Chart

Add Check box Control:

Click on Develop Tab > Under Controls Panel > Click on Insert > Select Checkbox from Form Control List.

Dynamic title_Image 1

Once you create the checkbox, you need to link a cell for its status.
Select Checkbox > Right Click > Select Format Control > In Cell Link (reference box), Insert the any cell address, you want to link with checkbox.

Dynamic Chart Title_Image2

This cell will display the status of Check box as TRUE or FALSE.

Dynamic Title_Image3

Now with the IF function for Target Values, you can create a new reference table.

Dynamic Title_Image4

Add Dynamic Chart Title:
Use Countif Function like this =COUNTIF($C$12:$C$16,”<400”) to calculate total number of Salesman have not meet the target. Then Concatenate it with Satement like this,
=COUNTIF($C$12:$C$16,”<400″)&” of the 5 Salesman did not meet the Target this Month”

Now add the Textbox in the Chart Title, To Add it Click on Insert Tab > Under Illustration Panel > Click on Shapes and Select Text Box

Dynamic Title_Image5

Now Select Textbox and insert Concatenate Statement cell address in the formula bar.

Dynamic Title_Image6

Share Now
November 15, 2019

0 responses on "How to Create Target Line and Dynamic Chart Title in Excel"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Social Media

    facebook page EC Analytics Consulting   Linkedin Page EC Analytics Consulting   Youtube EC Analytics Consulting


    0124- 4601426


    EC Analytics will help your business make better decisions by providing expert-level business intelligence (BI) services. Forecasting, strategy, optimization, performance analysis, trend analysis, customer analysis, budget planning, financial reporting and more. EC Analytics also offers Advanced Data Analytics training in corporate and retail.

    EC Analytics Consulting @ 2019 ALL RIGHTS RESERVED