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 %
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.
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!


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 45

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now