Loading...

Follow Excel Campus Blog on Feedspot

Continue with Google
Continue with Facebook
or

Valid

Bottom line: Learn time-saving tips that will help you write, modify, and audit formulas in Excel.

Skill level: Beginner

Video Tutorial
5 Tips for Working with Formulas in Excel - YouTube
Watch on YouTube and give it a thumbs up.

Formula Tips

We probably spend the majority of our Excel lives writing and modifying formulas. They are the heart of Excel, and amazingly powerful!

Formulas can also be frustrating, overwhelming, and difficult to figure out. So, in this post I share five tips & shortcuts to help you read and write formulas faster.

#1 – Enter & Edit Modes with F2

When you have a cell selected, pressing the F2 key puts the cell in Edit mode. If the cell contains a formula, you will see the formula in the cell and be able to edit it. This is the same as double-clicking the cell with the mouse.

But there's more to it…

Sometimes you might want to select a cell/range with arrow keys to change a reference. When you press the arrow keys in Edit mode, the text cursor moves within the formula.

At this point you can press F2 again to put the formula in Enter or Point mode. Now the arrow keys will select cells instead of moving the text cursor.

The mode is displayed in the bottom-left corner of the application window in the Status Bar.

You can press F2 again to toggle back to Edit mode, to move the text cursor. This toggle allows you to modify formulas by just using the keyboard.

The use of F2 will depend on the scenario. Sometimes it's faster to use the mouse. However, Enter & Edit modes make it very easy to select text or ranges with shortcut keys like Ctrl+Shift+End.

Checkout my articles on 5 Keyboard Shortcuts for Rows and Columns in Excel and 2 Keyboard Shortcuts to Select a Column with Blank Cells to learn more tips for selecting ranges.

#2 – Absolute & Relative Reference with F4

If you find yourself typing the $ symbol to create absolute/relative references, then the F4 key will quickly become your new best friend.

After creating a range reference in a formula (with either the mouse or keyboard), pressing the F4 key will add the $ symbols and make the reference absolute.

The F4 key is a toggle that will cycle through all absolute, mixed, and relative reference states.

  • 1st press – Full Absolute Reference: $A$2
  • 2nd press – Absolute Row Reference: A$2
  • 3rd press – Absolute Column Reference: $A2
  • 4th press – Full Relative Reference: A2

You can continue to press F4 to cycle through these states. The cycle will start at the existing state for the reference and change to the next when you press F4.

F4 also works on range references ($A$2:$A$10).

When you first create the reference by selecting it with the mouse or keyboard, press F4 to make apply the change to the full reference.

If you modify an existing range reference then you will need to select the entire reference before pressing F4. Otherwise it will only modify the part of the reference that the text cursor is in.

I share a quick way to select the entire range reference in the next tip

#3 – Select Argument Text with Screentip Hyperlinks

Sometimes it's difficult to find and modify arguments within complex formulas. We can use the function's screentip hyperlinks to help with this.

When you put the text cursor inside a function, a screentip appears below with the function's arguments. Click the hyperlink for any argument to select the argument's text in the formula.

If you are modifying the absolute/relative reference of the formula, then you can press F4 directly after clicking the link.

Bonus tip: If the function screentip is in your way, you can also move it by hovering the mouse over the border until the cursor turns into a cross arrow.

#4 – Evaluate Formula Components with F9

When dealing with long and complex formulas, you might want to see the results of a nested function/formula (a formula within the formula).

Select the text within the formula that you want to evaluate, then press the F9 key. Excel will calculate the selected text and return the result direct to the formula.

This can help you step through and understand how a formula calculates, but there are a few important things to know.

  1. You must select the entire expression or function. If you only select part of a function's text then Excel will show an error message.
  2. Press Escape after evaluating parts of the formula. This is important! Escape will exit out of Edit mode and not commit the changes.
    If you press Enter then the results of the evaluation will be committed to the formula, and you will lose the parts of the formula you evaluated.

The F9 shortcut works well when you are trying to figure out what a specific component of the formula is calculating. The next tip explains how to step through the entire formula.

#5 – The Evaluate Formula Window

