Ravindra BagaleCourses & study guides मराठी Track your progress

8. Charts

Come friends, today we make numbers speak – with charts. Which chart for which question, how to format it, and how to build dynamic charts that change with a drop-down or a checkbox – all today. Remember: a chart is not there to look pretty; it is there to make the message clear.

What you will learn in this module

  • Creating charts and using Chart Elements, Styles, Filters and the Format pane
  • Column, bar, line, area, pie/doughnut, combo with secondary axis, scatter
  • Histogram, box & whisker, waterfall, funnel, treemap, sunburst, map chart and sparklines
  • A choose-the-right-chart table
  • Dynamic charts: Table-based, drop-down + INDEX/XLOOKUP driven, OFFSET named ranges, checkbox show/hide

Live story: One slide, one message

Suppose Shraddha Bagale has one slide for Maharashtra in the national review. The message is: "Solapur grew fastest, but Pune is still the biggest." A pie chart with six thin slices hides that message, and a 3-D column chart makes it worse. This chapter picks the right chart for each question, formats it cleanly and makes it change with a drop-down.

All numbers in this story are fictional practice data.

Chart data used in this module (fictional monthly sales, ₹ lakh, sheet ChartData):

About this data. The data on this page for "8. Charts" is a made-up teaching sample. The names, shops, dates and numbers in the table are fictional. Any business name here is fictional and not linked to a real company.
Month Pune Nashik Nagpur Orders (Pune)
Jul 3.9 1.4 1.6 12,150
Aug 4.1 1.5 1.7 12,900
Sep 5.9 2.0 2.1 17,600
Oct 5.0 1.6 1.8 15,200
Nov 6.1 2.2 2.5 18,400
Dec 4.6 1.7 1.9 14,100

September (Ganeshotsav) and November (Diwali) are the festival peaks.

Concepts in this chapter

  1. 8.1Creating and Formatting a Chart
  2. 8.2Column and Bar Charts
  3. 8.3Line and Area Charts
  4. 8.4Pie and Doughnut Charts
  5. 8.5Combo Chart with a Secondary Axis
  6. 8.6Scatter Chart
  7. 8.7Histogram and Box & Whisker
  8. 8.8Waterfall and Funnel
  9. 8.9Treemap and Sunburst
  10. 8.10Map Chart
  11. 8.11Sparklines
  12. 8.12Choosing the Right Chart
  13. 8.13Dynamic Chart from a Table
  14. 8.14Drop-down Driven Dynamic Chart (INDEX / XLOOKUP)
  15. 8.15Named Ranges with OFFSET (Rolling Charts)
  16. 8.16Checkbox to Show/Hide a Series

The chapter recap is at the end of the last concept page.

Quick revision cards

Quick revision

Revise this chapter in two minutes. Save the cards on your phone or download them as PNG files. All names and numbers are made up for practice.

Revision card: Which chart?. Covers Compare cities; Trend over months; Share of a whole (few parts); Two measures, different scales; Do two numbers move together?; How values spread; Start-to-end change. With a photo of Ravindra Bagale and a short tip.
Card 1 of 2: Which chart? · Download PNG
Revision card: Charts: drop-down chart, sparklines, growing data. Example data table with Helper row for a drop-down chart: 90, 95, 110; Sparkline for Pune: rising line; Chart that grows with new months: auto-extends.
Card 2 of 2: Charts: drop-down chart, sparklines, growing data · Download PNG

More cards for every chapter: Ravi Sir's Cards.