Excel

[Garno]

Is there an amount of rows that I should not go past before Excel starts playing up?
I will really be after 5300 rows and columns A - M mostly populated with data (Words not figures) plus 1 link in all of the rows.
Would it be better if I split it into 2 and have 2 sheets of 2650 rows?
Whoever invented technology needs a slap ............

 

[RobinBHM]

probably fine if you uses XLSX not XLS

NHS had this problem with test and trace last year


The problem is that PHE's own developers picked an old file format to do this - known as XLS.
As a consequence, each template could handle only about 65,000 rows of data rather than the one million-plus rows that Excel is actually capable of.
And since each test result created several rows of data, in practice it meant that each template was limited to about 1,400 cases.
When that total was reached, further cases were simply left off.
For a bit of context, Excel's XLS file format dates back to 1987. It was superseded by XLSX in 2007. Had this been used, it would have handled 16 times the number of cases.
https://www.bbc.co.uk/news/technology-54423988

 

[Cheshirechappie]

Whoever invented technology needs a slap ............

As we used to say at work - to err is human, but to really foul things up, you need a computer.

(Except we didn't say 'foul'.)

Can't help with the Excel problem, I'm afraid. I use the backs of old envelopes, or sheets of photocopier paper. Never failed me yet.

 

[Just4Fun]

It isn't quite that simple even if you only consider XLS files. If you are using an older Excel version (from the 80s or 90s) the limits are lower - 16,000 rows instead of 65,000. Even with an old version though, 5300 rows should not be a problem. I have certainly exceeded that.

 

[Spectric]

I think this is to do with the platform excel runs on, the old XLS was from the early days of 16 and then 32 bit OS's and now with 64 bit you need XLSX or XLSM if you have macro's or VBA code. Why the NHS would use Excel, a spreadsheet program rather than an SQL database like Access or Sculptor is strange.

 

[Garno]

I won't be doing anything that complex.
Since way back when we have been recording (writing in a book) every single book we have bought, lent from the library, kids have got from school and not forgetting those that Mrs G stole from the local Darby and Joan clubs, in fact if it was classed as a book we wrote it down. It started as a hobby for the kids in the 80's as we were too mean to get them bikes and space hoppers to play with. And before you ask .......... Yes ............... They have resented me ever since.

We (Mrs G and myself) counted up how many between us all and the respective families that the Garno offspring have somehow managed to produce and have found that we have had 5300 different titles over the last 40 odd years, With epub and audiobooks being so much easier and cheaper to get these days the list continues to grow at an alarming rate.

In our infinite wisdom we have decided to log them all on the old computer and have settled on excel. it's fun learning how to use it and I am in awe of the things it can do, no doubt within a week I will be ready to throw it out the window. A positive came out of all the logging down of books and that was the kids always enjoyed reading and were always trying to out do each other as well as read more the myself and Mrs G, it has also got the little ankle biters (The grand kids) into a reading habit.

So thats what I will be using it for.-

 

[Oraclebhoy]

If you can, use the xlsx format. It’s XML in the background.
Handles larger files better.

 

[HamsterJam]

As well as the max number of rows/columns possible don’t forget your computer will need plenty of grunt to work with enormous datasets.
Using the xlxs format, you’ll probably find your computer slows up before you get anywhere near the limit.

 

[John15]

I use Exel to keep a record of my world bird species and sub species seen. It has about 32,000 lines and is produced by Cornell University. Cornell update it every August with the latest scientific findings. Not sure how many columns are available.

John

 

[gmgmgm]

Is there an amount of rows that I should not go past before Excel starts playing up?
I will really be after 5300 rows and columns A - M mostly populated with data (Words not figures) plus 1 link in all of the rows.
Would it be better if I split it into 2 and have 2 sheets of 2650 rows?
Whoever invented technology needs a slap ............

So long as your computer is less than 10 years old and has say 4GB of RAM, you should be fine with that. At work, I frequently see spreadsheets 100 times bigger than that. As said above, use "xlsx" format.

Always keep it simple, don't try to divide up the data.

 

[Padster]

@Garno just one other thing no one has mentioned.... make sure you backup your file. I’d suggested three copies (at least), the actual live file plus the previous version, and a third copy that is stored on different media to the first two.

Regards

Padster

 

[Jelly]

I have recently finished processing a set of spreadsheets with 92 columns and between 320,000 and a little over 1 million rows (quite close to the limitations of modern xlsx) for a project at work...

I would think 5300 rows wouldn't even cause excel to do whatever the computer equivalent of raising a sweat is!

 

[timothyedoran]

I think this is to do with the platform excel runs on, the old XLS was from the early days of 16 and then 32 bit OS's and now with 64 bit you need XLSX or XLSM if you have macro's or VBA code. Why the NHS would use Excel, a spreadsheet program rather than an SQL database like Access or Sculptor is strange.

The answer to your NHS excel use, is that they probably used it because they were working at pace without the time or resources to set up the data management in a robust and reliable way. They probably did a prototype in Excel hoping to do something better when the pressure dropped, but it never does. Excel is also "manager friendly" meaning it could be emailed between management types simply. No need for custom code, front ends, buttons etc. But plenty of risks too, of which one unfortunately was realised, and data were lost.

Couple that with staff who are working at home, internet difficulties, child and dependents and all the rest of what 2020 threw at us. I think it's no wonder mistakes get made.

About Excel for the book list. I think Excel will suit you well. Make regular backups. A simple way to back up is to email it to yourself especially if you have a Gmail type account.

 

[timothyedoran]

I have recently finished processing a set of spreadsheets with 92 columns and between 320,000 and a little over 1 million rows (quite close to the limitations of modern xlsx) for a project at work...

I would think 5300 rows wouldn't even cause excel to do whatever the computer equivalent of raising a sweat is!

That sounds like a good candidate for a database.

 

[Jelly]

That sounds like a good candidate for a database.

You're not wrong!

The files are outputs from a legacy non-relational database, which have to be pre-processed via a macro to sanitise it to go into a relational database...

Because it's not something that needs doing frequently, and the legacy system is due for imminent replacement, processing the data through excel is more cost/time effective than writing a script (legacy database) and associated query (relational database) to do it automagically!

Doesn't half get the computer kicking out some heat thought!

 

[Richard_C]

No trouble with that size even on old systems.

I do (or did, mostly retired) lots of HR work and 'what if' modelling on pay and benefits, and before that was HR head for a biggish employer. Excel enabled a lot of the work we did - proper big databases and systems for running things, excel for 'thinking' with.

Some hints and thoughts:

If you are keeping author records, use 2 columns, surname and first name. Maybe even an honorific which will be blank in most cases, but think SIR ....Conan Doyle. You can always concatenate them, but keeping them separate makes it so much easier to sort and search. As an example, Tate gallery online shop has got its act together now, I emailed them a few months back, but last year if you were shopping for a Hepworth print you would find some listed under H for Hepworth, some under B for Barbara and one under D - for Dame. This wasn't multiple listings of the same thing, you had to look at all 3 to see the complete choice. I was surprised that they had no books on Brancusi, then I found them listed under C - his first name is Constatntin but nobody ever uses it, we don't talks about Pablos, they are PIcassos. Be consistent and get it right from the start, it pays off.

You also need a house rule on indefinite & definite articles at the start of titles. Would you search for "The Book Thief" under B or T? Doesn't matter which way you do it as long as its just one way. Excel is brilliant but its also 'stupid' - inanimate. It doesn't understand that you want to sort things alphabetically and ignore A s and The s.

Backup has been mentioned before. When PCs first started to appear our IT manager wisely said "It's not IF your hard disc fails, it's WHEN it fails." They are a lot more reliable now, but I have always remembered that and backup all that is important. Jack Schofield, wise Guardian tech editor who sadly died last year, always said data doesn't really exist unless it exists in 3 places. My 3 places are the laptop, NAS and Microsoft Onedrive. That might be overkill for you, but have at least one backup.

When I was a student, to correlate 2 sets of variables you had to manually do it - sums of squares etc., if you were lucky you had a comptometer type mechanical calculator to help. 200 experimental results was 2 days tedious work. Now with excel you just highlight the 2 columns of data and choose the correlation you want from the functions - 60 seconds tops.

 

[Garno]

Thank you everyone for all of the wonderful replies it has certainly given me a few more things to think of.

I think the general consensus is that Excel will easily cope with what I want it to do and that has given me another idea to go along side the main one.
Apart from the main sheet that will contain everything in the 5300 rows I am now thinking of perhaps having 26 sheets for the different authors, my thinking is that it would be easier to find books by Authors (OK I don't know how to search in Excel yet )

 

[D_W]

It depends less on the size and more on what you're doing with it and how much dynamic calculation.

I have far fewer rows than that in some sheets and they're trouble to work with. It's not out of the realm of possibility for a file to get corrupted, too, and be trouble when it really shouldn't be.

If it's mostly data and only a few calculations, it shouldn't be trouble.

Use the search function in excel if you have more than one tab - select additional options and choose "search entire workbook" rather than just a single worksheet, and the function will search the entire file.

 

[--Tom--]

Control key + F is the search in excel, works well.
Excel won’t struggle with that size at all. All in one sheet would work best. As you get used to it you could add a pivot table to give you some stats from it if so inclined

 

[Spectric]

Hi all

On the subject of backup, with Windows 10 there is an easy option for backup that is transparent to the user. All you need to do is have two or more drives formated as NTFS and then go to control panel, storage spaces and here you can create a mirror. This is essentially a software version of the Raid array where your data is written to all drives in the mirror and so if one drive crashes your data is still ok on the other drives. It is a good first step in protecting your work, but always have a backup as well and with big cheap storage devices there is no excuses.