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

Friendly and Informative Error Handling

When creating macros, it is helpful to know what is going on within the macro itself in case an error crops up. Here's one ...

Discover More

Modifying the Color Scheme

Windows maintains what it calls a color scheme to control how the various elements of Windows appear. Here's how to change ...

Discover More

Turning Off "Link to Previous" by Default

When you add a new section to a document, you may want the headers or footers in that section to be different from those in ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Pasting without Updating References

Do you need to paste formulas without updating the references in whatever you are pasting? You can accomplish this, depending ...

Discover More

Selecting Formulas

Want to select only the formulas in your worksheet? It's easy to do using the Go To Special dialog box.

Discover More

Deleting All Names but a Few

Want to get rid of most of the names defined in your workbook? You can either delete them one by one or use the handy macro ...

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 6Mpixels. 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 7 - 1?

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.