The most effective way to format this spreadsheet

Hello all,

I'm looking for some advice on how to most effectively present the following information and recorded data in a spreadsheet.

I've been asked to create a spreadsheet where the following information is recorded daily (for an indeterminate number of weeks): Date, Batch quantity, production time, Processors, Productivity % (calculated from the 3 previous values)

The above information is then recorded for the following products:
Product Group1: product a, b, c, d, e, f

Product Group 2: product a, b, c, d, e, f, g,h, i

Product Group 3: product a, b, c, d, e, f, g

In other words, the Date, Batch quantity, production time, Processors, and Productivity % are recorded for 22 products once per day, every day.

I need to understand the best way to efficient record this information and present it.

I am thinking that the first tab can be dedicated to a summary of the average productivity %, perhaps also show the average for each day of the week. The summary tab seems like the easy part...

But what is the best way to position the spreadsheet for data entry? Separate tabs for all 22 products is not going to be practical... Ideally, this should all fit into a single page with the daily values entered vertically (oldest first).

Perhaps I am answering my own question... but your opinion matters:

How about like this...
            product a | Batch quantity | production time | Processors |Productivity % | product b | repeat the columns...

day 1                         22                2h             3           [calc]                    ...
day 2 
day 3

Open in new window

My only concern is that doing it this way makes it cumbersome to scroll across 110  (22 x 5)  columns in order to enter each day's data!

Any other ideas?

Thanks so much,
S ConnellyTechnical WriterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
I would suggest 7 columns:
Product group, Product, then your 5 data columns.

This will allow you to filter / pivot / query whatever combination you want.
S ConnellyTechnical WriterAuthor Commented:
Hello Rory,

Thanks for your reply. I don't understand what you are suggesting. I think that is what I am already doing.

The columns are: product (product group isn't that important), and then the four data columns (not five :)... followed by column 6 which repeats for next product.
Rory ArchibaldCommented:
No, I'm talking about one table with only 6 columns:
Product, Date, Batch quantity, production time, Processors, and Productivity %
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Glenn RayExcel VBA DeveloperCommented:
Rory is suggesting that you consolidate all your data into one table and add two new columns for "Product Group" and "Product".  Under those two columns you would enter the Group names and Product names repeatedly, as needed for each date recorded.  Like so:
new data table
By doing this, you'll be able to take advantage of Excel Tables and be able to quickly summarize and report this data using PivotTables.  See the attached example workbook.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
S ConnellyTechnical WriterAuthor Commented:
Thank you for the clarification Rory. And thank you so much for your effort in building an example, Glen. That is really appreciated.

But unless, I have misunderstood your intent, the example provided will make it difficult or impractical to enter new dates (the days will accumulate over many weeks).

Please correct me, if I am misunderstanding.
S ConnellyTechnical WriterAuthor Commented:
I was also thinking of another idea, although I have no idea how to build this concept.

Perhaps in addition to using your suggested format, I add a single date, data entry tab. On this tab, a single day's data is entered for all the products. This data is then automatically inserted into a third tab where the historical data is stored in the suggested format.

The point of this is that ultimately, I want to make it clean/easy to enter in new data, each day.

Is such a idea even possible in Excel (without add-ons)?

Thanks again for your support.
Glenn RayExcel VBA DeveloperCommented:

Your primary focus should always be the definition and design of your data.  Once you've properly defined what you want to record and store (including any limits you might impose), then creating reports (output) and data entry (input) are your secondary tasks.

Data saved as an Excel Table, as in the example file, allows you to quickly add new data to the table simply by typing in values in the next available row.  The table range is automatically extended, meaning any PivotTables or formulas referencing the table, fields, and data are also updated.  (Try adding a sample line at the bottom to see what happens).  Additionally, if you have formulas (like the Productivity measure), those will be copied into the new row/record as well.

You can further refine the data entry using Data Validation if you wish.  You'd create new master tables listing the correct Product Group names and individual product names that are valid to use, ensuring that only those values are entered into the Excel Table.  Similarly, you can limit date entry and put checks on the values for batch quantity to ensure that overly large or invalid amounts are not entered by mistake.  By extension - and to answer your question above - you can create data entry forms that simplify the data entry process.  You would need to use some automation using Visual Basic (VBA, macros) to make this work, but no special add-ins are necessary, just some coding.

S ConnellyTechnical WriterAuthor Commented:
Sorry folks for the slow response.

I'll read over the comments this evening.

Thank you to everyone. I really appreciate your assistance. :)
Martin LissOlder than dirtCommented:
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
S ConnellyTechnical WriterAuthor Commented:
Yikes! Sorry for the very slow response!

I'll grade this one now.
S ConnellyTechnical WriterAuthor Commented:
Thank you all.

I appreciate your effort Glenn... I didn't end up using your spreadsheet but it was most certainly helpful in creating what I eventually required. Your advice was also excellent.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.