How to Create Target Line and Dynamic Chart Title in Excel

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

April 30, 2015

1 Responses on How to Create Target Line and Dynamic Chart Title in Excel"

Leave a Message

© copyright www.excelcrazy.com. All rights reserved. Design and Development by www.creativebrainweb.com