Seven Keys to Avoiding Spreadsheet Hell – Ignore the Sixth and You’ll Be Eternally Sorry

hell-275x143[1]

Microsoft Excel is a powerful and widely used tool, but it can also be easily misused.
Here are 7 tips that are sure to make your Excel experience more efficient and enjoyable.

1. Referencing Cells Using the ‘$’ Symbol

The ‘$’ sign is essential when writing formulas. It allows you to lock a row or column, so when you copy the cells down, the referenced cell remains the same.
Tip: Select an address in the formula and use the F4 key to add or remove the ‘$’ symbol.

touche-f4

2. Assigning Descriptive and Symbolic Names to Frequently Referenced Cell Ranges

Whenever a formula uses a fixed cell range, you can assign a descriptive name to that range and use its name in the formula. This will likely make the worksheet more legible and easier to understand. Depending on the situation, this method can replace the use of the ‘$’ symbol.

To quickly add a name, select the cell(s) you’d like to name, enter the name in the Name box, and press Enter. Alternatively, you can also use the Name Manager.

Tip: It’s possible to create dozens or even hundreds of named ranges in one fell swoop as shown in the following image.

Named Range

3. Adding Comments to Complex Formulae

If a formula is complex, it is essential to add comments to allow other users to understand how or what the formula is accomplishing. You can use comments, or even create a separate documentation sheet in the workbook.

Comments

4. Making Lengthy Formulae Reader Friendly

Rather than writing a long formula on a single line, you do either of the following:
– ALT + Enter to add a line break to the formula
– Use whitespaces to simulate indentation

indentation

5. Cross-Referencing Calculations

Though this method requires a little bit more work, using a double entry data table will allow you to check totals both horizontally and vertically.

Additionally, a useful trick is to use information the information provided in the bottom right hand corner of a workbook, for example, to calculate the sum of a selection of cells.

Cross-reference

6. Copying and Pasting Values Instead of References

Since Pivot Tables allow you to connect to an external database, if you aren’t careful you might end up copying and pasting all of the data to another workbook.

Double click on the total TCD to see whether or not this is the case. Of course the reverse is also true. If you receive a TCD, double click on a cell.

Tip: Copy paste values ​​and then copy and paste the formatting to make your table more secure. You may lose some interactivity but at least won’t be exposing your entire company data.

Copy Paste Values
Alternatively, you can also paste as image.

7. Making Sure Your Data is Updated

It’s essential to have the most accurate and recently updated data especially when doing presentations to potential clients.

To refresh an individual pivot table, right click on it and choose Refresh.

Tip: Alternatively, you can also make sure that you’re data is updated whenever the workbook is opened. In the PivotTable options, you can choose ‘Refresh data when opening the file.”

Automatically Refresh
You can also create an event procedure that will refresh the Pivot Table each time the worksheet is activated.

It’s up to you to decide on the best solution depending on the size and refresh duration of your Pivot Table.

All the best in avoiding spreadsheet hell, I wish you an Excel-lent day!

Do not hesitate to confess your own spreadsheet sins! I am sure there is enough to write a novel!

Gaëtan Mourmant
PS. The picture above right is from http://www.exceluser.com/formulas/index.htm

Hyper Navigation Tutorial

Increase your productivity with the Hyper Navigation utility.

If you’re tired of looking through dozens of worksheets trying to find data, watch this free Hyper Navigation Macro video tutorial.

You’ll learn how to produce an easy to use tool to help you analyze and compare data more efficiently.

Best yet, the macro takes less than 5 minutes to write.

To download the workbook accompanying this tutorial, click on the following link:


7g-hyper-navigation.xlsm

 
Stay up to date & register for our newsletter below:

 

This video is an excerpt from the VBA Training CD. It explains how to create the Hyper Navigation utility in Excel 2007 and Excel 2010.

To learn how to create the same utility in Excel 2003, click here.