How to Find & Remove Duplicates in Excel Quickly

How To Find & Remove Duplicates in Excel (Step by Step , Video Tutorial)

With thousands of rows in today’s spreadsheets, it is virtually impossible for anyone to spot a duplicate value or duplicate row in Excel visually or manually. But you’ll be getting the wrong picture, and your analysis will be wrong until you remove the duplicates. So it is extremely important to De-Duplicate your Data carefully in the Excel sheet, before you begin your data analysis.

Fortunately, since Microsoft Excel 2007, it has been extremely easy to find Duplicates, and Remove them in a single click.

In this step by step article, and a tutorial video on How to Find Duplicates in Excel, and How to Remove Duplicates in Excel Sheet, you’ll learn a couple of hidden gems of Excel features and functionality.

With Office 365, and all the way back to Microsoft Excel 2007, there has been steady enhancements in different Excel versions in terms of finding and tracking duplicates, and even removing them.

Now it is extremely easy to spot duplicates, and there are multiple ways to find duplicates or eliminate duplicates from your Excel workbooks and worksheets.

Let’s look at How to Find Duplicates in Excel first. Then we will see how to remove duplicates in Excel sheet. And we will also show you Excel formulas to remove duplicates. Use any of these methods to quickly remove duplicate cells in Excel.

Step 1: Spot a duplicate value, and get it to change its color automatically

In this simple and easiest method to find duplicate rows in Excel, we make use of Conditional Formatting in Excel, available on the Home Tab.

  • Simply select the data range where you want to spot any duplicate values, and then click on Conditional Formatting. A drop down menu of choices appears. Select the first choice – Highlight Cell Rules. Then select the Last option –Duplicate Values. The short cut key combination is Alt +H L H D.highlight_cells_duplicate_value

Step 2. Select your favorite color to highlight the duplicate values.

Here I have chosen the Light Red color.duplicate_values_popup

 

Step 3. Both the duplicate values are now highlighted.

finding duplicate values in Microsoft Excel

There you are. A quick and nifty way to find the duplicate values in any column, quickly.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!

This method uses the EXACT duplicates to highlight.

If you want to show partial matches in terms of duplicates or similar to duplicates, you can use another interesting technique.

Using Conditional Formatting in Excel to Find Partial Matches

Here we use the “Text That Contains” setting under Conditional Formatting > Highlight Cell Rules >

With this setting, and keying in any value, it will be able to find Partial Matches of values.

Conditional Formatting - Text That Contains to Identify Partial Matches
Conditional Formatting – Text That Contains to Identify Partial Matches

I am searching for names that contain the word “Ram”.

Partial Match in searching for Duplicate Values in Excel
Partial Match in searching for Duplicate Values in Excel

As you can see, Ram Kumar was highlighted, due to Ram, but Krish was also highlighted, because the full name contains the word “ram” in the last name, Venkataraman.

This way, you can pick partial matches to be highlighted in Excel, using Conditional Formatting technique.

And if you want to do it again and again with different values to find, it is better to set a cell to the value being found. Then set the Conditional Formatting to search for the value in that cell.

I have demonstrated this in the video on How to Find & Remove Duplicates in Excel. Click to watch it and see how this is done properly. This guided video will help you in identifying and eliminating duplicates easily.

How to Remove Duplicates in Excel?

We will talk about another nifty way to eliminate duplicates in Excel, once for all, in the next article.

If you were to head to the Data Tab, and look under the Data Tools group, you’ll find the “Remove Duplicates” button hiding there. Some versions of Excel show a small icon, others show the full word, and some versions have a huge icon, and the words “Remove Duplicates” visible in full.

Check carefully to identify the button in your version of Excel. But all Excel versions since Excel 2007 have this button to Remove Duplicates.

STEPS TO REMOVE DUPLICATE ROWS IN EXCEL

With this option, simply select the entire data range, and then click on this “Remove Duplicates” button. It will identify rows that are EXACT DUPLICATES, and simply remove them.

Then Excel will notify you that X number of rows containing duplicates were found, and Removed.

However, this Remove Duplicates button click WILL NOT show you which records were removed. 🙁

Some people find this feature a bit of a risk. Thus, it is better to use Conditional Formatting technique described earlier to SEE the Duplicates visually, BEFORE REMOVING them.

With these 2 techniques, you can FIND the Duplicates, SEE them visually, DOUBLE CHECK that the correct ones are highlighted, and then only REMOVE the Duplicates.

Better Safe than Sorry!

How to Compare Two Excel Sheets for Duplicates and Highlight the Duplicate Data

Comparing two Excel sheets for duplicates and highlighting the duplicate data can be done using a few different methods. Here’s a step-by-step instruction for a couple of common approaches:

Method 1: Using Conditional Formatting in Excel

  1. Open Your Excel Workbook: Make sure both sheets you want to compare are in the same workbook.
  2. Select the Range in the First Sheet: Click on the first cell of the range you want to compare and drag to select the entire range.
  3. Go to Conditional Formatting: On the Home tab, in the Styles group, click on ‘Conditional Formatting’.
  4. New Rule: Click on ‘New Rule’.
  5. Use a Formula to Determine Which Cells to Format: Select this option.
  6. Enter Comparison Formula:
    • Suppose you are comparing Sheet1 to Sheet2. The formula will look something like this:
      =COUNTIF(Sheet2!A:A, A1)>0

      This formula assumes you’re comparing column A of both sheets. Adjust the range (e.g., A:A or B:B) and cell (e.g., A1, B1) to suit your data.

  7. Set Format: Click on ‘Format’, choose a highlighting color under the ‘Fill’ tab, and click OK.
  8. Apply and OK: Click OK to apply the conditional formatting. Duplicates will be highlighted in the chosen color.
  9. Repeat for the Second Sheet: Now, do the same for the range in the second sheet you’re comparing.

