12 of my Favourite Excel Features

During the six plus months I spent preparing for the CFA Level 2 exam, I didn’t write one single blog post, but I did work on a number of spreadsheets, most of them are property of Carlson Wagonlit Travel, but one will eventually end up on this blog when I deem it as good as it gets. While working away on spreadsheets dealing with online travel sales and e-commerce website KPIs I noted down twelve features of Excel that I like and few people leverage.

• Find Unique In Place was a feature I used every month to produce a list of unique vacation destinations. You can take a column of airport codes and with a couple quick clicks produce a filtered list of the unique destinations. Microsoft is always changing the menus in Excel, here is how to do it in Excel 2007.
• =subtotal can produced the same sum of a column as the more well known sum formula but it works with filtering, =subtotal has a lot of tricks actually.
• =lookup everyone always talks about vlookup, but the lookup function is even more general.
• Filter columns by heading is a trick I never can remember how to do, but once you learn the power of, you’ll want to learn to add subtotals. Apparently filter equals funnel in the Excel UI.
• Ranges are basically named variables, but not all ranges have names, though they probably should. Excel is very inconsistent especially when you throw in VBA, but learning what ranges are and how to leverage them makes debugging Excel formulas and making modifications easier most of the time.
• Conditional Formatting is another easy to use feature of Excel that everyone should use but does not. I use it to check for logic mistakes in my spreadsheets and to make negative numbers red, some conditional formatting is automatically applied in newer versions of Excel. But you can do a lot more than make negative numbers turn red.
• Paste Special in MS Office is another trick everyone should know and does not. When moving data between Excel, PowerPoint, and Word sometimes you want to paste in a special way.
• =randbetween is another function that people would use if they only knew it existed, want a random number between one and a million, you don’t want rand, you want randbetween.
• Cells that are drop down menus is another Excel trick that you see, but might not know how to do, like everything in Excel there is more than one way, the best way is to use a range, apparently a range generated by a formula.
• Option T is a keyboard shortcut in the Mac version of Microsoft Excel, you do know that Excel was originally a Mac only program from Microsoft right? I don’t know the windows shortcut, “option T” lets you cycle through the formula value locking down the column, row, or the entire cell. Mr. Excel explains how one character can make such a difference in an Excel formula.
• Match everything not being on at default when using lookup/vlookup is another of the great mysteries of Excel, it is probably turned off for speed, but debugging this error the first time you make it is no fun. One solution is to use the Index and Match functions together instead of vlookup.

I’m not sure I explained everything in the proper terminology, but then most people who use Excel don’t know the proper terminology, hopefully the links I chose helped. Excel can do a lot, but most people use it as the world’s most complicated to-do list, or they refer to it as a database.

Good Luck with all your future Excel problems, the answer is usually already online if you can enter the right query in Google or post the question to the correct forum. Stack Overflow came through for me when I was at CWT. If you have a favourite Excel tip, trick, or technique you can leave it below.

This entry was originaly posted on July 21st, 2014, it was last edited on May 18th, 2019 and is filed under: Technology and tagged: Microsoft Excel, Stack Overflow.