Easiest Way to Create Dynamic Step Charts with Error Bars
HTML-код
- Опубликовано: 2 май 2018
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Use this trick to create a super quick dynamic Step chart in Excel using a scatter plot and error bars (great graph to show price & inventory fluctuations).
⬇️ Download the workbook here: pages.xelplus.com/step-chart-...
Key Highlights:
Data Setup and Initial Charting: Converting data into an Excel table and inserting a scatter plot chart.
Applying Error Bars: Utilizing horizontal and vertical error bars to create step chart effects.
Custom Error Bar Lengths: Techniques to dynamically set error bar lengths based on date differences.
Data Label Optimization: Adding data labels for positive and negative values separately for clarity.
Final Visual Enhancements: Adjusting line thickness and color, and formatting the chart for a polished look.
What is a Step Chart? A Step Chart is great for visualizing variables that change on an irregular basis. For example price and inventory changes. If you create a line chart directly on the data set you will realize it doesn't show the correct visualization. There are different ways to get the right visualization. This videos shows you the easiest version. It uses a little chart trick called error bars.
🌍 My Online Excel Courses ► www.xelplus.com/courses/
Many thanks to Mehdi & to Bart for providing us with different methods of creating the Step Chart.
★ Links to related videos: ★
Alternative version of Step Chart: • How to Make a Step Cha...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
Grab the file I used in the video from here 👉 pages.xelplus.com/step-chart-file
Bravo Medhi and Leila. Beautiful application to errors bars and table (to make the chart dynamic).
I love all your tutorials!!!
🙏Thanks, Stacey!
Excellent one. Thank you
So many cool useful tricks! Thanks a lot for sharing!
You're very welcome Yulin. Thank you for your support.
Thanks Leila for the chart expertise!
You're very welcome Kevin.
Wow ! Just Wow. I had never used Error Bars before this.
Leila, discovered your channel very recently, but super impressed. This is awesome!
Thank you! I'm happy to hear that!
This was brilliant fun to make.
Thanks LG....💞
Awesome and smooth error bar.
Thanks to give us this amazing video without any cost. I respect your important time values 💞
Love the way of ur presentation
Lots of love🌷🌹🌷🌹
Thank you Dev! I love creating the videos too and sharing them with others who also enjoy working with Excel. Many thanks for your support.
Really awesome! Thank you to all three of you!
That's team work! Nothing can beat that :) Great to see you Celia.
Indeed, Leila! Impossible to beat specially when the team is all 5 - stars!
Great to see you too, Leila! In fact I see you every night - I am taking your Dashboards course on Udemy. The VBA one is next in the queue. ;-)
;-) I hope you're enjoying the dashboard course - hopefully it's not giving you any nightmares ;)
Leila Gharani HAHAHA! Not at all, Leila. On the contrary: it makes me very happy to learn all that new stuff.
Excellent presentation of this technique
And not only that!
Leila has masterfully refreshed the technique of creating step chart originating from MVP Tushar Mehta from 20 years ago
Pure beauty in action
This is the right time to use error bars, thanks
Very nice video. Never worked with error bars. I always thought they were only for shares on the stock market. Good to see how you can modify these error bars.
Thanks Bart and thank you for the whole step chart idea. Yes - error bars can really come in handy - in this case for cosmetic purposes rather than statistical analysis :)
Leila, You are simply Awesome....
Glad you like it Samuel :)
This is WOW 😮
Neat example of a step chart using error bars!
Thank you Doug.
You are amazing!
I'm glad you like the video. I'm just the messenger though - Bart & Mehdi were the ones that came up with all the ideas.
I love error bars :) Thank's for the EXCELlent film, Leila!
Thank you Malina! Agree - error bars come in really handy. I wonder how many people use them for their actual purpose.....
Hehe, yeah, ...not me ;)
Thanks Ms. Leila I really appreciate
Thank you! Appreciate your support.
excellent
Amazing
Thanks Leila. That's great, made chart not boring anymore
You're welcome 😊
Very good vídeo!!!!
Thank you for your support!
super!
Wow, I love this! This has inspired me to improve the stacked area charts that I'm currently using to show balance changes over time, as steps would be more suitable.
I have a dollar value recorded for each date within the given range, so I'm thinking the easiest method would be to use a stacked column chart and change the spacing between each column to 0 so that there is no space between each column.
I'm sharing this in case others find it useful, though I'm also interested to know if there are other methods to achieve this!
Thanks for your tips, Ashlee!
very good
Awesome!0I am a big fun for the step chart, would u please post a video to how to combine step chart with column chart. I saw someone did it that was a mind blowing.BTW huge thanks for your excel sharing, i watched your first video from the latest one that meaned a lot to me.:)
Bundle of Thanks Mam Nice Topic & Nice Video
You're very welcome Khalid. Thank you for dropping by.
thumbs-up to the both of you..
Thank you Joe! Thanks for your support.
thanks thanks thanks
Cute tutor...
Woww nice.... I have learned a new thing
Glad to hear that!
awesome Thank alot
Thank you & your're very welcome.
Wallahi
I have subscribed almost 100 ( RUclips channels ) But
I love your chart presentation more than any Excel expert
That's really kind! Thank you! I'm very happy to hear you like the chart videos.
Thanks Leila :)
You're very welcome John. Do you use charts in your reports or formulas only?
I do use charts and your videos are very interesting to me, not only to use but to learn more and more. I love excel as you might have noticed already. Mostly I use Formulas, and the more complicated the scenario is, the more fun I get. I have been following Mike for Years and Aladin, Bill Szyisz and many more. Thanks to people like you and Mike we followers get better and better. God Bless you Leila :) :)
As I'v been watcing every video, as civil engineer, usggest me how tp prepare related charts & cost preparation
Good day. thank you very much for your effort. we are waiting to have the downloadable workbook !
Thanks for reminding me! I'm at a seminar today, but I'll upload it right after.
The Excel file is now available here: www.xelplus.com/how-to-create-step-chart-in-excel/ - just scroll down and download the file. Have fun :)
Thank you very much. i highly appreciate it :-)
You're welcome. Thanks for reminding me.
Awesome Leila! Thanks for this useful video. 🥰
Just a tiny comment; the currency is EUR in table, but in step-chart, you select USD instead of EUR.
P.S: I’m in love watching your videos about Excel tips and skills. A huge thanks for your kindness. 👍
Best regards,
Sothearith
Thanks for the feedback Sothearith!
Leila Gharani You’re very welcome!
Thank you. Is there any way to shade the area under the curve?
You're welcome - I have to think about that.....
V. Good like it most.
👍Who noticed the tiny mistake of currency between y axis and the data inside the chart, $ and €?
You got me there :)
I added some cosmetic to your Awesome graph. And I have sent a mail.hope you have received it.
Rather than =IF(DATE how about =MAX(0, to eliminate the negative day counter in the last row
Or, if the last price plotted is the current price: use today() in the formula to calculate the date difference (length of the horizontal error bar).
Can you tell if the Row(PrTable[[#Headers],[Date]]) is always 0? Also can you tell why A12-A11=-43288?
The row formulas gives the row the header is sitting in. If you deduct the previous row, you start from 1. Regarding the huge difference: if you go to cell A11 and change the cell formatting to general, you see 43288. That's how dates look as whole numbers. The value is the number of days since Jan 01 1900. So if we deduct zero from A11, we get -43288. It shows it to us as a number and not a date, because it has -ve in front of it....
my princess...
Have a suggestion for u make a video short length and to the point, u can check MJ tube channel he also make video on excel in short and simple way, don't mind take it as compliment
it is ok some people like this style , so if you have suggestion do your own videos
Hi Leila.. could you please help me with heat map? What is heat map actually ?? And where we need to use it?
Hi Balaji - here you can get a good overview: trumpexcel.com/heat-map-excel/ and here too which is on an actual map: support.office.com/en-us/article/show-a-heat-map-in-3d-maps-be0bad76-3f04-4e9b-8a53-1801fc2b17d9
Leila Gharani wow.. thats cool.. thank you
Does anyone else have a problem with substraction of the dates... I get #Value for some reason. Any ideas?
Thanks!
Does Excel recognize the dates as real dates? If you go to home, and from the number field drop down, do you see date as the number formatting option?
Thank you Leila! Yes I can see a few date options. Yet, there is date, short date, long date. Regardless of which one I pick it does not get the substraction!
Keep up with the good work!
If it's recognized as dates, you should be able to subtract.... that's strange that you get #value - you might get a date back which you can format as general and then see the number.... what if you write =today() in one cell and then in the next one write =today()+2 - in another cell subtract these 2 cells....?
Yes, I figured it out! My excel recognized dates in the format 16.5.2018. Thanks again!
That's great! Thanks for the feedback.
Instead if ROW's formula, "=if(rows([Date]=(row([@Date])-row(PriceTable[[#Headers],[Date]]),0,A5-[@Date]))" ----------- why cant we use "=IF(A5-[@Date]
Hey there
I was waiting from morning
Happy come back
I will warch frist then continue annoying you 😅😅😅😅
Hi there! This one took a lot longer to edit... Thank for watching & waiting :)
this is very tricky
i appreciate ur time
but it deserves .
that way to show the changes in prices is very practical
i do need it to impress work buddies
it adds to my experience an techniques
how can i thank u
i am in a rush because of q1 - 2018 business review
i will finish it and join u in vba
meanwhile
best rose from me
Thank you. Yes, it's tricky but I agree, it's a nice chart to impress. Most people don't know about error bars...Good luck with business review. Hope all goes well!
thx , jus one favours could u plz go slowly
I love You. You’re so cute.
Your vlogs are difficult.