There are typically three "Wow" moments when learning to use Microsoft Excel...
1. Learning to do a SUM formula and dragging it across your data using AutoFill
2. Creating your first graph with a simple selection of your preferred data and clicking a button
3. Learning how to use the VLOOKUP formula to query a range of data for a specific result
Many users may never make it as far as #3, but for those that do, the VLOOKUP function is life-changing.
VLOOKUP, HLOOKUP, INDEX and MATCH...
Put simply, VLOOKUP will look for a value (either numbers or text) in a column. Once it finds a match in another column or datasheet, the VLOOKUP will return the desired value from any cell in the same row as the matched value.
When the data is more intricate, VLOOKUP's cousins INDEX and MATCH are required, adding further complexity.
While VLOOKUP searches for the value in a column, HLOOKUP (Horizontal Lookup) searches for the value in a row.
Now it gets even better with XLOOKUP
Microsoft have recently announced the upcoming release of XLOOKUP, a new function that rolls up the skills of VLOOKUP, HLOOKUP, INDEX and MATCH into a single, more powerful and more accessible function.
This new function basically allows you to search in any direction.
Unlike VLOOKUP, XLOOKUP is able to return an array with multiple items, which allows a single formula to return more than one item. For examples on how you can use XLOOKUP check out the Microsoft Office support site.
This function is now available via Microsoft's public beta test stream, the "Office 365 Insiders Program".
Following further testing and quality assurance through the Insiders program the feature will eventually roll out to mainstream users of Office 365's Excel.
Bonus Tips on getting more from Excel
To keep you loving Excel until we all get to enjoy XLOOKUP, here are three tips on getting the most from your Excel spreadsheets.
1. Use"traffic light" conditional formatting for simple visual representation of your data.
Choose the Conditional Formatting drop-down menu from the Styles section of the Home ribbon on Excel, and from the Colour Scales sub menu choose the top left option, which should be the Green - Yellow - Red Colour Scale. Excel will automatically format your text in shades from Green to Red in relation to each other.
2. Autofill formatting, not just data or formulas.
Using Autofill to have Excel replicate pages of formulas has been THE key feature of Excel for some time, but did you know you can Autofill formatting without filling in formulas or texts? Grab the Autofill box with your right mouse button instead of your usual left mouse button, and drag down the series you want to fill. When you let go of the button, choose Fill Formatting.
3. Keep your data separated into sheets, don't set up multiple tables on a single sheet.
Having your data laid out in separate sheets with column labels at the top of each column with each row representing a record allows your spreadsheet to act as a database without fussing around re-formatting and exporting/importing data to get it prepared for use by an external applications. No formatting of spacing either, just keep it as data with simple (but elegant) formatting.
Need help with Microsoft Office 365?
Our experienced Business Technology Managers (BTMs) are available to assist you roll out an Office 365 or Excel project, or to ensure that you have an up-to-date and connected productivity suite. If you need help with yours, give us a call on 1300 307 907 or contact us via the form below.
