As my school year gets closer to the end, my days become more and more hectic. Students who are struggling are finally asking for help after school. And my students have to pass the state test given at the end of the school year in order to graduate, so that means creating extra practice problems for that. Bottom line is, I don’t have as much time to get to the library to research as I “normally” do. (But summer is coming, PTL!) So I use my spare minutes here and there to update my notes and files and to think of ways to make my research more visual because I am a very visual person.
Keeping on with my Excel series, I decided to think of my Excel Group Sheets sort of like my “research wall”. I don’t have to keep all of the information within a certain image size since I’m not printing it, (although I could print the group sheet only by printing page 1 only) so I can put all kinds of information in the worksheet and scroll to whatever section has the information I’m looking for. Last week, I showed how to use comments as post-it notes. This week, I’ve decided to add a timeline to help me zero in on the most likely locations to look for records for my ancestors.
I did a search online for a timeline template and found one that I think is incredible at http://www.vertex42.com/ExcelArticles/create-a-timeline.html, but while I loved the way it looks, I think it would take a pretty advanced Excel user to modify it in any way (like having it show up next to my group sheet). I’m not looking for something that gives me MORE work to do, but a timeline that uses the very basic skills of Excel. Knowing how to do these things are all that are required to make my timeline.
- Highlight a column
- Adjust the width of a column
- Merge cells
- Format cells (left justified, top or bottom, centered, etc.)
- Add color to cells
- Add call outs and adjust their size and shape.
These are the steps to make a replica of my timeline using Excel 2007. I believe everything I do in 2007 can be done with earlier versions except some of the color gradients. I also know that a lot of personalizing can be done to the task bar, so yours may not look exactly like mine in the images.
Due to length, I’m going to make this a multi-part posting.
Choose the location for your timeline.
Because I can’t change the width of columns A-I without changing my group sheet, I decided to put my timeline to the right of my group sheet. I’ve removed my comment balloons for my illustration photos, but I left a couple of columns between my group sheet and my timeline to leave space for those. So my timeline begins at Column L. I also had to consider which row had the height that I wanted to use for my timeline bar. I wanted to have notations above and below and I wanted a row that was high enough to read my dates. I chose Row 6. I plan to put timelines with different information above my date bar, so I wanted to be near the bottom of several rows with the same height. I also wanted to add call-outs below the tame bar, so the thinner rows below Row 6 were good for that.
Format your columns:
I wanted to be able to indicate specific years for items on my timeline, but I didn’t want to take the space to type every year at the top of each column. So I adjusted my columns to be very small and then merged the cells in the timeline bar to groups of 5.
- Decide where you want your timeline bar. Remember, you can’t adjust the height of the top 40 rows or it will alter the height in your group sheet. (Mine is Row 6)
- Highlight several columns (for mine, I highlighted columns L-P)
- Clicking and hold your cursor on letter L at the top of the column and then drag your cursor to the last letter before releasing your click.
- Clicking and hold your cursor on letter L at the top of the column and then drag your cursor to the last letter before releasing your click.
- Right click on one of these columns and choose “column width” and adjust the width. I chose a width of 1.
- Instead of trying to highlight 100 columns, I only adjusted 5 or so and then clicked on one of the letters in the center and then clicked on “insert” over and over until I had the number of thin columns that I needed. Every inserted column has the same width as the column to the right.
Create the Date Bar
- On the Date Bar row (Row 6), highlight the first 5 cells of the date bar. (Column L – P). Right click that area and select “format cells”. Under the alignment tab, select “merge cells”.
- Type in the date you’d like in the box. In my tool bar, I told it to left justify and put it at the bottom of the cell. You can also italicize or choose a different font for different looks.
- Repeat the last two steps working your way across the bar until all of the relevant dates are included.
- Highlight the entire Date Bar and select a fill color.
- You can also add borders around each cell, or across the top and bottom – whatever you’d like for the look you’d prefer.
Your Timeline should now look similar to this.
Next time – Creating a Location Bar
