Software

Video: Three Excel Ctrl-key tricks for the spreadsheet ninja

Bill Detwiler shows you three handy keyboard shortcuts that will help you work faster in Excel.

Whether you regularly work with Excel or you support users who do, knowing how to get the most from Microsoft's spreadsheet application can make your job a lot easier. During this TR Dojo episode, I show you three handy tricks that will help you work faster in Excel.

For those who prefer text to video, you can click the Transcript link that appears below the video player window or read Jeff Davis' article, "Three timesaving Ctrl-key tricks in Excel," on which this episode is based. If you want even more Microsoft Excel keyboard shortcuts, check out the following downloads and articles:

I also encourage you to browse through TechRepublic's Microsoft Office blog and subscribe to our Microsoft Office newsletter. For the latest TR Dojo lessons, sign up for one or more of the following:

About

Bill Detwiler is Managing Editor of TechRepublic and Tech Pro Research and the host of Cracking Open, CNET and TechRepublic's popular online show. Prior to joining TechRepublic in 2000, Bill was an IT manager, database administrator, and desktop supp...

23 comments
ceji
ceji

Thanks... love it

sravan_kr2000
sravan_kr2000

These are some of the tricks / shortcuts which some seasoned excel user will know. There are many such shortcuts in excel like F4 which will repeat previous command / action, if available. One can find many useful tips if you use F1 (Help -> Keyboard shortcuts). Thanks, Sravan Kr.

Bob.Ricketson
Bob.Ricketson

The Ctrl key is useful but has had a flaw (in Excel only) since the inception of Excel. That is NOT being able to deselect a cell that you selected while doing Ctrl + select cells. Other Office applications will allow you to re-click a cell to deselect it but the only way in Excel is to start all over. Any ideas why something so obvious has survived so long? Thanks, bob

Bo Tym
Bo Tym

I've been a TR member for some time now but don't often find the time to post comments. This video among many others has provided me with a bit of a breather from staring at numbers and letters all day every day. imho there are quite a few ppl here who appreciate what you do, and how you do it. I for one will not complain about the time difference between watching your vids and reading the transcript. Although I do find the transcripts a handy referance on some topics. Now to practice some of these on the spreadsheets related to 10 jobs within cybercrime organizations where I fit in to catorgories 7-10 *laughs maniaclly*

e_caroline
e_caroline

Thanks for the transcript link. I read the entire transcript in less than a minute... the video runs over five minutes. The shortcuts info is useful... but the delivery via video is a timekiller. Slidewshows, videos and PowerPoint presentations too often are used to pad out modest amounts of info into impressive SEEMING communications.

Matthew G. Davidson
Matthew G. Davidson

Bad Post...Skip Right on By!!!! One of my favorite uses for the Ctrl-key in Excel is to size each cell at the same time to the text entered in the cells. After you create the spreadsheet click the "Select All Cells" area between Column A and Row 1 (top left of spreadsheet); hold the control key down; double click the divider line between Column A and B. Your cells are now sized appropriately to the entered text.

The Daleks
The Daleks

There are generally 3 modifier keys that programs use for shortcuts: Ctrl, Alt and Shift. For single-key shortcuts, it's almost always the Ctrl key. That's because the Alt key by itself activates the menu or ribbon and the Shift key by itself types capital letters (duh). Excel works like most other applications. Use Ctrl with the up, down, left, right, home, end, and other keys. And like in most other applications, add the Shift key to a shortcut to select from starting point to ending point, rather than just moving the cursor there. My favorite in Excel is F5: Click cell A1, press the F5 key to display the Go To dialog, then type a cell reference (like H30). Press Enter, and your cursor will be on cell H30. If you do this and press Shift + Enter, you'll select all the cells from A1 to H30. And guess what? In Word, F5 also displays the Go To dialog. (But pressing Shift doesn't do anything.)

babycody
babycody

I have found Ctrl+D to be useful. This is used to duplicate the contents of the cell immediately above the selected cell. I also like Ctrl + ~ which allows you to see the formula instead of the values in every cell.

Gopals
Gopals

Will the same short cuts be used in OpenOffice.org also?

Bill Detwiler
Bill Detwiler

In the above TR Dojo post, I share three handy keyboard shortcuts that will help you work faster in Excel. Original post: http://blogs.techrepublic.com.com/itdojo/?p=1641 In previous episodes, I've also demonstrated shortcuts on the following: Video: Cool, lesser-known Microsoft Word keyboard shortcuts http://blogs.techrepublic.com.com/itdojo/?p=1511 Video: Keyboard shortcuts for moving faster in Windows Vista http://blogs.techrepublic.com.com/itdojo/?p=280 What other applications would you like to see me tackle in a TR Dojo episode?

welldone
welldone

Ctrl + PgDn = next sheet in a workbook Ctrl + PgUp = previous sheet Ctrl + Tab = next open workbook

RU7
RU7

I have become so used to this that it hardly registers anymore. I just become very careful where I click when I get past the 20th or so click.

SgtPappy
SgtPappy

bitching. Some of us like the videos. He supplies a link for people like you to read it. What more do you want?

RU7
RU7

Without the CTRL key.

edh1215
edh1215

You don't need to hold Ctrl for that.

david
david

Ctrl-D and Ctrl-R are the shortcuts for the Edit>Fill functions Down and Right. Extremely useful time savers. Also the Edit>Fill>Series function is often useful, but I don't know of a keystroke for it.

ram
ram

In OpenOffice.org, The first one (Ctrl -> Arrow key, Ctrl->Shift->Arrow key) and second one (Ctrl Select of distinct cells) works similarly. Ctrl->Enter creates n Line break within the same cell.

RU7
RU7

If you are already at the last populated cell in a row or column, whether or not you pressed CTRL Arrow to get there, pressing CTRL Arrow now takes you to the last possible row or column. If you go to the row or column beyond the last populated one and include the Shift key you select all the rows or columns beyond the last populated one. This is good if you are going to hide them to make a pretty form or sheet for display.

RU7
RU7

I don't remember where I found this but it is nifty. Say you have a two or more columns of 100s or 1000s of pieces of data. You want to add a column with a formula that calculates the sum for each row. Type the formula in the top cell and press CTRL+Enter. Now double-click the fill-handle. The formula is copied down to the last cell with data in the adjacent cell.

pmccracken
pmccracken

I have to use VLOOKUP alot to add information to spreadsheets. I came across using the F4 key for inserting the $ for me to get absolute values for copying from one sheet or workbook to another. You just click in the formula area(s) and hit the F4 key.

Prague
Prague

I like F2 when returning to a cell with a formula. For example, the cell may contain the results of a formula and you want to tweak it. You bring the cursor to the cell you want to edit, and hit F2 and you can edit the formula in place. It's easy to remember because it's the same keystroke used to rename a file and the UI is the same.

erimaster
erimaster

Selecting all cells in a table including those that may be occasionally blank in a column. Especially helpful for macros. I saw this once. It involves going to the bottom cell in the spreadsheet 65536 then up to the last populated cell.

mafergus
mafergus

Might be a good time for Windows 7 shortcuts.