With everyone using Excel more and more in business, the level of skill and knowledge required has increased hugely. Just being able to create a simple spreadsheet and input data is often no longer enough and more advanced use of the tool is required especially when it comes to formulas.
The trouble with Excel is that most of us don’t know what we don’t know. We might be vaguely aware that there is more we could be doing, but without some guidance – it’s hard to know where to start and with 479 functions in the latest version of Excel there are plenty to choose from!
The good news is that Excel has some powerful advanced formulas and functions if you know how to use them and once you do there’s so much potential for time saving and productivity hacks. Below are just a few of the different advanced formulas and functions that could help you get more from your data.
Advanced data manipulation
If you are importing data from other systems, the huge choice of functions can be invaluable. A range of text functions allows you to manipulate data, either concatenating cells into one, or splitting data into multiple columns, then date functions allow you manipulate how dates look and are interpreted. These are super handy tools if you have spreadsheets that need to update based on dates or similar info.
The use of IF functions can allow you to change data dependant on values in other cells, for example you may wish to check for overdue payments by comparing due date and today’s date and mark up accounts due for payment. You can also use the IS functions to validate data from formulas and tidy up what you see in cells. This makes cleaning your data much faster and efficient.
When needing to create more complex formulas you sometimes have to use a number of different functions. To allow you to benefit from the power of multiple functions in one calculation Excel lets you create nested formulas of up to eight levels. Done right, this kind of multi-level formulas can create hugely powerful actions in your spreadsheet that could save lots of time and help you really drill down into your data to get the most from it.
Now Excel is certainly not a relational database however functions such as VLOOKUP, HLOOKUP, INDEX and MATCH allow you to reference data from other tables. Microsoft are also adding and improving things all the time with Excel 365 and have recently introduced a new XLOOKUP. All very handy when working in multiple, but related, spreadsheets. Using these functions means that, provided you have a unique key for referencing, you can pull in data from other Excel spreadsheets and the data will automatically change whenever the reference information changes. No more having to update multiple spreadsheets with duplicate information!
Putting it into practice
The majority of us are self-taught in Excel and can get by with what we need to do. However, we don’t necessarily do things the most effective way and could be saving time, energy and frustration by utilising some of the additional functionality it has to offer.
To really get to grips with these more advanced options, face to face training has so much to offer. Not only do you learn how to do a formula or function, you also learn why you’re doing it, and why it works. This helps you understand how to do something right, but also means you can trouble shoot if things don’t go to plan. At Underscore, we’re all about learning by doing, and when it comes to Excel, we’ve learnt from experience that there’s no substitute for in-person training.
If you want to learn more about the functions shown above plus many more and how they can help make your Excel spreadsheets more efficient, then our Excel Module 4 Advanced Formulas and Functions course will help. If you have a group needing training then please get in touch and we can organise a date to suit you.