This lets you visually see where the value is located in the original data set.
Excel’s conditional formatting feature comes in handy here.
A possible approach would be to look up the number result in the data set.
However, this would be a wrong approach since it is possible to have two or more identical numbers in a data set.
This means that if the App Perino is selected for the month of March, this gives a result of 802.
However, 802 also appears under the Fightrr app for the month of May.
To highlight the correct cell, a check needs to be done for the row and the column based on the selected App and month.
Set up a conditional formatting rule by going to Conditional Formatting > New Rule.
Here you can opt to use a formula to determine which cells to format.
The logic behind is to find what column is equal to the selected month, as well as to find what row is equal to the selected App.
Some challenges in setting up conditional formatting formula rules:
- In the conditional formatting interface, you will not get the Excel help to know which arguments to put in.
- You have to be very careful with fixing the cell references. By default, clicking on a cell gives you a fully fixed cell reference, which might not always be the case. Because you are writing the whole formula from the point of view of the first cell, you would also have to think about how it should apply to the rest of the cells.
To make this easier, type the formulas on the spreadsheet so that you know that it works before transferring it to the conditional formatting rule.
To start off, two conditions need to be met: a cell needs to have the correct corresponding App name and Month. The AND() condition is suitable here.
Cell J7 = AND(B6=C3,A7=B3)
Next, determine which cells need to be fixed.
- First argument: Data cell’s month (B6) is equal to the selected month (C3).
- The cell of the selected month is fixed, so the cell reference should be fully fixed as $C$3.
- As for the month of the data cell, it does not change as it moves vertically but changes when it moves horizontally. In this case, only the row reference should be fixed. This gives you B$6.
- Second argument: Data cell’s App name (A7) is equal to the selected month (B3).
- The cell of the selected App name is fixed, so the cell reference should be fully fixed as $B$3.
- The app name of the data cell does not change as the selection moves horizontally. However, it changes when moved vertically. In this case, only the column reference should be fixed. This gives you $A7.
The final formula is:
Cell J7 = AND(B$6=$C$3,$A7=$B$3)
When hitting ENTER, you will notice that if the App Perino and the month of March are selected, the formula results in a “FALSE” because none of the conditions have been fulfilled by cell B7 (Fightrr, January).
Pull the formula across and all the way down to match the original data set.
This way, you can see if the formula works.
Notice that cell L9 displays as “TRUE” because this corresponds to the App Perino and the month of March.
You can then use the formula in cell J7 in the conditional formatting rule.
Highlight the data set (cells B7:G16) which you want to format conditionally.
As done earlier, go to Conditional Formatting > New Rule.
Select Use a formula to determine which cells for format.
Paste the formula.
Under the Fill tab, select a color.
You can also add a border formatting under the Border tab.
Once you have the type of formatting you need, click OK.
After which, you can then clean up and remove the temporary cells we set up to the right of the original data set.