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!


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.

Editor's Picks