Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


The most effective way to format this spreadsheet

Posted on 2014-10-17
Medium Priority
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:Shawn Connelly
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

by:Shawn Connelly
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 %
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 27

Accepted Solution

Glenn Ray earned 2000 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

by:Shawn Connelly
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

by:Shawn Connelly
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 2000 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

by:Shawn Connelly
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 49

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

by:Shawn Connelly
ID: 40453871
Yikes! Sorry for the very slow response!

I'll grade this one now.

Author Closing Comment

by:Shawn Connelly
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

Industry Leaders: 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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

722 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