5 ms·

I'd like to learn how to be a spreadsheet wizard who doesn't need to use the mouse. What are the major shortcut keys that the pros are using?

by j_m_b 10y ago

ZenoArrow 10y ago
I wouldn't call myself a pro, but I've had plenty of experience in Excel. Some of my most-used shortcuts aren't obvious from the current Excel interface as they're ones that follow the menu structure of Excel 2003. However, Excel 2003 shortcuts still work in the latest versions, so they're still valid, they just take a little more practice to become second nature. Here's a bunch to start with (note that commas in examples below mean you release the previous key before pressing the next one)... Alt,D,F,F - Set filters Alt,D,F,S - Clear filters Alt,W,F,[Enter] - Freeze panes Ctrl+Shift+DownArrow - Select all cells down from current cell Alt,O,C,A - Autofit to content of currently selected cells. To give an example of most of the above (plus a couple more)... Ctrl+Home Ctrl+Shift+Right Ctrl+B Alt,D,F,F Alt,O,C,A DownArrow Alt,W,F,[Enter] Assuming your data has column headings at the beginning of a sheet, what the above combination does is formats the column headers in bold text, sets column filters, autofits the width of the columns so that the full name of the columns are all displayed, and freezes the top row so that the column headers will still be shown when scrolling down the worksheet. One more shortcut tip I'll pass on is for Paste Special. If you want to get the most out of Excel you should learn about Paste Special, it lets you do things like remove formulas, copy formats, transpose columns into rows (and vice versa). To use it from the keyboard, first highlight and copy the cells you're interested in, then use the 'menu key' on your keyboard (Google it if you're not sure where this key is), then look for the underlined letters in each of the menu options to choose the ones you want. Also, these functions aren't necessarily keyboard friendly but I'd recommend taking a look at 'Text to Columns', 'Remove Duplicates', 'Evaluate Formula' and Pivot Tables, all of which I've found very useful.
- cm2187 10y ago
  CTRL+SHIFT+L is faster to set filters. A few other useful ones: F2 to edit a formula CTRL ENTER to apply the current formula to the selected range ALT = to insert a SUM( ) where the range is automatically selected CTRL : to insert the current date SHIFTLOCK F9 to evaluate a fragment of a formula while editing it CTRL ARROW to navigate through a block of numbers (and SHIFT to select them) But you will only be a pro if you start using array formulas. F2 a cell (or selected range) to start editing the formula, then CTRL SHIFT ENTER to apply it as an array formula. Array formulas are useful for two things: 1) make several cells behave as a single vector / matrix. So you can do matrix algebra. From simple things like {TRANSPOSE()} to actual maths. 2) do super flexible aggregations. Like enter in a single cell: ={SUM(A1:A10*A1:A10*(B1:B10>C1:C10)*(D1:D10="A")*(MONTH(E1:E10)=ABS(F1:F10)))} Which reads "do the sum of the square of elements in col A WHERE Col B greater than Col C AND Col D is "A" AND the month of Col E is the absolute value of Col F. You can also earn money at the office, like when I bet with a colleague I could tell him how many times a column changed value in a single formula: ={SUM(1*(A1:A10<>A2:A11))-1} Array formulas are slow but very powerful.
  - ZenoArrow 10y ago
    Thanks for the tips, didn't know about Ctrl+Shift+L. Regarding array formulas, I wouldn't recommend them. Especially as you can use SUMPRODUCT for the same purposes as array formulas... http://ww2.cfo.com/accounting-tax/2010/12/spreadsheets-hate-arrays-then-hijack-sumproduct/ http://ww2.cfo.com/accounting-tax/2010/12/spreadsheets-hate-...
j_m_b 10y ago
I am an emacs user and before that a vim user. Whenever I use a spreadsheet, it always feels real clunky to navigate. Thank you both for the shortcuts!

