5 Excel Features I’m Excited To Use in 2020
As I sit down to write this article, there are now only a few days remaining in the year 2019. Can you believe another decade has passed us by?
Having personally entered the corporate world in mid-2011, I can truly say I have learned everything I now know about Microsoft Excel within this past decade. It’s pretty crazy to pause and think about my journey. From first understanding how to write a Vlookup formula to now running a full-blown educational website and selling Excel add-ins that I coded from scratch to companies all around the world (if you want to learn more about me personally, you can check out my About page).
As I sat down and planned out what I would like to write about next year, I thought it would be interesting to squeeze in one last 2019 post covering the up-and-coming Excel features that I am most excited to use and what I believe will shape the next 10 years of this incredible application’s history. Let’s dive on in!
#5. Integrated Stock Prices
I am a HUGE personal finance nerd and have been engrossed in tracking specific stocks and the performance of my various investment funds (whether it be 401K, Health Savings accounts, etc…). Needless to say, I was ecstatic when Microsoft announced they would allow Excel users to pull live stock price data directly inside our spreadsheet cells through their new Data Types functionality.
However, what excites me more was the (somewhat hidden) announcement that historical stock data would soon be available for us to pull natively inside Excel (see screenshot below).
Now, why would this be a game-changer? Having access to historical data is vital when forecasting anything, especially stocks. With this data available to Excel users, we can create (and share) our very own models that automatically analyze hundreds of stocks and flag ones we might want to invest in. It will also allow users to come up with their own portfolio strategies and backtest them to see if they are viable to use.
One might argue that such data is available to us already through third-parties, but getting this data for free and not having to import files constantly will be a huge win for personal finance fanatics. While it is not a feature I will likely use in the corporate world, it is something that I plan to utilize greatly in my personal time once it is available (hopefully it comes next year!).
#4. Power Query/Power BI
During the later have of this past decade, Microsoft has seemingly gone all-in on what they are calling their “Power” applications. These are largely cloud-based applications that can easily bring-in data from outside sources (whether it be databases or websites) and perform automated processes to give you a clean end result. One example could be taking survey results, marrying them with sales data, and compiling them into a monthly dashboard that is viewed on a mobile device by your Sales VPs. Having the ability to funnel in all sorts of data from applications and automating the cleanup and joining of that data is what these Power Apps are all about. This is clearly a skill businesses are going to be looking for in the near future and there are two specific Power Apps I think Excel users (myself included) can jump right in and learn fairly easily with a little bit of effort. Power Query and Power BI are the two areas I’m going to be focused on learning in 2020 and I see a large demand for folks with these skill-sets in the marketplace currently. If you haven’t heard of these applications, I’ll give you a high-level overview of what each application does.
What is Power Query?
Power Query is a data cleanup automation tool that has recently been embedded directly into Excel in the Get & Transform Data group within the Data tab. Essentially what you can do is connect to data sources or tables within Excel files and create clean-up rules that can all be done with the click of a button. You can choose to code these rules are use menus/forms to create them and order them in the steps you want to occur. Examples could be splitting first/last names into separate columns, performing calculations, deleting blank rows, and so much more. Once you have all your rules created, they can be stored and repeated on-demand whenever you want.
What is Power BI?
Power BI is a dedicated dashboard building application. It allows you to easy connect to third-party data sources like Salesforce, Google Analytics, and social networks to get real-time information. From there, you have access to many different dashboard modules (think charts and slicers) to build any sort of summary dashboard you can think of. You can then easily share your created dashboard across your company with all your data security settings applied (if someone only has access to a subset of the database, they only see that subset in the dashboard).
#3. Excel Ideas
The Ideas feature has been Microsoft’s boldest jump into the use of A.I. and Machine Learning within Excel. At its core, you can highlight a set of data and Excel will analyze it and come up with suggested visualizations (charts) and commentary to help you understand your data set. It’s a pretty novel idea and the fact that the algorithms are constantly improving themselves based on the data they are feed and how the users ultimately use the suggestions is something to get excited about.
Now, I don’t believe this technology will be replacing financial analysts anytime soon (there’s a reason it’s called “Ideas” and not “Answers”), but I see this as possibly giving us a second opinion while trying to analyze our data or potentially providing some automation in the analysis-creation process. It will be interesting to see how this technology progresses in the coming years and what the frequency of use by Excel users will be.
#2. Dynamic Array Functions
Do you remember wanting to write a complex formula, searching for it on the internet, and then copy/pasting it into your spreadsheet with the instructions of keying ctrl+shift+ enter afterward? Who knows why you had to do it, but by-golly it made the thing work! Well, that formula you found was an Array Formula. These types of formulas return multiple answers and hence needed curly brackets around them (auto-inserted with ctrl+shift+enter) in order to tell Excel what to expect.
In early 2019, Microsoft announced a new suite of functions that completely shifted the way we as users will write complex formulas. These new functions called Dynamic Array functions took the complex/lengthy formulas of yore and streamlined them into simple, dynamic functions that anyone would be comfortable using…..and the best part, now need to use the ctrl+shift+enter keyboard combination!
The first suite Microsoft released contained functions such as UNIQUE. Simply feed unique a range of cells and it will return every single unique value it finds into individual cells (essentially eliminating duplicate values). And even better, if your list of values grows or shrinks over time, the list returned by your UNIQUE formula will automatically adjust (hello dynamic drop-down lists!).
#1. The XLOOKUP Function
By the end of the next decade, no one will think twice about a Vlookup function unless they open up an ancient Excel file. That’s because in 2019 Microsoft unleashed the XLOOKUP function. XLOOKUP contains the functionality of VLOOKUP, HLOOKUP, IFERROR, and INDEX/MATCH, all in one streamlined Excel function. This is the function we all will be teaching our kids how to use prior to their first interview and it’s a function I can guarantee I utilize on a daily basis in the corporate world.
More on XLOOKUP:
What Are You Looking Forward To?
Well there was my top 5 list. I hope you enjoyed it and maybe I convinced you to slot some time in 2020 to explore some of these exciting new features. So, what are you excited for in your Excel educational journey? Are you hoping to become a master chart-builder, become a master VBA coder, or a dashboard guru? Let me know in the comments section below! I’d love to see what you’re looking forward to as we enter a brand new decade of Excel use.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.