The most effective way to format this spreadsheet

Posted on 2014-10-17
Last Modified: 2014-11-19
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,
Question by:sconnell
  • 6
  • 2
  • 2
  • +1
LVL 85

Expert Comment

by:Rory Archibald
ID: 40386397
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.

Author Comment

ID: 40386705
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.
LVL 85

Expert Comment

by:Rory Archibald
ID: 40386916
No, I'm talking about one table with only 6 columns:
Product, Date, Batch quantity, production time, Processors, and Productivity %
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

LVL 27

Accepted Solution

Glenn Ray earned 500 total points
ID: 40387142
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.


Author Comment

ID: 40387321
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.

Author Comment

ID: 40387337
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.
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 500 total points
ID: 40387434

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.


Author Comment

ID: 40400481
Sorry folks for the slow response.

I'll read over the comments this evening.

Thank you to everyone. I really appreciate your assistance. :)
LVL 47

Expert Comment

by:Martin Liss
ID: 40453880
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.

Author Comment

ID: 40453871
Yikes! Sorry for the very slow response!

I'll grade this one now.

Author Closing Comment

ID: 40453881
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.


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question