Excel with maps, sparklines, and traffic light KPIs for powerful dashboards

If you work with data in Excel and want to go beyond the typical bar chart, the maps, sparklines and traffic light KPIs They are a brutal combination for create clear visual panelsElegant and highly professional. With these resources, you can go from a sheet full of numbers to a dashboard that anyone can understand at a glance.

In the following sections we will see How to use maps in Excel, its most important formatting options, how to integrate them with Traffic light type KPI and small trend lines (sparklines) to tell the complete story of your data. All explained in simple language, with practical examples and attention to the formatting details that make all the difference.

What is a map in Excel and what is it used for in a KPI dashboard?

A map chart in Excel is a type of visualization that represents values ​​or categories associated with geographic regions (countries, autonomous communities, provinces, states, etc.) coloring those areas according to the data. It's perfect for dashboards where you need to see how your indicators are distributed by region.

In a dashboard with maps, sparklines, and traffic light-based KPIs, the map typically acts as geographical context layerIt allows you to identify at a glance which regions stand out (for better or for worse) and then delve into details with other charts or numerical indicators.

Excel can represent both numerical values (sales, incidents, ratios) such as categories (segments, market type, branch status) on the map. Depending on your choices, you'll have different color and format options to ensure the map effectively conveys the message.

This type of chart is especially useful when you want to combine it with Traffic light KPIs (green, amber, red) that summarize performance and with sparklines that show the evolution over time, achieving a very visual, organized and easy-to-interpret dashboard for managers or clients.

Series options in Excel maps

The calls Standard options They are the heart of the map format in Excel. From there you choose the projection, zoom level, label visibility, and the type of color scale you want to apply to your geographic data.

These options change slightly depending on whether your map is based on numerical values (for example, total sales by country) or in categories (for example, expanding, stable, or declining region). Even so, the general logic is the same: tell Excel how to represent the information in your table on the map.

Mastering these configurations allows you to build maps that truly help in decision-making, avoiding typical problems such as maps saturated with text, strange projections or poorly chosen color scales that confuse more than they clarify.

Map projection: how the world is drawn

The map projection defines the mathematical method by which Excel converts the Earth's spherical surface into a planeIn other words, how it "flattens" the world on the screen. Although Excel usually chooses the projection it considers appropriate, you can change it depending on your needs.

Common options include projections such as Mercator, Miller, Albers and RobinsonEach one balances the size and shape of countries differently, so some are more suitable for seeing the whole world and others for analyzing specific areas with less distortion.

Not all projections are available on all maps, as The choice depends on the geographical area. that you are representing. For example, if your map only shows a specific country or region, Excel may restrict the options that don't make sense for that level of detail.

If your Excel panel focuses on a broad area (such as several continents or the entire world), it makes sense to try different projections until you find the one that best suits your needs. the one that offers a more intuitive visual reading experience for your end users.

Map area: zoom level and geographic scope

The setting of Map area Control which part of the world is displayed and at what level of zoom. Here you define whether you want a view restricted to a country or region, or if you prefer a more global map that includes many territories.

Excel automatically selects the area it thinks best fits the data you've entered, but you can force zoom towards a more local or more global level, provided the option is available according to the scope of your map.

For example, if your traffic light KPIs are calculated for each autonomous community, you might want to set the area to the national level, so that Spain occupies most of the graph And don't waste space showing regions that don't add anything to your experience.

However, if the dashboard compares results by country worldwide, it's better to opt for a world map area that allows you to see at a glance which markets stand out and then complement it with sparklines and numerical KPIs for fine analysis.

Map labels: control region names

Map labels are used to show geographical names over the colored areas (countries, regions, states, provinces…). This adjustment is key to making your map legible without turning it into an unreadable jumble of text.

Excel lets you decide if you want do not show any labelsYou can choose to show only the regions that fit well without crowding, or to show all possible regions. Depending on the size of the chart and the density of regions, some options will work better than others.

The program itself dynamically adjusts the number of labels based on the available space: the larger the map On the sheet, more names will be able to be clearly displayed, without overlapping or cutting them out.

In dashboards with many elements (maps, tables, traffic light KPIs, sparklines…) it is usually advisable to let Excel show only the labels when there is enough space, supporting the rest of the geographical identification with a good legend or with the original data table.

Series color in value-based maps

When the map is fed by numerical valuesYou can take advantage of the default color options to create visually meaningful color scales. This is where you decide whether you prefer a gradient that goes from low to high or a diverging scale that highlights intermediate values.

Excel primarily offers two schemes: Sequential (2 colors)which is the default and is ideal for viewing intervals from smallest to largest, and Divergent (3 colors)which is often used when you want to emphasize a central area (for example, the target) and highlight which regions are worse and which are better.

Additionally, you can modify the parameters of Minimum, Midpoint and Maximumas well as the exact colors associated with each of those levels. This allows you to adapt the map to the KPIs you are tracking or to your company's corporate color palette.

A very powerful use in traffic light-based KPI panels is to choose colors that visually interact with the traffic lightFor example, cool tones for areas with acceptable results and warmer or more intense colors to highlight where there are problems or where the objectives are greatly exceeded.

Color format in category maps

When your map is based on categories instead of values (for example, if you assign each region a label such as "High priority", "Normal", "At risk"), the color behavior changes significantly.

In this case, the Standard Colors section for configuring numerical scales is not available, but you can modify the colors category by category so that each type is identified clearly and intuitively.

