how to maintain the timeline

How to Maintain the timeline PhL 9.4.09 Unfortunately, I have no real timeline software, and I am limping along with Excel. The task is how to add new names and then get the entire chart to display things right. Note: If you are going to fiddle a lot, make a copy of the original chart and play with the copy. 1. Adding new names is easy. Just add them to the list, putting in only the birth and death date of each person, a middle column computes the lifespan which will be the length of that person's "bar". Then sort the data by birth date, my chosen method for display. 2. Go then to Chart 1, which is the earliest one in time. Somewhere on the chart right-click so that you can see the Source Data item. You will see then on the spreadsheet something like this: The two columns B and C are actually the "widths" of two stacked sets of horizontal bars. The leftmost bars are made invisible, and then the next set of bars shows the lifespan of each person, and I manage to get the person's name in this lifespan bar. Each lifespan bar then starts where the invisible supporting bar ends, and that is why the length of the supporting bar is the birth date of the person. In the above example, I am displaying 28 people on Chart 1, since 28 rows are outlined as shown. Here is the Chart 1 that goes with the above data: (this idea of making the supporting bars invisible results in what they like to call a "floating bars" chart). The idea here is to have several different charts and tape them together to form one large chart. The top of Chart 2 gets taped to the bottom of Chart 1, but Chart 2 is horizontally shifted to the right so that the main diagonal of names continues as if it were all on one sheet. The date bars must be lined up to do this. There are very many technical issues involved just in making a chart page be the way you want, so I will run down these issues here: (1) How to control bar color (and hence invisibility). The left (invisible) set of bars is called Series 1 and it involves just the data in column B of the spreadsheet: the B column data controls the heights of the invisible Series 1 bars. Then Series 2 are the bars we care about, their widths are controlled by the data in column C. These are the only two Series of data there are. In the strange Microsoft language, the vertical axis in my chart is called the Category axis, so each person is a category. The name of each person on my spreadsheet in column A is the "category label". In a normal vertical bar chart, you would think of this as the "x axis" but of course we have horizontal bars so that is a little confusing. The bar heights are called the "Values". You can select any gray bar, right-click and do Format Data Series and you get a set of tabs. If you do this, you see that I have set the bars gray, and there is no "border" around each bar (that would interfere with the long names). The Data Labels are the "Category name", because we want the people names to appear in the boxes! The Options box then controls the height and spacing of my gray boxes, but in a manner that does not affect the total height of each row! I have 100 and 10 here right now. Once Series 1 is somehow made invisible, you can no longer select it to look at IT'S Format Data Series! Someone else noticed this little problem, to wit, The answer is that you select the overall chart, then play with the arrow keys until you see Series 1 appear in the "Name Box" at the top left: These arrows let you move around between your "Series" and select particular items in a Series. So in the above I have done this and the block squares show where this Series lies 1. You then pick the Menu Item Format/Selected Data Series and you can then access formatting of this series. Right now the area color is set to "none" so it is transparent (and non-clickable). (2) How to control the font (size, etc) of the people whose names are the data labels. Right-click on any box name, right click to get Format Data Labels... The changes you make affect all labels of the series. I have it set now to Arial Bold 9 with autoscale. You can control both the color of the letters, and the little rectangle that they fill (which overlies the color you set for the bar itself). (3) How to control the row height on the chart. This is controlled solely by the number of names you have in your series. Fewer names means wider bars, etc. I found that about 30 names per Chart is OK, give or take a few (28 in above examples). Remember that the relative bar size within the given row size is set by some options already mentioned above (4) How to select the date range for a chart page. I think it is best if each page covers the same number of years, say 400 years, or 300 years. Then the time scale on each chart page is the same so the overall multipage chart won't be distorted. You need to divide the spreadsheet data so that no person straddles two pages. And you want about 30 people per page. (5) How to make the two end gridlines show up If you set an axis to something like 1500 to 1900, the end gridlines don't show up. But if you then select the chart and say Format Plot area, you can put a box around the chart and that in effect makes those end gridlines. (6) Printing Margins. I have them set like this in Page Setup This applies to all chart pages at once, and the spreadsheet page two I suppose. Notes Added 1.16.10 Today I added lots of people and I see that the instructions above are not very clean. 1. The first thing you do is add the people to "the list", putting in just their birth and death dates, letting the spreadsheet compute the lifespan. Trim names so they are not too long by replacing some middle names with just initials. Then sort the entire list by birth date. 2. Now, we have several charts because we only want to have maybe 33 people per chart (don't do anything yet! Keep reading). These are called Chart 1, Chart 2 and so on. After doing the sort, for each chart, right click in the white to the right area, bring up Source Data, and then select on the left just the first three columns for the range of people you want on that sheet. So you might put the first 33 people on Chart 1, the next 33 on Chart 2, and so on. (don't do anything yet, keep reading!) From time to time, you will have to add a new chart. To do this easily, use Edit/Move of Copy Sheet. (a Chart is a sheet). Check the copy box, then insert this new sheet to the left of the Sheet1 list of people. The new chart gets some default name that you then change. Now you have to set the people range for this new sheet. Since the last chart won't be "full, a problem arises at this point. If you select just the people you want, they all get very tall bars to fill out the page. If you select off the bottom of the list to include some blank lines, the chart screws up completely. You can undo, or reselect, or just type in the range which might be the safest thing. So it seems that if every chart must be "full" and if the last chart has a small number of names on it, its bars will be very tall compared to those on earlier sheets. Therefore, we want to arrange to have about the same number of people on each sheet. This means look at the total number of people (today this is 83), and divide them equally on some number of charts such that you don't have too many on a sheet. Having 33 looks good, so that would be a target. Today I started with two Charts and about 66 people, but now there are 83 people. I added Chart 3, and now I will compute 83/3 = 27.67 and put about 28 people on each Chart. So 1-28, 29-56, 57-83. Done. We won't have to worry about chart heading dates any more, the chart will just get denser in the time frames already displayed. Then print the charts (select one at a time and hit print button) and tape them together in such a way that you can see dates at the top of each page. Align the times for the first two chart pages.