Method 2: Using Excel’s ‘Remove Duplicates’ Feature

  1. Copy Data to a New Sheet: If you want to keep the original sheets unchanged, copy the data from both sheets into a new sheet, one below the other.
  2. Select the Combined Data: Click on the first cell and drag to select the entire range.
  3. Data Tab: Go to the ‘Data’ tab on the ribbon.
  4. Remove Duplicates: Click on ‘Remove Duplicates’.
  5. Choose Columns: In the Remove Duplicates dialog box, choose the columns you want to check for duplicate information, then click OK.
  6. Duplicates are Removed: Excel will remove duplicate rows and show a message indicating how many duplicates were removed and how many unique values remain.
  7. Highlight Original Data: You can then manually or using conditional formatting (as described in Method 1) highlight these duplicates in your original sheets.

Using Excel Formulas to Find Duplicate Data in Excel Sheets

The VLOOKUP, INDEX, and MATCH functions in Excel can be very useful for identifying duplicates across sheets or within a sheet.

However, it’s important to note that these functions can identify duplicates, but they don’t directly remove them. You should generally use these functions in combination with conditional formatting to highlight duplicates or with other Excel features to remove them. Let’s go through how to use each function for identifying duplicates in Excel Sheets:

VLOOKUP to Identify Duplicates

The VLOOKUP function searches for a value in the first column of a range and returns a value in the same row from a specified column.

  1. Select a Cell for the VLOOKUP Result: Click on a cell where you want to display the result (e.g., next to your data).
  2. Enter the VLOOKUP Formula:
    • Example formula: =VLOOKUP(A2, Sheet2!A:A, 1, FALSE)
    • This formula checks if the value in A2 of the current sheet exists in column A of Sheet2.
  3. Copy the Formula Down: Drag the fill handle down to copy the formula to other cells.
  4. Identify Duplicates: If VLOOKUP finds a match, it will display the matching value; if not, it will show an error. You can use conditional formatting to highlight cells where VLOOKUP doesn’t return an error, indicating a duplicate.

INDEX and MATCH to Identify Duplicates

The combination of INDEX and MATCH is often used as an alternative to VLOOKUP. MATCH finds the position of a value in a range, and INDEX returns a value at a given position in a range.

  1. Select a Cell for the Result: Choose where you want the result to appear.
  2. Enter the INDEX-MATCH Formula:
    • Example formula: =INDEX(Sheet2!A:A, MATCH(A2, Sheet2!A:A, 0))
    • This formula searches for the value in A2 of the current sheet in column A of Sheet2 and returns the corresponding value.
  3. Copy the Formula: Drag down the formula as needed.
  4. Highlight Duplicates: Use conditional formatting to highlight cells where INDEX-MATCH returns a value, indicating a duplicate.

Removing Duplicates Using a Formula

While there’s no direct formula to remove duplicates cells in Excel, you can use a combination of functions to create a list that excludes duplicates. One common approach is using the IF, COUNTIF, and an array formula.

  1. Create a New Column: In a new column next to your data, you’re going to enter a formula that will flag duplicates.
  2. Enter the Array Formula:
    • Example formula: =IF(COUNTIF($A$1:A2, A2)=1, A2, "")
    • This formula checks if the current value in column A has appeared before. If it’s the first occurrence, it shows the value; otherwise, it shows an empty string.
  3. Enter as an Array Formula: After typing the formula, press Ctrl + Shift + Enter instead of just Enter. This converts it into an array formula.
  4. Copy Down the Formula: Drag the fill handle down to apply this to your entire data set.
  5. Filter or Sort: Now, you can filter or sort this new column to separate unique values from duplicates.

Remember, these methods will help you identify and segregate duplicates, but to actually remove them from your data, you might have to do some manual data management or use the “Remove Duplicates” feature in Excel that we discussed earlier.

And this brings us to a close of this article. I hope that with these techniques, you can pick up duplicates in your data in Excel or remove them, as you wish.

PRO TIP: Do remember to watch the Video, for how to identify values that start with a particular Text, or End with a particular text. It is highlighted in the Video in detail, and can be very handy.

Do you have a Duplicates related question in Excel?

Simply write to us, and our Excel experts will try to help you out. Write to us at the Contact Us page on this website.

You may find these resources useful:

Cheers,
Vinai Prakash,
Founder of ExcelChamp.Net & Master Trainer for all Excel Courses at ExcelChamp.

Visit our ExcelChamp Channel on YouTube for more Excel Tips & Tricks.

Vinai is passionate about maximizing Excel to improve everyday productivity. He conducts Excel workshops in Singapore, Australia, Middle East. If you would like to engage him to train your staff in maximising Excel, do contact us.

And join our Online Excel courses to learn Excel well, and become a Pro.

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

Master Excel Lookup Functions like VLOOKUP, HLOOKUP, INDEX, MATCH, OFFSET

How to Refresh a Pivot Table (Manually or Automatically)

Top 12 Most Important Excel Functions You Must Master

Top 12 Most Important Excel Functions & Features You Must Know

In this Excel tutorial, we highlight the Top 12 Most Important Excel Functions and Features that can help most users in getting the basics right, and improve productivity and efficiency drastically.

12 Most Important Excel Functions and Features YOU MUST LEARN & MASTER

Learn the most important formulas & features of Microsoft Excel. I have listed them below for your learning pleasure.

Must-Know Excel Functions for Productivity & Career Enhancement

  1. Average,
  2. Sum,
  3. Count,
  4. Countif,
  5. CountA,
  6. CountBlank,
  7. Sumif,
  8. Concatenate,
  9. Proper,
  10. Round,
  11. Vlookup, and
  12. Creating Dropdown lists in Microsoft Excel.

With a sound knowledge of these 12 Most Important Functions and Features of Excel, you will be on your way to Excel Mastery. You have to take one step at a time.

Learn one Excel function and master it. You come closer to victory with each step, each new function, each new formula learned. Some of these are basic Excel Formulas and Functions than are the easiest to master.

