masthead for advanced excel

Hidden secrets in Excel Quick Access Toolbar Part 2

Have you ever encountered the situation like this: You want to copy and paste only the visible part of a range that contains hidden columns/rows, like the one below:

One work around is to go to Home->Editing->Find and Select->Go to special

Then choose "Visible cells only" from the window.

However this method involves a number of clicks and you have to spend a couple of seconds to locate the menu every time you perform the task. If you have a worksheet with many hidden rows/columns, it will be very tedious.

To improve on the efficiency of performing such task, we can make use of the Quick Access Toolbar and utilize "Select Visible Cells" command. Follow the steps below:

1. Click on the inverted triangle on the QAT and choose "More Commands"

2. In the customization window, choose all commands from the dropdown list

3. Scroll down to find the "Select Visible Cells" command and click on "Add" button to add the command to the QAT and make sure the command appears in the box on the right hand side. Click "OK"

Now go back to the worksheet and just click "Select Visible Cells" in the QAT for once after you select the range. You save all the time navigating through the menus and windows! If you are a fan of shortcut key, simply activate this feature using the combination "ALT+;".


New! Comments

Have your say about what you just read! Leave me a comment in the box below.