The 7 Wonders of Excel

Bottom line: Learn about 7 Excel features that will save you time, wow your boss, and help you grow in your career.

Skill level: Beginner

Introduction

If you're a member of one of our training programs you've probably heard me say that, “Excel is a tool with infinite possibilities and opportunities”.  It is packed with features that allow us to do everything from analyzing data to creating interactive applications.

My goal for this post is to share the seven most important features of Excel that will help you get your job done faster and impress your boss/users. 

“Excel is a tool with infinite possibilities and opportunities”

This, of course, is a highly subjective list.  It's difficult to pick just seven features.  But I believe these are the features that will make the most impact in your Excel projects and also get others asking, “Hey, how did you do that??“

#1 Formulas

Formulas and functions are the heart of almost every Excel file and financial model.  Excel's calculation engine is incredibly powerful, allowing us to perform complex calculations on thousands of data points in a fraction of a second. 

I remember the first time I copied a VLOOKUP formula down thousands of cells and saw all of the results appear instantly.  I was in awe of how quickly the task was done.  This would have taken me hours to find the matching results by manually scanning the sheet or using the Find window.  And that was 20 years ago!

Microsoft has made some amazing improvements to formulas in recent years with features like Dynamic Array Formulas (spill ranges) and Rich Data Types that allow us to store multiple data points in a single cell.

I believe formulas have a special place in every Excel user's heart.  With all these new features, we will never run out of things to learn with formulas.  It is a skill that we continually practice, learn, and improve upon for life.

#2 Tables & Filters

Excel Tables have become a vital part of most modern financial models or report files.  I like to refer to them as “a worksheet within a worksheet” because they keep our data contained and organized.  This allows us to create formulas, pivot tables, and reports that automatically include new data, which helps save time and eliminate errors.

I'm embarrassed to admit how many times I sent out a report that was not updated because I forgot to change the source range of a pivot table to include new rows. Ugh!  Excel Tables eliminate this problem because new data is automatically included.

Tables have a ton of features and advantages.  A few of my favorites are:

• Filters are automatically turned on for the table.
• Several design options to shade alternate rows, change colors, etc.
• The Total Row writes SUBTOTAL formulas for you when you select a calculation type (sum, average, count, etc.) from the drop-down.
• Structured reference formulas that allow you to reference an entire column(s) in your table and automatically include new data.

All of these features save us time when working with data and tying out numbers in our reports and analysis.

#3 Pivot Tables

Pivot tables are BOTH the simplest and most complex feature of Excel. 

What?  How does that make sense?

Well, I believe the name “pivot table” makes them sound more advanced or complex.  There is a lot of mystery behind that term that scares many Excel users away.  And I think it's a real shame.

Once you understand what a pivot table is and how easy it can be to use, you unlock a whole new world of Excel and data analysis.

Pivot tables are summary reports.

That's it!  Simple as that.

However, they are also much more than that because pivot tables allow you to create summary reports WITHOUT writing any formulas.  With simple drag-and-drop and a few button clicks, you can turn a big set of data into a summary report and interactive dashboard with charts and slicers (filters).

Pivot tables are also the gateway to business intelligence tools like Power Pivot, Power BI, Tableau, etc.  Understanding how pivot tables work is critical knowledge for those advanced data analytics tools.

#4 Power Query

In my opinion, Power Query is the most mind-blowing feature of Excel in the 2010s.  I have also seen this ring true for the hundreds of thousands of people that we've taught Power Query to.

Unfortunately, this is also a feature with a few complexities that can scare people away.  The name “Power Query” can feel a bit intimidating, and Microsoft has gone through a few name changes to try and make it more approachable. 

In my opinion, the user interface is even more intimidating and pretty difficult to figure out on your own.  You're probably not going to stumble your way through Power Query and make sense of it.

So what is Power Query?

Power Query is a data automation tool for preparing and importing data into Excel or Power BI.

Again, that's it!

To me, the key word in the sentence above is automation. 

A study by Forbes showed that 80% of a data analyst's time is spent cleaning & preparing data.  This is the grunt work of removing blank rows/columns, converting text to dates, splitting/combining columns, stacking or appending data sets, etc.

It ends up being the most boring and tedious work, and the place we tend to make the most errors as we get frustrated with the repetitive copy/paste and formatting tasks.

Power Query helps us completely automate this data cleanup work.  And just like pivot tables, you can do it all with simple button clicks in the Power Query Editor.  You do NOT need to write code.

It essentially allows us to create automations for very specific data cleansing and importing tasks.  Once you create the query, you can run it over and over again (refresh) as you get new data.

The data can be in an Excel file, CSV, database, website, and many other sources. 

We've heard so many stories from members of our training programs that were able to take a manual process that took them 4 to 8 hours and turn it into a 5-minute task with Power Query.

This not only frees them up to spend time on creating charts and dashboards (the fun stuff), but also reduces errors, stress, frustration, and overtime.

#5 Conditional Formatting

This one is probably the most questionable item on the list for me.  You might have another favorite feature of Excel that you would put in place of Conditional Formatting.

But I have it here for two main reasons:

Wow Factor

The first reason is Wow Factor.

