In today’s post, I would be writing about two very interesting and helpful functions of Excel that could be exploited to improve efficiency when working on MS Excel:
Show All Formulas: Sometimes, when working on complex models, one needs to give a quick run-through of the entire model to check for any inconsistencies that might have been unintentionally left out. Use ‘CTRL’ + ‘~’ to see the magic. Clicking these two together changes all the functions in a way so as to make them all visible. This is very helpful when checking the financial models or understanding the models linkages quickly.
Converting Text to Numbers: Oftentimes, when people are working on information sheets with information that is contained in a set of cells, the information extraction process can be overwhelmingly high. For instance, there could be addresses of people residing in a particular city as a text, where you want to separate their postal codes/ zip codes. Considering that the postal codes are 5-letter figures, you could use this formula ‘=VALUE(RIGHT(A10, 5)).’
Using VALUE lets you extract numbers within text and convert the same to numbers in a separate cell and RIGHT (alternatively, LEFT) is used to identify the number of characters that would be extracted from a particular cell from the right or left side, for instance, in this case, it’s 5 characters from the right of cell A10.