Apr 18, 20224 min read

Advanced Microsoft Excel Features You Must Know

While Microsoft Excel has been around for the longest time, it has plenty of features that you probably know nothing about. Even Microsoft experts can tell you that every day is a learning day with Excel.

So what are some of these wonderful advanced features that you have been missing out on? In this article, we'll explore six advanced Excel features you must know and how they can help increase your productivity.

1. Fuzzy Matching

Fuzzy matching is a Microsoft Excel productivity feature that allows you to check through related items across different lists and merge them if they are approximately similar.

For instance, if you are carrying out a survey and some entries have typing errors, a fuzzy match will count them together with the correctly spelled entries as long as the spelling remains as close as possible to the correct one.

To make your entries even more accurate, you can set your preferred level of Similarity Threshold to your fuzzy match. However, you should know that the fuzzy matching feature will only work on text columns.

Here’s how to get your fuzzy match-up:

First, make sure you have Fuzzy Lookup installed and enabled on Excel.
The next step is to turn your list into a table. You will achieve this by highlighting your list and pressing Ctrl + T.
Once you do this, you will see the Fuzzy Lookup Tool appear on the taskbar.
Now, select which of the converted tables you want to compare.
You will then see a pop-up asking you to select the Similarity Threshold you want for the comparison—a threshold of 0.85 isn’t so bad.
Lastly, choose a cell where the Fuzzy Lookup Table will be inserted, and click Go on the Lookup Tool to complete your comparison.

2. New Window and Arrange

If your work requires you to deal with multiple worksheets, then this is a feature you will find helpful. Microsoft Excel allows you to open various windows and arrange them as you wish for ease of access.

With all the tabs you need open, and in your view, you can save yourself the time and hassle of shuffling between multiple windows. It will also go a long way in minimizing errors and confusion.

To use the arrange feature, here are the steps you need to follow:

Open your desired workbooks, then click on the worksheets you want to open.
Click on the View tab, then Arrange All.
Select the option that best suits you on the dialogue box that appears.
Click OK.

3. Text to Columns

So you have been making changes to your worksheet when suddenly you have to split the data from one column into different ones. Say you want to have the first and second names in two separate columns. It seems hectic, right? Fortunately, you do not have to copy-paste your data cell by cell with the text to column feature.

It allows you to separate the texts in your columns using a delimited width or a delimiter such as a comma, hyphen, or space. So if the entries in your column have any of these, the feature will enable you to shift part of its data to a new column.

Here is how to use the text to columns feature:

Click on the column that has your intended text.
Click on Data, then select Text to Columns.
A dialogue box will appear showing the Convert Text to Columns Wizard. Click on Delimited > Next.

Choose the Delimiters that apply to your data. This may be Space or Comma.
Once done, click on Next and choose the data Destination on your worksheet.

Then click on Finish.

You should note that this feature is also practical for other data, such as dates or serial numbers. The key is to ensure that your delimiters are all in the correct place before you try to split the data.

4. Import Statistics From Websites

Transferring data from a particular website to your Excel sheet can also be a pain point, but not anymore. With the Import Stats feature, it’s all so simple. Here's what you need to do to get this feature up and running.

First, you need to open the Excel sheet into which you want to import data.

Click on File, and on the drop-down menu, select Open. Then choose Add a place. Once you do this, a dialogue box appears asking you which files you want to import to Excel.

Go down to the question bar, add the URL of the site you want to import data from, and click Open. It will take a second or two, and then another dialogue box will open asking you to input your Windows security key. This will happen if the PC you are using has a login password when you start Windows.
So enter your Windows username, the login password, and click Okay.

5. Remove Duplicates Feature

The last thing you want when dealing with data is redundancy. Unfortunately, after transferring data from one column to another or from a different site, you may run the risk of having things replicated. To avoid this, you can use the Remove Duplicates feature in Excel.

Select the table, then proceed to the Data tab to get this done.

Here, click on Remove duplicates. This will prompt a window to open. The pop-up window will ask which columns and rows you want to scan for duplicates. Input the necessary columns and rows, and Excel will clear your copies.

6. Custom Lists

Custom lists are an efficient way of avoiding tedious data entry and the risk of errors. Creating a custom list ahead of time allows you to add a drop-down selection or use Excel’s autofill feature, thus saving you time.

A few examples include a custom list of products, employees, or locations. You can also use Excel’s custom lists, such as the days of the week or months of the year. To use Excel’s autofill feature, you can simply type one word and drag the fill handle to complete the rest.

You need to follow the steps for custom List