馃攳
How to Create an Organizational Chart Linked to Data in Excel (Easy & Dynamic) - YouTube
Channel: Leila Gharani
[0]
Today we're going to create
[1]
an organizational chart in Excel.
[3]
(pleasant music)
[7]
Forget Smartart, forget shapes.
[9]
They're not dynamic.
[11]
Once your organizational data changes,
[13]
let's admit, they do change quite a lot.
[17]
New people come in, people move.
[18]
Your shapes don't change unless
[21]
you manually move them around
[23]
and the Smartart
doesn't update unless you
[26]
manually type the names in.
[28]
Those times are gone.
[29]
Get ready for a surprise.
[31]
(pleasant music)
[36]
First thing you need is
data for your org chart.
[39]
In this example, I'm going to
show the organizational chart
[42]
just for the finance department,
[44]
and I have the data set up in this way.
[47]
Now I'm going to show you why in a second
[50]
but basically what it
comes down to is that
[52]
I have the employee names.
[53]
Each employee has a unique ID.
[56]
I have the title and the manager ID.
[59]
This is the person the
employee reports to.
[62]
So, this one that doesn't
have a manager ID,
[65]
it means that they're boss boss.
[68]
Boss of the whole department here.
[70]
And then I have the type of employee
[72]
and you're going to see where
this comes in in a second.
[75]
That's the data I want to plot.
[76]
I want to put this in a dynamic way,
[79]
so that if someone shifts positions,
[82]
it's going to get reflected in the chart.
[84]
If I have new people added,
[86]
they're going to get
reflected in the org chart,
[89]
without me having to do
a lot of manual updates.
[93]
There's actually one
manual thing I need to do
[95]
and that's to press the refresh button.
[99]
So, here's how you do it.
[100]
Let me just go to an empty sheet.
[102]
What you need to do is to insert
[105]
a free add-in from Microsoft.
[108]
So, all you have to do is go to Insert,
[110]
click on Get Add-ins, type
Visio in the search box.
[116]
This add-in that we're going
to use is a Visio add-in,
[119]
but here's the best part.
[121]
You don't need to have
a Visio subscription
[125]
to use the Visio add-in for Excel.
[128]
All you need to have is
Excel for Office 365.
[132]
It's an official add-in from Microsoft.
[134]
It's currently in preview,
[136]
so expect this to be updated
and improved on soon.
[140]
Click on Add.
[141]
You can see and read the license terms
[143]
and then click on Continue
[145]
and then this is going to
show up in your Excel file.
[149]
It's called the data visualizer
[151]
and here you don't just
have the org chart,
[153]
but you can create a flowchart,
[156]
a cross functional flowchart
[159]
or an organizational chart.
[162]
Next step is to select the
type of chart that you want.
[166]
In this example, I'm
going to go with hybrid.
[169]
If for some reason you click on this
[171]
and it tells you you're not logged in,
[173]
you can log in.
[175]
If you're logged in under an account
[177]
that doesn't have Office 365,
[179]
make sure you sign out
and you sign back in
[182]
with the account that has Office 365.
[186]
So, next step was to pick
the one that you want,
[188]
let's go with hybrid.
[189]
The moment you do that it's
going to create a template table
[193]
for you and it's going to
visualize that template right here.
[198]
So, this data is connected
to this template.
[202]
This is not my data.
[203]
It's just standard data
that came with the add-in.
[207]
The purpose of this is
that you see the structure
[210]
that this org chart needs.
[213]
Before I copy and paste my data over this,
[215]
let's just take a look at
how this is structured.
[219]
We have on top Bianca.
[221]
Bianca is right here.
[222]
Then we have people that
are reporting to Bianca.
[227]
So, we can see ID1 and ID1.
[229]
We have two managers right here.
[232]
Then notice also that we
have different colors here.
[235]
So, managers are
reflected with the screen.
[240]
Staff has the color blue.
[242]
And consultants so
that's Mattia Sabbatini,
[247]
is reflected with orange.
[249]
So, let's say for
example that one of these
[252]
staff members becomes a consultant.
[255]
Let's take this person.
[256]
If I switch this to consultant
[260]
and I press refresh, this
diagram gets updated.
[265]
This is connected to this.
[266]
Every time you make changes here,
[268]
you need to press refresh
to get it reflected
[272]
in your chart.
[274]
Now let's bring in our own data.
[276]
Well first step is to
make sure you have it
[279]
in the structure that the
Visio add-in understands.
[283]
And I've already done that.
[284]
So, I'm going to copy
this, paste it right here.
[289]
I think I have one more person than this
[291]
but it's no problem for the chart
[293]
because this is an official excel table.
[297]
The moment I refresh this,
[299]
it's going to put in my
information in this chart.
[304]
So, if you take a look at this data
[305]
and take a look up here,
[307]
it says table design.
[308]
This table is called table one,
[312]
and it's automatically connected to this.
[315]
What happens if I add more people to this?
[318]
Well let's say I add a
duplicate ID by mistake.
[322]
I'm going to put ID2.
[324]
I already have ID2 here.
[327]
What happens when I press refresh?
[329]
I get this data checker.
[331]
It says that this row number and this one
[334]
have a duplicate employee ID.
[336]
So, I'm going to change
that, let's go with 30,
[339]
and I'll just add myself in,
[341]
working on videos.
[343]
Let's report to Roger Mun.
[346]
He has ID20.
[348]
And let's say I'm a consultant.
[350]
I'm going to press refresh and
we see my name pop up here.
[355]
If I happen to change division,
[357]
so if I need to move
to Daniella's division,
[360]
all I have to do is change
the ID that I'm reporting to.
[364]
Daniella has ID2 and press refresh
[368]
and I'm right here right?
[370]
So, you can zoom into this.
[372]
You can make the chart area bigger.
[374]
So the zoomed in version
would fit in properly
[377]
or you can click on this
icon here to fit the diagram
[381]
to your space.
[383]
This way you get to have an
org chart that's dynamic,
[387]
and is connected to cells.
[390]
Now before we wrap this up,
[391]
let me just show you one of those
[393]
flowchart diagrams as well.
[395]
So, once you have the add-in installed,
[397]
you can get back to it by going to insert,
[400]
my add-ins and you'll see it
[402]
under the recently used add-ins.
[404]
Just click on it and it pops up here.
[407]
Here is a basic flowchart
[409]
and here we have a cross
functional flowchart.
[413]
So, let's just click on this one
[415]
and see how it looks in
the template that we need.
[418]
So, this flowchart has a different setup.
[420]
We have process step IDs,
[423]
the description of the process step,
[425]
connector labels.
[426]
So, if it's a yes and no,
[428]
the shape type.
[430]
Let me just move this out of the way.
[432]
The function is what we see here.
[435]
So, you just have to update this
[437]
to fit your function.
[438]
So, let's say we have project managers.
[440]
We have subject matter experts,
[443]
and we have our users here.
[446]
If you update this, you can add
in as many lines as you need
[449]
and then press refresh and you see
[452]
the data reflected in here.
[454]
(pleasant music)
[457]
You can also save this as
PDF or print it out directly.
[460]
Just go to the More options here
[463]
and click on Open in Web.
[465]
We come to this page here.
[468]
You can click on More Options here,
[470]
directly print this or
download this as PDF.
[477]
One thing to note is if you want to change
[479]
the way this looks, you
need to have a Visio license
[483]
in order to be able to edit this in Visio.
[487]
So, when I click this it says
[489]
you need an Office subscription
with a license for Visio
[493]
if you want to edit this.
[495]
Otherwise if you're okay
with the way this looks,
[498]
you can use it without a Visio license.
[501]
I hope you enjoyed this data
linked org chart in Excel.
[505]
As you can see, there's lot
of new and useful developments
[509]
in Excel for Office 365.
[511]
If you're excited about
these, hit that thumbs up.
[514]
If you don't have Office 365 yet,
[517]
consider getting it and get prepared
[521]
to simplify your work.
[522]
(pleasant music)
Most Recent Videos:
You can go back to the homepage right here: Homepage