Since its launch in 1985, Microsoft Excel has become one of the most important programs for businesses worldwide. In fact, in an interview with the Irish Times, Microsoft’s CEO Satya Nadella called Excel "Microsoft’s most important consumer product", pointing out that it had over 750 million users. However, most people think of Excel as that program they learnt the basics of in school - in reality, it’s so much more. Excel can do anything from helping financial analysts and accountants create graphs, pivot tables and storing important data to assisting teachers in keeping attendance and making lesson plans. So, because having a working knowledge of Excel is vital for almost all office-based professionals today, we thought we’d help you learn five key functions to make your life easier.
So, what are the benefits of learning how to use Microsoft Excel? Well, properly understanding Excel can make you better at your job (no matter what that is). Not only can proficient Excel skills help you organise and analyse business data, which aids your decision making and management, but it’s also a great time-saving tool. For example, instead of adding up 127 columns of monthly expenses yourself, use Excel. It’ll add everything up for you, and you’ll know it’s correct. In fact, there’s a lot more to the program than you probably even realised, and there’s a use for it no matter what you do. So, here are the five functions you simply must know to make your work life easier.
1. The VLOOKUP function
The VLOOKUP function is perfect if you need to find information in a large spreadsheet or you're always looking for the same kind of information. To put it simply, it looks at a table in the first column for a specific value and returns a cell in the same row where you choose the column to return from. One of the benefits of VLOOKUP is that you can find data automatically instead of wasting your time doing it manually. So, here’s how you use the VLOOKUP Formula:
- First, identify a column of cells you’d like to fill with new data.
- Then select "Function" (the FX button) and type "=VLOOKUP" and insert this formula into your highlighted cell.
- Enter the lookup value for which you want to retrieve new data.
- Enter the table array of the spreadsheet where your desired data is located.
- Enter the column number of the data you want Excel to return.
- Enter your range lookup to find an exact or approximate match of your lookup value.
- Click "Done" (or "Enter") and fill your new column.
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:
- Select the data you’re looking to remove the duplicates of.
- Click “Data” in the top menu of the Excel window to open the Data menu.
- Click the “Remove Duplicates” button.
- A window will pop up. If your column includes a header, make sure the box next to “My list has headers” is checked.
- A window will pop up telling you how many duplicates were removed and how many remain. Click “Enter” to get rid of the window.
- 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.
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:
- Type "=SUM"in a cell, followed by an opening bracket "(".
- 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:A4select cell A2 and drag through cell A4.
- Type a comma "," to separate the first argument from the next.
- Type the second argument in, for example, C2.
- 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:
- First select the cell where you’d like the formula results to display.
- 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.
- Select "SUM" in the list to enter the SUM function into the cell to display the answer.
- 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.
- The number that appears in the cell you selectedwill 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:
- Select the cell where you’d like the formula results to be displayed.
- Go to the Home tab and hit the "FX button". Then, select the AutoSum drop-down arrow.
- Select AVERAGE in the list to enter the AVERAGE function into the cell you’ve chosen for results to be displayed.
- Highlight the cells which contain the numbers you want to find the average of to enter these references as arguments for the function.
- Press "Enter" on the keyboard.
- 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:
- 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!
- Now click cell B1 - this is the cell next to A1.
- Click the "FX button" and then select the AutoSum drop-down arrow.
- Select "TRIM".
- Select cell A1. Now, cell B1 should contain the following formula: =TRIM(A1)
- 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.