Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Quickly Entering Data.

Quickly Entering Data

by Allen Wyatt
(last updated March 16, 2017)

13

One of the nifty shortcuts provided in Excel allows you to copy data from the cell above the current cell. All you need to do is press Ctrl+". (That's a quote mark, which means you must hold down the Shift key as well.)

As an example of how this works, let's suppose you enter the number 12345 in cell A4. If you then move to cell A5 and press Ctrl+", 12345 appears in the cell. If you then go to cell A6 and type ABC and press Ctrl+", you end up with ABC12345 in the cell.

In reality, you could also use Ctrl+' (that's an apostrophe) to copy data if you wanted. There is only a slight difference in the way that Ctrl+' and Ctrl+" work, and that difference only applies when using the shortcuts to copy numeric values (including dates and times). The Ctrl+" shortcut retains the formatting of whatever is being copied, but the Ctrl+' shortcut does not.

As an example, let's assume that cell A5 contains the formatted value 12.34%. If you position the cursor in cell A6 and press Ctrl+", then Excel copies 12.34%. If, instead, you use Ctrl+' then Excel copies 0.1234.

These shortcuts can be real handy when you are entering data that has a lot of similarities within it—perhaps inventory numbers or part numbers. Give it a try!

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7925) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Quickly Entering Data.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Blank Page Printing after Table at End of Document

When you print, do you get an extra blank page printed at the end of the document? It could be because of the final paragraph ...

Discover More

Using the GotoButton Field

Need to jump from one place in your document to another? One way to do this is through the user of the GotoButton field, ...

Discover More

Getting a Word Count

It is often helpful to know how many words you've typed in a document. Perhaps you have a target word count, or you can't go ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Converting Forced Text to Numbers

If you have some numbers stored in cells that are formatted as text, you may get some surprises when you try to use those ...

Discover More

Clearing and Deleting Cells

When you want to remove information from a worksheet, you can either clear cells or delete cells. This tip examines the ...

Discover More

Typing Check Marks into Excel

Need to enter a check mark into a cell? There are a number of ways you can get the desired character, depending on the font ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 8Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 - 3?

2017-04-01 09:48:21

Adil Sardar

Now I have a nifty tool on the subject, thanks Allen


2017-03-18 04:26:07

Michael (Micky) Avidan

@Ricardo Ribas,
I'm sure you understand, now, the "BIG" difference between 3 words and 190(!) words.
Thanks for the detailed explanation.
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2017)
ISRAEL


2017-03-17 08:52:42

Ricardo Ribas

Micky,

Try copying a formatted number ($ 1,234.00, plus Bold, Italic, Underline, fill color, text color) with all three key combinations and see the results.
Also try copying a formula and see for yourself.

Ctrl+" takes out all text and alignment formatting in the copied cell. Borders and fill patterns are not copied either.

If the original value is a number with the accounting format, the resulting cell has the currency format.
If the original cell has an accounting format with no money symbol, just the number, the copied cell transforms it to comma style, leaving out the space after the last digit.
Date, time and scientific formats are copied fine, but if the cell has a fraction format, the resulting copy is a text, not a value.

Ctrl+D leaves nothing out. You get an exact copy of the original cell, content AND formatting.
Ctrl+' copies content leaving out ALL formatting (similar to Paste Formulas command).

I have found some other differences too besides the formatting.
If the original value is a formula, for instance, A3 is =SUM(A1:A2).
Ctrl+D in A4 results in =SUM(A2:A3), which is what you would expect in a regular copy and paste action.
Ctrl+" in A4 is similar to Paste Value. Does not copy the formula, just the resulting number.
Ctrl+' in A4 copies the original formula =SUM(A1:A2), without format.

Hope this helps.


2017-03-17 06:05:45

Michael (Micky) Avidan

@Ricardo Ribas,
Could you, please, expand and explain the meaning of: "in some instances" ?
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2017)
ISRAEL


2017-03-16 10:29:53

Ricardo Ribas

Ctrl+D copies the content and the exact formatting of the cell above. Ctrl+" changes the copied format in some instances.
Try it with a number in a cell with bold text and accounting number format. The resulting text is plain and the format is changed to currency.


2017-03-16 08:41:46

RKeev

The biggest difference is that if you use Ctrl-D you can't "add" to the cell and using Ctrl-Shift-" often enters incorrectly if you enter other digits. Meaning (using Allen's example) if you place .1234 in the cell (excel changes the value to 0.1234) then click the cell beneath and enter a 2 and Ctrl-D nothing happens, the 2 remains only.

However if you enter a 2 and use Ctrl-Shift-" remembering excel automatically places the zero to the left, the value entered will be 20.1234 not 2.1234 as desired.


2017-03-16 08:04:26

Lisette

What about CTRL + d? That's what I use to copy information "down." Seems a lot easier that using " or '.


2017-03-16 06:40:00

Bob Thorp

Cntrl+D will also copy down from the cell above including the fomatting. I find this eaiser to do than Cntrl+" which as stated requires the Contorl and Shift and ".


2017-03-16 05:15:52

Terence

There is also ctrl + d, this seems to function the same as ctrl + " but doesn't put you inside the cell afterwards (as if you have pressed enter)


2017-03-16 04:54:25

Surendera M. Bhanot

I normally use Ctrl+' to copy the cell contents from the cell immediately above. This tip explained the use of Ctrl+" and the what is the difference. Great!!
The tip of Sarfraz Ali are the Gem on the Crown.

However, one will find that after copying the data as such, the cursor in the cell remains in the <Edit> mode at the end of the data. This enable you to modify cell contents easily. Suppose you are listing part number in a Inventory list, one can delete the last few character(s) and enter and enter new ones. without touching touching the mouse!!


2017-03-16 04:33:16

Sarma

Real wizardry


2017-03-16 04:25:07

Sarfraz Ali

Ctrl+D and Ctrl+R different?
Copy down and copy on the right adjacent cell.


2015-01-21 12:14:49

Amjadali

You can also use Ctrl+d to copy data from the cell above the current cell


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.