How to highlight Duplicates in Excel 2013

Is there an easy way to locate and highlight duplicates in a list in Excel?

Yes, Excel has many powerful tools to locate and highlight duplicates in a list or data range. Conditional formatting is one of the tool with which we can highlight duplicates.

Let's learn the process of highlighting duplicates with the help of an example:

1. Select the cell range where you want to find and highlight duplicates

2. Use the Conditional Formatting tool located in "Home" tab of the ribbon as mentioned below:

Conditional Formatting >>> Highlight Cells Rules >>> Duplicate Values [Shortcut: Alt+HLHD]

This will activate the "Duplicate Values" dialog box.

3. Select "Duplicate" from the dropdown in the dialog box and also select relevant highlight option

 To apply any customized format you can select "Custom Format..." from the dropdown list. The preview of format will appear while you are selecting from the dropdown.

4. Click "OK"

This will apply the desired format to duplicate values in the selected range.

IMPORTANT NOTES:

• The feature is NOT case sensitive.
• Excel will recognize the cells as duplicate only if both cells contains exactly same data.
• In case any extra spacings are present in any one of the cells, Excel will not recognize them as duplicates (If both cells have same extra spacings, then it will recognize as duplicates!!)

How to use SUBSTITUTE function in Excel 2013

SUBSTITUTE is very useful function in Excel when we want to replace specific text in a text string with some other text.

Syntax:               =SUBSTITUTE(text, old_text, new_text, [instance_num])

• text: The original text string containing the text to be replaced
• old_text: The text/data you want to replace with new_text
• new_text: The text/data that is used to replace the old_text
• [instance_num]: An option which specifies which occurance of old_text you want to replace

• If [instance_num] is specified, just that instance of the old_text is replaced;
• Otherwise, all instances of old_text are replaced with new_text.

IMPORTANT NOTES:

