Chart IF Formula Examples in Excel

Yes. In Excel, use an IF formula to create chart-ready values in helper columns, then select those values and choose Insert > Insert Column or Bar Chart, or another chart type. IF does not make a chart by itself; it decides which values the chart can display.

What it means to chart an IF formula

An Excel chart plots values stored in worksheet cells. An IF formula can control those values based on a condition, so the chart changes when the source data changes. For example, it can show a sales figure only when it meets a target, split results into two series, or turn text statuses into numbers that a chart can compare.

The formula has three parts: a condition, the value to return if the condition is true, and the value to return if it is false. In general, the pattern is =IF(condition,value_if_true,value_if_false). The condition can compare numbers, dates, or text. The results can be numbers, text, or an Excel error value such as #N/A, depending on what the chart needs.

Keep the source data separate from the helper formulas when practical. That makes it easier to check the original figures, understand the chart, and update the rules later. The examples below use Excel for Microsoft 365 on Windows; the IF formulas also work in earlier Excel versions that include IF.

Example: show values only when they meet a target

Suppose A1:B5 contains monthly sales: month names in A2:A5 and sales amounts in B2:B5. The amounts are 80, 125, 95, and 150, and the target is 100. Put a heading such as At target in C1. With sales in B2:B5, enter this in C2 and fill it down through C5:

=IF(B2>=100,B2,NA())

The results in C2:C5 are #N/A, 125, #N/A, and 150. Excel charts generally omit #N/A values from a plotted series, so the chart shows the sales amounts that meet the target without plotting the below-target amounts as zero. The worksheet cells will display #N/A for those excluded rows; that is expected in this technique.

To make the chart, select the month labels in A1:A5 and the helper series in C1:C5. On Windows, hold Ctrl while selecting the second range; on Mac, use Command. Choose Insert > Insert Column or Bar Chart > Clustered Column. Excel creates a chart with the months as category labels and qualifying sales as the plotted values. If selecting separate ranges is awkward, place the month labels and helper series next to each other in a compact chart table, then select that table.

Example: split a chart into two conditional series

A single helper series can hide values that fail a condition, but two series can make it easier to compare categories such as above target and below target. Continue with months in A2:A5 and sales in B2:B5. Enter At target in C1 and Below target in D1. With the sales figures in B2:B5, use these formulas in C2 and D2 respectively, then fill both down to row 5:

=IF(B2>=100,B2,NA())

=IF(B2<100,B2,NA())

The first series returns 125 and 150 for the months that meet the target and #N/A otherwise. The second returns 80 and 95 for the other months and #N/A for the qualifying months. Select A1:A5 and C1:D5, then insert a clustered column chart. Excel displays each month as a category, with the applicable value in one of the two series. Choose distinct series colors so the legend clearly identifies each group.

This approach is useful when a reader needs to compare the two groups, but the gaps are intentional: a month will have one plotted column and one missing column. If you want every month to show two columns, consider using zero as the nonmatching result only when a zero is a truthful and understandable chart value. A zero can look like a genuine measured value, so it is not always a good substitute for a missing point.

Example: convert status text into chartable numbers

Charts can plot numeric values more directly than words such as Complete and Pending. Suppose task names are in A2:A6 and status text is in B2:B6. Create a heading such as Complete flag in C1. With a task’s status in B2, enter this in C2 and copy it down:

=IF(B2="Complete",1,0)

The formula returns 1 when B2 contains Complete and 0 for any other text. A column chart can show the flag for each task, but it may be clearer to summarize the count of completed and pending tasks before charting. A 1/0 chart shows each individual record; it does not automatically count or label the categories as totals. Also check that the status spelling is consistent. A value such as Completed, with an extra d, does not match the test and returns 0.

For a count-style chart, make a small summary table with the labels Complete and Pending and their corresponding counts, then chart that table. You can calculate those counts with counting functions, or enter them directly for a small illustrative example. The key point is that IF can classify each row, while a separate summary step may be needed to turn classifications into totals.

Choose a chart type that fits the formula output

The formula’s output and the question you want the chart to answer should guide the chart type. A few practical choices are:

Column or bar chart: compare conditional values across categories, such as monthly sales meeting a threshold or task completion flags.
Line chart: show a trend over time. Use a continuous numeric series where possible, and decide whether excluded points should appear as gaps rather than as zero values.
Stacked chart: show components that add to a meaningful total. Do not stack alternative versions of the same measure, such as an actual value in one series and that same actual value in another conditional series.
Pie chart: use only when the data represents parts of one whole. A list of monthly sales is usually a time series, not a set of parts of one total.

For charts that compare categories, a helper series returning #N/A for nonmatching rows is often more informative than a formula returning text. Text results such as Met and Missed can still be useful as labels in the worksheet, but they are not numeric measures to plot as column heights.

Make the chart update cleanly

When you fill a formula down, check that each row refers to its own source value. A formula copied from C2 to C3 should refer to B3, not keep referring to B2. Excel normally adjusts relative references during filling. If a condition uses a fixed threshold stored in a cell, an absolute reference can keep the threshold fixed as the formula is copied. For example, put the threshold in F1 and refer to it as $F$1.

For a chart that should grow as new records are added, format the source range as an Excel table using Home > Format as Table. Tables expand as rows are added, and formulas in a calculated column are usually filled into new rows. When the chart uses table columns, it can include added rows as the table expands. Check the chart’s data range if it does not update as expected.

To edit which cells the chart uses, select the chart and choose Chart Design > Select Data. In that dialog, you can change the chart data range, add or remove a series, and adjust category labels. These controls are useful if Excel interprets a helper column as a category rather than as a numeric series.

Common problems and how to fix them

The chart shows zeros instead of gaps: check whether the formula returns 0 for rows that should be omitted. Use #N/A when excluding a plotted point is the intended result, and review whether a zero would mislead the reader.
The chart includes #N/A as a visible point or label: confirm that the formula is in the series values rather than in the category-label range. You can also inspect the chart’s selected data ranges through Chart Design > Select Data.
All rows appear in the same group: check the condition, comparison operator, threshold, and source values. For text comparisons, check for inconsistent spelling or extra spaces.
The chart has an unexpected legend or axis: verify that the header row and category labels were included correctly. Use Select Data to set the series name, series values, and horizontal axis labels.
The chart changes when the threshold changes, but the formula does not: confirm that the condition refers to the intended threshold cell. If it should remain fixed as you copy the formula, use an absolute reference such as $F$1.

One common misconception is that IF can format a chart point by changing its color. IF returns a cell value; it does not directly apply chart formatting. Splitting data into separate conditional series gives each group its own color, which is usually the clearest method for conditional coloring.

Frequently Asked Questions

Can an IF formula create a chart in Excel?
No. IF returns a value in a worksheet cell. Select that result as part of a chart’s data range, or use it in a helper series to control what the chart plots.

How do I hide a value from a chart with IF?
Return NA() for rows you want omitted from a plotted series, rather than returning zero. Excel charts generally do not plot #N/A values, although the worksheet still displays the error.

Can I use IF to change the color of a chart bar?
Not directly. Use separate helper columns for the different conditions and add each column as a chart series, then assign a color to each series.

Why does my IF-based chart show zero for a missing result?
The formula may be returning 0, or the chart may be interpreting an empty result as zero. Check the formula output in the worksheet. If the point should be omitted, try returning NA() and confirm the helper column is used as a numeric series.

Can I chart text returned by IF?
Text such as Met or Missed is useful for labels or classification, but a standard column or line chart needs numeric values for its plotted heights or positions. Convert the categories to numbers or create a numeric summary table to chart them.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.