I Miss Green Bar Paper and Fixed-Pitch Fonts...

I have to admit, that I am pretty old school, at least that is what my staff (and kids) tell me... As much as I like, and embrace, technology (managing technology and IT types being a significant portion of my work responsibility), I still prefer printed books to digital ones, I prefer file folders stuffed with papers to 300 page PDF documents (of the same stuff).  Paper has a tactile quality that is simply something that a computer will never be able to replicate.

As a result of my fondness for paper, I go through frequent paper purges in my office. Essentially, trying to digitize documents so they are retrievable records of the work we handle--today happened to be one of those days.

As I was digging through one old file folder, I found several really old reports from when I used to work at a subsidiary of the Atlantic Richfield Company (one of my first "real" jobs after college).  The reports themselves were nothing spectacular, but they made me nostalgic none-the-less.  These reports were old school spreadsheets printed on line printers in our data center and were printed on green bar paper in a monospaced font. Ahhhh, the "good ole' days."

Back in the "day" most people did not have PCs on their office desktops.  Most of us had a terminal that was networked to a mainframe in the data center (and if we did have a PC, we were using it like a terminal).  There was fairly robust spreadsheet software on these mainframe systems, but it wasn't like the WYSIWYG applications that are used today.  Then, when you printed a report, it went into a queue and you had to get up from your desk and walk to the data center to pick it up (although if you were high enough on the corporate food chain, the report would be delivered to you). 

The entry level (from a skill perspective) just to get to the data from the mainframe was pretty high (you had to actually know and understand how the data was stored to retrieve it).  But then to to format that data into a useable report, well, you really had to understand more about how computers are programmed/work than you do with today's high level programming languages and GUIs.

Don't get me wrong, I love my Macintosh and its elegant GUI. I am glad that many more people can utilize spreadsheet, word processing and database software to create reports, and I believe those are skill sets that should be taught in our secondary education system (although, I also believe that PowerPoint maybe the root of all evil...but I'll save that for a different post).  I am also happy that printers are cheap and ubiquitous (and that we recycle a lot more paper now, so we "kill" fewer trees).

The problem is, that with all of the tools and options we have today, too few people know how to really use them.  Rather than the intuitive GUIs and WYSIWYG applications truly bringing computing power to the masses, enabling the end user to produce a report that is concise, clear and too the point, too many people spend too much time worried about how the report looks, and not the actual content of the report.  Or (only slightly less worse) they can not use the most rudimentary of tools inside of these applications to perform simple tasks.

Looking at this old report, I remembered that it was in this particular position that I discovered that I had a somewhat unique technical skill that my peers (and supervisors) lacked.  The ability to translate between "geek speak" (before the popular GeekSpeak radio/pod-cast was glimmer in anyone's eye) and English. This skill has propelled me throughout my career, for which I am thankful. But its has also held me back (in some circles)--as I am perceived as the office's "go to tech guy..."

In fact, just the other day, I was instructed to change the fonts on all reports going to one of our high level executives.  When I asked why, the answer came back: "That is the font he prefers reading internal reports in" (sic). Really?! Are you serious?  I'm trying to re-work a report so that it contains the correct data and you want me to stop and change fonts on 30 reports? This is our priority today? Yes, there is something wrong with this picture...

There is something wrong with the fact that most of the people that work in offices (including mine) don't have the requisite familiarity with the primary business applications that they use on a daily basis (just in the last month, I have shown the same person 6 times how to sum a column of numbers...seriously).  There is something wrong with the fact that many people can't simply format a report (despite all of the WYSIWYG features embedded in our programs) so that it is readable...

Which is why the discovery of that old report made me nostalgic for "the good-ole' days" of green bar paper and mono-spaced fonts.  The fonts were easy to read, clear and unambiguous.  We didn't worry if the font matched our letterhead or wordmark, it was a report after all--its purpose was to convey information first (style wasn't unimportant, the information needed to displayed in a way comprehensible by the user, but it was a distant second, and wasn't really about aesthetics).

All of these observations really make me wonder why we don't have any line printers (or any impact printers for that matter) anymore?! We do have Excel and a bunch of laser printers though, and they do produce more aesthetically pleasing documents (or at least they can). Changing a font to a different fixed-width one is pretty straight forward in most applications (⌘A and change the font) but since that train had already left the station today, simulating green bar paper is my mission...Fortunately all of that WYSIWYG power in Excel can come to the rescue (using the conditional formatting feature)...

So, for those wishing to replicate the shading of green bar paper in Excel, here are some quick instructions: First highlight the rows that you wish to apply the formatting to (you can either do a portion of the spreadsheet or select all rows in the spreadsheet as done here):

image 1: select all sells in spreadsheet

Select the Conditional Formatting option form the Format menu in the menu bar:

image 2: select conditional formatting from menu

When the Conditional Formatting window appears, select "Formula Is" from the drop down list on the left side of the dialog box. Then type the following formula: =mod(row(),2)=1

image 3: type formula into dialog box.

Next, we need to select the color we want to see in the alternating rows. To do this, click on the Format button (below the formula bar).

When the Format Cells window appears, select the Patterns tab. Then select the color that you'd like to see. In this example, we've selected a light grey. Then click on the OK button.

image 4: select highlight color desired.

When you return to the Conditional Formatting window, you should see the following. Next, click on the OK button.

image 5: click ok to apply formatting to spreadsheet.

Now when you return to the spreadsheet, the conditional formatting will be applied.
As you can see, you now have alternating colors in the rows. You can insert, delete, and move rows, and you don't have to worry about reapplying formatting.

image 6: congrats alternate rows are now shaded.

As good as this is, the traditional green bar paper that I worked with was shaded in blocks of 4 or 5 lines.  In order to accomplish that, a better understanding of what is going on with the conditional formatting command is necessary...

The Excel MOD function returns the remainder after a number is divided by a [specified] divisor. The result of the function ends up having the same sign as divisor. So in this case the formula in the conditional formatting field (which was =mod(row(),2)=1) means that Excel is dividing the row number by 2, and returning the remainder (which is either 1 or 0).  If the remainder is equal to 1, then it is shading those cells as specified.

So, let's say that you want to shade blocks of 4 rows (like my green bar paper of old), the formula will need to be modified slightly.   In this case we want blocks of 4 rows to be shaded and then blocks of 4 rows to be unshaded.  This means one pattern of shaded and non-shaded cells is comprised of 8 rows (rather than 2 in the alternating scenario above).

However the remainder results from specifying a formula of =mod(row(),8) will range from 0-7. So grouping those numbers together we'd have rows with a remainder of 0-3 will be shaded and those 4-7 will not (i.e., the formula to type in the dialog box would be =mod(row(),8)<=3).

This would work perfectly, if the rows started with 0 instead of 1, but since the first row of any spreadsheet is row 1, the first cycle of shading only shades 3 rows.  Therefore, to get the formula to work properly a small modification needs to be made to the formula as follows:

=mod(row()-1,8)<=3

image 7: enter the modified formula in the formula bar

By subtracting one from the row number we are effectively forcing the rows to start at zero (as far as the formatting is concerned) and  everything works out perfectly:

image 8: groups of 4 rows are now shaded.

So, you can use this formula for any number of rows =mod(row()-1,2n)<=(n-1)

Where n = the number of rows you want grouped and shaded.  For instance if you wanted to shade every 6 rows the formula would be: =mod(row()-1,12)<=5.

QED