One of the best ways of visualizing data is through charts. Bar and line graphs are one of the most popular types of charts in Excel. You can easily convert excel tables into graphs or charts. Other popular graphs that you can create in Excel include pie charts, scatter graphs, histograms, and waterfall graphs.
To create a combo graph, your data needs to have at least two variables. For example, you can plot the combo graph of the average rainfall recorded every month of the year alongside the average temperature. In this example, we can plot the rainfall against months as bar graphs. Conversely, we can combine ta a graph of temperature against the same months as a line graph. This will give us two graphs in one, also known as a combo graph.
In this section, we will look at how to combine a bar and line graph in Excel
Step 1: Create a bar Graph
1. Launch the Excel software and enter your data
2. Highlight all the data, including the headers
3. Click the insert tab, then under charts, click the column button and select the clustered column
Step 2: Convert the bars to a line graph
Since we want to have a combination of a bar and a line graph, we need to convert the temperature bars to a line graph. Excel comes with an inbuilt feature to this. Follow these simple steps.
1. Right-click on any Temperature bar and select change series type
2. Doing that will launch a dialog box to change the chart type. Choose a combo, then convert the temperature bars to line with markers.
3. Press Ok, and Excel will automatically populate a combo bar and line graph.
Method 2: Create a combo bar and line graph directly
Rather than creating bar graphs, first then converting one into a line graph. You can create a combo graph directly. Excel has an inbuilt feature that allows users to create a combo graph.
Step 1: Launch Excel and Enter the data you wish to plot. Make sure it has two variables
Step 2: Highlight all the data, including the titles.
Step 3: Click on the insert tab, then click on the Charts drop-down arrow. This will launch a pop-up window with different types of charts.
Step 4: Click on the all charts tab and select ‘combo,’ which is the last option in the list. Now choose your preferred combo graph template on the right column.
Step 5: Press Ok to apply the changes. The graph will auto-populate using the values that you highlighted earlier.
As you can see from the graph above, the rainfall values have been plotted as bars, while the temperature has been plotted as a line graph.
How to interchange bar and line graph
After plotting your combo graph, sometimes you may want to interchange the line and the bar graph. If you want to convert the bar into and line into a bar graph, then follow these simple steps.
1. Open the spreadsheet with your chart
2. Click on chart tools which are located at the top right corner.
3. Under chart tools, click on the design tab
4. Click on Switch Row or Column feature
Once you click the switch column or row button, the data on the X-axis will be plotted on the Y-axis, while the data on the Y-axis will be plotted on the X-axis. This means the line graph will become a bar graph while the bar graph will change to a line graph. However, it will still remain a combo graph.
How to design a combo graph in Excel
There are so many ways that you can design a combo graph, from changing the bar colors legend and adding a title. In this section, I will guide you on how to design your combo graph.
1. How to add a chart title
A chart title helps readers understand easily what the graph is all about without going further into details. The chart title is usually located at the top part of the graph. You can easily change the chart title of a combo graph by double-clicking on the Chart title icon named ‘Chart Title’ and then replacing it with anything of your choice.
2. How to change from clustered column line graph to a clustered column bar graph
A combo graph can be designed in so many ways. However, the most popular is the clustered line graph. The clustered column bar graph is the opposite of the clustered line graph.
If you already have a clustered line graph, you can change it to clustered bar graph under Change graph type >> Combo settings pages. Choose the third option
As you can see from the image above, the line graph appears to be clustered in the form of a bar graph.
How to change the line and bar color on a combo graph in Excel
Changing the color of the bars or the line in a combo graph improves aesthetics. Furthermore, it can be helpful, especially when you want matching colors with your company’s brand.
All you need to do is right-click on the bar or the line in your graph, then select fill. Once you do that, you can choose your favorite color from the palette.
How to edit the legend on a combo graph
A Legend in a graph is like a key on a map. It helps readers to easily study the graph by providing details about the graph. For example, in the combo graph that we have created in this article, the legends show the color for rainfall is blue while the temperature line is brown. It shows the color alongside the title.
To edit the legend, just right-click on it to launch a formatting window. On this window, you can change the legend’s color, add a border and edit other text options.
How to change the combo chart style
Excel comes with various premade templates for all types of graphs. These templates are helpful when one wants to change the style without creating a template from scratch. All you need to do is click on the chart tools, then design and choose your favorite design.
How to create a stacked column and a line chart combined
Let’s say you have three variables of data. In our first example, we had only two variables (Rainfall and Temperature) which we plotted against months of the year. But now we can introduce Humidity as the third variable.
To effectively compare these three variables of data, we have to create a stacked column and line graph. You can do that by following these simple steps.
Step 1: Create data with at least three variables
Step 2: Highlight all the data, including its titles, and click on the insert tab
Step 3: Under the insert tab, navigate to charts and click the launcher arrow pointing down.
Step 4: This will open a window with various types of charts. Click on the all charts tab and navigate down to the combo graph.
Step 5: Under the combo, the graph section, navigate to select the chart type and axis section, then select the stacked column. Once you select the stacked column on the first option, Excel will automatically mark the next variable as a stacked column. The third variable you can leave as a line graph.
Step 6: Click on ok to apply the changes.
As you can see from the graph above, rainfall and temperature have been stacked on the same bars. Humidity, on the other hand, has been plotted as a line graph. That is how to plot a stacked bar and line graph in Excel.
Combo Graph Practice Spreadsheet
How to create a combo graph practice sheet
How to combine a bar and Line Graph in Google Sheets
Google Sheets is a cloud-based Excel alternative. It allows users to edit and collaborate on spreadsheets online in real-time. If you don’t have Microsoft Excel on your computer, you can still use Google sheets to create a combo graph. To access google sheets, you need a Gmail account and a browser on any device.
Step 1: Launch Google Sheets by following this link
Step 2: Click on the Icon that looks like a + to create a new blank sheet.
Step 3: Create the set of data that you wish to plot a combo graph. (Your data needs to have at least two variables. So that one can be plotted as a bar graph, while the other one can be plotted as a line graph.
Step 4: Click on insert, then select chart.
Step 5: Google Sheets will create the most suitable graph based on your data using its algorithm. If the graph that it creates is not a combo graph, don’t worry, as you can still change the graph type. Just click on the three vertical dots and choose edit chart>>chart type, then choose combo graph.
Note that you can use the chart editor to customize your combo graph further.
Final thoughts
In this tutorial, we have learned how to combine a bar and line graph on the same chart in Excel and Google sheets. Combining a graph helps users to compare two or more variables easily on the same graph. Use the practice sheets provided in the tutorial to practice your own combo bar and line graph
Similar Articles
How to Create a Waterfall Graph in Excel- With Examples
How to do a Correlation Graph in Excel- With Examples
How to create a line graph in Excel
How to remove gridlines in excel outside table: 4 easy ways