Top Menu

7 Guaranteed Time Savers in Excel

1.     Navigating your Worksheets

The next time you are looking to move through the worksheets in your workbook using the navigation keys (buttons found to the left of sheet 1), don’t left click, right click instead, this should bring you a list of all the worksheets in that workbook.

2.     AutoFill (The Fill Handle)

Unfortunately, there are still people out there that either are copying one cell at a time, or clicking and dragging down to copy formulas all the way down a list/column. No Need, let Excel do the work for you.

Double-click the fill handle (little box down the bottom right hand corner of cell selected) to automatically fill down when your formula resides aside of other data. It’ll stop when there’s no data alongside it any more or the first break in the data.

3.     Creating the same Table/List in Multiple Sheets (Grouping Worksheets)

The Next time you’re looking to create a table in Excel but have the exact same table in multiple Worksheets.

Start by selecting the First Sheet (click on sheet Tab) and while holding the Shift Key, select the last sheet in the group.

Now whatever task you perform in the first sheet gets applied across the board.

Make sure when you are done to deselect the sheets, so they become individuals again,  just click on a sheet that’s not part of the group.

4.     Duplicating a Worksheet(s)

You have created a table in a worksheet and now you are looking to copy the contents into another worksheet.  The most common way of copying this is to either try copy and then paste or Right click on Worksheet tab and select create a copy.

There is a however a quicker way, left click on worksheet tab as if your about to move the worksheet but hold down the CTRL key and the same time.  Drag or move this sheet (keeping your finger on the CTRL key). This will duplicate the contents of the worksheet.

5.     Quickly Transposing a Table or List

Select the data (List or Table) you want to transpose.

Select Copy from the top left of the Home tab or use Ctrl + C as an alternative option.

Next click on a blank cell within your worksheet, now click on the Paste dropdown button on the Home tab.

A list of options will appear. Choose Transpose from this menu (last option on the second row)

Excel copies the selected cells into the new cells

6.     Shortcut Key for AutoSum

To sum up a group of cells without having to look for the AutoSum button, try hitting Alt + =

7.     Removing blank cells from a List

Option 1:

 

Highlight all cells (See shortcuts), switch on filters.  From one of the drop-down menus, deselect everything and then choose just the blanks option. Then Highlight and Delete

 

Option 2:

 

Highlight all Cells, this time go to the home tab and the Find & Select button.  From there select go to Special; in that dialog box choose blanks.  Right click on any highlighted area and delete. (Make sure these are blank rows or columns not individual cells).

8 things you need to know about Mastering VLOOKUPS

  1. The VLOOKUP can only work from Left to right (aka a Left Lookup)
  2. The VLOOKUP always finds the first match
  3. The VLOOKUP is not case-sensitive (can be with Case Function)
  4. The VLOOKUP has two match types (True – Approx, False – Exact)
  5. The VLOOKUP uses approximate match by default
    • For approximate matches, data must be sorted
    • You can force VLOOKUP to do an exact match
  6. The VLOOKUP allows wildcards for partial matching (* – Wild Card)
  7. You can use VLOOKUP as an alternative to a nested IF statement
  8. The VLOOKUP can only handle a single criterion

Best practices for Outlook – Managing your Inbox

Using the 4 D’s when working with Mail

  • Delete it.
  • Do it (respond or file for reference, can this be completed within 2 minutes or less).
  • Delegate it (forward).
  • Defer it (using categories and flags) for a second review in your task list.

Delete it

Delete messages that you don’t need to read. If it’s junk, delete it.

If you never want to receive another message as part of this conversation, Ignore it.

Do it: The 2-minute drill

It’s amazing what can be done in two minutes. For example, many messages can be responded to in 2 minutes or less. But if a message takes longer than two minutes to deal with, defer it.

Delegate it

Sometimes you receive a message that is really meant for someone else to deal with. In these cases, reply and include the person to whom you are delegating the message on the To line. If you find you are doing this often, consider creating a Quick Step that replies and adds the delegated person to the To line.

Defer it

 

When to defer a message

Deferring a message means that you will review it later, when you have time.

Reasons to defer a message:

  • It can’t be dealt with in less than two minutes.
  • It will take a while to read.
  • It will require a carefully crafted response.
  • It requires additional action in another program

Duplication Shortcut Key

When working in Word, PowerPoint, Excel and Visio and you need to copy an object(Shape, picture,chart etc)and all its formatting, rather then using the usual copy and Paste, try using CTRL + D.  This will simply duplicate the object you have selected including all your editing and formatting options.

Changing the Default Folder

When you first open PowerPoint, the default folder (Open or Saving) is Documents. To change this, go to the File Tab (Backstage view) and select options. In there on the left is the save option and on the right you will see the default file location.

Changing the Default Folder

When you first open Word, the default folder (Open or Saving) is Documents. To change this, go to the File Tab (Backstage view) and select options. In there on the left is the save option and on the right you will see the default file location.

Changing the Default Folder

When you first open Excel, the default folder (Open or Saving) is Documents. To change this, go to the File Tab (Backstage view) and select options. In there on the left is the save option and on the right you will see the default file location.

Removing blank cells from a List

Option 1:

 

Highlight all cells (See shortcuts), switch on filters.  From one of the drop down menus, deselect everything and then choose just the blanks option. Then Highlight and Delete

 

Option 2:

 

Highlight all Cells, this time go to the home tab and the Find & Select button.  From there select go to Special; in that dialog box choose blanks.  Right click on any highlighted area and delete. (Make sure these are blank rows or columns not individual cells).