Birth Dates Heat Map in Excel – How To

First off whoever has birthday on July the 7th my best wishes to all of them. You people have a very interesting birth date. 7 and 7. Everyone’s birthday is interesting in one or the other. But what if you like to know how the birthdays of employee in an organization are spread over the year or what are the common days of birthday around the year. We can do this using birth date heat map.

So what is heat map?

Well the word itself says loud what is it about. Basically a graphical representation of how data spreads between two extremes i.e. Hot and Not Hot or Cold. In this visual analysis, data is put head-to-head with a certain criteria and the resultant values are then plotted in colors for each criteria or category representing the frequency of variable meeting certain criteria. In simple words it counts the occurrences and same count is then used to give color.

Table of Contents

• So what is heat map?
• RANDBETWEEN – Randomly generating Dates in Excel
• Bonus Tip – Format as Date
• Bonus Tip – What the “#####” is that?
• Random dates in Excel – Better approach
• Step-by-Step
• Bonus Tip – Replace formulas with values
  • Getting Month’s days
  • Hiding cell content

If my words don’t make sense then just have a peek at the right and its easily understood.

Our today’s goal is to do the same but using our heart favourite tool – Microsoft Excel!

It seems an up hill task but it is fairly easy and takes just few minutes.

If you are thinking of doing this of an organization where you work then you need to have access to some confidential data as birth dates are a strict privacy issue. So, be very careful. But to learn we can go around this problem easily by generating some random dates.

RANDBETWEEN – Randomly generating Dates in Excel

Syntax of RANDBETWEEN formula is:

=RANDBETWEEN(Bottom,Top)

Bottom: the lowest or floor value

Top: the highest of peek value

So basically we are giving a range to excel in which excel can randomly select any value in that range. So you can hard code figures in as bottom and top values in the formula and get the value. For example:

=RANDBETWEEN(100,200)

It will give you anything randomly. Random formulas generate random values every time excel workbook is updated i.e. every time a change is made in any cell within workbook then random formula give you a new random value. You can manually refresh workbook by pressing F9 key.

However, randomizing dates is a little tricky. If you put dates as bottom and top values, it will end in an error. To fix this we can use DATE function to specify the dates. In that case our RANDBETWEEN formula will look like this:

=RANDBETWEEN(  Date(year, month, day)  ,  Date(year,month,day)  )

Please note I intentionally put spaces to make each part clearly visible and how it is structured. In Excel you put this formula without spaces.

This way excel will give you a random date between two dates. Test fire it by putting the following formula in any cell in practice worksheet:

=RANDBETWEEN(DATE(2013,1,1),DATE(2013,12,31))

Bonus Tip – Format as Date

Not getting really the dates as a result of above formula?

Nothing to worry about. Excel actually calculates the date as a number of dates from January 01, 1900. Resultant is then given a date format to show it in the format of mm/dd/yyyy or other. So just in case you get numbers instead of actual date then probably this is the reason. To solve this simply select the cell that contains the value and hit Ctrl+Shift+3. You can also select date format by going to Home tab>Number group>Format drop down and select Short date or Long date as you deem fit.

Bonus Tip – What the “#####” is that?

Getting ##### well it only shows that the column width is not appropriate to display the whole value. Just increase the width by dragging the column or double clicking the column margin.

Random dates in Excel – Better approach

But even better randomization of dates is achieved by reversing the formula order

=DATE(  RANDBETWEEN(bottom,top)  ,  RANDBETWEEN(bottom,top)  ,  RANDBETWEEN(bottom,top)  )

Following excel spell helps you understand how formula is constructed:

DATE(	year	,	month	,	day	)
DATE(	RANDBETWEEN(bottom,top)	,	RANDBETWEEN(bottom,top)	,	RANDBETWEEN(bottom,top)	)

So, use any of the approach to have the basic data for this tutorial. I will explain the second approach in step-by-step excellence

Step-by-Step

Open Excel program and name one worksheet as Heatmap and the second one as Dates. You can rename worksheets by right clicking on tab and select rename. Type and press Enter to complete renaming.

Step 1: Open worksheet named Dates. Have A1 as the active cell. Go to name box and type A2000 and press SHIFT+Enter. This will select all 2000 rows instantly.

Step 2: Press F2 and in the formula bar paste following formula and press Ctrl+Enter to quickly paste the formula in all 2000 cells:

=DATE(RANDBETWEEN(1905,1995),RANDBETWEEN(1,12),RANDBETWEEN(1,31))

Step 3: Having the cells selected hit Ctrl+F3. Name manager dialogue box will open. Click Newand define the range as: dates

Up to this point we have prepared our base data that we will use to create birth date heat map.

Now we need days of month and months and in between a matrix where we can create the heat map. To do this lets move on.

Bonus Tip – Replace formulas with values

In the above steps we have achieved the dates but there is one nuisance. Every time you make any change in the worksheet dates gets updated. Well this is not a defacto fault as RANDBETWEEN formula is designed to give you random numbers every time all the time.

But it is better if dates don’t change. So having dates selected. You can easily reselect them by going to name box and selecting dates. And all 2000 entries will be selected instantly. Hit Ctrl+C and then hit Alt+Ctrl+V. From the dialogue box click “Values” radio button and click OK.

This will get you dates that are not getting randomized every time.

Getting Month’s days

Step 4: Open worksheet name heat map. Click cell A2 and go to name box. Type A32 and hit Shift+Enter to select the cells.

Step 5: Press F2 to enter edit mode and put the following formula and press Ctrl+Enter

=ROW()-1

ROW formula gives the respective row number of each cell. However as we are starting from A2 we have reduced it by 1. This gives us perfect 31 days number easily. You could have done that by dragging the fill handler to cell 32 and selecting fill series from the smart tag. But you just excelled at a new way! 🙂

Step 6: In cell B1 type January and drag the fill handler until it shows December.

Step 7: In cell B2 put this formula:

=SUMPRODUCT(--(MONTH(dates)=COLUMN()-1)*--(DAY(dates)=$A2))

Step 8: Drag the fill handler to December column. Double click the fill handler to fill the formula down all the rows.

To understand how SUMPRODUCT function works please check read my previous article on conditional sum using SUMPRODUCT. To learn more usage of SUMPRODUCT function visit this page.

Step 9: Having data selected in the Home tab click conditional formatting in the styles group. From the drop down go to color scales and select the one you like. Colors all the way! Heat map almost ready. Follow the last step to heat up it all!

Hiding cell content

Step 10: Make sure the whole data is still selected. Hit Ctrl+1. From the list at left select Custom. On the right under type by default General is written. Remove the word general and type in:

;;;

Step 11: Rejoice!

Yes! Thats it! Once you tap semi colon key thrice you will see the number under Sample disappear. Click OK.

Your heat map is now all ready.

So there you have it! A reason to celebrate in hot summer days with a heat map created in Excel 🙂

Let me know if you have other ways to make it or think of making it even better let me know in the comment section below