Office hacks: reporting more colorful use of Excel to create heat maps

In Excel, we often use heat maps in order to display data distribution more visually (such as the number of cell phones sold in different places, statistics of different places, etc.) Excel 2019 supports the insertion of 3D maps, and in which heat maps can be generated directly. The following is an example of creating a schematic diagram of the per capita disposable income of each district and city in Fuzhou City in 2018 and the year-on-year increase last year (Note: the data comes from Fuzhou Statistical Yearbook 2019 of Fuzhou Bureau of Statistics).

First prepare the data. Because it is the production of map heat map, Excel’s three-dimensional map is automatically identified by the actual address entered, and we get the data address is mostly omitted city address (so that the country has the same name address is easy to identify the wrong), so you also need to use Excel’s intelligent fill to complete the address, so that the subsequent identification is more accurate. Insert column B in the original data, enter the full name of the county and city in B2 (such as “Gulou District, Fuzhou City, Fujian Province”, so that other cities such as Zhengzhou also has Gulou District will not be identified), complete the input, locate E2, press Ctrl + E to complete the intelligent fill (Figure 1).



Delete the data in column A, click “Insert → 3D Map → Open 3D Map”, in the open 3D map page, select the data type bubble map, in the “location” under the click “Add field → County “, then click the drop-down list on the right and select “Full Address”. In this way, the map can accurately recognize the address entered in the “County and City” column above (Figure 2).


Continue to add the “Per capita disposable income (summation) field” under “Size” as described above. Expand the “Layer Options” and drag the “Size” slider at the bottom to set the size of the bubble (size indicates the value of disposable income per capita by location, with larger values indicating higher disposable income per capita), and under the “Color “option, select the more striking “purple”. In this way, the bubble size can be used to indicate the per capita disposable income of each district and county in Fuzhou on the map (Figure 3).


Click “Add Layer”, under the opened Layer 2, select Heat Map as the data type, add the full address field under “Location”, and add the “Value” option to the Add the “Year-on-year (summation)” field to the “Value” option. Under “Layer Options”, drag the sliders of “Color Scale” and “Radius of Influence” to adjust the size of the heat map and the color scale display. The principle of adjustment is to make the color scale display clear, and the radius of influence of the heat map should not overlap (Figure 4).


Here you can change the default color in order to better display the year-over-year change in per capita income by location on the heat map. Under “Layer Options” above, expand the drop-down list to the right of “Color” and select “Custom/Percentage”. Next, under the “Low” (0%) option, select “Gray” and click “Add Color” to add white (corresponding to a minimum of 8%), light red (8.5%), red ( 9%), and dark red (9.3%). This is a gradient from gray to dark red, indicating the increase in year-over-year per capita income data (the larger the increase, the darker the color), and the results can be previewed in real time on the Layer 2 tab on the left (Figure 5).


After completing the above settings, you can visually represent the per capita income and growth rate information of each county and city in the current scene page through bubbles and heat map. The size of the purple bubble indicates the per capita disposable income, and you can see that the per capita disposable income of the four urban areas in the center of Fuzhou is the highest, while the per capita disposable income of the neighboring cities Minqing and Lianjiang is lower. The heat map indicates the year-on-year growth rate, and it can be seen that although the per capita income of Lianjiang is not high, its year-on-year growth rate is still good and has been shown in dark red, while the growth rate of Changle district is barely visible (because its growth rate is only 8.1%) (Figure 6).


The default map data is opened in a separate 3D map page, and the window can be resized to a suitable size and stacked on the original data page, and the 2D bar chart can be inserted by right-clicking in the map area and selecting “Insert Chart→Layer 1” to select the per capita disposable income data, which is more convenient for the 2D and 3D heat map This makes it easier to display both 2D and 3D heat maps (Figure 7).


The 3D map also supports setting dynamic 3D scenes. In the map page, click “Scene Options” at the top, set the scene duration to 7.5 seconds in the window that opens, set the scene name at random, select 3.5 seconds for the effect duration, and select “Push In” for the effect. When finished, click “Play Demo”, you can see a dynamic scene effect of pushing in the earth in the current page, and the address on the map is pushed in from large to small demo, which can better show the heat data of the map (Figure 8).


Tip: How to add smaller locations to the 3D map

Sometimes we need to mark smaller locations on the map, such as in the above example, to show such locations as your own neighborhood, train station, or airport. Of course, these locations may not be recognized by the map even if the complete address is entered, and at this time, you can use the latitude and longitude method to set up. As above, first enter the name of the location in Excel, then open and enter the actual address to search, you can get the latitude and longitude values, and enter the latitude and longitude of the location in Excel as prompted. As above, add the address field in Figure 1 and select longitude or dimension in the drop-down list so that the actual address can be located precisely on Earth (Figure 9).


Leave a Comment