Using the AVERAGE Function in Excel

How to use the Average Function in Excel

The Average Function is pretty easy to use.

It adds all the numbers in the provided range, and then divides the grand total by the count of the numbers provided.

You can only Average Numeric Values.

Syntax of Average Function in Excel:

=AVERAGE(number1, number2, number3)

=AVERAGE(Range_of_Cells) like =AVERAGE(A1:A10)

When do you use the AVERAGE Function in Excel:

Managers and business owners often ask executive about the Average Sales, or Average Revenue, or Average Salary, or Average Number of Tickets Resolved, or Average Time Spent on Calls.

All such measures can be easily calculated with the Average Function, which is pre-built into Excel, and is one of the most popular excel functions.

Using the SUM Function in Excel

How to use the SUM Function in Excel
How to use the SUM Function in Excel

The Sum Function adds all the numbers in the provided range, and then provides the Grand Total in the resulting cell.

You can provide numbers, refer to multiple cells, or select a range of cells to sum the numbers easily.

The SUM function only works on Numeric Values. It is considered the most basic excel formulas and functions.

If you provide a non numeric value (text value), it will result in a #VALUE error to be displayed in the Grand Total cell.

Syntax of Sum Function in Excel:

=SUM(number1, number2, number3)

=SUM(Range_of_Cells) like =SUM(A1:A10)

When do you use the SUM Function in Excel:

Managers and business owners often ask their staff about the Total Sales, or Total Revenue, or Total Salary for the company or department, or Total Number of Tickets Resolved, or Total Time Spent on Calls, or Total Expenses.

All such measures can be easily calculated with the SUM Excel Function, which is pre-built into Excel. This function is the most common, and most popular excel functions.

This function can be invoked from the Home Tab, by clicking the Sigma Icon. If the grand total cell is next to a range of data, it will auto populate the SUM formula, without writing anything. This is a handy trick that you should practice and get used to.

It will help you a lot to get the Grand Total, and is one of the most important function in Excel, and the most commonly used function in Excel too.

If you want to summarize data in Excel easily, check out this detailed article and video tutorial.

Using the COUNT Function in Excel

How to use the COUNT Function in Excel
How to use the COUNT Function in Excel

The COUNT Function counts all the numeric numbers in the provided range, and then provides the Total Count in the resulting cell.

You can provide numbers, refer to multiple cells, or select a range of cells to Count the numbers easily.

The COUNT function only works on Numeric Values.

If you provide a non numeric value (text value), it will NOT BE COUNTED in the final count.

Syntax of COUNT Function in Excel:

=COUNT(number1, number2, number3)

=COUNT(Range_of_Cells) like =COUNT(A1:A10)

When do you use the COUNT Function in Excel:

It is common in the management, and executives to count the number of rows of data.

For example, managers often want to know the total number of Headcount, the total number of  transactions, or Total Number of Tickets Resolved.

All such measures can be easily calculated with the COUNT Function, which is pre-built into Excel. This function is the most common.

This function can be invoked from the Home Tab, by clicking the drop down arrow of the Sigma Icon.

If the grand count cell is next to a range of data, it will auto populate the COUNT formula, without writing anything.

This is a handy trick that you should practice and get used to for counting the number of rows or transactions easily in Excel.

It will help you a lot to get the Total Count, and is one of the most important function in Excel, and one of the most commonly used function in Excel too.

SUM, COUNT, AVERAGE are the basic Excel formulas and functions that everyone should know at the bare minimum. But there are several more important functions in Excel also.

Using the COUNTIF Function in Excel

How to use the COUNTIF Function in Excel
How to use the COUNTIF Function in Excel

The COUNTIF function is amazing. It can conditionally count values, based on a criteria. This is one of the most popular logical functions in Excel.

This COUNTIF function can count the number of people in a particular department, count the number of products in a particular category, count the number of values that exactly match a particular value to name a few.

Countif can be also used to count non numeric values. This is a Big Plus for people wanting to count text values, based on a criteria.

Syntax of COUNTIF Function in Excel:

=COUNTIF(numbers in a range, criteria)

=COUNTIF(A1:A100, “=Oranges”) will count the number of rows in cells A1 through A100, which contain the word “Oranges” exactly.

=COUNTIF(A1:A100, “>5000”) will count the number of rows in cells A1 through A100, which contains values that are greater than 5000.

Interestingly, you can use this to count values that are greater than or lesser than a particular value too.

You can use Greater Than (>), Lesser Than (<), Greater Than or Equal To (>=), Lesser Than or Equal To (<=), and Not Equal To (<>) signs in the COUNTIF function.

Check out the Multiple Ways to Count in Excel.

When do you use the COUNTIF Function in Excel:

If your client wants to know how many big orders, above 10,000 were received today, or how many products of the Electronics category were sold, you could use the COUNTIF Excel function easily.

=COUNTIF(A1:A100, “>10000”)

=COUNTIF(A1:A100, “Electronics”)

=COUNTIF(A1:A100, 500) shows the number of entries where the value equates to 500.

Additional Resources: How To Count Cells With a Certain Value in Excel

This is one of the several logical functions in Excel. Other logical functions in Excel include IF, SUMIF, AVERAGEIF etc.

Some people consider COUNTIF to be an advanced function in Excel. Nonetheless, it is extremely useful, and I consider it to be an essential Excel formula to know and use.

Using the COUNTA Function in Excel

How to use the COUNTA Function in Excel
How to use the COUNTA Function in Excel

The COUNTA Function counts all non blank values in the provided range, and then provides the Total Count in the resulting cell.

You can provide numbers, text, refer to multiple cells, or select a range of cells to Count the non blank cells easily.

The COUNTA function works on Numeric & Non-Numeric Values. Thus, it can count Text value cells too.

The A in COUNTA actually stands for Alphanumeric values. So that’s why it can count text as well as numbers.

If you provide a non numeric value (text value), it CAN BE EASILY COUNTED in the final count.

