When you have some range of cells with gaps in the data, you cannot use the keyboard shortcut Ctrl+Shift+Down Arrow or Ctrl+Shift+Up Arrow, you cannot select them all at a go, because it will stop you before the next blank cell. I am revealing some of the MOST IGNORED shortcuts which are very powerful and useful here. Go.Īlso check out: Comprehensive list of Excel keyboard shortcuts. So go ahead share your favorite ones in comments. I am sure there are tons more shortcuts that I have omitted. What are your favorite shortcuts for writing / editing formulas? Fill down / side ways: Once you have a formula in a cell, you can drag it down or sideways (using mouse) to fill the formula down or across.(more: Using mouse to save time in Excel) You can move these rectangles or resize them to edit the formula input ranges. Use mouse to edit formula ranges: When you select a cell with formula and edit it (by pressing F2), you can see these blue, green, red rectangles around the cells to which the formula is talking.(related: auditing formulas & spreadsheets) Press CTRL + ` (back quote, usually the key above tab on left) to on / off show formulas mode. Very useful when explaining your worksheet to others. F9: Evaluate selected portion of a formula. Select a portion of a formula and press F9 to evaluate it alone.Writing and editing formulas is such an important part of Excel that there are many other useful shortcuts and tricks. And you get a copy of the formulas alone. If you just want to make a copy of the formulas and omit cell formatting etc., copy the cells with formulas, go elsewhere, press ALT + E and then S and F. (more: Guide to Excel cell references and when to use what?) Just place cursor inside the range / cell address and press F4 to cycle thru all available reference styles. When typing formulas, often you may want to change a certain cell reference to Absolute or Mixed or Relative. F4: Switch reference styles – Absolute > Mixed > Relative > Absolute.You can save a ton of time by just arrow key + tabbing. Use arrow keys to select the function, name or structural reference you want, Press Tab key to let Excel type it for you. As you start typing a formula, Excel shows auto-complete suggestions. Tab: Auto-complete functions, names, structural references.Excel will show a list of all names and pick what you want. Whenever you want to type a name, press F3 instead. When you have a lot of names, often remembering them can be tricky. When you select a cell and press F2, Excel places cursor at the end and lets you edit the cell value / formula. So today, let us learn 5 important keyboard shortcuts that will save you a lot of time and help you write better formulas. As an analyst (or manager), I bet a good portion of your Excel time is spent writing formulas and getting the results.

Elevator button shortcuts