Working with large volumes of data is one of Excel’s many utilities. The book format combines two dimensions of a sheet with a third dimension, which provides the ability to use several in one document and perform operations on data on them. It is not for nothing that the computing capacities provided by the first applications of this type were the main direct way for computers to enter offices already in the 1980s.

From the days of VisiCalc and SuperCalc or earlier Lotus 1-2-3 references to Microsoft Excel, these applications have undergone sensational development both in functions and in the amount of data they are able to generate. For example, I still remember the limitation to 256 columns in the first versions of Excel I worked with, a few years ago. Now, for the vast majority of needs, the performance of tables is more than sufficient.

Now, although Excel is perfectly capable of managing such volumes of data, there are tasks that are still just as complex, and generally apply those attributable to the user. And it’s because checking data, something that can sometimes only be done manually, can be complex and tedious, leaving plenty of room for error. Of course, we can use automatic sorting systems, but in tables with several columns, we can prefer to keep the original order and visually identify the values ​​we are looking for in each of the columns.

In these circumstances, it would be most practical to have some function that would give us some visual element to help us identify the values ​​we’re looking for, wouldn’t it? The truth is that this feature exists, it’s called conditional formatting and as its name suggests, applies one format or another to cells, depending on their contents with respect to the rest of the range to which said format is applied.

Excel conditional formatting, step by step

For a better understanding, let’s show it with an example. Imagine having a list of a year’s worth of sales for a quarter of a long list of sales representatives. And on the right, the total for the year of each of them. And what you want is to identify at a glance the sellers who sold the most in each quarter, and of course also those who achieved a larger turnover in total for the year. A sheet can be something like this:

With only 28 lines shown in the image, this already seems like a bit of a tedious task, but imagine that the list is still further down… which is the case. Identifying the highest (or lowest) numbers in each column can be tedious. But with Excel’s conditional formatting, you’ll be able to identify them in no time and with no room for error.

The first thing you need to do is select the cells with the values ​​from the first column. It’s important that you don’t select every cell in every column because you only want to compare the values ​​of individual sellers for that quarter:

Once all the values ​​are selected, make sure you have the Home tab selected on the top ribbon and look for the Conditional Format button. Then click on it to display the menu

As you can see, this tool offers a huge set of options along with the flexibility of being able to create your own conditional formats. However, for this first contact with the function, we will stick with pre-designed formats that are perfectly useful for what we want.

The formats grouped in the higher or lower values ​​rules (the second menu item) allow you, as their name suggests, to highlight the highest or lowest values. You can do this by choosing a fixed number or percentage of them. As you can see, the four items in this regard offer you “10” and “10%”. If it fits what you are looking for, perfect, choose the one you prefer. However, if you want to adjust the number (or percentage), click on “More rules” and use the window that appears for said customization

Once you’ve selected and confirmed the conditional formatting you want to apply, the number of values ​​you’ve chosen will be highlighted in the column

Keep in mind, and this is very important, that conditional formatting is dynamic, so if you edit a cell value, any corresponding changes are automatically made.

As you may have seen when exploring the feature, conditional formatting gives you multiple styles, so I encourage you to explore them until you find the one that best suits what you’re looking for. In the following image, you can see five different styles of conditional formatting, among which you will find those found in Excel. In row 2 below the quarter and above the data you can see the name of each one. And as you can see, there are many different options

If you like Excel tricks, remember that we also recommend these three to make your life easier, and that we also told you how to calculate the rule of three inversely, as well as how to calculate different types of percentages.

More information about Excel: Microsoft