Showing posts with label Shortcut keys. Show all posts
Showing posts with label Shortcut keys. Show all posts

Copy visible cells only

You have a table of data and you hide some rows and columns and you want to copy only the visible cells into another sheet? I’m going to show you some techniques that you can use to do that. Let’s see how our original table looks like:

Copy_Visible_only1

Now, let’s hide some rows and columns so that our table looks like this:

Copy_Visible_only2

If you select the entire table (cells A1:E12) and just copy and paste (Ctrl+C and Ctrl+V) it into another sheet, it would copy all rows and columns and would look like the original table. That’s not what we want to do, we just want to make a copy and show only the 7 rows and 4 columns that we see on the image above. For that we need to select the visible cells only so that we can copy only those. Let’s select again cells A1:E12 but this time we’re going to select first only the visible cells, before copying. We can do this by just using the shortcut Alt+; (semicolon) or you can go to the Home menu bar, on the Editing group, and click on the Find & Select button and choose Go To Special option.

Copy_Visible_only3

Instead of going to the menu bar, you can just press F5 or Ctrl+G to open the “Go To” dialog box and then just press the Special button on the bottom. On the Go To Special dialog box, select the “Visible cells only” option:

Copy_Visible_only4

This will select only the visible cells, as you can see on this image:

Copy_Visible_only5

Now you can just Ctrl+C (Copy) and Ctrl+V (Paste) it into another location. This will only copy the visible cells, like you wanted.
If you are working with Filters, you don’t need to use this techniques because, when you copy filtered data, it only copies the visible cells already.

Excel Shortcut Keys — Part II

In my previous post about Excel shortcut keys I’ve showed you some of the most basic shortcuts that you can use on Excel. There are many more shortcut keys in Excel and the best way for you to memorize all of them is to have printed Quick Reference Cards next to your computer to check anytime you need.
Here you can find a good and complete set of this Quick Reference Cards for Excel 2010:

Keyboard shortcuts - Ctrl keys

Keyboard shortcuts - Function keys

Keyboard shortcuts — Miscellaneous

Moving around Excel sheets

Excel lists all of your sheet names on the Sheets Tab, on the bottom of your page, all except the ones that are hidden. For example, you can have your sheets tab like this:

Sheetstab1

You can change your sheet just by clicking your sheet name on the sheets tab bar and if the sheet name isn’t visible, you can scroll using the little arrows located on the left side of your sheets tab. You can also more around your sheets by using Ctrl+Page Up or Ctrl+Page Down. On this example, we have a sheet for each month (January to December) but it only displays January to May. That is because there isn’t enough space to display all sheets names. You can adjust the space my moving the separator to the right to increase the space for the sheets names but it can happen that even so, there isn’t space for all of your sheets, as you can see on this example:

Sheetstab2

On this case, you can right click on one one the navigation arrows that are on the left side of your sheets tab and it will display a pop-up window with all of your sheets names where you can select the sheet to where you want to go. It will show up like this:

Sheetstab3

I bet that this little trick will save precious time when you’re working with large workbooks that contains dozens or even hundreds of sheets!

Excel Shortcut Keys — Part I

Excel has very useful shortcut keys that helps you save time while inserting data on your worksheet. This is the first of a series of posts about Excel Shortcut Keys.

Ctrl + Shift + ;  Enter the current date on a cell
Ctrl + B  Bolds highlighted selection on sheet
Ctrl + I  Italics highlighted selection on sheet
Ctrl + U  Underlines highlighted selection on sheet
Ctrl + Z  Undo last action
Alt + =  Create a formula to sum all of the above cells
Ctrl + Shift + $  Formats the selected cells as currency format
Ctrl + Shift + #  Formats the selected cells as date format
Ctrl + Shift + %  Formats the selected cells as percentage format
Ctrl + Space  Selects the entire column where the cursor is placed
Shift + Space  Selects the entire row where the cursor is placed