Thank you for your very useful instruction which I'm going to apply in my charts to make them easier to understand.
@ExcelOffTheGrid
2 ай бұрын
Glad it was helpful!
@IvanCortinas_ES
Жыл бұрын
Certainly the presentation of the graph is much more attractive that way. Thanks for sharing Mark. Excellent!!
@ExcelOffTheGrid
Жыл бұрын
My pleasure! Thanks Ivan.
@howtomasterexcel9702
Жыл бұрын
This is an excellent tutorial. Many thanks for sharing!👏
@kebincui
Жыл бұрын
Very creative idea👍👍. Thanks Mark
@ExcelOffTheGrid
Жыл бұрын
Thanks Kebin 😁
@Picla_Peremohy
Жыл бұрын
Awesome as alway Mark. This is fantastic!
@ExcelOffTheGrid
Жыл бұрын
Thanks Jim, I'm glad you like it! 😁
@drsteele4749
Жыл бұрын
That's a much improved visual presentation. Well done! And I did not know about the mmmmm format for months. I have a tip: at 6:05 in MAXIFS, or any of those IFS functions, you can just use "" and skip the &""
@ExcelOffTheGrid
Жыл бұрын
Thanks for the “” tip. The syntax of all those functions feels wrong, so it doesn’t surprise me that there is another method. 👍
@drsteele4749
Жыл бұрын
@@ExcelOffTheGrid It's a mystery that those functions require the quotes and the ampersand as opposed to what should be simple operands. Painful.
@RicardoJimenezCR
Жыл бұрын
Great tip! I would simplify it a bit. In the Forecast Labels and Actual Labels columns you could simply write the texts "Forecast" and "Actual" instead of the max values. And then you wouldn't need to add additional series to the graph, you just need to add the data labels to the current series and point them to those column. Also you can edit the formating of the data labels so that they won't show 0 values (and not needing to add NA()). I enjoyed your video very much, keep it up! Thank you!
@ExcelOffTheGrid
Жыл бұрын
Thanks Ricardo - Let me work through an example with your suggestions, hopefully I can pick up a few tips too. If we just used the Forecast & Actual columns, wouldn't we get data labels for every data point along the line (apart from the blank cells)? And, in terms of the formatting the label, what if the value is 0? I still want the label to display. So I'm not sure that works in all cases.
@JenMayB
Жыл бұрын
@@ExcelOffTheGridi would just use a ghost space after the names actual and forecast for the label column names so that they are different, but not visible when in the chart.
@ExcelOffTheGrid
Жыл бұрын
@@JenMayB That would work, but I still wouldn’t do. It’s solving a presentation layer issue by breaking the semantic nature of column headers in the data layer. I would favour hard coded labels over changing the column names.
@axeemx
Жыл бұрын
Thanks for the tip. But still i wud prefer to add labels to my original line series. That way its more natural. As per my view.
@ExcelOffTheGrid
Жыл бұрын
Provided you have a method to do that dynamically so that you don’t have to keep updating it manually. Then, Yes, I agree.
@DrDamoStrikesBack
Жыл бұрын
Interesting technique - thanks for sharing. As always with XL there are many ways to solve a problem. As an alternative, rather than using extra series, I would calculate the label similarly (using helper columns) but use the "Labels from cells" method of attaching these custom data labels to the existing series. Instead of using your IF() function to calculate a value, it would simply return the name of the series. Cheers!
@ExcelOffTheGrid
Жыл бұрын
Yes, I can see that working too. That’s a good method. 👍
@annemettevejlegardkristens9263
Жыл бұрын
@@ExcelOffTheGrid However - wouldn't that just show the value at every value point instead of just showing Actual (just once) at the end of the line? I tried to re-do your excellent example as I'm not a chart person (yet 😉) and I tried both combos and I thought your solution with a helper column was as great way to keep the chart clean. 💪👍
@ExcelOffTheGrid
Жыл бұрын
@@annemettevejlegardkristens9263 their suggestion is to calculate the Label Text (i.e the words “Actual” or “Forecast” in the helper columns, with the others showing blank). Then use the labels From Cells. So the label only shows against the points we want.
@RonDavidowicz
Жыл бұрын
Nice! but now I’ve got to go back and change all my charts😀
@ExcelOffTheGrid
Жыл бұрын
🤣 - yes, you do!!!!!
@guitars0206
Жыл бұрын
Great tip. If one created a chart starting in PowerPoint, does the chart function in PowerPoint support the formulas you outlined?
@ExcelOffTheGrid
Жыл бұрын
I should, but I've never tried it. Give it a go. Please let us know either way.
Пікірлер: 26