Upper and Lower Control Limits in Excel

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.

lower and upper limit

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.

limits chart data table

Highlight the data table. Go to the ribbon and the Insert tab. Choose a Line chart.

ribbon line chart limits

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.

basic line chart upper lower

Right-click the first lower limit line and choose Format Data Series from the menu.

Format Data Series limits chart

Change the line color to red and set the width to 5 pts.

format lower limit line

Do the same for the upper limit line. Change the chart title. Your chart with upper and lower control limits is ready.

chart with upper and lower control limits

Further reading:
Chart that Ignores N/A! Errors and Empty Values
Chart with a goal line