Microsoft Excel Tutorial: Use 3D Maps to summarize city data at the state level.
Got sales data by city but need to display it by state in Excel's 3D Maps? A.K. asked if there's a way to sum sales at the state level instead of individual cities—yes, and it's easy to do! In this tutorial, I’ll walk you through the steps to modify your 3D Map to show sales totals by state.
By default, Excel's 3D Map plots data at the city level. If you want to switch to state-level mapping, simply adjust the location field to use State instead of City. But instead of Excel’s default column representation, I’ll show you a better way—using a Region Map to shade states based on total sales. This makes your data much easier to analyze at a glance.
Need to show both cities and states? No problem! You can add a second layer to the 3D Map, keeping city data while still displaying state-level totals. I’ll also show you how to tweak the visuals to avoid clashing colors and create a more polished, professional-looking map.
This method provides a clearer picture of sales performance across different locations, helping you make better business decisions. Whether you're analyzing regional trends or just want a better way to present your data, these Excel 3D Map tricks will come in handy.
If this video helped, LIKE, SUBSCRIBE, and COMMENT with your Excel questions! Thanks to A.K. for the great question—see you in the next episode of MrExcel!
Buy Bill Jelen's latest Excel book: MrExcel 2024 Igniting Excel
Table of Contents
(0:00) Changing Excel 3D Map from City to State
(0:18) New 3D Map is at City level by default
(0:44) Choices - replace city or add new layer
(0:54) Changing City to State
(1:05) Changing to Region
(1:23) Adding a second layer for State or City
(1:40) Changing Column Color in Excel 3D Map
(2:06) Wrap-up
Got sales data by city but need to display it by state in Excel's 3D Maps? A.K. asked if there's a way to sum sales at the state level instead of individual cities—yes, and it's easy to do! In this tutorial, I’ll walk you through the steps to modify your 3D Map to show sales totals by state.
By default, Excel's 3D Map plots data at the city level. If you want to switch to state-level mapping, simply adjust the location field to use State instead of City. But instead of Excel’s default column representation, I’ll show you a better way—using a Region Map to shade states based on total sales. This makes your data much easier to analyze at a glance.
Need to show both cities and states? No problem! You can add a second layer to the 3D Map, keeping city data while still displaying state-level totals. I’ll also show you how to tweak the visuals to avoid clashing colors and create a more polished, professional-looking map.
This method provides a clearer picture of sales performance across different locations, helping you make better business decisions. Whether you're analyzing regional trends or just want a better way to present your data, these Excel 3D Map tricks will come in handy.
If this video helped, LIKE, SUBSCRIBE, and COMMENT with your Excel questions! Thanks to A.K. for the great question—see you in the next episode of MrExcel!
Buy Bill Jelen's latest Excel book: MrExcel 2024 Igniting Excel
Table of Contents
(0:00) Changing Excel 3D Map from City to State
(0:18) New 3D Map is at City level by default
(0:44) Choices - replace city or add new layer
(0:54) Changing City to State
(1:05) Changing to Region
(1:23) Adding a second layer for State or City
(1:40) Changing Column Color in Excel 3D Map
(2:06) Wrap-up
Transcript of the video:
Alright, today changing the 3D Map from city to state. This is from A. K.
“I was wondering, I've created a map for sales from different states.
“However, my data shows different cities have sales.
“So there's a way to have the state on the map show the sum of sales?”.
Yes, we can do that. So we have data here.
We're going to go to Data Model, 3D Map. And you see by default it's creating the map at the city level. Maybe you drag the revenue out here to the height.
And then when we tip the map. We can see the various heights of all of those.
But you want to get that data by state. So if we're going to have the state replace the city. Then we're just going to come here to this location. If you want to have both the city and the state, then we would add a layer. Alright, so let's first replace the city with the state. So instead of choosing city here.
We use State. And I don't like how they do this.
That they give us just one little column per state.
I prefer to take this and change it to what they call a Region map.
See? And then they actually color the states in darker for higher numbers. So that's probably the easiest way how I would do this. If you wanted to show both the states and the cities, check this out. You can add a layer.
And in layer two, have it by city. And again, maybe revenue as the height or category or something like that. So now you kind of have this cool thing, although that's a horrible color, isn't it? Let's see, Layer options.
Maybe try and choose something a little bit complimentary. Just not the blue and the orange - that was kind of hideous. All right, so now as we go in, we can see that Texas was the highest revenue. And within Texas we have all these little cities that had various revenues like that. So lots of different ways to go depending on how you want it to look. I want to thank A.K. for sending that question in.
And I want to thank you for stopping by. We'll see you next time for another net cast from MrExcel. If you like these videos, please down below, Like, Subscribe and Ring the Bell. Feel free to post any questions or comments down in the comments below.
“I was wondering, I've created a map for sales from different states.
“However, my data shows different cities have sales.
“So there's a way to have the state on the map show the sum of sales?”.
Yes, we can do that. So we have data here.
We're going to go to Data Model, 3D Map. And you see by default it's creating the map at the city level. Maybe you drag the revenue out here to the height.
And then when we tip the map. We can see the various heights of all of those.
But you want to get that data by state. So if we're going to have the state replace the city. Then we're just going to come here to this location. If you want to have both the city and the state, then we would add a layer. Alright, so let's first replace the city with the state. So instead of choosing city here.
We use State. And I don't like how they do this.
That they give us just one little column per state.
I prefer to take this and change it to what they call a Region map.
See? And then they actually color the states in darker for higher numbers. So that's probably the easiest way how I would do this. If you wanted to show both the states and the cities, check this out. You can add a layer.
And in layer two, have it by city. And again, maybe revenue as the height or category or something like that. So now you kind of have this cool thing, although that's a horrible color, isn't it? Let's see, Layer options.
Maybe try and choose something a little bit complimentary. Just not the blue and the orange - that was kind of hideous. All right, so now as we go in, we can see that Texas was the highest revenue. And within Texas we have all these little cities that had various revenues like that. So lots of different ways to go depending on how you want it to look. I want to thank A.K. for sending that question in.
And I want to thank you for stopping by. We'll see you next time for another net cast from MrExcel. If you like these videos, please down below, Like, Subscribe and Ring the Bell. Feel free to post any questions or comments down in the comments below.
