Quickly Entering Data

Another thing: it is completely dependent upon the cell directly above it.

So, if you highlight a noncontiguous range and return the selector to the first of them (others in selected range shaded, this one white) for which the cell directly above is filled with something, then do Ctrl-' repeatedly, it works until one jumps from that portion of the range to the first "next" noncontiguous part of it whereupon repeating again will not repeat the original material but rather look to the cell above this one (to a cell not in the range at all) for what to repeat. So, A1 has "XXX" and you select "A2:A4, A7:A12, A15:A16" doing it in A2:A4 repeats what is in A1 but upon reaching A7, you begin repeating what is in A6, and so on.

However, in another odd thing, one of those things that makes making sense of what Excel does near the "edges" interesting, if one has a selected range, contiguous or not, with the first cell selected having a cell directly above that has content, and one presses Ctrl-', then Enter, all is normal, but the "Ctrl-Enter" trick for a range works here: press Ctrl-' first to begin placing something in the selected cell, then not Enter but rather Ctrl-Enter, and the entire range fills with whatever Ctrl-' had placed in the selected cell. If one had already filled some cells with something else, it replaces that. So all cells get the same material. As happens with Ctrl-Enter when used after more traditional entries. So clearly, whether Excel regards the Ctrl-' and Ctrl-Shift-" operations in any special way, that regard stops at the point at which the material pops up in a cell after which anything further done is accomplished in its own normal manner. Another, though less interesting, example is simply Ctrl-', then typing something extra, then Enter.

Last thing I looked at is that it is not a very simple direct copy and paste by Excel to place the material there. Copying a cell (or range), then selecting the next lower cell and pressing Ctrl-' gets the normal result, not a pasting of the copied material. So as a minimum, it does its own copy of "cell above me" and pastes that (causing the copy you did to go away as that is done).

Hmm... Also, sadly, it does not roll the top to the bottom... so do it in row 1 and it does not copy what's in that column, just row 1,048,576... (Sigh...)

Another oddity...

If you press either one multiple times before leaving the cell, you get multiple copies. Maybe something of use there.

Reminds me though, I meant to point out in the last post regarding the comment that one still has to press Enter... well, who cares? Not press Enter literally, just leave the cell. But you have to leave the cell in any case to get more work done. Press any of the combos, Ctrl-D, Ctrl-', or Ctrl-Shift-', and you can leave the cell in whatever manner you find convenient, arrow, mouse, Enter and arrow or mouse... whatever. So identical in this respect.

Eh... yeah, Ctrl-D's handy for copying a cell downwards, but then so's Ctrl-C, Ctrl-V, pretty much. So not of much marginal benefit. And Ctrl-C, Ctrl-V works anywhere, eh? Not just immediately downward. Not blowing my skirt up.

Neither, really, do Ctrl-' and Ctrl-Shift-'. Working on only the immediately above cell... vs. anywhere... not too competitive.

But I DO find the Precision As Displayed result from Ctrl-Shift-' interesting. Though... in a year now, I've not used it once. Still remembering it though, in case.

But some people love these little not-useful-but-only-I-know-it-in-this-office kinds of things. Yay, for them.

Ctrl+D is even faster. It copies data and formulas + format of the cell or adjacent range above without the need of pressing Enter. It's only disadvantage is that when your table is filtered it copies the cell(s) above even if that row is hidden.

Ctrl+" and Ctrl+' copies the visible cell above skipping the possible hidden cells in between but they work only on a single cell. You have to press enter.

Carrying the formatting forward is something done regardless. Enter 6.01 in a cell and make it 0.0000 so it displays as 6.010 and change the font and color perhaps. Use either Ctrl-' or Ctrl-Shift-" (I won't be using Ctrl-" because lots of people don't get that it's the same and Ctrl-Shift-" removes that ambiguity understanding-block... better they never realize the Shift-', Shift-", and in the usage in Allen's Tip, ', are all the same than that they never really get that the Shift key has to be included to get -") and you will get 6.01 in your original font and color. Copy or otherwise achieve having THREE identically formatted cells in a row ("in a column") and press EITHER Ctrl-' or Ctrl-Shift-" and you get the complete formatting brought along with it.

