Sunday, October 26, 2014

Excel Shortcut - Paste visible cells only,how to paste across filters without ruining your dataset

If you've worked with large data sets which are constantly changing in Excel then chances are you've needed to paste a value across a set of filtered cells.

There’s a shortcut for this that will enable you to paste across a set of filtered values without having to paste one-by-one on the filtered data.

The answer is simple, use the shortcut Alt ;

By pressing these two keys at the same time you can select only the visible data and then use the Ctrl V shortcut to paste copied values.

A quick walk-through:

I have an excel file with population by country. 

What if for some reason Afghanistan and Zambia merged and formed a new country but kept the name Afghanistan. I might want to use a filter to pull up the two countries.

Copying and pasting the value down across the filter risks pasting “Afghanistan” across the data filtered in between.

Instead we can copy an instance of Afghanistan (Ctrl+C), then highlight (select) the data and press Alt and ; at the same time. The data set should then look like below:

Now finally just press Ctrl+V to paste the values to only the visible cells.
The integrity of the final data-set should be maintained when unfiltered.