Here are some Excel Tips you might find useful.

Sort by more than three columns
Excel’s sort feature only allows you to sort by upto 3 columns only.

Suppose, you have more than 3 columns, say five columns of data that you want to sort, how would you do that? Well, the key to this is sorting by the last key first and working back to the first key. Assume that your data is in the columns A:E and you want to sort by A,B,C,D and finally E.

• Select all the columns A:E.
• Go to Data>Sort. Sort by C then by D then by E. Click ‘Sort‘.
• Now, again with the columns A:E selected, go to Data>Sort. Sort by A then by B. Click ‘Sort‘.

Entering Named Ranges Into Formulas
When you write a formula, sometimes you would want to use a Named Range as one of the arguments for the formula, but you cannot remember the name. Nothing to worry. Simple press [F3] when you reach the argument that you want the Named Range in, and Excel will display the ‘Paste Name‘ dialog. Click the name you want, and click ‘OK‘.

Transpose Formulas without changing the reference
Using transpose you can change the change the data in columns into rows and viceversa. But if you try to do the same with formulas, you will find that the reference has changed in the newly created column or row. Here is how you can overcome the problem.

• First, select the cells in the column that you want to transpose.
• Then go to Edit>Replace and replace “=” with “#”.
• Now, copy the cells and transpose the data using Edit>Paste special and choosing ‘Transpose‘.
• Now, select the cells in the transposed row or column and again replace all the “#”s with “=”.

View multiple sheets or workbooks
To view multiple sheets or workbooks at the same time, open the workbooks you want to view. To view multiple sheets in the active workbook, click ‘New Window’ on the ‘Window’ menu. Switch to the new window, and then click a sheet you want to view. Repeat for each sheet you want to view. On the ‘Window’ menu click ‘Arrange’. Under ‘Arrange’, click the option you want.
In order to view sheets in only the active workbook, select the ‘Windows of active workbook’ checkbox.

Catching Formula Errors
Whenever typing one of Excel’s functions, especially nested ones, into a cell, use lowercase. This way when you press [Enter], Excel will capitalise only the names of the functions you have entered correctly.

Watch out for more Excel Tips in future.

If you're new here, you may want to subscribe to my RSS feed or get updates through email. Thanks for visiting!