Managing a Large Spreadsheet

[jjaggii]

Hello
I am a software tester and I will soon be carrying out end to end testing which will cover a period of 6 years. Without the luxury of software tools I will utilise Excel. Currently my plan is to make every column a day (2192 columns), and every row a person (up to 500 people). Every person will have a series of up to 100 event types happen relating to them over the 6 years as time moves from left to right, but not necessarily on the same date. Most events are predetermined but other event types will be plugged in as I proceed which will be consequential of a previous event.

In this raw state it is going to be a nightmare to manage.

I guess my only question (for now) would be to ask which excel tools might I go read up on to help me manage my project.

Thank you.

 

[Kamolga]

Hi,

You should rather work on 3 columns (always try to get less columns, more rows)

A kind of log where you can have several entries for one person on several days.
Date Person Event


From there you can have pivot table to put it back in the way you described (I would do the pivot in another workbook to optimise speed of this Logbook) .

Actually, in your case, as I would reach 1000000 lines, I would use MS Access (not a must but a personal preference)

 

[SpillerBD]

Actually, in your case, as I would reach 1000000 lines, I would use MS Access (not a must but a personal preference)

On this point, you could use a different worksheet per year to organize the years and not suddenly run over the row limit of modern Excel sheets. Not saying Access still isn't the better choice, but I know not everyone is familiar enough with it.
As far as having data on multiple sheets, that would be combined with the PowerBI tools, Get and Transform, to load the multiple sheets into one data set to go into a Pivot Table.

You should use Tables. It simplifies entry and defines the ranges.
Also, consider DataValidation with drop-down availability. You will want to ensure the Names are consistent.

 

[Kamolga]

As far as having data on multiple sheets, that would be combined with the PowerBI tools, Get and Transform, to load the multiple sheets into one data set to go into a Pivot Table

You can do it in Excel directly with PowerPivot, don't know if this is what you mean.

 

[shift-del]

Hi,
Actually, in your case, as I would reach 1000000 lines,

I don't know how do you get 1 million rows. I count 500 people x 100 events = 50,000 rows.

 

[Kamolga]

I don't know how do you get 1 million rows. I count 500 people x 100 events = 50,000 rows.

In my proposition of 3 columns, 1 row per day per person for 6 years
-> 500*2192 go roughly to 1 million. I guess hollidays/WE/people with no event on a day (less rows) would be more or less covered by the people having several events in the day (more rows).

To my understanding, 100 is possible types of event (nothing to do in the calculation, it is a separeted list), so 100 possible events by 500 people, if average 1 event per person per day, an event would happens on average 5 times a day.

Now if this event can happen once per person only, indeed, you end up with 50k rows maximum. In such case, Excel in 3 columns is definitely my favorite option.

 

[SpillerBD]

You can do it in Excel directly with PowerPivot, don't know if this is what you mean.

Was thinking more conventionally in appending all data into a single data set. Didn't think of using PP to analyse the parallel tables.

More than one-way to "skin" the data.