Brief overview of the column row highlighter
The column row highlighter is an experiment in conditional formatting. Moreover, we developed it as an Excel tool without macros. Basically, we like to call that sheet programming. Do not get us wrong, we love coding. But, it is a good challenge to try to do things in Excel without coding.
So, the way the tool works is, you make a selection and a row and column is highlighted. In addition, the intersection gets its own shading too, with a bold font.
Although this started off as an experiment, it works great. For such a small tool, it has the potential of doing some interesting things. Therefore, take a look and decide for yourself. You never know, it may help you in your project.
Try this…
Once you get accustom to playing with the highlighter tool, try returning the value of the intersection point. For example, in the above image, the intersection point will return 4,269. Good stuff!
Return row and column number
In the above image, we show you how to get the row and column of the intersection point. These formulas represent the location of Pinterest for Jun. So, it returns numbers. We use the MATCH function to accomplish this task.
Give it a shot! Also, you can probably use VLOOKUP or another formula to produce same results.
Get value of intersection point
As mentioned earlier, in Excel, there is more than one way to skin a formula. Now, we want to get the value of the intersection point. To do this, we use the OFFSET formula. Again, you will have to enter the formula yourself, from the above image. It helps if you work it yourself sometimes.
Make it one formula
Once we make it one formula, we can get rid of the position formulas. Make sure you insert the MATCH functions in the right location. You should get the same result as shown in the previous section.
Imagine if you have to type this long formula by scratch from the beginning. Um, um, um.
We make it easy for you.
Finally, make it look good
Presentation is what really matters. In the end, the client is not even looking at the formula. For the most part, they just want results.
So, here you go…
The video
Here is a video, showing the highlighter in action.
Download
Requirements: Excel 2007 or higher or compatible.
Download 8.1 kilobytes