Spotting at a glance the missing numbers in a short sequence of one- or two-digit numbers is often easy. However, finding missing values can be a more time-consuming process if a sequence contains tens or hundreds of values or consists of numbers containing more than two digits. When working with this type of sequence, a Microsoft Excel spreadsheet can come in handy. You can find missing numbers in a sequence using Excel's Conditional Formatting feature.
- Skill level:
Other People Are Reading
Enter the sequence in order into a column in an Excel spreadsheet.
Highlight all except the first cell of the sequence. For example, highlight cells A2 through A12 if your sequence is in cells A1 to A12.
Click "Format" and select the "Conditional Formatting" option. A dialogue box titled "Conditional Formatting" appears.
Select "Formula Is" from the drop-down menu on the left side of the dialogue box. Enter the following formula into the field next to the drop-down menu:
Click "Format." A window titled "Format Cells" appears. Select a colour and/or pattern. Click "OK" to return to the "Conditional Formatting" dialogue box.
Click "Add." A new section containing a drop-down menu and blank field appears.
Choose "Formula Is" from the drop-down menu and enter the following formula into the field:
Repeat Step 5. You do not have to select the same colour and/or pattern as you did in Step 5.
Click "OK." The values before and after the missing value should now be highlighted in the colour and/or pattern you selected. For example, if one highlighted cell contains the value "3216" and the other "3218," the missing value is 3217.