How to remove just the value in a cell in a row

Occasional Visitor

I want to remove duplicate values in a column, but just the value in the cell. I don't want to remove the entire row and move all the rows up. Attached is a screenshot of what I'm looking to do. On the left is what I want to remove the duplicates from, and on the right is what I want it to look like. I have a spreadsheet with ~300,000 rows that I need to run this on.




2 Replies



To do that, follow these steps...

  1. In cell D2, place the formula =COUNTIF(A$2:A2,A2)=1 and double click the fill handle to copy it down.
  2. Type Flag in cell D1.
  3. Select cell D1 and press Ctrl+Shift+L to apply Filter.
  4. Expand the filter dropdown in D1 and uncheck (Select All) and check False and click on OK.
  5. Now select the first cell in column A below the column header 'Color', in this case, it would be cell A3 as per your sample data and press Ctrl+Shift+Down Arrow Key to select all the visible values in column A.
  6. Press Delete from the keyboard to delete all the selected values in column A.
  7. Press Ctrl+Shift+L again to remove the Filter and you will have the desired data in the end.

Delete Duplicate Values.jpg


I use the formula "if".
put formula in a new row, "=if(A2=A1,"",A2), you will get a new row exactly the same as you wish, and then just copy this new row and replace row A. be careful to paste as value.