Automated Control Chart in Excel (with built-in Data Simulation)

[@bjornotto98]

This Channel is a hidden gem, glad the KZbin algorithm lead me here

[@theofficelab]

Thank you, Lyrous! 😊

[@ericmatenge8563]

Which channel?

[@bjornotto98]

@@ericmatenge8563 sorry I intended to write "this channel"

[@0pal23]

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!

[@theofficelab]

Thank you for these kind words, Keegan 😊 Really appreciate it!

[@0pal23]

@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.

[@theofficelab]

That's amazing! I am sure he will be impressed.

[@mdyusoof786]

This is an Unique Channel for Excel Teaching.

[@LuizCarvalhonnf]

I usually don't comment on videos, but I had to comment on this one. Incredible material you created sir. thank you

[@theofficelab]

Thank you so much, Luiz! 😊

[@nickial268]

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. :)))

[@PaschalisTsi]

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!

[@theofficelab]

😄 Thanks for that geeky comment! It's great to hear that you got some new inspiration with this dynamic chart range technique.

[@baliflorent6971]

You are a wizard. Happy to learn from you

[@waltercomas6649]

This video is pure gold!!! Thank you very much!!!

[@AShina-zn7nx]

So glad I found the Channel, I have subscribed without thinking twice. Great Technique

[@theofficelab]

Awesome, thank you!

[@tristanludwigcruz909]

Another awesome and great video tutorial. Thank you for continuing this office lab!

[@theofficelab]

Glad you think so, Tristan 😊 Thanks for your positive feedback!

[@loismathius8747]

This best ever automated control chart video finally. Thanks a lot, I love it!

[@theofficelab]

Thanks, Lois. I am happy you like it!

[@DJKnightrin]

You make creating excel spreadsheet FUN!!! Thanks for your video. Keep it coming.

[@theofficelab]

Thank you, D.J.! Your feedback is pure motivation for me 😊

[@darkmeong5004]

One word: beautiful!

[@otengosupile]

Wow this is what i have been looking for, for a very long time! Thank you

[@karan-aulakh96]

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?

[@RamakrishnanRukmini]

Excellent teaching sir. Thank you very very much.. Language is clear, clean and to the point. Ramakrishnan Vaidyanathan

[@theofficelab]

So nice of you 😊 Thank you very much for your positive feedback!

[@andy.puempel]

Brilliant. Nicely done with excellent production and presentation. Thank you.

[@theofficelab]

Thanks for your positive feedback, Andy!

[@rashedalnaamani]

Fantastic...lots of knowledge gained from this ...thanks allot

[@theofficelab]

My pleasure, Rashed 😊 Glad you like it.

[@johnstath9666]

Excellent tutorial. Is there a way to add a selection range for date, so that you display points within a date range?

[@JC-te1zz]

This is a great video, which introduced me to some neat Excel features. Thank you for making it! Liked and subscribed!

[@KaynMoony]

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?

[@kevintroxell7571]

This was extremely helpful, thank you!

[@tdtrecordsmusic]

Cool !! I bet if Access got this much love it would get new features too ;p

[@theofficelab]

😄 Thanks TDT Records!

[@bugsyschannel6197]

Awesome! Learned a lot. Thanks for sharing your expertise.

[@michaelb1785]

Really appreciate this, thanks for sharing your knowledge!

[@theofficelab]

My pleasure! Glad you like it, Michael 😊

[@josebenjaminsanchez135]

Hello, Greetings from Colombia. I love to excel. Thank you very much for the tutorial. Spectacular (use translator).

[@theofficelab]

Thank you very much for your feedback! 😊

[@JanStelling]

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?

[@theofficelab]

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.

[@sleakismth3420]

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.

[@theofficelab]

Awesome, thank you!

[@jnhnmk]

@@theofficelab Can you help how to compere program language evolution using excel chart please

[@WeisSchwarz]

You're the best man. I'm subbing !

[@theofficelab]

Thanks for the sub! 😊

[@supplychainmysteries7643]

Great video!

[@theofficelab]

Thank you, Issac 😊 Glad you enjoyed it

[@jgojiz]

Oh that was so cool to watch

[@theofficelab]

Thank you 😊

[@febbyjoseph]

Awesome work !!!

[@lukev730]

Amazing tutorial Thank You

[@theofficelab]

My pleasure! Glad you like, Luke 😊

[@skulz66]

This is amazing! Thank you for sharing this

[@theofficelab]

Glad you enjoyed it! 😊

[@michaelb1785]

I have subscribed immediately!

[@theofficelab]

Awesome 😊 Great to have you on board, Michael!

[@la3ar214]

Thank you!

[@jnhnmk]

Can you help how to compere program language evolution using excel chart please

[@pmrich7035]

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?

[@ExactProBi]

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

[@theofficelab]

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.

[@akkintouch]

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?

[@mohammedelmachkour4607]

Amazing & simple. Thx

[@theofficelab]

Glad you like it 😊

[@ianvching1647]

Thank you very much 🙏

[@jenrirajagukguk8202]

Great tutorial....that was so cool

