Microsoft

Three timesaving Ctrl-key tricks in Excel

Working with Excel data is a whole lot easier when you know a few shortcuts -- like these easy efficiency-boosting keyboard tricks.

Trick 1: Fast navigation

When you press Ctrl and any arrow key (north, east, south, or west), you jump to the last populated cell in that direction. Think of using the Ctrl-arrow key shortcut as an alternative to pressing Page Down to find the bottom row of a data set or pressing Tab to find the last column. Bonus tip: Hold down the Shift key while you press any Ctrl-arrow key shortcut to select all the cells between where you are and where you jump with the Ctrl key.

Trick 2: Noncontiguous selections

You can select any cells you want, regardless of whether they're next to each other in a row or column. Hold down the Ctrl key while you click on a cell or click and drag through a range of cells. As long as you hold down the Ctrl key, you can click and select to your heart's content.

You can also use this in conjunction with the AutoCalculate function on Excel's status bar to analyze any combination of individual cells or blocks of cells. Just select the values you want to analyze, right-click on the status bar, and choose the desired function to see the results (Figure A).

Figure A

Trick 3: Fast data entry

Suppose you want to put the same string, number, or formula into two or more cells. Using the old-fashioned approach, you'd type the string, number, or formula into the first cell and then copy and paste that entry into the destination cells. But there's a little-known tip that makes short work of placing the same entry in multiple cells. First, select all the cells you want to populate. Type the entry, but don't press Enter. Instead, press Ctrl + Enter. When you do, Excel will copy what you typed into all of the selected cells.

36 comments
elongp
elongp

Please put Office version in Tags or Categories! You used to put alternate instructions for 2007...

swexpert
swexpert

Ever try CTRL and the Tilde key ~ just above the TAB key, that is? It changes the Excel view from showing formula results to showing the formulas being used in each cell. This is an oldie but a goodie that has tagged along since Excel's beginning version, and as far as I can see, is not documented any more. But it still works like a charm, and you can print what you see onscreen. It's my fave!

DonG43
DonG43

I like the examples which explains how to do it, instead of just a list of all that are avaialable. Ones I use a lot are CTRL R to copy the formula from the cell to the left and CTRL D to copy the formula from the cell above. Also CTRL + to insert a column or row and CTRL - to delete a column or row.

brent.doane
brent.doane

I know of only one. The other two will be very useful! THANK YOU!

massco
massco

Marvellous. Where do you guys learn these things? Terry

kellyj12
kellyj12

Trick 1 and 3 are new to me, and will be very helpful. Another one I use a lot is Alt-Enter, which allows wrapping text. It wraps the text where you want it to wrap, independent of cell size

danny
danny

Hi Jeff - Thanks for the article. I use each of the three tips - with a few twists: 1) Tip # 1 - In addition, I like to use Ctrl + End to take me to the last cell in the workbook. Frequently, this does NOT take me to the last cell - it takes me to a remote cell that may have a "forgotten" data entry - or even a cell that has not data but retains "formatting>' This can save a lot of paper - and aggravation - when you print your worksheet! 3)Ctrl + Enter - I use this a lot when I am teaching an Excel class. I select the range and then generate a series of Random Numbers using the =RANDBETWEEN(100,200) Function to populate the range when I press Ctrl + Enter. Of course, I now need to convert the formulas into values - follow this link to my website to watch a short video that illustrates how to do this fast - http://www.thecompanyrocks.com/excels/ms-excel-training/excel-tips/generate-random-numbers-for-excel-spreadsheets/ Thanks again! Danny Rocks www.thecompanyrocks.com

Bob.Ricketson
Bob.Ricketson

Noncontiguous selections are nice until you want to unselect a cell. Excel will not let you do it. You have to start over. This flaw is in Office 2007 as well. Pretty obvious, you'd think that would have been figured out in the first iteration of Excel after the first update.

david.hanshumaker
david.hanshumaker

