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>
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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
 
Upvote 0
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>

WWvRPku.png



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>

h8TxDM4.png
 
Upvote 0
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>
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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:
Upvote 0
@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>
 
Upvote 0
you can post a link to a hosted cloud service, something reputable and well known without a password of joining requirement
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top