Microsoft Excel, a commercial spreadsheet application allows calculations, provides graphing, tools, macro programming and pivot tables. You can use formulas for not only calculation but also to help you in other ways. In order to find the unique entries in Excel, a certain formula has to be used which helps in excluding counting of duplicate data entries.
Follow this step by step tutorial to learn how to count unique entries in Excel.
Step # 1 – Put in the formula
Let’s suppose, we want to know how many departments are there in our company just by looking at the sheet or in how many states do we have our offices in. It might take very long if you have a large amount of data and counting manually may take ages. We have a function which will help you to count these unique entries.
So at the top of the sheet, insert a new row and type
Is equals to sum bracket open 1 forward slash bracket open count if bracket open B3 colon B37 comma B3 colon B37 and then bracket close three times
“b3” is the first cell that has data in it and “b37” is the last. After typing “b3” in the function, you can press “shift + ctrl + down arrow” to automatically enter the last cell name in the function.
Step # 2 – Calculate unique entries
After you have typed in the function, press “ctrl + shift + enter” and the number of unique data entries will be calculated. You can drag the function to other columns as well.
Step # 3 – Add some new unique entries
Let’s see what will happen if we add three new departments to the list. The function will re-calculate and it will show a total of 10 departments now. This means that within the specified range, the number of unique entries gets re-calculated whenever you make a change in it.
Now you can count the unique entries in Excel easily to get a clearer view of your data.
Home