Syntax of COUNTA Function in Excel:

=COUNTA(number1, number2, number3)

=COUNTA(Range_of_Cells) like =COUNTA(A1:A10)

When do you use the COUNTA Function in Excel:

It is common in the management, and executives to count the number of rows of data. If the data being counted is numeric, you use COUNT, but if you are counting names, or departments, or Products or Categories etc., it is better to use COUNTA, as it counts text values too.

For example, managers often want to know the total number of Employees, the total number of  Customers, or Total Number of Tickets Resolved.

All such measures can be easily calculated with the COUNTA Function, which is pre-built into Excel.

This function not known to many people. Learning to use this function well will improve your productivity and efficiency in Excel.

Additional Resources:  Counting Text Values in Excel

Using the COUNTBLANK Function in Excel

The COUNTBLANK Function counts all the blank values in the provided range, and then provides the Total Count in the resulting cell.

You can provide cells, refer to multiple cells, or select a range of cells to Count the Blanks easily.

The COUNTBLANK function works on Any kind of cells.

If you provide a range of Numeric or Non-Numeric cells to the COUNTBLANK function, then these will not be counted.

This function only counts empty cells.

CAUTION: A common issue with the COUNTBLANK function is that if a cell contains a space, it does NOT get counted.

This may make the CountBlank total not seem to total with the visible blanks. But this is because it is difficult to see a space in a cell.

Syntax of COUNTBLANK Function in Excel:

=COUNTBLANK(cell1, cell2, cell3)

=COUNTBLANK(Range_of_Cells) like =COUNTBLANK(A1:A10)

When do you use the COUNTBLANK Function in Excel:

You may be wondering why you may want to count blanks. But this efficient function definitely has its uses.

For example, office managers often want to know the total number of Products without a price (price is blank), count the number of Products that didn’t get a looked up value from a VLOOKUP operation, or count how many employees don’t have a valid cost center code.

All such things can be easily counted with the COUNTBLANK Function, which is pre-built into Excel.

The COUNTBLANK function is a life saver when checking counts and it helps to find the illusive missing or empty value.

It will help you a lot to get the Total Empty or Blank Count, and is one of the 12 most important function in Excel, and one that is not so commonly known.

Using the SUMIF Function in Excel

The SUMIF function is amazing. It can conditionally SUM values, based on a criteria. SUMIF is another one of the most popular logical functions in Excel, along with COUNTIF.

This SUMIF function can SUM the total Salary in a particular department, SUM the total Sales in a particular category, Sum the amounts that exactly match a particular value to name a few.

SUMIF can be also used to sum based on a Criteria that is a non numeric value.

This is a Big Plus for people wanting to SUM Numbers, based on a text value criteria.

Syntax of SUMIF Function in Excel:

=SUMIF(range, criteria, [sum_range])

In this formula, the criteria is the second argument. If the range meets the criteria, and the range is a numeric range, it can be summed up directly.

But if the range is a Text based Range, and it matches the criteria, you can provide a third, optional argument, which would then sum up the corresponding values from the sum range, where the range matches the criteria.

=SUMIF(A1:A100, “>1000”) will sum all the number cells A1 through A100, which contain a value that is greater than 1000.

=SUMIF(A1:A100, “>5000”, B1:B100) will sum the number  in cells B1 through B100, which contains a values that are greater than 5000 in the cell range A1 through A100.

You can use Greater Than (>), Lesser Than (<), Greater Than or Equal To (>=), Lesser Than or Equal To (<=), and Not Equal To (<>) signs in the SUMIF function.

=SUMIF(A1:A100, 1000) will sum all the amounts that are exactly equal to One Thousand in the range A1 through A100.

When do you use the SUMIF Function in Excel:

If your client wants to know the total sales for a particular category, like total sales amount in the Electronics category, you could use the SUMIF function easily.

=SUMIF(A1:A100, “Electronics”, B1:B100)

=SUMIF(A1:A100, “=1000”) will give the total of the values that are exactly 1000 in cells A1:A100.

Once you learn COUNTIF & SUMIF, you know two of the most important excel Formulas and Functions. These are the basic Excel formulas and functions that everyone should know.

Using the Concatenate Function in Excel

Combining Text, or Concatenating Text is frequently required.

No doubt that the CONCATENATE function, now also called as the CONCAT function is one of the Top 12 Most important functions in Excel.

The concatenate function helps you to combine two or more items into a single string.
The most common use is to use it for combining people’s first and last names.

SYNTAX of CONCATENATE Function in Excel

CONCATENATE(text1, text2, text3…)

For example, if you want to concatenate and combine two string, you get this

=CONCATENATE(“Hello”, “World”)
Result: HelloWorld

As you notice, there is no space between the two words.

This is because concatenate does not add any additional spaces in the combined text. Nor does it remove any spaces.

So if you want to add a space between the two strings, you will have to hard code the space in the concatenate function.

=CONCATENATE(“Hello”, ” “, “World”)
Result: Hello World

If the values to be combined are in separate columns, you can also use the Concatenate function

=CONCATENATE(A2, ” “, B2)
Result: Mickey Mouse

Now you can drag the formula or copy and paste it into the cells below, to get all the other names combined.

Note: Concatenate is best suited for combining Text Values. If you use it to combine numbers, you will get strange results.

=CONCATENATE(5, 6)
Result: 56

Concatenate can be used to combine separate Day, Month, and Year to resemble a date too.

=CONCATENATE(5, “-“, “Dec”, “-“, 2024)
Result: 5-Dec-2024

Using the PROPER Function in Excel

It is often required to display names of people, Categories, Departments to appear nicely in Excel, with the first letter of each word appearing in a Capital Letter.

While we have Excel functions like UPPER() & LOWER(), which can convert every letter to uppercase or lowercase, they are not so handy.

Because customers often want to see only the first letter capitalized, and the remaining letters to be in small letters.