The Evaluate Formula Window is a great tool if you've inherited a workbook that contains complex formulas. It allows you to step through each component of the formula and see how it calculates.

Here's how to use it.

  1. Select the cell that contains the formula you want to evaluate.
  2. Go to the Formulas tab in the Ribbon and press the Evaluate Formula button. (keyboard shortcut: Alt, T, U, F)
  3. The Evaluate Formula window will appear with the formula loaded in the Evaluation box.
  4. The underlined expression within the formula will be evaluated next.
  5. Press the Evaluate button, or the Space Bar, to evaluate the expression and see the result in the formula.
  6. You can continue to press Evaluate or Space Bar to evaluate each step.
  7. You will eventually see the result of the entire formula. You can then press Restart or Space Bar again to start over.
  8. You can close the window at any time during the evaluation process. The window does NOT change the formula at all.

If you find yourself stepping through the formula several times, then you might want to switch back to editing the formula and use the F9 shortcut. I often use both of these techniques when learning or debugging a complex formula.

Function Lock on Laptop Keyboards

At the end of the video I mentioned a tip if the function keys (F1-F12) are not working for you. This is typically due to your keyboard having multi-purpose function keys, which is common on laptops and smaller/newer keyboards.

If that's the case, you will have to press the Fn key in combination with the function key. This can definitely slow you down!

Fortunately, many keyboards have an Fn Lock feature that allows you make the function keys the primary command. You can usually do a quick Google search with the model name of your keyboard/laptop and the phrase “fn lock”, to see if it is available. All manufactures handle this differently.

As a last resort, you can also modify the BIOS to lock the function keys. This is not a permanent change, but harder to toggle on/off quickly.

Checkout my article on the Best Keyboards for Excel Keyboard Shortcuts to learn what to look for in a keyboard. I also share the keyboard I use in that article.

Conclusion

I hope these tips help save you time when working with formulas. This is by no means an exhaustive list.

Please leave comment below with any additional tips or shortcuts you use when working with formulas. Thank you! 🙂

The post 5 Formula Editing Tips for Excel appeared first on Excel Campus.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Bottom line: Learn how to retain and revert to the original sort order of a range or table in Excel.

Skill level: Beginner

Video Tutorial
Retain or Revert Back to Original Sort Order in Excel - YouTube
Watch on YouTube and give it a thumbs up.

Restore the Original Sort Order

Often times we receive a data set that does not have a column of sequential numbers or dates. If we sort one or more columns in the range, it can be difficult or impossible to revert back to the original sort order.

One way to solve this problem is to add a column of sequential numbers to the data set. I typically refer to this as an index column.

After sorting the data you can then go back and sort the index column to restore the original sort order.

Setting up the Index Column

It's important to setup the index column BEFORE you apply any sorting to the data.

Here are the steps to create the index column:

  1. Type a 1 in a blank column to the right of the data range/table.
  2. Double-click the fill handle to fill the number down. Checkout this article on the fill handle to learn more.
  3. Select Series from the Auto Fill Options menu to create a sequential list of numbers 1,2,3,…
  4. IMPORTANT: If you are NOT using an Excel Table then you will need to reapply the filters to include this new column. This must be done BEFORE you sort the other columns so that the index column is included in the sort.

If you are using an Excel Table then you do not have to worry about step 4. The new column will automatically be included in the table as long as you create it directly to the right of the last column. See the video above for more details.

Checkout my video on a Beginner's Guide to Excel Tables to learn more about the benefits of using this awesome feature of Excel.

Here is another method for using the fill handle.

Checkout my article on Fill Handle Hacks to learn more about this technique.

Other Techniques for Retaining Sort Order?

My friend Robbie asked me this question about retaining sort order the other day, which sparked the idea for this post, and this index column method is the one I've always used.

I've also mentioned it before in my article on how to prevent Excel from freezing when deleting rows.

However, I'm curious to know if you have other techniques or methods to solve this problem. Is there a different or better way?

Please leave a comment below and share your thoughts. Thank you!

The post Revert to Original Sort Order in Excel appeared first on Excel Campus.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview