Let me tell you straight, Microsoft Excel is one of those useful applications which helped this world to progress, period. And if you are reading this, I’m sure you use it in one way or another way in your work.
So today in this post, I will share with you 7 Advanced Features of Microsoft Excel which can make you a PRO in your OFFICE
…so let’s get started.
Seven Features in Microsoft Excel that you need to Learn
1. AutoSave Option
There’s one thing that haunts all the Excel users in the world, that’s closing an Excel file without saving it. But Microsoft played it smart by introducing the “AutoSave” option.
If you are using Office 365 (Yes, it’s limited to Office 365), you have this “AutoSave” button in the top left corner.
So when you turn it on the first time, it asks you to do two things, one is to save the file in the new format and second is to save it in OneDrive folder (It’s always better to save all the files in OneDrive).
And once you do this, it saves your file automatically, every few seconds, as you work. But here’s the best part: You can go back to a specific version of a file and restore up to that or create a sperate file of it.
There’s an option “Version History” for this, where you can click and get the side panel to see all the version of that file which are saved by the AutoSave.
2. Ideas Button
If you work with a lot of data, create charts, and do analysis, then this new “Idea Button” can help you in so many ways. As the name suggests it gives you initial ideas about the data.
If you see, below we have a sample sales data for years and months:
And the moment I hit the Ideas button it gives me some recommendations on what could be the analytical outcomes of the data.
Let’s say if you want to create a pivot table of year wise sales, create a month-wise chart, or simply want to show the trend line.
Not just this, you can also ask questions about the data and get the answers. Isn’t it amazing?
3. Script Lab
Script Lab is one of the coolest things which I have seen in Excel, in recent days. Actually, it’s a separate add-on which you need to install from Microsoft’s App Store.
As I said you can test your code, there’s a run button that you can use and your code will be executed in a side panel. Isn’t it cool?
VBA is quite popular among the Excel power users and if you do a lot of repetitive tasks every day then you must learn VBA.
VBA is a coding language (and coding is power) that can help you create macros to automate all those small tasks which are repetitive and the best part is you can learn it using a VBA guide like this.
Here’s one more interesting thing, apart from writing codes you can also use the macro recorder in Excel which can record all the activities you do in your workbook and generate code out of it.
And you can run that code whenever you need it.
5. Power Query
Power Query is like a revolution in Excel and which can help you to crunch your data in no time. Basically, the power query is pre-added in the newer versions of Excel.
All you need to do is, open your data in the power query editor and there are a number of options that you can use to clean and transform your data.
But here’s the best part: When you create a query in power query it stores it and the next time you just need to refresh it and all your data will get transformed with that.
In simple words, it’s a one-time setup and real-time.
You heard about VLOOKUP, right? But now, it’s just a thing of the past. Microsoft has replaced it with XLOOKUP which is way more powerful than VLOOKUP.
The BIGGEST lack of VLOOKUP is it can’t lookup value to the left, but with XLOOKUP you can look up in any direction. Even with the XLOOKUP, the combo of INDEX and MATCH is no more required.
NOTE: If you are not a Microsoft insider user then you need to wait to use it as right now it’s not available for everyone, but it’s important to learn to use it in advance.
6. Dynamic Arrays
Before introducing dynamic arrays in Microsoft Excel, using arrays in Excel was tough (quite a bit). But with dynamic arrays, there’s no more need to use that Ctrl + Shift + Enter.
With dynamic arrays, there are some new functions that are introduced by Microsoft but the best thing the entire calculation engine has been completely redesigned.
Microsoft Excel is not stopping here, there are a lot of new options that are coming every month. But now tell me one thing, do you have a favorite Excel feature which you use? Share with me in the comment section, I would love to hear from you.