

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.
Contents
Remove duplicate values with just four simple steps in Excel
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:
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.
Work on the same Excel file 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.
Hide Zeroes in Excel
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:
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.
Show formulas instead of values in Excel.
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:
Change the Autorecover period in Excel
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.
Insert a button into your 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.
After you’ve inserted a button, you can further customize it
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.