Most used functions in MS Excel

According to a recent Microsoft post, the most used functions in MS Excel, in order, are

  • VLOOKUP
  • IF
  • SUM
  • COUNTIF
  • SUMIF
  • CONCATENATE
  • AVERAGE

Although the order may be a bit subjective and skill dependent, it is true that these are the functions that get the most interest in our courses.

Below a summary of why these functions are important and a link to all our materials relating to each function.

VLOOKUP

vlookup-explainedProbably Excel’s most powerful function even though the new XLOOKUP will replace it once everyone is on the same version of Excel (which will take time).

The basics are that it tells Excel to

  • look through a list,
  • find the correct item, and
  • report back what it finds with regards related information.

In simple terms it mimics what you would do if you looked up a name on your company telephone list, and when you found the name you jotted down the phone number.

Although what it does sounds simple, it is used in many ways including:

  • merging databases
  • finding missing items
  • standardising reports
  • finding duplicates
  • using sliding scales.

If you don’t know about it, this is one you must learn. If you already have XLOOKUP, still learn VLOOKUP first. It is very good for understanding how Excel ‘thinks’ and you will easily adapt to XLOOKUP.

To see all our information on this function, look at the VLOOKUP search page.

IF

Similar to how you would speak, the IF function gives Excel an either/ or situation.

For example you may tell a sales person IF they sell more than 10 000 widgets a month they will get a commission of 5%.

In a similar way, the Excel IF function needs a condition (you sell more than 10 000), and what happens if the condition is true (you get a 5% commission ).

Unstated in the sentence is that if you don’t sell that many you get nothing.

Excel needs to know what happens if the condition is met AND not met. Although we don’t speak like this, we should say IF they sell more than 10 000 widgets a month they will get a commission of 5% otherwise they will get nothing.

To see all our information on this function, look at the IF search page

SUM

This is a shortcut that allows you to add up a number of cells. In general it just is easier than clicking on each cell and typing a plus sign in between.

To see all our information on this function, look at the SUM search page.

COUNTIF/ SUMIF

In our view these 2 functions are second only to the VLOOKUP formula in usefulness.

Again if you were speaking to someone you would say out loud what this function does e.g.

  • you may ask the finance team to sum all sales generated by Jane Doe. In Excel speak you are saying please SUM all sales IF the salesperson is Jane Doe.

Similar to VLOOKUP it can be used to:

  • merge databases
  • find missing items
  • standardise reports
  • find duplicates.

To see all our information on this function, look at the SUMIF/ COUNTIF/ AVERAGEIF search page.

CONCATENATE

A horrible word and difficult to spell, the more recent versions of Excel have a replacement which says what it does i.e. TextJoin.

CONCATENATE allows you to join text/ cells together to create a single cell. Simplistically, this is what is used when you get your personalised text telling you your long lost uncle has left you millions. No one is sitting there and typing in the name into each text. A system is taking your name, putting it in the right place in a sentence and making it look like a single sentence. This is like what mail merge does in MS Word.

You will be surprised how often in your MS Excel work you need to combine a number of cells into a single cell.

To see all our information on this function, look at the CONCATENATE search page.

AVERAGE

Much like the SUM function, this is a shortcut that allows you to average a number of cells. In general it just is easier than clicking on each cell and calculating the average.

To see all our information on this function, look at the AVERAGE search page.

Other useful MS Excel functions

The above are the most popular, but there are many more.

Our online MS Excel Functions and Formulas course goes through all of these and more.