Formula Debugging in Excel – Tip [spreadcheats]
Can you imagine building a complex worksheet without formulas? I can’t. While no one can dispute the usability of formulas, we all know how painful it is when an excel formula returns a mysterious error and we don’t know what is causing the error.
Introducing Excel Formula Helper
One of the most common comments we get here is “how does xxx formula works?” Even though there is vast documentation on excel formulas including the built-in help in MS Office, often it is difficult for us to learn a formula quickly and use it in a snap. So to help new users of excel […]
Make your VBA macros work anywhere: Enable Relative References
Do you know that while recording macros in excel you can use the relative reference based recording so that you can use your macro anywhere and the references would be adjusted automatically? Yes! I have learned this yesterday and I thought it is pretty cool. Here is how you can make your macro VBA code […]
Excel Formula for Finding Repeated Words in a Cell
If you ever need to know whether a particular word is repeated in a cell (for eg: a file extension or telephone number or name of the customer) here is how you can find it. =if(lower(cell-with-text)<>SUBSTITUTE(lower(cell-with-text),lower(word-to-check),””,2),”Yes”,”No”) We are using substitute() to replace the second occurance of a particular word and then compare this with original […]
Extracting Unique, Duplicate and Missing Items using Formulas [spreadcheats]
Often I wish Microsoft had spent the effort and time on a data genie (and a set of powerful formulas) that can automate common data cleanup tasks like extracting duplicates, makings lists unique, find missing items, remove spaces etc. Alas, instead they have provided features like clippy which are intrusive to say the least. So […]