How to edit colors by data range in U.S. State Heat Map Excel Template?

Posted on
U.S. State Heat Map - Updated Map

In the previous article, we learnt how to change the gradient colors used in the U.S. Geographic State Heat Map Excel Template. In this article, we will edit the colors used in the ‘Colors by Data Range’ option in the same template.

Please visit product page to understand the features of the U.S. Geographic State Heat Map Excel Template.

 

The template allows control over the color used for a range of data values. In order to enable that option, choose YES in the drop down menu for ‘USE DATA RANGES?”.

U.S. State Heat Map - Choose YES for Data Ranges
U.S. State Heat Map – Choose YES for Data Ranges

 

The map will update now to reflect the data range table. For example, states with data value from 0 to less than 1001 will be colored Green, while states from 1001 to less than 2501 will be colored blue.

U.S. State Heat Map - Updated Map
U.S. State Heat Map – Updated Map

 

You can have up to 6 data ranges (in the image above, we have used 5 ranges). To fill those 6 ranges, the template provides seven colors to choose from: Green, Blue, Gray, Orange, Yellow, Red and White.

U.S. State Heat Map - 7 Colors
U.S. State Heat Map – 7 Colors

 

It is natural for each of us to have different preferences when it comes to colors. If you would prefer to change one of the available colors, it is easy to do. Let’s see how to do that step by step. In this tutorial, we will replace Blue color with Purple.

 

VIDEO DEMO

 

  1. Select cells H8 to H13. These are six cells where we choose the colors for the 6 data ranges.
    U.S. State Heat Map - Select 6 cells
    U.S. State Heat Map – Select 6 cells

     

  2. Click on ‘Data’ ribbon and then choose Data Validation –> Data Validation..
    U.S. State Heat Map - Data Validation Menu
    U.S. State Heat Map – Data Validation Menu

     

  3. Let’s edit the ‘Source’ field and replace BLUE with PURPLE (or any other color name you would prefer). Then, click OK. This step makes PURPLE one of the colors in the drop down.
    U.S. State Heat Map - Replace color in Data Validation
    U.S. State Heat Map – Replace color in Data Validation

     

  4. From the ‘Home’ ribbon, choose Conditional Formatting menu and choose ‘Manage Rules’
    U.S. State Heat Map - Conditional Formatting menu
    U.S. State Heat Map – Conditional Formatting menu

     

  5. Choose Sheet Map
    U.S. State Heat Map - Choose Sheet Map
    U.S. State Heat Map – Choose Sheet Map

     

  6. Click on the rule that has ‘Blue’ Color, as we are trying to replace Blue with Purple.
    U.S. State Heat Map - Select Conditional Formatting Rule
    U.S. State Heat Map – Select Conditional Formatting Rule

     

  7. Edit the formula and replace ‘BLUE’ with ‘PURPLE’. After that, click on the ‘Format’ button.
    U.S. State Heat Map - Change Formula
    U.S. State Heat Map – Change Formula

     

  8. Choose the Purple color in the Fill section.  Then click OK and OK in the next 2 windows that pop up.
    U.S. State Heat Map - Choose Purple Fill color
    U.S. State Heat Map – Choose Purple Fill color

     

  9. Now we have implemented the new Purple color. In order to see this in action, choose PURPLE for one of the ranges.
    U.S. State Heat Map - Use the new color
    U.S. State Heat Map – Use the new color

     

  10. The map will appear like this now, with all the states with data value from 1001 to less than 2501 colored in Purple.

    U.S. State Heat Map - Updated Map with Purple
    U.S. State Heat Map – Updated Map with Purple

 

I am sure for many of you, Purple may not be your color of choice. 🙂 Please follow the steps above and use any color of your choice to visualize data on the map as you prefer. If you have any questions or suggestions, please leave them in the comments. I look forward to hearing from you.

How to change Gradient colors in U.S. State Heat Map Excel Template?

Posted on
U.S. State Heat Map with 2 Color Scale
In this article, we will learn how to change the gradient colors used in the U.S. Geographic State Heat Map Excel Template. Using the template, you can create a geographic heat map at the state level for 50 states of U.S using your own data.

HOW TO CHANGE GRADIENT COLORS

The template comes with the default red to green gradient colors. If you like the gradient style of colors, but would like to change the actual gradient colours used, please follow the steps outlined below.

