In this Excel tutorial, you will learn how to create a chart with upper and lower control limits.
How to set lower and upper limit
First, let's check how to determine the lower (LCL) and upper (UCL) limit.
We have a data table. In our case, it is small. Typically, however, data analysts handle large volumes of data, and it is not so easy to find the smallest and greatest value.
Use the MIN and MAX functions to set lower and upper limits.
The syntax of the MIN and MAX functions
MIN (number1, [number2] ...)
MAX (number1, [number2] ...)
As you can see, in order to determine the smallest or largest value, you should enter a number or a range of data. One argument to the function is enough; the next ones are optional.
In my case, the formulas are:
MIN formula = MIN (A2: A7)
MAX formula = MAX (A2: A7)
Preparing lower limit and upper limit data
In this case, you are given a data table with a designated lower and upper sales limit. Your task is to create a chart with control limits.
Let's begin by preparing a data table.
The lower and upper selling limits have been set manually, which is also acceptable.
Highlight the data table. Go to the ribbon and the Insert tab. Choose a Line chart.
Adding upper and lower control limits in chart
Your chart should look similar to this one. LCL is visible as a orange line and UCL is a grey one.
Right-click the first lower limit line and choose Format Data Series from the menu.
Change the line color to red and set the width to 5 pts.
Do the same for the upper limit line. Change the chart title. Your chart with upper and lower control limits is ready.
Further reading: Chart that Ignores N/A! Errors and Empty Values Chart with a goal line