To do this, you can select the data point you're interested in directly from the map legend or click on the area itself on the map. Then, in the Excel ribbon, within Chart Tools > FormattingYou can change the shape fill of that category.

You can adjust these colors from the task pane Object Format, by accessing the Format Data Point dialog box and choosing the Fill options that best suit you within the Excel color palette or with custom shades.

This approach is perfect when you want to each state of your traffic light KPI have an immediate color on the map (green for correct, yellow for attention, red for critical, for example), achieving a consistent visual reading throughout the dashboard.

How to access the serial options and configure the format

To truly get the most out of maps in Excel, it's important to know where all these options are controlled and how to navigate the formatting panel with ease.

Once you have created your map chart from the data table, you can open the formatting area in two quick ways: right-clicking on the outer part of the map and by choosing “Format chart area”, or by double-clicking on that area of ​​the chart.

By doing so, you will see the following appear on the right side of the Excel window: "Object Format" task paneFrom there you can switch between series options, graph area, fill, borders and other important visual aspects.

If the Serial Options are not displayed initially, click on the small serial options expander which appears in the panel itself, and choose the data series that corresponds to your map values. This is especially useful when you have several overlapping elements or combined charts.

Once you have selected the correct series, press the button. “Standard Options” within the panel. That's where you can modify projection, map area, labels, and color options, both for values ​​and categories, as we've seen in previous sections.

Integrate maps with traffic light KPIs in Excel

Traffic light KPIs are indicators that They use traffic light-type colors (typically green, yellow, and red) to immediately show whether a value is within, near, or outside the set target. Integrating them with maps in Excel provides a powerful view of performance by region.

A common way to do this is to calculate one or more KPIs for each geographical area (for example, margin, growth and market shareand apply conditional formatting rules to them so that they are displayed with traffic light icons or fill colors in the cells of a summary table.

These same KPIs can be represented on the map using consistent color scales, so that when looking at the dashboard, The color of the cell and the color of the map region tell the same storyThis avoids confusion and helps the user to "read" the dashboard almost without thinking.

When combining maps with traffic light KPIs, it's best to decide from the outset. What will the color logic be? (for example, green when it exceeds the target, yellow when it is close, red if it is far below) and respect it in all the charts and elements of the panel.

Thus, when moving from a data table to a map, or from a map to a block of key indicators, the user always maintains the same visual reference and can compare regions very quickly and reliably.

Use of sparklines to show trends by region

Sparklines are small miniature lines or columns that They are inserted directly into a cell Excel to represent a temporary trendThey are perfect to accompany maps and traffic light KPIs without taking up much space.

Imagine that your map shows current sales by province and the traffic light KPIs indicate if each province is on target Or not. With a sparkline next to it, you can add the evolution of the last few months to understand if the situation is stable, improving, or deteriorating.

These small graphs are created from the tab Insert > Sparklines (sparklines) and can be line, column, or gain/loss. They are then adjusted with quick formatting: colors, thickness, high or low point markers, etc.

By integrating them with your map, the idea is that the user can choose a region on the map And right next to it, in the table, you'll see its associated sparkline, its traffic light-based KPI, and the numerical value. This combination transforms a simple color map into a very powerful analytics dashboard.

Furthermore, being lightweight elements, sparklines fit very well into panels with many components, maintaining a clean and tidy aesthetic without falling into the trap of excessively large graphics that can overwhelm the eyes.

Other formatting aspects in map graphics

Beyond the standard options, Excel maps support virtually the same styles and graphic elements than any other type of display: titles, captions, data labels, backgrounds, borders, and quick styles.

From the ribbon and the formatting panel you can apply predefined chart styles that adjust colors, shadows and borders, or customize every detail so that the map fits with the overall design of your dashboard or with your organization's corporate image.

It is also possible to add or remove elements such as legends, chart titles, and labels Regarding the data: In a panel with many components, it's usually a good idea to use short, descriptive titles, and only use legends when they provide useful information without cluttering the visible area.

Similarly, you can adjust the plot area and the graph area to balance the space the map occupies in relation to other elements such as KPI tables and columns with sparklines, maintaining a harmonious composition.

Remember that, ultimately, the goal of this whole format is not to "decorate" the map, but facilitate reading and decision-makingLess is more: if a visual effect doesn't add clarity, it's probably unnecessary.

When to use maps, sparklines, and traffic light KPIs in Excel

Excel maps work especially well when your data is naturally linked to a geographical dimensionSales by region, incidents by province, sales offices, logistics centers, service areas, etc.

If the goal of your dashboard is for a manager to quickly see where the problems or opportunities are concentratedThe map is the perfect first visual filter, while the traffic light KPIs and sparklines provide detail without losing the overall view.

Conversely, if your analysis doesn't have a clear geographical component, basing the panel on a map might not make sense, and it might be better to opt for... pivot tables, bar charts, line charts or scatter chartsreserving sparklines and KPIs to highlight key points.

The key is to ask yourself: “Does geography provide relevant information for this indicator?” If the answer is yes, a well-designed Excel map, combined with traffic light KPIs and small trends, can make a difference versus a flat report full of numbers.

A good dashboard with maps, sparklines, and traffic light-based KPIs in Excel becomes a tool where see, understand and decide They merge in a matter of seconds, without needing to dive through hundreds of cells.