Copying (Not) With Filters On

Excel’s filters offer an extremely powerful way to analyze information, but I avoid copying when filters are activated. I’ll explain why in this article, and show you another, safer technique to use.

Click this link to open an Excel 2007 file (zipped, no macros) that we’ll use to discuss this issue. Note that the letter “C” appears three times in Column A, as shown here:
CopyingNOTWithFiltersOn_05b_Start

Let’s assume we want Column B to contain just the “C’s” from Column A, and we decide to do this by copying.

  • Filter the first column to show only C’s. The worksheet should look as shown here:
    CopyingNOTWithFiltersOn_10b_Filtered
  • Select the three cells containing the letter “C” and copy to Cell B2. The worksheet should look as shown here:
    CopyingNOTWithFiltersOn_15b_After_Paste

Did you notice what happened. Where are the other two “C’s” that we pasted? Let’s find out.

  • Clear the filters from Column A. The worksheet should now look as shown here:
    CopyingNOTWithFiltersOn_20b_AfterPaste_FiltersOff

Do you see how Excel did not paste the cells where we wanted? Further, can you imagine the damage that would occur if there were other values in Column B that we overwrote? This is the reason I avoid copying when Filters are being used.

So, is there another, safer way to accomplish this task? Absolutely.

  • Click the Undo icon multiple times until the file is back to it’s original appearance.
  • Filter the first column to show only “C’s” as we did earlier.
  • In Cell B2, enter the formula “=A2” and press the Enter key.
  • Click Cell B2 again.
  • While holding the Shift key down, press the Down Arrow key twice. The worksheet should now look as shown here:
    CopyingNOTWithFiltersOn_25b_AfterHighlightingCells
  • In the Home tab, in the Editing group, click the Fill icon CopyingNOTWithFiltersOn_30a_FillIcon.
  • In the list that appears, click Down.
  • The worksheet should look as shown here:
    CopyingNOTWithFiltersOn_35a_AfterFillDown
  • Clear the filters from Column A again. The worksheet should look as shown here:
    CopyingNOTWithFiltersOn_40a_AfterClearFilters
  • Note that Column B now has the correct values from Column A.

For the reason shown in this article, I make it a practice to never copy when filters are on.

For information on how to use Filters, including multiple Filters and custom Filters, please see Chapter 6, “Using Filters to Analyze Data” in the book “Simply Learning Excel 2007” available at Amazon.com.

This entry was posted in Excel Filters. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>