Jeff, several years ago you gave this trick combined with Goto/Special/Blanks. That was invaluable to me as I use it frequently to fill a column with data. For example, if col A contains product names, with blanks in many rows. Say, A2 is "nuts", A34 is "bolts", and A73 is "wrenches", the column can be filled in very quickly by selecting the cells in col A to be filled, F5 (Goto), Special, Blanks. With only the blank cells now in col A selected, type "=A2" in cell A3 and CTRL+enter. Copy and paste values from col A onto itself, and the column is filled. That is an excellent example of using this trick #3.

johndoe4024
johndoe4024

Never seen this one before, but a fan already!

andy.poulos
andy.poulos

And for those who want to enter today's date, "CNTRL ;"

daniel
daniel

I use alot of shortcuts in Excel. I'm a traimer and I encourage all to learn a few and many do! BTW Ctrl+Enter has another flavour not mentionned here. This one can be used multiple times a day. If you have only one cell selected and type a value/formula pressing Ctrl+Enter will NOT move the active cell down. This way you can go for the formatting option immediately or the Fill handle. Try it you'll like it! Ctrl+Tab : Switch between open workbook Ctrl+PgDn/PgUp : Move to next/prev worksheet Ctrl+[Plus sign] : Insert cells/row Ctrl+[Minus sign] : Delete cells/row Ctrl+* : Select region (this one is amazing) Ctrl+; : Insert today's date Ok that's enough...

staffordd
staffordd

>>>>>Marvellous. Where do you guys learn these things? >>>>>Terry At work. :-) d.

dhays
dhays

Trick # 1 only goes to the last populted cell in the set of populated cells, if you have discontinous populations it does not go to the last cell populted in the direction, only to the last continuously populated one from where you start. (In O2003)

satiutni
satiutni

Thanks Danny like you I have been training in Excel for over 20 years now - great tip about random generation and using the right mouse button to copy and paste values in one go! Like you I sometimes find old data or formatted cells at the "bottom" when using Ctrl-End so I usually select all rows and columns between the real end and the offending cells and delete them to give me a clean sheet then having selected the last cell of data I can use Shift-Ctrl-Home to select the whole sheet for printing copying formatting or whatever. If necessary I can keep Shift held down to deselect title rows and or columns. Sometimes I think just using theses "tricks" makes it all fun! Regards Paul Reader www.intuitas.com

staffordd
staffordd

Hi You said... "This can save a lot of paper - and aggravation - when you print your worksheet!" Surely though before you print, you would select the area you want to print, and use Set Print Area to avoid this problem?? Just wondering. I always use Set Print Area, then Print Preview, then Print. That way, you NEVER get any cells in your print that you don't want or need. Or - is there a better way? thanks d.

daniel
daniel

Have you heard of ASAP utilities. It is an add-in that has a feature allowing you to remove a selection from a noncontiguous selection. Google 'Excel ASAP Add-in'.

jngant
jngant

One can use the currently selected cells or ranges to copy (use Ctrl-C) or cut and paste (Ctrl-X) on the source cell(s). Then you can hold the Ctrl key down while you select non-contiguous cells (or clumps if you selected several for the copy or paste), THEN hit Enter. This is useful if for instance you wish to copy a date or item to several different lines in a SS. Be careful how you use it, tho; if several cells are selected for copying (or pasting), remember the entire clump will be moved to each selected destination.

vjanecky
vjanecky

CNTRL + ' This will copy exactly what is in the cell above it.

DonG43
DonG43

David: I have been writing complicated formulas to do that for the past nine months. This is a fantastic trick; one I will use frequently.

pcorneillie
pcorneillie

Usefull and spot on (not a list of the most 100 used tricks) Another one I use often is to fill in horizontal series. If you press after one entry, you navigate to the next cell down. If you select the horizontal area you want to fill in, pressing enter moves you to the next cell in that selection and not down;

Rick_from_BC
Rick_from_BC

yep, I use that one a lot, too. It's a fast way to speed entry work, because you only need to fill in one cell of the column with the word "Nuts" or "bolts" and then go back with a simple formula to add those words to all the blank cells. Works on the Mac and the PC at least in versions 2000 and 2003.

Navyman
Navyman

I use the control ; all the time to enter the date. Great time saver, especially if you forget today's date.

twistedg
twistedg

