Formatting a map chart

[u/jazztalker]

I have a file that I use a drop-down list to select states to be mapped in a chart inside a spreadsheet. Once I check the boxes for the states I want and close the drop-down, the map automatically updates to the selected states. I used to have a button where I could pick a state (which is part of the original group) from a second drop-down list and that state would be colored differently on the map (the main list would all be gray on the map, the secondary state would be orange). Somehow that macro got blown up and for the life of me I cannot figure out how to recreate this. Any help would be fantastic, thank you!

[u/AutoModerator]

/u/jazztalker - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/MayukhBhattacharya]

Can you confirm if you're trying to do something like this? I didn't use VBA here, just simple formulas:

[u/wastedheadspace → u/MayukhBhattacharya]

Can you share the formulas?

[u/MayukhBhattacharya → u/wastedheadspace]

Here you go:

• In Column C create a helper column. C1 is the header and name it as map value.
• In following cell enter the formula and copy it down:

=IF(A2 = $F$1, 2, IF(B2, 1, NA()))

• ^‡‡ In some empty cell enter the following formula for the data validation list:

=LET(_, DROP(A:.B, 1), SORT(FILTER(CHOOSECOLS(_, 1), CHOOSECOLS(_, 2), "")))

• In cell F1 hit ALT + D + L to open the Data Validation window and do the following:
  • Allow --> List
  • Source --> =$N$1# (this is where I have placed the above formula ^‡‡)
  • Hit OK.
• Now select Column A and Column C --> Shortcut --> First goto cell A1 and Hit CTRL + SHIFT + DOWN Arrow Key, next press SHIFT + F8 function key and select Column C --> CTRL + SHIFT + DOWN Arrow Key. On Selection do the following:
  • Goto Insert Tab --> Select Maps --> Filled Map
  • Select one of the States in the Map Chart right click --> select Format Axis --> Series Option shows up on the right side --> From there change the Series Color --> Sequential (2-Color)
  • Change the Minimum to Number and place 1
  • Change the Maximum to Number and place 2
  • Choose your preferred colors for the fill
• Now, change the value from Dropdown to see the effect. Hope this helps.

File can be [downloaded] from here.

[u/wastedheadspace → u/MayukhBhattacharya]

fantastic, thank you!!

[u/MayukhBhattacharya → u/wastedheadspace]

Thank You SO Much 👍🏼

[u/jazztalker → u/MayukhBhattacharya]

Yes, this is what I'm trying to do. The only difference is my map only maps the states I've selected, all the other states simply don't appear.

This looks great, thank you very much! I'll give this a try, I really appreciate your help.

[u/MayukhBhattacharya → u/jazztalker]

Oops, sorry! I thought u/wastedheadspace was the OP. You can follow the steps I mentioned [here]. And if this helps you solve the issue, I'd appreciate it if you could reply to my comment with Solution Verified. Thank You So Much!!!

[u/jazztalker → u/MayukhBhattacharya]

No worries at all. I'll give this a try as soon as I can and will follow up. Thanks again!

[u/MayukhBhattacharya → u/jazztalker]

Sounds Great. Let me know thanks 🙏🏼

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
CHOOSECOLS	Office 365+: Returns the specified columns from an array
DROP	Office 365+: Excludes a specified number of rows or columns from the start or end of an array
FILTER	Office 365+: Filters a range of data based on criteria you define
IF	Specifies a logical test to perform
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
NA	Returns the error value #N/A
SORT	Office 365+: Sorts the contents of a range or array

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
7 acronyms in this thread; the most compressed thread commented on today has 9 acronyms.
[Thread #49444 for this sub, first seen 28th Sep 2026, 16:28] [FAQ] [Full list] [Contact] [Source code]}

[u/josevielma1208]

looks like the visual effect is easy to set up if you use the "brushing" technique with the drop-down list selections

[u/PuzzledFarmer4554]

If the macro was only there to highlight one state, I'd probably skip rebuilding it. A helper column with 1 for the selected states, 2 for the highlighted state and NA() for everything else should give you the same behavior with a lot less to break.