Syntax of PROPER Function in Excel:

=PROPER(text_value)

=PROPER(“mickey mouse”) would result in Mickey Mouse. Notice that we supplied the “mickey mouse” in lower case. But the returned value had the first letter of each word capitalized.

When do you use the PROPER Function in Excel:

Whenever you want to capitalize the first letter of each word, you can use Proper function in Excel instantly.

I consider this to be one of the most useful and important functions of Excel.

Additional Resources: Learn the Text Functions in Excel

Using the ROUND Function in Excel

Whenever you divide two numbers, the resulting answer can be a large fraction. For example, dividing 100 by 3 gives us 33.333333333.

But business users may not want to use so many decimal points. Thus, the ROUND function comes in handy.

It can be used to round any fraction to any number of decimal points.

Syntax of ROUND Function in Excel:

=ROUND(number1, number_of_digitits_to_round_of)

=ROUND(33.33333333, 2) will result in 33.33 being the answer.

=ROUND(33.333333, 1) will result in 33.3 being the answer.

=ROUND(33.333333, 0) will result in 33 being the answer.

When do you use the ROUND Function in Excel:

The business managers, finance managers often ask to present the numbers rounded. For example, they may ask the Profit margin to be displayed in 2 decimal points.

Sometimes, a business user may want to round the values up or down, based on the business case.

So ROUNDUP(100/3, 2) will result in 33.34

And ROUNDDOWN(100/3, 2) will result in 33.33.

With multiple Rounding functions available, you can fulfill every user’s needs and requirement around Rounding.

So ROUND, ROUNDUP or ROUNDDOWN, and help each and every user with one of the most useful Excel functions.

Using the Vlookup Function in Excel

VLOOKUP is considered one of the Most Useful Functions of Excel. It is used to benchmark the skills of interviews often.

If you know VLOOKUP and can use it well, with its optional settings, then you are considered an Advanced User or an Experienced User of Excel.

Heck, some people also consider you to be an Excel Guru if you have mastered the VLOOKUP Function.

Learning and Using VLOOKUP is quite easy.

The use of this function in almost every corporate spreadsheet, and its amazing use cases have raised it to the Top of the Most Important Excel Functions of all times.

However, don’t think that it is difficult to use or that it is complex.

Let’s understand what is VLOOKUP first, and then we understand its syntax and usage.

VLOOKUP is used to lookup any value in a table, and pick a corresponding value from the other columns of the matching row.

Syntax of VLOOKUP Function in Excel:

=VLOOKUP(lookup_value, table_array, column_index_number, [range_lookup])

=VLOOKUP(“James Bond”, employee_Table, 2) will find the second column of the employee_table, where the first column of the employee_table is set to “James Bond”.

In this example, we maybe looking for the department or salary, or employee code of the employee named James bond. Depending on the table, if the second column after the employee name is Department, we get the department.

If the third column is Salary, and we need the salary, then we can modify this formula slightly.

=VLOOKUP(“James Bond”, employee_Table, 3) will find the third column of the employee_table, where the first column of the employee_table is set to “James Bond”. Now we can get the Salary of James Bond.

When do you use the VLOOKUP Function in Excel:

Whenever you want to lookup one value, and pick another corresponding value from the lookup table, then the VLOOKUP function will be the most appropriate.

Managers and business users often use the VLOOKUP function to lookup product cost, employee salary, product attributes from the Product Master table, or the employee Master Tables.

The interesting thing is that the lookup table can be on another sheet in the same Excel file, or it could be in a completely different Excel file.

The Looked up table can come from another file in another folder or directory.

This makes it very powerful, and one of the most powerful and useful functions of Excel.

Once you master these most important functions of Excel, you will improve your level of expertise in Excel, and with it you will also raise your competency, and understanding of Microsoft Excel

Creating Dropdown lists in Microsoft Excel

To avoid data entry errors, and to provide a basic level of data validation, it is often desired that users don’t key in arbitrary values, which are difficult to debug, could be full of spelling mistakes, and a single typo can make the Lookup on that value fail.

Thus, business users often want the data to be of a good quality. One simple way to improve data quality is to avoid typing in values in cells, and instead pick the correct value from a drop down list.

For example, you could display a drop down list of Customers, Products, Countries, Departments, Cost centers, Accounting GL Codes, to name a few. This way,

When do you use the DROPDOWN Data Validation Feature of Excel:

Whenever you want to limit user entry to only a few select values, it is appropriate to setup a dropdown data validation list. It is fairly easy to setup. In fact, Microsoft already provides you with a few standard list, like the Days (Monday, Tuesday, etc.), Month Names, Areas (North, South etc.).

And the good thing is that you can always define your own custom list in Excel. I have a detailed article on how to setup a Drop Down list in Excel. Check it out!

There you have it. A roundup of the most essential excel formulas in Excel. Consider them the Most important Excel Formulas and Functions to Master first.

Conclusion: Top 12 Most Important Functions of Excel

The tips, tricks and all the 12 Most Important Excel Functions and Features  work in every version of Microsoft Excel.

most important excel functions

These are the list of most important excel functions that every professional or analyst must know.

Knowledge of these must-know excel functions for productivity and career boost is mandatory for every professional and Excel enthusiast.

Additional Resources & Video Tutorials from ExcelChamp Vinai Prakash

Subscribe to our channel for more Excel Tips & Tricks

These tips work in every version of Microsoft Excel. Just give it a try, and if it helps you, remember to give it a Thumbs Up, and Like the Video.

Cheers,

How To Center a Title in Excel

A centred title looks so much better in Excel reports or charts. I have seen many users and even management requests for the chart title to be in the center of the report or Excel chart.

There are several ways to center a report title in Microsoft Excel. And most of these techniques will work in any version of Excel. So if you have had any of these versions of Microsoft Excel, you are safe.

This tip is applicable to Excel 2003, Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019 or the latest Microsoft Office Excel 365.

title in center in Excel

