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 itle_Image1

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
April 30, 2015

1 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 *

Tableau Training in Delhi

All Rights Reserved. EC Analytics Consulting 2014 - 2019.

NM 23, Sector 14, OLD DLF Colony - Gurgaon (Haryana) India. 9582876837 | Privacy Policy

Drop us a Query

Call Us: +91 8826547882

Drop us a Query