Effortlessly Copy Visible Cell Data and Paste Filtered Data Rows with These Simple Tricks

Effortlessly Copy Visible Cell Data and Paste Filtered Data Rows with Simple Tricks

Have you ever found yourself in a situation where you need to copy only the visible cells or rows of data from a filtered table in Microsoft Excel? If so, you're not alone. This is a common scenario faced by many users, but the solution can be elusive. This article will provide you with simple and effective tricks to quickly copy visible cell data and paste filtered data rows in Excel.

1. Alt +;: Copy Visible Data in a Column

The first trick is a quick and easy way to copy only the visible data in a single column after filtering. Here's how:

1. Apply a filter to your table
2. Select the column you want to copy
3. Press and hold the Alt key
4. While holding the Alt key, press the semicolon key (;)

All the visible data in the selected column is now copied to the clipboard. You can then paste it elsewhere in the same workbook or in a different application.

Table.SelectColumns("Table1", {"Column1"})
Table.Copy()

2. F5 -> Ctrl + Shift + *: Copy Filtered Data Rows

The second trick is especially useful when you need to copy multiple rows of data that have been filtered. Here's how:

1. Apply a filter to your table
2. Press the F5 key to activate the 'Go To' dialog box
3. Press the Ctrl and Shift keys simultaneously
4. While holding the Ctrl and Shift keys, press the asterisk key (*)
5. Click 'OK' in the 'Go To' dialog box
6. All the filtered data is now selected, right-click and choose 'Copy'

You can then paste the filtered data rows elsewhere in the same workbook or in a different application.

FilteredRange = Table.SelectRows("Table1", each [Column1] < 10)
FilteredRange.Copy()

Troubleshooting Common Issues

When using the aforementioned tricks, you might encounter some issues, such as copying hidden rows or not being able to paste the copied data as values. Here's how to address these issues:

Copying Hidden Rows

To ensure that hidden rows aren't inadvertently copied, carefully follow the instructions provided. If you find that hidden rows are being included, you can adjust the filter settings to hide the unwanted rows before using the tricks.

Pasting as Values

When pasting the copied data into a new location, you might want to paste it as values to avoid any linking or formatting issues. To do this:

1. Right-click the destination cell or range
2. From the context menu, click 'Paste Special'
3. Select 'Values' or 'Values and Number Formats' as appropriate

• Use Alt + ; to copy visible data in a filtered column
• Use F5 -> Ctrl + Shift + * to copy filtered data rows
• Paste as values to avoid formatting or linking issues

References

• Microsoft Support.

  Copy Visible Cells in a Filtered List

• Microsoft Support.

  Copy a Range of Cells

• O'Leary, J. (2021)

  Copy Filtered Rows to a New Location