Combo Histogram and Waterfall Charts Practice Questions with Solutions

Introduction

Excel provides many chart types for different kinds of data. In this chapter, you will practice Combo Charts, Histograms, Pareto Charts, and Waterfall Charts. These charts are useful for comparing different measurements, understanding data distribution, finding frequently occurring problems, and analyzing increases and decreases in financial data. Every question below uses complete Excel data so you can directly enter the data into Excel and practice. Combo, Histogram and Waterfall Charts Practice Questions with Solutions to help you understand the concepts.

Question 1: Create a Combo Chart for Monthly Sales and Profit

Problem Statement

A company wants to compare its monthly sales and profit. Create a Combo Chart where Sales are shown using columns and Profit is shown using a line.

Excel Data

Excel Solution

1. Select A1:C9.
2. Go to Insert.
3. Select Combo Chart.
4. Choose Clustered Column – Line.
5. Set Sales as the Column series.
6. Set Profit as the Line series.
7. Add the chart title:

Monthly Sales and Profit

Expected Result

The chart should contain:

• Columns → Sales
• Line → Profit
• Horizontal Axis → Months

The chart allows you to compare Sales and Profit together.

Concepts Covered

• Combo Chart
• Column Chart
• Line Chart
• Multiple data series
• Chart title

Question 2: Create a Combo Chart Using a Secondary Axis

Problem Statement

Create a Combo Chart showing monthly Sales as columns and Profit Percentage as a line. Because Sales and Profit Percentage have very different scales, use a Secondary Axis for Profit Percentage.

Excel Data

Excel Solution

1. Select A1:C9.
2. Go to Insert → Combo Chart.
3. Select Custom Combination Chart.
4. Set Sales to Clustered Column.
5. Set Profit % to Line.
6. Enable Secondary Axis for Profit %.
7. Add the chart title:

Sales and Profit Percentage

Expected Result

The chart should show:

• Sales → Columns using the primary axis
• Profit % → Line using the secondary axis

Concepts Covered

• Combo Chart
• Secondary Axis
• Percentage data
• Different data scales
• Custom Combination Chart

Question 3: Create a Histogram for Student Marks

Problem Statement

A teacher has collected marks from 30 students. Create a Histogram to understand how the marks are distributed.

Excel Data

Excel Solution

1. Select B1:B31.
2. Go to Insert.
3. Select Insert Statistic Chart.
4. Choose Histogram.
5. Add the chart title:

Student Marks Distribution

How the Histogram Works

A Histogram groups numerical values into ranges called bins.

For example:

Marks
  │
  │          ███
  │      ███ ███ ███
  │  ███ ███ ███ ███
  │  ███ ███ ███ ███ ██
  └────────────────────────
    40  50  60  70  80  90
             Marks

The height of each column represents the number of students in that range.

Expected Result

The Histogram should show how many students fall into different mark ranges.

Concepts Covered

• Histogram
• Data distribution
• Frequency
• Bins
• Statistical Chart

Question 4: Create a Histogram With a Specific Bin Width

Problem Statement

A company wants to analyze employee salary distribution. Create a Histogram and set the Bin Width to ₹10,000.

Excel Data

Excel Solution

1. Select B1:B26.
2. Go to Insert → Insert Statistic Chart → Histogram.
3. Right-click the horizontal axis.
4. Select Format Axis.
5. Find the Bins section.
6. Select Bin Width.
7. Enter:

10000

8. Add the chart title:

Employee Salary Distribution

Expected Result

The salary values should be grouped into ranges such as:

Concepts Covered

• Histogram
• Bin Width
• Salary Distribution
• Frequency
• Format Axis

Question 5: Create a Pareto Chart for Customer Complaints

Problem Statement

A company has received different types of customer complaints. Create a Pareto Chart to analyze which types of complaints occur most frequently.

Excel Data

Excel Solution

1. Select A1:B8.
2. Go to Insert.
3. Select Insert Statistic Chart.
4. Choose Pareto.
5. Add the chart title:

Customer Complaints Analysis

How a Pareto Chart Works

A Pareto Chart combines two things:

Complaint Frequency
  │
  │ ███
  │ ███
  │ ███ ███
  │ ███ ███ ███
  │ ███ ███ ███ ██
  └────────────────────
    Complaint Categories

    ─────────────────●
      Cumulative %

• Columns → Number of complaints
• Line → Cumulative percentage

Expected Result

The complaint with the highest number of occurrences should appear first, followed by the other complaint categories.

Concepts Covered

• Pareto Chart
• Frequency Analysis
• Cumulative Percentage
• Customer Complaint Analysis

Question 6: Create a Pareto Chart for Product Defects

Problem Statement

A manufacturing company records different types of product defects. Create a Pareto Chart to analyze the defects.

Excel Data

Excel Solution

1. Select A1:B8.
2. Go to Insert → Insert Statistic Chart.
3. Select Pareto.
4. Add the chart title:

Product Defect Analysis

Expected Result

The chart should display:

• Defect categories as columns
• Cumulative percentage as a line

Concepts Covered

• Pareto Chart
• Quality Analysis
• Defect Analysis
• Frequency
• Cumulative Percentage

Question 7: Create a Waterfall Chart for Company Profit

Problem Statement

A company starts with a profit of ₹500,000. Additional sales increase the profit while different expenses reduce it. Create a Waterfall Chart to show these changes.

Excel Data

Excel Solution