Please check out both of these methods and my warning about their usage. Finally, read my preferred and recommended actions while centring titles in Excel spreadsheets.

Method 1: Merge and Center Title Text

Step 1: Select the range of cells where you want to center the text item. Take note that the text you want to center must be in one of these cells. Further, there should be no other text in the selected range, or it will be overwritten. Microsoft Excel will keep the text in the top-left cell of the selected range, and discard any other text in the selected range.

Step 2: Click on the Merge and center button. This is present on the Home Tab, under the Alignment group. It looks like 2 cells on top, a double-headed arrow, and 2 cells at the bottom.

Merge and center in Excel

The selected text is merged and centered in the selection. If multiple rows were selected, the centered text will be at the bottom row.

In case you want to align the text in the middle too, you have to click on the Middle Align icon in the formatting toolbar. Now the text should be exactly in the middle horizontally and vertically.

Middle Align in Excel

CAUTION: While you may like this method of merging and centering text, because of its ease and convenience, we do not recommend to merge cells in Excel worksheets.

All Excel pros adhere to this rule – never merge cells, unless you are presenting it in a report or dashboard, as an end result. Raw data should never be in merged cells.

Merged cells can cause havoc while trying to sort data.

Basically, you can’t sort data if it contains even a single merged cell. So you have to unmerge the cells before you can sort it.

Further, data in an Excel Table can’t be merged too. This is because sorting and filtering get affected if you merge cells in Excel. In this case, you have to convert the Excel table into a normal range. Then only you can centre the text.

Method 2: Center Across Selection

Some of the older versions of Excel did not contain the Merge and center option in the ribbon. This method 2 works when you are using such older versions,s but it also works in newer versions of Microsoft Excel, including Office 365.

And this method does not merge the cells. It only centers the text. Thus, our earlier problem with merged cells is actually eliminated.

To use this “Center across Selection” method, do the following steps:

Step 1: Select the multiple cells where the text is to be centered. The text should be present within this range.

Step 2: Click on Format Cells (Shortcut Control + 1 on Windows). Or right click the mouse button on the selection, and pick Format Cells from the Options menu.

center across selection in Excel

Step 3: On the Format Cells dialog box, stay on the Alignment Tab. In the Text alignment dropdown showing “Horizontal“, you will see the default alignment set to “General“. Pick “Center across Selection” from the selection. See screen shot below.

This will center the text across the selection. But the cells won’t be merged. This is the preferred way of centering a title in Excel.

How to Unmerge Cells in Excel

If you have clicked on Excel’s Merge and center text icon, and now decided to unmerge it, just select the text, and click on the “Merge and center” icon again. This button acts like a toggle switch. The merged cells will be unmerged, and the centered text will go back into the first unmerged single cell.

How to Align Text within a Single or Merged Excel Cell

If large cells, the text may appear at the bottom left corner. If you want to change the cell alignment vertically and horizontally, you need to click the Middle Align button & Center buttons under the Alignment group under Home Tab. Now the text will move to the center of the cell.

How to Center a Title in Excel Charts

When you create an Excel Chart, by default a Chart Title is added in the centre. In some old versions of Excel, you might not get the Title in the Chart. And in some cases, Excel can’t figure out what title to put, because the column name happens to be blank. In this case, you will end up without a chart title at all. But you can add a chart title by hand, manually and then center it.

Chart With Title in Center

Steps to Center the Chart Title in Excel

  1. Click the Green + icon on the top right of the Chart (applies to Excel 2013, 2016, 2019, Office 365 spreadsheets).
  2. The Chart Elements will be displayed. Click the Checkbox on Chart Title. A Chart title is now placed in the top center of the Chart.

    Adding a Chart Title in Excel

  3. You can click inside the text box, and type any appropriate title text. It will be centered by default.

Print Excel worksheets with Worksheet titles in header or footer of the print out

  1. Go to Page Layout.
  2. Choose Page Setup popup from the bottom right flyout menu.

    File Name in center of page

  3. Choos Header/Footer tab from the Page Setup popup
  4. Click Custom Header or Custom Footer option. Another Header or Footer Popup will open.
  5. Click the Green Excel icon and the File name will be printed in the Left section.
  6. Click inside the Right section, and then click on the page number # icon. The page number will now be printed in the right section.
  7. You can display the current date in the Center section.
  8. When you do a Print Preview, you will see a beautifully formatted Report, with the date centred, the file name on the left, and page number in the right section.

At ExcelChamp, we conduct a training course for Excel Formatting & Formula Tips & Tricks, which covers such formatting tips for beginner & advanced users alike.

Hope you enjoyed the tips presented here. They are pretty simple and easy to implement. Go ahead and amaze your audience with amazing charts, reports and analysis done in Excel.

Cheers,
Vinai Prakash,
Founder & Master Trainer at ExcelChamp.Net

 

Find Age in Years within Microsoft Excel [Video Tutorial]

Wondering How To Find the Age in Years from Date of Birth, in Microsoft Excel?

See multiple ways to calculate Age in Excel from any date of birth.

This simple, step-by-step tutorial on finding the age in Excel from date of birth or any other date, work on Microsoft Office 365, Microsoft Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 & Excel 2003 too.

We cover Microsoft Excel Functions & Formulas that allow you to calculate the following things in Excel:

  1. Find the age in Years
  2. Find the Age in Months
  3. Find the Age in Days
  4. Finding the exact age in Years, Months & Days.
  5. Combining Years & Months to create a Text String the way we want.

Here’s how to calculate the number of years since the date of birth

Not only the years we can also calculate the months. I will show you multiple techniques to calculate the age in years, months & days.

If you want a whole number or you want the age as a fraction, there are so many ways to do it.

What we want to calculate is the age in years –  something like Age is 55 years. Or Age is 55.56 years.

Before we start i want to highlight to you that the date of birth that you are using should be a date field so you can select it and make sure the formatting is not General or text, but it should be set as a short date or any kind of date.

