I absolutely adore you. Lets start off with your voice. I could listen to talk to you for hours. You're very engaging, intelligent and produce amazing videos! PLEASE DON'T STOP!
@The Office Lab I’m an engineer working for a pharmaceutical company in Canada and my boss is going to absolutely gush over the dashboard I designed for operational equipment effectiveness. I’ve spent a few nights watching your videos multiple times. Just so good.
Thank you for sharing all these amazing tutorials. I would give a high compliment that you are not only an Excel expert, but also an Excel artist. I really enjoy watching all your courses. :)))
Geeky comment: Excel...lent Tutorial. Some years ago, I have made for a customer a similar chart but extended to highlight all violations of "western electric" ruleset, very useful for applying SPC over a process. The dynamic range chart though without VBA code is something I was looking for for ages! Thank you!
good evening, thank you very much for the video it's really excellent, here I'm looking for how to do two checks for example normal and pathological on the same graph!!!
Very nice tutorial. I learned a lot about dynamic ranges and how to mark data points in diagrams. I never had the idea of making big points without a fill for markers. Thanks again. But may I ask, after you added more than 20 data points the X-axis annotations didnt extend. Is there a way to make that dynamic too?
Awesome Video again! Wouldn't it be more efficient to use Excel Tables feature for data for charts? Tables can auto-expand and chart ranges will increase as well. Also OFFSET function though awesome, it is a volatile function affecting Workbook performance
Thank you 😊 Regarding your comment, I used a table at first and yeah, you are right. A table is probably the better choice from a performance and effort perspective. The problem I saw was that tables only expand when you add data but don't shrink when you remove a value or row, especially if you only have one input column and all the other columns with formulas that depend on that one column. That's why I decided to use OFFSET instead.
The Office Lab using tables and then using vba to resize the table won't that be better? Reduce the formulas as it's going to be consistent all the way. Another question I have is we can't hide the helper columns used to create the charts .. is there a way not to show the not important data?
Great tutorial! As I am working an an application scientist for an analytical instrument supplier, I immediately got new ideas for my next training classes. However, I saw that the z-axis is not labeled when 20 data points are passed. Is this normal? Did I miss something?
Hey Jan, that is great to hear 😊 I wish you all the best with your next classes! Regarding your question, you are absolutely right. I realised that after uploading. I just forgot to include the index data series in the chart area for the labels. That's why it is stuck at 20. But you can easily correct that by creating a named range for the index data series as well, and then just reference that name when you set the horizontal axis labels.
Recently subscribed - thank you - your content is just incredible! I went through this tutorial - and I must have made a mistake somewhere but when i run my restart macro, my chart settings reset to the default settings and colors and I also seem to lose my limit alerts signals. Any idea where I could have gone wrong?
Great to have you on board 😊 Try to go to your Excel Settings > Advanced > Chart. There you have to uncheck the 'Properties follow chart data point options for current workbook' option. That should prevent the reset of the chart (seems like it is activated by default for most people). I demonstrated that in one of my recent videos (starting at around 08:28): ruclips.net/video/qQJSuNh2FRc/видео.html Hope that helps.
Thank you, this tutorial is very helpful. Why does my chart format change after hitting the restart button, it resets to sold line and different colors..? I also get a pop-up saying that there is a problem with one or more formula references
Can you show us how to update the Automated chart controls so that instead of clicking to add random values, clicking the button returns daily actual values? Wanted a way to make the chart looks like it's updating with real data over time.
Thanks for the great tutorial. It seems I have an issue with the offset calculation in the name definition. The count function does not include/count the fields with the error value #N/A. Any idea what could cause this behaviour?
Hi Uwe, thanks for your feedback 😊 If you look closely, I always reference the 'Actual Value' column inside of the COUNT function for each of these OFFSET formulas, exactly for the reason that you mentioned.
How would you alter the trend to have new data reflect a change in target or limits while keeping the old data/limits as they were prior to the change?
Great video and very informative!! Just want to ask, is it possible if we convert the data set into table? That way we can automatically adjust the chart data set as we add values? Your thoughts on this?
how can i make a macro button to save the data to another sheet for real case in the factory because they need the historical data , and thank you for this wonderful tutorial ❤❤
Hello thanks for the tutorrial, i'm not done yet but i'd like to know if in a real scenario the upper critical limit and the lower critical limit should be calculted out of standard devriation (x3)?
Amazing tool and tutorial!! However, the subscription access doesn't work anymore, to access the worksheet download. Does anyone have a copy of the worksheet? Or site subscription access is needed.
This is a great tool! Question, if my actual value is left blank, I get a critical alert showing in red as if it has a zero value. How do you remove it?
Very useful sir What if i want to import masters data to get mis reports like party wise Item wise Month wise and make comparison of all this? Please help ..
Awesome content! Like and subbed. I got a question tho. When entering the new values, the x-axis didn't update automatically. I'll try to apply this to my needs, but the x-axis will contain date values, and i need all of them to be showed.
Hi Victor, sorry for the late reply. I forgot to name and include the x-axis in this video. You just have to name your x-axis range just like I did with all the other data series. After that, open the chart data source window and set the 'Horizontal Labels' to the defined named range. Hope that helps.
Excellent video! I have one question. I would like to do this but with a dinamic table. I mean, the part of the limits and critical values. The thing is when i took the values of the dinamic table is all OK, i can make the chart, but when i want to add the critical points and the limit, in that case i´m having problems. One solution that come trough my mind is to make those colums (critical and limit) before creating the dinamic table, and then with this 3 colums make a dinamic table. Do you think is a good option? Do you have a better advice? Thanks in advance
I got stuck at minute 16.00, when I tried to follow your tutorial by using Microsoft 2010 version. It very useful to my job, any chance can download this control chart?
Hi Tiam 😊 sorry to hear about your issues with Office 2010. You can download the worksheet on excelfind.com. Just take a look at the video description.
it might be useful to have a customer supplied center point value and the actual mean of the current data set drawn on the chart. A second chart below of the Range or % Standard Deviation would be useful. I know this site is for training. Maybe these idea would make for good assignments.
I followed the procedure successfully. Under the warning and critical column, each cell displays right triangle at the left top corner. I could remove it by selecting the "ignore the error". But I have to do it for individual cell sperately. Is there way i could get just clear empty cells like what you have shown in the video? Thanks.
how about declare your data as Table (Format as a Table) and plot that. Table extends automatically as you add data? Is there any downpoint of using this?
What happens when the min and max limits are positive and also what happens when the control limits are not the same eg. +0.5 and -0.3 are the tolerances
How come that you used the awkward method rather than having the data in an excel table? Also, named cells for the target and limits would had made the references easier.
![Blox Fruits Dragon Rework Update [Full Stream]](http://i.ytimg.com/vi/EqDAp8udhm0/mqdefault.jpg)
This Channel is a hidden gem, glad the RUclips algorithm lead me here
Thank you, Lyrous! 😊
Which channel?
@@ericmatenge8563 sorry I intended to write "this channel"
I absolutely adore you. Lets start off with your voice. I could listen to talk to you for hours. You're very engaging, intelligent and produce amazing videos! PLEASE DON'T STOP!
Thank you for these kind words, Keegan 😊 Really appreciate it!
@The Office Lab I’m an engineer working for a pharmaceutical company in Canada and my boss is going to absolutely gush over the dashboard I designed for operational equipment effectiveness. I’ve spent a few nights watching your videos multiple times. Just so good.
That's amazing! I am sure he will be impressed.
This is an Unique Channel for Excel Teaching.
I usually don't comment on videos, but I had to comment on this one. Incredible material you created sir. thank you
Thank you so much, Luiz! 😊
So glad I found the Channel, I have subscribed without thinking twice. Great Technique
Awesome, thank you!
Thank you for sharing all these amazing tutorials. I would give a high compliment that you are not only an Excel expert, but also an Excel artist. I really enjoy watching all your courses. :)))
You are a wizard. Happy to learn from you
Wow this is what i have been looking for, for a very long time! Thank you
This video is pure gold!!! Thank you very much!!!
You make creating excel spreadsheet FUN!!! Thanks for your video. Keep it coming.
Thank you, D.J.! Your feedback is pure motivation for me 😊
This best ever automated control chart video finally. Thanks a lot, I love it!
Thanks, Lois. I am happy you like it!
Another awesome and great video tutorial. Thank you for continuing this office lab!
Glad you think so, Tristan 😊 Thanks for your positive feedback!
Geeky comment: Excel...lent Tutorial. Some years ago, I have made for a customer a similar chart but extended to highlight all violations of "western electric" ruleset, very useful for applying SPC over a process. The dynamic range chart though without VBA code is something I was looking for for ages! Thank you!
😄 Thanks for that geeky comment! It's great to hear that you got some new inspiration with this dynamic chart range technique.
Excellent teaching sir. Thank you very very much.. Language is clear, clean and to the point. Ramakrishnan Vaidyanathan
So nice of you 😊 Thank you very much for your positive feedback!
Fantastic...lots of knowledge gained from this ...thanks allot
My pleasure, Rashed 😊 Glad you like it.
One word: beautiful!
Brilliant. Nicely done with excellent production and presentation. Thank you.
Thanks for your positive feedback, Andy!
Hello, Greetings from Colombia. I love to excel. Thank you very much for the tutorial. Spectacular (use translator).
Thank you very much for your feedback! 😊
Great video!
Thank you, Issac 😊 Glad you enjoyed it
Cool !! I bet if Access got this much love it would get new features too ;p
😄 Thanks TDT Records!
Thank you very much 🙏
Awesome! Really Awesome!
Glad you think so! 😊
This is a great video, which introduced me to some neat Excel features. Thank you for making it! Liked and subscribed!
Thank you!
I have subscribed immediately!
Awesome 😊 Great to have you on board, Michael!
This was extremely helpful, thank you!
Awesome work !!!
Awesome! Learned a lot. Thanks for sharing your expertise.
Great material...
Thank you 😊
Really appreciate this, thanks for sharing your knowledge!
My pleasure! Glad you like it, Michael 😊
An Excellent video to simplify the process. I had a doubt though... couldn't we format the data points as Data Table to keep it dynamic?
Amazing & simple. Thx
Glad you like it 😊
This is amazing! Wow...
Amazing tutorial
Thank You
My pleasure! Glad you like, Luke 😊
Thanks a lot.
Great tutorial....that was so cool
Thanks so much! Glad you enjoyed it 😊
You're the best man. I'm subbing !
Thanks for the sub! 😊
What a beautiful content, i love it
Glad you enjoy it! 😊
Great job Thanks
Thank you for your positive feedback, Bhaijan 😊
This is amazing! Thank you for sharing this
Glad you enjoyed it! 😊
Oh that was so cool to watch
Thank you 😊
awesome technique
Glad you like it!
That was sooo much value! Thank you so much!! One more Sub from my side. Keep that good work
Thank you, Minh 😊 Great to have you on board!
The Office Lab my pleasure!
You explain things in depth and clearly.
I love the style btw, very neat and clean.
This helped me out a lot thanks!
Subbed and liked.
Awesome, thank you!
@@theofficelab Can you help how to compere program language evolution using excel chart please
Again amazing, thank you so much!
My pleasure 😊 Glad you like it
Excellent tutorial. Is there a way to add a selection range for date, so that you display points within a date range?
good evening, thank you very much for the video it's really excellent, here I'm looking for how to do two checks for example normal and pathological on the same graph!!!
Thanks..
My pleasure 😊
Very nice tutorial. I learned a lot about dynamic ranges and how to mark data points in diagrams. I never had the idea of making big points without a fill for markers. Thanks again.
But may I ask, after you added more than 20 data points the X-axis annotations didnt extend. Is there a way to make that dynamic too?
Amazing
thanks for knwoledge sir...
My pleasure, CIENTIES 😊 I am happy you found some value in this video.
AMAZINGGGGGGGGGG
Awesome Video again! Wouldn't it be more efficient to use Excel Tables feature for data for charts? Tables can auto-expand and chart ranges will increase as well. Also OFFSET function though awesome, it is a volatile function affecting Workbook performance
Thank you 😊
Regarding your comment, I used a table at first and yeah, you are right. A table is probably the better choice from a performance and effort perspective.
The problem I saw was that tables only expand when you add data but don't shrink when you remove a value or row, especially if you only have one input column and all the other columns with formulas that depend on that one column. That's why I decided to use OFFSET instead.
The Office Lab using tables and then using vba to resize the table won't that be better?
Reduce the formulas as it's going to be consistent all the way.
Another question I have is we can't hide the helper columns used to create the charts .. is there a way not to show the not important data?
Great tutorial! As I am working an an application scientist for an analytical instrument supplier, I immediately got new ideas for my next training classes.
However, I saw that the z-axis is not labeled when 20 data points are passed. Is this normal? Did I miss something?
Hey Jan, that is great to hear 😊
I wish you all the best with your next classes!
Regarding your question, you are absolutely right. I realised that after uploading. I just forgot to include the index data series in the chart area for the labels. That's why it is stuck at 20.
But you can easily correct that by creating a named range for the index data series as well, and then just reference that name when you set the horizontal axis labels.
whooaa..awesome
Very nice tutorial. Thank You for sharing.
Wouldn't "" value work for the warning and critical values as well?
Recently subscribed - thank you - your content is just incredible! I went through this tutorial - and I must have made a mistake somewhere but when i run my restart macro, my chart settings reset to the default settings and colors and I also seem to lose my limit alerts signals. Any idea where I could have gone wrong?
Great to have you on board 😊 Try to go to your Excel Settings > Advanced > Chart. There you have to uncheck the 'Properties follow chart data point options for current workbook' option. That should prevent the reset of the chart (seems like it is activated by default for most people).
I demonstrated that in one of my recent videos (starting at around 08:28): ruclips.net/video/qQJSuNh2FRc/видео.html
Hope that helps.
@@theofficelab thank you so much. I will try that. You're doing good work 😊
@@TheOneWhoLeftOtAllBehind. Thank you 😊
Very well explained. Can you do this for Google sheets also?
@the Office lab! can there be a way of having the warning zone highlighted ? i mean the background of the warning zone having a different color?
Thank you, this tutorial is very helpful. Why does my chart format change after hitting the restart button, it resets to sold line and different colors..? I also get a pop-up saying that there is a problem with one or more formula references
Very useful, why didn't you use list objects for your data area?
Can you show us how to update the Automated chart controls so that instead of clicking to add random values, clicking the button returns daily actual values? Wanted a way to make the chart looks like it's updating with real data over time.
Thanks for the great tutorial. It seems I have an issue with the offset calculation in the name definition. The count function does not include/count the fields with the error value #N/A. Any idea what could cause this behaviour?
Hi Uwe, thanks for your feedback 😊
If you look closely, I always reference the 'Actual Value' column inside of the COUNT function for each of these OFFSET formulas, exactly for the reason that you mentioned.
How would you alter the trend to have new data reflect a change in target or limits while keeping the old data/limits as they were prior to the change?
Can you help how to compere program language evolution using excel chart please
Great video and very informative!!
Just want to ask, is it possible if we convert the data set into table? That way we can automatically adjust the chart data set as we add values? Your thoughts on this?
15:00 ... the function na() returns #n/a ... not a big deal but makes the formula tidier ...
For updating the chart to include new data that we add, what is the benefit of OFFSET to TABLE? Thanks
how can i make a macro button to save the data to another sheet for real case in the factory because they need the historical data , and thank you for this wonderful tutorial ❤❤
Hello thanks for the tutorrial, i'm not done yet but i'd like to know if in a real scenario the upper critical limit and the lower critical limit should be calculted out of standard devriation (x3)?
Amazing tool and tutorial!! However, the subscription access doesn't work anymore, to access the worksheet download. Does anyone have a copy of the worksheet? Or site subscription access is needed.
This is a great tool! Question, if my actual value is left blank, I get a critical alert showing in red as if it has a zero value. How do you remove it?
Hi Javier, I just responded to your email request .
Thank you so much for the quick response! It worked! I love this chart.
Very useful sir
What if i want to import masters data to get mis reports like party wise
Item wise
Month wise and make comparison of all this?
Please help ..
Awesome content! Like and subbed.
I got a question tho. When entering the new values, the x-axis didn't update automatically. I'll try to apply this to my needs, but the x-axis will contain date values, and i need all of them to be showed.
Hi Victor, sorry for the late reply.
I forgot to name and include the x-axis in this video. You just have to name your x-axis range just like I did with all the other data series. After that, open the chart data source window and set the 'Horizontal Labels' to the defined named range. Hope that helps.
You are late!
Just joking ☺️. You delivered again.
Thanks! 😃
Excellent video! I have one question. I would like to do this but with a dinamic table. I mean, the part of the limits and critical values. The thing is when i took the values of the dinamic table is all OK, i can make the chart, but when i want to add the critical points and the limit, in that case i´m having problems. One solution that come trough my mind is to make those colums (critical and limit) before creating the dinamic table, and then with this 3 colums make a dinamic table. Do you think is a good option? Do you have a better advice? Thanks in advance
Good
Thanks 😊
Hi, Which references do you use?
I got stuck at minute 16.00, when I tried to follow your tutorial by using Microsoft 2010 version. It very useful to my job, any chance can download this control chart?
Hi Tiam 😊 sorry to hear about your issues with Office 2010. You can download the worksheet on excelfind.com. Just take a look at the video description.
can you simulate spring mass mesh in excel sir??please.. thank you
how can i use this tool in the real case of mounting the process ?
it might be useful to have a customer supplied center point value and the actual mean of the current data set drawn on the chart. A second chart below of the Range or % Standard Deviation would be useful. I know this site is for training. Maybe these idea would make for good assignments.
4:17 where did that come from?
I followed the procedure successfully. Under the warning and critical column, each cell displays right triangle at the left top corner. I could remove it by selecting the "ignore the error". But I have to do it for individual cell sperately. Is there way i could get just clear empty cells like what you have shown in the video? Thanks.
how about declare your data as Table (Format as a Table) and plot that. Table extends automatically as you add data? Is there any downpoint of using this?
Tipp #1: Add the Cell Format in Visual Basic in Sub Simulate and Sub Restart: = Format(WorksheetFunction.Norm_Inv(Rnd(), mean, std), "#.#")
How do omitted data 2 sd outside in LJ chart?
What happens when the min and max limits are positive and also what happens when the control limits are not the same eg. +0.5 and -0.3 are the tolerances
How come that you used the awkward method rather than having the data in an excel table?
Also, named cells for the target and limits would had made the references easier.
Why I am not able to to change the series value to actual value,it's showing error, please help
In part 2 I am not able to change the actual value in series value,please somebody reply
I m getting error
Why not use the NA() and ISNA() functions?
Sure, that's also possible. I just wanted to demonstrate that you can use errors in general to 'hide' data points.
I can't program the buttons, it was telling me that an object is required
What I can do?
I genuinely have no idea why this would be necessary. Great videos tho.
what is price
Price for what exactly? 😊
the X axis values of the chart are not dynamic
Don't use excel, have no need for this, will have forgotten when I need it, still interesting though
Thanks, Couchmann941! 😄 Glad you enjoyed it nonetheless.
No VBA plzzzzzz
Not getting password after subricibing
Hello, someone who can pass it to me please.