1. Select A1:B9.
2. Go to Insert.
3. Select Waterfall or Stock Chart.
4. Choose Waterfall.
5. Right-click Starting Profit.
6. Select Set as Total.
7. Right-click Final Profit.
8. Select Set as Total.
9. Add the chart title:

Profit Movement Analysis

How a Waterfall Chart Works

A Waterfall Chart shows how a starting amount changes:

Starting Profit
       │
       ↓
 Additional Sales
       ↑
 Marketing Expense
       ↓
 Salary Expense
       ↓
 Rent Expense
       ↓
 Electricity
       ↑
 Other Income
       ↓
 Final Profit

Positive values increase the running amount, while negative values decrease it.

Expected Result

The chart should start with the Starting Profit and show every increase and decrease until it reaches the Final Profit.

Concepts Covered

• Waterfall Chart
• Starting Value
• Positive Values
• Negative Values
• Total Values
• Financial Analysis

Question 8: Create a Waterfall Chart for Monthly Cash Flow

Problem Statement

A company wants to analyze its monthly cash movement. Create a Waterfall Chart showing how different cash inflows and outflows affect the opening balance.

Excel Data

Excel Solution

1. Select A1:B10.
2. Go to Insert → Waterfall or Stock Chart.
3. Select Waterfall.
4. Right-click Opening Balance.
5. Select Set as Total.
6. Right-click Closing Balance.
7. Select Set as Total.
8. Add the chart title:

Monthly Cash Flow

Expected Result

The Waterfall Chart should show how the Opening Balance changes after each cash inflow and outflow until it reaches the Closing Balance.

Concepts Covered

• Waterfall Chart
• Cash Flow
• Income
• Expenses
• Opening Balance
• Closing Balance

Question 9: Create a Combo Chart for Revenue, Expenses and Profit

Problem Statement

A company wants to compare Revenue, Expenses, and Profit in one chart. Create a Combo Chart where Revenue and Expenses are columns and Profit is a line.

Excel Data

Excel Solution

1. Select A1:D9.
2. Go to Insert → Combo Chart.
3. Select Custom Combination Chart.
4. Set Revenue to Clustered Column.
5. Set Expenses to Clustered Column.
6. Set Profit to Line.
7. Add the chart title:

Revenue, Expenses and Profit

Expected Result

The chart should contain:

• Revenue → Column
• Expenses → Column
• Profit → Line

This allows you to compare all three measurements in one chart.

Concepts Covered

• Combo Chart
• Multiple Data Series
• Column + Line Chart
• Business Reporting

Question 10: Choose the Correct Chart for Different Data

Problem Statement

Different types of data require different chart types. Use the complete datasets below and create the appropriate chart for each one.

Dataset A: Monthly Sales and Profit

Correct Chart: Combo Chart

Dataset B: Student Marks

Correct Chart: Histogram

Dataset C: Customer Complaints

Correct Chart: Pareto Chart

Dataset D: Financial Changes

Correct Chart: Waterfall Chart

Excel Solution

Create each chart separately.

Expected Result

You should create four different charts:

• Dataset A → Combo Chart
• Dataset B → Histogram
• Dataset C → Pareto Chart
• Dataset D → Waterfall Chart

The important part of this question is recognizing which chart is appropriate for the type of data.

Concepts Covered

• Chart Selection
• Combo Chart
• Histogram
• Pareto Chart
• Waterfall Chart
• Data Visualization

Key Takeaways

• Combo Charts combine different chart types in one chart.
• Secondary Axis is useful when two data series have very different scales.
• Histograms show the distribution of numerical data.
• Bin Width controls the size of ranges in a Histogram.
• Pareto Charts show category frequency along with cumulative percentage.
• Waterfall Charts show how positive and negative values change a starting amount.
• Combo Charts are useful for comparing different measurements.
• Histograms are useful for understanding data distribution.
• Pareto Charts are useful for analyzing complaints, defects, and other problems.
• Waterfall Charts are useful for financial and sequential value analysis.
• Choosing the correct chart is an important Excel data-analysis skill.

FAQs

1. What is a Combo Chart in Excel?

A Combo Chart combines two or more chart types in a single chart. For example, Sales can be displayed as columns while Profit is displayed as a line.

2. What is a Secondary Axis?

A Secondary Axis provides a separate scale for one data series. It is useful when two datasets have very different numerical values.

3. What is a Histogram in Excel?

A Histogram groups numerical values into ranges called bins and shows how frequently values occur in each range.

4. What is Bin Width?

Bin Width determines the size of each range in a Histogram.

For example, a Bin Width of 10 can create ranges such as:

• 0–10
• 10–20
• 20–30

5. What is a Pareto Chart?

A Pareto Chart combines columns and a cumulative percentage line. It is useful for analyzing problems, complaints, defects, and other frequency-based data.

6. What is a Waterfall Chart?

A Waterfall Chart shows how a starting value changes through a series of positive and negative values until it reaches a final value.

7. Where can Waterfall Charts be used?

Waterfall Charts can be used for:

• Profit analysis
• Cash flow
• Budget analysis
• Revenue changes
• Expense analysis
• Financial reports

8. Can a Combo Chart use a Secondary Axis?

Yes. A data series can be placed on a Secondary Axis when its scale is significantly different from another series.

9. What is the difference between a Histogram and a Bar Chart?

A Bar Chart compares separate categories, while a Histogram groups numerical values into continuous ranges.

10. What is the purpose of a Pareto Chart?

A Pareto Chart helps you analyze the frequency of different categories and see their cumulative contribution.

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar