Tips for an effective layout for repeated data

**nicemustang** · 03-21-2016, 11:56 AM

Hello!

I am building an excel sheet to register data from a user research: Basically we will ask 3 test subjects a couple of questions about 3 different topics. The questions for the topics are the same, and so are the different topics for each test subject. This might sound complicated but it basically looks like this:

Subject 1 Subject 2 Subject 3
Topic 1
Question 1
Question 2
Question 3

Topic 2
Question 1
Question 2
etc.

This is how I made the layout now, but there are many, many more questions than just 3 per topic of course, so I would love to keep the top row (with subjects) frozen, but also the row with the topic you are working in. I didn't find any option for this however

I can now only freeze that top row, and even though that helps it is still now very easily visible, I even tried color coding the different questions etc, and again, that kinda helped but it's still a big chaotic datasheet.

Do you guys know any way to make this more organized and easier to read perhaps? Or create more overview etc? Just any tips to make this better/more effective/easier would be nice!

**MrShorty** · 03-21-2016, 12:51 PM

It depends somewhat on what kind of analysis you need to do on the data after it is all collected. A lot of times, it seems that people's data analysis would be a lot easier if they had entered the data as a "database" -- more of a list with records and fields

Please Login or Register  to view this content.

I know that there will be some duplicated cells that way, but this kind of layout often makes it easier to create pivot tables, or use filters, or use lookup and other functions to work with the data. I note that the table you proposed should be fairly easy to create from a database using a pivot table (with topic and question as row labels and subject as column labels).

That would be my first suggestion -- consider the kind of analysis you are going to need to perform on these data. As it often seems appropriate for this sort of thing, consider arranging the raw data in some kind of "database" list format.