Hosne Ara
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.
Now, I will filter the above data for the Apple Watch, which results in:
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.
- As a result, the Go To Special dialog comes up. Now, click on the Visible cells only and press OK.
- Then, only visible cells are selected as shown in the below screenshot.
- 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.
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
- How to Move a Group Of Cells in Excel
- How to Shift Cells Up in Excel
- How to Shift Cells Down in Excel
- How to Shift Cells Down in Excel Without Changing Formula
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.
- 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.
- 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.
- 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.
- Then press Alt + ; to select the visible cells in the selection.
- When only visible cells are selected, type the cell reference of the filtered data in Cell F5.
- Then press Ctrl + Enter.
- Consequently, we will get the below result. We can see all the filtered cells are moved here.
- 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
- How to Move Cells in Excel with Arrow Keys
- How to Make Excel Move Automatically to the Next Cell
- How to Shift Cells Right in Excel
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.
Later, I filtered the above data for Apple Watches. Now I will move the sales data from column D to column C.
To perform the task, follow the below steps.
Steps:
- First, select data from both columns (columns C & D) by pressing the Ctrl key.
- 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.
- 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
- How to Move Cells in Excel Without Replacing
- Fix: Excel Cannot Shift Nonblank Cells
- How to Use the Arrows to Move Screen Not Cell in Excel
- Move and Size With Cells in Excel
- Move One Cell to Right Using VBA in Excel
- How to Perform Double Click Cell Jump in Excel
- [Fixed!] Unable to Move Cells in Excel
- How to Move Down One Cell Using Excel VBA
