5 Keyboard shortcuts for writing better formulas

Posted on June 27th, 2013 in Learn Excel - 52 comments

As an analyst (or manager), I bet a good portion of your Excel time is spent writing formulas and getting the results.

5 Important Keyboard shortcuts to write better formulas in Excel
So today, let us learn 5 important keyboard shortcuts that will save you a lot of time and help you write better formulas.

  1. F2: Edit a formula cell. When you select a cell and press F2, Excel places cursor at the end and lets you edit the cell value / formula.
  2. F3: Paste names in to formula. When you have a lot of names, often remembering them can be tricky. Whenever you want to type a name, press F3 instead. Excel will show a list of all names and pick what you want.
  3. Tab: Auto-complete functions, names, structural references. As you start typing a formula, Excel shows auto-complete suggestions. Use arrow keys to select the function, name or structural reference you want, Press Tab key to let Excel type it for you. You can save a ton of time by just arrow key + tabbing.
  4. F4: Switch reference styles – Absolute > Mixed > Relative > Absolute. When typing formulas, often you may want to change a certain cell reference to Absolute or Mixed or Relative. You can use F4 key to do the switching. Just place cursor inside the range / cell address and press F4 to cycle thru all available reference styles. (more: Guide to Excel cell references and when to use what?)
  5. ALT + ESF: Paste Formulas only. 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.  And you get a copy of the formulas alone.

Bonus Shortcuts & Tricks:

Writing and editing formulas is such an important part of Excel that there are many other useful shortcuts and tricks. Here are a few of my favorites:

  • F9: Evaluate selected portion of a formula. Select a portion of a formula and press F9 to evaluate it alone. See the results and debug easily. (related: debug formulas using F9)
  • CTRL + ` : Show formulas. Very useful when explaining your worksheet to others. Press CTRL + ` (back quote, usually the key above tab on left) to on / off show formulas mode. (related: auditing formulas & spreadsheets)
  • 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. You can move these rectangles or resize them to edit the formula input ranges. Very useful and very time saving. (more: Using mouse to save time in Excel)
  • 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.

What are your favorite shortcuts for writing / editing formulas?

I am sure there are tons more shortcuts that I have omitted. So go ahead share your favorite ones in comments. Teach us something new. Go.

Also check out: Comprehensive list of Excel keyboard shortcuts.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

