I'm building a book reading tracker on excel.

unsolved

I have the simple formatting done. I'll provide a screenshot of my template. But, there are some things i want to add that seem too complicated for me. I'm just beginning to use excel again after many years of not using it. Excel Reading List

  • Is there a way to add another log into my sheet without adding another page of data? I don't want to clutter the reading list with all of the following data: I want to record each reading session: how many pages I read that date, how long I read for, the number of sessions.

with that being said, is there a way i can insert that kind of table or link that can show up when I hover over a certain book? i don't want to create another excel page for each book with all that data and i don't want to clutter up the spreadsheet.

  • also, can anyone tell me why G3 is a different color than the rest of the row? I thought I formatted each row to be in uniform color.

  • lastly, on E2 there's a little red note on the top right hand side of the cell. how would i take that off? and what exactly it is?

Posted Apr 24, 2017, 6:01 PM UTC · edited

135
CFAman500•

Welcome to the world of Excel. :)

First, the easy ones: it doesn't appear that G3 is a different color in your screenshot. Just the way light hits your screen perhaps? Either way, rather than having to manually color separate lines, you should probably use an XL table. You can do this by selecting your data (at least the headers) then go to Insert - Table. This will create a nice formatted area, it auto-expands as you add more rows, and will make formulas/analysis easier.

E2 has a comment attached. If you hover cursor over E2, you might see it. To remove this, right-click on cell, delete comment.

For the questions you asked at beginning, I think you'll be able to answer them all easily with a PivotTable. So, assuming you've got your table create, you can go to Insert - PivotTable. This will then create a powerful tool where you can move different fields around, and XL will auto-calculate different statistics, like how many pages read on a date, or how long, etc.

Intro lesson: http://www.contextures.com/CreatePivotTable.html

2
ZombieLabradorOP•

Thanks for that. I dont know how i inserted that comment.

1
ViperSRT3g324•

Cell H3 is actually full white #FFF compared to cell G3's #F2F2F2.

2
8BallRunOut25•

It looks like you'd want 2 tables here. The one you have is good as a summary page - now you'd want one for a reading log which is where the data entry will happen.

In a new Sheet create a table called "ReadingLog" with columns, "Book Title", "Read Date", "Number of Pages Read", and "Time Spent Reading"

Back to the summary page (I'd rename your table here "Books"), now you can use formula's to summarize the data you want. For example, "Started" will equal the first "Read Date" for that specific book in the "ReadingLog" Table. in Cell J3 you could have...

=INDEX(ReadingLog[Read Date],MATCH([@[Book Title]],ReadingLog[Book Title],0))

Also, try removing the formatting in G3 (Home > Clear > Formatting) - It should go back to the table formatting.

Lastly, red thing in the corner indicated that cell has a comment, right click on E2 and delete comment

2
ZombieLabradorOP•

I love this idea. Is there a good website to explain this excel command? What would be another command that might be useful for this purpose?

1