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.

VBA Training CD

Microsoft Excel 2003, 2007 and 2010

A course for Beginners and Intermediates


- This self-paced training CD contains 13 practical and easy to understand modules.
- Each module is standalone, so you can learn only the topics you choose and in the order you wish.
- Included are dozens of videos, practice questions, and comprehensive exercises to test your knowledge.

CD & Online Access $69
Online Access Only $64
Please contact us if you have any questions.

Create your own applications in Excel

VBA (Visual Basic for Applications) is the programming language for Excel. By mastering it, you will be able to automate routine tasks, manage databases, and interact with users (userforms, dialog boxes)… In short, you will push the limits of Excel!

The syllabus is built on 13 standalone modules that allow you to progress at your own pace and in the areas that interest you the most:

• Introduction
• Your First Macro
• Visual Basic Editor
• Excel Programming Basics 1
• Excel Programming Basics 2
• Program Structures – Loops
• Program Structures – Conditional & Logical Operators
• User Interactions – Basic Message Boxes
• User Interactions – UserForms (1)
• User Interactions – UserForms (2)
• User Interactions – UserForms (3)
• Error Handling & Debugging
• Overview of Intermediate & Advanced Techniques

In total, dozens of videos and exercises with complete solutions illustrate the concepts of each module.

Finally, 7 summary exercises will validate the knowledge you acquired in the chapters studied.

Don’t wait, customize Excel to your needs and develop your own tools by designing them yourself!

Testimonials

“Honestly? I am a fan! A clear and pleasant interface whose you can even change the colors! Easy navigation. But of course, all this is merely incidental. Because the most important thing is the instruction and concerning this, hats off Gaetan!

“Lessons are well-structured, progressive and… clear! It includes the famous animations (always meaningful) that made the success of Polykromy as well as numerous videos. Of course, written explanations are also available. In short, you can choose your mode of learning … You can even test your progress thanks to the proposed solution and compare it to your own.

“What more can you ask for? Nothing because even the paintings beautifully illustrate each lesson.”

From Top Assistante – a site dedicated to improving the knowledge of assistants and secretaries

——

“Here is a copy of the email I sent to G.Mourmant after testing his CD:
Well what Gaetan, work!
This is simply an awesome training CD.
Great in every sense:

“It starts off at a level for beginners who will finally be able to discover the potential of VBA through videos and be carried away by a voice that explains things so simply and clearly. And then there are things that could be considered as details but which makes the interactive training tool so strong:

“- The interactive table of contents
- The table of procedures

“All of which simplify non-linear learning and will allow a user to use this CD every day.
I’m am even going test your CD on those around me who know nothing about VBA but have good command of Excel and see what happens! ;-)

“In any event, I strongly believe in this CD.
Have you sent this to any PC magazine, SVM, etc. …
I really think that you should!
Congratulations again for this beautiful BB!”

From Excel Downloads – a site specializing in Excel applications.

Course Summary

Click the link below to view the course syllabus.