VIDEO DEMO

1. Open the Conditional formatting menu in Home ribbon and choose ‘Manage Rules’.

U.S. State Heat Map - Conditional Formatting Menu
U.S. State Heat Map – Conditional Formatting Menu

2. Choose the sheet: Map

U.S. State Heat Map - Conditional Formatting Choose Sheet Map
U.S. State Heat Map – Conditional Formatting Choose Sheet Map

 

3. Select the Gradient Color Scale rule and then click Edit Rule.

U.S. State Heat Map - Conditional Formatting Choose Graded Color Scale Rule
U.S. State Heat Map – Conditional Formatting Choose Graded Color Scale Rule

 

4. Change the colors (for minimum, midpoint and maximum) as needed.

U.S. State Heat Map - Conditional Formatting - Change Colors
U.S. State Heat Map – Conditional Formatting – Change Colors

 

If you are familiar with Excel, you can use other conditional formatting rules and settings too. For example, you can also choose the format style be 2-Color Scale instead of the 3-Color Scale.

U.S. State Heat Map - 2 Color Scale
U.S. State Heat Map – 2 Color Scale

 

That will change the map visualization to appear like this.

U.S. State Heat Map with 2 Color Scale
U.S. State Heat Map with 2 Color Scale

 

Whenever you change colors, please update the NOTE so that the footnote that appears on the map aligns with the color rule applied.

U.S. State Heat Map - Note
U.S. State Heat Map – Note

 

If you have any questions, please leave them in the comments below.

Activity Pattern Heat Maps – Free Excel Template

Posted on
Activity Pattern Heat Maps - Excel Template - By Month and Day

This Excel template can be used to visualize activity patterns. Some of the scenarios where you could use this would be visualizing call arrival patterns in call centers, support request arrival patterns in contact/support/service centers, website traffic and order placements. By understanding the arrival patterns, you could make decisions around staffing (in contact/call center example) and website maintenance timing (website traffic example). This template uses simple formulas and some conditional formatting.

 

FREE DOWNLOAD

Activity Pattern Heat Maps Excel Template

 

VIDEO DEMO

 

DATA INPUT

Just enter your activity date-time data in column A in the table. The columns with green labels are calculated using formulas. Please do not modify them.

Activity Pattern Heat Maps - Excel Template - Input
Activity Pattern Heat Maps – Excel Template – Input

The visualizations on the right will update. It is just a count of activities in each cell. For example, Jan month and day of 5th will represent the number of activities on 5th of January (from the data you entered). The Grader Color Scale is used where dark red indicates the lowest value and dark green represents the highest. You can edit the conditional formatting rules if you would like a different color scheme.

BY MONTH AND DAY

Activity Pattern Heat Maps - Excel Template - By Month and Day
Activity Pattern Heat Maps – Excel Template – By Month and Day

BY HOUR OF DAY AND WEEKDAY

Google Analytics provides a similar visualization as well.

Activity Pattern Heat Map - Excel Template - Hour Of Day
Activity Pattern Heat Map – Excel Template – Hour Of Day

Do you find this template useful? This is a very simple and basic attempt at visualizing activity patterns. I look forward to your feedback to expand on this template to accommodate other common use cases. Please share your thoughts in the comments below.

U.S. Geographic State Heat Map – Excel Template – Support

Posted on
Welcome to the support page for the U.S. Geographic State Heat Map Excel template. You can create a geographic heat map at the state level for 50 states of U.S using your own data.

REQUIREMENTS

You need a copy of Microsoft Excel to open this file. The product has been tested in
  • Microsoft Excel 2007 and above (2010, 2013, 2016..)for Windows
  • Microsoft Excel 2011 and above (2016) for Mac

FEATURES

  • Instant Heat maps in Excel
  • Print or Save as PDF
  • Copy Heat map and paste it in other applications such as PowerPoint and Word
  • Accepts State full names as well as standard abbreviations
  • By default creates gradient scale heat map. You can also customize by selecting 6 different data ranges and choosing custom colors (from 7 different colors)
  • Displays data labels (that can be different from the data) on the map
  • Customize title and note as needed

 

Please see below for a list of support articles about this template.

  1. How to change Gradient Colors?
  2. How to change colors by data ranges?

If you have any questions about this Excel template, please leave a comment and I will respond as soon as I can. Thank you.