To calculate the years,  the formula is extremely simple. We will be using the DATEDIF function of Excel.
=DATEDIF(DateofBirth, TODAY(), “Y”)

Similarly, we can calculate the age in months too.

=DATEDIF(DateofBirth, TODAY(), “M”)

This will result in the number of months from the date of birth to today’s date. You might see a huge number like 666 here.

But you may not want this.  Perhaps we only want how many months after the 55 whole years have elapsed.

In this case,  we modify the formula from only “M” to “YM”

=DATEDIF(DateofBirth, TODAY(), “YM”)

YM means it is only the months after the completed years. By doing this we can see that only six months have elapsed after 55 years.

Finally, we only need the days that have elapsed after the completed months in the age.

=DATEDIF(DateofBirth, TODAY(), “D”)

This will tell you that 20,000 days have passed from the date of birth, but, of course, we don’t want this.

So you change D to MD. The “MD” tells Excel to calculate how many days after the month have elapsed. 

As you can see, we have the age calculated in different cells – the age is 55 years 6 months and 9 days exactly till today.

If you wish to see a complete combined date, you can use the CONCAT function to combine multiple formulas, or use the & to combine text as well.

As an example, we can write

=CONCAT(DATEDIF(dateofbirth,TODAY(),”Y”),” Years, “,DATEDIF(dateofbirth,TODAY(),”YM”),” Months & “,DATEDIF(dateofbirth,TODAY(),”MD”),” Days”)

For a DateofBirth date of 6-December-1966, it generates, 55 Years, 6 Months & 28 Days.

Hope you find this DATEDIF function quite handy for such use. Do give it a try and let us know how you use it.

Cheers,
Vinai

Recommended Reading for Next Steps:

  1. Using Date & Time Functions in Excel
  2. Difference Between 2 Dates, Without the Weekends

Master These Excel Lookup Functions To Save Time

One of the most popular Excel Lookup functions is VLOOKUP. Another Excel function to check out is the HLOOKUP function, which can do a horizontal lookup.

The newly added XLOOKUP is becoming very popular too. (The XLOOKUP function is currently only available in Office 365 versions).

For the advanced & power users of Excel, the mastery of Excel lookup functions like INDEX, MATCH & OFFSET can be considered vital. These functions are considered the “advanced” lookup functions in Excel.

With the help of these functions, you will be able to analyze data quickly. To learn more, you should enrol in the data analysis and interpretation training class

But with the introduction of XLOOKUP, some of the jugglery created by mixing INDEX & MATCH combination is no longer required.

VLOOKUP Function of Excel

The most MUST HAVE Function ever. Even Excel gurus can’t live without it. I polled a group of Excel experts recently, asking if Excel’s VLOOKUP was overrated. I got a severe backlash for even mentioning it.

Almost everyone said that it is their GO TO function, an absolute must-have and that Excel won’t be that useable if this VLOOKUP function was taken away from Excel!

Most people swear by their VLOOKUP functions. It is their GO TO function when they want to lookup value of any type.

According to legend, VLOOKUP mastery is what separates the Pro Excel users from the Amateurs!

Vlookup is akin to using a dictionary. You know the word, and you want to find out the meaning. This dictionary is the range of cells that contain the lookup up value, and its associated value. The V in VLOOKUP stands for the dictionary data array being a vertical dictionary. So for a vertical lookup you must use VLOOKUP function only.

=VLOOKUP(word, dictionary, column number of meaning, exact_match_ype)

In the VLOOKUP formula, the first column in the dictionary must contain the lookup up value, and the first row should be of the data. You should not include the headings in the dictionary table. The difficulty most people have with VLOOKUP is the last flag – the logical value of TRUE or FALSE (You can use 1 for True and 0 to indicate the False flag).

Once a matching value is found out, you will be able to get the return value based on the search. The error value of N/A will be generated if there is no exact match until the last row or the last column of the array.

The mystery is created because to use VLOOKUP for an exact match, you have to specify the last optional flag, and set its value to a FALSE or a 0. By default, it is set to 1, which is useful for an approximate match type only. So for an exact match of a specific value, the last parameter is not really optional… it is mandatory.

Practical Examples of VLOOKUP:

= VLOOKUP(50, age_income, 2) will find the income from the second column of the age_income range for the age value of 50. Note that this is set as an approximate match lookup, as the last optional value is not specified.

=VLOOKUP(A5, $K$10:$L$100, 2, FALSE)

In this VLOOKUP example, we are looking for a piece of information from the column of data in the range $K$10 to $L$100. The search value is in cell A5, and the particular value of interest is in the lookup array that is set as an absolute reference.

The above example uses the reference functions that will generate an Exact match. If the correct value is not found, an error value of N/A will be received.

There are two major shortcomings in using the VLookup function of Excel.

  • First of all, the VLOOKUP is really a slow function. It is obvious when you do a lookup on a large list of 100,000 values or more.
  • Secondly, VLOOKUP can only look up a corresponding value from the columns on the right of the looked-up value. It can’t look to the left!

Make sure you master this Excel function really well.

HLOOKUP Function in Excel

An often forgotten cousin of the VLOOKUP formula, this Horizontal Lookup and Reference function in Excel works in a similar way too. The only difference is that in this case, a lookup dictionary is a horizontal dictionary of columns, denoted by the H.

HLOOKUP is most used in range lookups, rather than exact matches, as columns are not the best suited for exact values, because of their limit of only16,000 columns. Whereas a list can grow vertically to over a million records easily.

In the following formula, this lookup function searches for the closest match, especially when we are not searching for an exact match, but an approximate match.

The dictionary is the table array and it is recommended that we use the absolute reference to lock the cells from moving.

=HLOOKUP(A5, $G$2:$K$100, 2)

Here the HLOOKUP will search for the exact or the next smallest value in the lookup table source data range of $G$2 to $K$100, and return the second row. If you want the third row, you can change the 2 into a 3.

