Category Archives: Excel

Excel: After pressing Enter, move selection …

1 Reply

Some people may be aware of this, but in Excel you can change the default behavior of the Enter key. By default, when you hit Enter it will move one cell down. In Excel 2007 this setting is found in Office button > Excel Options > Advanced, and it allows you to select from Up, Down, Right, and Left.

Changing this setting is not necessary, however, as you can achieve all four directions with the default setting.

  • Down, this is the default behavior
  • Up, Shift-Enter
  • Right, Tab
  • Left, Shift-Tab

There are two more Enter-based keystrokes that come in handy from time to time.

  • Ctrl-Enter saves the cell data, and keeps that as the currently selected cell (read: does not move in any direction)
  • Alt-Enter will give you a new line (LFCR) within the same cell

Excel Calendar Trick

4 Replies

This is old news by some measure, but it is a timeless piece of trickery with Excel.

Make a calendar in Excel:

=IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1))
<>MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1)-
(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+
{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),"",DATE(YEAR(NOW()),
MONTH(NOW()),1)-(WEEKDAY(DATE(YEAR(NOW()),
MONTH(NOW()),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)

To use it:

  1. Copy the formula text to the clipboard
  2. Activate an Excel sheet and select a 7-col by 6-row range
  3. Press F2
  4. Press Ctrl+V to paste the formula into the active cell
  5. Press Ctrl+Shift+Enter (to make it a multicell array formula)
  6. Format the cells using the “Date” number format.

Voila! You have a calendar for the current month.

Voila is right.

via SlackerManager

Excel Keystrokes

93 Replies

I used to work for a financial analysis company developing Excel macros/applications in VBA. There were — and still are — some 10th level Excel blackbelts that work there. I picked up quite a few keystroke tricks while working there. Even though I don’t work there anymore, I use these every time I’m in Excel.

  • ctrl-Space – select column
  • shift-Space – select row
  • ctrl-Arrow Key – depending on which arrow key you hit, it will move the cursor along a series of cells*
  • ctrl-Page Up, ctrl-Page Down – cycles through worksheets
  • ctrl-Home – selects A1 on the current worksheet

*Longer explanation: Say that A2 is selected, and column A is populated from A1 to A50. If you hit ctrl-Down Arrow cell A50 will now be selected. If you then hit ctrl-Up Arrow it will take you to A1. The same holds true for left and right. Let’s also say that cells A60-A100 are populated. If, with A1 still selected, you hit ctrl-Down Arrow once (taking you to A50) and then a second time, it jumps over the blank gap from A51-A59 and selects A60. If you hit it a third time you will be at A100. It will probably make a little more sense if you tinker around with it a little.

What are your favorite Excel keystrokes?