I'm getting motion sickness at my desk.
February 7, 2008 8:54 AM Subscribe
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?
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?
Response by poster: 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 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
Response by poster: 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
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]
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]
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
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
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
Response by poster: 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
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
Tools
Formula Auditing
Trace Error
posted by grateful at 10:17 AM on February 7, 2008
Best answer: 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]
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]
Response by poster: 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
posted by ohdeanna at 12:23 PM on February 7, 2008
This thread is closed to new comments.
posted by d4nj450n at 9:13 AM on February 7, 2008