Introduction: Procedure to Copy Cells on Filtered Values Using Excel Concatenate Function

By Banaao - A Makers' Playground

Many of excel junkies must have faced a situation where while sanitizing heaps of data we have filtered values in one column and want to paste the values there. Don’t dare to copy the cells and directly paste it, you will end up over-writing existing cells. However, if you have a single value/cell to be replaced, you can go ahead with that conventional technique.

In this article , I would be discussing about a simple technique which employs an in-built function to edit filtered cells without overwriting. The existing techniques make use of some differencing element like ‘X’ etc. But, that’s not required here.

Let’s get started. Below is a step by step guide to help you sail through excel –o-mania :

The sample problem is as follows:

I have 3 columns, ‘Numbers’, ‘Alphabets’ & ‘Alphanumeric’. My aim is to place the required values in the new column corresponding to alphabet ‘A’. The values are taken from the column ‘Alphanumeric’.[ You may even want to copy filtered cells to some other column with filter applied to it.]

Step 1: Applying Filters

1. First screenshot` is the excel sheet which contains the three columns as mentioned in the sheet.

2. Now, apply filter for these columns and add another column, ‘Alphanum’. Also, apply filter on ‘A’ in the column ‘Alphabets’.

Step 2: The Error Case

3. Select the cells which you want to add in the newly added column. I would be copying the the visible values in column ‘Alphanumeric’.

4. Now, just to highlight the error that creeps in when you directly place the copied cells. As shown in the screenshot the cells between value 3A isn’t visible and have over-written the cells.

Step 3:

5. Now, let’s try a new technique. Use a function ‘Concatenate’. Yeah, I know it is meant to concatenate two cells but here, we are going to use it for a single cell only.

6. Now drag and drop the cell so as to copy the formulae to below rows.

Step 4: The Result

7. Now remove the filter from the table. As you can see , the values corresponding only to ‘A’ have been copied. Now, you may copy and paste by values so as to remove formulas from the sheet.