Paste into visible cells only (Excel 2011)

[moodiel]

Hi,
In Excel 2011 for Mac, can you please kindly advise how one can paste a set of data copied from a worksheet into a different worksheet which has been filtered? Excel copies the data into simultaneous rows & columns and therefore copies some of the data into the unseen (filtered) rows, which is what I don't want it to do. Thank you.

 

[James_C]

Not sure exactly what you are doing, but easiest method is to take the filter off , from the Menu Data>Filter, copy and insert the data - I always select the rows, rather than the cells to avoid the risk of destroying the integrity of the database, my only moving down some of the columns of the destination database. Then turn filtering back on.

 

[moodiel]

Dear James,
Thanks for taking the time to respond. I am working on a packaging database and the data is listed according to sequential barcodes (and not according to suppliers). In order to update the information per supplier I am grouping the original data together as one set of data based on the specific supplier. Therefore the filter is set according to the different suppliers. The new updated information is in a separate worksheet and captured per supplier, therefore grouped in sequential rows. When I paste this updated information into the filtered section then the rows that are hidden are changed accordingly and therefore only part of the visible rows are updated. Hope you can make sense of the problem!

 

[James_C]

Ok - This is what you need to do.

1 Take the filters off (Data>Filter) so all the data is shown. I presume in this state the list - "Database" is sorted by Barcode.

2. Add the new data at the bottom on the table. This may mean that database is now not sorted by Barcode as the new items may be out of order.

3.Make sure there is no blank cells between the Column headers and the first row of data in the database. So if the Column headers are in Row 1, the first data row should be in row 2.

4. Select the entire table - You can do this by mouse or via the menu - Edit > Go To... Then click special and select the radio button current region - This should select the entire table - I presume you don't have any totals at the bottom on the table. If you do, you need to make sure that there is a blank row between the last data row and the totals.

5. With the entire table selected sort the table by Barcode to do with via the menu Data>Sort. A dialog box will come up, click the blank highlighted area under column to select the Column label for whatever you called the Barcode Column. Then click ok - Your database will now be sorted into the correct (barcode) order.

6. Click on any cell in the table and then turn filters back on - Data>Filter. When you then select a particular supplier by clicking the filter dropdown in the suppliers column label, the data for the supplier will be filtered and shown in barcode order.