Conditional Formatting allows us to dynamically change the formatting of cells based on a condition.  For example, you can use it for goal/targets and automatically shade a row green when a goal number is met and shade the row red if not. 

The awesome part is that the formatting will automatically change when the values in the cells change.

The possibilities are endless and it can save you a ton of time when you are preparing your reports.  It will also make the users of your files say, “Wow!  How did you do that??”

Ease of Use

The second reason is Ease of Use.

And by this I mean, Conditional Formatting can make it easier for your boss or co-workers to use and consume your Excel files.

Believe it or not, not everyone likes looking at a giant grid of text and numbers.  Excel files can quickly get overwhelming, leaving users confused about what to look at.

Conditional formatting can help solve this by bringing attention to the most important results on a sheet. This will save you and your users time, and reduce overwhelm.

#6 Power Pivot

Power Pivot is another one of the “power” tools that can feel a bit intimidating.  In my opinion, it is an advanced tool.

However, it is also extremely useful and can eliminate the need to maintain complex VLOOKUP formulas.

Power Pivot essentially brings the functionality of a relational database into Excel.  You will also hear it referred to as the Data Model. 

This feature allows us to create relationships between multiple data tables for more advanced reporting.  A good example is Actuals versus Budget reporting. 

If you have two or more tables that store your revenue and budget/forecast data, you can create pivot table reports that compare this data based on time period, department, or any other common factor.

Power Pivot also contains a formula language called DAX that allows you to write complex calculations across the tables in the data model.

I like to think of it as Pivot Tables on steroids. 

The possibilities become absolutely endless when you also consider the set of CUBE functions that you can use in Excel to create customized reports.

You don't have to be a mad scientist to use Power Pivot, but if you want to drive the Lamborghini of Excel then this is your playground.

#7 Extensibility

I probably wouldn’t be here, typing this post, if it wasn't for Excel's extensibility. Specifically macros & VBA. 

Extensibility is just a fancy word for the ability to program and extend the functionality of Excel.

We now have several options for this including VBA (macros), JavaScript APIs, OfficeScript, and more.  Some might even put the new LAMBDA function in this list.

For me, this programmability of Excel is what makes it truly limitless.

I have written thousands of macros and created hundreds of features in our Add-ins to automate processes in Excel.  Extensibility allows us to create features in Excel that don’t yet exist in the product. 

For example, I originally created my PivotPal add-in so I could have a search box for the fields list.  Something that didn't yet exist in Excel. 

My Tab Hound add-in's core functionality is the ability to search for sheet tabs in the workbook and quickly navigate to them.  I've also created several add-ins that allow you to create custom keyboard shortcuts for formatting and paste special operations. 

The point is, if you have an idea to improve Excel based on a specific problem you encounter frequently, you can create a solution for it WITHOUT waiting for Microsoft to develop the feature.

How cool is that!?!

What About Charts??

My friend and fellow Microsoft MVP, Jon Peltier (PeltierTech.com), kindly pointed out in the comments that I missed a big one. I originally grouped charts with pivot tables, but I think he is right that charts deserve a category all their own.

Charts allow us to quickly visualize data in Excel.

This is another feature that makes our spreadsheets much easier to read/consume.

Charts provide us with endless possibilities. There are tons of techniques we can use to customize charts to create just about any visualization we can think of. Here's a pic with a few of my crazy creations over the years.

Microsoft has also given us additional ways to visualize our data in recent years with new chart types like Waterfall, Map, Funnel, Histogram, and more. Power BI, although not a direct feature of Excel, has a ton of chart types and even ways to develop your own.

Needless to say, data visualization is a very important part of Excel.

As I mentioned at the beginning, this is a highly subjective list, and we really appreciate our Excel Campus community sharing your thoughts and making it better. Thanks again!

Explore the 7 Wonders

I had the honor of being on a call with Brian Jones recently, the head of the Excel Team at Microsoft, and he was talking about how they see Excel as a platform for building applications.

“Excel is a platform for building applications”

I couldn't agree more and I love that Microsoft continues to see Excel this way.  The new features that they continue to release every month show their dedication to this philosophy. 

These features give us more freedom to be creative in developing applications (spreadsheets) that save us time, automate processes, eliminate boring work, and provide opportunities for growth.

So I encourage you to invest time in exploring and learning these amazing features of Excel.

Free Training Webinar

I also know that these 7 wonders can be a bit intimidating. You might be wondering how the different features work together or which ones you should learn based on the work that you do.

If so, then we have a free webinar called The Modern Excel Blueprint that will help get you started.

During the webinar, I explain what each of these features does and how you can use them to get your job done much faster (and with less frustration). I also explain how to become the Excel Hero of your organization. Hint: It does NOT require learning all of the 7 wonders.

The webinar is free and it's currently running on multiple days & times. So pick a time that works for you and join us.

Conclusion

Some of these 7 wonders will likely change over time, and I look forward to updating the list as they do. But I hope this has helped you see some of the amazing possibilities with the electronic graph paper program (aka Excel) that we all love (or pretend to hate) so much. 😉

Please leave a comment below with your thoughts, and let us know if your top 7 features differ.