Courses & Tutorials

Excel Data Visualization in Brief

1- Key principles of data visualization

  • Strive for CLARITY & SIMPLICITY
    • Maximize impact , minimize noise
    • If it doesn’t add value or serve a purpose , get rid of it
  • Focus on creating a NARRATIVE
    • Don’t just show data, tell a story
    • Communicate key insights clearly, quickly and powerfully
  • Strike a balance between DESIGN & FUNCTION
    • Selecting the right type of chart is critical
    • Beautiful is good, functional is better, BOTH is ideal

2- The Good, The Bad and The Ugly

Excel Data Visualization - The Good the bad and the ugly
Excel Data Visualization - The bad
Excel Data Visualization - The Ugly

3- The 3 Key Questions

What type of data are you working with?

  • Integer, real, categorical, time series, geo spatial, etc.

What are you trying to communicate?

  • Relationship, comparison, composition, distribution, trending, etc.

Who is the end user consuming this information?

  • Analyst, CEO, client, intern, etc.

 

4- Bar and Column Charts

Commonly used for: Comparing numerical data across categories

Examples:

  • Total sales by product type
  • Population by country
  • Revenue by department, by quarter
Excel Data Visualization - Bar and Column Charts

Pro Tips:

  • Use stacked or clustered bars/columns to group by subcategory or compare multiple metrics
  • Create custom formatting rules to color code bars/columns based on their values

5- Histograms and Pareto Charts

Showing the distribution of a continuous data set

Examples:

  • Frequency of test scores among students
  • Distribution of population by age group
  • Distribution of heights or weights
Excel Data Visualization - Histograms and Pareto Charts

Pro Tips:

  • Adjust the bin size to customize the grouping of values
  • Use Pareto Charts to show the cumulative impact of each bin, ordered by significance

6- Line Charts

Visualizing trends over time

Examples:

  • Stock price by hour
  • Average temperature by month
  • Profit by quarter
Excel Data Visualization - Line Charts

Pro Tips:

  • Use linear or polynomial trendlines to visualize patterns or forecast future periods
  • Combine line & column charts to trend two variables on different scales

7- Area Charts

Showing changes in data composition over time

Examples:

  • Sales by department, by month
  • % of total downloads by browser, by week
  • Population by continent, by decade
Excel Data Visualization - Area Charts

Pro Tips:

  • Keep the number of unique categories relatively low (<6 ) to maintain clarity
  • Use data validation and custom formatting to dynamically highlight specific data series

8- Pie and Donut Charts

Comparing proportions totaling 100%

Examples:

  • Percentage of budget spent by department
  • Proportion of internet users by age range
  • Breakdown of site traffic by source
Excel Data Visualization - Pie and Donut Charts

Pro Tips:

  • Keep the number of slices small (<6) to maximize readability
  • Use a donut chart to visualize more than one series at once, or use transparent segments to create a custom “race track” visualization

9- Scatter Plots

Exploring correlations or relationships between series

Examples:

  • Number of home runs and salary by player
  • Ice cream sales and average temperature by day
  • Hours of television watched by age
Excel Data Visualization - Scatter Plots

Pro Tips:

  • Add a trendline or line of best fit to quantify the correlation between variables
  • Remember that correlation does not imply causation

10- Bubble Charts

Adding a third dimension (size) to a scatter plot format

Examples:

  • Product sales (X), Revenue (Y), and Market Share (size)
    by Company
  • Income per Capita (X), Life Expectancy (Y) and Population ( size ) by Country
Excel Data Visualization - Bubble Charts

Pro Tips:

  • Use color as a fourth dimension to differentiate between categories
  • Use cell formulas and form controls to create a dynamic, animated bubble chart

11- Box and Whisker Charts

Visualizing statistical characteristics across data series

Examples:

  • Comparing historical annual rainfall across cities
  • Analyzing distributions of values and identifying outliers
  • Comparing mean and median height/weight by country
Excel Data Visualization - Box and Whisker Charts

