Organizational charts, or org charts, are powerful tools for visualizing and understanding the structure of a company or team. While there are dedicated tools to create org charts, you can also make one using the versatile Microsoft Excel. If you have your organizational data in table form, transforming it into an org chart in Excel is a straightforward process. Let's dive into the step-by-step guide to create an org chart in Excel from a table.
Before we begin, ensure that your data is clean and well-organized in a table format. The table should include at least four columns: 'Parent', 'Name', 'Title', and 'Employee ID'. The 'Parent' column will help establish the hierarchy. Now, let's get started with the 'SmartArt' tool in Excel.
Preparing Your Data
Before creating the org chart, it's crucial to ensure your data is in the correct format. The 'Parent' column should reference the 'Employee ID' of the person's manager. If a person reports directly to the CEO, the 'Parent' column can be 'CEO' or 'None'.
Here's an example of how your data should look:
| Employee ID | Name | Title | Parent |
|---|---|---|---|
| 1 | John Doe | CEO | |
| 2 | Jane Smith | CFO | 1 |
Creating a New Table
If your data is not in a table format, select your range and go to 'Home' > 'Format as Table'. Choose a table style and check 'My table has headers' before clicking 'OK'. This will convert your data into an Excel table.
Once your data is in a table format, you can peroxidation the 'Total Row' (Table Tools > Design > Total Row) to add useful statistics to your table. This step is optional but can provide valuable insights at a glance.
Sorting and Filtering Your Data
Before creating the org chart, it's a good idea to sort and filter your data. This step ensures that your org chart is easy to read and navigate. Click on the table's header to open the 'Sort & Filter' pane. In the 'Sort' section, sort by the 'Parent' column in ascending order. Then, in the 'Filter' section, select 'Filter by Example' in the 'Parent' column and enter a value to filter your data.
For instance, if you want to see the employees directly reporting to the CEO, enter '1' in the 'Parent' column and click 'OK'. Repeat this process to view other branches of your organization.
Creating the Org Chart
Now that your data is sorted and filtered, you can create the org chart using the 'SmartArt' tool in Excel.
Here's how to do it:
Inserting the SmartArt Graphic
Select your table and go to 'Insert' > 'SmartArt'. In the 'Choose a SmartArt Graphic' dialog box, select 'Organization Chart' and click 'OK'. A new org chart will be inserted into your document, and your table data will be imported into it.
Editing the SmartArt Graphic
Your org chart might not look perfect at first. You can edit it by dragging and dropping the shapes to rearrange them. To edit the text in a shape, simply click on it and start typing. You can also change the style and colors of your org chart by using the 'Design' and 'Format' tools in the 'SmartArt' tab.
The 'Promote' and 'Demote' buttons in the 'Create Graphic' group of the 'SmartArt' tab can help you adjust the hierarchy of your org chart. If you made a mistake, you can use the 'Reset Graphic' button to return the org chart to its default state.
Remember, editing aSmartArt graphic can be time-consuming if your org chart is large or complex. To save time, you can update your table and click the 'Update' button in the 'SmartArt' tab to automatically update your org chart.
Customizing Your Org Chart
You can add more shapes to your org chart by right-clicking on a shape and selecting 'Add Shape'. To connect shapes to existing ones, hold the 'Ctrl' key and click on the shapes you want to connect. Then, click on the 'Promote' or 'Demote' button to change their hierarchy.
To delete a shape, simply click on it and press 'Delete'. You can also resize the shapes by dragging their resize handles. If you want to change the orientation of your org chart, you can choose a different layout in the 'SmartArt' tab.
Updating Your Org Chart
To keep your org chart up-to-date, you need to update the source table whenever there's a change in your organization's structure. Once you've updated the table, simply click the 'Update' button in the 'SmartArt' tab, and your org chart will be updated automatically.
If you want to print your org chart, make sure to turn off the gridlines and adjust the layout and scale as needed. You can also export your org chart to other file formats, such as PDF or BMP, for better compatibility with other applications.
There you have it! Creating an org chart in Excel from a table is a straightforward process once you get the hang of it. With this guide, you can now create and maintain professional-looking org charts that will help your team and company function more effectively. Now that you've mastered the art of creating org charts in Excel, why not explore other features and tools in Excel to unlock its full potential? Keep experimenting and learning, and you'll be amazed at what you can achieve with this powerful software.