In this video, I share 7 of my Excel ‘auxiliary’ formulae – useful, lesser-known functions that work around the edges of a task to make everything work.   

Download Your Excel Free Cheatsheet 

I apply each of them one-by-one to a typical data manipulation task.  Make sure you download the download file and work along with me to learn these 7 Excel formulae you’ve probably never heard of. 

1) TEXT

The TEXT formula displays a piece of data in a chosen format.  Need to display a date as a month, for example, use “MMM” in the second part of the formula and watch the data appear in your chosen format. 

But, can’t you simply format the cell to do the same job? Yes, but configuring this kind of custom formatting requires clicking through lots of dialogue boxes and can be onerous.  I find the formula faster. 

CLICK HERE TO WATCH THE VIDEO 

CLICK HERE TO DOWNLOAD THE FILE 

2) MID

I work with ‘codes’ of various types more and more these days including transaction codes, products codes and URLs.  The way these codes join together pieces of alpha-numeric data often necessitates ‘extraction’ of a few characters from a string of text and numbers.  

The MID formula helps with this process of extraction by allowing us to choose a start point in the text and the number of characters to extract.  In the video, I use it to quickly pick out a two-letter product code from a complicated text string.  Cool! 

3) RANDBETWEEN

This application of formulae might sound a little … random.  But, it’s something I have to do all the time when testing Excel models and creating training materials, for example.  Generating random data is a really useful thing to be able to do. 

The RANDBETWEEN formula is a great starting point.  It’s simple: input a lower and upper bound for the formula, and Excel chooses a random integer value between the two.  This formula updates every time Excel calculates; so, to avoid too much screen-flickering, you might want to ‘fix’ the randomly generated numbers by converting them to values with copy / paste special, as I do in the video. 

The formula potentially saves hours of time … but should not be used by teachers to allocate grades to student papers 😉 

4) &

Ok, so technically this is not formula, but it is another little-known technique in Excel.  The ampersand (‘and sign’) can be used to join values from different cells together.  I’ve found it useful in all kinds of contexts.  In the video, we use it to ‘rebuild’ a transaction code by using the ampersand to concatenate (join together) its component parts. 

5) RIGHT / LEFT

MID’s partners in crime are up next.  MID extracts characters from within (ie. from the MIDdle of) a text string; RIGHT and LEFT allow us to isolate characters from the beginning (LEFT) or end (RIGHT). 

Again, the formula is relatively simple.  Point LEFT or RIGHT to a cell and specify the number of characters you wish to display from the start or end of the entry. 

Like MID, LEFT and RIGHT?  Also check out the LEN and FIND formulae.  Your data manipulation skills will go through the roof! 

Download Your Excel Free Cheatsheet 

6) VALUE

This formula fixed a lot of Excel errors I found difficult to understand.  Numbers displaying as text is problematic in Excel, and it’s something that can easily be missed.  A MATCH formula, for example, will not necessarily match 3 formatted as a value to “3” formatted as text.  This one thing cost me hours on a project a few years ago … 

It’s why I added the VALUE formula to my auxiliary formula toolkit.  The formula ensures numbers are converted to values which should avoid any unexpected issues later in the calculations - and implementing it could not be simpler.  It’s essential when extracting numbers from a text string, as we see in the video.  

7) UNIQUE

Finally, something new to keep the real Excel enthusiasts happy!  The UNIQUE formula is now integral to my practice because it quickly picks out unique values from a column.  No more VBA routines or ‘remove duplicates’ operations to get this done. 

It’s saved me a lot of time.  But, it comes with some caveats.  First, it’s only available in Excel 365 at the moment, so if you’re using Excel 2019 or earlier, you may not have access to the formula.  Second, it’s an array formula, so its output ‘spills’ into adjacent rows / columns.  If you’ve never seen this, it can seem unnatural.  But this kind of ‘array-driven’ behaviour will become more common in Excel in the future … 

So, there you have it, my Excel auxiliary formula toolkit consisting of 7 formulae that you’ve probably never heard of.  I hope they will save you as much time as they have saved me over the years!