00:00Here is how you can create a dynamic target line to your chart like this. We'll start by creating
00:05a target mat line here or rather column using a formula say if the condition for that will be if
00:13c5 is greater than or equal to your target we're going to display the value is in c5. If it's
00:19not
00:20then we're just going to display as blank close parenthesis and hit enter. Well the not mat formula
00:26is going to be same somewhat as the mat column the only difference is that the condition will be
00:33less than the target itself. If it's true we're going to show c5 if it's not we're going to show
00:39as blank and after that you can apply the same formula for the rest of the row like this and
00:45once that's done this is what it's supposed to look like. Next we're going to insert a chart now. First
00:52select your name like this and then hold down your ctrl key and select the column for target
00:58mat and not mat and once that's done you're going to press alternate f1 to insert a chart. Next you're
01:06going to resize and reposition the chart. Once that's done you go to chart design change chart type. In here
01:17you're going to select combo from the left hand side of the menu. For the target line you're going to
01:22basically change it to a line chart. For the mat and not mat you're going to change it both of
01:28them
01:28to clustered column and after that click ok. Now to beautify this thing a bit right click on your
01:37orange chart here format data series. On this property window here you're going to select line and fill.
01:44I'm going to select this one to be filled up as say green. This green and then the borders are
01:53the
01:53one borders. I'm going to say no line for borders. Next you're going to select the gray bar chart.
01:59Gray bar chart probably I'm going to select as orange to fill and then the borders. I'm going to remove
02:05the
02:06borders. Again these are very cosmetic and very personal so you don't really have to follow this. You
02:14and for the target line I'm going to select the target line and basically change the line type to
02:20red so it's a bit more outstanding and then transparency going to put it to 70 percent
02:28and the width I'm going to say five points like this and let's close the format data series pop-up
02:34window
02:34and change the chart title to whatever you like here. I'm going to change it as show me the money.
02:44After that you can adjust your target to whichever target you like.
02:48You can see that your target line moves according to whatever you have assigned.
02:53You can see that your target line moves in the middle of the middle of the middle of the middle
02:53of the middle of the middle of the middle of the middle of the middle of the middle of the
02:53middle of the middle of the middle of the middle of the middle of the middle of the middle of
02:53the middle of the middle of the middle of the middle of the middle of the middle of the middle
02:53of the middle of the middle of the middle of the middle of the middle of the middle of the
02:53middle of the middle of the middle of the middle of the middle of the middle of the middle of
02:53the middle of the middle of the middle of the middle of the middle of the middle of the middle
02:53of the middle of the middle of the middle of the middle of the middle of the middle of the
02:53middle of the middle of the middle of the middle of the middle of the middle of the middle of
02:53the middle of the middle of the middle of the middle of the middle of the middle of the middle
02:53of the middle of the middle of the middle of the middle of the middle of the middle of the
02:55middle
Comments