00:00If you want to seriously impress someone with an Excel skill, do this kind of visual query that
00:04highlights your dataset table as you make changes to your selection. Let's start off by creating a
00:10pulldown on the financial element. Click on cell B3 here, go to data, data tools, and data validation.
00:18This pop-up window should appear. Make sure you're on the settings. On the allow, you're going to
00:22select list. And on the source, you're going to click on this button and basically select the
00:27financial element, which is ranging from, at least for me, from C5 all the way down to the very last
00:33element in C36. And then hit enter twice like this. And just as a test, if you do a pulldown,
00:40you can see all the different selection of financial element. I'm going to select this
00:44revenue TikTok here. Let's do it for the month. Same thing. Data, data tools, data validation.
00:50Pop-up comes up. Make sure you're on the setting tabs. Allow, you're going to change it to list.
00:54And the source, you're going to click on this button here. And then after that, select January
00:58to July like this and hit enter. And after that, hit enter one more time. And now your month,
01:05you have all the selection that's available on your dataset. With that being said, let's work on the
01:11result. Before we dive into the result, I want to show you what the match function does for me.
01:16Match basically goes through and looks for April in this dataset range here like this. And then we're
01:22just going to say zero or exact match and close parentheses. What this does is that it returns
01:29the position of the value April. So one, two, three, four. And that's how I get it. So if we
01:35change this
01:35to say May, you can see one, two, three, four, five. And that's how I get the value of five.
01:41Now you can
01:42appreciate that this number is going to be your column index on your VLOOKUP, which we're going to
01:49nest here next. So basically going to say VLOOKUP, open parenthesis. And the VLOOKUP, which is the
01:55outer shell, we're going to look for financial element, which is going to be cell B3 here,
02:01comma. And then the lookup array would be from C6 all the way down to the very last corner of
02:09your
02:09dataset like this here. And after that, press comma. And the third argument would be your match
02:15function itself. And we're going to do plus one. The reason for plus one is because we start
02:21with column C, not D. That's why we have to offset by one. That's why plus one is located here.
02:27And
02:28then comma. And then we're going to select false for exact match, close parenthesis, and hit enter.
02:34As you can see, the revenue TikTok in May, you go TikTok right across, in May is 6,709. And
02:41just
02:42as a test, let's change our financial element to say, advertisement and promo. In May would be 225,
02:50right here like this. Next, we'll set up the crosshair highlight using conditional formatting.
02:55So basically, you're going to have to do a prep cell at cell H1 and H2. H1 will be for
03:00column.
03:01And the formula for that will be match. And the first argument for that, we're going to use
03:08month to get the column itself, because the columns, or rather the month are arranged in a column.
03:16And then comma. And the lookup array would be this guy's here from D5 to J5. Comma. We're going to
03:23select zero for exact match. And then we're going to plus three to get the exact location on your
03:30worksheet. The three comes from A, B, C. So it's offset from here to here to get the value of
03:37three.
03:38The reason why you do plus three is to get the exact position on the worksheet. Let me explain.
03:43If I hit enter here, I get the value of eight. So if you count one, two, three, four, five,
03:49six,
03:50seven, eight, you can see May is on column eight. That's why we have to offset by three. And this
03:56again,
03:56would be your column index for your conditional formatting. Next on H2, we're going to use the
04:04match function again, open parenthesis. This time around to get the row, we're going to use financial
04:10element itself. And the second argument is your financial element ranging from cell C6 all the
04:16way down to cell C36. And I'm going to hit comma here, we're going to say zero for exact match,
04:23close parenthesis. Now you can see this thing starts at row number six. So it's definitely going to return.
04:30And if I hit enter here, 13 will be from one, two, three, four, five, six, seven, eight, nine,
04:3810, 11, 12, 13, which is your promo. But we don't want to start from here. So therefore,
04:43we're going to have to add five, which is the row ahead of it. So we're going to say plus
04:47five.
04:48And you can see it is 18, which is right here in cell row number 18 here. So now this
04:54is your column
04:55index, this is your row index. With all this information on hand, let's start our conditional
04:59formatting to highlight the intersecting cell. So the first thing you have to do to apply the
05:05constraint formatting is to select the area on your data set to apply the conditional formatting to.
05:10Then after that, go to home, style, conditional formatting, and you're going to select new rule.
05:15In here, you're going to select user formula to determine which cell to format.
05:19And on this section here, you're going to enter the following formula. This formula what it does is that
05:24it goes through every cell on your data set table that you have highlighted and compares with
05:29row index, which is on H2. And then compares the column as well with your column index on H1.
05:36And if both of those conditions are true, it's going to change, let's say the color to red like this.
05:43And after that, click one more time. And you can see that on the month of May for advertising and
05:50promotion is 225. And just as a test, if you go ahead and make changes to say March,
05:56you can see that the highlights are moving as per the results here, 1212. And just to close it off,
06:05and as a housekeeping, good practice, we're going to change this font color to be white,
06:10so that it's not visible to anyone.
06:13So
06:14And I'm sorry.
06:16So
06:17if you go ahead and see that, you'll see that you can see that you're going to change.
06:18You know, I'm good.
06:18You know, I'm good.
06:18I'm good.
06:19So
06:19you know,
06:19I'm good.
06:19I'm good.
Comments