Excel Training: Manual Methods

The client approached I Get It!® Development about training thinking that advanced Excel knowledge would lead to faster results. While this is absolutely true, it isn’t nearly true enough!

Training is always a good idea. If you think you know only 50% of what Excel can do, think again. A more reasonable estimate for even a power user of Excel would be somewhere around 1%. Entire suites of software for linear programming, statistics and data analysis are buried in Excel. There’s always more to learn!

Option 1: Standardize the spreadsheet

The first thing I Get It!® Development suggested was to really standardize the spreadsheets. Note that on the sample table on the first page, Service2, Service10 and Service11 are missing. That’s because field reps type whatever services they perform into the worksheet and leave out services they didn’t perform. That means that every Excel file has a different number of lines. The analyst never knew how many lines to expect on a worksheet. A template worksheet should always list all possible choices so that both people AND computers can know where to find the data.

Along these lines, one option presented to the analyst was simply to create a list of services, service packages, and formula references in a master workbook on one sheet called the formula sheet.

From the formula sheet, copy the region of titles and formulas into each individual spreadsheet to extract the data. Finally, copy the values back to the master workbook onto a second sheet called the consolidated data sheet.

You can watch the video of the process while reading the procedure in either Windows Media or Real Media.

To create a list in the master workbook:

1. Copy the list of services.
2. Paste the list five times, one right after the other, for each of the five service packages.
3. Go back to the top of the workbook and copy the first package - “None”.
4. Highlight the cells alongside Service1 to Service11 and then click Paste – the word “None” will be copied alongside each service name.
5. Go back to the top of the workbook and copy each service package in succession, following steps 3 and 4.
6. Type the appropriate cell reference into the third column.

The list should look like:

A Word From Our Sponsor

When this region is copied into a source workbook, the formula in the third column takes on the appropriate value for the number of hours for the listed service and service package.

The area in the source worksheet is then copied again, but this time Paste Special… Values is used to transfer the numbers, not the formulas, to the master workbook on the consolidated data sheet.

The final consolidated numbers are sorted in descending order and all zero values are deleted. Then the data is ready for upload.

It's easier to see this process in action, so please do have a look at the video.

As you can see in the video, it takes several minutes to set up for one sheet, but after set up, each sheet thereafter takes only a few seconds! It's an excellent way of dealing with standard worksheets.

Consultants don’t usually like getting laughed at, but the administrator chuckled, then laughed outright at the thought of trying to really standardize the field reps.

“I’d rather type this in by hand than go back to the field reps. What else have you got?” So we went on to the next topic.

If your workbooks are not standard, you need a tool that can work for any configuration of services and service packages. Continue on to the next manual topic using named ranges, the intersection operator and the indirect function or skip to the Excel VBA section.