After removing the useless rows of unseen data, you will need to save before Excel 'forgets' and resets the position. After that you can use Ctrl+End to get to the real end of data.

dhays
dhays

That is the way I do it.

satiutni
satiutni

Hi, In a sense everyone here is right about printing. As ljturk points out you would use set print area to consistently ignore those parts of the sheet you would not wish to print, macro entries, lookup tables etc. Alternatively simply printing will produce the whole sheet unless you use the print selection option in the print dialogue - which is where making a selection using these tips can be useful, especially if you only want a temporary print of selected cells for a particular purpose. Thanks to all - I think sharing this type of information is great Paul

rturk
rturk

Hi D, Typically if your sheets are set up properly, without extraneous cells containing formatting or spaces, you would just click print to print the entire sheet. You would set a print area if you only want to print a section of your data printed. This feature should not be utilized for all printing.

dhays
dhays

ctl + appears to only shift what is in the cell, not copy it.

Roomale_uk
Roomale_uk

More of these .. CTRL + Inserts cells, rows or columns CTRL - Deletes cells, rows or columns CTRL+1 Displays the Format Cells dialog box. CTRL+2 Applies or removes bold formatting. CTRL+3 Applies or removes italic formatting. CTRL+4 Applies or removes underlining. CTRL+5 Applies or removes strikethrough. CTRL+6 Alternates between hiding objects, displaying objects, and displaying placeholders for objects. CTRL+8 Displays or hides the outline symbols. CTRL+9 Hides the selected rows. CTRL+0 Hides the selected columns. CTRL+A Selects the entire worksheet. CTRL+SHIFT+A inserts the argument names and parentheses when the insertion point is to the right of a function name in a formula. CTRL+B Applies or removes bold formatting. CTRL+C Copies the selected cells. CTRL+D Uses the Fill Down command to copy the contents and format of the topmost cell of a selected range into the cells below. CTRL+F Displays the Find and Replace dialog box, with the Find tab selected. SHIFT+F5 also displays this tab, while SHIFT+F4 repeats the last Find action. CTRL+SHIFT+F opens the Format Cells dialog box with the Font tab selected. CTRL+G Displays the Go To dialog box. F5 also displays this dialog box. CTRL+H Displays the Find and Replace dialog box, with the Replace tab selected. CTRL+I Applies or removes italic formatting. CTRL+K Displays the Insert Hyperlink dialog box for new hyperlinks or the Edit Hyperlink dialog box for selected existing hyperlinks. CTRL+N Creates a new, blank workbook. CTRL+O Displays the Open dialog box to open or find a file. CTRL+SHIFT+O selects all cells that contain comments. CTRL+P Displays the Print dialog box. CTRL+SHIFT+P opens the Format Cells dialog box with the Font tab selected. CTRL+R Uses the Fill Right command to copy the contents and format of the leftmost cell of a selected range into the cells to the right. CTRL+S Saves the active file with its current file name, location, and file format. CTRL+T Displays the Create Table dialog box. CTRL+U Applies or removes underlining. CTRL+SHIFT+U switches between expanding and collapsing of the formula bar. CTRL+V Inserts the contents of the Clipboard at the insertion point and replaces any selection. Available only after you have cut or copied an object, text, or cell contents. CTRL+ALT+V displays the Paste Special dialog box. Available only after you have cut or copied an object, text, or cell contents on a worksheet or in another program. CTRL+W Closes the selected workbook window. CTRL+X Cuts the selected cells. CTRL+Y Repeats the last command or action, if possible. CTRL+Z Uses the Undo command to reverse the last command or to delete the last entry that you typed. CTRL+SHIFT+Z uses the Undo or Redo command to reverse or restore the last automatic correction when AutoCorrect Smart Tags are displayed.

Walmone-Hadwor
Walmone-Hadwor

This Excel 2007 tutorial can really help you to save plenty of your precious time by avoiding those mistakes. Here are the tricks: Tip 1: When the equal sign is placed as the first character in a cell that has not been pre-formatted as text, it tells Excel to create a formula. All Excel formulas start with the "equal" sign, that is = sign. ----------------------- http://www.earthlance.com/

