Excel: Top 5 formulas for an easier life

I think it's safe to say that Excel is much loved and much hated, particularly when it comes to formulas. However, once you move beyond SUM, AVERAGE and COUNT, a whole world of spreadsheet fun awaits - I promise!

1. SUMIF

SUMIF makes it easy to break down and automatically update sums based upon the criteria that you set. For example, below I'm using a SUMIF formula to calculate sales for each client.

2. TRANSPOSE

Converts a row to a column, or vice versa. Simply highlight an area of blank cells (the same number of cells as you want to convert, in the format you want them). For example, below I have converted the text from vertical to horizontal.

When you've selected the blank cells, enter =TRANSPOSE(cell range) and hit CTRL+SHIFT+ENTER.

3. TODAY

=TODAY() Automatically enters today's date into the selected cell whenever the worksheet is opened.

4. LOWER

This simple but time-saving formula converts text in the selected cells to lowercase. Its sister formula, UPPER converts to uppercase.

5. TEXTJOIN

You can use TEXTJOIN to quickly and easily bring the contents of multiple cells into one cell. The below example brings two cells together by setting the delimiter as a space.

Once you get used to how the formula works, it's easy to quickly combine cells. However, once you have the cells in the format that you want, I always copy them and paste as values into a different column to copy only the result of the formula, not the formula itself - very useful if you're trying to copy into another worksheet or move cells around.

These are just a few of my favourite formulas for saving time and energy - if you want to learn more, then Microsoft have a great support section of the website with lots of handy guides on functions and formulas.