Skip to content
Tutorials Center
My learning

Topics

Lesson 57 of 93 ยท Charts and Visualization

Flowcharts, Org Charts, SmartArt and Infographics

Draw flowcharts with shapes and connectors, build SmartArt and organization charts, keep an org chart in step with data, make a Pareto chart, and design simple infographics in Excel.

Not every picture in a workbook is a chart of numbers. Processes need flowcharts, teams need organization charts, reports need a clean infographic and quality work needs a Pareto chart. Excel handles all four with shapes, SmartArt and a little data preparation, so you can keep the diagram next to the data it describes instead of pasting screenshots from another program.

Excel cannot run on this site, so menu steps here are real Excel but read-only. The TryIts use JavaScript stand-ins: one turns a table of employees into an indented org chart, the other computes the sorted values and cumulative percentages behind a Pareto chart. They do not draw shapes.

Flowcharts with shapes and connectors

Use Insert > Illustrations > Shapes and the Flowchart group. The standard symbols are worth knowing:

Shape Use
Rounded rectangle (Terminator) Start and end
Rectangle (Process) A step or action
Diamond (Decision) A yes/no question with two or more exits
Parallelogram (Data) Input or output
Document, Database A file or stored data

A few habits make flowcharts quick to build and easy to maintain:

  1. Turn on the grid. View > Gridlines, and in Page Layout turn on Align > Snap to Grid so shapes line up.
  2. Draw one shape, then copy it. Hold Ctrl and drag to duplicate; hold Shift while drawing to get a perfect square or circle.
  3. Use connectors, not lines. From Shapes > Lines choose an Elbow Connector or Arrow, and drag its ends onto the connection points (small dots) of the shapes. A connector sticks to the shapes, so moving a box moves the arrow with it.
  4. Type inside the shape. Select a shape and start typing. Label decision exits with "Yes" and "No" in small text boxes.
  5. Align and distribute. Select several shapes, Shape Format > Align > Align Middle and Distribute Horizontally for an even layout.
  6. Group the finished diagram (Ctrl+G) so it moves as one object, and set Properties > Move and size with cells so it behaves when rows are resized.

Keep the flow in one direction, top to bottom or left to right, with one start and one end. A chart that needs crossing arrows is usually a sign that the process should be split into two diagrams.

SmartArt

SmartArt (Insert > Illustrations > SmartArt) is a ready-made diagram where you type a bulleted list in the Text Pane and Excel lays out the shapes. Categories include List, Process, Cycle, Hierarchy, Relationship, Matrix and Pyramid.

  • Press Tab in the Text Pane to demote a bullet into a sub-level and Shift+Tab to promote it.
  • SmartArt Design > Change Colors and SmartArt Styles restyle the whole graphic in one step; Convert > To Shapes turns it into ordinary shapes you can edit freely.
  • Pick the type by meaning: Process for steps in order, Cycle for repeating loops, Hierarchy for who reports to whom, Pyramid for ranked layers.

SmartArt is typed, not data driven: it does not read cells, so changing the people in your sheet does not update it (unless you use a Visio data visualizer or Power BI, see below).

Organization charts

For a small team, insert SmartArt > Hierarchy > Organization Chart. Type the names in the Text Pane with the boss at the top level and direct reports indented one level. To add an assistant box (sitting on the side of a manager) use Add Shape > Add Assistant.

For a chart that follows data, keep a table with two columns, Employee and Manager, which is the standard shape for a hierarchy:

Employee Manager
Rosa
Ken Rosa
Mei Rosa
Omar Ken
Tess Ken

From that table you can:

  • Compute the level of each person with a helper column (the boss is level 1; everyone else is the manager's level plus 1): =IF(B2="", 1, XLOOKUP(B2, $A$1:A1, $C$1:C1) + 1). The lookup ranges end on the row above, so list every manager above their reports (otherwise the formula would refer to itself and Excel would report a circular reference).
  • List a person's reports with =FILTER(A2:A100, B2:B100=E1) (FILTER, SORT, UNIQUE).
  • Show the structure as indented text with =REPT(" ", C2-1) & A2.
  • Feed the table to Microsoft Visio Data Visualizer (Insert > Add-ins > Visio Data Visualizer) or to Power BI, which draw the chart from your data. These need Microsoft 365 and a Visio or Power BI license for editing.
JS An indented org chart from an Employee and Manager table

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Pareto chart

A Pareto chart shows categories sorted from largest to smallest as columns, with a cumulative percentage line. It is the quality tool behind the "80/20 rule": a small number of causes produces most of the problems.

Excel 2016 and later: select the categories and counts and choose Insert > Statistic Chart > Pareto. Excel sorts, adds the line and the secondary axis for you.

Any version, by hand:

  1. Sort the table by count, descending.
  2. Add Cumulative %: =SUM($B$2:B2)/SUM($B$2:$B$9).
  3. Select the categories, counts and cumulative column, insert a Combo chart (Choosing the Right Chart) with counts as columns and the cumulative percentage as a line on the secondary axis, whose maximum you fix at 100%.
  4. Set Gap width to 0 to give the columns the classic Pareto look.
JS Sorting causes and the cumulative percentage

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Infographics in Excel

An infographic mixes simple charts, big numbers and icons into one clear picture. Excel is good enough when you plan the layout first:

  1. Start from a clean sheet with gridlines off (View > Gridlines) and a background color (Page Layout > Background, or a filled rectangle behind everything).
  2. Use big numbers. A cell with a font size of 36 pt and a one-line caption beneath it is an effective "KPI" tile. Link the number to a formula so it stays current.
  3. Icons and pictures. Insert > Icons gives free scalable vector icons you can recolor under Graphics Format > Graphics Fill. Place an icon next to each key number.
  4. Small, simple charts. A doughnut for one percentage, a bar chart for four categories, sparklines (Sparklines, Icons and In-Cell Visuals). Remove gridlines, legends and axes that add nothing. Chart text should match your sheet fonts.
  5. Align and group. Use Align and Distribute so tiles form a tidy grid, and use one palette of two or three colors.
  6. Export. Select the area, Copy > Copy as Picture, or print to PDF for sharing. For an interactive result, see Dashboards.

A good infographic answers one question. If you cannot say what the question is, remove elements until you can.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why use a connector instead of a plain line in a flowchart?

    Connectors glue to shapes. Move a box and the arrow stays attached, which keeps diagrams tidy as they change.
  2. 2. Which SmartArt category suits a reporting structure?

    Hierarchy includes organization charts, with levels for who reports to whom. Process is for ordered steps and Cycle for repeating loops.
  3. 3. What two parts make up a Pareto chart?

    Columns show each category from largest to smallest and a line on a secondary axis shows the running percentage of the total.
  4. 4. What is the benefit of keeping an Employee and Manager table behind an org chart?

    SmartArt is typed by hand, but a two-column table lets formulas derive levels and reports and can drive tools such as Visio Data Visualizer.

Summary

  • Flowcharts use the Flowchart shapes, snap-to-grid, connectors attached to connection points, and Align/Distribute; group the result.
  • SmartArt turns a typed list into a diagram; pick the category by meaning, and Convert to Shapes for free editing.
  • Keep an Employee and Manager table for org charts so levels and reports can be computed.
  • A Pareto chart is sorted columns plus a cumulative % line on a secondary axis, or Insert > Statistic Chart > Pareto in Excel 2016 and later.
  • Infographics combine big linked numbers, icons and a few simple charts on a clean sheet.

Next: Sparklines, Icons and In-Cell Visuals.