Or... just type 6.01 in the fourth cell and you get all the formatting with it. Three cells is the tipping point for Excel. Two and you don't see the formatting copied from above. Three and you do. Not because of HOW you made the entry, but because Excel is trying to help.

However, do the percent formatting thing and it SEEMS you do get the formatting carried along, even without the three cells being needed. But this is not so. Change font and color. Ctrl-Shift-" still brings the number formatting, seemingly, but notice it brings nothing else. Ctrl-' seems to bring no formatting at all. But give it three cells and do either in the fourth cell and you get it all.

This is a subtle place, open to misinterpretation. But it seems clear to me that what happens with Ctrl-' is that it brings EXACTLY the string that is in the cell above and then that is formatted EXACTLY how Excel would format and present it if you had typed that literal string. So Excel finds literally .1234 to be in the cell and enters that in the new cell then formats it using the new cell's formatting because it is not activating the help that comes along when there are three commonly formatted cells right above it. So it displays without any of that formatting coming along. If there ARE three commonly formatted cells above it, it also applies that help at the end and the format seems to come along with it, even though we are told it won't since it was Ctrl-'. Bear in mind the literal copy of a formula, as if you'd pressed F2 on the formula cell, then Ctrl-Shift-Home, then Ctrl-V, then Escape, then down arrow, then F2, then paste, then Enter, occurs. Excel does not process it as a copying of the cell with the attendant adjustments of formula references. It just takes the literal string as if typed and applies formatting as it closes the cell, so to speak, for display.

The difference seems to be that Ctrl-Shift-" takes the representation of the cell contents equivalent, it looks like, to an immediate copy, thenpaste values, and the result is what is pasted. Except... it looks even more like the exact process is taking what is DISPLAYED for the value used, and pastes precisely THAT string. Then Excel acts as if that string had been typed and formats for display appropriately. Since in the percent example, the displayed value is the string 12.34%, not a value of .1234, it is 12.34% that is entered, then acted upon formatting-wise, and so Excel SEEMS to be taking the number formatting along, but is not. It's not precisely coincidence that it takes on the same number formatting and so the formatting SEEMS to be copied, but it is a VERY close cousin to coincidence.

Note I have not tested two things. I have used "commonly formatted" above instead of "identically formatted" because I am just not going to bother trying to suss out whether it is the latter or just the former, whether there are formatting options that do not matter and are or are not brought forward, but I wager, since a single entry with different color and font/font size does not get carried along, unless this is cell 4+, then things like "Center Across Selection" aren't either. And of course, given how I think things are occurring here, none of them come along since they are not part of what Excel automatically does after you type some entry in a cell and press Enter. Except when it's cell 4+ in which case it is that, and not the shortcut providing the carry forward.

The other is that I wager Ctrl-Shift-" is sort of a Precision-As-Displayed sort of feature. THAT I am about to test. The result is: YES. Formatting a cell as 0.00 and entering 6.015983 then using Ctrl-' gives me 6.015983. But doing so and pressing Ctrl-Shift-" gives me 6.01. So instead of a copy, paste|values operation happening, it is using its Precision-As-Displayed functionality (which, for all I know IS to copy, then paste, values... but I suspect not) to take the DISPLAYED value, not the underlying value, and to enter this into the cell, then format it "in the usual way."

