Need advice on best way to set up spreadsheet

[Curious Dude]

Hi everyone. I'm studying for a test and I want to make a spreadsheet showing the chapters and the dates I read them and how much time I spend reading them.

I will need to read each chapter multiple times, so this is where I am running into a design problem. As an example, I want to read chapter 1 on 4 different occasions. So, for example, column A will have the chapter number, column B will have the date I read it, and column C will have the time spent reading it. I could have the 2nd time I read it in cell 2 of column B, with each successive date going down the column. The problem is I'm trying to avoid having the spreadsheet be very long vertically.

Is there a better way to design this? Perhaps use more spreadsheets with different chapters? I have ideas on how to set this up, but I wanted to see if I'm missing a better approach than what I've thought so far.

[Brumbot]

By the sounds of it, the way you already suggested is the best way to display this. Is there a reason why you don't want a long vertical spreadsheet? The only other way would be to add extra columns in so that you have an extra set of columns for each time you read it. But this just turns your vertical problem into a horizontal one!

[Curious Dude]

Exactly. That was my problem. I was trying to keep the list concise so I can easily see everything or almost everything at once.

I was just wondering if there was a more effective way of doing this, but it seems like you said, that I will either have a long vertical or horizontal sheet in any event.

Thank you for the reply.

[shg]

A pivot table would summarize your three-column data perfectly: Chapter, dates read, time spent by date, and total time by chapter.

[Curious Dude]

I understand the basic concept behind pivot tables, but I've never set one up.

Using the example above, could you give me a quick idea on how to set it up?

[shg]

Sure. Post an example of your data.

[Curious Dude]

I actually tried to attach a spreadsheet when I first made this thread, but when I click on the clip icon to attach it doesn't bring up a window to browse for a file. All that happens is a little rectangular pop up window shows and it's blank.

Has this happened to anyone before?

[shg]

Scroll down to Manage Attachments button, then go from there.

[Curious Dude]

Ok. I was able to upload it.

[shg]

	A	B	C	D	E	F
1	Chapter	Date	Hours Spent		Row Labels	Sum of Hours Spent
2	1	10/1/2016	1		1	7.5
3	1	10/3/2016	2.5		**10/1/2016	1.0
4	1	10/4/2016	4		**10/3/2016	2.5
5	2	9/17/2016	3		**10/4/2016	4.0
6	2	9/19/2016	2		2	5.0
7					**9/17/2016	3.0
8					**9/19/2016	2.0
9					Grand Total	12.5

[Curious Dude]

Thanks for posting that. Can you give me a brief rundown of how you set that up?

[shg]

Duplicate the chapter numbers as shown, select the data, Insert > Pivot Table, select a location on the sheet, press OK.

Drag Chapter to Row Labels, drag Date to Row Labels, drag Hours to Values.

[Curious Dude]

Thanks so much for your help! I'm having trouble reproducing what you did, but I'm going to try to tinker with it some more.

[shg]

You're welcome, good luck.

Pivot tables are a powerful capability of Excel, and Debra Dalgleish is the queen of pivot tables. Visit contextures.com, and buy one of her books.