In Excel, there are limitless ways to highlight and mark only certain data in the data set. There are different ways to do so, as well.

In the following example, we will show different ways to highlight only negative numbers in the range.

Contents

## Highlight using Conditional Formatting

For our example, we will use a simple numbers table with four columns and random numbers from -20 to 20.

To create this table, we used a very useful formula called **RANDBETWEEN**. It has only two parameters: **bottom** and **top**. For our bottom number, we choose -20, and for our top number, we chose 20. So, our formula looks like this:

`=RANDBETWEEN(-20,20)`

Now, all we have to do to highlight only the negative numbers in our data set is select our range, then go to **Home >> Conditional Formatting >> Highlight Cells Rules >> Less Than**:

In the pop-up window that appears, we will input the number 0, since we want to highlight all the numbers that are lower than this one.

We will choose a standard fill- **Light Red Fill with Dark Red Text**, and then click OK.

Our table now looks like this:

## Highlight using Filter

We will add this list of random numbers to **column F** as well. It would look something like this:

If we want to filter only the negative numbers from the list, we can do that by selecting the first row in our column, and then going to **Data >> Sort & Filter >> Filter**.

Once we click on the **Filter**, we will select the dropdown arrow in our column, go to **Number Filters**, and then select **Less Than**.

In the **Custom AutoFilter** pop-up window that appears, we will input **0** next to the “**is less than**” text.

We will click **OK**, and we will see that only the negative numbers are filtered out:

## Highlight using Formula

We can also use a formula to derive the negative numbers in our data set. We will clear the filter in **column F** by going **Data >> Sort & Filter >> Clear**.

We will create a simple formula in **cell E2**:

`=IF(F2<0,F2,"")`

This formula will copy negative numbers from **column F **and leave a blank space if the values in **column F **are higher than **0**. We will copy the formula till the end of our data set in **column F**. Results are as follows:

## Highlight using Formatting

We can also use formatting to help us highlight negative numbers. We select our range (numbers in **column F**), right-click on it, and then select **Format Cells**:

In the pop-up window that appears, we have to make several adjustments.

First, we select **Number** as a **category**. Second, we set decimal places to be 0, to avoid them (we defined our numbers as whole numbers, so there is no need for decimals). Next, we come to the best part. We can choose how we want to highlight our negative numbers.

In this case, we decided to highlight them with **red color** and to put them into parentheses (fourth option on the list).

When we click on **OK**, our range will look like this:

The same result could be achieved by defining this in the **Custom category** in **Formatting**.

To prove that, we will select our range once again, go to **Format Cells**, and then select the **Custom category**.

We will input the following syntax:

`#,##0 ;[Blue](#,##0);- ;`

Now, our negative numbers should be highlighted in blue and should be in parentheses.

When we click **OK**, we will see that this is the case: