Learn 6 Great Excel Functions Most Users Don’t Know!

Most Excel users only use a small number of the functions in Excel. There are a lot of extremely useful features, but even many advanced users don’t know all of them. In this article, we introduce you to six great Excel functions, which are hardly known.

1) Remove duplicate values with just a few clicks

Let’s assume you got a list of items. You are not sure, if any of the items is on this list twice. What do you do?

Probably the fastest solution: Remove Duplicates. This function will create a “clean” list, in which every item only appears once. Just follow these simple steps:

1. Select your list.
2. Click on the “Remove Duplicates” button in the center of the data ribbon.
3. Make sure your data is still selected correctly (Excel tries to identify the correct range). Confirm with OK.
4. Now your list is reduced to the unique items.

Please note: In some languages the “Remove Duplicates” function is not working correctly. For example in German, the characters “ß” and “ss” will be interpreted the same. Excel will only keep one of them although in other functions those letters won’t be regarded the same.

2) Show the same workbook in two windows

You are working on two Excel sheets simultaneously? There is a very useful function: Show the same workbook in two windows. You can even display it on two different screens.

Just navigate to the “View” ribbon and click on “New window”. Now the same workbook is shown in two windows which you can arrange as desired.

3) Hide zero values

In many cases, too many 0 (zeroes) make a worksheet look quite messy. In such case, you might want to hide all zeros. There are three ways of hiding zeros:

• Use conditional formatting. Therefore, create a new rule and apply it to all cells you want.
• Change the number format as shown in the image above. 
  • Select all cells you want to hide zeroes from.
  • Open the format cells window by pressing Ctrl + 1 on the keyboard.
  • Copy this code 0;–0;;@  into the “Type” box on the Custom tab.
  • Confirm with OK.
• Change it within the worksheet settings.

All three methods are described in detail in this article. Expert tip: Try ‘Professor Excel Tools‘. The Excel add-in provides two very easy names for hiding zeroes.

4) Show formulas instead of values

Check your formulas and find errors: Usually, Excel shows the results of formulas in cells. Only when you select a cell, you can see the underlying formula. But there is a way to display all formulas:

• Click on “Show Formulas” on the “Formulas” ribbon.
• Alternatively press Ctrl + ` on the keyboard.

5) Change the Autosave period

Very troublesome: Excel crashes. For some workbooks more often and for some workbooks less often. What can you do to avoid loosing too much time? You can define the autosave period.

Excel provides a function to automatically save your file in a backup location. By default, Excel does that every 10 minutes. For some cases this period is too long. So maybe you’d like to shorten this period.

Therefore click on “File” and then on “Options”. Within the “Save” tab you can change the AutoRecover period. Please be aware, that especially for large workbooks, this can slow down the performance.

Also useful: Set the tick of “Keep the last autosaved version if I close without saving”.

If you need more assistance with recovering lost data, please refer to this article.

6) Insert buttons into worksheet

You like to impress your colleagues? Or just want to use an easy way to work with your worksheet? Why don’t you add buttons to your worksheet?

Let’s take a look at an easy example: You prepare an Excel file and your colleagues should use it. In order to provide a simple way of switching between years and months you use buttons as shown on the image.

How to add buttons:

1. Click on “Insert” on the “Developer” ribbon.
2. Select a spin control. Please feel free to test the other buttons as well.
3. Insert the spin control by clicking on the worksheet. You can easily change the size and position per drag and drop on the corners of the button.
4. Right click on the button and click on “Format”.
5. Now you can customize the button. For example, set a minimum and maximum value. Or choose the step width (“Incremental change”).
6. Important: Link the form control to your desired Excel cell. Confirm with OK.