So, all that said... interesting. I have a new, nice way to copy a formula without adjustments, at least to cells one down or over (oh... NOT over... I always used up/down, and trying left/right shows it won't work left/right) although it isn't often I need that exact thing. Still. Might be some other uses here, who knows? Early days.

And Ctrl-D in the circumstances just above gives me the full string, with the original's formatting, so it's like copying the cell. It does, of course, change formula references, so it differs from both. It would seem to use a different underlying logical approach, basically a copying of the cell and then pasting it in the marked region. Not sure I have any reason to not just simply copy, mark, and paste rather than mark and Ctrl-D. Either seems as easy and I know the limits of the former's behavior while Ctrl-D might have some secrets I have yet to learn and so might screw me. But I shall experiment. Just because I never used it much in the past doesn't mean I can't come to value it. Maybe.

Interesting though, the Precision-As-Displayed thing. I wonder what other shortcuts might do that exact thing. I never knew about Ctrl-'/Ctrl-Shift-" before so I can't place them in my mind with any other shortcuts that came to exist when they did so I don't even know a subset to test. And without a subset, there are prohibitively many to test... (Most shortcuts came as older sets rolled into Excel early on, or absorbed later when the programs that popularized the various sets got superceded (monopolized into the ground of bankruptcy or being sold to their killers) or as MS bowed to demand for things like Shift-Insert rather than what would seem a more natural Ctrl-C, but I can't place these. And honestly, can't remember now if Shift-Insert was always there, just not pushed by MS due to the naturalness of Ctrl-C, but perhaps became the help site "new news" that all of them wrote about for a week, or month, or year, as if it were a new gem, then no one cared anymore, or any more for that matter, about it.) But I do wonder now about other things that slip PAD into their action.

(To me, that's the most interesting thing about this.)

I won't try it, because I don't want this bunch of typing and effort to maybe get lost, but I wonder, on the days when the question below is "What is four minus 1" would "three"

I've been using CTRL+D for some time now, as I often find myself entering info which is identical to the cell(s) above - several entries on the same date, for example. I have found that I could select more than one cell in the same row (adjacent in my experience) and then move to the appropriate cell below and CTRL+D will copy each of the selected cells.

The other combinations are new to me, so this had been a useful tip, and as usual the comments are often very helpful.

CTRL+D does the same thing. I think of it as "ditto."

A fundamental difference between Ctrl+D below and the Ctrl+' and Ctrl+" above is that Ctrl+D does not work when in edit mode. So, the example above where you type 12345, move down and type ABC first, and while in Edit mode hit the Ctrl combo, both ' and " get you ABC12345 while D gets you nothing. Two situations I got a different result from all three. One was when I typed 5%, and moved down and typed ABC before the Ctrl combo. D got me nothing but ABC since it didn't work, " got me ABC5%, and ' got me ABC0.05. The other was related to formulas as was mentioned below, but more specifically, Ctrl+D copies the formula and updates cells relativity as mentioned, Ctrl+" literally copies the result of the formula as if you did a Paste Special and chose Value (not mentioned), while Ctrl+' copies the formula verbatim as mentioned. As usual every key combo has it's uses for someone and knowing them all makes you the most powerful user. That's another reason why I love this site. The topic taught me a little (I already knew Ctrl+' to some degree), but the comments taught me more and made me experiment. Thanks everyone!

Note that this shortcut only works in the cell directly BELOW the cell you are "dittoing" from.

Be aware!
Ctrl+' and Ctrl+" is NOT copy. It is DUPLICATE.
It works fine for text, numbers, date/times.
BUT, if you Ctrl+' on a formula, it duplicates the formula, not changing the relative references.

Just for giggles and grins, doesn't Ctrl+D do the exact same thing as CTRL+"? In fact, Ctrl+D is even more versatile, as it is the standard "Fill Down" command. It works in two different ways. If you want to fill a single cell, simply place the cursor in that cell and press Ctrl+D. The formatted contents of the cell directly above is entered in the current cell. For example, if you have the value 12.34% in cell B3 and you place the cursor in B4 and press Ctrl+D there, cell B4 will get populated as 12.34%. In another set of circumstances, you may want to fill a given value down through a contiguous range. For example, having the same value 12.34% in cell B3, if you select cell B3 and drag down in column B through to B10, the range of cells from B4 through B10 will all get populated with that formatted value, 12.34%.

What advantage is there in using Ctrl+" over Ctrl+D?

Ctrl+D is even faster. It copies data + format of the cell above without the need of pressing Enter.