52 Responses to “5 Keyboard shortcuts for writing better formulas”

  1. Alexandre says:

    Someone knows this command on a Mac?

    • Suzanne Zaleski says:

      There is a way to customize your keyboard in Excel: Tools > Customize Keyboard. Save the desired action/keyboard combination there and you won’t have to look for a shortcut again.

      If what you’re hoping to do isn’t possible in Customize Keyboard, there is still salvation. Before working in your spreadsheet, go to System Preferences > Keyboard. Check “Use all F1, F2, etc. keys as standard function keys”. This will take away from your Mac controls to increase brightness or sound, for example; however, it will let you use the F2 keys as indicated in Excel.

      When done, just go back to your System Preferences and uncheck the box to restore defaults to the Function keys.

  2. Vitalie says:

    “ALT + ESF: Paste Formulas only” – I use Alt+HVF in Excel 2007 (I imagine all other versions with the Ribbon rely on the same sequence)

    • Luke M says:

      I believe both will actually work…Microsoft set things up so you can still use the old keyboard shortcuts.

  3. David says:

    For Paste special commands I set-up my Alt shortcuts so:

    alt + 1 is Paste Values
    alt + 2 is Paste formats
    alt + 3 is Paste formulas

    You don’t even have to look at that horrible pop-up box.

    • Jon Acampora says:

      I use a similar setup but have Alt+2 as the format painter. Paste Formats is probably better because the format painter requires you to use the mouse unless you are painting an adjacent cell.

      For those that don’t know, these are references to the Quick Access Toolbar (QAT). You can add any menu command to the QAT by right clicking it > Add to Quick Access Toolbar.

      Each button in the QAT is assigned a number for a keyboard shortcut. You can see the numbers by pressing the Alt key. So Alt+1 is the keyboard shortcut for the command in the furthest left position of the QAT

    • Mahesh Kulkarni says:

      alt + 1 is Paste Values
      alt + 2 is Paste formats
      alt + 3 is Paste formulas

      David can u please tell me how to do it ?

      • venu says:

        Mahesh, just add the paste values, paste formats and paste formulas in your QAT. Place them in position 1, 2, and 3 from left to right on the QAT

        Have a nice day

  4. Nick M says:

    Alt+H+V+V is paste as values. Use that one more than I realize.

  5. sameer says:

    F3 doesn’t work at all for me… I’m on Excel 2010. Any tips? Ctrl-F3 works, but I wish there was a shortcut STRAIGHT to the name box.

  6. Kevin says:

    How about F2 on the formula and ctrl c.

    DOS commands are the best….

  7. Christine says:

    Please clarify if these shortcuts are for PC – are there equivalents for Mac? thanks, all!

  8. Luke M says:

    Alt + PgUp/Down = Scroll left and right within a worksheet
    Ctrl + PgUp/Down = Change sheet tabs (handy when sheet tabs aren’t visible)
    Alt + F11 = Toggle to/from the VB editor
    Ctrl+Enter = Confirm entry to all selected cells
    Ctrl + ; = Select all visible cells

    My work would take several hours longer w/o these.

  9. shift + F3 to launch formula directory.

  10. Jon Acampora says:

    Alt+J+T+A will highlight the Table Name box when you have a cell selected inside a structured reference table. This is handy when you are renaming tables from their default name since you are NOT prompted to name the table when you press Ctrl+T to create it. Even if you are not renaming the table, the Alt+J+T+A shortcut will allow you to see the name of the table you are currently in. The table name is only displayed on the Design tab of the ribbon when a table is selected.

  11. Grant says:

    A cautionary note – I just bought a new HP laptop with a UEFI “BIOS”. The laptop was pre-installed with the function keys disabled and the HP special functions enabled. It is incredibly annoying to press F2 and watch the screen dim! The function keys are accessed by pressing fn + F-key – try using keyboard shortcuts now.

    Supposedly you can reverse this by altering the UEFI “BIOS” but I haven’t yet worked out how.

    • Hui... says:

      @Grant
      To access the BIOS setup
      Press the Power Button
      Press and hold the Esc key until a menu pops up.
      You should get a menu like:
      (F1) System Information
      (F2) System Diagnostics
      (F7) HP SpareKey
      (F9) Boot Device Options
      (F10) BIOS Setup
      (F11) System Recovery for Consumer Notebooks
      (F11) System Recovery for Business Notebooks
      (F12) Network Boot

      It will be laid out differently to that

      Otherwise you can just press the appropriate Function button just after the machine is turned on instead of the Esc key

  12. jeffreyweir says:

    You’re nicely in tune with Daily Dose of Excel, Chandoo. See http://dailydoseofexcel.com/archives/2013/06/25/how-to-be-great-at-excel/ from yesterday.

    • Chandoo says:

      Thanks Jeff for the link. I was reading the page last night. I might do a follow up of that article next week exploring few other areas of Excel greatness.

  13. Hubert says:

    alt + NV: to create a pivot table
    ctrl -/+: to delete/insert a column or row
    ctrl =: is equal to F9 on PC and is equal to command = on Mac

  14. I’m a huge fan of CTRL+A to select everything

  15. zurman says:

    I THINK LUKE TIPS ARE BEST

  16. stuart says:

    simplest used constantly is the enter key to paste! (some limitations, eg not when editing within a cell)

  17. Chau says:

    Alt+ESV (paste as values)
    ALT+ENTER in the formula box when you want a line-break
    ALT-Tab when your managers walks by :)

    How about writing your formula starting with a “+” instead of the “=”, you just won yourself .015 miliseconds in writing a new formula.

  18. Instead of Alt + ESF, use Ctrl-V to paste (as you would naturally) and then press Ctrl to open the little pop-up menu and then “F” for “formula” (or other letters for the other options – they are displayed when hovering over the icons).

  19. Travis says:

    Ctrl + D (copy down) and Ctrl + R (copy right) save more time and impresses people very time I do it!

  20. Shikher says:

    Dear Chandoo, Hui and other Chandoo.org excel PowerUsers,

    Could you please share with me a keyboard shortcut that allows me to trace all precedents in a cell formula that is calculated based on input cells in multiple other sheets? I am aware of “Ctrl + [” but this shortcut directs me (only) to the first precedent in the cell formula.

    To make my case clearer, please consider the following example -

    I have 3 worksheets with the following inputs and formula:
    Sheet 1: A1 = 2
    Sheet 2: A1 = 3
    Sheet 3: A1 = Sheet1!A1 + Sheet2!A1

    While in Sheet 3 Cell A1, I would like to invoke a shortcut that will first take me to Sheet 1 Cell A1 and then (when the shortcut is invoked again) to Sheet 2 Cell A1.

    Thanks!

    • Jon Acampora says:

      Hi Shikher,

      Here are some shortcuts for formula auditing.

      Alt+T+U+T – Trace Precendents (draws arrows)
      Alt+T+U+D – Trace Dependents (draws arrows)
      Alt+T+U+A – Clear Arrows
      Alt+T+U+F – Launch Evaluate Formula window

      If your precendents are on another sheet, you will see a black dashed arrow with a small spreadsheet icon at the end of it when you use the shortcuts above. If you double click on the line of the arrow, the Go To box will appear and you can view and navigate to the precendents or dependents. It is important to note that you have to double click on the black dashed line leading to the box, and not the box itself. The mouse cursor will change to a pointed arrow when you are hovering over the line, this is when you double click.

      These shortcuts come from Excel 2003, but they still work in 2007 and later versions.

      Hope this helps,

      Jon

      • Shikher says:

        Thanks Jon.

        Personally speaking, I find the use of a mouse debilitating for spreadsheet maneuvering. I was hoping to learn if there was a more advanced form of “Ctrl + [” that allows me to toggle between all the arguments in a cell formula.

        - Shikher

  21. John says:

    Some real useful shortcuts. But i prefer ctrl+v all the time.

  22. Luis Fernando Ortiz Maldonado says:

    I found this (I Think)

    In spanish keyborads and Excel 2007 in Spanish versión when I typing the formula “= suma (” and press Ctrl + E, Excel displays the Function Arguments window.

    Thanks!

  23. Artem says:

    Hi,

    Another tip for auditing formulas (on top of F9 for evaluating part of it):
    1) If formula is simple, you can hit Ctrl + [ to go to the cell the formula refers to (first refence is formula refers to several cells)
    2) If formula is complicated, you can select a part of it (just like for F9) and hit F5 (or Ctrl+G) and just hit ENTER, this will jump to the worksheet and area where the referred cell(s) is while still being in formula editing mode.

    I found the second approach very valuable for auditing workfiles.

  24. Berry says:

    Ctrl+1 Format menu, I use this more often than any other shortcut since I am usually making presentations or Dashboards

    To handle paste-specials such as Values or Formulas, I take advantage of the Quick Access Toolbar (putting it underneath formula bar) by removing the defaults and putting some custom buttons there, of which I use Paste Values and Paste Formulas very often with just a single click

  25. Matt says:

    Ctrl – ; inserts today’s date
    Ctrl – ‘ copies formula from cell above

  26. Ellen says:

    It would be really helpful to identify where your tips differ between the PC and Mac.

    Keyboard short-cuts is simply one example. I used to use all the keyboard short-cuts when I used Excel for PCs, but making the switch to a Mac has made things a little more challenging in Excel.

  27. Jason says:

    Please write this article for MAC Users :)

  28. James Rx says:

    Shift + F11 opens a new tab in the current work book

    CTRL+N Opens a new work book

    Holding CTRL and then pressing the arrow keys moves the selection to the next break in the data.
    Hold down CTRL+SHIFT and use the arrow keys and you can very quickly highlight large areas of data for editing.
    Once you have selected the range: CTRL+D will ‘Drop’ the contents or formula from the first row of cells into all the cells below. Very handy when adding formulars to large spreadsheets. Much quicker than dragging over 1000s of rows.

    CTRL+SHIFT+(number keys acros keyboard 1,2,3,4,5) Auto formats cells to Number, time, date, currency,percentage.

  29. Ade VP says:

    Have a look at http://www.shortcutworld.com/en/win/Excel_2010.html.

    Also in shortcutworld are shortcuts for other versions, and other programs

  30. Vad says:

    Am I the only one uses, Ctrl + V, Ctrl, V to past values only?
    Other shortcuts, I use
    Alt + F8 – macros,
    Ctrl + G in combination with Ctrl + [,
    F7 for spell check,
    Ctrl + Space and Shift + Space for columns and row selection,
    Alt H W for wrap text,
    Ctrl Shift L for applying filters

    • Jason Jay says:

      To make an existing formula a value this is very helpful. Select your range. I like the keyboard shortcut Control Q for the code because I use it so much

      Sub ConvertToValue()

      Selection.Copy
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
      Application.CutCopyMode = False
      End Sub

  31. Renee says:

    I LOVE the Alt + D + P shortcut to open the pivot table wizard and access the multiple consolidation ranges. This one saved me TONS of time when convering old data into a tabular format.

  32. Ravi says:

    Hello All,

    I am revealing some of the MOST IGNORED shortcuts which are very powerful and useful here. In fact, Very few people use them.

    1. Shift+Enter:
    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. If the data in question is in D2:D12000 with some gaps in between and we want to select whole range, here is the method: 1)Keep the cursor at D2, 2) Go to the Namebox, type D12000 (you should know about the data range prior), press Shift+Enter. That’s it, it goes down as an express elevator with out even stopping at any blank cells. It’s already selected. And now you can do whatever you want with it i.e. building formula or applying some formats or colors etc.

    2. Ctrl+. :
    When you deal with large range of cells, may be thousands of rows deep down, especially when you build formulas, you want to check whether you are building the formula in the right column after selecting thousands of rows down, because you cannot see the column header. Then the only option is to disturb the entire selection and going to the top cell of that column to see the column header to make sure where to build the formula. Then again you have to select the whole range. To avoid this confusion, you can use ” Ctrl+. “. Just select a whole range of cells and press this shortcut to see what happens. It will change the Active cell of the selection. This indirectly lets you know the column headers as well. In some formulas giving the correct cell reference of the Active cell is very important. Then this shortcut is very useful.
    These two are my favorite shortcuts which very few people know about and use them.

    Thank you.
    RK

  33. Chris McC says:

    A recent shortcut I’ve found really helpful is Ctrl and Tab, flicks through workbooks in the same fashion as Alt and Tab flicks through program’s. extremely handy for the amount of workbooks I work with.

    For fellow Mac users it should be Alt and Tab but if not try Ctrl and Tab ive bled my MacBooks battery dry so need to wait for power.

  34. Chris McC says:

    A recent shortcut I’ve found really helpful is Ctrl and Tab, flicks through workbooks in the same fashion as Alt and Tab flicks through program’s. extremely handy for the amount of workbooks I work with.
    For fellow Mac users it should be Alt and Tab but if not try Ctrl and Tab ive bled my MacBooks battery dry so need to wait for power.

  35. Atif says:

    If you want to increase the screen worksheet size by getting rid of ribbon space, Ctrl+1 comes handy.
    You can toggle between visible and hidden ribbon space through Ctrl+1.

  36. Raju Surampudi says:

    Ctrl+Alt+V will go Paste Special

  37. David says:

    When typing a formula, excel offers a list of functions matching the letters being typed. You can then arrow down the list to the function wanted; but is there a keyboard shortcut to pasting the function into the formula, or does one have to use the mouse?

  38. David says:

    Solved own problem: TAB enters the selected function into the formula.

Leave a Reply