How to apply conditional functions in Excel

  • Conditional functions in Excel allow for flexible and customizable calculations.
  • Conditional formatting helps you visually identify patterns and trends in your data.
  • The IF function is essential for establishing logical comparisons in Excel.
  • There are several conditional formatting options, such as highlighting cells and using data bars.

excel conditional

The functions conditionals in excel They can be a very practical tool when working with the Microsoft spreadsheet program. It can help us, for example, to identify patterns or trends in a spreadsheet in a very visual way.

Excel It allows us to make calculations based on a certain condition that must be fulfilled. This is a handy resource to make calculations more flexible and customizable. All of this may sound a bit abstract, but it is best understood when explained with examples. It is then that we discover the true potential of conditional functions.

El excel conditional formatting It is very practical when performing a visual analysis of the data. Cells are highlighted by color scales, data bars, and icons. But all this is not merely aesthetic, obviously, but rather a way of facilitating the identification of patterns, trends, and also problems.

Before getting into the subject, we briefly explain how to apply conditional formatting in a calculation daughter. First of all, we have to select the cells in which we want to apply that format. To do so, let's first "Start", then we select the group "Styles" and there we click on the conditional format, choosing one among the several that are available.

"IF" function

Excel conditionals are based on the SI function, which allows us to make logical comparisons between a value and a result. As always, it is much easier to understand any Excel concept through a example than with the usual technical definitions. This is also the case. Let's look at the following table:

IF function

In this illustrative example, we see A practical application of Excel's IF function: In column B we see a series of budgeted amounts to execute, for example, a series of projects. On the other hand, column C shows what has finally been spent on each of these projects. By applying the IF function we can determine whether or not the actual amount exceeds the budgeted amount and what to do with the difference. The syntax of this function is this:

=SI(C2>B2;»Exceeded budget»;»Within budget»)

Important: Whenever we use text in our formulas, we will have to write the text in quotes, with the only exception of the use of TRUE or FALSE, which is already included in Excel.

The cell where we apply the formula is D2, in which we want the corresponding result to appear. But if we want the calculation of the difference to be executed and it is reflected in another point of the table (for example, in E2), we will apply this other function:

=SI(C2>B2;C2-B2;0)

This is read as follows: IF(actual quantity C2 is greater than budgeted in B'; then budgeted quantity B2 must be subtracted from actual quantity C'; otherwise, nothing is returned).

It must also be said that, although Excel offers us some interesting conditional functions by default (SUMIF, COUNTIF, etc.), any user can create a conditional function from the IF function.

It should also be noted that Excel offers us the possibility of applying combined conditionalswhere two or more conditions must be met. The syntax for these cases is articulated using the AND function. We would express it like this, following the previous example with figures taken at random:

=SI(Y(C2=1>1oooo);C2-B2;0)

How to express conditionals in Excel

data bars

We review below the different types of conditional functions that we can apply in an Excel spreadsheet: to highlight cells, for upper and lower values, data bars, color scales, and icon sets.

highlight cells

Within the Excel Conditional Formatting button, we first find the option to Rules for highlighting cells, within which we can apply different colors, outlines and font types depending on each case:

  • Is greater than.
  • It is less than.
  • Among.
  • Is equal to.
  • Text it contains.
  • A date.
  • Duplicate values.

upper and lower values

It is the second option that we find in the Conditional Formatting button. It is used to establish different colors for the values ​​of the cell where the result of the conditional function is reflected according to certain ranges:

  • top 10.
  • bottom 10.
  • 10% of higher values.
  • 10% lower values.
  • Above average.
  • Below average.

Data bars and color scales

They are used to establish a visual comparison between cells. This option allows us to play with the colors and styles of the data bars, as shown in the image above (on the right). It is a very effective solution and it also adds an aesthetic plus to our spreadsheet if we are going to use it for a presentation.

icon sets

The last option with which you can organize from three to five ranges of values, assigning each of them a specific icon. In addition to the classic up or down arrows, we also have other icons that represent shapes, indicators and valuation signs.


Leave a Comment

Your email address will not be published. Required fields are marked with *

*

*

  1. Responsible for the data: Miguel Ángel Gatón
  2. Purpose of the data: Control SPAM, comment management.
  3. Legitimation: Your consent
  4. Communication of the data: The data will not be communicated to third parties except by legal obligation.
  5. Data storage: Database hosted by Occentus Networks (EU)
  6. Rights: At any time you can limit, recover and delete your information.