I recently ran into a problem with an application I developed over a year ago. I had pre-formatted 100 rows in a template, some just formatting, some conditional formatting. “We’ll never need 100 rows”, I said. One hundred ninety-six rows later and I feel like the Patent Commissioner who said everything has already been invented.
Should I have picked 1000? Should I have formatted the whole column? I wasn’t trying to be stingy, I just doubled what I though was the most rows we’d ever use. What do you do?
Time to release version 2.0!
Is there any code in the template or the application? Why not format more rows using VBA?
I once created a spreadsheet that used conditional formatting to draw a web-site’s information architecture from a set of structured data. The columns were page name, page type and level in the hierarchy. In another sheet, once a bunch of pivot tables had been refreshed, it displayed the information architecture, left to right in terms of depth, top to bottom for order, with an artistic use of borders to create the linkages.
It was sweet. But once the information architecture reached about 400 rows of display, the conditional formatting gave up, even though the formulae were all there.
Hey ho. Not overly related, but it shows that conditional formatting in Excel has its limits…
Dick
I usually have a copuple of hidden rows at the top of each sheet which contain any Formats, Validation, Formulae that I need for each column.
I use event code on activating the sheet to call an ExtendFormulae macro which looks at the used range of rows, compared with those already formatted, and when this difference falls below a preset value, the macro copies rows 1:2 down the page for a further N rows.
I guess I don’t understand the problem. Doesn’t the “Extend data range formats and formulas” option work for you?
Depending on the setup, Excel 2007 tables, or Excel 2003 lists, are a great solution to this type of problem as well.
I did this exact thing today at work. I set up a workbook template with formulas filled down 500 rows. Based on the expected usage, it should take about 3-4 months before the limit is reached. I plan on waiting until the users complain about the missing formulas before I do anything.
So there!
Thx,
JP
For my big app with variable number of rows I did a few things.
First, I standardized on 100 rows per data sheet.
Then I made sure that the main data area (all formatted, conditional formatting and data validation)has a named range, and then the data area that has populated data is a dynamic named range.
Lastly, under the right click menu I added a user utility to insert or delete single or multiple rows (sheet is protected). The rows are brought in froma template row, include all formatting and formulas.
The user now sizes the sheet to his requirements.
The problem with lists is that people don’t know what they are or how to use them. Rather than try to learn, they just freak out. Also, enough folks are still on 2000 & 2002 to make lists not a universal solution.
I had thought that Excel is efficient about checking the default formatting for the Worksheet first, then for the Row &/or Column formatting, and then for the individual cell…
So you should be able to format an entire column, using up less memory than attempting to format only 100 rows of that column. (I could be mistaken about this, but I thought that this was right.)
a, “copy” then “paste formats” its only 200 rows for cripes sake….
Use a hidden defined name like FormattedUsedRange to store the text address of the worksheet’s UsedRange the last time formatting had been updated. Then have BeforeSave and BeforePring event handlers check that range against the worksheet’s current UsedRange. If the former is larger, extend the conditional formatting (paste-special-format should work) and update FormattedUsedRange.
Re Roger Govier’s suggestion, hidden rows/columns in otherwise visible worksheets tend to cause more problems than they solve. Better to use very hidden worksheets to store special formatting templates in ranges with hidden names specific to the hidden worksheets.
JW: I populate the worksheet via VBA, so it doesn’t extend formatting and formulas automatically. I don’t do the formatting in VBA because it’s already a pretty slow process and I can’t add seconds onto the running time.
Mike: I’ve heard that too, but I’ve been unable to prove it using the file size that windows reports.
I would create a formatting procedure, to be activated by a button so that formatting only happens when the user wants it to happen. The number of rows would have to be defined as a variable.
Add seconds onto runtime for a model that usually involves only 100 rows?
I have several work books for AP and expenses where I have added a button which after checking for the last row and adding on then check that the row above is not empty it copies the formula and formatting into the new row from that row.and adds one to an index column.
The operators know that when they have used the last indexed row they have to press the button.
copying from above means that if we have changed any of the format’s or formula the formula then this will continue
This templates usually uses less than 100 rows, but it isn’t the only template in the model. I don’t know if dynamic formatting would add seconds, or merely milliseconds, but it already takes about 30 seconds to run already.
Ok, I just ran a quick test. There is no doubt that there is a big savings when formatting the entire column rather than only N rows. It’s certainly almost immeasurable with 100 rows, but Excel is efficient about how it applies formatting across an entire Row, Column or Worksheet. In the following, I formatted columns A-H for 100 Rows, 10,000 rows and for the entire column. The resulting file sizes were:
Excel 2003 Excel 2007
——- ——-
100 Rows 17K 10K
10000 Rows 489K 227K
Entire Column 14K 8K
So if you are using “relatively invisible” formatting like font characteristics that won’t show up unless there is data within the cell, then it’s probably easiest to simply format an entire column. If formatting the borders, or back color (aka “fill”) or the like, and don’t want this shown for empty cells, then I guess you need conditional formatting, VBA, or some of the ideas that are being kicked around in here…