Combination Charts (Bar + Line, etc.) in Excel
Combination charts in Excel allow you to display multiple types of charts in a single chart area, such as combining a bar chart and a line chart. These charts are useful when you want to compare two or more data sets with different ranges of values but within the same axis. This tutorial will guide you through creating combination charts with real-time examples.
Step-by-Step Tutorial
Step 1: Prepare Your Data
Start by organizing your data in Excel. Here's an example where we have sales data and profit data for each month:
| Month | Sales | Profit |
|-------|-------|--------|
| Jan | 150 | 30 |
| Feb | 180 | 35 |
| Mar | 200 | 40 |
| Apr | 220 | 45 |
| May | 250 | 50 |
| Jun | 270 | 55 |
| Jul | 300 | 60 |
| Aug | 320 | 65 |
| Sep | 340 | 70 |
| Oct | 360 | 75 |
| Nov | 380 | 80 |
| Dec | 400 | 85 |
In this example, the "Sales" data is on the primary axis (left), and "Profit" will be represented using a secondary axis (right) since the values are different in scale.
Step 2: Insert a Basic Chart
1. Highlight the entire data range (A1:C13).
2. Go to the "Insert" tab in the ribbon.
3. In the "Charts" group, choose "Column" and select a "Clustered Column" chart.
This will insert a simple bar chart displaying the sales data for each month.
Step 3: Convert to Combination Chart
To combine the bar chart with a line chart:
- Click anywhere on the chart to select it.
- In the ribbon, go to the "Chart Tools Design" tab, and click "Change Chart Type".
- In the "Change Chart Type" dialog box, under "Combo", you will see options to combine different chart types.
- For the "Sales" data, select "Clustered Column" (the default). For the "Profit" data, select "Line".
- Check the box next to "Secondary Axis" for the "Profit" data to place it on a different axis.
- Click "OK" to apply the changes.
Step 4: Customize Your Combination Chart
You can further customize your combination chart to make it more readable and visually appealing:
- Click on the chart title to edit it, for example, "Sales and Profit Comparison".
- Click on the "Legend" to move it or change its position.
- If needed, you can adjust the axis by right-clicking on either the primary or secondary axis and selecting "Format Axis" to change the scale, units, or style.
Step 5: Format the Line Chart
To make the line chart stand out:
- Click on the "Profit" line in the chart.
- Right-click and choose "Format Data Series".
- Change the line style, color, or markers for better visibility. For example, you can change the line color to blue and make it a dashed line for contrast.
Real-Time Examples
Example 1: Sales vs Profit
Scenario: You have monthly sales and profit data, and you want to compare both metrics visually. Using a combination of clustered columns (for sales) and a line (for profit), you can create a chart that shows both metrics with different scales.
Resulting Chart: A combination chart with sales data represented by bars and profit data represented by a line, with two different y-axes.
Example 2: Comparing Revenue and Expenses
Scenario: You are comparing monthly revenue and expenses, which are in different units. The revenue is displayed as a bar chart, while the expenses are displayed as a line chart, making it easy to spot trends and differences between the two data sets.
| Month | Revenue | Expenses |
|-------|---------|----------|
| Jan | 1500 | 500 |
| Feb | 1800 | 600 |
| Mar | 2000 | 700 |
| Apr | 2200 | 750 |
| May | 2500 | 800 |
| Jun | 2700 | 850 |
| Jul | 3000 | 900 |
| Aug | 3200 | 1000 |
| Sep | 3400 | 1100 |
| Oct | 3600 | 1200 |
| Nov | 3800 | 1300 |
| Dec | 4000 | 1400 |
Resulting Chart: A combination chart with revenue as bars and expenses as a line, which gives a clear visual comparison of both data sets with different units.
Example 3: Temperature and Humidity Comparison
Scenario: You have monthly data on temperature and humidity levels, and you want to show both data sets in one chart. The temperature is represented as a bar chart, while humidity is represented as a line chart with a secondary axis.
| Month | Temperature (°C) | Humidity (%) |
|-------|------------------|--------------|
| Jan | 10 | 60 |
| Feb | 12 | 58 |
| Mar | 15 | 65 |
| Apr | 18 | 70 |
| May | 22 | 75 |
| Jun | 25 | 80 |
| Jul | 30 | 85 |
| Aug | 28 | 90 |
| Sep | 20 | 70 |
| Oct | 15 | 65 |
| Nov | 12 | 60 |
| Dec | 10 | 55 |
Resulting Chart: A combination chart with temperature displayed as bars and humidity as a line, providing a clear visual comparison between these two variables.
Tips for Using Combination Charts in Excel
- Use combination charts when you need to compare two different types of data that have different scales.
- Ensure that one data series is clearly represented as a bar (or column) and the other as a line to improve clarity.
- Always use the secondary axis for the data series with a different scale to avoid confusion.
- Customize the chart by adjusting the colors, lines, and styles to make the chart more visually appealing and easier to read.
Conclusion
Combination charts in Excel are a powerful way to visualize multiple types of data in a single chart. By combining bar charts and line charts, you can compare different data sets that have different units or scales. This technique enhances data presentation and makes it easier to interpret and analyze the information at a glance.