I'd like to learn how to be a spreadsheet wizard who doesn't need to use the mouse. What are the major shortcut keys that the pros are using?

by j_m_b 10y ago

I'd like to learn how to be a spreadsheet wizard who doesn't need to use the mouse. What are the major shortcut keys that the pros are using?

ZenoArrow 10y ago

I wouldn't call myself a pro, but I've had plenty of experience in Excel. Some of my most-used shortcuts aren't obvious from the current Excel interface as they're ones that follow the menu structure of Excel 2003. However, Excel 2003 shortcuts still work in the latest versions, so they're still valid, they just take a little more practice to become second nature. Here's a bunch to start with (note that commas in examples below mean you release the previous key before pressing the next one)... Alt,D,F,F - Set filters Alt,D,F,S - Clear filters Alt,W,F,[Enter] - Freeze panes Ctrl+Shift+DownArrow - Select all cells down from current cell Alt,O,C,A - Autofit to content of currently selected cells. To give an example of most of the above (plus a couple more)... Ctrl+Home Ctrl+Shift+Right Ctrl+B Alt,D,F,F Alt,O,C,A DownArrow Alt,W,F,[Enter] Assuming your data has column headings at the beginning of a sheet, what the above combination does is formats the column headers in bold text, sets column filters, autofits the width of the columns so that the full name of the columns are all displayed, and freezes the top row so that the column headers will still be shown when scrolling down the worksheet. One more shortcut tip I'll pass on is for Paste Special. If you want to get the most out of Excel you should learn about Paste Special, it lets you do things like remove formulas, copy formats, transpose columns into rows (and vice versa). To use it from the keyboard, first highlight and copy the cells you're interested in, then use the 'menu key' on your keyboard (Google it if you're not sure where this key is), then look for the underlined letters in each of the menu options to choose the ones you want. Also, these functions aren't necessarily keyboard friendly but I'd recommend taking a look at 'Text to Columns', 'Remove Duplicates', 'Evaluate Formula' and Pivot Tables, all of which I've found very useful.

cm2187 10y ago
CTRL+SHIFT+L is faster to set filters. A few other useful ones: F2 to edit a formula CTRL ENTER to apply the current formula to the selected range ALT = to insert a SUM( ) where the range is automatically selected CTRL : to insert the current date SHIFTLOCK F9 to evaluate a fragment of a formula while editing it CTRL ARROW to navigate through a block of numbers (and SHIFT to select them) But you will only be a pro if you start using array formulas. F2 a cell (or selected range) to start editing the formula, then CTRL SHIFT ENTER to apply it as an array formula. Array formulas are useful for two things: 1) make several cells behave as a single vector / matrix. So you can do matrix algebra. From simple things like {TRANSPOSE()} to actual maths. 2) do super flexible aggregations. Like enter in a single cell: ={SUM(A1:A10*A1:A10*(B1:B10>C1:C10)*(D1:D10="A")*(MONTH(E1:E10)=ABS(F1:F10)))} Which reads "do the sum of the square of elements in col A WHERE Col B greater than Col C AND Col D is "A" AND the month of Col E is the absolute value of Col F. You can also earn money at the office, like when I bet with a colleague I could tell him how many times a column changed value in a single formula: ={SUM(1*(A1:A10<>A2:A11))-1} Array formulas are slow but very powerful.
- ZenoArrow 10y ago
  Thanks for the tips, didn't know about Ctrl+Shift+L. Regarding array formulas, I wouldn't recommend them. Especially as you can use SUMPRODUCT for the same purposes as array formulas... http://ww2.cfo.com/accounting-tax/2010/12/spreadsheets-hate-arrays-then-hijack-sumproduct/ http://ww2.cfo.com/accounting-tax/2010/12/spreadsheets-hate-...

j_m_b 10y ago

I am an emacs user and before that a vim user. Whenever I use a spreadsheet, it always feels real clunky to navigate. Thank you both for the shortcuts!