How to Insert Proportional Area Chart in Excel (4 Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

Charts are useful tools that can be used for representing data in an Excel worksheet. We can also use Area Charts to display the data charts in a proportional way. This article is going to show how you can insert data in a Proportional Area Chart in Excel.


How to Insert Proportional Area Chart in Excel (With Easy Steps)

First, We have to take a dataset of 4 different items. Then, we will modify the data according to our requirements.

After that, we need to remodel the modified data so that we can insert that in an Area Chart.


Step 1: Dataset for Proportional Area Chart

We have taken a dataset of 4 different fields and their land areas. The purpose of this article is to present them in a 2-D Chart in square shapes.

Taking Dataset for Proportional Excel Area Chart

Read More: How to Create an Area Chart in Excel


Step 2: Data Modification

As mentioned earlier, we are going to present the field area in square shapes. For that, we have to calculate the equivalent Arm Length of each square. For that, we will use the SQRT function.

  • So, Add another Row just below the dataset.

Adding a Row to Modify Dataset for Proportional Excel Area Chart.

  • Then, write the following formula in C10 and Press Enter.

=SQRT(C5)

This SQRT() function finds the Square Root of a given number.

Inserting Square Root Formula to Find Arm Length

So, we get the Arm Length for Field A and which is 10.

  • Now, Hold and Drag the C10 cell rightwards to Copy the formula.

Calculating Arm Length for Proportional Excel Area Chart

Thus, we have got Arm Length for each Field.

Finding Arm Length for All Fields


Step 3: Remodelling Data

We have to insert the Field Area in a 4-coordinated system. Already we have found the Arm Length which will be used as coordinates. Now these coordinates can be both positive and negative.

  • Add a new data table like the one given below.

Adding New Data Table for Remodelling

As we have to create a square, the Arm Length has to be taken as both X Values and Y Values.

We have to take two X Values negative and the other two positive and also have to include 0.

So, we have taken -15, -10, 0, 12 and 13 sequentially.

Inserting X Values for Proportional Excel Area Chart

All the squares are connected to point (0,0). Field A is extended from 0 to -10. And we assume that it’s in the 2nd Coordinate.

  • So, insert positive 10 in C11 and C12.

: Inserting Y Values for Field A for Proportional Excel Area Chart

We want Field B to be in the 1st Coordinate.

  • So, write the Arm Length 12 in D12 and D13.

Inserting Y Values for Field B for Proportional Excel Area Chart

Similarly, we want Field C to be in the 4th Coordinate.

  • So, Write -13 in E12,E13 and E14.

Inserting Y Values for Field C

And, Field D is to be placed in the 3rd Coordinate.

  • So, Write -15 in F12, F11 and F10.

Inserting Y Values for Field D for Proportional Excel Area Chart

And, we have remodeled the dataset.

Read More: Excel Stacked Area Chart Negative Values


Step 4: Creating Proportional Area Chart

Now, let’s insert this remodelled data in an Area Chart.

  • First, Select the data range (C9:F14).

Copying Y Values for Proportional Excel Area Chart

  • Then, go to the Insert tab from the Insert Line or Area Chart section Select 2-D Area chart.

Inserting 2-D Excel Area Chart

  • A chart like this will appear.

Initial Outlook of the 2-D Chart

  • Now, we have to set the X Values.
  • So, place your mouse on the chart and Click right.
  • A window like this will appear.
  • Click on Select Data.

Selecting Data for Proportional Excel Area Chart.

  • After that, Select the Edit option in the Horizontal (Catagory) Axis Labels section.

Editing Horizontal Axis Labels for Proportional Excel Area Chart

A new window Axis label range will appear.

Window for Axis Label Range

  • Now, Select X Values range (B10:B14) and Press OK.

Selecting X Values as Horizontal Axis Labels for Proportional Excel Area Chart

  • And, in the previous window, Press OK.

Completion of Editing Horizontal axis labels

Your chart will still look the same. But don’t worry.

  • Double Press on any color.

Outlook of 2-D Chart

And, this new window will appear.

  • Change the Series Options from Primary Axis to Secondary Axis.

Changing Series Options From Primary Axis to Secondary Axis

  • And, again Change the Series Options from Secondary Axis to Primary Axis.

Changing Series Options from Secondary Axis to Primary Axis

 

  • Thereby, your data will be plotted as a Proportional Area Chart.

Final Proportional Excel Area Chart

Read More: Smooth Area Chart Excel


Practice Section

You can download the practice workbook where you can practice yourself.

Practice Section for Proportional Excel Area Chart


Download Practice Workbook

You can download and practice this Workbook.


Conclusion

We have shown you how to insert a proportional Area Chart using 4 data. If you have to compare more than 4 data this method will not be useful. We hope you find the content of this article useful. If there are further queries or suggestions, make sure you mention them in the comment section. Have a good day.


Related Articles


<< Go Back To Excel Area ChartExcel ChartsLearn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sourav Kundu
Sourav Kundu

Sourav Kundu, BSc, Naval Architecture & Marine Engineering, Bangladesh University of Engineering and Technology, is a dedicated technical content creator of the ExcelDemy project. He has a keen interest in Excel and he leverages his problem-solving skills to provide solutions on user interface with Excel. In his position as an Excel & VBA Content Developer at ExcelDemy, Sourav Kundu not only adeptly addresses challenging issues but also demonstrates enthusiasm and expertise in navigating complex situations. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo