Managing a Large Spreadsheet

jjaggii

New Member
Joined
Apr 11, 2017
Messages
11
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.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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)
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top