How to Move Filtered Cells in Excel (3 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

In this article, I will discuss how you can move filtered cells in Microsoft Excel. When we want to copy filtered cells to move to another location, unfortunately, hidden rows are too copied. In such a case, we have to follow alternative techniques to shift only filtered cells. Luckily, there are some options and features available in excel, which we can use to move filtered cells. Let’s have a look at the article.


How to Move Filtered Cells in Excel: 3 Methods

Suppose, we have the below dataset containing state-wise sales data for some electronic devices.

3 Methods to Move Filtered Cells in Excel

Now, I will filter the above data for the Apple Watch, which results in:

3 Methods to Move Filtered Cells in Excel

Later, if I move above filtered cells applying a simple copy-paste method, the following happens which we did not want.

So now I will shift filtered cells using the below methods.


1. Move Filtered Cells Using Go To Special Option

We can use the Go To Special option to move filtered cells to another worksheet in excel. Follow the below steps to do the task.

Steps:

  • First, select the filtered cells and press Ctrl + G to bring the Go To dialog.
  • When the Go To dialog appears, click on Special.

Move Filtered Cells Using Go To Special Option

  • As a result, the Go To Special dialog comes up. Now, click on the Visible cells only and press OK.

Move Filtered Cells Using Go To Special Option

  • Then, only visible cells are selected as shown in the below screenshot.

Move Filtered Cells Using Go To Special Option

  • Finally, copy (Ctrl + C) these selected visible cells and paste (Ctrl + V) them to another excel sheet. From the result, we can see that only filtered cells are present in the new sheet as we wanted.

Move Filtered Cells Using Go To Special Option

Note:

  • You can get the Go To Special dialog by following the path too: Home > Editing > Find & Select > Go To Special.

Read More: How to Move Highlighted Cells in Excel


Similar Readings


2. Excel Quick Access Toolbar to Shift Filtered Cells

In this method, we will add the Select Visible Cells command to the Quick Access Toolbar. Afterward, we will use the command to select filtered cells only and copy them to another worksheet. Follow the below steps to perform the task.

Steps:

  • Firstly, click on the Customize Quick Access Toolbar icon. Next, click on the More Commands from the below menu.

Excel Quick Access Toolbar to Shift Filtered Cells

  • As a consequence, the Excel Options dialog will appear.
  • Now, choose the Select Visible Cells command from All Commands.
  • Add the command to the Customize Quick Access Toolbar area and press OK.

Excel Quick Access Toolbar to Shift Filtered Cells

  • Consequently, you will see that the newly added command is visible in the Quick Access Toolbar.
  • Select the filtered cells, and click on the Select Visible Cells icon.

Excel Quick Access Toolbar to Shift Filtered Cells

  • Finally, as usual, copy the selected cells and paste them into another excel sheet.


3. Use Keyboard Shortcut to Move Filtered Cells in Excel

In this method, I will use keyboard shortcuts to move filtered cells to a new location.

Steps:

  • Initially, select the location where you want to shift the filtered data.

Use Keyboard Shortcut to Move Filtered Cells in Excel

  • Then press Alt + ; to select the visible cells in the selection.

Use Keyboard Shortcut to Move Filtered Cells in Excel

  • When only visible cells are selected, type the cell reference of the filtered data in Cell F5.
  • Then press Ctrl + Enter.

Use Keyboard Shortcut to Move Filtered Cells in Excel

  • Consequently, we will get the below result. We can see all the filtered cells are moved here.

Use Keyboard Shortcut to Move Filtered Cells in Excel

  • Now, if we remove the Filter, we can see that no hidden cells are moved to the new location, only filtered ones are moved

Read More: How to Move Cells in Excel with Keyboard


Similar Readings


Shift Data between Filtered Cells with Excel Fill Option

We can move data between columns in filtered cells. For instance, we have the below dataset containing month-wise sales data for several items.

Shift Data between Filtered Cells with Excel Fill Option

Later, I filtered the above data for Apple Watches. Now I will move the sales data from column D to column C.

Shift Data between Filtered Cells with Excel Fill Option

To perform the task, follow the below steps.

Steps:

  • First, select data from both columns (columns C & D) by pressing the Ctrl key.

Shift Data between Filtered Cells with Excel Fill Option

  • Then from the Excel Ribbon, go to Home > Edding > Fill > Left. I have chosen Left as I am moving data of column D to the left side.

  • As a result, we can see that sales data for February is moved to the column where sales data for January is listed.

Shift Data between Filtered Cells with Excel Fill Option

  • You can remove the Filter and check the moved data.


Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.


Conclusion

In the above article, I have tried to discuss several methods to move filtered cells in Excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.


Related Articles

Hosne Ara
Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo