My new blog

This blog will not be updated anymore. 
Instead, I will provide great tips & tricks on this page: http://easy-excel.com/

Sort values in Excel

It’s easy to sort a table in Excel, but you have to be careful! There are two important rules:

1. No empty cells in the table
2. Select only one cell or the entire table before you click on Sort

Wrong (only the selected names will be sorted):

Right (all the names will be sorted, and Excel excludes the heading and the total automatically):

The result:

Trace Dependents before you delete a cell in Excel

Before you delete a cell, you should check if there are other cells that are affected by it.

Select the cell, go to the Formulas ribbon and click on Trace Dependents. The arrows show the dependent cells.

Zoom in and Zoom out in Excel

This is a standard shortcut that works in most applications, including all MS Office applications and all web browsers:

Hold down the Ctrl key and use the scroll wheel of your mouse to zoom in and out. 

Of course, you can use the zoom bar on the right bottom of the page, but what’s the point?

Show File Path in Excel

If you want to see the location of your Excel file, or e-mail the link to the file to your colleagues, you can display the file path in the Quick Access Toolbar:

You only have to do this once – when you make any changes in the Quick Access Toolbar, they apply to all Excel documents. 

Do like this (Excel 2010):

1. File => Options

2. Choose All Commands in the drop-down and scroll down to and choose Document Location.

3. Click Add

In Excel 2007:

1. Office button => Excel Options => Customize

2. Choose All Commands in the drop-down and scroll down to and choose Document Location.

3. Click Add

Highlight Cells in Excel that Contain a Formula

A new version of this post has been published on my new blog: 

http://easy-excel.com/?p=133

Is there an easy way to locate the cells in Excel that contain a formula? Yes, there is!

Open the Go to-window (Ctrl+G) and click on Special. Choose the radio button Formulas:

Click OK, and all the cells that contain a formula are highlighted!

Create a Drop Down List in Excel

Today I’ll show you how to create a drop down list in 20 seconds:

1. Open the Data Validation window from the Data ribbon (or use the shortcut Alt => D => L)
2. Select the array you want to have in the drop down list

Done!

For more posts about drop down menus, have a look at my other blog:

http://exceliseasy.wordpress.com/2012/10/14/create-a-drop-down-list-in-excel-in-3-minutes/

http://exceliseasy.wordpress.com/2012/10/15/create-a-dynamic-drop-down-in-excel-in-5-minutes/