I'm getting motion sickness at my desk.

February 7, 2008 8:54 AM

Spreadsheet design: I have to work with an Excel workbook that is driving me nuts...

The workbook has three worksheets representing data from January, May, and September respectively and a 4th sheet that summarizes (grand totals) the data from the 3 months. Within the month worsheets there are long lists of raw data that roll up into category subtotals above. These subtotals then roll up into summary subtotals at the top of the sheet. These totals are then linked to the 4th grand summary sheet. Whenever there is an error, I have to scroll through thousands of lines to find the cell with the problem and it's dizzying. I keep thinking there has got to be a better way.

This workbook is my boss's design and he's very proud of it- but I think it's got too many steps and is not making life easier, as a tool like this ought to.

Okay- so if you've read this far- any advice? Is there a place where I can go to find examples of elegant spreadsheets?

posted by ohdeanna to grab bag (13 comments total)

Well this is not what you are looking for, but it sounds like a job for a a database then a spreadsheet, especially assuming that you are going to keep adding months. Maybe put it in access?
posted by d4nj450n at 9:13 AM on February 7, 2008

It will only have data from Jan. May. and June. The data in the three months has to be constantly updated (it contains budgets, financial commitments, and actual outlays) and constantly changes the bottom line on the summary page. Perhaps I should have mentioned that it's financial data...
I have not used Access very much at all and I have never set anything up in it- perhaps it's good for tracking and summarizing financial data?
posted by ohdeanna at 9:21 AM on February 7, 2008

I meant Jan. May and Sept.
posted by ohdeanna at 9:23 AM on February 7, 2008

When you scan the thousands of lines to find a problem, what problems are you finding and correcting?

Where is the data coming from originally?
posted by odinsdream at 9:27 AM on February 7, 2008

It's grant data. Each row contains a grant ID number and a cost estimate. These numbers sometimes are erroneous or inaccurate and often need updating. And sometimes an excel range needs to be changed as rows are added or deleted- which occasionally throws off subtotals if the formulas aren't checked.

Right now, data is cut and pasted into the workbook. The source spreadsheet is truly ugly, unformatted, and unusable.
posted by ohdeanna at 9:35 AM on February 7, 2008

You might be able to use conditional formatting to help organize the information visually without changing structure.

Some of the ones I use:

check for value in row
=$X1=Y
X is Y's column, Y is value

check for duplicates
=COUNTIF(X:X,X2)>1
X is column

shade alternate rows
=MOD(ROW(),2)

You can modify the shade alt rows one to shade in groups of three or more.

Of course, I may be completely misunderstanding your problem.
posted by JeremiahBritt at 9:43 AM on February 7, 2008 [1 favorite has favorites]

Can you group some of the ranges of cells, and collapse (and therefore hide) them when you don't need that section?
posted by inigo2 at 9:45 AM on February 7, 2008

It is kinda hard to get a good grip on exactly what your data looks like, but if the rows are more or less regular, you could store them in a database (access) and have some little access applications (forms? not sure what they are called) to add data, edit data, etc. And some reports for the summaries.

The problem of course is doing this. You could get a book and spend months setting this up. Or you could get a contractor that could probably do this for you in a week or so. Of course that costs money and it sounds like the boss does not think there is a problem.
posted by d4nj450n at 9:53 AM on February 7, 2008

Ok, for the matter of requiring that some ranges need to be changed as data is added or deleted, look up how to do Absolute Referencing in your formulas.

For example, to get the sum of the first five rows, you can do:

=SUM(A1:A5)

However, if you add or remove rows, that formula will change. To prevent that, you'd use:

=SUM($A$1:$A$5)

This will always show you the sum of cells A1 through A5, even if you copy paste in that area, or add and remove rows in that area.

If you just wanted to stick to the same row or column, you can use the $ in these different ways:

$A1 : stick to A, update row dynamically
A$1 : stick to row 1, update column dynamically.
$A$1: always reference A1.

Toggle between these modes by pointing to a reference while editing a formula and pressing F4.
posted by odinsdream at 9:56 AM on February 7, 2008

I know- I wish I could post the actual sheet. But, I can't. I will investigate Access, though, as a solution. Maybe I could set it up...

The collapsing ranges idea is helpful too. I forgot about that function.
posted by ohdeanna at 9:58 AM on February 7, 2008

If the errors you are researching are formula problems rather than wrong numbers, you can select the following menu (Excel 2002/WinXP):

Tools
Formula Auditing
Trace Error
posted by grateful at 10:17 AM on February 7, 2008

I normally would be the first to recommend a database but in this case, I think the spreadsheet is fine. Porting it to a database and then devising the queries for it would be more trouble than it's worth.

A couple changes to the spreadsheet could go a long way though.
1) Turn on AutoFilter (Data> Filter > Autofilter) on the column headers. Using autofilters, you can quickly isolate rows to fix based on a specific value or range of values.
2) Is your spreadsheet using the Subtotal function or is it manually subtotaling with basic formulas? You may want to look into SUBTOTAL.
3) If you need to add certain numbers from a range of cells that meet certain criteria, you may want to use a SUMIF function.
4) Since you're adding and deleting rows, you're probably coming up against the issue where the ranges in your formula don't include new rows. For that, you'll want dynamic named ranges. Essentially let's say you're doing =SUM(D4:D100) in D1. If you add one more row of data, in row 101, the formula will still only be summing D4:D100. With dynamic named ranges, the named range will expand to include the data in row 101.
posted by junesix at 10:40 AM on February 7, 2008 [2 favorites has favorites]

junesix: could kiss you (dynamic named ranges may save me)... thanks. (thanks to everyone else, too...)
posted by ohdeanna at 12:23 PM on February 7, 2008