October 2013

Index and match versus lookups – when to use them

1 comment

Formulas discussed: Lookup, Vlookup, Hlookup, Index, Match.
Just want the table to show you which formula to pick – click here. The workbook used throughout this post is available here.

This article and the attached spreadsheet will discuss the various methods of searching for a data point in an Excel table and return the value in a corresponding row or column, without having to manually look through the table yourself. Common examples include searching for a sales result for a particular period, or matching a data field to a person’s name.

screen shot - sorted data table

Brendan WalpoleIndex and match versus lookups – when to use them
read more

Excel’s best feature hidden in plain sight – Using Styles

No comments

Styles are a reasonably new feature, being significantly upgraded in Excel 2007 for Windows (earlier verisons had much reduced functionality), but not making it to the Mac version until Excel 2011, inexplicably being left out of Excel 2008 for Mac, along with all macro/VBA functionality. I love styles so much that 10 years ago wrote a .xla file that I used to keep formatting consistent throughout a model (although far inferior to what MS ultimately incorporated, as it was very slow, having to look at each cell individually). And when I found out that they’d been included in Excel 2011, I went and upgraded the whole Office suite on the spot (which if the company you work for is a participant in Microsoft’s Home Use Program, is well worth it).

What are Styles

Styles are a set of preset, editable formats that you can apply throughout your workbook. Excel contains a good number of built-in styles straight out of the box, such as Normal, Output, Heading1, Total, Input etc. These are all capable of being customised to your liking, or you can create new ones.

Brendan WalpoleExcel’s best feature hidden in plain sight – Using Styles
read more