Create Excel Chart with the If Statement

As already discussed, options for work in Excel are limitless. It is guaranteed that nobody in this world knows everything there is to know about it. It is also a great tool for the visual presentation of the data.

We can do this with the help of the charts. What is good regarding the charts in Excel, is that we can combine them with existing formulas.

In the example below, we will show how to combine charts with the IF formula.

Create Excel Chart with the If Statement

To create a chart that will be used in our example, we need to create the original table first. Our table will consist of the list of eight students and their grades in several subjects: Maths, Geography, History, and Sports.

The next order of things is to copy the student’s list and then create a separate field- cell I2, where we will have a dropdown with all the subjects we have in the original table. To do this, we will select the cell I2 and then go to Data >> Data Tools >> Data Validation:

When we click on it, the following window will appear:

On this window, we will choose List beneath Allow, and choose a range B2:E2 as a source (this is where the list of our subjects is located):

Now we have a dropdown in the cell I2:

Now we need to make sure that we show the proper results every time we select the dropdown. It is worth saying that the dropdown functions in the same way as the IF function in this case (if we select Geography we will be presented with a particular data. If we select History we will have different data and so on).

The formula that we are going to insert in cell I3 is as follows:

HLOOKUP is the same thing as VLOOKUP, with the difference that the data from the table_array are being searched horizontally.

The first parameter that we need to define is lookup_value. In our case, it will always be cell I2, so we lock this cell with a keyboard shortcut (F4).

Next, we need to find table_array. This part is easy, as we will select our table. We will lock these cells as well, as we do not want to change this part when dragging the formula down.

For the next thing, we want to search for row_index_num. This part is a little bit tricky, and we will use the formula ROWS to help us out. In it, we will define an array that includes cell H2 (always, that is why it is locked), and any cell in column H in the currently active row.

For the final part, we define range_lookup as false.

This is just a general overview of the formula. If you are not sure how exactly does HLOOKUP formula work, you can always look up detailed explanations, as it is not in the scope of this exercise.

All we need to do now is drag our formula to the 10th row (the final row where the data is located) and we will end up with the following table:

If we change the subject in cell I2 (let’s say, to Sports) we will have different data, due to our formula:

And we can see that the data for Sports in column I corresponds to the data in column E.

You might wonder- all this trouble, but for what? Well, now it is time to insert our IF function. We will create another column, right next to column I, where we will put a reward for students- 0$ for those who got grade 1, 100$ for grade 2, 300$ for 3, 500$ for 4, and finally, 1000$ for students that got grade 5.

The above said should be placed in the IF formula as follows:

These are the results that we get:

One more thing that we did here is to concatenate the text from cell I2 with the word “Rewards”. This is the formula to do so:

We did this so that the text would change whenever we change the subject in column I.

We will now select the data from columns H and J, and go to Insert >> Charts >> Recommended Charts:

On the window that pops up, we will choose the first chart, which is Clustered Column:

We will click OK, and have the same result as previewed:

Everything we prepared till this point can finally come to fruition, as all we need to do to get the rewards for the students in any other subject is to change the subject itself in a dropdown that we have in cell I2. For example, we want to show the rewards per student for History we just choose History in this cell:

You will notice that all of the data (columns I, J, and the chart itself) immediately change once we change the subject in cell I2, which is pretty handy.