+ Reply to Thread
Results 1 to 3 of 3

Pivot table, clustering and heat map...

  1. #1
    Registered User
    Join Date
    08-02-2019
    Location
    Antwerp, Belgium
    MS-Off Ver
    Office 365 for Mac
    Posts
    2

    Pivot table, clustering and heat map...

    Hi everybody,

    I have a very large list, about 500,000 rows with two columns, that I would like to transform into a heat map. Column A contains numbers from 1-500,000: not continuous (i.e. with gaps), unique, and ordered from small to large. Column B contains values associated with those numbers - also numbers, ranging from about 1550 to about 1750. What I would like to do is create a heat map to show where higher values in column B are concentrated in the numbered series.

    I would like to create a pivot table where:
    - Column A (row header) brings together the numbers in batches of 10,000 (1-9,999, 10,000-19,999, ...)
    - Row 1 (header) draws from column B to create batches of 10 (1550-1559, 1560-1569, ...)
    - The data from column B are counted and put in the correct column
    - A heat map shows where higher concentrations can be found

    I'm including an example below, I hope I'm being clear - and I hope it's possible to do this in Excel...

    Many thanks in advance!
    Steven

    SOURCE

    1 1602
    2 1611
    3 1605
    4 1630
    5 1632
    6 1621
    8 1630
    9 1632
    10 1636

    PIVOT

    1600-1609 1610-1619 1629-1629 1630-1639
    1-4 2 1 0 1
    5-10 0 0 2 2

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Pivot table, clustering and heat map...

    How familiar are you with Excel's pivot table tools? https://www.excel-easy.com/data-****...ot-tables.html Steps I followed:

    1) Copy Source -> paste into a blank workbook -> Text to columns to split -> Put "A" at top of the first column and "B" at the top of the second column.
    2) Select the source -> Insert -> Pivot table
    3) A goes in the row labels field, B goes in the column labels field, Count of B goes in the Values field.
    4) Right click on the row labels -> Group -> 0 to 10 in groups of 5
    5) Right click on the column labels -> Group -> 1600 to 1640 in groups of 10
    6) Conditional formatting applied to the pivot table to get the heat map effect.

    I got different results than what you show (only 1 entry in the 5-10/1620-1629 group and 4 in the 5-10/1630-1640 group) but they seem correct (I'm not sure how you got 2 and 2 in those groups). Is that what you are trying to do?
    Last edited by MrShorty; 08-02-2019 at 03:09 PM.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    08-02-2019
    Location
    Antwerp, Belgium
    MS-Off Ver
    Office 365 for Mac
    Posts
    2

    Re: Pivot table, clustering and heat map...

    That is exactly what I'm trying to do - and you're right, I made an error doing it by hand...

    I've used pivot tables before, but I'm terrible at math and get confused easily when trying to understand what happens (also English is not my native language). You walked me through it, I'm going to experiment a bit and read the link you sent me to get a grip on this.

    Many thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Heat Map help for Pivot Table
    By dev.jajati in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 06-10-2017, 06:05 AM
  2. Replies: 6
    Last Post: 01-24-2017, 06:56 PM
  3. Heat Map using a Pivot Table (excel 2010)
    By szc in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 04-16-2015, 10:43 PM
  4. [SOLVED] Pivot table heat map
    By Jackie5467 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 11-19-2013, 09:39 AM
  5. Heat Mapping by row for a table of information (or a pivot table)
    By jacebailes in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-06-2013, 06:22 PM
  6. [SOLVED] clustering
    By Karim in forum Excel General
    Replies: 0
    Last Post: 01-11-2006, 02:00 PM
  7. clustering
    By Karim in forum Excel General
    Replies: 0
    Last Post: 01-11-2006, 01:55 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1