[@theofficelab]

Thanks so much! Glad you enjoyed it 😊

[@maurolimaok]

Awesome! Really Awesome!

[@theofficelab]

Glad you think so! 😊

[@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?

[@boazmambrasar5870]

What a beautiful content, i love it

[@theofficelab]

Glad you enjoy it! 😊

[@Thechristykiki]

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

[@tylerharb3483]

This is amazing! Wow...

[@DavidTran23]

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.

[@oluwaseunogunsanya355]

Great material...

[@theofficelab]

Thank you 😊

[@sharadtaparia]

Very well explained. Can you do this for Google sheets also?

[@stevennye5075]

Very useful, why didn't you use list objects for your data area?

[@kiliev2007]

Again amazing, thank you so much!

[@theofficelab]

My pleasure 😊 Glad you like it

[@NaurisKrumins]

Very nice tutorial. Thank You for sharing. Wouldn't "" value work for the warning and critical values as well?

[@ardavan1258]

For updating the chart to include new data that we add, what is the benefit of OFFSET to TABLE? Thanks

[@bithibithi4989]

Thanks a lot.

[@MinhTran-vl4vz]

That was sooo much value! Thank you so much!! One more Sub from my side. Keep that good work

[@theofficelab]

Thank you, Minh 😊 Great to have you on board!

[@MinhTran-vl4vz]

The Office Lab my pleasure!

[@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 ❤❤

[@office4u791]

awesome technique

[@theofficelab]

Glad you like it!

[@bhaijankhan131]

Great job Thanks

[@theofficelab]

Thank you for your positive feedback, Bhaijan 😊

[@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)?

[@TheOneWhoLeftOtAllBehind.]

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?

[@theofficelab]

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.]

@@theofficelab thank you so much. I will try that. You're doing good work 😊

[@theofficelab]

@@TheOneWhoLeftOtAllBehind. Thank you 😊

[@belkadiredouane2734]

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!!!

[@Captaan_Ahab]

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.

[@stivenhuertascardenas7192]

Hi, Which references do you use?

[@vembritalk]

thanks for knwoledge sir...

[@theofficelab]

My pleasure, CIENTIES 😊 I am happy you found some value in this video.

[@uweschneider9204]

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?

[@theofficelab]

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.

[@omarfarid9292]

how can i use this tool in the real case of mounting the process ?

[@jasonlim7899]

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?

[@jaimahavirhometexindia9582]

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 ..

[@bizcochodulce]

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

[@RamakrishnanRukmini]

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.

[@glatocha]

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?

[@jhanolaer8286]

can you simulate spring mass mesh in excel sir??please.. thank you

[@samuelmundia320]

Thanks..

[@theofficelab]

My pleasure 😊

[@jamc666]

15:00 ... the function na() returns #n/a ... not a big deal but makes the formula tidier ...

[@javierquispe6750]

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?

[@theofficelab]

Hi Javier, I just responded to your email request .

[@javierquispe6750]

Thank you so much for the quick response! It worked! I love this chart.

[@nirupamroy3356]

How do omitted data 2 sd outside in LJ chart?

[@barneshudson]

Tipp #1: Add the Cell Format in Visual Basic in Sub Simulate and Sub Restart: = Format(WorksheetFunction.Norm_Inv(Rnd(), mean, std), "#.#")

[@mishras.k.2896]

Amazing

[@tiamhokwee7494]

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?

[@theofficelab]

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.

[@leksdic]

You are late! Just joking ☺️. You delivered again.

[@theofficelab]

Thanks! 😃

[@IDSukmanAn]

AMAZINGGGGGGGGGG

[@jamesmasters4255]

4:17 where did that come from?

[@akkintouch]

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_98]

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.

[@theofficelab]

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.

[@savvy9647]

whooaa..awesome

[@RHH1095]

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.

[@LarsHHoog]

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.

[@ankitseth100]

Why I am not able to to change the series value to actual value,it's showing error, please help

[@tooshlong]

I genuinely have no idea why this would be necessary. Great videos tho.

[@topnewsprovider.8406]

Good

[@theofficelab]

Thanks 😊

[@nazarethlazo5531]

I can't program the buttons, it was telling me that an object is required What I can do?

[@ankitseth100]

In part 2 I am not able to change the actual value in series value,please somebody reply

[@ankitseth100]

I m getting error

[@nicadi2005]

Why not use the NA() and ISNA() functions?

[@theofficelab]

Sure, that's also possible. I just wanted to demonstrate that you can use errors in general to 'hide' data points.

[@sanjeevsingla7036]

what is price

[@theofficelab]

Price for what exactly? 😊

[@jasd7949]

the X axis values of the chart are not dynamic

[@couch9416]

Don't use excel, have no need for this, will have forgotten when I need it, still interesting though

[@theofficelab]

Thanks, Couchmann941! 😄 Glad you enjoyed it nonetheless.

[@martinmathew3554]

No VBA plzzzzzz

[@soumyadey4364]

Not getting password after subricibing

[@lauravillegas3193]

Hello, someone who can pass it to me please.