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.