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.


Wednesday, September 4, 2013

Shortcut of the week: Help

When you're stuck trying to figure out how to do something in Excel, sometimes it's easy to find your answer with the help feature. To access the help menu simply press F1 on the keyboard.
From here you can search about your quandary, look for Excel templates, or find out how to use more advanced features in Excel.

Sunday, August 25, 2013

Excel Keyboard shortcut of the week

To quickly add comments to cells, hold shift and press F2. This works for editing the existing comments as well.

Excel and the two most powerful uses of F4

The F4 key can contribute to greater productivity in Excel. This post will examine two quick ways which this key can be used to help users work faster in Excel

Saturday, August 17, 2013

Windows Key Shortcuts

A little bit off topic but a bit of a compliment to Excel are simple windows tips. Since I don't want to write another blog for windows tips, here are some of my most used windows shortcuts.