Pro Tips:

  • By default, quartiles are calculated by excluding the median ; this calculation can be adjusted to
    include the median, but may significantly change the result (particularly for smaller data samples) cell formulas and form controls to create a dynamic, animated bubble chart

12- Tree Maps and Sunburst Charts

Visualizing hierarchical data with natural groups/sub groups

Examples:

  • Number of Employees by Department and Office
  • Revenue by Book Title, Sub-Genre, and Genre
  • Population by City, State, and Region
Excel Data Visualization - Tree Maps and Sunburst Charts

Pro Tips:

  • Use Tree Maps when you are only visualizing 1 or 2 hierarchical levels (i.e. topic & sub topic) or when
    relative sizes are important, and Sunburst charts to visualize the depth of multiple hierarchical levels
  • Make sure your raw source data is grouped and sorted before creating hierarchical charts

13- Waterfall Charts

Showing the net value after a series of positive and negative contributions

Examples:

  • Corporate balance sheet analysis
  • Personal income and spending
Excel Data Visualization - Waterfall Charts

Pro Tips:

  • Use sub-totals to create “checkpoints” and split up certain types of gains/losses (i.e. Gross Revenue
    Cost of Goods Sold = Gross Profit , Gross Profit Operating Expenses = Operating Income, grouped and sorted before creating hierarchical charts

14- Funnel Charts

Showing progress through the stages of a funnel

Examples:

  • Volume of views, clicks, and sales on an ecommerce site
  • Number of runners who reach each checkpoint in a marathon (5k, 10k, half, etc.)
Excel Data Visualization - Funnel Charts

Pro Tips:

  • Use “percent of total ” calculations to show the % of users (rather than #) at each funnel stage
  • Customize colors to emphasize progression towards an end goal

15- Radar Charts

Plotting three or more quantitative variables on a two dimensional chart, relative to a central point

Examples:

  • Comparing test scores across multiple subjects
  • Sales of different types of vegetables, by month
  • Visualizing personality test results across subjects
Excel Data Visualization - Radar Charts

Pro Tips:

  • Normalize each metric to the same scale (i.e. 0 1 , 1 10 , 1 100 ) to improve readability and create
    more intuitive comparisons across data series to emphasize progression towards an end goal
  • Limit the number of categories or data series to minimize noise and maximize impact

16- Surface and Contour Charts

Plotting data in three dimensions to find optimum combinations of values

Examples:

  • Accident rates by hour of day and day of week
  • Elevation by latitude and longitude
  • Cookie deliciousness by oven temp and baking time
Excel Data Visualization - Surface and Contour Charts

Pro Tips:

  • Don’t use surface charts if a simple heat map will tell the same story
  • Avoid using wireframe chart types when possible, as they can be difficult to interpret

17- Stock Charts

Visualizing stock market data, including volume, high, low, open, and closing prices

Examples:

  • Facebook’s daily stock performance in 2021
  • High, low, and closing prices for Google in Q1
  • Relative performance across multiple stocks
Excel Data Visualization - Stock Charts

Pro Tips:

  • Manually set axis minimum/maximum values to enhance readability
  • Switch from a date to a text axis to eliminate gaps when markets are closed

18- Heat Maps

Visualizing trends or relationships using color scales

Examples:

  • Accident rates by time of day and day of week
  • Average temperature by city, by month
  • Average sentiment by hashtag
Excel Data Visualization - Heat maps

Pro Tips:

  • Use intuitive color scales (i.e. red to green ) and apply custom formatting to hide cell values
  • Switch from a date to a text axis to eliminate gaps when markets are closed

19- Geospatial / Chrorpleth Maps

Visualizing location based data

Examples:

  • Frequency of accidents by street address
  • Unemployment rate by country
  • Average rainfall by state
Excel Data Visualization - Geospatial and Choropleth Maps

Pro Tips:

  • Use Excel’s Power Map plug in to create geo spatial visualizations and animate changes over time
  • Utilize attributes like color and size to visualize multiple attributes at once

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button