drbayer
drbayer

I use ctrl + ; frequently to insert the date. Don't forget ctrl + shift + ; to insert the current time. I don't use that nearly as often, but occasionally I need a time stamp.

staffordd
staffordd

rlturk replied to my post about using Set Print Area "Reply to "but surely..." Hi D, >>>>Typically if your sheets are set up properly, Hello - not to split hairs, but your reply contains far more subjective statements than mine ever did. I suggested using Set Print Area because what it does is, it ELIMINATES DOUBT. You are making a conscious decisions about what you are, and AREN'T, going to print. When you say "if your sheet is set up properly" - well - what does that MEAN? "Properly" according to a complete novice might mean some columns and rows with numbers in them, and one total. And "properly" - if undefined, how does that even help? >>>>without extraneous cells containing formatting or spaces, THIS is the definition of "properly"??? I think many Excel experts might well disagree. This is, at best, a partial definition, but again, how does a novice even KNOW if they have "extraneous cells" or formatted cells or spaces?". Maybe they created them when they first made the sheet, without realising... >>>you would just click print to print the entire sheet. You SHOULD be able to, but experience has taught me, that sometimes when you do that, you get a disaster. You get 50 sheets of paper streaming out of your printer. So, my suggestion was intended to help novice or other less experienced users (and I would class myself with years of experience but very, very little actual knowledge) to AVOID print errors - and the way to do that, the SAFEGAURD, is to SET PRINT AREA like it's a religion. I like to DEFINE things. Not leave them to chance. YOU reduce your likelihood of misprints by setting up your sheets "properly". But what about shared sheets, where you are not the only author? When your colleague comes along and "works on the sheet" and messes it all up - you will still trust that you can just hit PRINT and all will be well? I don't. I don't trust anything or anyone, because if you do, you WILL be disappointed. Better safe than sorry is the logic behind my suggestion, whereas you seem to be attempting to define something that is surely undefinable/subjective beyond belief - namely that there is a "standard way" to set up a sheet "properly" - my God, there must be about 3 billion "standard ways" (insert number of actual Excel users worldwide into where I have guessed "3 billion"). >>>You would set a print area if you only want to print a section of your data printed. But you ALWAYS want a "section" of your data printed, from one cell, to one small table section, or a few tables, or a pivot chart, to most of your sheet but not all, onwards and upwards to the whole sheet. And just pressing PRINT might well get you MORE than the whole sheet. So why take a chance? It takes about...what...five-seven seconds to use the Set Print Area function?? - but a LOT of trees could be saved by using it consistently. >>>>This feature should not be utilized for all printing. Another absolute statement in what is a very subjective universe that we live in. For you, that's fine. For the rest of us, maybe some will want to do it your way, some will want to do it my way, some will want to do it a THIRD, possibly better way. But you wrote this as if it were an absolute, which intimated that my suggestion was pretty stupid. Setting that issue aside for a moment, a more accurate statement, taking into account the differing needs of experienced and novice users, might read: "This feature CAN be used if you want a safe way to be absolutely sure you do not accidentally print more than you mean to. For experienced users, who already KNOW all the dangers of "sheets that are not set up properly" it can be dispensed with (ignored)." But ***I*** still do it, every time I print, and I have not wasted paper or ended up with an incorrect print of any kind (i.e. wasted paper/killed trees) for many years now - and why? Because I use Set Print Area. So what may have seemed stupid to you, can be a useful safeguard for less experienced users - and that is most likely who might benefit from this advice. It really is so, so subjective. What is a "properly set up sheet"? It depends on your purpose. And we talking about human beings here - imperfect, forgetful - they might accidentally end up at the bottom of the sheet and format a cell somehow, and then end up killing several trees by just happily pushing PRINT. I try to work from a point of knowledge, not a point of ignorance. So I like to KNOW what is going to happen when I push print - not GUESS. So my "stupid" idea, that you seemed to want to refute, takes the guesswork out, and let's you ensure, at a very small price (7 seconds of your time), that your print contains ONLY what you want, and no trees die in the process. For that matter... NEW TOPIC: And why are we printing in the first place? Surely the whole point of a computer is, you STORE your data (information) electronically, ON the computer, you READ IT on the screen (thus saving another tree), and you SHARE IT via email,shared file structures, or other file sharing programs??? - ANYTHING but paper. Please. My desk has no paper on it whatsoever. If I print something, it's generally because I have to take it to a meeting or other location where there is no computer. And I probably print...3 or 4 sheets of paper a MONTH. If I HAVE to. That's because I use my COMPUTER to store and read information, I don't "automatically" print every single document I come across or work on. Printing is one of the most pointless, wasteful activities man has ever engaged in. We mostly print because that's what we did back in the time of the "paper world". That's the behaviour of the 1950s and 1960s, still haunting us in the Naughties. I walked down the hall this morning, where people are clearing out their offices because they are moving to different offices. Bag after bag after bag of invoices, forms, letters, all "printed" because they were SO important, line the hallway, bag after bag after bag - all now waste, and hundreds of trees dead in the bargain. We HAVE to start thinking about not automatically printing things. I watch it all day long in my office - person sits there, typing a document, stands up, goes and get the print(s). Repeat 20 times that day. Repeat day the next day. And the next, and the next. Like clockwork. In a week, a tree is dead. In a month, 4 trees are dead. Killed by ONE PERSON. There are about 3500 computer users in my building. You count up the death toll. Then multiply by the number of businesses and homes in the world that own and use at least one printer. And where does that paper end up, in a very short time? In those bags I saw in the hallway. Human beings - what can you do with them? Look - I am imperfect, and I don't know ANYTHING. That's where I start from, and see what I can learn. I have learned that I do not like the destruction of the rain forests just so we can print out every email we receive, every spreadsheet we make, every chart we make - and THEN THROW IT AWAY A WEEK LATER. Or keep changing it and re-printing it - even worse. WAIT UNTIL IT'S CORRECT - then print ONE COPY (if you REALLY need to). But never mind, I guess that since I don't know how to set up an Excel spreadsheet "properly", my advice about both Set Print Area, and conserving resources by not printing unnecessarily, are not useful, pertinent or of any value to anyone. If only I was as smart as everyone else - sigh. What is "truth"? Said Pilate. Then washed his hands. What is "properly" Said D. Then washed his hands. "Properly" is the way each of 3 billion different Excel users sets up their sheets - for each person, no matter how bad by YOUR perception - the way they do it, is "properly" FOR THEM. That can't be enforced as an absolute, just as I am not suggesting that Set Print Area should ABSOLUTELY be used EVERY SINGLE TIME (did I actually say that? I certainly don't recall saying that). It's what is *appropriate*, under the circumstances, at the time, and also, each user is entitled to use the tool in the way that best suits their individual needs. I don't think we can IMPOSE anything on anyone. I can't MAKE people stop printing needlessly - but I can say - hey, PLEASE THINK ABOUT THIS. Maybe you want to save a tree. Maybe you want to not have a huge pile of crp on your desk (it's nice, you should try it). Best of luck, and my tendancy is, avoid absolutes. They are a killer, and they just piss people off (in this case, me), if someone tells me DO IT THIS WAY - my tendency is not to. If they say "you MIGHT want to try this, I found it helped me" then I might tend to listen more. A LOT more. For some people, there is only black and white, whereas, I see a uniform and predictable shade of grey - the whole world is nothing but one massive "grey area". There is no absolute way to work with Excel, Access, Word, Power Point, etc. Different users have different ideas which may or may not be useful to OTHER users. And so on... have fun ! :-)

rcstan
rcstan

CTRL + Page Up / Page Down cycles thru Worksheets within a single Workbook. CTRL + Tab cycles thru open Workbooks.

sheila
sheila

Ctrl " will enter into a cell the contents of the cell above.

dhays
dhays

I did not read all of your tips, but one that got my atttention is the ctl-V paste function, if used as suggested it does not replace what is there it moves it down a row, and only copies the last entry just copied or cut, not the whole clipboard. Moving to another location and doing some operation, disables the paste function and you have to use the clipboard's paste function which will paste over what is there.

Editor's Picks