Heat Map using a Pivot Table (excel 2010)

Hi,

I am trying to create a Heat Map using a Pivot Table which has Day of Week as the Columns and Hour of Day as the Rows. Once the data is there, I will use conditional formatting to change the color of the data to show the "hot spots".

I would like to have it show all of the days of the week across 7 columns, even if there is no data for a certain day. I have seen that using a Group may do this, but my data source is open and will have some blank cells so that seems to preclude the use of Groups.

I would also like to have it show all of the hours of the day as well (actually will be 8 (am) through 11 (pm), again, even if there is no data in that particular hour. including all of the hours and all of the days of the week creates a better visual.

I am using a pivot table as I can then use other info from the data source (i.e.: store number and/or incident type) as a filter. I have tried to create it in a worksheet, but cannot figure out how to be able to filter the data using more than one drop down box.

Hopefully this is enough info to come up with an idea on how I can do this.

Thanks in advance.

Thank you for responding, I appreciate it. (actually typing this response for the third time... ugh)

Anyway, I had seen those two pages and watched the video, but I already have a heat map created.

I am looking for a way to have a user be able to do a multiple aspect search via drop downs to be able to choose the information (based on columns in the original dataset). A pivot table starts to do this, but if there is no data on a Sunday, the pivot table does not include a column for Sunday. I would like a way to have it show all of the days of the week along with the 8:00am to 11:00pm time frame that I need. I have attached a picture of my current heat map.

Heat Map.jpg

Here is the formula that I am currently using which allows for one drop down box: =COUNTIFS('Case Name'!$F$3:$F$37,S$10,'Case Name'!$H$3:$H$37,$D11,'Case Name'!$B$3:$B$37,'Heat Map'!$S$8)

Really looking for a way to be able to choose any or none of County, Company, Store and Incident Type..

Thanks again for the response, it is appreciated.

I will try that to see how dummy rows may affect the rest of the data being pulled from the main spreadsheet.

If I stay with the heat map that is in excel (see screen print above) and do not use a pivot table, is there a way to have the cells reacting off of two drop downs?

This is my formula for one drop down:

=COUNTIFS('Case Name'!$F$3:$F$37,S$10,'Case Name'!$H$3:$H$37,$D11,'Case Name'!$B$3:$B$37,'Heat Map'!$S$8)