Thursday, November 22, 2012

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:

Wednesday, November 21, 2012

Trace Dependents before you delete a cell in Excel


A new version of this post has been published here: http://easy-excel.com/?p=356


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.


















Tuesday, November 20, 2012

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?




Monday, November 19, 2012

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


Friday, November 16, 2012

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!



Thursday, November 15, 2012

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:

Wednesday, November 14, 2012

Avoid #DIV/0 in Excel


An updated version of this post has been published here:http://easy-excel.com/?p=381



In the table below we get the #DIV/0! error because there are no values in the cells that the formula refers to. Let’s see how we can avoid it.
















You don’t want to make any changes only in the cells that turn out to return this error message – that will get you in trouble if you change the data later, so we’ll write a formula that takes into account that some cells are empty.

Let’s wrap the IFERROR function around it:

=B4/C4 becomes =IFERROR(B4/C4,”-“)

What we do is to tell Excel to return whatever is between the double quotes (“) if the formula returns an error. Problem solved!



Tuesday, November 13, 2012

Copy Format in Excel

This blog is no longer being updated. Please visit my new blog at www.easy-excel.com for an updated version og this post: http://easy-excel.com/?p=715


Sometimes some of the data in a table have the wrong format. Here is an easy way to fix it:




Choose one of the cells with the right format and click on the Format Painter on the Home ribbon.

















Select the cells you want to change, and they will get the same format! If you want to change formats in several different arrays, double-click on the format painter and press Escape when you’re done.





Monday, November 12, 2012

Keep the Heading visible in Excel


How can you keep the heading visible in a long list when you scroll down? Easy – use the Freeze Panes feature!

Select the row below the one you want to lock and choose Freeze Panes from the View ribbon:


Sunday, November 11, 2012

Add Sums to an Excel Table


In this post I will show you how to sum up all the values in a table in a second:
Select all the values in the table plus one extra column and one extra row for the sums:












Press Alt + Equal sign (=) and Excel adds all the sums:

Saturday, November 10, 2012

Hide Zeroes in a Table in Excel


A new version of this post has been published here:
http://easy-excel.com/?p=319




Is it possible to hide all the zeroes in this table? Yes, and it’s easy!










Select the cells, go to the home ribbon, choose Conditional Formatting => Highlight Cell Rules => Equal to...











Type a zero in the left field and choose Custom Format in the drop-down menu.













Go to the Font tab and choose white colour (or light grey, if you don't want to hide them completely): 























And now the table looks nice and clean:


Remove Duplicates in Excel

How can you remove duplicates from a list?
Easy! Excel has a built-in feature for this. Just select one of the cells in the list and click on Remove Duplicates on the Data ribbon.




 














VoilĂ , the duplicates are gone:



For more advanced ways to handle duplicates (and triplicates, quadruplicates etc.), take a look at my blog Excel is Easy.