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

[Music] hey guys how's it going welcome back to another advanced excel data visualization class i'm really happy to have you here as this will be a huge tutorial because i'm going to show you how to create an automated control chart in excel with a data simulation built on top now this tutorial is an amazing training to level up your overall excel skills as we cover a broad range of techniques like advanced chart design dynamic chart ranges and data simulation with excel vba all in one tutorial before we dive deeper into this topic don't forget to download the worksheet for this tutorial on excelfind.com your number one excel resource platform out there the first part today will be the creation of this automated control chart down here which is probably the reason why you clicked on this video this chart allows you to define a target value and two deviation limits and as soon as a data point in your data series surpasses these limits they are automatically highlighted either yellow if it surpasses the warning deviation limit or red if the data point passes the critical red line we will build this chart with a static data set at first and then in the second part i show you how to make the chart range dynamic so that as soon as you add a new data point this data point will be included in the chart as the chart range is dynamically expanded and for both these parts no vba is required the only we are we're going to need in this whole worksheet is for the third part which is this amazing data simulation module up here here i'm going to show you how to set up a normal distribution with your parameters of choice and then take this distribution and randomly create new data points from it and add them to your data series so for example we can increase the standard deviation right here and that means the next simulated data points will have a bigger variation and are more likely to surpass the limits if we reduce the standard deviation now the process will be a lot more stable we can also simulate a level shift by changing the mean now all the new data points swing around our upper warning deviation line and we get a lot of warnings right here in case this level shift of our process is something we wanted to have and expected we can simply adjust the control parameters which makes the new data points all fine but now our old data points obviously produce many many warnings and critical alerts if we want to start over again we can just hit the restart button and reset the simulated data set this tutorial has such a high density of hands-on advanced excel knowledge that i recommend to watch the whole tutorial first to understand all these techniques in combination and then re-watch it a second time to apply these techniques step by step for yourself without further ado let's get right into it to create the automated control chart let's start with a basic static data set this data set has 20 data points and at first we insert a simple line chart so that you can see how this data series looks like we're gonna make it a bit bigger and adjust the design to make it look more appealing at first we change the background to this dark blue make the header white a bit bigger and rename it then we set all the labels to white as well and for this shot in particular i recommend to remove these grid lines as we're going to add our own limit lines here in a second and to make our data series look more beautiful we change the color to white make it a smooth line and add some simple white markers that's it for this basic chart design now we're gonna set up the control parameter section oh there it is that was fast my gosh let's see what we have here so far only the labels and descriptions for these fields are set up now we have three input fields right here the first one is for the target value so this is the optimum value that we want to have and then the second one is for defining the upper and lower limit before we get a warning alert and then the same for the upper and lower critical limit so let's enter some values here and set up these fields for the target volume let's take a closer look at our data series assuming that it is at least on the correct level seems like it is swinging around the value 6 so let's also take that as our target value for now then we define the warning deviation limit as 2 and 2 here means that the data series is allowed to deviate from the target value no more than 2 either upwards so the upper limit is 8 or downwards so the lower limit is 4. and to surpass the critical deviation limit let's take 4 as the tolerance value here and this limit is surpassed if the data series goes higher than 6 plus 4 which equals 10 or lower than 6 minus 4 which equals 2. since these two deviation limit values represent the absolute deviation so the allowed positive and negative deviation at the same time it makes sense to change the numbers format to a custom format that has a plus minus symbol here we got one in the symbol section let's copy that and use it to create our own custom numbers format so we put it in there and up there we see how this will look for a sample value beautiful now every time we change it it will stay in this format for the sake of completeness we format the target volume cell in a similar style but without the additional symbol just like this now that we have set up the section let's bring these control parameters into our chart for that we will add 4 additional data series one for each deviation limit starting with a lower critical limit then the lower warning limit the upper warning limit and the upper critical limit for the lower control limit we insert the formula that i already mentioned so we reference the target value and subtract the critical deviation limit we want to have absolute references here so we need two dollar signs for each cell reference the shortcut for this is f4 then we copy this over to the lower warning limit data series and change the second reference to the warning deviation limit parameter for the upper warning limit we simply replace the minus symbol with a plus symbol and for the upper critical limit we change the reference back to the critical deviation limit parameter up there and now since all references here are absolute references we can click here to autocomplete all data series and the values will stay the same within each data series pretty straightforward actually now that these additional data series are set up we can click on our chart and expand the chart data range to include these additional data series and there they are right in our chart the design of these lines now should on the one hand reflect the type of limit displayed so let's make this upper critical limit line red the other hand we don't want these lines to be too dominant so let's make it a dashed line to set the focus on our actual data series i see we also have some markers for these lines let's quickly remove them for all the lines and now we can proceed with the upper warning deviation line which we will colorize orange and then for the lower deviation limits we use the same respective style and colors beautiful now we take care of the key feature of this control chart which is the automatic highlighting of every data point that surpasses one of these limit lines and for this we need two additional data series let's put them under the header limit alerts one data series will be for the warnings and one for the critical alerts the big question is what do we put in here because we need something that only outputs relevant values if the actual value surpasses these deviation limits and we have to keep in mind that if a data point surpasses the orange warning limit line it isn't necessarily a warning because if it also surpasses the critical red line we want a critical alert to be displayed and not a warning that's why we have to start with defining the critical alert data series first and then depending on these values we can then define the warning alerts for the critical alert formula we use the if function and check if the absolute value of the difference between the actual and target value is bigger than the critical deviation limit by using the abs function we make sure that both negative and positive deviations are covered at the same time now if we have an absolute deviation that is bigger than this defined critical deviation limit we want this cell to simply display the actual value of this data point otherwise we set it to 0 for now we're going to change that 0 into something else later but for the first setup we need a zero right here if we then use the autocomplete feature to fill the whole data series you see now all values are zero except for the one data point that surpasses the critical deviation limit with a value of 11. that's perfect now we can define the formula for the warning alert data series and in this if statement we now need two logical tests so let's use the and function to make sure that multiple conditions have to be met at the same time the first logical test checks what the value of the critical alert data series is because if we already had a critical alert here we cannot have a warning alert at the same time so only if this one equals zero a warning alert could be produced the second condition now is similar to what we already had in the critical alert formula we simply check if the absolute difference between the actual and the target value is bigger than our warning deviation limit this time if that is the case we want the actual value to be displayed here and if not we put a zero in here perfect as you see we now have a lot of zeros in here and the only data points that are not zero are these that are between the warning and the critical deviation limit and now it's time to include these two data series into our chart they are added as simple line charts and that's totally fine because this is the chart type we need them to be now to transform this into the automated control chart we need to apply some formatting to these data series yeah let's start with the warning alert line and for that we open the formatting area the key for our final design where only the deviating data points are highlighted is the use of markers so we go to the marker options here and set it to build in because that allows us to customize these markers regarding their type and size we use round markers and increase the size to something like around you know 16 should be good and then we set it to no fill and change the border into the solid line with this orange color and a line width of 2.5 to increase the highlighting effect we add a glow effect here set it to the same color with a size of let's take 10 and a high transparency value of 75 you see at the moment we have many elements in this data series that we actually want to get rid of which are of course the lines we don't need them and the zero value data points to get rid of these lines the simple solution is to set the line to no line easy so now these wanted highlighting data points up here already look beautiful and we only have to remove these data points at the bottom which we will do in a second first we apply the same style formatting to the critical alert data series so we go through the same steps here set the marker options to build in with a circle and a size of 16 again no fill and this time a solid line with the color red and a line width of 2.5 again then for the clove effect we use red for now and again set the transparency to 75 percent last but not least we set the line of this line chart to no line and there we go the upper area is now perfectly set up we only have to remove the bottom zero value data points the solution for not displaying certain data points in any chart is you might already know it setting these data points not to zero but to an error value if a data point has an error value it will be left out in the chart because there is nothing that could be displayed so all we have to do now is to adjust the formulas of both data series and replace the zero with an explicit error value i personally always use the n a arrow which stands for not available we hit enter and instantly see the first data point has been removed successfully so let's do autocomplete to apply these changes to the whole series and now all critical bottom values are gone for our chart beautiful we kind of messed up the warning alert series though because now we have a lot of unwanted arrows there but that's easy to fix the reason for this is we currently check if the critical alert data point is a zero and of course if it is an arrow instead this will give us an instant arrow here as well so we have to change the first logical test into the is arrow function applied to the respective critical alert cell and then we also change to zero here to an n a error use autocomplete and there we have our warning alert data points back in here amazing that's the whole trick for this automated control chart now we have a beautiful chart that highlights all deviation values correctly without the need for us to do anything manually as a side note you might ask yourself why didn't we put these error values in the formula right away the answer to this question is this way we wouldn't be able to format the whole data series correctly because we wouldn't have seen it completely and that's the reason why we needed that extra step now to demonstrate you why i call this automated we now can control everything in this chart with these three control parameters up there if we for example increase the warning deviation limit to plus minus 3 you see the orange line moves in the chart and the highlighting alerts adjust automatically if we reduce it to 2 again they highlight everything correctly we could also make this limit more tight and now we have an extra warning popping up at the end of this series and in the same manner we can also move the critical deviation limit lines of course either making them tighter or increase their tolerance value tremendously and suddenly the one critical alert is reduced to become a warning alert as the exact value 11 is not bigger than the target value plus the critical deviation limit ain't that a beautiful data visualization technique in excel i personally really like it let me know in the comments what you think about this technique and in the meantime we just continue with the next step in this tutorial yeah now the chart itself is awesome i guess we all agree on that but it has one big issue if we add new data points down here most of the other values in our data range don't adjust and also our chart doesn't display this new data point because it references a fixed range of data wouldn't it be great to have a dynamic data range for our child so if we add or remove an actual data point the whole data range and chart update accordingly without the need to use any vba let me show you how to do this we start with the data range what we want to have is a data set where we only have to enter values for the one data series right here and make all other series be calculated based on what's in here if there's no actual value in the row we don't want to have any of the other series to have a value in the row but as soon as we add an actual value for the row we want all the other series values to be calculated dynamically we will add a simple formula to the other data series that check what is the current value for the respective cell in this actual data series if it is empty we want the cell to be empty too if not we want the value to be displayed here for the index series here we even need a little extra to make it correctly start with the number 1. now apply this new formula to all rows and it perfectly displays the original values but in a dynamic way and to allow more rows to be added dynamically let's drag this formula down to cover a potential range of 1000 data points for now you see all these cells are empty because there's nothing in the actual value column but as soon as we add another data point here the index is appearing correctly if we remove it it is disappearing correctly beautiful now we apply the same technique to all the data series on the right to also make them dynamically grow and shrink let's add this if formula here and if we autocomplete twice it now also covers the whole range of 1000 potential values now we add it to the rest of these data series and once that's done we select the upper row and use autocomplete to track it down completely so that it covers the range of 1000 potential values and what we have now is a fully dynamic data range that grows and shrinks as we add or remove data points in this actual value column one last adjustment you can do is if you don't want to have all these arrow values displayed here but only the relevant alert cells you can apply conditional formatting that checks if there is an error in the cell as a reference you just have to pass the upper left cell and make sure that there's no dollar sign in this reference so it's a dynamic reference and if that condition is true we want the text color to be white instead of black and now this looks a bit cleaner the next crucial step now is to make the chart range dynamic so that in case our data range grows or shrinks the chart dynamically references the full data range at the moment the chart references the data range with absolute cell references so if we open the data source selection we can see these fixed cell references in here now we need to replace them with something that is more dynamic for any sort of dynamic referencing there is one powerful excel function not many people know about actually but you definitely should know about it because this function is simply amazing if you ask me to quickly demonstrate you how this function works i'm going to use this empty column to dynamically reference the actual value column and put it out here the function i'm talking about is the offset function it has five parameters in total but for our purpose we only need to fill in two of these the first argument is called reference and what you need to put in here is the starting cell for our dynamic range so we select c17 and then we could enter something for the arguments rows and columns to create an offset but actually we don't need that what we do in status we skip these by simply entering commas and jump right to the height parameter whatever number we enter here this will be the height of the range this formula will spit out starting with the first reference that we just inserted so right now the value we need in here would be a 21 so since we want to have this dynamic we don't enter the number directly but instead we use the count function to count the numbers of values that currently are in this column we pass the whole range of our 1000 potential values from c17 to c1016 to this function so in the current state this count will return the value 21 and thus referencing our current range perfectly there it is let's add some additional values and you see the offset formula dynamically references the exact range of available values let's remove this formula from here again because we want to somehow use it in our chart now the clean way to do this is not to directly enter this formula in the charts or stata reference but instead to define a named range with this formula that can then be easily referenced in our chart let's open the name manager that you find in the formulas tab we gonna add a new entry and what's a bit unfortunate is that we need to define a named range for every single data series in here because otherwise it doesn't work the chart can't handle a named range with all data series at once so it is a bit of work but it is necessary we start with the actual value data series range simply call it actual value and down here where it says refers to we enter the offset formula that i just showed you before let's reference this first cell then three commas and for the height we enter the count formula with the range of 1000 potential values after hitting enter there it is to make sure that the reference works correctly what you can do is just click on the formula itself down there and the current reference will be highlighted in the sheet as you see it does exactly what we want to accelerate this process of defining the names for the additional six data series we can simply copy the formula from here and reuse it so to set up the named range for the lower critical limit data series we just insert the copied formula and we can leave the count formula as it is the only thing we need to change is the first reference in here from c to d and if we now hit the ok button you see this new named range now references the correct data series with all its data points dynamically let's repeat that exact same process for the remaining five data series you should always check if you have set it up correctly by clicking into this reference field down there so you see it is not that big of a deal actually but pretty repetitive and we only have to do this multiple times here because we have so many data series in this chart for a more regular chart with only a few data series it will be a lot less work obviously once we are said and done we can now reopen the chart data selection window and now all we have to do is for each data series that is referenced here click on edit the serious name reference can be left as it is we only have to replace the absolute cell reference with the name that we just defined so let's select it and replace it with the name of the respective dynamic range oh i mistakenly wrote values instead of value let's fix that and click ok now we do that for all data series so let's change the lcl reference to the name lcl the lw reference to the main lwl and so on and so on you see this becomes much easier if you name your ranges exactly like the data series title because if you do it that way you don't have to remember anything there we go we now transformed our automated control chart from a static to a fully dynamic chart you see the four additional data points are already included and now if we for example remove these three values here everything adjusts dynamically and if we enter new values they are instantly displayed and the automated control chart does its work so we finished the first two parts of this tutorial and now we are at a stage where you could just enter your data manually or you could also fill in some real-time data here and everything would perfectly work but what's also really interesting and useful is doing simulations for your data to see what results certain process parameters and control parameters would produce and ladies and gentlemen that's exactly what we're gonna do in this third part of the tutorial i'm gonna show you how to set up a fully functional simulation feature that allows you to define and visualize a normal distribution for your process and then randomly generate new data points from this defined distribution and add them to the next empty cell in your actual value column for this process simulation we need a new section in our worksheet and there it is oh man that was quick not much in here so far besides the header and two input fields for a mean and standard deviation value let's enter some sample values for these two parameters that approximate the process our current data represents and now the first thing we want to do for better user experience is to visualize a normal distribution based on these parameters on the right side so that you can see how the distribution looks like that you gotta sample your data points from knowing how to plot a normal distribution from these parameters in excel should be basic knowledge if you do statistical analysis or any sort of simulation so just follow me along we gonna put the required data on a new worksheet that we call process distribution data and in here we need two data series we call the first one x and here we put in the range of all values that we want to have covered so in our case let's put in 0 to 12 because that's approximately the range of our process and we're going to use steps of 0.2 so let's drag that down until we have 12. and the second data series is p and this stands for the probability density for each respective x value to compute this value we use the norm dot dist function in excel where we simply enter the x value the mean value of the distribution for which we're going to use this input field as an absolute cell reference and then also the standard deviation from this input field with an absolute cell reference then we set the last parameter to false to get the probability density function or as it is called in here the probability mass function let's hit enter change the numbers format and use autocomplete to fill the whole data series to visualize these data we then insert a simple line chart and there you see the beautiful normal distribution for the given parameters we add the x data series as the labels and now you can see that our mean value is perfectly where we have the highest probability density in this distribution to improve the visual appearance of this chart i recommend to remove the grid lines recolor the line and then really important make the line smooth now it looks way better okay let's copy the chart over to our primary worksheet and make it fit into this defined section we still need to remove the borderline and fill of the chart area let's adjust this also a little bit and there we go now every change we make to these parameters is instantly visualized in this chart so we see how the distribution actually looks like that we want to generate our new data from beautiful for the actual process data simulation with excel bba we now need to take these two values and the respective normal distribution generate a new data point and find out where is the next empty cell in our data range so that we can add it into this cell like this we will cover this in the first vba macro and then we also gonna create a second vba macro to reset the whole simulation by deleting all data points and randomly generate a new first data point from our distribution that means we will have two separate macros and for a good user experience it makes sense to add one button for each macro we could either insert the standard buttons from the developer tab but if you ask me they look like trash so i try to avoid these as much as possible and most of the times create my own buttons with a simple rounded rectangle shape as the last step before we write the macros we need to define three named cells so that we can easily reference them by name in the vba script the first one is the actual value header cell which is the cell with the name of the data series and the other two are the input cells for the simulation mean and the simulation standard deviation value now we are set and ready to jump right into the vba editor and open the empty script for our main worksheet we call the first macro just simulate and start the code with a with statement to reference the active sheet and inside this with a statement the first thing we do is reading the defined value for the mean from the worksheet so let's write dot range with opening brackets then we pass the name that we just defined for the cell and grab the cell's value and then we do the same for the standard deviation now we have both these values available in here after that we add another with statement to reference the cell with the data series header of the actual value data series because all operations that come now will be based on this cell at first we're going to check where we have to enter the simulated values so what is the next empty cell if the first cell in our data range is empty which is the cell right below the header cell and can be referenced with the offset function then we insert a simulated value from the defined normal distribution into the cell to simulate the value we use the inverse norm distribution function which is called norm underscore inf and this function expects a value between 0 and 1. this is the part where we create the randomness by putting in the rand function r and d function that generates a random value between 0 and 1. and then this norm in function takes this linear random value and maps it to the respective actual x value in our defined normal distribution with a given mean and standard deviation value i honestly don't want to make this a statistics class so i won't go into further detail here but yeah that's how this formula roughly works in case the first cell is already filled with value and maybe some cells below as well we have to find the next empty cell the way we approach this is to find the last cell that has a value at the bottom of the data range we can jump there with the and function and passing xl down as parameter and then we use the offset function to jump to the next cell below that cell which then obviously is the first empty cell in this column and we set the value of this cell by using the same formula that we already used above for randomly generating a new data point that's already the first vba macro and now we only have to assign this macro to the first button so let's close the vba editor right click on the button and assign a macro and now every time we hit that button we generate new data with every single click and if we change these distribution parameters the generated data change accordingly a very low standard deviation the process will be really stable and barely produce any warnings or critical alerts at all and if we set it really high to let's say three this process goes nuts and produces a big amount of warnings and critical alerts the second macro to reset and restart the whole simulation is actually pretty simple again we start with a with statement to reference the active sheet also we read in the mean and standard deviation value from the worksheet at first then we grab the cell address of the first data cell by referencing the header cell using the offset function one cell down and then access the address property after that we're going to use that address to define the range of all potential data and clear the content using the clear content statement so by now the whole data range of actual values should be empty and then we set the first cell's value to a newly simulated data point using the formula from above this is how to start the whole simulation process all over again finally let's assign this vba macro to the second button and now if we hit that button the whole range of data is reset and a new starting value is generated as you see if i press the button multiple times it's always a different value in there and then we can just use the other button to proceed with a new simulation we are now ready and set to play around with the parameters and try out different setups and of course you can just make increase the dimensions of the chart to cover the full width well that's already it we covered a lot of knowledge in this tutorial you learned how to create this automated control chart you learned how to set up a dynamic chart range even for multiple data series in the cleanest possible way and you also learn about creating your own simulation macro and visualization in excel vba to dynamically generate your own random process data i hope you enjoyed it let me know in the comments and visit excelfind.com for the worksheet and many more tremendous excel resources have a beautiful day and see you next time cheers [Music]

Channel: The Office Lab

Views: 116,885

Rating: 4.9707932 out of 5

Keywords: automated control chart in excel, min max line chart in Excel, excel dynamic chart range, excel dynamic chart range multiple data series, the office lab, excel chart design, excel data simulation, excel data simulation VBA, excel line chart design, excel interactive chart, excel 365, excel offset function, excel dynamic chart, excel auto-chart, excel real-time data, excel VBA, excel macros, excel top formulas, excel dynamic formulas, excel dynamic array functions

Id: GAY3Yy1B4qs

Length: 41min 48sec (2508 seconds)

Published: Sat Jul 25 2020