On the same graph, a Column-Line chart combines a line graph and a column chart. An X-axis is shared by the two charts, but each has a separate Y-axis. A combination chart has two typical applications: when displaying two separate data sets simultaneously and when displaying sets of the same sort of data but with vastly different ranges. In this article, we will show you how to create a column and line chart in Excel.
For the demonstration purpose, we can view the below example where we showed how we managed to combine the column and the line chart in a single plot in the Excel worksheet.
What Is a Combination Chart in Excel?
A Combination Chart is a type of chart in Microsoft Excel that combines two or more chart types into a single chart. This allows you to display different types of data using different visual representations within the same chart.
How to Create Column and Line Chart Combo in Excel: Step-by-Step Procedures
Plotting diverse data sets with various chart styles or axes can be helpful for rapidly and effectively displaying data of various types. In this article, we will show you how to create a column and line chart using the 4 given steps below.
To do so, we will apply the Combo Chart command and later alter the chart type using the Change Chart Type command. In order to avoid any kind of incompatibility issue, try to opt for the Excel 365 edition.
Step 1: Insert the Clustered Combo Chart in the Worksheet
- Firstly, select all the columns from the given data set
- Then go to the Insert tab > Charts group > Combo > Clustered Column Line.
- Right after selecting the command, we can see that there is a chart in the worksheet.
- But the chart is not a proper shape. We need to set the data and the Axis properly for this chart. In order to show the column values and the line
Step 2: Change the Chart Type
In order to accommodate multiple chart elements and axis assignments, we need to change the chart type manually.
- Right-click on the plot area and then from the context menu, select Change Chart Type.
Step 3: Set the Type of Charts and Assign the Secondary Axis
In the next step, we are going to set the type of the chart and set the axis type for each chart element.
- Now from the All Charts option, click on
- Then choose the Clustered Column Chart for the No of cars sold option.
- And also tick on the Secondary Axis option.
- For the Average price of the car option, choose the Line type of chart.
- Press OK after this.
Step 4: Final Form of the Combined Column and Line Chart
The final form of the chart will be revealed after some small tweaking like chart title, axis title, etc.
- After pressing OK we can see that the chart now has both the clustered column and the line type of chart in the worksheet.
- We can add the chart title and the chart axis title to the chart.
- We now have the column chart with the line chart in the same plot area.
Draw an Average Line in the Excel Stacked Plot with Column Chart
We can add an average horizontal line in the worksheet and also show a column chart in the same plot area. Follow the below steps properly.
Steps
- First, you need to Enter the following formula in cell E5:
=AVERAGE($D$5:$D$17)
- Then drag the Fill Handle to cell E16 to get the average of the car price for each month.
- Then select the range of cell D4:E16 and then go to Insert tab > Charts group > Combo > Clustered Column.
- There will be a preview shown and in that preview, we can see how the chart is going to look after the chart type selection.
- We can see that there is an average line showing over the stacked column denoting the average car prices over the months.
Read More: Create Clustered Stacked Column Combo Chart with Lines in Excel
Create a Combo Chart in Excel with Multiple Series Data
In the previous examples, we actually had a single series of data clusters arranged in column configuration and another linear data present in the line form. Now we are going to add multiple series of data in the column configuration and linear data.
For doing this, we can use the below dataset, where we can see the month, the expenses and sales, and the profit values arranged.
Steps
- First, we can select the range of cell B4:E10, then go to insert tab > Chart group > Combo Chart > Clustered Column with Line.
- Right after pressing the button above, we can see that there is a preview showing the chart with two separate columns and with a line.
- Right-click on the chart and then from the context menu select Change Chart Type.
- Then there will be another window where you can set up the chart type and the axis belongs to the chart type.
- In the chart type, select Combo chart and then select a Clustered chart for the
- And then we can set the Line type chart for the Expenses and the Profit
- And put the tick sign for the Profit and Expenses These two values are going to be set on the same axis, which is the secondary axis.
- Click OK after this.
- After clicking OK we can see that the chart now has one column showing the Sales against the month in the primary axis, and two separate lines showing the Profit and Expenses against the Month in the secondary axis.
Read More: How to Combine Two Graphs in Excel
Create a Clustered Chart in Excel with Three Sets of Data
In the previous examples, we actually had a single series of data clusters arranged in column configuration and another linear data present in the line form. Now we are going to 6 series of data in the column configuration and linear data.
For doing this, we can use the below dataset, where we can see the Year, the Expenses and Sales, New Customers, Marketing Costs, and the Website visits values arranged.
Steps
- First, we can select the range of cell B4:H9, then go to the Insert tab > Chart group > Combo Chart > Clustered Column – Line.
- Now click on the plot area and then right-click on the mouse. And then in the context menu, select Select Data.
- After that, in the Select Data Source window, we can switch the Row with
- Click OK after this.
- After clicking OK, we can see that the row column is now switched.
- Now again click on the plot area and then right-click on it.
- Then from the context menu, click on Change the Chart Type.
- Then there will be another window where you can set up the chart type and the axis belongs to the chart type.
- After setting the char type and axis, click OK after this.
- After clicking OK we can see that the chart now has 3 separate columns on the primary axis and three separate lines on the secondary axis.
Tips to Effective Use Column and Line Charts in Excel
Column and line charts are two of the most commonly used chart types in Excel. Here are some tips for effectively using these chart types:
- Choose the right chart type: Column charts are best used for comparing data across categories or time periods, while line charts are best used for showing trends over time.
- Limit the number of data series: Too many data series on a chart can make it difficult to read and interpret. Stick to a maximum of 3-4 data series per chart.
- Label axes and data points clearly: Make sure that both the x-axis and y-axis are labeled clearly and that each data point is clearly labeled as well.
- Use colors wisely: Use colors to highlight important data or to differentiate between data series, but avoid using too many colors that can make the chart overwhelming.
- Minimize clutter: Remove unnecessary gridlines, axis ticks, and chart elements that don’t add value to the chart.
- Use data labels and callouts: Use data labels and callouts to highlight specific data points or to provide additional context for the data.
- Use a consistent format: Use a consistent chart format throughout your presentation or report to make it easy for readers to understand and compare data.
- Test and refine: Always test your chart with a sample audience and refine it based on their feedback to ensure that it effectively communicates your data.
Frequently Asked Questions
What is a Column and Line chart?
Line Chart: A line chart is a type of chart that displays data as a series of points connected by a line. It is commonly used to show trends over time or to compare multiple data sets. In Microsoft Excel, creating a line chart is relatively straightforward.
Below you can see an example of a combined chart that contains both the column and line chart.
Column Chart: A column chart in Excel is used to visually represent data in columns or vertical bars. It is one of the most commonly used chart types in Excel. And it can be useful for a wide range of data analysis tasks.
A column chart in Excel is a commonly used chart type that displays data in columns or vertical bars. It is useful for a wide range of data analysis tasks, including comparing data, showing trends, highlighting differences, identifying patterns, and visualizing data. With Excel’s column chart features, users can customize the chart’s appearance and functionality to fit their specific needs. They can adjust the chart’s formatting, add labels and titles, and apply different colors and styles to the chart’s columns and markers. Excel’s built-in data analysis features can also be used to calculate and display additional information on the chart, such as averages or trends.
What are the Advantages and Disadvantages of a Line Chart?
Advantages:
- Line charts are useful for showing trends and changes over time. As the line connects data points in a series.
- They are easy to read and interpret, even for those who are not familiar with data analysis.
- Line charts can compare multiple data series on the same chart. Making it easy to identify patterns and trends across multiple data sets.
- They are flexible and can display a wide range of data types. Such as continuous data, categorical data, and date/time data.
- They can be easily customized to suit specific needs, with options for changing colors, labels, axes, and more.
Disadvantages:
- Line charts can be misleading if the data is not accurate or if the scale is not appropriate.
- They are not effective for showing data that has a lot of variability or noise, as the line may not accurately represent the data points.
- Line charts can be difficult to read if there are too many data series on the chart or if the data is too complex.
- They do not work well for data that is not continuous, such as data with large gaps or missing values.
- Line charts may not be the best choice for comparing data that has significant differences in magnitude or scale.
What type of Data is presented in a Line Chart?
Line charts are typically used to display and visualize continuous data over time, such as stock prices, temperature changes, or population growth. They can show trends and changes in data that is not necessarily continuous. Such as survey results over time or changes in sales revenue from month to month. Additionally, line charts can be used to compare data sets with similar units of measurement. Such as the performance of different products or the sales of a particular product in different regions. Overall, line charts are useful for visualizing and interpreting data that show trends or patterns over time.
What are the Elements of a Line Chart?
The key elements of a line chart are:
- X-axis: This is the horizontal axis that represents the independent variable or the data category. Such as time, age, or other numerical or categorical data.
- Y-axis: This is the vertical axis that represents the dependent variable or the data values. Such as sales revenue, temperature, or other numerical data.
- Data points: These are the individual points that represent the specific data values for each data category or time period. They are on the chart using the x and y values.
- Line: This is the line that connects the data points on the chart. forming a continuous curve that represents the trend or pattern in the data.
- Legend: This is the key that explains what each line represents. Particularly useful when multiple data series are on the same chart.
- Gridlines: These are the horizontal and vertical lines that run across the chart and help the reader to better understand the data and follow the trend.
- Title: This is the name or description of the chart that typically provides a brief summary of what the chart is displaying.
- Axis labels: These are the labels that provide context to the data points. Indicating the units of measurement and scale for each axis.
Things to Remember
Choose the right chart type: There are many different types of charts available in Excel. Including column charts, bar charts, line charts, pie charts, and more. Choose the chart type that best represents the data you want to display.
Label your data: Make sure your data is clear with titles, axis labels, and data labels where appropriate. This makes it easier for viewers to understand the chart.
Use appropriate scaling: Choose an appropriate scale for your chart to ensure that it is easy to read and understand. Make sure the minimum and maximum values are appropriate for the data you are displaying.
Format your chart: Use formatting options such as colors, fonts, and borders to make your chart visually appealing and easy to read. Use consistent formatting across all charts in a set to ensure they are easily comparable.
Consider using additional features: Excel has many additional features to help you create more complex charts. Such as trendlines, data labels, and error bars. Consider using these features to enhance your charts and make them more informative.
Check your data: Always double-check your data before creating a chart to ensure accuracy. Mistakes in data can lead to misleading charts.
Keep it simple: Avoid cluttering your chart with too much data or too many design elements. Simple charts are often the easiest to read and understand.
Update your chart: If your data changes, make sure to update your chart accordingly. This ensures that your chart always accurately represents your data.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice yourself.
Conclusion
In this article, I’ve covered the step-by-step process of creating a Column and Line chart in Excel, specifically in a combined manner. Additionally, we also showed how you can draw an average line with column values in the chart area. Moreover, Multiple series data with linear data are in a detailed manner. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.
