Why do I love Excel so much are you maybe wondering? Because Excel is kind of a fairy tale! If you are curious, there is an infinity of mysteries to explore. And once you know how to play well with its functions and macros, you’ll enter a powerful wonderland of possibilities. So, are you ready to discover my Top 5 Magical Formulas?
VLOOKUP
My #1 favourite function, the day that I discovered a world of possibilities opened to me!
VLookup function allows you to do something like this :
If the value of one cell (“A3”) of this specific column (A) is
the same than this specific cell that I chose to compare it with (whatever cell) or a choosen text / number that I indicate here,
then I want to see appearing in my current cell (where I am writting this vlookup function)
the associated value which is at x (2 for column B, 3 for column C etc…) columns from cell (“A3” in example).
It can seem complicated, but it is not… you need to see it applied to a specific case to understand well, and you’ll see that you will use it all the time afterwards!
CONCATENATE
Allow you to compil the value of two cells in one and you can even add words, numbers or spaces in-between.
If you want to do the contrary: divide the value of one cells in two cells depending of specific criteria (a space, a coma, whatever…), you can use the “Text to columns” tool in “Data”.
SUM. IF, COUNT.IF, AVERAGE.IF
All these functions associated with “if” are very useful to get automatic calculation from a large database.
ISNUMBER
This function can be integrated into more complex functions to avoid errors messages when making calculations if you have both numbers and texts mixed in the same column or row.
ISERR, ISNA
These functions are really useful associated with “if” to replace error messages by a “0”, in order to make your sheet looking nicer and doing making calculations using these cells possible.
TRANSPOSE
“Convert a vertical range of cells into an horizontal range of cells (or vice versa)” explains Excel, and I think the explanation is quite clear! 😉