Boost your Excel-experience with these 11 functions

At the end of the third quarter of last year, Microsoft Office 365 had 200 million users. One of the most used Office applications is Microsoft Excel. In this article, Global Knowledge listed 11 useful Excel functions you could use to boost your Excel knowledge.

According to the Harvard Business Review, Excel has more than 500 different functions. It's hard to select the most useful functions for your situation from such a big variety. Which functions are essential for every Excel user?

1. Paste special

Shortcut: CTRL+ALT+V

Paste special let's you choose which data you want to paste. For example, when using paste special, you could choose to paste only the values. With shortcut ALT + E + S + V you'll paste only the values straight away. Wondering how this works exactly? This video explains it all.

2. VLOOKUP

Syntax: (value, table, col_index, [range_lookup])
You can use the function VLOOKUP when you want to use certain information again in your table. You've used this bit of information before and you don't want to re-enter it in the table. This function is also useful to add information from one column to another column. At Global Knowledge, we use VLOOKUP to transfer the description of a training course from one sheet to the course code on another sheet.

It works as follows. The function searches in the first column of the table of your choosing for the value you're looking for. As soon as this value is found, a value is retrieved from a cell on the same row. You choose this row with col_index.

3. INDEX-MATCH

Index-Match is a combination of the Index and Match functions. This function is a step up from VLOOKUP and it's ideal when you're working with a large dataset. Index and Match helps you find the data you need efficiently in a large dataset. With VLOOKUP, it's only possible to look for values from left to right and the value you're looking for has to be on the left of the table you're looking at. With Index and Match, it's possible to search everywhere in the table.

How does that work exactly? Watch the video below to find out.

4. Pivot Table

Shortcut: ALT + N + V. With this shortcut you can create a pivot table from the data you've selected. Need a more extensive list of different shortcuts? You'll find it on this page.

You can use a pivot table to analyze, calculate and summarize data. A pivot table is useful to discover comparisons, patterns and trends in your data.

First of all, select the cells you want to use in your pivot table. Done? Click on insert, then pivot table. Under Choose the data that you want to analyze, select Select a table or range. After this you verify the cell range in Table/Range. You can either place the pivot table in a new or existing work sheet. This summary is a shortened version of the description by Microsoft. You'll find the full description, including images, on their webpage.

Rather learn by watching a video? In this video, you'll learn how to build a pivot table in no less than 6 minutes.

5 Flash fill

Shortcut: CTRL + E

Flash fill automatically fills your data once it recognizes a pattern. Office uses the example of a work sheet with three columns. One for the first name, one for the last name and the last column for both names combined. With flash fill, Excel fills the last column automatically, based on the data in the first two columns.

You turn on flash fill with the shortcut CTRL+E. You can also decide to do this manually: tools -> options -> advanced -> editing options. Once you're there, simply turn on flash fill.

6 Add multiple rows

Do you want to add multiple rows to Excel? Click on the heading of the row you want to add multiple rows next to. Hold down CTRL, click the selected row and on the menu that pops out. On the menu, simple press insert.

7 Use F4

Shortcut:: Press F4.
F4 simply repeats the thing you last did in your worksheet. It doesn't matter what you're doing. Let's say for example you've added something - press F4 and Excel just repeats your action.

8 Remove duplicates

There is no short cut for this function
Sometimes duplicates make it more complicated to interpret data the way it should be interpreted. If you search for duplicates and mark them afterwards, it becomes easier to delete them.

Select the cells you want to check for duplicates. After you've done this, click home -> conditional formatting -> highlight cells rules -> duplicate values. You'll see a box that contains 'values with'. Next to this text, pick the formatting you want to use to the duplicate values. Afterwards. press ok. This process helps you select the duplicates.

After you've selected the duplicates, you can decide to remove them. Select the cells with the duplicates you want to remove first. Then click data -> remove duplicates and under columns you decide which duplicates you'd like to remove. Click ok, and you're done. If you'd like to read this explanation with images, check out this Microsoft Office webpage.

9 Co-authoring

Co-authoring makes it possible for you and your colleagues to work in the same document. You'll see changes quickly and that makes it easier to fill each others' gaps.

If you already use a version of Excel that supports co-authoring, you can select Share in the upper right corner of your screen. This makes it possible to share your document via e-mail and it's saved in the cloud.

10 Navigate using CTRL and the arrow keys

Are you always scrolling through endless amounts of data? With CTRL and the arrow-keys, it's a lot easier to scroll from beginning to end of a dataset.

11 Start using AutoSave

With AutoSave, your documents are saved automatically. Say goodbye to constantly pressing CTRL + S to save your document. Excel does this automatically when you turn on AutoSave.

_{The following sources were used to write this article: Harvard Business Review, Mental Floss, CNBC, Microsoft By The Numbers, Computerworld, Easy Learning, multiple pages from Office Support, Avantix Learning.}