5 Keyboard shortcuts for writing better formulas

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Leave a Reply