Say No To Standard Comments Inside A Formula!
- Gaurav Dhama
- Jan 16, 2022
- 2 min read
Have you ever used a formula in excel to give a standard comment! If yes then you must have noticed that the formula becomes too bulky making it hard to read and interpret.
It actually matters a lot that the formula is understandable. And if you have used this formula in multiple places it is difficult to find each formula and replace its value. Even if they are in sequence, editing the comment in one formula without breaking the formula and the replicate it. Sounds like a lot of work, right?
Well there is a good work around to make your formula small and easy.
Instead of comments use numbers as output, then use another column to refer these numbers to give you the standard comments stored in another table.
Lets take a very simple example

Comments in Column I are derived based on the values in column J i.e. 1 , 0 or -1.

If you noticed in above snips, spelling of average is incorrect, well this very common mistake and can be a bad impression and also you cannot use spell check for these values.

So if we use the method we are discussing, we can use the spell check in the column where standard comments are defined and excel will correct all the incorrect spelling, which in turn will correct all the formula results automatically.

Now we have a set of comments with correct spellings.
You must be wondering why did I use two columns instead of a complex formula in one single column. Well lets see why.
Conditional formatting the numbers using icons.
Green for 1
Yellow for 0
Red for -1
Now we can visually see the performance next to the comment.

This is a very simple example. Once you get the concept, you can create logics and derive a number out of it and refer these numbers to drive out your standard comments.
Thanks for reading. If you have any doubt or query, do write back and we will try to SOLVE THAT QUERY.
Comments