5 Valuable EXCEL Features

Microsoft Excel has many outstanding features, which is why it is one of the world’s most heavily used applications. Here are five Excel features you may not know about that can make your Excel experience even more productive!

1. Auto Fill

Auto Fill allows you to take a formula or pattern that has been applied to one cell and apply that same formula or pattern to other cells. In the example below, we have used a formula to give Bob a 3% salary increase. We can then select the tiny green dot at the bottom right corner of the cell containing Bob’s new salary and hold the mouse down and drag it vertically until all of the other cells in the column are selected.

Picture1

Once we let go of the mouse, all of the remaining cells in the column will be populated using the same formula that was used to compute Bob’s new salary.


Picture2

2. Remove Duplicates

When analyzing or sorting through data, no matter what industry you are in, it can get chaotic and maybe even a bit messy. Depending on the way the data is imported, some sequences may be added more than once, resulting in duplication. Knowing how to remove copies of data segments is important so that the copies don’t skew the end amount.

To access the Remove Duplicates feature, tap on Data > Data Tools > Remove Duplicates. Make sure the proper dataset is selected. You can then click Remove Duplicates. You will be able to view the different columns, so ensure the “My data has headers” box is crossed off if the column names are failing to display. Simply click okay and you’re good to go.

3. Find and Search

These may seem like basic functions but they are integral and can save you tons of time. Also, it is good to know that Find and Search are not one and the same. 

To access Find, click the Search (Alt + Q) bar at the top of the sheet. This will give you three options, with the top one being Find. Click on it. Find will only return case-sensitive matches, whereas using the Search feature will give you broader matches. Search isn’t limited to case sensitivity and it will find the words in pieces of text too. 

Both of the features can make finding information in a spreadsheet much easier. 

4. Insert Function

The Insert Function button allows you to tell Excel what you are trying to do, and it will give you a list of likely functions to solve your problem. It will also walk you through how to enter the parameters needed for that particular function.

Picture3

For example, imagine you wanted to find the largest value in the list of current salaries in our previous example. You would type “Largest in a set of values” in the Search box and select “Go,” and Excel will return two possible functions that meet your needs. You can see exactly what the functions do at the bottom of the screen. Once you determine which of these functions you want to use, select the function and press ENTER.

Picture4


Once you press ENTER, you will be presented with the Arguments screen where Excel will walk you through each of the arguments that the function requires. The text at the bottom tells you exactly what needs to be entered for that argument.

Picture5

Once all the arguments are entered, just select OK, and your function will be entered into the appropriate cell! If you look at the formula bar above the data, you will see the function that Excel created for you:

Picture6

5. Conditional Formatting


Conditional Formatting allows you to graphically depict your data based on their values. You can use colors or icons to depict the data in different ways. For example, imagine we wanted to show the difference in people’s salaries. We could use the Data Bars type of conditional formatting as shown below so that the people with the highest salaries have the longest purple bars while those with the smallest salaries have the shortest bars.

Picture7


We could also use icons instead of just colors so that the people with the highest salaries have a green “up” arrow, while those with the lowest salaries have a red “down” arrow.

Picture8


There are many more options for conditional formatting; be sure to try them out until you find the one that works best for you!

Want to find out more ways your staff can make the most of Excel? Don’t hesitate to get in touch. For more workplace productivity tips, you can join our free training sessions where you will be able to learn about the latest techniques and tools to help your organization achieve proficiency.

Let’s Connect

Check out these resources:

Best 10 Features for Data Analysis in Excel (careerfoundry.com) 

Analyze Data in Excel - Microsoft Support