12 tools and formulas to save you time in Excel

How do you get on with Excel? I am an unashamed Excel geek - I think it's a brilliant piece of software. The versatility and usability are superb and you can do almost anything with it: from keeping track of your accounts to full-on project management.

However if you don't know your way around Excel it can feel overwhelming and simple tasks can end up taking a lot of time, not to mention causing a huge amount of frustration.

So for this month's bumper blog I'm sharing 12 tools and time-saving formulas that will make your Excel life so much easier.

Freeze panes
Deceptively simple this one. Yet guaranteed to be your next Excel best friend.

When you have a worksheet with lots of data there's not a lot more frustrating than scrolling down and losing sight of the header row so you have no idea of the context of the data you're looking at.

By clicking View and Freeze Panes you can fix the row and column headers so that they are always visible no matter how far into the spreadsheet you go.

​Text to columns and TEXTJOIN
In the past I have spent hours going through conference delegate lists in Excel either moving first names and surnames into separate columns to make them easier to sort or putting them back into the same column so they look neater.

If only I'd known about these nifty little tricks:
Text to columns is a function in the Data ribbon that separates out text into different columns
The TEXTJOIN formula does the reverse - moving text from separate columns into one cell.

These videos show you how they work

​COUNTA, COUNTBLANK, COUNTIF, COUNTIFS
How many times have you found yourself squinting at the computer screen while trying to count up how many delegates have said yes to an invitation or how many respondents to a survey have answered a question?
There's really no need. The various counting formulas in Excel can do it all for you.
COUNTA counts anything in a cell
COUNTBLANK returns the number of empty cells
COUNTIF counts how many cells contain a single criterion, e.g. the number of people attending an event
COUNTIFS counts more than one set of criteria, e.g. the number of people attending an event who are also vegetarian
See how they all work in this video:

​SUMIF and SUMIFS
Most of us know how to create a simple sum in Excel. But what if you only want to add up certain cells in a worksheet? The SUMIF and SUMIFS formulas are massive time-savers. They work in a similar way to the COUNTIF/S brothers.
SUMIF adds up all the entries that relates to one criterion, e.g. the total of your expenses in a given month
SUMIFS enables you to further filter that calculation e.g. the total of just your travel expenses in that month
Take a look at the video to see how it's done.

​TODAY, EOMONTH, DAYS
There are lots of date formulas in Excel and to be honest you'll never use most of them. There are three that can come in really handy.
TODAY - returns today's date. Sounds simple and it is. It does what it says on the tin - always returns today's date when you open the spreadsheet. No biggie, but will save you lots of time and hassle.
EOMONTH - calculates and returns the last day of the month based on another given date. Very useful for deadlines.
DAYS - calculates the number of days been any two given dates. More exciting than it first appears, this one.
This video shows you how they all work together.

​Hopefully you've got enough there to help you become a fully-fledged Excel expert in no time! And if it all sounds too much like hard work then get in touch to find out how I can help sort out your spreadsheets, wangle your Word documents and pimp up your PowerPoint.

Helen Leach VA Services specialises in helping small business owners implement and streamline processes. I take on the day-to-day admin enabling you to focus on your business and serve your clients. Get in touch to find out how to take your business from chaos to calm.