Creating a Genealogy Timeline

**Michael7** · 08-14-2009, 02:45 PM

Hi Everyone,

I hope I'm posting this in the right area. I also hope I can get some help with this.

I've been working on creating a timeline that shows genealogy as well. So far the only thing I can seem to do is gather the info and try to draw this thing out by hand. That's honestly not feasible at all, because this is a HUGE project (Spanning 70 generations and thousands of people).

Excel is the only program that I can seem to find or think of that MIGHT be able to do what I need to do. This is what I need:

I need to have a timeline at the very top (just a line representing the time in years). Then beneath that I need the genealogy part, showing who fathered whom and so forth. Their lives would be represented by lines/bars that are accurate to the timeline as well (showing how long they lived, etc.).

An attempt at an illustration follows:

________________time in years_______________
|___father___________
    |_____son_________
       |______grandson_________

And the vertical lines for each person, stem off the parent, while the horizontal line/shaded bar represents their lifespan . . . all accurate to the top timeline dates. It would also take into consideration multiple children of course, but I'd suppose if I can do one child then two or more shouldn't be much harder.

My main question is . . . is this possible? Please tell me it is, because the only other choice I'd have is doing this by hand, measuring with a ruler, and getting tons of eyesore and headaches!

I'm also new to excel. I've tried to learn enough to figure it out myself but honestly it is way overwhelming to me to figure it out. So I'm hoping some experienced users may know if it's possible, and may show me how it can be done?

Thank you so much, in advance! I appreciate any and all help with this!

**Palmetto** · 08-14-2009, 03:06 PM

I would take a look at using one of special softwares that are specifically designed for genealogy.
Genealogy Software

**oldchippy** · 08-14-2009, 03:23 PM

How about

http://www.genopro.com/

**Michael7** · 08-15-2009, 07:09 AM

Thanks for the links

Though sadly I've searched and searched different genealogy programs and seem to hit dead ends thus far in reaching the layout that I'm looking for. Legacy Deluxe 7 has a screenshot (Screenshot) of close to what I'm looking for (except I'm wanting the connections from parents to children to be seen).

GenoPro has a ton of good features, thanks for the link also, oldchippy. I asked if what I'm wanting is possible on their forums too but no reply yet.

One other thing I'm running into with genealogy programs is (of course beside the timeline-layout), the years setting. The timeline needs to go from a starting point of 0, and upward instead of BC and AD, because the years are currently unsure and must be relative to a starting point until I find the BC years.

**martindwilson** · 08-15-2009, 07:54 AM

you could perhaps use a graph type stock h-L-c but the orientation is wrong

**Andy Pope** · 08-15-2009, 08:05 AM

You might be able to do it with bar and xy scatter.

Can you post an example of your data

**Michael7** · 08-18-2009, 07:39 AM

Thanks SO much for the help, I really appreciate it. Still going through genealogy software I still cannot find one that does what I'm needing. So I'm so hoping that excel can do it!

martin, yeah the orientation is off with the example provided

Andy, that's kinda what I'm thinking, some sort of graphing feature would hopefully do it. But again I'm not knowledgeable at all with excel!

Some really loose example data would be something like:

John was born in the year 1476 and lived 96 years
John had Mark when he was 34 years old
John had Adam when he was 36 years old
Mark had Bob at age 25 and lived to be 80
Adam had no sons, and only lived 40 years
Bob had Paul when he was 23.

And so on and so on. I can manage to calculate the time each one was born and died myself, because a lot of the info relies heavily on other info and can be as odd or random as "this person was born 23 years after this person died." So my main thing is getting the dates represented the way I was showing, both for my sake (it'll help me along having something visual to work with), and also when I need to present the complete thing!

**Andy Pope** · 08-18-2009, 07:59 AM

That's not really data you could use in a systamic way.

I understand that's the sort of info you come across as you do the research but how do you currently store the processed information, such as
name, dob, dod, relationships.

**Michael7** · 08-18-2009, 09:32 AM

Everything thus far is in text form. Nothing in excel or anything, just a lot of text in One Note 2007.

I really have to put the info into whatever form I would need to, into excel. Basically starting from scratch

**Andy Pope** · 08-19-2009, 07:54 AM

I haven't got the time to write a complete genealogy package but this might get you along a bit.

I constructed a mini data table from your textual information. Note it was missing some information which I had to fabricate. This is mark with italic values.

The construction of the bars is a simple stacked bar chart.
Difficult part is constructing the order as defined in column H. You need to use an order that causes least amount of line/name obscuring.

Finally you need to contruct an xy set of data for creating the connections.
This uses INDEX and MATCH formula to get YOB and relationship positions.
You have to have a empty cell every 3rd point in order to break the line.

**Michael7** · 08-24-2009, 08:45 AM

Wow, Any! That looks pretty much exactly how I'm wanting it! A little Tweaking and it'd be perfect!

I really REALLY appreciate that, and know you have limited time, so I appreciate it even more!

Is there any quick tutorial or something so I can know how this was done, so that I can edit and use it? Again, I don't know excel so I'm at a loss. Any pointers in the right direction to help me or anything?

**Andy Pope** · 08-24-2009, 08:57 AM

Take a look at the formula used to construct the chart data.
Then you can check the xl help and the forum for more information.

The chart itself is a combination chart of bar and xy-scatter.