Spreadsheets are a great tool for analyzing, manipulating and 'testing' data. Over the years there have been significant advances in spreadsheets that allow for a lot more functionality. Based upon my interactions, I feel comfortable in saying that most people are unaware of and/or greatly underutilize many of the functions and features found in most popular spreadsheet applications.

I often find myself importing thousands of records from databases into a Microsoft Excel spreadsheet for some random ‘quick and dirty’ analysis or output. Once in Excel the possibilities are almost limitless when it comes to the representation of this data, whether it’s via pivot table, charts or just a plain old on the fly filtered list.

When viewing thousands of records it is more often palatable and easier on the eyes with some sort of visual record cues. For example, let’s say we’re looking at a list of customer invoice records, which are sorted by customer. Ata quick glance, by default, all the rows generally look the same. Without intense scrutiny the data sort of, blends all together. What about some sort of visual separation by customer? Excel does have Grouping and AutoFormat, however sometimes the grouping can be overkill. All those '+' and '-' can become overwhelming. Don’t get me wrong grouping is great for adding fast subtotals. The AutoFormat is nice for shading; however it shades on alternating records. What if you wanted to shade groups of records? One answer.. Conditional Formatting.

Conditional Formatting allows you to specify cell properties based upon certain value or formula information. After playing with it a bit, one can see that it is really quite useful. For my scenario:

1. Sort your list
   
2. Insert what I call tend to call a 'Helper Column'. This column is typically hidden, and used to indicate when there is a change in a specific value, i.e. Customer. I prefer to put this column as the first or last column in the data set. For this example we will use column A.


3. Type the following formula in the first data row. We’ll start on row 2 because row 1 is our 'header': '=MOD(OFFSET($A2,-1,0)+ OR($B2<>OFFSET($B2,-1,0),0),2)'. Basically what this formula is saying is alternate 1 or 0 in this cell A2, based upon B2 being different than B1. (The $ before a cell RC value indicates that value is constant (will not change) when it is copied to another cell.)


4. Copy (or use Fill Down; Ctrl + D) the formula down the entire length of the data set. There are a number of shortcuts that make this easier (Mental note: discuss some quick keyboard navigation another time).


5. Select the entire data set including the 'Helper Column', making sure you have the upper rightmost column active, A2 in this example.


6. Select Conditional Formatting… from the Format menu item.


7. Change condition 1 to ‘Formula is’ and enter ‘=$A2=1’. We’re only going to apply the format if the cell value is 1. The 'Helper Column' should be alternating 0 and 1 on a change in Customer.


8. Click the Format button


9. Select the Pattern Tab


10. Choose which color to fill the cell with when the condition is met


11. Click Ok to close the Format dialog


12. Click Ok to close the Conditional Format dialog.

Labels: Apps, How-To, Information, Tech