The human brain is programmed to read and interpret maps much better than to analyze a series of data arranged in rows and columns. That's why graphs and other ways of representing information work so well. In this post we are going to see how to create a map chart in excel and achieve that visual impact we are looking for.
This way of presenting information can be very useful in many ways. Above all, for offer geographical representation, more visual, enlightening and affordable, with which to accompany a study or academic work, or also in our professional environment, when making presentations to our superiors, our clients, etc.
When is it convenient to use this resource? There are many possible scenarios, but in general creating a map graph in Excel It is very suitable for comparing values and displaying categories in a distribution across different geographic regions. It can be used, for example, when the data we want to present is related to different locations (countries, regions, provinces, districts, postal codes, etc.).
Before continuing with the explanation, it should be noted that the possibility of creating these map graphics is available starting with version 2019 of Microsoft Excel.
Show values or categories
Excel map charts are used to display both values (quantitative data) and categories (non-numeric data), with different models available. In general, the program uses two or three colors, introducing intensity variations in them to establish degrees or separations between data ranges. Let's see two examples in the following images: value and category map graph:
- Example 1: Chart of Countries by tax revenue (values, left map). Here, each country's tax revenue is represented by a two-color gradient spectrum. Darker colors represent higher values and lighter colors represent lower values.
- Example 2: Chart of Countries by categories (categories, map on the right). In this case, the categories are displayed with a standard legend to identify each of the groups. Each country is represented with a different color.
How to create a map chart in Excel step by step
With the introduction of the Geography Data in Excel, the ability to create map graphics has been greatly simplified. Now it is much simpler, as we explain below. To explain the process, we will use as an example a column of data in which we will enter geographic values, which can be, for example, country, state, city, etc.
Geography Data in Excel
To activate this category of data we only have to select the list or column of data and, in the ribbon at the top of the page, click on the Data tab. In the next drop-down menu, we choose "Type of data" and we select «Geography».
With this, Excel will automatically convert this data into a type specifically designed to be later represented on a map.
Creating the map from the data
Added to the data column a second column in which the data or categories are entered, as the case may be. With this, we now have everything necessary to create a map graph and obtain a graphical representation of the data.
To do this, we can select any cell within the data range. Then we go to the tab "Insert", where we first select "Graphics", then «Maps» and finally the option «Filled map».
Then we will be able to review the appearance of the map that will be generated and, if we like it, confirm by clicking "Accept". If we want to add a new geographic value to the table or delete one from the table, Excel will update the linked map chart automatically.
Customizing the appearance of the map
After creating our map chart in Excel, we still have some interesting options to adjust its design to our tastes and preferences. Simply click on the map and use the tab options «Graphic design» (or “Format” on the ribbon).
Another way to customize the map-chart is to double-click on the chart to open the “Format” box. object". In it, in the right column, a series of options appears:
- Map projection type. By default, Excel will choose the one it considers most efficient.
- Map area (automatic, global, regions with data only).
- Map labels (none, show all, only best fit).
- Colour (flat or sequential with 2 or 3 colors).
Some details about map graphs in Excel
Once the standard method for creating these charts in Excel has been explained, we must mention some situations that we may encounter and how to resolve them:
- If we work with data that may have more than a single geographic locationa, we will have to add a top level detail column to the data.
- We will need to have an online connection if we want to create new maps or append data to existing maps.
- Map graphics only allow one-dimensional display. To get something more sophisticated you have to use the function Excel 3D Map.