New Excel Functions: Power Query, XLOOKUP, & Others

New Excel Functions—for some of us, every Excel update is like Christmas morning. Others shudder at the thought of using Excel (though that probably means you’d have a tough time as a management consultant). Those who know the power of Excel know it offers much more in the way of simplicity than complexity. A little basic knowledge makes Excel one of the most valuable tools for every business owner and consultant. And the latest batch of new features for Excel makes it more functional than ever. Let’s take a look at some of the new things you can do with Excel 2019.

New Excel Functions

Power Query

Excel aficionados will remember the Power Query tool for data importing from Excel 2013, which was reconfigured as the Get & Transform tool in Excel 2016. In Excel 2019 it’s back as the Power Query tool. This makes the importing of data from external software a snap by giving you the ability to clean and reorganize the data before inputting it into Excel. You can choose how to group the data, edit values, merge and reposition rows & columns, auto-complete, and much more, all before making the data fit with your existing Excel files.

XLOOKUP

Excel has always given you the ability to search for items within your spreadsheets. Now the new XLOOKUP function enables you to narrow your search to a specific row within a specific table or range. This allows you to drill down search results much more quickly than with the standard search function.

Excel Data Model

The new Data Model feature is designed to work hand-in-glove with the Power Query feature. That feature gave you the ability to import and organize large quantities of data from external data management sources. The model enables you to put many large data sets into relation with one another within your Excel workbook, creating a multi-faceted system of relational data. You can customize how the model integrates visually with the rest of your workbook using PivotTables and PivotCharts.

Funnel Chart

The funnel chart is a kind of data visualization when a data set undergoes a gradual reduction from a total set into smaller portions of that original set. There are many business functions in which the funnel chart comes in handy. For example, you might want to drill down from total sales leads to created appointments, to kept appointments, to successful sales. A funnel chart would enable you to do this, and Excel now enables you to make funnel charts.

Excel 3D Maps

For a long time, Excel has enabled you to master the art of 2D data visualization. Now Excel gives you the ability to take your data into the third dimension with 3D Maps. You can chart data spatially onto a three-dimensional representation of the globe, or a customized map. You can also bring in the fourth dimension by charting your data’s transformation over time. This can create a highly compelling form of telling the story of your data, which Excel enables you to share with other people.

Excel Switch Function

The new Switch function is a tool for comparing an expression that you provide to a number of supplied values. The function will find the first value that matches the expression you’ve provided and replace the value with a different one. This can be used for a variety of value types, such as string values, numeric values, dates, and more. An example of a way to use this function would be to assign verbal values such as satisfactory, excellent, and poor to a series of numeric values from a survey. Using the Switch function, you could search the list of numeric survey results and easily reassign them the verbal values.

Excel Data Types

Excel is always expanding its array of data types. The two new data types in Excel 2019 are Stocks and Geography. These enable you to input values and convert them to these particular data types. Both Stocks and Geography are known as linked data because they have the ability to connect to online data, which easily enables you to transfer relevant, live-updated information from the internet into your Excel worksheet.

Natural Language Query

Many business owners with limited computer vocabularies will look at this new feature as the one they’ve been waiting their whole lives for. While Excel has always offered an intuitive way to input simple numeric values, editing and indexing that data has been more complicated. To do this, you used to have to learn how to create complicated formulas in Excel’s language. Now, Excel lets you use your own, natural language. You can ask Excel questions about your data in language that makes sense to you, and it will perform simple tasks and answer basic queries. For example, you can ask Excel to tell you which of your business’s products sold the most units over the last quarter, or which employee logged the most overtime.

Power Pivot vs Pivot Table

These two features sound similar but are actually totally different. The Power Pivot feature gives individuals the ability to perform many of the same complex analytic tasks as big data firms. Power Pivot helps you analyze almost unlimited data sets from multiple sources, in myriad ways, without requiring massive server capabilities.

Pivot Table, on the other hand, helps you create reports from data sets by breaking that data down into smaller categories—such as months, weeks, or quarters. Power Pivot is an add-on feature that uses a different calculation engine from the native Excel software, hence its lightning speed. You can use these two features together, analyzing massive data sets with Power Pivot and creating reports about it with Pivot Table.

Microsoft Excel Live Collaboration

Live collaboration on shared documents is one thing Excel had been missing in comparison with the rest of the Microsoft Office suite, as well as Excel’s rival, Google Sheets. Finally, Excel offers Live Collaboration. You can add other users and offer them the ability to view or even edit your document alongside you. There are two things worth noting here. One is that your files have to be stored in SharePoint Online or OneDrive to allow live collaboration. Two is that some users have reported bugs in actually seeing people’s work live as it happens. Some people have reported lag time between one user’s input and another’s ability to see it.

Will These New Excel Features Save Consultants Time?

Microsoft Excel’s whole ballgame is about simplifying complex business functions and making them more accessible. Some of the new features mentioned above will have already been squarely within the consultant’s wheelhouse. This includes the Natural Language Query feature. This feature will be a big help for lay users who were never able to learn the formulas required to make Excel’s functions work. But many sophisticated consultants have likely interiorized those functions. Still, many of the new functions will make the consultant’s job much easier. For example, being able to use Power Pivot to analyze massive data sets without outsourcing your analytics will make many consultants’ jobs easier. Likewise, the Funnel Charts and 3D Maps features will help consultants create eye-popping displays faster than ever.

For those of you who need banker-level Excel training, we can’t recommend Breaking into Wall Street’s course enough.

Concluding Thoughts

Microsoft’s Excel software is now older than many consultants and small business owners are. Still, it’s learning more all the time. Each new update brings with it an exciting new array of Excel features. The features rolled out in the last year give small business owners and boutique consultants the ability to keep up with the big boys. There are newer, faster ways to input, sort, index, and analyze data. And there are also ways to create beautiful, attention-getting displays of information. This can help transform simple numerical values into powerful narratives that tell the story you want your work to tell.

Additional Reading:

• Promote Job Openings to MC’s >1M-strong community
• Case Interview: Complete Prep Guide
• Consulting Resume: Complete Guide
• How Consultants Use Excel
• Fast Math: Quant Skills
• Amazon Leadership Principles