A map chart is somewhat discrete, in that it's not going to plot down to a city level, but it will do state, zip codes, things like that. And in this case, we're gonna go back to our 3-year's sales data and use Ctrl + right arrow. A map chart, somewhat like it sounds, has the ability to map data, on a map. There's not much we can do about it, but I'm not gonna worry about it, because we're going to place a map chart over it. If you paste the following code into into the specific worksheet’s code page, this event will be setup.- Picking back up, with our somewhat complete dashboard, you notice that there's some unused white space, at the top of the dashboard that's created by our timeline and our slicer. If you want to get a little fancy, you can even set up an event trigger by running the VBA macro whenever there is a change on the spreadsheet. Loop through each row in the Excel table and change the color of states that are flagged “Yes”.Turn all 50 states grey (or whatever color your want) to reset them.Retrieve the highlight color form the square box on the spreadsheet (named “HighlightColor”).There are essentially three parts to this code: 'Loop through each row in table and color states that are flagged Set tblStates = ActiveSheet.ListObjects("Table_States")įor Each cell In (4).CellsĪctiveSheet.Shapes(cell.Offset(0, -1).Value). = HighlightColor 'Reset All States Set shpUnitedStates = ActiveSheet.Shapes("UnitedStates") 'PURPOSE: Update Highlighting Within United States Map Chart 'SOURCE: Dim shpUnitedStates As Shapeĭim HighlightColor As Long 'Determine Highlight Color Set shpHighlight = ActiveSheet.Shapes("HighlightColor") Let’s now take a look at the VBA code I used. I find just pulling the fill color from a shape or cell is much easier to manage than dealing with color codes. The highlight color is stored visually as the fill color of a square Excel Shape. I utilized a simple Data Validation list to allow the user to select “Yes” or they could delete the value to remove the highlight color. I just needed to grab a list of the US States and their abbreviations, then paste those two lists into Excel. These state shapes will function as if they were part of Excel’s native insert shapes feature.īecause all the state shapes have a standardized naming convention, it was very easy to build a table that listed out all the Shape Names. Since the state icons will all be Excel Shapes, you can manipulate them any way you’d like! This means you can recolor them, add a border, implement a gradient, or even tweak the transparency. I’ve painstakingly gone through and designed an Excel shape icon of each state with the United States that you can manipulate until your heart’s content. Whether you are looking to build a cool infographic, customized map chart, or Excel dashboard of the 50 states within America, this guide will give you a tremendous headstart (and there are some downloadable goodies along the way)! 50 US States Shape Icons īefore you begin, you’re likely going to need a map of the United States that you can work with inside Microsoft Excel. This typically leaves us spreadsheet users with the challenge of creatively working within the Excel environment to build our own alternative solutions to get the desired look. Its functionality is as basic as can be and leaves a lot to be desired. Let’s face it, Excel’s built-in Map Chart feature seemingly was thrown together overnight.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |