Connect with us

Education

7 Advanced Features in Microsoft Excel

Published

on

7 Advanced Features in Microsoft Excel

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 to 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.

It allows you to write JavaScript straight into your Excel application and test it there. It also allows you to write HTML and CSS along with the Javascript.

Script Lab Excel

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?

One of the finest examples of using Script Lab is creating a pivot table with Javascript in Excel. But there’s much more you can do with it + you can also create worksheet functions with Script Lab using JavaScript.

4. VBA

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 Editor

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.

macro-recorder-vba

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.

Excel Power Query

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.

6. XLOOKUP

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.

Excel XLOOKUP

https://www.cnbc.com/2019/09/01/microsoft-releases-excel-xlookup-feature-spreadsheet-jockeys-rejoice.html

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.

excel-dynamic-arrays

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.

Puneet Gogia is the Co-Founder of Excel Champs. He helped thousands of people to understand the power of spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

Continue Reading
1 Comment

1 Comment

  1. Pingback: 10 Excel Tricks That Entrepreneurs Should Learn - The Weekly Trends

Leave a Reply

Your email address will not be published. Required fields are marked *

Support Us!

Write for Us

The Weekly Trends

The Weekly Trends

Facebook

Trending