In the trenches

Mainframe data is never perfect. There is a long backlog for requests at the average IT Department. Because we cannot get information in a timely manner from the IT guys, the user departments have to take things into their own hands.

The MIS Department is a willing ally in this regard. Realizing that they are understaffed, they will often purchase a 4th GL reporting tool and foist that on the user community in the hope that some users can "serve themselves" and run their own reports.

I began using a product called EZIQ from Pansophic in 1989. Fourth GL was just coming to the forefront, and this product had numerous problems. It was possible to group by customer number and sum twenty fields, but if you wanted the customer name as well, the detailed version of the report was cumbersome and hard to read. You could calculate a gross profit percentage on the detail lines, but at the total line, your only choices were to sum or average the gross profit percentage from the detail lines. If you are reading this book, I don't have to tell you that neither option makes sense.

The first week of EZIQ was rough. I was a young Data Analyst, recently promoted from the MIS Department, trying to sell the concept that "Sure! You have gross profit percentage on every detail line. Why do you need the gross profit percentage for the entire division??" It didn't play well in Minnetonka.

I had been casually using Lotus 1-2-3 since 1985. I had some basic programming skills. I figured that between my knowledge of Lotus and some basic data processing concepts, I could use Lotus 1-2-3 as the engine for producing the final reports. So, after finding a way to download the Lotus *.wks file from the IBM Mainframe to Lotus, I began using the spreadsheet to actually do the bulk of the data processing.

From 1989 to 1998, some portion of my job involved taking mainframe data to Lotus, and later to Excel, and using the spreadsheet to sort, summarize, format, and print reports.

During the Fourth-GL Reporting Package sales presentation, the VPs of Finance were given a demo that made it appear that they were buying a tool that could generate a custom green-bar report in about 10 minutes. No one was smart enough during that presentation to ask if you could calculate a gross profit percentage on the total line, so we were stuck. The point is that the managers expected SPEED.

There were advantages to the fourth GL Tool, however. Early on, ninety percent of the reports that I created were ad-hoc. "We have a problem with product line A," they might have said. "Tell us everyone who has that product." These are requests that would have taken a day for a COBOL programmer, and I could have the data in a spreadsheet in about an hour.

The Fourth GL tool, even with its warts and shortcomings, did offer a faster way to get the ad-hoc data out of the mainframe and to the desks of the decision makers.

The volume of requests increased, and when we hit upon a useful report, it would be added to the list for monthly distribution.

During those first few years, I only had the ability to query history, and only at the end of the month. This kept the volume of requests to a low roar and allowed me to perfect the techniques that I teach you in this book.

I'll also walk you through several case scenarios and how we dealt with them. I'll walk you through the detailed steps of how to deal with and solve these problems in Microsoft Excel.

My goal is to explain the concept in plain English — no "programmer-speak." This book is aimed at accountants, marketers, financial or operations analysts, administrative assistants — anyone who has a large volume of data and needs to get that data into a meaningful format. You might think that I would start with that super-power of Excel data analysis — the pivot table — but I'm going to start with far more pedestrian tasks. Read through these, learn what methods are there, and then, weeks from now, you can refer to this book when you run into a similar problem.

Changing formulas to values

Excel is fantastic at performing calculations. Sometimes, we need to have Excel temporarily calculate something and we need to change the formulas to values. I refer to this technique dozens of times in this book.

One day I received a call from one of the marketing staffers in the company. She had purchased a list of data, and this data included the telephone number and the area code in separate columns as shown below.

A concatenation formula joins two or more fields together.

She wanted to combine the area code and telephone number into a single field. I showed her how to use a concatenation formula to create the desired results.

She was thrilled. Before she knew of this formula, she believed she was going to have to manually retype over 1,000 telephone numbers.

A #REF! Error indicates that one of the cells Excel needs to calculate a formula is missing.

Commonly, Excel novices would now either hide or delete columns B and C. Deleting these columns will have disastrous results. The formatted telephone numbers that were in column D will change to #REF! errors. The #REF! error is Excel's way of saying that you have a formula, and that you have deleted one of the cells needed to calculate that formula.

When this occurs, immediately hit Ctrl+Z to undo the deletion of the columns. The #REF! errors go away.

Spreadsheet veterans may remember this as the /Range Value feature of Lotus. It is just as simple in Excel.

Before you can safely delete the B and C columns, you need to change the formulas in column D to values.

The shortcut for Paste Special Values isesv.

Throughout this book, I advise you to "Paste Special Values" into some range or another. When you see those instructions, I am referring to this technique.

I find that I do Paste Special Values a lot. My fingers can perform the shortcut keys: esv while I'm sleeping!

Other uses for Paste Special

Performing a calculation on every cell in a range

This technique can also be used to multiply a range by a certain number, or subtract, or divide.

This is somewhat obscure, but if you wanted to add the number 2 to every cell in a range of 10,000 cells, it is simple to do so without a formula.

Changing text that looks like numbers to real numbers

Some data sources provide numbers that Excel sees as text. Text that looks like numbers is great when you need to keep the leading zeroes (for example, U.S. Zip codes), but lousy when you want to perform calculations on the numbers. Excel 2002's...