Automated Control Chart in Excel

Share on pinterest
Pinterest
Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn
Share on reddit
Reddit
Share on whatsapp
WhatsApp
Share on email
Email
Share on pinterest
Share on facebook
Share on twitter
Share on linkedin
Share on reddit
Share on whatsapp
Share on email

Bottom Line: Learn step-by-step how to create an automated control chart in Excel with a data simulation built on top. This chart automatically highlights deviations in your chart based on chosen control parameters. With a dynamic chart range and a data simulation module, the possibilities with this chart are endless.

Skill Level: Advanced

Video Tutorial

If you are working with time series in Excel and want to make sure that all values are within a defined span, an automated control chart helps you to keep track of any small or critical deviation. 

This advanced data visualisation technique allows the viewer to monitor time series data and automatically detect deviations. With a dynamic chart range, you can add as many data points to the existing data set and the chart range will automatically expand and display the new data. In this tutorial, you will also learn how to create a basic data simulation module with Excel VBA. Based on a given mean and standard deviation, you can generate new random data from a normal distribution and add them to your data set.

Automated Control Chart in Excel - Final Result

The tutorial is divided into three major parts:

Table of Contents

Automated Control Chart in Excel

Start with a static data set

When you build the automated control chart, it makes sense to start with a simple, static dataset of around 20 data points. 

The first thing to do is creating a simple line chart and format in an appealing way.

Automated Control Chart in Excel - Basic Chart

It makes sense to remove the gridlines, as we will add dynamic limit lines to represent the allowed span of values before a deviation is highlighted.

Set up the control parameter section

To create a dynamic span of allowed values, you need to define three control parameters:

  • Target Value: The optimum value of your data series
  • Warning Deviation Limit: Max. upper and lower deviation before a warning alert
  • Critical Deviation Limit: Max. upper and lower deviation before a critical alert

Based on these three values, you can then set up four additional data series that will represent the upper and lower limits in your chart. The formula for these limits is simple: Just calculate Target Value ± Deviation Limit

Make sure to reference your control parameter cells with absolute cell references, so that you can use autocomplete to add the limit values for each respective actual value in your data set.

Automated Control Chart in Excel - Control Parameters

Add and format the limit lines in the chart

Click on your chart and expand the chart source data area to include the four new data series into the chart. Once they are included, you can format them in a style you prefer. I recommend to use a dashed line format to reduce their visual dominance.

Automated Control Chart in Excel - Limit Lines in Chart

Add alert data series and format them

Now, you have to take care of the crucial part of this chart. The two data series that are responsible for the highlighting alerts in the chart. You have to create two data series, one for the Warning Alerts and one for the Critical Alerts.

Start with the Critical Alerts data series. The formulas the following syntax:

= IF(ABS(Actual_ValueTarget_Value)>Limit, Actual_Value, 0)

For the Warning Alert series you then use the exact same syntax (of course with the warning alert limit), but you also have to check if the Critical Alert value is 0.0, because only then a Warning Alert can be thrown. Otherwise, all Critical Alerts would also be Warning alerts automatically. To check multiple conditions, you can use the AND function.

For the first setup, you should use 0.0 as the value for all cells, that don’t meet the criteria of an alert. The reason for that is, that you can see and format the whole data series in the chart once you included it into the source data of the chart. The chart will look like this:

Automated Control Chart in Excel - Alert Data Series

 

To format both alert data series in the best possible way, follow these steps:

  • Add Markers for the data points, make them circular with a size of 16 pt
  • Colorise the markers orange (or red for the critical) and add a Glow effect in the same color with a size of 10 pt and 75% transparency.
  • Set the line to No Line to remove it
  • Replace the 0.0 value with an #N/A error value in the formulas in order to remove all the bottom data points
If you did everything right, the data series and chart will look like this:
Automated Control Chart in Excel - Final Alert Data Series

Dynamic chart range in Excel

Set up a dynamic data range