• The SUBSTITUTE function is Case Sensitive.
• The function can take texts, numbers as well as cell references for replacement.
• Text strings should always be placed using double quotes (eg. “Tom") while the reference cell formula (eg. C4) do not need double quotes. For numbers, using double quotes is optional.
• The output generated will always in text format, even if the input data were in number format.

RECOMMENDED BOOKS

Excel 2013 Bible
Microsoft Excel 2013
Excel 2013 Dummies



Excel 2013 Formulas
Microsoft Excel 2013
Office 2013 Library



Excel 2010 Dummies
Excel 2013 VBA
Financial Analysis


Excel Pivot Tables
Excel VBA Dummies

How to create a Scenario Summary Report in Excel 2013

After using Scenario Manager to add scenarios to a table in a worksheet, we can generate a summary report to visualize all the scenarios as well as scenario with current input values. The summary report show input values in different scenarios as well as the output generated (based on the selection).

1. Open the workbook containing the required scenarios.

2. Open the Scenario Manager dialog box.

Go To: “DATA” tab in the ribbon>> What-If Analysis>>>Scenario Manager [Shortcut: Alt+AWS]

3. Click on the "Summary" button in this dialog box.

A new dialog box will open giving the options to generate the summary report. The dialog box gives us choice of reports that can be generated: Scenario summary (static) and Scenario PivotTable report (dynamic).

After selecting the type of report, click on "Result cells" and select the result cell (required output).

4. Click "OK" to generate the report.

Excel creates a separate worksheet with input values along with selected result cells for all the scenarios as well as current input values. We can appropriately rename and re-position this Scenario summary worksheet and then save the workbook.

How to use Scenario Manager in Excel 2013

Scenarios form an important part in real-world problems. The inputs used in the real-world problems are dynamic and hence lead to different outputs. Scenario Manager in Excel help us in getting solutions of these problems by accounting for changing inputs. Before we create a scenario, we must design the worksheet so that it contains at least one formula that is dependent on the input cells.

Once various scenarios are created based on different input values, Excel provides facility to create a detailed summary report of these scenarios.

Let's understand this on the basis of Costing example. The example calculates Net revenue of a coffee shop.

Formula: Net revenue = Gross Revenue - Total Variable Cost - Total Fixed Cost

Where, Gross Revenue = (Number of coffee cups sold *Selling Price per unit);                                 Total Variable Cost = (Number of coffee cups sold*Variable cost per unit)

Let's name these input cells as Number_Units_Sold, Selling_Price_Unit, Variable_Unit, Total_Fixed

Go To: "DATA" tab in the ribbon>> What-If Analysis>>>Scenario Manager [Shortcut: Alt+AWS]

CREATING SCENARIOS:

1. Click on the "Add" button in the Scenario Manager dialog box.

Insert Scenario name as "Base Case" and select all the input values (C2:C5) in Changing Cells field. In case the input values are scattered, we can hold CTRL key while clicking on different input cells OR use comma after clicking on each input cell.

2. Click "OK". This will open Scenario Values dialog box.

Insert various input values corresponding to this scenario as shown above.

3. Click "Add" to insert new scenario OR click "OK" to stop adding scenarios.

Once all the scenarios are added, the Scenario Manager dialog box will open again and show names of all the entered scenarios.

4. To see result of a particular scenario, click on the Scenario name and press "Show" button.

Excel will automatically change input values as listed in the scenario and show relevant output.

IMPORTANT NOTE:

• Always assign range names to your input cells before you begin creating the various scenarios. It is easy to recognize the input filed by names while using the Scenario Values dialog box to enter input values for various scenarios.

How to Trace Precedents and Dependents in Excel 2013

Excel offers excellent formula auditing tools for tracking down a cell that is causing error by tracing the relationships between the formulas in the cells of spreadsheet. By tracing the relationships, we will be able to locate the Precedents and Dependents.

Also read: How to use Goal Seek feature in Excel 2013

Precedents are cells or ranges of cells that affect the active cell value.
Dependents are cell or ranges of cells that are affective by the active cell.

Trace Precedents and Trace Dependents are powerful tools under Formula Auditing in Excel. Go to:       "FORMULAS" tab in the ribbon>>>Functions under Formula Auditing [Alt+M]

• Trace Precedents [Alt+MP]: When we click this button when the active cell contains formula, it draws arrows from the primary cells which are directly affecting the formula in the active cells (direct precedents). On clicking the button once again, it shows more arrows from cells which are affecting direct precedent cells and so on.
• Trace Dependents [Alt+MD]: When we click this button, it draws arrows from the selected cell to the cells (direct dependents) that use, or depend on, the results of the formula in the selected cell. On clicking the button once again, it shows "tracer" arrows identifying the cells (indirect dependents) that refer to formulas found in the direct dependents.
• Remove Arrows [Alt+MA]: On clicking this button, all the arrows created by precedents and dependents are removed. Additionally, Excel also gives options of customized removal of arrows by providing two buttons in the drop-down besides Remove Arrows.

1. Remove Precedent Arrows [Shortcut: Alt+MAP] - Removes only precedent arrows
2. Remove Dependent Arrows [Shortcut: Alt+MAD] - Removes only dependent arrows

IMPORTANT NOTES:

• Double-clicking an arrow activates the cell on one end of the line. Double-clicking again activates the cell on the other end.
• If the relationship shown by arrows are in the same worksheet, then Excel draws red or blue arrows. It shows black dotted arrow with miniature icon of worksheet in case the relationship exists with another worksheet (in same or different workbook).

More interesting articles on this website:

• How to create Two-Variable Data Table in Excel 2013
• Text Alignment keyboard shortcuts for Excel 2013
• How to freeze panes in Excel 2013
• How to generate RANDOM number in Excel 2013

How to use MATCH function in Excel 2013

How to use CHOOSE function in Excel 2013

Excel's Lookup functions like VLOOKUP, HLOOKUP, CHOOSE, INDEX, MATCH etc. are used to find and return data from a list or table based on a lookup value or index number.

In CHOOSE function, it uses an index number to find and return a specific value from a corresponding list of data. The index number indicates the position of the value in the list.

Syntax:     CHOOSE(index_num, value1, [value2], [value3]....[value254])

• index_num: Specifies the Value is to be returned by the function. It could be a number or formula or a cell reference. The value for this should be betweent 1 and 254.
• value1: The value/data we want to fetch when "index_num" equals 1
• value2: The value/data we want to fetch when "index_num" equals 2

The value/data for output { value1, value2, .....etc.} could be numbers, cell references, defined names, formulas, functions, or text.

IMPORTANT NOTES:

• If index_num < 1 OR if index_num > "number of values" then Excel gives #VALUE! error
• If index_num is a fraction, it is changed to the lowest integer before being used (eg. 3.8 to 3)
• If index_num is an array, every value is evaluated when CHOOSE is evaluated

How to use VLOOKUP in Excel 2013

VLOOKUP is probably one of the most useful function in Excel when it comes to looking up fields related to a variable. We can use the VLOOKUP function to search the first column of a range of cells, and then return a value from any cell on the same row of the range.

The V in VLOOKUP stands for Vertical i.e. it looks up in vertical table. Since most of the real world data appears in vertical table, the VLOOKUP is one of the most used function in Excel. It works similar to human mind, first it searches for relevant key item and goes further in the row to get it's details.

Let's understand this better by most commonly encountered real world example of Employee details. Suppose a company has 10 employees with unique Employee ID followed by details like Name, Departement, Contact Number and E-mail ID.

If we need the Contact Number corresponding to Employee ID 201309 we first search the row in which this ID exists and then we move further in the same row to locate Contact Number. That's exactly how Excel also works, we just need to feed the data for what we are looking and from where we need to look.

Syntax:      VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value: Insert the value to search in the first column of range or table. This value could be a value or it could be a cell reference (more dynamic). This helps excel to identify the row in which our data exists.
• table_array: The entire range of cells that contains data. We can directly use the range reference or a range name. This tells excel the location for search.
• col_index_num: The column in range selected under table_array corresponding to desired matching value field. (eg. Contact Number lies in column 4, hence 4 should be inserted)
• range_lookup: Optional field. Specifies whether we need exact match or approximate match. FALSE (or 0) corresponds to exact match while TRUE (or 1) corresponds to approximate match. The default value is FALSE (or 0) i.e. exact match.

IMPORTANT NOTES:

• If col_index_num < 1, Excel gives #VALUE! error
• If col_index_num > number of columns in range, Excel gives #REF! error
• While searching for text values in the first column, ensure that any cell value does not contain leading spaces, trailing spaces or inconsistent quotation marks. It may give error or incorrect values in the output.
• While searching for number or date values in the first column, ensure that data is stored in appropriate format (not as text format)