Excel Actual Vs Target Chart - Conditional Formatting & Dynamic Target | FREE Download

[@mirrrvelll5164]

This is the best explanation of this "issue" and how to plot some data, to compare them but with target (border) line, especially because of this dynamic part of it where you are using =COUNTA function, YET! With due to respect to other MVPs and other people with tricks, but this is like 100% what I was looking for. Crazy, and especially useful, you earned subscriber more.

[@Pinexl]

Thank you very much for your amazing feedback! We're very happy you found the video so useful!

[@guyarnaultmfoumbi8876]

Thanks for this video, I want to know if I have many target point

[@Pinexl]

Hello, with this technique, you can have only one target line. However, instead of using an error bar to show the target, you can simply have one additional column in the data table with the target value and us it to calculate the Above Target measure. Then use a combo chart and show the target series with a line. This way you can have multiple targets in the same dynamic chart.

[@serigamel]

Thanks so much for your video sir

[@Pinexl]

Glad you liked it!

[@manny8282]

Excellent video! I need the same target chart but in percentage, the formula you listed is not clear for me to understand, can you drop a message here with the =if( formula for percentage instead of $. ? Thanks!

[@Pinexl]

Hi and thanks for the question! If you want to have the chart showing percentages, the formula should stay the same. The only thing you'd need to changes is the Target value and Actual values format to percentage. Hope this helps!

[@nicoled9765]

How do I stack columns for only two values out of the 4, like for Profits column and Targeted Profits column? The other columns are Sales and Costs which I don’t want to stack. Thanks.

[@Pinexl]

Great question! Unfortunately, this would be very tricky to do... You could try separating the second pair of series in a secondary axis. This would allow you to control the overlapping separately from the first group. Still, you might not be happy with the results, depending on what you'd like to achieve. In this case, I'd advise you split the groups in different charts. An alternative approach would be to structure your data table, so that you have 2 sets of data for each month, showing the two sets of overlapping columns.