The Top 5 Excel Functions to Make Work Easier

If you’re looking for ways to be quicker in the workplace, this is the function for you.

2. The Remove Duplicates Function

When dealing with large amounts of data, duplicates are often a problem as they can make lists longer than they should be. For example, a list of 100 items could actually only be 70 items long once you remove the duplicated items. The Remove Duplicates function is a built-in Excel tool that makes cleaning up this data quick and easy. So, to remove duplicates in Excel for a single column, follow these steps:

1. Select the data you’re looking to remove the duplicates of.
2. Click “Data” in the top menu of the Excel window to open the Data menu.
3. Click the “Remove Duplicates” button.
4. A window will pop up. If your column includes a header, make sure the box next to “My list has headers” is checked.
5. A window will pop up telling you how many duplicates were removed and how many remain. Click “Enter” to get rid of the window.
6. Excel will only remove exact duplicates. This means if an item is misspelt, both variations of the spelling will remain. For example, neither will be deleted if you have “Goat” and a misspelt version like “Gaot”.

The Remove Duplicates function is a great tool, but you may miss duplicates if they aren’t exact, or you could delete values you want to keep. So, make sure you have a clear idea of what you want to achieve when using it.

Related: Why Microsoft Office Skills Are Important for Job Seekers

3. The SUM Function

The SUM function is probably one of the best, if not THE best, functions for beginners to learn in Excel. This function is an excellent example of how Excel can simplify your otherwise manual calculations. SUM essentially just adds values. Using this function, you can either add individual values, cell references or ranges – or a mix of all three! For example, if you’re given the cost of 100 items bought for an event, you can find out the total cost of the event by using the SUM function. To create this formula, you must:

1. Type “=SUM”in a cell, followed by an opening bracket “(“.
2. To enter the first formula range, which is called an argument(a piece of data the formula needs to run), select the numbers you need. For example, if the numbers you’re adding up are between A2:A4 select cell A2 and drag through cell A4.
3. Type a comma “,” to separate the first argument from the next.
4. Type the second argument in, for example, C2.
5. Finally, type a closing bracket “)”, and then press “Enter”.

If you think you’ll struggle to remember this, the SUM Function also includes a shortcut:

1. First select the cell where you’d like the formula results to display.
2. Go to the Home tab and in the editing group click the “FX button”. To the left of this box, you will find a range of options.
3. Select “SUM” in the list to enter the SUM function into the cell to display the answer.
4. Highlight the cells which contain the numbers you want to find the combined sum of to enter these references as arguments for the function, then press “Ok” on the keyboard.
5. The number that appears in the cell you selected will be all the numbers you selected added together.

4. The AVERAGE Function

The AVERAGE function does exactly what it says on the tin. Yep, you’ve guessed it… it finds the simple average, or mean, of numbers in your spreadsheet. Like with the SUM function, Excel has a shortcut to enter the AVERAGE function, sometimes referred to as Auto Average due to its association with the AutoSum feature, which is located on the Home tab of the ribbon. To use the AVERAGE function, follow these steps:

1. Select the cell where you’d like the formula results to be displayed.
2. Go to the Home tab and hit the “FX button”. Then, select the AutoSum drop-down arrow.
3. Select AVERAGE in the list to enter the AVERAGE function into the cell you’ve chosen for results to be displayed.
4. Highlight the cells which contain the numbers you want to find the average of to enter these references as arguments for the function.
5. Press “Enter” on the keyboard.
6. The number that appears in the cell you selected will be the average of the numbers you selected.

5. The TRIM Function

When you copy text directly from a document into Microsoft Excel, it can occasionally come with characters you’d prefer to see removed, such as unnecessary spaces. Rather than wasting your valuable time removing these manually, use the TRIM function to do it for you. This function strips extra spaces from text and leaves only a single space between words and no space for characters at the start or end of the text. So, here’s how to use the TRIM function:

1. Select the cell/cells where you want to delete spaces. For example, select cell A1. In this cell, type ” Pitman” (without the quotations). Be sure to add a space or two before “Pitman” like we’ve done above so you can see the TRIM function in action!
2. Now click cell B1 – this is the cell next to A1.
3. Click the “FX button” and then select the AutoSum drop-down arrow.
4. Select “TRIM”.
5. Select cell A1. Now, cell B1 should contain the following formula: =TRIM(A1)
6. Click “Enter”. B1 should now have “Pitman” written in it without any additional spaces.

How Can Pitman Help You?

Whether you want to improve your career prospects with one of our highly respected diplomas, gain that promotion, get back to work or simply stand out from the crowd, Pitman Training is here to help. Learning how to use Microsoft Excel with Pitman Training gives you the skills to make work easier and shows employers that you’ve taken one of the best Excel courses available. Throughout this course, you will have the freedom to take the Excel lessons at your own pace, either online or based at one of our handy town or city centre locations, with support on hand should you need it. So, what’s stopping you from getting started? Give us a call for a free, friendly consultation with your local Pitman expert today.