This Channel is a hidden gem, glad the KZbin algorithm lead me here
@theofficelab4 жыл бұрын
Thank you, Lyrous! 😊
@ericmatenge85633 жыл бұрын
Which channel?
@bjornotto983 жыл бұрын
@@ericmatenge8563 sorry I intended to write "this channel"
@0pal234 жыл бұрын
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!
@theofficelab4 жыл бұрын
Thank you for these kind words, Keegan 😊 Really appreciate it!
@0pal234 жыл бұрын
@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.
@theofficelab4 жыл бұрын
That's amazing! I am sure he will be impressed.
@LuizCarvalhonnf4 жыл бұрын
I usually don't comment on videos, but I had to comment on this one. Incredible material you created sir. thank you
@theofficelab4 жыл бұрын
Thank you so much, Luiz! 😊
@nickial2683 жыл бұрын
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. :)))
@PaschalisTsi4 жыл бұрын
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!
@theofficelab4 жыл бұрын
😄 Thanks for that geeky comment! It's great to hear that you got some new inspiration with this dynamic chart range technique.
@tristanludwigcruz9094 жыл бұрын
Another awesome and great video tutorial. Thank you for continuing this office lab!
@theofficelab4 жыл бұрын
Glad you think so, Tristan 😊 Thanks for your positive feedback!
@mdyusoof7863 жыл бұрын
This is an Unique Channel for Excel Teaching.
@AShina-zn7nx3 жыл бұрын
So glad I found the Channel, I have subscribed without thinking twice. Great Technique
@theofficelab3 жыл бұрын
Awesome, thank you!
@loismathius87474 жыл бұрын
This best ever automated control chart video finally. Thanks a lot, I love it!
@theofficelab4 жыл бұрын
Thanks, Lois. I am happy you like it!
@DJKnightrin4 жыл бұрын
You make creating excel spreadsheet FUN!!! Thanks for your video. Keep it coming.
@theofficelab4 жыл бұрын
Thank you, D.J.! Your feedback is pure motivation for me 😊
@waltercomas66493 жыл бұрын
This video is pure gold!!! Thank you very much!!!
@RamakrishnanRukmini4 жыл бұрын
Excellent teaching sir. Thank you very very much.. Language is clear, clean and to the point. Ramakrishnan Vaidyanathan
@theofficelab4 жыл бұрын
So nice of you 😊 Thank you very much for your positive feedback!
@darkmeong50043 жыл бұрын
One word: beautiful!
@baliflorent69713 жыл бұрын
You are a wizard. Happy to learn from you
@karan-aulakh964 жыл бұрын
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?
@andy.puempel4 жыл бұрын
Brilliant. Nicely done with excellent production and presentation. Thank you.
@theofficelab4 жыл бұрын
Thanks for your positive feedback, Andy!
@rashedalnaamani4 жыл бұрын
Fantastic...lots of knowledge gained from this ...thanks allot
@theofficelab4 жыл бұрын
My pleasure, Rashed 😊 Glad you like it.
@otengosupile Жыл бұрын
Wow this is what i have been looking for, for a very long time! Thank you
@josebenjaminsanchez1354 жыл бұрын
Hello, Greetings from Colombia. I love to excel. Thank you very much for the tutorial. Spectacular (use translator).
@theofficelab4 жыл бұрын
Thank you very much for your feedback! 😊
@michaelb17854 жыл бұрын
Really appreciate this, thanks for sharing your knowledge!
@theofficelab4 жыл бұрын
My pleasure! Glad you like it, Michael 😊
@la3ar2142 жыл бұрын
Thank you!
@bugsyschannel61973 жыл бұрын
Awesome! Learned a lot. Thanks for sharing your expertise.
@ianvching16479 ай бұрын
Thank you very much 🙏
@kevintroxell75713 жыл бұрын
This was extremely helpful, thank you!
@lukev7304 жыл бұрын
Amazing tutorial Thank You
@theofficelab4 жыл бұрын
My pleasure! Glad you like, Luke 😊
@mohammedelmachkour46074 жыл бұрын
Amazing & simple. Thx
@theofficelab4 жыл бұрын
Glad you like it 😊
@fityes17 ай бұрын
Amazing work, many thanks!!
@JC-te1zz2 жыл бұрын
This is a great video, which introduced me to some neat Excel features. Thank you for making it! Liked and subscribed!
@kinkyAC4 жыл бұрын
thank you very much, it's amazing
@sleakismth34204 жыл бұрын
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.
@theofficelab4 жыл бұрын
Awesome, thank you!
@jnhnmk3 жыл бұрын
@@theofficelab Can you help how to compere program language evolution using excel chart please
@bithibithi49892 жыл бұрын
Thanks a lot.
@ExactProBi4 жыл бұрын
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
@theofficelab4 жыл бұрын
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.
@akkintouch4 жыл бұрын
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?
@tdtrecordsmusic3 жыл бұрын
Cool !! I bet if Access got this much love it would get new features too ;p
@theofficelab3 жыл бұрын
😄 Thanks TDT Records!
@belkadiredouane27342 жыл бұрын
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!!!
@jenrirajagukguk82024 жыл бұрын
Great tutorial....that was so cool
@theofficelab4 жыл бұрын
Thanks so much! Glad you enjoyed it 😊
@johnstath96663 ай бұрын
Excellent tutorial. Is there a way to add a selection range for date, so that you display points within a date range?
@office4u7914 жыл бұрын
awesome technique
@theofficelab4 жыл бұрын
Glad you like it!
@supplychainmysteries76434 жыл бұрын
Great video!
@theofficelab4 жыл бұрын
Thank you, Issac 😊 Glad you enjoyed it
@tylerharb34832 жыл бұрын
This is amazing! Wow...
@WeisSchwarz4 жыл бұрын
You're the best man. I'm subbing !
@theofficelab4 жыл бұрын
Thanks for the sub! 😊
@KaynMoony3 жыл бұрын
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?
@oluwaseunogunsanya3554 жыл бұрын
Great material...
@theofficelab4 жыл бұрын
Thank you 😊
@DavidTran233 жыл бұрын
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.
@vembritalk4 жыл бұрын
thanks for knwoledge sir...
@theofficelab4 жыл бұрын
My pleasure, CIENTIES 😊 I am happy you found some value in this video.
@kiliev20074 жыл бұрын
Again amazing, thank you so much!
@theofficelab4 жыл бұрын
My pleasure 😊 Glad you like it
@bhaijankhan1314 жыл бұрын
Great job Thanks
@theofficelab4 жыл бұрын
Thank you for your positive feedback, Bhaijan 😊
@JanStelling4 жыл бұрын
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?
@theofficelab4 жыл бұрын
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.
@maurolimaok4 жыл бұрын
Awesome! Really Awesome!
@theofficelab4 жыл бұрын
Glad you think so! 😊
@jgojiz4 жыл бұрын
Oh that was so cool to watch
@theofficelab4 жыл бұрын
Thank you 😊
@boazmambrasar58704 жыл бұрын
What a beautiful content, i love it
@theofficelab4 жыл бұрын
Glad you enjoy it! 😊
@mohmmadabdo58994 жыл бұрын
Thanks..
@MinhTran-vl4vz4 жыл бұрын
That was sooo much value! Thank you so much!! One more Sub from my side. Keep that good work
@theofficelab4 жыл бұрын
Thank you, Minh 😊 Great to have you on board!
@MinhTran-vl4vz4 жыл бұрын
The Office Lab my pleasure!
@michaelb17854 жыл бұрын
I have subscribed immediately!
@theofficelab4 жыл бұрын
Awesome 😊 Great to have you on board, Michael!
@febbyjoseph3 жыл бұрын
Awesome work !!!
@pmrich70353 ай бұрын
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?
@omarfarid9292Ай бұрын
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 ❤❤
@jnhnmk3 жыл бұрын
Can you help how to compere program language evolution using excel chart please
@Thechristykiki2 жыл бұрын
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
@sharadtaparia4 жыл бұрын
Very well explained. Can you do this for Google sheets also?
@stevennye50754 жыл бұрын
Very useful, why didn't you use list objects for your data area?
@NaurisKrumins4 жыл бұрын
Very nice tutorial. Thank You for sharing. Wouldn't "" value work for the warning and critical values as well?
@TheOneWhoLeftOtAllBehind.4 жыл бұрын
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?
@theofficelab4 жыл бұрын
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): kzbin.info/www/bejne/p4KthKiEnZd5iMU Hope that helps.
@TheOneWhoLeftOtAllBehind.4 жыл бұрын
@@theofficelab thank you so much. I will try that. You're doing good work 😊
@theofficelab4 жыл бұрын
@@TheOneWhoLeftOtAllBehind. Thank you 😊
@antoniosemakalu6777 Жыл бұрын
@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?
@jamc6664 жыл бұрын
15:00 ... the function na() returns #n/a ... not a big deal but makes the formula tidier ...
@ardavan12582 жыл бұрын
For updating the chart to include new data that we add, what is the benefit of OFFSET to TABLE? Thanks
@Lyriks_ Жыл бұрын
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)?
@uweschneider92044 жыл бұрын
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?
@theofficelab4 жыл бұрын
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.
@Captaan_Ahab3 жыл бұрын
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.
@RHH10954 жыл бұрын
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.
@mishras.k.289611 ай бұрын
Amazing
@jaimahavirhometexindia95824 жыл бұрын
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 ..
@IDSukmanAn3 жыл бұрын
AMAZINGGGGGGGGGG
@jasonlim78994 жыл бұрын
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?
@stivenhuertascardenas71923 жыл бұрын
Hi, Which references do you use?
@omarfarid929215 күн бұрын
how can i use this tool in the real case of mounting the process ?
@tiamhokwee74944 жыл бұрын
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?
@theofficelab4 жыл бұрын
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.
@barneshudson4 жыл бұрын
Tipp #1: Add the Cell Format in Visual Basic in Sub Simulate and Sub Restart: = Format(WorksheetFunction.Norm_Inv(Rnd(), mean, std), "#.#")
@jhanolaer82863 жыл бұрын
can you simulate spring mass mesh in excel sir??please.. thank you
@bizcochodulce4 жыл бұрын
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
@RamakrishnanRukmini4 жыл бұрын
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.
@leksdic4 жыл бұрын
You are late! Just joking ☺️. You delivered again.
@theofficelab4 жыл бұрын
Thanks! 😃
@savvy96472 жыл бұрын
whooaa..awesome
@akkintouch4 жыл бұрын
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
@VictorHenrique_984 жыл бұрын
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.
@theofficelab4 жыл бұрын
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.
@nirupamroy33563 ай бұрын
How do omitted data 2 sd outside in LJ chart?
@javierquispe67503 жыл бұрын
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?
@theofficelab3 жыл бұрын
Hi Javier, I just responded to your email request .
@javierquispe67503 жыл бұрын
Thank you so much for the quick response! It worked! I love this chart.
@glatocha4 жыл бұрын
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?
@jamesmasters42553 жыл бұрын
4:17 where did that come from?
@LarsHHoog4 жыл бұрын
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.
@topnewsprovider.84064 жыл бұрын
Good
@theofficelab4 жыл бұрын
Thanks 😊
@tooshlong3 жыл бұрын
I genuinely have no idea why this would be necessary. Great videos tho.
@ankitseth1004 жыл бұрын
Why I am not able to to change the series value to actual value,it's showing error, please help
@ankitseth1004 жыл бұрын
In part 2 I am not able to change the actual value in series value,please somebody reply
@ankitseth1004 жыл бұрын
I m getting error
@nicadi20054 жыл бұрын
Why not use the NA() and ISNA() functions?
@theofficelab4 жыл бұрын
Sure, that's also possible. I just wanted to demonstrate that you can use errors in general to 'hide' data points.
@nazarethlazo55314 жыл бұрын
I can't program the buttons, it was telling me that an object is required What I can do?
@jasd79492 жыл бұрын
the X axis values of the chart are not dynamic
@sanjeevsingla70364 жыл бұрын
what is price
@theofficelab4 жыл бұрын
Price for what exactly? 😊
@martinmathew35544 жыл бұрын
No VBA plzzzzzz
@soumyadey43644 жыл бұрын
Not getting password after subricibing
@couch94164 жыл бұрын
Don't use excel, have no need for this, will have forgotten when I need it, still interesting though
@theofficelab4 жыл бұрын
Thanks, Couchmann941! 😄 Glad you enjoyed it nonetheless.