Area Chart in Excel – Types, Inserting, Line vs Area, Correcting Chart

In one of our earlier blogs, we understood the usage of line charts in excel. In the line charts, the consecutive data points join to form a line. The functionality of the Area chart in excel is quite similar to it. Let us see how. 😎

Area chart and its Types

Area charts are nothing but line charts, in which the area between the lines (data series) and the category axis (horizontal axis) is filled with legend color.

There are three types of area charts. (just like the line chart)

  • Area chart. (2D as well as 3D)
  • Stacked Area chart (2D as well as 3D)
  • 100 % Stacked Area chart (2D as well as 3D)

The three chart types have their 3D versions also, but they work the same as the 2D charts.

Why do we need Area Charts

The question that arises is, what is the point in filling the area between the data series (lines) and bottom axis? Well, there are some cases in which the analysis of the entity happens on the basis of the area it covers in the chart.

  • The area under Velocity-Time graph gives displacement. This is so because displacement = velocity*time
  • Area under the Acceleration-Time graph gives velocity.

In both of the examples, we would study the change of velocity or acceleration with increasing time. We can also use the area charts to form a special type of gridlines to make a combo chart.

special gridlines using area chart of excel

This is a very cool formatting feature and you must check it out!

infographics Area Chart in Excel

Inserting Area Chart in Excel

Let us say we went on a trip. The journey to reach the destination was of 8 hours. We have the data of the speed of the car after certain hours.

inserting area chart in excel raw data

As you can see, initially both time and speed are zero as the journey has not started when time is zero and the car is at rest. Then there are the average speeds after 1 hour, after 2 hours, and so on.

To insert an area chart in excel:-

  • Select the data range A1:B10
  • Go to insert tab
  • In the charts group, click on recommended charts button
  • Move to all charts tab
  • Choose the area chart from the menu there.
inserting area chart in excel step 2

This is going to insert a default area chart in excel. We made some changes as follows.

This is how the chart looks now.

inserting area chart in excel result

Comparing Line Chart and Area Chart (Multiple Data Series)

Let us say I have the report of the targetted number of projects for different employees as follows.

comparing line chart and area chart in excel

We can insert both the line chart and the area chart for this type of data as explained before.

In a line chart, the data points are plotted on the chart and consecutive points of a series are joined to form a line. In an area chart, the area between these lines and the bottom axis is filled. These areas overlap if there is more than one data series as in this case i.e Harsh, Sumit, Radha, Kamla

The consecutive data series can be seen overlapping as:

  • The data series for Harsh can be seen on the top in orange color.
  • “Sumit” overlaps “Harsh” as grey covers a part of orange.
  • “Radha” overlaps both “Sumit” and “Harsh”.
  • “Kamla” is in front so it overlaps all three series. We can see the complete area occupied by “Kamla” because it is on the front and no data series overlaps it.

Now there is one disadvantage of this overlapping as the next data series covers the previous data series to some extent. Now, look at this variation where we changed the data of Radha and Kamla for 2022.

disadvantage of area chart of excel

However, this is a case when we have multiple data series in the chart.

In this case, usage of Stacked Area Charts becomes efficient.

Correcting an Area Chart in Excel

We are assuming that you know the basic terminology of a chart in excel, and if you don’t then please read the fundamentals of charts in Microsoft excel.

As we know when we select the data, excel inserts a selected chart type with some default settings including:-

  • Data Series on the chart to be plotted.
  • What would horizontal and vertical axis represent.
  • Axis labels on the axis of chart.

There might happen a case when excel doesn’t recognize the data you provide to it. and the chart becomes inefficient to read. For instance, look at this area chart.

correcting a chart in Microsoft excel

Here you can see that there is a problem with this chart. It is completely different from the chart we inserted earlier. To correct it:-

  • Right click on the chart
  • Click on Select Data option from the shortcut menu
correcting a chart in Microsoft excel step 2

Consequently, using the above controls you can alter the default setup of any chart in Microsoft excel.

Thank you for reading. πŸ˜‰

Leave a Comment