Entering the Same Data Over and Over Again

In Excel, in that location are several ways to filter for unique values—or remove indistinguishable values:

• To filter for unique values, click Data > Sort & Filter > Advanced .
  

• To remove duplicate values, click Information > Data Tools > Remove Duplicates.
  

• To highlight unique or duplicate values, employ the Provisional Formatting control in the Style group on the Home tab.

Filtering for unique values and removing indistinguishable values are two like tasks, since the objective is to present a list of unique values. In that location is a critical deviation, however: When you filter for unique values, the duplicate values are just hidden temporarily. However, removing duplicate values means that you are permanently deleting duplicate values.

A duplicate value is one in which all values in at least one row are identical to all of the values in another row. A comparison of duplicate values depends on the what appears in the cell—not the underlying value stored in the cell. For case, if you take the same date value in different cells, i formatted every bit "3/8/2006" and the other equally "Mar eight, 2006", the values are unique.

Check before removing duplicates: Before removing duplicate values, it's a good idea to outset endeavour to filter on—or conditionally format on—unique values to confirm that you achieve the results you expect.

Follow these steps:

1. Select the range of cells, or ensure that the active cell is in a tabular array.

2. Click Data > Advanced (in the Sort & Filter group).

   

3. In the Avant-garde Filter popup box, do one of the following:

To filter the range of cells or tabular array in place:

• Click Filter the list, in-place.

To copy the results of the filter to another location:

• Click Re-create to some other location.

• In the Copy to box, enter a cell reference.

• Alternatively, click Collapse Dialog to temporarily hide the popup window, select a cell on the worksheet, and and then clickExpand .

• Cheque the Unique records only, then click OK.

The unique values from the range will copy to the new location.

When you remove duplicate values, the only effect is on the values in the range of cells or table. Other values outside the range of cells or tabular array volition non alter or move.  When duplicates are removed, the offset occurrence of the value in the list is kept, merely other identical values are deleted.

Because y'all are permanently deleting information, it's a good idea to copy the original range of cells or table to some other worksheet or workbook earlier removing indistinguishable values.

Follow these steps:

1. Select the range of cells, or ensure that the agile cell is in a tabular array.

2. On the Data tab, click Remove Duplicates (in the Data Tools group).

   

3. Do one or more of the following:

   • Under Columns, select one or more columns.

   • To apace select all columns, click Select All.

   • To quickly clear all columns, click Unselect All.

     If the range of cells or table contains many columns and you want to simply select a few columns, you may find it easier to click Unselect All, and then under Columns, select those columns.

     Note:Data will exist removed from all columns, even if you don't select all the columns at this pace. For example, if you select Column1 and Column2, simply not Column3, then the "central" used to find duplicates is the value of BOTH Column1 & Column2.  If a duplicate is found in those columns, so the entire row will be removed, including other columns in the table or range.

4. Click OK, and a bulletin will announced to indicate how many duplicate values were removed, or how many unique values remain. Click OK to dismiss this bulletin.

5. Undo the change past click Undo (or pressing Ctrl+Z on the keyboard).

Note:Yous cannot conditionally format fields in the Values area of a PivotTable report by unique or duplicate values.

Quick formatting

Follow these steps:

1. Select i or more cells in a range, table, or PivotTable report.

2. On the Home tab, in the Manner group, click the small arrow for Provisional Formatting, and then click Highlight Cells Rules, and select Duplicate Values.
   

3. Enter the values that yous desire to use, and then choose a format.

Advanced formatting

Follow these steps:

1. Select 1 or more than cells in a range, table, or PivotTable report.

2. On the Abode tab, in the Styles grouping, click the arrow for Conditional Formatting, and and then click Manage Rules to brandish the Conditional Formatting Rules Director popup window.
   

3. Practise i of the following:

   • To add a provisional format, click New Dominion to display the New Formatting Rule popup window.

   • To modify a conditional format, begin by ensuring that the advisable worksheet or table has been chosen in the Evidence formatting rules for list. If necessary, choose another range of cells by clicking Collapse button in the Applies to popup window temporarily hide it. Cull a new range of cells on the worksheet, and so expand the popup window once more . Select the dominion, and then click Edit rule to display the Edit Formatting Dominion popup window.

4. Nether Select a Rule Type, click Format only unique or duplicate values.

5. In the Format all list ofEdit the Rule Description, cull either unique or duplicate.

6. Click Format to display the Format Cells popup window.

7. Select the number, font, border, or fill format that yous want to utilise when the cell value satisfies the condition, and then click OK. You can choose more than one format. The formats that you select are displayed in the Preview panel.

In Excel for the spider web, y'all tin remove duplicate values.

Remove indistinguishable values

When yous remove indistinguishable values, the but effect is on the values in the range of cells or table. Other values outside the range of cells or table will not modify or move. When duplicates are removed, the first occurrence of the value in the list is kept, but other identical values are deleted.

Of import:Y'all can always click Disengage to get back your data subsequently you have removed the duplicates. That being said, it'due south a good thought to copy the original range of cells or table to some other worksheet or workbook before removing duplicate values.

Follow these steps:

1. Select the range of cells, or ensure that the active prison cell is in a table.

2. On the Information tab, click Remove Duplicates .

3. In the Remove Duplicates dialog box, unselect whatsoever columns where you don't want to remove duplicate values.

   Notation:Data will be removed from all columns, even if you don't select all the columns at this footstep. For example, if y'all select Column1 and Column2, but not Column3, so the "key" used to find duplicates is the value of BOTH Column1 & Column2.  If a indistinguishable is institute in Column1 and Column2, then the entire row will be removed, including data from Column3.

4. Click OK, and a message volition appear to indicate how many duplicate values were removed. Click OK to dismiss this message.

Note:If you want to become back your information, simply click Undo (or press Ctrl+Z on the keyboard).

rosanoveleoper.blogspot.com

Source: https://support.microsoft.com/en-us/office/filter-for-unique-values-or-remove-duplicate-values-ccf664b0-81d6-449b-bbe1-8daaec1e83c2

Share this post