Data Visualization Issue - Misaligned on X Axis

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167
Hi All,

I attempted to post an attachment, but it appears I do not have this permission? I am attempting to fill the are under my XY scatter plot, but for some reason the area chart and XY scatter plot are just slightly misaligned along the X Axis. If anyone is willing to attempt to recreate this to help me troubleshoot, here is the initial guidance I am following:

https://peltiertech.com/fill-under-between-series-in-excel-chart/



Area Chart
X ValueY Value
Scatter Plot-1,000.000.00
X ValueY Value-914.290.00
-32,000,000.000.00-914.290.00
-14,000,000.00-1.00-400.00-1.00
-4,000,000.00-2.00-114.29-2.00
-2,000,000.00-3.00-57.14-3.00
0.00-3.000.00-3.00
0.00-3.000.00-3.00
2,000,000.00-3.0057.14-3.00
4,000,000.00-2.00114.29-2.00
14,000,000.00-1.00400.00-1.00
32,000,000.000.00914.290.00
914.290.00
1,000.000.00
-3.50Y min
0.00Y max

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,281
Office Version
2019, 2016, 2013
Platform
Windows
No one has permission to post attachments, the board has finite capacity which is better used hosting questions and links and the odd html graphic image
 

thisoldman

Well-known Member
Joined
Jan 5, 2014
Messages
1,073
I hope this will help you.

Area charts are a form of category charts. When you change the horizontal axis to a Date, Excel is going to place it where it wants. There are no negative Dates in Excel.

Rather than dividing up the area chart into 1,000 horizontal units, I used 800 units—an 800 unit scale fits your posted data so well. I used Custom number formatting for the labels: 0,," M"

For a filled funnel interior:
Area
Scatter00
Y00
-32 M0800
-14 M-1260-1
-4 M-2360-2
-2 M-3380-3
0 M-3400-3
0 M-3400-3
2 M-3420-3
4 M-2440-2
14 M-1540-1
32 M07200
8000
0x min8000
800x max

<tbody>
</tbody>




For a filled funnel exterior, I switched the y values to positive numbers and formatted the secondary vertical axis to have "Values in reverse order.:

Area
Scatter00
Y00
-32 M0800
-14 M-12601
-4 M-23602
-2 M-33803
0 M-34003
0 M-34003
2 M-34203
4 M-24402
14 M-15401
32 M07200
8000
0x min8000
800x max

<tbody>
</tbody>

 

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167
Thank you! I really appreciate the guidance and knowledge. Any thoughts on how to maintain alignment when the key inputs change? Did you use a formula for the X axis of the area chart?

Area Chart
X ValueY Value
Scatter Plot0.000.00
X ValueY Value0.000.00
-33,333,000.000.0080.000.00
-13,000,000.00-1.00260.00-1.00
-2,800,000.00-2.00360.00-2.00
-800,000.00-3.00380.00-3.00
0.00-3.00400.00-3.00
0.00-3.00400.00-3.00
Sales ---->800,000.00-3.00420.00-3.00
Appointments ---->2,800,000.00-2.00440.00-2.00
Qualified Leads --->13,000,000.00-1.00540.00-1.00
Initial Leads---->33,333,000.000.00720.000.00
800.000.00
800.000.00

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
 

thisoldman

Well-known Member
Joined
Jan 5, 2014
Messages
1,073
I had to think about this. You do have to use fixed horizontal axes. Primary runs from -40,000,000 to +40,000,000; secondary runs from 0 to 800.

If the primary x-axis runs from -40,000,000 to +40,000,000, it has a range, max minus min, of 80,000,000.
We'll set the secondary axis to run from zero to 800. That will give us a conversion factor of 1/100,000—each secondary axis unit equals 100,000 primary axis units.

We know:
-40 M primary equals 0 secondary
0 M primary equals 400 secondary
+40 M primary equals 800 secondary

If we add 40 M to each of those three left-hand values:
-40 M plus 40 M equals 0 M
0 M plus 40 M equals 40 M
+40 M plus 40 M equals 80 M

Now we just have to convert those primary scale results to the secondary axis units:
(0 M) * 1/100,000 = 0
(40 M) * 1/100,000 = 400
(80 M) * 1/100,000 = 800

The scatter chart x values start in B5. That cell contains the value -33,333,000. The Excel formula for the conversion is:
=(B5 + 40000000) / 100000
Then copied downward.

Area Chart
Y Value
Scatter Plot00
Y Value00
-33,333,0000670
-13,000,000-1270-1
-2,800,000-2372-2
-800,000-3392-3
0-3400-3
0-3400-3
Sales ---->800,000-3408-3
Appointments ---->2,800,000-2428-2
Qualified Leads --->13,000,000-1530-1
Initial Leads---->33,333,00007330
8000
8000

<tbody>
</tbody>
 
Last edited:

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167
Well, first I would like to commend you for cracking the sales/conversion funnel. I have not found any solution that works dynamically like this.

Can you think of any way to shade the various stages in different colors (i.e. Y=0 to -1, Y=-1 to -2, Y=-2 to -3)? This would show the journey along the sales path and make this the perfect solution.
 
Last edited:

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167
My initial thought was to plot additional areas, like the breakout below. But i'm running into a bunch of issues so I think I'm missing something.

Area 1
X ValueY Value
00
800
260-1
540-1
7200
8000
Area 2
X ValueY Value
260-1
360-2
440-2
540-1
Area 3
X ValueY Value
360-2
390-3
400-3
400-2

<tbody>
</tbody>

<tbody>
</tbody>
 
Last edited:

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167
@thisoldman.... we just created the best conversion funnel graphic available on the web.... I wish I knew how to post pictures on here... plug this data in and take a look. Thank you very much!


  • 1 scatter plot
  • 4 area charts (I added an extra layer for "the lip" of the funnel)

4 AREA CHARTS ---- (X2,Y2); (X2,Y3); (X2,Y4); (X2,Y5)
X2Y2Y3Y4Y5
1 SCATTER PLOT00.000.000.000.00
X1Y100.000.000.000.00
-32,000,0000.0800.000.000.000.00
-32,000,000-0.580-0.50-0.50-0.50-0.50
-14,000,000-1.5260-1.50-1.50-1.50-0.50
-4,000,000-2.5360-2.50-2.50-1.50-0.50
-1,000,000-3.5390-3.50-2.50-1.50-0.50
0-3.5400-3.50-2.50-1.50-0.50
0-3.5400-3.50-2.50-1.50-0.50
1,000,000-3.5410-3.50-2.50-1.50-0.50
4,000,000-2.5440-2.50-2.50-1.50-0.50
14,000,000-1.5540-1.50-1.50-1.50-0.50
32,000,000-0.5720-0.50-0.50-0.50-0.50
32,000,0000.07200.000.000.000.00
8000.000.000.000.00
8000.000.000.000.00
-40,000,000X min0X min
40,000,000X max800X max

<colgroup><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,281
Office Version
2019, 2016, 2013
Platform
Windows
you can post a link to a hosted cloud service, something reputable and well known without a password of joining requirement
 

Watch MrExcel Video

Forum statistics

Threads
1,095,320
Messages
5,443,770
Members
405,251
Latest member
shanezer

This Week's Hot Topics

Top