Webbo3 Data Analysis Bootcamp · Data Visualisation Module · Lesson 15

Excel Charts: Choosing the Right Type, Combo Charts, Chart Elements, and Professional Styling

A hands on lesson on picking the correct chart for your data, building every major chart type, and the genuine, professional habits that separate a chart anyone can read from one that quietly confuses its audience.

Every Pivot Table you have built so far in this bootcamp produces a grid of numbers. A chart takes that grid and turns it into something a person can understand in two seconds rather than two minutes of reading. But a chart can also actively mislead if the wrong type is chosen, and this lesson treats that decision as the real skill here, the clicking itself is the easy part.

1. Column Chart and Bar Chart: When to Use Each

Column and bar charts are structurally identical, the only real difference is direction. A column chart stands its bars vertically, with categories sitting along the horizontal axis and values measured up the vertical axis. A bar chart lies the same bars on their side, categories running down the vertical axis and values measured along the horizontal axis. Both exist specifically to compare discrete categories against each other, products, regions, salespeople, departments, and both handle multiple data series cleanly by clustering bars of different colours side by side within each category group.

The genuinely useful rule for choosing between them. Default to a column chart for most comparisons, since it is the more familiar, more immediately readable format. Switch to a horizontal bar chart specifically when your category labels are long, for example full product names or full department titles, since cramming long text under narrow vertical columns forces labels to overlap, rotate, or shrink to the point of being unreadable, while a horizontal bar chart gives those same labels the entire left margin to breathe. Also reach for a bar chart whenever you have more than roughly ten to fifteen categories to compare, since stacking them vertically down the page reads more naturally than squeezing them sideways across it.

2. Line Chart and Area Chart

A line chart plots data points connected by a continuous line, and it exists for one specific purpose: showing how a value changes over time or across a continuous sequence. Monthly revenue, daily website visits, year over year growth, all of these are line chart territory, since the connecting line itself visually represents the trend, the direction of change, in a way a column chart cannot. Line charts also handle multiple series cleanly, plotting several trend lines on the same axes for direct comparison over the same time period.

An area chart is a line chart with the space beneath each line filled in solid. This single visual change shifts what the chart emphasises: a line chart draws attention to the shape of the trend itself, while an area chart draws attention to the magnitude, the volume, accumulating beneath that trend. A genuine limitation worth knowing before you plot multiple series this way: a standard, non-stacked area chart can completely hide a smaller series behind a larger one, since the larger filled area sits visually on top and obscures whatever sits behind it. If you genuinely need to compare multiple series as filled areas, the stacked area chart variant solves this by layering each series on top of the previous one's total, rather than overlapping them, ensuring every series stays visible.

3. Pie Chart and Doughnut Chart

Both chart types exist to show a part-to-whole relationship, where every slice should add up to a single, complete 100 percent. The real, functional difference between them is not just the hole in the middle. A pie chart can only ever display a single data series at a time. A doughnut chart can display several data series simultaneously, with each series rendered as its own separate ring, one nested inside the next, which is the genuine reason to reach for a doughnut chart over a pie chart, not simply a stylistic preference for the hollow centre.

The hollow centre of a doughnut chart is not purely decorative either. That empty space can hold a summary figure, a grand total, a key percentage, or a short label, giving the chart a second layer of information without adding visual clutter to the ring itself.

The real limit that applies to both, and the most common mistake beginners make with this chart family. Both pie and doughnut charts read poorly once you push past five or six categories, since the human eye genuinely struggles to compare the precise angle or arc length of many thin, similarly sized slices against each other. If your data has more categories than that, or if several slices are close enough in size that telling them apart by eye becomes genuinely difficult, switch to a bar chart instead, which lets the reader compare bar length directly rather than guessing at angles.

4. Scatter Plot and Bubble Chart

A scatter plot, sometimes called an XY chart, exists to answer a genuinely different question from every chart type covered so far. Rather than comparing categories or tracking a trend over time, it plots two separate numeric variables against each other, one on each axis, to reveal whether a relationship, a correlation, exists between them at all. Each point on the chart represents one individual record, positioned according to its value on both variables simultaneously. If the points generally rise together from left to right, that suggests a positive correlation, if they fall together, a negative correlation, and if the points scatter with no visible pattern, that itself is the genuine finding: no meaningful relationship between those two variables in this dataset.

A bubble chart is a scatter plot with a third variable added. Instead of every point being the same fixed size, the size of each bubble itself encodes a third numeric value, so a single chart can now compare three variables simultaneously: position on the horizontal axis, position on the vertical axis, and bubble size. A genuine, practical example worth understanding rather than memorising: plotting a product's customer rating on one axis against its average days to sell on the other, with bubble size representing its return rate, lets you spot in one glance whether products with low ratings and fast sales cycles also tend to have larger return rate bubbles, a pattern that three separate charts would make far harder to connect visually. The real trap with bubble charts: they only earn their place when all three variables genuinely relate to the same underlying story. Forcing three unrelated numbers into one bubble chart purely because the format looks sophisticated produces a chart with no actual story to tell, just dots of varying size scattered with no coherent pattern.

5. Combo Charts: Combining Two Chart Types

A combo chart plots two or more data series on the same set of axes, using a genuinely different chart type for each series, most commonly a column for one series and a line for another. Select your data, go to Insert tab → Charts group → Insert Combo Chart, and choose a preset such as "Clustered Column - Line on Secondary Axis," or build a custom combination by selecting a different chart type for each individual series inside the dialog.

The genuine reason this chart type exists, not just a way to look busy. Combo charts solve a specific, real problem: when two series being compared sit on dramatically different numeric scales, for example monthly revenue in the hundreds of thousands alongside a profit margin percentage sitting between 0 and 100, plotting both as plain columns on the same axis makes the smaller series nearly invisible, flattened against the bottom of the chart by the much larger one. A combo chart solves this with a secondary axis, a second vertical scale added to the right side of the chart specifically for the series that needs its own range, while the original series keeps using the primary axis on the left. The columns then show the absolute revenue figures clearly, while the line, measured against its own independent secondary scale, shows the percentage trend clearly too, with neither series squashing the other.

6. Chart Elements: Title, Axis Labels, Legend, Data Labels, Gridlines

Click any chart and a small plus icon appears at its top right corner. Clicking it opens the Chart Elements menu, a checklist controlling every major piece of a chart's structure.

Chart Title sits at the top by default, reading "Chart Title" until you click directly into that text box and replace it. A genuinely useful, lesser known technique: rather than typing a fixed title, click the title box, type an equals sign in the Formula Bar, then click a specific cell on your worksheet. The chart title now updates automatically whenever that linked cell's content changes, which is exactly the right move for a dashboard meant to stay current without manual editing every time the underlying period or filter changes.

Axis Titles versus Axis Labels, a distinction worth getting precisely right. Axis labels are the actual category names or numeric values running along each axis automatically, "Jan," "Feb," "Mar" along the bottom, or 0, 50, 100 up the side, generated directly from your underlying data. Axis titles are a separate, optional text element explaining what those labels actually represent, for example adding the word "Month" beneath the category labels, or "Revenue (₦ millions)" beside the numeric ones. Tick the Axis Titles checkbox in Chart Elements to add empty placeholder boxes for both axes, then click into each one and type the actual description.