Tips and Techniques

Excel Best Practices Resources & Tips

Appendix C: Excel Best Practice Techniques and Hip Pocket Tips contains a list of the 150 top best practice techniques and where to find them in the book. Readers have requested this as a "rip-out" so they can have it available for quick reference by their computers. So by popular demand, here is Appendix C. follow this link to download Appendix C. The file is in PDF format and is approximately 434K in size

Listed below are various spreadsheet tips and techniques.
Where appropriate, links to sample files will be provided.
Check back here frequently as new ones will be added.

Tip 001: Inlining Increments

Ch. 7 Smart Data - inlined row counting in section of Anchor Cells

Mechanism for incrementing (or decrementing) cells in a spreadsheet by a set amount without requring a second column to keep track of the changes.
Benefit

While you can use a direct increment or decrement in your formulas, counting differences will automatically adjust when you insert or delete rows in your spreadsheet.

Tip 002: Adding Uncertainties

Ch. 8 Data Analysis - section on Quantifying Uncertainty

In this simplified example, independent uncertain quantities are combined. Notice that the adding of uncertainties is not just a simple linear sum. More info on combining uncertainties can be found in Chapter 8 and 11 of Excel Best Practices for Business.

Tip 003: Layered Pattern Technique
Note: This file is relatively large (500K).
That's because most of the spreadsheet is data.

This spreadsheet shows how a large and complex spreadsheet can be broken up into separate presentation layers, an analysis engine, source data. This modularity simplifies spreadsheet design, as each component focuses on just one purpose and is less prone to error. Revising any component is not disruptive to the rest of the spreadsheet. Also, there is no penalty in computation performance when increasing quantity of data analyzed.

This example is taken from Chapter 8 (Analysis of Data) of Excel Best Practices for Business.

Tip 004: Interactive Web Survey
Note: To use this file, you MUST be using
a version of Excel that supports XML.
(Excel 2003 - Professional or Office 2003 Professional).

Ch. 12 Spreadsheet Portals, XML, and Web Services

This spreadsheet allows you to participate in an online interactive survey. As you submit information, you get back survey summary results.

It uses the XML capabilities of Excel, and you need Office 2003 Professional or Excel 2003 Professional for this.

This example is taken from Chapter 12 (Spreadsheet Portals, XML, and Web Services) of Excel Best Practices for Business.

Disclaimer
Evolving Technologies Corporation and the author are supplying these spreadsheets "as-is" without any warranty of any kind.
Evolving Technologies Corporation and the author make no assertions about the correctness, efficacy, or safety in your use of these spreadsheets, files, and instructions supplied and disclaim any direct, consequential or incidental responsibility, liability or obligation in connection with the use of such materials.