Home

Map Charts with Counties

%3CLINGO-SUB%20id%3D%22lingo-sub-279337%22%20slang%3D%22en-US%22%3EMap%20Charts%20with%20Counties%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-279337%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EOkay%2C%20so%20I'm%20making%20a%20project%20to%20show%20data%20for%20each%20county%20in%20Kentucky.%20My%20table%20is%20formatted%20with%20Column%20A%20having%20the%20heading%20%22County%22%20and%20Column%20B%20having%20the%20heading%20%22Acres%20Used%20for%20Farm%20Land%22--the%20data.%20However%2C%20when%20I%20go%20to%20the%20recommended%20charts%2C%20It%20only%20gives%20me%20the%20option%20to%20use%20a%20United%20States%20map%20instead%20of%20a%20Kentucky%20state%20map.%20To%20make%20matters%20worse%2C%20it%20is%20showing%20two%20of%20the%20counties%20in%20different%20states%20(one%20in%20Arkansas%20and%20another%20in%20Indiana).%20I%20went%20through%20and%20added%20%22%2C%20Kentucky%22%20to%20the%20end%20of%20all%20the%20counties%3B%20however%2C%20this%20did%20not%20fix%20the%20problem.%20Any%20answers%20or%20possible%20troubleshooting%20I%20could%20do%3F%20Pictures%20are%20attached.%20Thanks%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20706px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F58450iE10CA8312BE5AC2C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screen%20Shot%202018-10-29%20at%202.15.35%20PM.png%22%20title%3D%22Screen%20Shot%202018-10-29%20at%202.15.35%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20526px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F58449i0B27B47D2567A470%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screen%20Shot%202018-10-29%20at%202.16.06%20PM.png%22%20title%3D%22Screen%20Shot%202018-10-29%20at%202.16.06%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-279337%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMap%20Chart%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-282581%22%20slang%3D%22en-US%22%3ERe%3A%20Map%20Charts%20with%20Counties%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-282581%22%20slang%3D%22en-US%22%3E%3CP%3EVirginia%20is%20composed%20of%20133%20Cities%20and%20Counties.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20plot%20Richmond%20City%20in%20Excel%3F%26nbsp%3B%202016%203D%20Maps%3F%26nbsp%3B%202019%20Map%20Charts%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20seems%20to%20only%20know%20about%20Richmond%20County%2C%20which%20is%20entirely%20different%20than%20Richmond%20City.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-279519%22%20slang%3D%22en-US%22%3ERe%3A%20Map%20Charts%20with%20Counties%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-279519%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20can't%20have%20distinct%20colors%20for%20a%20range%20of%20numbers%2C%20but%20you%20can%20define%20up%20to%20three%20colors%20in%20a%20gradient.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20the%20Format%20Data%20Series%20panel%20click%20on%20Series%20Color%20and%20select%20Diverging%20(3-color).%20You%20can%20change%20the%20colors%20and%20you%20can%20change%20the%20minimum%2C%20midpoint%20and%20maximum%20to%20exact%20numbers.%20Set%20Minimum%20to%200%2C%26nbsp%3Bmidpoint%20to%2060000%20and%20maximum%20to%20120000.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20don't%20like%20the%20gradients%2C%20then%20you%20need%20to%20add%20a%20column%20to%20your%20data%20table%20and%20use%20formulas%20to%20translate%200%20-%2060%2C000%20to%201%2C%2060%2C001%20-%20120%2C000%20to%202%2C%20120%2C001%20to%20whatever%20to%203%2C%20etc.%20Then%20use%20the%20column%20with%20the%20values%201%2C%202%2C%203%20to%20plot%20the%20chart%20and%20set%20the%20colors%20for%20these%20numbers%20as%20described%20above.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20the%20following%20screenshot%20I%20have%20added%20a%20formula%20to%20the%20data%20table%20to%20generate%20numbers%20from%201%20to%203%20for%20the%20number%20ranges%20in%20column%20C%2C%20using%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFS(%5B%40number%5D%26lt%3B33%2C1%2C%5B%40number%5D%26lt%3B66%2C2%2CTRUE%2C3)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThen%20I%20changed%20the%20chart%20series%20definition%20to%20plot%20the%20numbers%20from%20column%20D%20instead%20of%20C%20and%20gave%20each%20of%20the%20three%20number%20a%20distinct%20color.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F58495i9F35B604B0E25D8D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222018-10-30_22-38-02.jpg%22%20title%3D%222018-10-30_22-38-02.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20more%20than%20three%20distinct%20colors%2C%20use%20the%20%22lowest%20value%22%2C%20%22middle%20value%22%2C%20and%20%22highest%20value%22%20instead%20of%20numbers%20and%20set%20your%20thresholds.%20With%20%22x%22%20distinct%20number%20of%20values%20in%20column%20D%2C%20there%20will%20be%20%22x%22%20distinct%20colors%20in%20the%20map%2C%20but%20they%20will%20be%20a%20mix%20of%20the%20%22start%22%20and%20the%20%22end%22%20color.%20You%20won't%26nbsp%3B%20be%20able%20to%20define%20%22x%22%20exact%20hues.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-279491%22%20slang%3D%22en-US%22%3ERe%3A%20Map%20Charts%20with%20Counties%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-279491%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20You%20so%20much!!%20Life%20Saver.%20I%20just%20have%20one%20more%20question.%20I%20want%20to%20change%20the%20legend%20for%20the%20map%20chart.%20It%20is%20auto%20set%20for%20the%20faded%20shades%20as%20the%20numbers%20fall%3B%20however%2C%20I%20would%20like%20to%20seperate%20the%20data%20into%20seperate%20categories.%20For%20example%2C%20I%20want%200-60%2C000%20to%20be%20one%20color%2C%2060%2C001-120%2C000%20to%20be%20another%2C%20and%20so%20on.%20Is%20there%20a%20way%20to%20do%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-279398%22%20slang%3D%22en-US%22%3ERe%3A%20Map%20Charts%20with%20Counties%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-279398%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eif%20you%20add%20a%20column%20for%20State%20and%20enter%20Kentucky%20in%20all%20cells%2C%20then%20the%20map%20can%20be%20configured%20to%20show%20Kentucky%20only%20and%20the%20counties%20will%20be%20plotted%20correctly.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F58453iCA3F707F8A626D7C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222018-10-30_09-21-19.jpg%22%20title%3D%222018-10-30_09-21-19.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Dalton Emberton
New Contributor

