How to Create "The Economist"-Style Charts in Excel

Objective

• Guide on replicating three distinct chart types inspired by The Economist using dynamic methods in Excel.

---

1. Bar Chart with Category Titles on Top

Key Features

• Categories (e.g., ships, aircraft, helicopters) have titles above each section instead of alongside.
• Easier category-bar association and more compact design.
• Useful for datasets with widely varying values for clarity.

Steps to Recreate

1. Insert 100% Stacked Bar Chart:
   • Highlight data, go to "Insert" → "100% Stacked Bar".
2. Reorder Categories:
   • Right-click axis, choose "Format Axis" → "Categories in reverse order".
3. Clean Up Chart:
   • Remove unnecessary numbers and gridlines.
   • Adjust legend position.
4. Set Colors:
   • Assign red for China, gray for US using Fill Options.
5. Add Data Labels:
   • Right-click series, "Add Data Labels", align and format as needed (e.g., bold, white font).
6. Add Dynamic Titles:
   • Insert text boxes linked to cell values. Selecting the chart before inserting keeps texts linked and mobile.
   • Duplicate and reference relevant cells for each category.

---

2. Dumbbell Chart (Connected Dot Plot)

Use Case

• Visualizes change or comparison between two data points (e.g., 1970s vs 2020 life expectancy).

Steps to Recreate

1. Prepare Data:
   • Assign a numeric index for each category, since scatter plots need numeric Y values.
2. Insert Scatter Plot:
   • Insert empty scatter plot.
   • Add two series: "before" and "after" (e.g., 2023 and 2024 engagement scores).
   • X-values: percentages or scores, Y-values: numeric category positions.
3. Add Connecting Lines:
   • Use custom error bars on "after" series (horizontal, minus direction) to visually link before/after dots.
   • Error amount equals difference between after and before (calculated separately).
4. Add and Format Labels:
   • Add a series of zeros for X and indices for Y as label anchor points.
   • Right-click, "Add Data Labels", set to show department names.
   • Remove markers if necessary.
5. Customize Appearance:
   • Adjust dot sizes, colors (e.g., light/dark orange), and line thickness/styles for clarity.
   • Color code chart title sections to match data series for better readability.

---

3. Visual Matrix with Color Coded Categories

Use Case

• Visualizes qualitative data using custom color-coded shapes (similar to a heat map, but not numerical).

Steps to Recreate

1. Prepare Data:
   • Structure source data as an Excel table with columns for department, year, complaint frequency.
2. Create Category Shapes:
   • Design colored shapes in PowerPoint, save as pictures, insert into Excel and associate with each category (e.g., "rare", "common").
3. Map Categories Dynamically:
   • Use lookup formula (e.g., XLOOKUP) to assign correct shapes based on data.
   • Place assigned shapes alongside each data row.
4. Generate Dynamic Matrix:
   • Use UNIQUE and TRANSPOSE to list department names and years as matrix headers.
   • Lookup and fill matrix cells with correct images using the concatenated department-year key and XLOOKUP.
5. Add Legend and Final Formatting:
   • Create and position a legend using the inserted shapes.
   • Ensure layout remains dynamic (updates automatically as data is added).

---

PowerPoint Charting Tip: Think-Cell Mention

• Think-Cell is a time-saving PowerPoint add-in for auto-updating charts, trusted by Fortune 500 companies.
• Allows Excel-PowerPoint linking, easy updates, and drag-and-drop editing.
• Free 30-day trial linked in the video.

---

Final Notes

• All chart styles replicate The Economist’s clean, compact, and dynamic visuals.
• Fully dynamic setups mean charts update automatically as source data changes.
• Templates and further resources/courses are available via links provided in the video.

---

Takeaway

• These methods improve Excel chart aesthetics and clarity, making data communication more effective.
• Dynamic charting approaches enable efficient updates and adaptability in business contexts.