Simple unpivot example in excel

One unpivoting example

(kohe teen sama loo ka eesti keeles)

Few days ago I had challenge to unpivot one quite complex crosstable.

And challenge was to use formulas to repeat in future with new data and prevent any macros


I did it!


I made simplification as example I’d like to share (there is sample workbook)


Some step-by-step explanation

(sorry – I use ee-location, so in formulas I use ; rather than , and [#header..] is replace to [#päised..])


On my sample I have table with names and 8 columns with attributes


Name, Attr1, Attr2, Attr3, …

Henn, , 3, 5

Ants, 1, , 4

Peeter, , , 7


Unpivoting result have to be something like

Name, AttrName, AttrValue

Henn, Attr2, 3

Henn, Attr3, 5

Ants, Attr1, 1

Ants, Attr3, 4

Peeter, Attr3, 7


1st I create intermediate table. Actually I can extend origin table, but for safety I created new one

btw – in origin – the starting cross wasn’t table and contains several sections

  • 1st column:     Nr =ROW()-ROW(Tabel2)+1

this is trick to generate numbers to table – insensitive of table placement

  • 2nd column:     Nimi =VLOOKUP([@Nr];Tabel1;MATCH(Tabel2[#Päised];Tabel1[#Päised];0);FALSE)

this is another trick to get with VLOOKUP matching attribute from lookup table

  • 3rd in number of attributes > 0


this is actually working with different table with different number of attributes in my actual example I had 17 attributes and with different names.

There I simply count how many attributes current line have

  • 4th is calculate starting rownumber in resulting table


this is “classical” running total formula working in table

  • 5th is trick – bitmask showing what attributes exist on particular row






This SUMPRODUCT calculates bitmask on the way where each bit shows presence of attribute.

One vector is {true,false,…} vector comparing attribs

The second vector is list of 2-powers (in descending order) {256, 128, 64, …}

  • 6th step is to create same bitmask in binary format

Bitmask =DEC2BIN([@Mask];8)&”0″

on case more than 9 attributes – dec2bin have to be broken for 2 parts becouse limitation (? why) of dec2bin

This finalizes intermediate table


NExt I create resulting table


  • 1st     NR =ROW()-ROW(Tabel3)+1 — this is same as in previous table

it’s question of taste – on some cases I create max size table and unhide unneccessary rows with some IF-s. In that case I simply extend the table with autogenerated rows

  • 2nd     LineNr =MATCH([@Nr];Tabel2[ResLine];1)

this gives me line number in origin table 🙂

  • 3rd     ResNR =COUNTIF(Tabel3[[#Päised];[LineNr]]:[@LineNr];[@LineNr])

gives me ordinar number of attribute


all the above garantees me that line number will never bigger than actual number of original lines and attribute number is never bigger than

number of attributes and start with 1.


  • 4th     AtrCol =IF([@ResNr]=1;




this finds the position of “1” in my bitmap mask – and actually position of attribute column in table

when attribute number = 1, I simply find “1” in bitmask

for next attributes I give additional “starting point” for find reading this from line above


Folllowing is simple

Nimi =VLOOKUP([@LineNr];Tabel1;MATCH(Tabel3[#Päised];Tabel1[#Päised];0);FALSE) — this is “old” trick

AttrName =OFFSET(Tabel1[[#Päised];[Attr1]];0;[@AttrCol]-1) — this read attribute name from Header

AttrValue =OFFSET(Tabel1[[#Päised];[Attr1]];[@LineNr];[@AttrCol]-1) — this read attribute value from correct line and column


Any comment are welcome


Rubriigid: Excel. Salvesta püsiviide oma järjehoidjasse.

Lisa kommentaar

Täida nõutavad väljad või kliki ikoonile, et sisse logida: Logo

Sa kommenteerid kasutades oma kontot. Logi välja / Muuda )

Twitter picture

Sa kommenteerid kasutades oma Twitter kontot. Logi välja / Muuda )

Facebook photo

Sa kommenteerid kasutades oma Facebook kontot. Logi välja / Muuda )

Google+ photo

Sa kommenteerid kasutades oma Google+ kontot. Logi välja / Muuda )

Connecting to %s