10 Excel Chart Types and When to Use Them Line Chart: The line chart is one of the most frequently used chart types. Column Chart: Column charts are typically used to compare several items in a specific range. Clustered Column Chart: A clustered column chart can be used if you need to compare.

• Different Types Of Charts In Microsoft Excel
• Different Types Of Charts In Excel
• Types Of Charts In Excel 2007

While creating a chart in Excel, you can use a horizontal line...

...as a target line or an average line.

It can help you to compare achievement with the target.

Just look at the below chart.

You like it, right?

Say 'Yes' in the comment section if you like it.

Let’s say you have an average value which you want to maintain in your sales throughout the year. Or, a constant target which you want to show in a chart for all the months.

In this case, you can insert a straight horizontal line to present that value.

This horizontal line can be a dynamic one which will change its value...

...or a line with a fixed value.

And in today’s post, I’m going to show you exactly how to do this.

I’m gonna share with you that how you can insert a fixed as well as a dynamic horizontal line in a chart.

An average line plays an important role whenever you have to study some trend lines and impact of different factors on trend.

And before you create a chart with a horizontal line you need to prepare data for it.

Before I tell you about these steps let me show how I am setting up the data.

Here I am using a dynamic chart to show you that how this will help you to make your presentation super cool.

Download this dynamic data table from here to follow along.

In above data tables, I am getting data from raw table to the dynamic table by using a VLOOKUP MATCH.

Every time when I change the year in the dynamic table it will automatically change the sales values and the average will be calculated on those sale figures.

Below are the steps you need to follow to create chart with an horizontal line.

• First of all, select the data table and insert a column chart.
• Go To Insert ➜ Charts ➜ Column Charts ➜ 2D Clustered Column Chart. or you can also use Alt + F1 to insert a chart.
• So now, you have a column chart in your worksheet like below.

• Next step is to change that average bars into a horizontal line.
• For this, select the average column bar and Go to → Design → Type → Change Chart Type.

• Once you click on change chart type option, you’ll get a dialog box for formatting.
• Change the chart type of average from “Column Chart” to “Line Chart With Marker”.

• Click OK.

Here is your ready to rock column chart with a average line and make sure to download this sample file from here.

One of my colleagues uses this same method to add a median line.

You can also use this method to add an average line in a line chart. Steps are totally same, you just have to insert a line chart instead of a column chart. And you will get something like this.

This is one more method which I often use in my charts is adding a target line.

There are several other ways to create a Target Vs. Achievement chart, but target line method is simple & effective.

First of all, let me show you the data which I am using to create a target line in the chart.

Different Types Of Charts In Microsoft Excel

I have used above table to get the target and actual figures from the month wise tables and make sure to download sample file from here.

Now let’s start with the steps.

• Select the dynamic table which I have mentioned above.
• Insert a column chart. Go To Insert → Charts → Column Charts → 2D Clustered Column Chart.
• You’ll get a chart like below.

• Now, you have to change the chart type of target bar from ​Column Chart to Line Chart With Markers​. To change the chart type please use same steps which I have used in the previous method.
• After changing chart type your chart will look something like this.

• Now, we have to make some changes in this line chart.
• After that, make a double click on the line to open formatting option. Once you do that, you’ll get a formatting option dialog box.
• Make following changes in formatting.
1. Go to → Fill & Line → Line.
2. Change line style to “No Line”.

• Now, go to marker section and make following changes.
1. Change marker type to Built-In, a horizontal bar, and size 25.
2. Marker fill to solid fill.
3. Use white color as the fill color.
4. Make border style to a solid color.
5. And, black color for borders.

• Once you make all the changes to the line you’ll get a chart like I have below.

Congratulations! your chart is ready.

Download this sample file from here to follow along

Conclusion

So that’s the whole story about a horizontal line.

And, now if someone asks you “how do I add a horizontal line to an excel chart?” you can show them two different methods and also help them to understand why it’s important.

I'm sure this technique will help you to get better at charting.

Now tell me one thing. Have you ever added a horizontal line in a chart? Or, do you have an alternate method for this? Please share with me in the comment section, I would love to hear from you.

And, please don’t forget to share this tip with your friends.

Must Read Next

1. How to Create a Dynamic Chart Title in Excel
   

Different Types Of Charts In Excel

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

LinkedIn - YouTube

Charts help you visualize numeric data in a graphical format but the problem is there are just too many types of charts to choose from. You have bar charts, bubble charts, pie charts, line histograms and so on.

Types Of Charts In Excel 2007

If you are finding it hard to pick the right chart type for your type of data, refer to chart chooser diagram. Start from the center of the chart chooser diagram and take the route that best matches your data type. Is the data static or does it change over time? Does the chart show a comparison or relationship between data? You may sometimes have to draw more than one chart.

The poster, designed by Andrew Abela, is also available as a PDF.

Related: Create Graphs Online with Google Charts

You may also want to check out Chart Chooser – an online tools that lets you shortlist charts visually. Chart Chooser is not as detailed as the above chart suggestion flowchart but the advantage is that it lets you download different chart types in a format that is ready to use inside Excel or Microsoft PowerPoint.