Monday, December 30, 2013

Google Trix! - Automatic Org charts on google spreadsheet

I was recently looking for a smarter way to draw my team's organizational chart. The longer, fancier way is to meticulously build the org tree using your favorite vector/office tool (like visio, powerpoint etc). It is perfectly doable for a smaller team. But what if your org has ~300 people.

I had a spreadsheet with Employee name, title and manager, and was looking for a programmatic way to automatically build the tree using the manager names. Visio has a way to import a csv into a org chart but since I don't have it and my coding being a bit rusty, I decided to look for an easier solution in Google docs.

Google docs in the past had a nifty plugin to draw org charts but now the feature is included as one of the chart options. The option just requires 3 fields - Name, Manager's name and Tooltip (optional).

Here is how my org data looks like.
Org FTE Data

Let's format the data by concatenating the Name and Title to look as below

Now, select the columns F-H, go to Insert > Chart, check "use row 1 as headers" in the start tab, and
select "Organizational Chart" under the "More" section in charts tab.

Chart Editor

Our chart is almost ready. It doesn't look that fancy and the chart has hardly any formatting options, but it works. Deeper and narrower trees seem to work better. If your org is relatively flat with a lot of people, it might be harder to fit them on to a single page. You can find the working spreadsheet here. Hope it helped!

1 comment:

AAB College said...

Thank you for sharing