Software

Office challenge: What's the quickest way to copy the contents of the active cell to the other cells in the selected range?

In this week's Office challenge, learn the solution to last week's Excel challenge on Data Validation and test your skills on a new Excel challenge.

I frequently see users press [Ctrl]+' to copy the contents of the previous cell to the current cell. It's a shortcut for entering the same data from row to row, as you enter data. But what if you want to copy the contents of a cell to a larger range, simultaneously? For instance, suppose the selected range is A1:A5. Furthermore, A1 is the active cell and you want to copy its contents to cells A2:A5—how would you accomplish this?

Last week we asked…

How would you inhibit the entry of weekend dates without using VBA. In no time at all, Andrewsa posted the solution I had in mind:

  1. Select the cell or range in question.
  2. Choose Validation from the Data menu. In Excel 2007 and 2010, click the Data tab. Then, choose Data Validation from the Data Validation dropdown in the Data Tools group.
  3. On the Settings tab, choose Custom from the Allow dropdown.
  4. Then, enter the following formula: =AND(WEEKDAY($A2)<>1, WEEKDAY($A2)<>7). (You don't have to make the column reference absolute for this formula to work.)
  5. Click OK.

The AND operator combines two simple expressions, which check each entry for the weekday value. If the weekday value of the current input isn't 1 or 7 (Sunday and Saturday, respectively) Excel accepts the input value. If you try to enter a weekend date (1 or 7) Excel displays a warning and rejects the entry. Use the Input Message and Error Alert tabs to customize the message and Excel's response.

Jbenton offered a more efficient formula, =WEEKDAY(A1,2)<6. It certainly looks sound and after a bit of testing, I found it works just as well.

Thanks to everyone for another great challenge!

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

8 comments
stapleb
stapleb

I would use either Ctrl + D for fill down, or F2 and then Ctrl + Enter. F2 and Ctrl + Enter works best of the range is non-contiguous whereas Ctrl + D would be the faster one in this case. Someone mentioned double clicking the fill handle. I don't believe this will work as it usually requires filled cells to the left of the entry to know how far to fill down.

jbenton
jbenton

i used to think so too until i accidentally discovered that if you do this in an already filled column it will fill down to the next blank cell, regardless of what's on either side whichever method you choose depends on the situation and experience, sometimes a simple copy and paste is easiest (esp. when using the keyboard)

stapleb
stapleb

I just love accidental discoveries. Well done you, and that will teach me for not testing before "mouthing off". For the question originally asked, I think the double click would be fastest. That is a major comment from someone who just loves their keyboard. And now thinking about it, Ctrl + Shift + Down arrow, then Ctrl + D would also do it quickly from the keyboard. Oh the plethora of options just does my head in!!!

obedak
obedak

F2 and then Ctrl+Enter

jbenton
jbenton

Ctrl-D and -R are definite winners here other alternatives for similar situations: If the cells are already filled in a column of data or are adjacent to such a column, you can double-click the fill handle of the top one to copy its contents down (ie no need to select the range first) To copy a cell's contents to other (non-contiguous) selected cells, press F2 to edit the active cell then Ctrl-enter to apply this to all selected cells cat-skinning can be achieved by a variety of methods

len222
len222

In your example, with A1 being the active cell and A1:A5 selected, Ctrl-D copies from the top cell to all other selected cells. I use this shortcut all the time. Ctrl-R does the same thing while copying to the right.