My program manager wants a comprehensive spreadsheet to track all aspects of a program, ie: the kitchen sink. This includes the embedding of pdf files in the spreadsheet, although I have not personally seen this done (other than embedding links to documents outside a spreadsheet). So, my question is just how large can an Excel file be? Or, is it only limited by a PC’s capabilities? …Does Excel itself have limitations in this regard?
For instance: Not quite the same issue, but I heard the UK assumed a spreadsheet was infinite. The UK was maintaining an Excel spreadsheet on COVID cases, but they actually reached a max limit on the number of rows a spreadsheet can have. Perhaps a better example, a path can be too long subsequently blocking a file from opening - once buried in too many sub-folders; YET, the PC does not give one warning - allowing one to create such a trap in the first place. Grrrrrr!
I just want to plan ahead as stupid little things can happen, like being able to save a huge file, closing it, and then finding it cannot be opened because you have exceeded some max size…or annoying things like this.
Officially, a single “worksheet” can have up to 1,048,576 rows by 16,384 columns. You can have as many of them in a file as your system resources allow, though.
This is such a terrible idea. You want some kind of dedicated program tracking platform like Trello or Microsoft Planner if you’re in the Exchange world.
Microsoft Excel is probably humankind’s greatest achievement, but you can’t use it for everything.
Randomly trying here with some game capture files I have, I can successfully embed files smaller than 2GB. It wouldn’t accept a file larger than 2GB. Excel doesn’t complain until 4GB total, at which point it stopped accepting more embedded objects.
That applies only to the practically obsolete 32-bit Excel and also only applies to Data Models. which are a speical weird sort of spreadsheet within an XLSX.
The was about the decades-obsolete XLS file format, not the current XLSX. Avoid using 20 year old file formats and you won’t have nearly the issue.
Truly though, @Palooka has it right. Using an Excel file as a grab bag container for random stuff (PDFs? Ye Gods!) is such a gross abuse that a disaster is sure to ensue. The only question is how many weeks’ work will disappear before anyone notices.
I’d start looking for another job if the boss won’t listen to reason. This will end in tears.
I’ll second LSLGuy’s advice. Run don’t walk if you are forced to go ahead with this. There is no up side. It will fail, and there will be tears.
Using a spreadsheet like this basically ignores pretty much the last 60 years of software engineering experience. The idea almost always comes from people who have no formal computer science skills, and have just grown up using spreadsheets. Managers, accountants etc. They just don’t get how brittle spreadsheets are, and how totally ill suited they are for anything except their base purpose.
You need to answer questions about:
Version control of the code base.
Testing: especially including unit tests, regression tests,
Error detection and control
Recovery from errors
Backup, archiving, reversion, version control of data
Release process of updates
and so on.
Spreadsheets are not data bases. Nor are they coding languages. It isn’t just that their ability to handle data does not scale, it is that every part of the entire paradigm does not scale. This leads to an exponentially increasing likelihood that the whole mess will fail.
The link and 4GB limit it mentions apply to all current 32-bit versions of Excel. A lot of people still use 32 bit Excel for a variety of reasons, even when using Office 2019 on 64-bit Win10. And the 4GB limit is soft - you’ll probably hit that limit long before 4GB.
Excel 2007 has a memory limit of 2GB. Again, soft, maybe 1.5GB in practice. Before that, it was 1GB.
Soft memory limits like that are exactly the kind of thing you have to worry about: it works, then you make some little change, and it doesn’t work, and you can’t undo it in a way that makes it work.
Excel on Win98 (FAT) has a file-size limit of 2GB, so probably Office 97 has a hard file size limit. The UK situation mentioned above used the Office 97 file format for processing and transmission, which is the kind of thing that can happen even when using 64bit office and XLXS generally. In that situation, you would hit file size and column/row limits rather than memory limits.
Unlike everybody else, I don’t have a problem with using zipped XML as a container, nor using Excel to display the documents. Using containers does have some advantages — and some disadvantages. But so does any file system. And I’d certainly rather use Excel for this than Sharepoint or IBM Notes Provided the file size was less than 1GB, and you keep good backups. Sure, you can go past 4GB with Office 365, but do you really want to download 8GB to check something? Carrying a full 44 gallon drum is more difficult than carrying 44 one gallon pails, and you should keep that in mind.
Edit: You’re American. That’s probably a 50 gallon drum.
If it just as a container, and has no code, maybe it is OK. I have seen worse. I discovered in a previous life that the oil and gas exploration industry tends to use Powerpoint as a default container. You could ask for data from some exploration work and be told, “sure, I’ll send you the Powerpoint” and you would have to extract all the data from it. Nuts. We would get asked to send results as a Powerpoint. Even though it wasn’t a presentation, and just a bunch of stuff. Nobody had heard of a tarball.
But you can be sure that no-one will be able to resist the desire to add semantics to the cells, and some parts of the spreadsheet will have actively computed data as well as passive payload. Since the desire is to use the system to track a programme, you can imagine that there will be all sorts of costs, time, date and deadline tracking, and so on. This does not sound good. Creeping featurism will be almost impossible to manage, and that spells the doom of anything it happens to. It is the almost total disconnect between a spreadsheet and software engineering practice that always worries me. The UK debarcle being just another example. They clearly had zero process in place, not actually understanding what a spreadsheet was and what was required.
Stupidity like that tends to bring out my homicidal fantasies. Why do people insist on using the wrong program for the purpose? It’s a PowerPoint presentation, an Excel spreadsheet, and a Word document. A good chunk of my last year in the Navy was spent retyping and formatting a major research project because the moron admin honcho in San Diego decided to use Excel for the documents and Word for the spreadsheets. That might not have been all that bad except he set the things to disable the functions that wold have made it work even though they were the wrong programs for that purpose.
I miss MS Binders. That way you could put the stuff together and do that even by using the correct programs. What a concept!
The bizidjits won’t use Binder or Sets because they don’t know how to use Binder or Sets. But Excel or Word but just one or the other depending on the industry? That they know how to use. So that’s what they do use.
Okay, as I have repeatedly stated, I am a techno-peasant who has trouble with basic things like pagination in Word, but even I can go “What?!?!” to that one, followed up with “But why?!?” In what universe does that make sense?
Numbers in the number app, letters in the letters app…
I can answer part of that because I used to work with a guy who used Excel as a word processor.
He did lots of presentations back in the overhead projector days. All his habits were based on how work was done in the pencil & paper days. He liked the outline / bullet point format of organizing his thinking and his information. Back in the day he’d use 1/4" graph paper as a template to handwrite his neatly indented outlines.
Fast forward to the early 2000s when I met him.
By making the columns in Excel narrow, column A held his main points, column B held his indented sub-items, column C held his double-indented sub-sub-items, etc.
He also liked the idea he could make block diagrams by putting a border around a chunk of cells and typing some words into the cells inside the border. And make lines connecting the boxes by single-bordering the cells in between.
It was pretty painful.
But it “worked” in the sense his ideas got out of his head and onto his screen. So he was happy. And was totally resistant to learning PowerPoint for outline-oriented slides, or any tool of any kind for block diagrams. In his mind it wasn’t broke so it didn’t need fixing.
In his defense he was in his early 60s then and this is now 20 years ago.
Provided the file size was less than 1GB, and you keep good backups. Sure, you can go past 4GB with Office 365, but do you really want to download 8GB to check something? Carrying a full 44 gallon drum is more difficult than carrying 44 one gallon pails, and you should keep that in mind.