Before we create a dynamic chart range, we have to make the data range dynamic. We want to make all data series dependent on the Actual Value data series. Only if a row has a value in the Actual Value data series, the other data series should be computed, otherwise, we want them to be empty. For this, we can use a simple IF statement with the following syntax:

= IF(Actual Value Cell “”, “”, Formula)

Add this statement to all data series and drag it for a range of 1000 rows (or as many as you want) and the data range will be dynamic.

Set up named ranges with OFFSET

To reference the data range dynamically, we need to define a named range for each data series. For this, open the Name Manager in the Formulas Tab and create a new name. To dynamically reference only these cells that have values in them, you can use the powerful OFFSET function combined with the COUNT function with the following syntax:

= OFFSET(First Cell in Range,,,COUNT(Potential Range))

The COUNT function counts the numbers of non-empty cells starting from the first cell in the range. The returned value is then passed to the OFFSET function and is used to set the height of the returned range. That way, the OFFSET function will always return the exact range with data in it. 

Automated Control Chart in Excel - Dynamic Data Series Ranges

Once you have set up a name with syntax for each data series, open the Source Data Field of the Chart and replace the absolute references for each data series with the defined named ranges. This way, the chart will automatically include new cells as soon as you add new data into them. 

Automated Control Chart in Excel - Replace Absolute References

If you have set up everything correctly, your chart will now include every new data point that you enter like this:

Automated Control Chart in Excel - Dynamic Chart Range

Data Simulation Module in Excel

Set up simulation parameter section

For the data simulation, you have to set up two input fields where you can enter the values for the mean and standard deviation of a normal distribution. For a better user experience, I recommend to create a normal distribution chart that instantly visualises the distribution based on these parameters. 

As the next step, create two buttons that we will connect to the macros for the simulation later. One button for the Data Generation and one button to Restart the simulation process.

Automated Control Chart in Excel - Simulation Parameter Section

Define named cells and code VBA macros

As the last preparation step, you should create named cell references for 

  • The header cell of the Actual Value data series: Actual_Value_Header
  • The Mean input field: Simulation_Mean
  • The Standard Deviation input field: Simulation_Std
Based on these named ranges, you can then use the following VBA code to create the two required macros:

Sub Simulate()

 

    With ActiveSheet

 

        mean = .Range(“Simulation_Mean”).Value

        std = .Range(“Simulation_Std”).Value

 

        With .Range(“Actual_Value_Header”)

 

            If .Offset(1, 0).Value = “” Then

                .Offset(1, 0).Value = WorksheetFunction.Norm_Inv(Rnd(), mean, std)

            Else

                .End(xlDown).Offset(1, 0).Value = WorksheetFunction.Norm_Inv(Rnd(), mean, std)

            End If

 

        End With

 

    End With

 

End Sub

 

▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬

 

Sub Restart()

 

    With ActiveSheet

 

        mean = .Range(“Simulation_Mean”).Value

        std = .Range(“Simulation_Std”).Value

 

        first_cell_ref = .Range(“Actual_Value_Header”).Offset(1, 0).Address

        .Range(first_cell_ref & “:C100000”).ClearContents

        .Range(first_cell_ref).Value = WorksheetFunction.Norm_Inv(Rnd(), mean, std)

 

    End With

 

End Sub

Once created, connect both macros to the respective buttons by right-clicking and clicking on assign, and everything is set up perfectly for a clean simulation of new data points.

Automated Control Chart in Excel - Final Result

Conclusion

The automated control chart is a powerful and advanced chart technique that you can apply to any time series that has to stay in a defined span. For an instant insight into all techniques covered in this tutorial, download the worksheet from our Download Area.

You may also like the following Excel tutorials:

Share on pinterest
Pinterest
Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn
Share on reddit
Reddit
Share on whatsapp
WhatsApp
Share on email
Email
Share on pinterest
Share on facebook
Share on twitter
Share on linkedin
Share on reddit
Share on whatsapp
Share on email