Okay, so I'm making a project to show data for each county in Kentucky. My table is formatted with Column A having the heading "County" and Column B having the heading "Acres Used for Farm Land"--the data. However, when I go to the recommended charts, It only gives me the option to use a United States map instead of a Kentucky state map. To make matters worse, it is showing two of the counties in different states (one in Arkansas and another in Indiana). I went through and added ", Kentucky" to the end of all the counties; however, this did not fix the problem. Any answers or possible troubleshooting I could do? Pictures are attached. ThanksScreen Shot 2018-10-29 at 2.15.35 PM.pngScreen Shot 2018-10-29 at 2.16.06 PM.png

4 Replies

Hello,

 

if you add a column for State and enter Kentucky in all cells, then the map can be configured to show Kentucky only and the counties will be plotted correctly.

 

 2018-10-30_09-21-19.jpg

Thank You so much!! Life Saver. I just have one more question. I want to change the legend for the map chart. It is auto set for the faded shades as the numbers fall; however, I would like to seperate the data into seperate categories. For example, I want 0-60,000 to be one color, 60,001-120,000 to be another, and so on. Is there a way to do this?

You can't have distinct colors for a range of numbers, but you can define up to three colors in a gradient.

 

In the Format Data Series panel click on Series Color and select Diverging (3-color). You can change the colors and you can change the minimum, midpoint and maximum to exact numbers. Set Minimum to 0, midpoint to 60000 and maximum to 120000.

 

If you don't like the gradients, then you need to add a column to your data table and use formulas to translate 0 - 60,000 to 1, 60,001 - 120,000 to 2, 120,001 to whatever to 3, etc. Then use the column with the values 1, 2, 3 to plot the chart and set the colors for these numbers as described above.

 

In the following screenshot I have added a formula to the data table to generate numbers from 1 to 3 for the number ranges in column C, using

 

=IFS([@number]<33,1,[@number]<66,2,TRUE,3)

 

Then I changed the chart series definition to plot the numbers from column D instead of C and gave each of the three number a distinct color.

 

2018-10-30_22-38-02.jpg

 

If you want more than three distinct colors, use the "lowest value", "middle value", and "highest value" instead of numbers and set your thresholds. With "x" distinct number of values in column D, there will be "x" distinct colors in the map, but they will be a mix of the "start" and the "end" color. You won't  be able to define "x" exact hues.

 

Virginia is composed of 133 Cities and Counties.

 

How do I plot Richmond City in Excel?  2016 3D Maps?  2019 Map Charts?  

 

It seems to only know about Richmond County, which is entirely different than Richmond City.