As you can see, the value 87 appears more than once, so let's delete the duplicates as follows:. You can see the results below. Excel deleted the three duplicate values and moved everything below the removed values the value 82 up, accordingly.
For this reason, you'll want to be careful how you use this feature. You'd probably not want to use this method to delete duplicate values from this particular sheet because of the displaced value.
Now, let's use this feature to delete duplicate rows. There's only one rows 4 and 9 are the same. To delete row 9, do the following:. This time, Excel deleted only one row-row 9-and moved rows 10 and 11 up one row. As you can notice, this time the tool hasn't remove Bill and Jon because the last name is different. But in some situation, you can not used this tool. It's not because there is a bug but because the data are too complex, like in this example.
Duplicate values can be annoying many of the times. As I work on excel regularly and seldom spending find few duplicate cells or vale kills a lot of time. Here we'd simply choose not to include the Added on column when comparing the rows. The end result is that we'd delete the rows where only the Title , Release Date and Director values are the same. If you weren't sure that you wanted to delete your duplicate values but would rather hide them instead, you can still use the same technique that works in Excel To do this:.
You'll see the same dialog box as in Excel Simply check the Unique records only box to hide the duplicates rather than delete them. And that's the end of this blog! There are lots more resources on training in Excel here. When duplicates are removed, the first occurrence of the value in the list is kept, but other identical values are deleted. Because you are permanently deleting data, it's a good idea to copy the original range of cells or table to another worksheet or workbook before removing duplicate values.
If the range of cells or table contains many columns and you want to only select a few columns, you may find it easier to click Unselect All , and then under Columns , select those columns. Note: Data will be removed from all columns, even if you don't select all the columns at this step. Click OK , and a message will appear to indicate how many duplicate values were removed, or how many unique values remain.
Click OK to dismiss this message. You cannot remove duplicate values from outline data that is outlined or that has subtotals. To remove duplicates, you must remove both the outline and the subtotals. For more information, see Outline a list of data in a worksheet and Remove subtotals.
Note: You cannot conditionally format fields in the Values area of a PivotTable report by unique or duplicate values. To change a conditional format, begin by ensuring that the appropriate worksheet or table has been chosen in the Show formatting rules for list.
If necessary, choose another range of cells by clicking Collapse button in the Applies to popup window temporarily hide it. Choose a new range of cells on the worksheet, then expand the popup window again. Select the rule, and then click Edit rule to display the Edit Formatting Rule popup window.
Under Select a Rule Type , click Format only unique or duplicate values. In the Format all list of Edit the Rule Description , choose either unique or duplicate. Click Format to display the Format Cells popup window. Select the number, font, border, or fill format that you want to apply when the cell value satisfies the condition, and then click OK.
You can choose more than one format.
0コメント