Top 10 time saving skills you must know in Microsoft Excel

My goal with this article is to help you enhance your Excel skills and save time for yourself. I would like to share some of the tricks and techniques I use to save time when working with formulas. The compound time-saving effect can make a big difference to our work setting allowing us to spend our earned time to other things important to us.

Most of us spend a lot of time working with formulas, and it’s not always a smooth process. As you advance, your formulas get more complicated and time-consuming to maintain. This is the environment of Excel, it can do some amazing calculations with limitless potential, but this means it can also get really messy.

I like to explore the fastest and most effective approach to accomplish a task, and this article includes my top 10 techniques that help me be more productive.

  1. AutoSum Alt+= keyboard shortcut
  2. F2 & Esc keyboard shortcuts
  3. Using Ctrl key for multiple cell references
  4. Being aware of enter and edit modes
  5. Taking advantage of the function screen tip
  6. Quickly jump to a cell reference with F5
  7. Using the Functions Argument
  8. Expanding the formula bar
  9. Understanding Structured References
  10. Using Trace Dependents before deleting

These Excel features, functions, keyboard shortcuts, and techniques I use nearly every day. If you are an Excel user, I encourage you to pick some of these up If you haven't already to save up some time.

AutoSum Alt+=

The one formula I use the most frequently is the SUM function. And the quickest way to create a sum formula is to have Excel do it for you.

The AutoSum button (found on the Home tab and Formulas in the Ribbon) will automatically create a sum formula for a row or column of numbers. The button is only on the Formulas tab on the Mac.

The keyboard shortcut for AutoSum is Alt+= (hold down the Alt key, then press the “=” key).




Ways to use it:

F2 & Esc keys

Once a formula is written, I tend to spend a lot of time editing and revising it. This is especially true when I'm working on someone else’s file and learning what their formulas calculate. This is where the F2 and Escape keys on the keyboard can really save my time.

How to use it:

Quickly add multiple Cell References

Often times you will need to calculate cells in a range which is not joined together. The image below shows an example of this.

I need to create a formula that sums the Total rows for each product. The SUM formula for the Grand Total should be =SUM(C9,C13,C17).




The problem is that typing this formula can take considerable time and be more prone to a mistake, particularly if you have a lot of subtotal rows to sum up. The fastest way to produce this formula is to ​hold down the Ctrl key ​and select the cells you want to include by ​left-clicking the cells with the mouse​.




Step by step how to use it:

  1. Type =SUM( in cell C18 to start the formula.
  2. Hold down the Ctrl key on the keyboard.
  3. Left-click cell C9 with the mouse.
  4. Continue to keep the Ctrl key held down and left-click on cell C13
  5. Formula now reads: =SUM(C9,C13
  6. Continue to left-click additional cells to add them to the formula
  7. When finished, close the formula with a parenthesis )
  8. Click Enter on the keyboard to enter the formula.

Awareness of enter and edit mode

I remember dealing with the frustrating moments and wondering why is that while editing a formula and moving the cursor left/right, instead of the navigating the courser in the formula section, a new cell reference is inserted in the formula?



Later I found out that this behavior is made by the different formula mode that Excel is in when I'm editing the formula in the cell.

There are two different modes that Excel can be: Edit & Enter.

Edit Mode – Excels default mode. In this environment, we can use the left and right arrow keys to navigate the cursor within the formula and make changes.



Edit Mode – Excels default mode. In this environment, we can use the left and right arrow keys to navigate the cursor within the formula and make changes.



How to use it:

Pressing the F2 key will toggle between Edit and Enter modes, giving you more control over your formulas. The F2 equivalent on the Mac is Ctrl+U.

This entry mode applies to other features in Excel where you need to choose a group of cells, for example, changing the source data for a chart or PivotTable.

Taking Advantage of the Function Screen Tip

The truth is that the function ScreenTip is Packed with Features and a lot of people don't use it to the full extent.

I talking about this small box that appears under the formula bar or cell when you are editing a formula.




The part in the function that you are editing will be displayed in bold in the ScreenTip.




You can click on any of the argument in the ScreenTip box to select the text of that argument. This way you can navigate within the formula much quicker and reduce the possibility of making errors.




For what can it be used:

Quickly jump to a cell reference with F5

There are many occasions when I have to navigate within my workbook to find a cell or range that is on another worksheet and the following tip is saving me a lot of time.




Step by Step what you can do:

Using the Functions Argument

The Functions Argument Window can give you detailed information about a function in your formula. It can save you time when trying to audit a function, to see what is included in the formula in a way that won’t make your head spin.




Selecting the cell with the reference and click the formula icon to the left of the formula bar. The quickest way to open it is with the keyboard shortcut, Shift+F3 (Ctrl+A on Mac).

When to use it:



Note: the Same function on Mac is called the Formula Builder. It is laid out a little differently than what I presented here, but it has the same features and should be helpful.

Expanding the formula bar

When your formula gets very long, you can expand the height of the Formula Bar. This a simple quick tip, but it can be really helpful when viewing large formulas.




How to expand the formula bar:

Understanding Structured References

Excel Tables were designed to save time for the user when working with data and I think they do a very great job of it. What were previously time-consuming tasks like the styling, sorting, filtering, summarizing and rearranging our data, now become extremely quick and easy with Tables.

A separate article should be written on the topic of Excel Tables, cause it has many great features, one particularly nice feature of the Tables is the new formula syntax called Structured References.

As you can see in the image below, the structured reference syntax uses column names instead of cell addresses. If this cell was not in a table, the formula would read =E5/D5




It uses the names of the columns of the table, eliminating the need to include the sheet name in the formula to reference a range. This makes formulas much simpler to read and write. It has its learning curve and some getting used to, but in the long run, you will save a lot of time using tables.



Using Trace Dependents before deleting

Have you ever been in a situation where you needed to delete an entire sheet, but you were afraid that some formulas depend on it and don’t want your model to blow up? This is a common problem, especially when you are receiving a project from someone else and you don't know how it was built.

Trace Dependents feature can help you out in these situations. Before you delete the sheet, you should trace the dependents of cells that might be used by different sheets in the workbook.

Example:

We have a VLOOKUP formula on ‘Sheet1’ that references the ‘Data’ sheet. =VLOOKUP(A1,Data!$B$2:$D$100,2,False) If you delete the ‘Data’ sheet then this formula on ‘Sheet 1’ will return an error.




To prevent this you need to trace the dependent formulas of the cells on the ‘Data’ sheet to see what formulas rely on them and it is used in the following way:

Bonus Tip: Learn a new function every day

I hope that you gained some useful tips from these mentioned techniques. There is constantly something new we can learn in Excel, and that is what makes it enjoyable and challenging to work with at the same time.



As a bonus tip, I would encourage you to continue learning more about Excel functions whenever you are able to and a great way to go to the Formulas tab in the ribbon, click on any of the Function drop-down buttons, hover the mouse over a function you are not familiar with, and press F1 on the keyboard to launch the Excel help menu and there you can search and read about the function while simultaneously testing it out on the worksheet. You are going to be amazed at what you learn!


ABOUT

    "Our main focus is on helping people to overcome barriers into Data Analysis and Visualization with help of MS Excel, Power BI and Tableau platform in organizations and in an online education environment with help of ExGap Blog and ExGap Course"

    Continue reading here

RECENT ARTICLE

SIGN UP

Stay up to date on the most popular data analysis and visualization products out there, and learn how to make data-driven decisions. All of our courses ensure quality, usability, and uniqueness of the content.

By creating an account you agree to our Terms & Privacy.