While working with Microsoft Excel data, we frequently use graphs in excel. A graph is a visualization of data that uses pictures to express the information, such as bars in a bar chart or line graph. A graph is considerably simpler to display comparisons and patterns and lets viewers understand the significance of the statistics. We can change the type of graphs and also combine different types of graphs in one data representation. In this article, we will demonstrate 2 suitable ways to combine a bar and a line graph in Excel.
Download Practice Workbook
You can download the workbook and practice with them.
2 Suitable Ways to Combine Bar and Line Graph in Excel
We may use graphs in Excel to visually convey information. People can absorb and recall information more easily with the aid of graphs. Many individuals comprehend images more rapidly than long passages of text. To combine bar and line graphs, we are going to use the following dataset. The dataset contains some months, total unit sales of each month, and the total amount of sales for each particular month.
1. Add Secondary Axis to Combine Bar and Line Graph in Excel
By combining graphs we may display and contrast two distinct data sets that are connected to one another in a single graph. We can easily combine bar and line graphs by adding a secondary axis in excel. Let’s follow the procedures to use a secondary axis for combining bar and line graphs.
- Firstly, select the data range that we wish to use for the graph. In our case, we select the whole data range B5:D10.
- Secondly, go to the Insert tab from the ribbon.
- Thirdly, click on the Insert Column or Bar Chart drop-down menu, under the Charts category.
- Then, select the Clustered Column under the 2-D Column. We use the Clustered Column when the order of categories is not important.
Note: It really doesn’t matter whatever form of a chart that begins with, but if you are dealing with several sets of data, I would recommend choosing the type of chart that corresponds to the bulk of the information because it will save time later.
- Now, we have a graph of two datasets. One is Unite Sale another is Total Sales and both graphs use the same chart type.
- Click the graph’s Total Sales data column. Avoid clicking the legend’s description of Total Sales. Instead, click one of the chart’s Orange bars. And, you will see that the orange bar is now highlighted.
- Subsequently, go to the Chart Design from the ribbon. The Chart Design will appear after selecting the bar chart.
- Further, in the Type group, click on Change Chart Type.
- This will display the Change Chart Type dialog box.
- At this moment, go to the All Chart menu.
- Consequently, choose Combo which is the last chart type in the list.
- In the Choose the chart type and axis for your data series, we change the Total Sales chart type. For this, click on the drop-down menu and select LIne.
- Furthermore, click on the OK button to close the Change Chart Type dialog box.
- You will now be able to see the Total Sales line graph but the Unit Sale bar is now visible. To show the Unit Sale column in a Bar graph, we need to add a secondary axis. For this, click on the line graph.
- Thus, the Format Data Series window will display on the right side of the Excel sheet.
- Now, click on the Series Option and select Secondary Axis.
- Finally, That’s it! You may make even more complicated but understandable charts by continuing the procedures described in this article with other data sets.
2. Use Custom Combo Bar Chart Tool to Combine Excel Graphs
You may display many types of data on a single chart with custom combo charts. This represents the same mixture chart, columns, lines, regions, and phases. Utilize them to graphically demonstrate the contrasts between various data sets. Combo graphs combine two or more different chart styles to make the information easier to interpret. Let’s illustrate the instruction to use the custom combo chart.
- Likewise the previous method, first, select the whole dataset.
- In the second place, go to the Insert tab from the ribbon.
- After that, in the Chart group, click on the Insert Combo Chart drop-down menu.
- Select Create Custom Combo Chart.
- This will open the Insert Chart dialog box.
- Now, go to the All Charts option and select the Combo option from the list.
- Then, choose any combination of charts, in our case, we select the second chart option.
- This automatically converts the Total Sales column into a Line graph.
- If we take a close view, we can see that the Secondary Axis checkbox is checked.
- Lastly, to close the Insert Chart window, click on the OK button.
- Finally, we can see the bar and the line graph are not combined into one single chart.
Things to Keep in Mind
While using the amount in a chart, we have to change the format for the particular column. For this,
- In the first place, select the amount series.
- Then, right-click on your mouse.
- Further, click on the Format Axis.
- This will display the Format Axis window on the right side of the Excel sheet.
- Now, from Axis Options, select Currency under the Category drop-down option of the Number menu.
- And, that’s it!
The above methods will assist you to Combine a Bar and a Line Graph in Excel. Hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback. Or you can glance at our other articles in the ExcelDemy.com blog!
- Make a Line Graph in Excel with Multiple Lines (4 Easy Ways)
- How to Make a Line Graph in Excel with Multiple Variables
- Add a Vertical Dotted Line in Excel Graph (3 Easy Methods)
- How to Make a Double Line Graph in Excel (3 Easy Ways)
- Add a Marker Line in Excel Graph (3 Suitable Examples)
- Draw a Horizontal Line in Excel Graph (2 Easy Ways)
- How to Make a Single Line Graph in Excel (A Short Way)