Working with numbers, data and spreadsheets can be time-consuming, but there is a way to improve efficiency with Excel functions.
Following on from his previous webinar on Excel’s VLOOKUP function, BizSmart Select Member Les Gutteridge of Microsoft Office training company 101 SystemWorks, explains in his follow-up webinar exactly what this function is for and expands on some of the other key functions you need to use to save time.
VLOOKUP can pull details out of a spreadsheet by simply typing in one number or product code.
When using this function you can bookmark areas of the spreadsheet you’re working with by giving them a name. You can then use this name in a formula, or use it to navigate around the spreadsheet.
When you want to fetch a name, press F3 and you’ll be given the drop down options. Or if you remember the name, you can just start typing and Excel will bring up the name.
There are two different VLOOKUP functions – approximate VLOOKUP and exact VLOOKUP (e.g. using a person’s ID).
The COUNTIF function gives you more complexity and will get you “cooking on gas” with your Excel usage. You can count the numbers of a particular thing based on a particular rule.
For example, in a sales tracking spreadsheet you might want to count the number of sales people who have billed more than £15,000 in the last month. The COUNTIF function will do this.
You can press ctrl and back apostrophe to move from the normal display to formula display, which is useful when you’re typing formulae.
The SUMIF function is similar, except in the above example you could add up the total amount billed for all of the sales people who hit their targets.
Other useful functions that Les covers in the webinar include Match, Index and Match Index together.
BizSmart aims to help SME and micro-business owners scale their businesses and create value through sound practical business support. We aim to give you insight and clarity and fire up your determination to succeed. You can access blogs like this and more besides through our free SmartRoom service here.