I want to put colour inside polygons I made on an excel chart. I have more than 1 polygon and I would like to use more than one colour. The chart below is for illustrative purposes only and serves as an example of what I want to do. Is there a way to do this?
Thanks for your help.
1 4
4 4
4 1
1 1
1 4
I would start with this tutorial: https://peltiertech.com/fill-under-b...n-excel-chart/ Once you understand how the technique works, I expect it would be readily adapted to your polygons.
Originally Posted by shg
Thanks for your input. I have studied another video of this process other than the one you referenced. This works great when shading between 2 lines on a chart. I don't see it being applicable to my case of a one line closed loop.
Are you required to do the chart as "one line closed loop?" Would you be allowed to treat your polygon as "two lines that form a closed loop?"I don't see it being applicable to my case of a one line closed loop.
Looking at your square, for example, yes, you can look at it as a single line from 1,4 to 4,4 to 4,1 to 1,1 back to 1,4. You can also look at it as two lines. One line goes from 1,1 to 1,4 to 4,4. The second line goes from 1,1 to 4,1 to 4,4. That's how I would expect to do this -- decompose the polygon into 2 (or more, if the polygon's complexity requires it) lines/series that form a closed loop -- then use the techniques in those tutorials to construct the "stacked area chart" that will create the fill inside of the polygon.
A square is probably too simple of a polygon to adequately represent what you are trying to do. Can you provide examples of simple and complex polygons that you expect to need to work with? Place the data for the polygons in a spreadsheet file and upload to the forum, and our users can tinker with it to help apply the tutorials to your specific polygons.
Thanks for your reply. Listed below is the actual data for 4 polygons. There are 90-100 polygons in total.
48.35233429 -59.29572096
48.35284681 -59.29150212
48.35425631 -59.28594094
48.35707523 -59.27884558
48.3596379 -59.27347616
48.36232869 -59.26810665
48.36399438 -59.26197019
48.36540388 -59.25449131
48.36578827 -59.24912179
48.36642892 -59.24279357
48.36681337 -59.23493117
48.36796654 -59.22745228
48.37040108 -59.21613807
48.37116986 -59.21038503
48.37322 -59.20233086
48.37398878 -59.19696145
48.3746295 -59.18814024
48.37411691 -59.18162015
48.37360439 -59.17740131
48.37181051 -59.17260718
48.36950417 -59.17126475
48.36642892 -59.17164827
48.36284121 -59.17375775
48.36104733 -59.17682603
48.35656271 -59.18890728
48.35348746 -59.19772849
48.35130925 -59.20328967
48.34989975 -59.20635795
48.348234 -59.2080839
48.34579946 -59.20789214
48.34298054 -59.20328967
48.3419555 -59.2011803
48.34028974 -59.2021391
48.33952096 -59.20367329
48.33849592 -59.20846742
48.33708642 -59.21537102
48.33657391 -59.22246629
48.335677 -59.23358874
48.33478002 -59.24336886
48.332858 -59.25334064
48.33055166 -59.26369604
48.32862965 -59.27117493
48.32529814 -59.27731149
48.32452936 -59.28076319
48.32055726 -59.28594094
48.31427869 -59.29418687
48.30940962 -59.29955629
48.30338738 -59.30550108
48.28980527 -59.31662353
48.28198915 -59.3246777
48.2749418 -59.33580015
48.27237913 -59.34021076
48.27135409 -59.34366256
48.27020086 -59.35420962
48.26917582 -59.36456503
48.26891956 -59.37338624
48.26968834 -59.38009809
48.27186661 -59.3854676
48.27430115 -59.3889193
48.27673569 -59.39045349
48.28083591 -59.39198758
48.28788326 -59.39006997
48.29339298 -59.38796049
48.30069659 -59.38393341
48.30441242 -59.38048161
48.30876897 -59.37798871
48.31479121 -59.373578
48.32119791 -59.3691674
48.3265795 -59.36494855
48.32862965 -59.36245566
48.33119231 -59.35746966
48.33503628 -59.35459325
48.33823959 -59.35075792
48.34259615 -59.3444296
48.34579946 -59.33388244
48.34874658 -59.31988357
48.3505404 -59.30665165
48.35220616 -59.29667987
48.35233429 -59.29572096
48.38628959 -58.9338583
48.38526455 -58.94229599
48.38436758 -58.95150073
48.38423945 -58.957062
48.38436758 -58.96108909
48.38526455 -58.96415737
48.38757096 -58.96492441
48.3898773 -58.96319846
48.39282442 -58.957062
48.396284 -58.94977488
48.39897479 -58.94536427
48.40204998 -58.93846067
48.40538142 -58.93289949
48.40896919 -58.92235233
48.41178812 -58.91391463
48.41550395 -58.90355923
48.41691345 -58.89723091
48.41781036 -58.8884097
48.41691345 -58.88169785
48.41473517 -58.88227324
48.41281316 -58.88284852
48.4112756 -58.88188961
48.40871293 -58.88131433
48.40550955 -58.88476613
48.39974357 -58.89339559
48.39436198 -58.90317571
48.39064614 -58.91218868
48.38885226 -58.91985933
48.38616146 -58.93405006
48.38628959 -58.9338583
48.26898526 -59.17444779
48.26996413 -59.17503372
48.27118767 -59.17364209
48.27206864 -59.17115163
48.27334113 -59.16748931
48.27451572 -59.16514537
48.27436888 -59.16346071
48.27456467 -59.16148304
48.27534775 -59.15972514
48.27524986 -59.15789397
48.2758861 -59.15642898
48.27534775 -59.15452457
48.27549459 -59.15269341
48.27387948 -59.14961702
48.27280278 -59.14903108
48.27211759 -59.14712667
48.27084511 -59.14646738
48.26986624 -59.14668714
48.26869164 -59.14544191
48.267566 -59.14580819
48.26624457 -59.14558842
48.26492314 -59.14624761
48.26394427 -59.14522225
48.26272073 -59.1459547
48.26164403 -59.14463621
48.26046937 -59.14573493
48.25944162 -59.14507574
48.25821809 -59.14617446
48.25626041 -59.14639412
48.25449846 -59.1459547
48.25317703 -59.14654063
48.25258973 -59.14654063
48.25146409 -59.14727308
48.25063206 -59.149324
48.24906596 -59.1517412
48.24916385 -59.15371887
48.24852754 -59.15555003
48.24935957 -59.15877293
48.25038739 -59.16206898
48.25156198 -59.16324095
48.25293236 -59.16382699
48.25361755 -59.16543838
48.25479214 -59.16558489
48.25537943 -59.16763582
48.25626041 -59.1673428
48.25723921 -59.16924722
48.25860959 -59.16880779
48.25924584 -59.17063895
48.26042043 -59.17100512
48.26105667 -59.17268988
48.26232922 -59.17290954
48.26306329 -59.17415477
48.264189 -59.17437453
48.2651678 -59.17561976
48.26644029 -59.17525348
48.26766383 -59.17569301
48.26883849 -59.17459429
48.33300127 -58.98979196
48.33456744 -58.98495766
48.33593782 -58.98158836
48.33613354 -58.97675396
48.33750392 -58.9757285
48.33867857 -58.96869687
48.33995106 -58.96444851
48.34004895 -58.9625441
48.34083197 -58.96034666
48.34063625 -58.95477992
48.33926587 -58.95185004
48.3367209 -58.95258249
48.33535052 -58.95228947
48.33280555 -58.95536586
48.33133728 -58.95419389
48.32977111 -58.9544869
48.32830284 -58.9560983
48.32644306 -58.95653783
48.32399599 -58.9593212
48.32282139 -58.96078619
48.32184252 -58.96151863
48.32008064 -58.96356956
48.31880815 -58.96415549
48.3154801 -58.96825735
48.31430551 -58.97089421
48.31254356 -58.97265221
48.31195626 -58.97514257
48.31029227 -58.97880489
48.30990069 -58.98026988
48.31009648 -58.9821743
48.31009648 -58.98202779
48.30950918 -58.98569011
48.30843249 -58.98935253
48.30833459 -58.99154987
48.30853031 -58.99360079
48.30784512 -58.99491927
48.30794302 -58.99638416
48.30794302 -58.99814217
48.3087261 -58.99990008
48.30990069 -59.00151148
48.31146686 -59.00209751
48.31273934 -59.00297647
48.31430551 -59.00282996
48.31518642 -59.00429484
48.31694837 -59.00385542
48.31949334 -59.00326948
48.32017853 -59.00297647
48.32145102 -59.00121856
48.32252771 -59.00063252
48.3232129 -58.99916763
48.32517058 -58.99887461
48.32585577 -58.9985816
48.32683464 -58.99653067
48.32791133 -58.9953588
48.32918382 -58.99477276
48.33123939 -58.99330788
48.33231608 -58.99111044
48.33300127 -58.98949894
I looked at the first set of data. I was able to plot the first set as a scatter plot. I guess it's technically a "polygon" but it's got so many points it's kind of a blob.
However, you can't fill a scatter plot. So I decided plot it as two lines, an upper line and a lower line. This required rearranging the data just a bit. However, the problem there is that Excel does not plot line data along the x axis as you might expect. The series of points treated as though the x values are equally spaced, instead of using their true values as it does on a scatter chart. So you can't draw your polygon properly using line charts without doing a lot of gymnastics to normalize and align the x values. Which I wasn't going to dive into.
It might be possible to create a shape using VBA with AddPolyline method.
Are you required to use Excel for this? I thought I remembered seeing this feature in Gnumeric, so I pulled up my old Linux computer, opened Gnumeric, copied one of your data sets in, and created an XY scatter chart and told it to "fill" the data series. See result in attached picture (note that it took longer to boot up the old computer than it did to create the chart).
If you decide you must use Excel for this, I would still expect to use the techniques described in Peltier's tutorial to generate the fill. As I described before, it still looks like a process of decomposing each polygon into 2 or more data series, then building the stacked area data series to provide the fill. If it helps, the process can be visualized as drawing the polygon and fill from left to right, bottom to top. Here's how I would expect to proceed (as a broad overview for now, programming details yet to be determined):
1) Decompose polygon into individual "left to right" data series. For examples 1 and 2 where there is a single left and right point, this should be straightforward. For examples 3 and 4, this will be more complicated because the left and right "edges" feature "zigs" and "zags," and we will need to identify the zigs and zags so we can fill the zigs while not filling the zags. This part should end up with a table of values with a single column of X values (sorted in ascending order), and multiple columns of Y values -- each column corresponding to the y values for the different left to right parts of the polygon we've identified. I haven't explored in enough detail, but this might require an interpolation algorithm to fill in y values for data sereis that don't have y values associated with each x value.
2) Build the values for the stacked area data series. This is mostly following Peltier's tutorial. Calculate appropriate X values for the integer based "date" axis that the area chart uses, then calculate y values for the different "stacks" in the stacked area chart.
3) Steps 1 and 2 should be the hard part. With those steps completed, creating the chart should be as easy as following the tutorial to build the XY scatter + stacked area combination chart, then formatting chart elements as desired.
Step 1 is probably the hardest part. Once that is done, the rest is following Peltier's tutorial.
How do you want to proceed? It seems to me that the easiest by far is to drop Excel and move to a spreadsheet/chart engine that has built in formatting that knows how to fill the inside of a "shape" defined by an XY scatter chart series. If you decide you must use Excel, the above overview is how I would expect to accomplish the task.
Last edited by MrShorty; 03-23-2024 at 12:54 PM.
Thanks for your input. My charts display a lot of data. They are very, very busy with many other lines and shapes besides these polygons I want to highlight. With 90-100 polygons, it will be a large undertaking but it may be my only option. Your thoughts and efforts are appreciated.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks