How many of these Excel power features do you use?
There are a handful of websites on Excel that I have found to be truly useful. Jon Acampora’s Excel Campus (www.excelcampus.com) is one of them. He recently had a short article on The 7 Wonders of Excel – Excel Campus, in which he discussed 7 features of Excel that he views as most important (Formulas, Table Filters, Pivot Tables, Power Query, Conditional Formatting, Power Pivot, and VBA). How many of these features do you use?
Formulas and conditional formatting are perhaps among the first features Excel users start to employ, and may be what they think Excel is all about. Indeed, easy-to-use formulas were what made spreadsheets a revolutionary tool when they first became available.
Tables & filters should be used more often and by more people than they are (as I discussed in this post: A Key Advantage of Tables). They simplify handling data, whether a few lines or tens-of-thousands of lines.
For analyzing data, pivot tables should be in most people’s tool box of Excel tricks. They aren’t as difficult to learn as some people think and pay back the time required to get familiar with them after only the first couple of uses.
VBA might be considered best left for geeks, although some basic scripts can usefully be written using just the macro recorder. It certainly has a place in the list of things you should become familiar with, and if you have some background with programming you will have less of a learning curve with it than others.
However, Power Query (and to a lesser extent Power Pivot) is one of the newer features that many people could benefit from exploring, especially if you regularly import information into Excel for analysis (or would like to). Database types often talk about the ETL process (extract, transform, load, meaning getting data from another source, converting it to whatever you need, then bringing it in to your program, such as Excel) and spend a lot of time on this process. Power Query (and Power Pivot where appropriate) allows you to automate this process and accomplish some amazing things with incredible ease. This feature has allowed me to greatly simplify some painful and time-consuming processes for clients, saving them hours every week or month.
All of these features have a place in your toolbox of Excel tricks and are worth at least understanding what they are used for, whether or not you perfect using them. When thinking about what to learn next, I would encourage you to think about what you spend the biggest part of your time in Excel doing, then consider which of these features might be useful for those purposes.
If you have a task that you regularly do that consumes a fair bit of your valuable time repeatedly doing the same things each week or month, feel free to contact me through the Contact Us page to see whether some of these features might help you save yourself time and grief.