Today, let’s learn how to make an impressive map chart with Excel. Something like this:
Step 1: Set up your Data for the Map Chart
You need geographical fields (district / county/ state or country), few numeric values to create the map chart in Excel. Here is the layout of the data I’ve used.
I will explain the logic for “Calc. Column” in Step 3.
Step 2: Create “selection mechanism” with Data Validation
Select a blank cell to set up “drop-down” selection mechanism. This is where we will select one of the products to show on the map.
Go to Data ribbon & click on “Data Validation”. Change the rule type to “List” and point to the list of products. You can set up this list elsewhere in the spreadsheet or directly point to the table headers.
Related: Learn more about setting up “drop-down lists in Excel”
Step 3: Calculate the values for “selected” product
I recommend using either XLOOKUP or INDEX MATCH to calculate the values of “selected” product for our table.
Here is the XLOOKUP formula I’ve used. This assumes the drop-down list selection is in cell G6.
=XLOOKUP($G$6,
Table2[[#Headers],[Mint Chip Choco]:[After Nines]],
Table2[@[Mint Chip Choco]:[After Nines]])The above formula looks up the G6 (selection values) against table headers and returns the corresponding product sales values to the calculated column.
Step 4: Make the Map chart
Select the geographical fields & the calculated columns alone, then go to Insert Ribbon & click on the Filled Map chart option (see below).
Customize the map chart colors & title if you want.
Place chart next to the selection cell and you will have a dynamic map chart in Excel, like below:
Download the sample file
If you need a hand with this technique, check out the example workbook.
Video Tutorial – How to make map charts in Excel?
I made a video explaining this process with a few more examples & tips. Check out the video below or my YouTube channel.
Known issues & problems with Maps in Excel
Excel map charts are not perfect. Here are some known issues with it.
- Not all geographical data is supported: In many countries, you can’t show data by district or city or regions. For example, if you try to make the same chart with districts of an Indian state, the map will not work.
- No support for LAT / LONG or street addresses: You can’t create map charts with specific point information (like addresses or coordinates).
- Incorrect maps: When a geographical boundary is disputed (due to wars or whatever), the map chart doesn’t show correct outlines or altogether omits the regions.
- Unable to zoom: You can’t zoom in to a specific region with the map charts.
Possible Alternatives:
You can use Power BI or Tableau other data visualization tools to make the maps. They allow working with more granular data or can let you control the zoom for your maps.
Got a question?
Do you have a question or doubt about using map charts in Excel? Leave a comment so I can help.
15 Responses to “Christmas Gift List – Set your budget and track gifts using Excel”
[...] Christmas Gift List – Set your budget and track gifts using Excel … [...]
I'm confused: if you spend $10, and your budget is $40, shouldn't the amount in the "Within Budget?" column stay black, since you didn't go over budget?
In other words, since we overspent on the electronic photo frame, shouldn't the $8 cell turn red?
@JP.. maybe Steven is encouraging consumerism... ?
I havent realized it earlier, but now I see it. If you unprotect the sheet, you can change the formula in Column I to =IF(G13=0;" ";F13-G13) from =IF(G13=0;" ";G13-F13), that should correct the behavior.
Thanks Chandoo. I thought of making a shopping list spreadsheet for Christmas, but this is neat so I think I'll use this instead.
Chandoo & Steven thanks for this spreadsheet. But for the sake of a person who has been staring at this megaformula in vain for the last 40 mins and not afraid to ask, would it be possible for you to walk us through the logic used here?
=SUM(SUMPRODUCT(SUBTOTAL(3,OFFSET($K$13:$K$62,ROW($K$13:$K$62)-MIN(ROW($K$13:$K$62)),0,1)),--($K$13:$K$62="-"))+SUMPRODUCT(SUBTOTAL(3,OFFSET($K$13:$K$62,ROW($K$13:$K$62)-MIN(ROW($K$13:$K$62)),0,1)),--($K$13:$K$62="0")))&" / "&SUBTOTAL(2,$G$13:$G$62)
Thanks Chandoo.. This is one of the best budget spreadsheets I've ever seen.. The Arrays are out of this world!! And it's FREE!!
Chandoo, can you tell us more about Steven? Does he have his own site?
JP, I think Chandoo changed it when he changed the currency formatting from £ to $, a negative figure is a good thing in this case. But don't change the formulas, the overbudget and under budget won't work properly if you do. Also Chandoo I think you've accidentally broke the conditional formatting for the alternating row colouring the formula is different to the version I sent you. As for the megaformula chrisham, it gave me a headache trying to get it all working, so I will let Chandoo talk you through it.
Hi,
In cells I6 and I7, I understand that subtotal together with offset function returns an array of ones after which, the sumproduct function gives the desired result.
But I’m not able to figure out the reason for using an array in I8 to return the most expensive gift.
Can’t the formula be just
“=VLOOKUP(SUBTOTAL(4,$G$13:$G$62),$G$13:$J$62,4,0)”
Savithri, Cell I8 needs the array, if the formula was “=VLOOKUP(SUBTOTAL(4,$G$13:$G$62),$G$13:$J$62,4,0)” it would find the highest price from the filtered range (i.e. highest actual in filtered range is $50) BUT then return the first person with that actual, not looking in just the filtered range (so first person on the list with a $50 actual.)
To see what I mean, change the formula, then change all the actuals to $50 then filter for baby, it lists the first name on the list.
But a good question 🙂
Thank you. I now realise that the array is used to get the ‘filtered range’ instead of the entire range, as table array for look up value.
[...] Download This Template [...]
this looks like an awesome excel sheet!! is there anyway i can get it emailed to me unprotected? for some reason, i am unable to download it 🙁 help!!
Hi I also can not download to a mac as the sheet is protected any help would be great
[...] to send her a pricey present. Rather, send a card with a picture of your child. Here’s a cool Excel sheet that will help you estimate your budget per person and let you track [...]
[...] husband and I pour/poor over the Christmas spreadsheet (yes, I do know how dorky that sounds, but we’re not the only ones!), figuring out who should give what to whom. We live at a distance from most of our family, so it [...]