Both VLOOKUP & HLOOKUP return single-cell values from a single row or a single column.

XLOOKUP Function in Excel

Did you know that new functions are added to Excel quite frequently, and these are extremely useful functions making approximate matches as well as exact matches?

Finally, after years of backlash at Microsoft for creating the mess with the Match Type (True and False) in VLOOKUP, they got rid of it completely in the Excel XLOOKUP function.

By default, XLOOKUP is set to do an exact match.

XLOOKUP requires a deeper understanding of the various scenarios it can handle. I’d recommend a dedicated video tutorial on XLOOKUP by Excel MVP Chandoo to learn how to use XLOOKUP in Excel.

This new XLOOKUP function of Excel is only available from Microsoft Office 365 users. It does not work on Excel 2016 or Excel 2019 versions.

Good knowledge of these lookup functions can help you to prepare the data in advance before you begin to analyze or summarize data with Excel.

With so many better functions, I have now stopped using VLOOKUP. There’s a new feature in Power Query in Excel, which really negates the use of vlookup function in Excel.

Using INDEX Function in Excel

If you know the row number, you can find the value on that row or column cell directly. INDEX can be used as an Array function also. Paired with MATCH, you can find any value on any row or column in a 2-dimensional array.

How To Use Excel MATCH Function

When you want to find an exact match in an array and return the row number in the array, MATCH comes to your rescue.

It is one up on VLOOKUP, which requires you to know the column you want to return. MATCH can find a match for a value that is lower, exactly equal or higher than the specified value.

=MATCH(value, range_Lookup_Cells, FALSE)

This simple example MATCH function will look for the value in the range_Lookup_Cells, and return the row of the matched value exactly.

Index and Match can work on a single row or a single column of data too. These 2 lookup functions of Excel are the exact opposite of each other.

The index finds the value on a particular row, and Match finds the row or column number where the match happens to be found in the data range.

Both Index and Match can also run as an array form of the Excel Lookup Function.

Paired with INDEX, an INDEX & MATCH Function can manage to look up on the left or the right of any array of cells.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!

Master the OFFSET Function within Excel

To navigate your way in a two-dimensional array of rows and columns, you can use the OFFSET function in Excel. It can traverse any number of Rows or Columns, and get you the value.

How to use the offset function in Excel:

=OFFSET(Starting Cell, Row to move up or down, Columns to move left or right, Number of rows required to be returned, number of columns required to be returned)

I generally use OFFSET more than INDEX and MATCH combinations. Using one super-powerful OFFSET function is more straightforward.

Thee

Once you start using Offset in Excel, you wouldn’t want to use other lookup functions of Excel.

When Do I Use the INDIRECT Function of Excel?

The Excel INDIRECT function returns the reference specified by a string text. Any References are immediately evaluated to display their contents.

You can Use the INDIRECT function when you want to change the reference to a cell within a formula, without changing the formula itself.

=INDIRECT(A3)

The above Indirect function will check what is in cell A3. And A3 will have the cell reference to another cell. So if A3 contains B35, Excel will then read the value in cell B35.

Thus, we can get the value of the reference in cell A3. The reference is to cell B3, which may contain the value 45.

The INDIRECT can be very useful in creating custom management dashboards and reports.

What does the FORMULATEXT Function of Excel Do?

Displays the text of another formula. This formula helps to see all formulas next to their values and can be useful to spot mistakes and issues with formulas.

=FORMULATEXT(A3) will provide you with the formula in cell A3 as a Text Value.

This FormulaText function is useful to see the formula without having to go into Editing mode.

View this article for more information on how to get the Formula of another cell in Excel.

Begin Using ROWS Function of Excel

Displays the row number of a reference cell.

=ROWS(A1:B4)

The above function will return a value of 4. This is because there are 4 Rows in the given range.

Use the COLS Function in Microsoft Excel?

Displays the column number of a reference cell. As evident in the following syntax, COLS function is useful to count the columns in a range.

This can be useful, when combined with INDIRECT and OFFSET, the function begins to help in generating a dynamic range.

=COLS(A1:B4)

Will return a 2. This is because there are 2 Columns in the given range: A & B.

Use the TRANSPOSE Function in Excel like a Pro

Converts rows into columns and columns into rows. Just like the Transpose feature in Paste Special, but done programmatically.

So if you use TRANSPOSE(A1:D3), you have selected 4 columns and 3 rows.

After the Transpose is completed, you will get an array reference of 3 Columns, and 4 Rows. The horizontal table would have flipped and will be visible vertically.

So the next time you have a list of products that you want to flip from rows to columns, you can use the Transpose function.

Pretty nice use of hanging values in rows into columns.

When Do I Use the UNIQUE Function of Excel?

The UNIQUE function of Excel generates a list of unique values that automatically spill down. An array function can be used to create data validation lists too.

Available from Microsoft Office 365 onwards. This UNIQUE function is not available in Excel 2016 or Excel 2019.

=UNIQUE(Data_Range)

Learning the Lookup Functions in Excel Quickly & Easily

As you can see, there are a lot of LOOKUP functions in Excel, and learning and mastering them takes time. But once you do master them, you can do wonders with your Excel skills.

It is worth the effort to learn the Excel Lookup Functions. Call Intellisoft at +65-6296-2995 for Excel 365 Classroom Training that covers the key Lookup functions of Excel.

You will definitely enjoy it!

Cheers,

Vinai

Founder of ExcelChamp. Master Trainer at Intellisoft Systems in Singapore.

Need Tips on Pivot Tables, Data Analysis or Creating Better Charts?

Do check out these resources

And if you want to learn Advanced Excel fast, then check out our detailed guide.

Articles Written By Vinai.

Vinai is passionate about maximizing Excel to improve everyday productivity. He conducts online Excel workshops as well as classroom workshops in Singapore, Australia, Middle East. If you would like to engage him to train your staff in maximising Excel, do contact us.

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!