Excel Series: Useful but overlooked functions

TODAY we will be looking at a few RAND(om) functions which often seems to be LEFT out of Excel spreadsheets. These are a few examples of useful, but often overlooked functions.

Sometimes ignorance is bliss, but not in this case. Ignorance could be the reason you are not more efficient in your workbooks! So, let’s get you in the know instead…

=TODAY()

This often overlooked function allows the user to insert a dynamic date into their worksheet. This means that every time the sheet is recalculated, the date will also be recalculated. So, if the file is saved today and opened tomorrow, the date will reflect tomorrow’s date.

It is a very straight forward function, which, importantly, has nothing inside the brackets.

*Note: if you want Excel to add a static date for today, make use of the keyboard shortcut (Ctrl + ;).

Mathematical functions

There are also a few mathematical functions which Excel users do not utilize as they do not fully understand their usefulness. Let’s have a look at a few of these overlooked functions.

=RAND()

Another function that does not require an argument between the brackets, is the =RAND() function. This function will pick a random number between 0 and 1, which will change every time the file is recalculated. This function is therefore useful to generate random bits of data. However, the next function is infinitely more useful…

=RANDBETWEEN(bottom,top)

This function generates a whole number between two points that you specify. It could apply to both dates or numbers. Therefore, in conjunction with a VLOOKUP or other lookup function, this function can allow the user to pull up random samples of information. So, if you have ever wanted to use Excel for a lucky draw, this function is the answer you have been looking for!

=CONVERT(number,from_unit,to_unit)

Stop the press! No need to install a separate app to convert your unit of measures any longer – here is a function that allows you to do it from within Excel!

Simply select the number you wish to convert, then select the unit it is currently in from the dropdown that appears, and do the same for the unit you wish for it to convert to.

Although the examples below are only for distances, there are conversions for weight, temperature, time and other categories.

Text corrections

There are a number of different text correcting functions that exist. They all work very similarly.

=UPPER(text)

This function will convert all the text within the cell reference to upper case. The cell reference is added as the argument within the brackets.

=LOWER(text)

This function will convert all the text to lower case.

=PROPER(text)

This function converts all text to sentence case – in other words, the first letter of each word is changed to upper case, and the rest of the word is depicted in lower case. Importantly, note that this does not only apply to the first word within the cell – it changes the first letter of each word to upper case.

Text extractions

Sometimes data is put together in such a way that text to columns will either not suffice, or will split the text in the wrong way. In such cases, the user may decide to make use of text functions that allow the extraction of certain parts of the text.

=LEFT(text,num_chars)

This function will extract the letters starting from the left of the cell, up to a specified number of characters.

=RIGHT(text,num_chars)

The inverse of the LEFT function, where it will extract the letters starting from the right-hand side of the cell, up to a specified number of characters.

=MID(text,start_num,num_chars)

This function extracts text based on the starting position designated by the function, up to a specified number of characters. Hence, the name “MID”, which means it is starting somewhere in the middle.

=LEN(text)

A useful function to use along with the above-mentioned functions, and is used to calculate the length of the text. For example:

=CONCATENATE(ref1,ref2)

Lastly, the function which is the opposite of Text to Columns…

Concatenate puts Humpty Dumpty back together again! Simply select the cells you need to combine, ensuring that any additional text is added within double quotation marks. It can also be simplified by using the & sign in between the cell references.

For example:

Conclusion

There you have it, a handful of lesser-known, overlooked, but useful functions that may just make your life easier.

For more interesting, but overlooked functions, join our next upcoming Excel Advanced training course where we cover other data cleanup functions, mathematical, statistical and financial functions. Don’t miss out!

And, as always, don’t forget to leave a comment about some other functions that you have found helpful. Also, if there’s a specific function you have been curious about, drop us a line – we may just add it to this post in future!