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 responsibilities), 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 which 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.

While digging through an old file folder, I found several really old reports from when I worked 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 nonetheless.  These reports were old-school spreadsheets printed on line printers in our data center, 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 networked to a mainframe in the data center (and if we did have a PC, we used it like a terminal).  These mainframe systems had fairly robust spreadsheet software, but it wasn't like the WYSIWYG applications 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 format that data into a usable 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 may be 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 to 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 bad) 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 a glimmer in anyone's eye) and English. This skill has propelled me throughout my career, for which I am thankful. But it 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 rework 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 who 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 be displayed in a way comprehensible to 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 row colors. 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 the 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 shaded, and those 4-7 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