Avoiding Color-Coding Problems

Using Color is a very appealing way to format cells in your Excel worksheet. It makes the information you are analyzing and providing to other users visually apparent and adds life to what may otherwise be dull data.

Color-Coding can be problematical, however. If you wish to analyze your data by color, doing this can cause Unnecessary difficulties. Let’s say, for instance, that you want to produce the Average of cells based on color, (e.g. perhaps they represent Regions), and you have coded these cells with five different colors. How Do You Do This?

The VBA coding gurus would say, “Easy, you just write a complex code that you can link to a macro, and Badda-Bing, there you have it!” The fact is, of course, not many of us are comfortable using VBA, and this can also be an issue if the workbook is ever passed on to another user for maintenance.

The Best Solution is to Avoid the Problem entirely, while still making use of your Color-Coding scheme. Simply add an additional column, (which can be hidden, if you wish), and indicate the analysis criteria (this may be “Region”, “Status”, “Type”, “Customer”, whatever…) in this new field.

By constructing your spreadsheet with this additional column/field, you will then be able to easily use a simple AverageIf function (or whatever other function suits your purposes) and easily analyze your data. No VBA Required!

By avoiding the use of special programming, you will avoid potential problems in the future, and you will still be able to avail yourself of Visual AND Analytic Attributes.

In other words, you can have the Best of Both Worlds.  Cool…