In this file, the source data sheet is a pasted special pivot table.  The task is to convert this pivot table data layout to the result shown in the “Desired result” sheet.  Effectively, we need to convert a matrix like data layout to a tabular layout.  We need to “denormalise” the pivot table.

Solution for MS Excel 2010 and higher versions

If you are using the Power Query add-in, then a few simple steps (no formulas) can solve this problem.  The result will be dynamic and refreshable (just as in a Pivot Table).  You may refer to Power Query result worksheet.

You may watch a short video of my solution here

Solution for all versions of MS Excel

1. Select A6:F234 on the Source Data worksheet
2. Press Ctrl+G > Special > Blanks > OK.  This process will select all blank cells in range A6:F234
3. With the selection in place, press the = key
4. Press the up arrow key
5. Press Ctrl+Enter.  You will now see all blanks cells filled up the values just above.
6. Select A6:F234, copy the range and paste them as values (Alt+E+S+V)

To denormaise the pivot table, the multiple columns appearing in the row labels area of the pivot table (range A6:F234) will need to be crunched into one column by concatenating.  The result is shown in G7:G35 of “Source data adjusted” sheet.  Likewise, the multiple rows appearing in the columns labels area of the pivot table (range G4:DE5) will need to be crunched into one row by concatenating.  The result is shown in H6:DF6 of “Source data adjusted” sheet.

The process of denormalising is as follows:

1. Select range G6:DF235 of the “Source data adjusted” sheet
2. Press Alt+D+P > Multiple Consolidation ranges > Next
3. Select “I will create the page fields” > Next
4. In the range, box, select G6:DF235 > Add > Next
5. Select New Worksheet > Finish.  A new worksheet will get created with a pivot table in it.
6. From the pivot table field list, uncheck Row and Column.  Only Value should remain checked.  The output should appear as shown in “Sheet2”
7. Double click on 6167 in the pivot table.  Another sheet (sheet3) will open up with data in three columns
8. Insert five columns after column A
9. Select range A2:A23588 and go to Data > Text to columns > Delimited > Next > Other > : > Next > Finish
10. Insert one column after column G
11. Select range G2:G23588 and go to Data > Text to columns > Delimited > Next > Other > : > Next > Finish
12. Give meaningful headings in range A1:I1

To exactly match the data on the “Desired Result” sheet, select range A1:I23588 on “Sheet3” and sort column E (CFA code) in ascending order.

• Manik Nag says:

Dear Ashish,

Thank you indeed, you are a genious! it worked

Can you plz explain this process with the help of diagram. it difficult to understand.

• veera says:

Sir,
I have It like this
Machine ID Part1 Part2 Part3
A123 Spanner Gear12 Chain
B3 O-ring oil-22
C3 Chain

I need like this way pls
Machine ID Part
A123 Spanner
A123 Gear12
A123 Chain
B3 O-ring
B3 oil-22
C3 Chain

• Hi,

• Md. Rokibul Alam says:

Please follow the link where I have attached my document. I have a list of data in the tab named “Daily EV-DO Utilization”. I want to filter these data and only keep which are greater than 69.99%. In the second tab named “Filtered” I have shown the data that I need after applying filter. I have attached this file to make it easy to understand what I want to do.

• Hi,

Try this

1. Create a copy of sheet named “Daily EV-DO Utilization”. Name of the newly copied sheet is “Daily EV-DO Utilization – copy”
2. Insert a column before column D and in cell D4, enter this formula =A4&”:”&B4&”:”&C4. Copy this formula down
3. Insert a row before row 4 and in cell E3, enter this formula =E1&”:”&E2. Copy this formula to the right
4. In cell D3, type Dummy
5. Press Alt+D+P > Multiple Consolidation ranges > Next > I will create Page Fields > Next
6. In the range, select D3:J503 > Next > New Worksheet > Finish
7. From the Pivot Table Field List box, uncheck Row and Column (The Pivot Table should appear as you can see in Sheet1 of this workbook).
8. Double click on 1626
9. In the resulting worksheet, inserts two columns before column B
10. Select range A2:A3001 and go to Data > Text to columns > Delimited > : > Finish
11. Now insert one column before column E
11. Select range D2:D3001 and go to Data > Text to columns > Delimited > : > Finish
12. Give meaningful column headings in A1:F1
13. Format last column as % ages (The Table should appear as you can see in Sheet2 of this workbook)
14. Filter column F on >66.99%

Hope this helps.

• Md. Rokibul Alam says:

Dear Ashish Mathur.

Thank you very much. Your suggestion worked for me a lot. Need a bit help more regarding arranging the sheet 2 as it is in the “Utilization” tab at the attached link below:

http://www.mediafire.com/view/?rks2rznmc7utswc

Thanking,
Md. Rokibul Alam

• Hi,

After you have completed the steps of Data > Text to columns and Filtering, copy the filtered data to a new sheet and create a Pivot Table from that filtered data. Drag BTS Name, BSC and Cell ID to the Row labels. Drag Carrier and Sector to the columns labels and Values to the Value area of the Pivot Table.

• Md. Rokibul Alam says:

Dear Ashish Mathur,

Many many thanks. I have got what I wanted to do by following your suggestion.
Again thank you.

Regards,
Md.Rokibul Alam

• Paul says:

Very interesting article! Unfortunately, I struggled with apply this logic to the crosstab excel file I’m working with. In my case the majority of the fields are blank, which is accurate. I’m really trying to condense the data down just to the combinations that have values and make it easier to read/understand.

The file is at:

Can you help or offer suggestions?

Thanks

• Hi,

1. While on any cell of the User Group Permissions sheet, I pressed Alt+D+P > Multiple Consolidation Ranges > Next > I will create page Fields > Next
2. Select range A1:DM1864 > Add > Next > New Worksheet > Finish
3. In the Pivot Table created on the new sheet (sheet1), uncheck Rows and Columns in the Pivot Table Field List (only value should be ticked in the Pivot Table Field List box). The Pivot Table will now show a figure of 5352
4. Double click on 5352 and a new sheet (sheet2) will get created with 3 columns and 216109 rows
5. If you now wish to get rid of rows where the last column is empty, then you may just filter on blanks on the last column, right click > Delete > Table Rows
6. Clear the filter

You will be left with 5353 rows.

Does this help.

• Paul says:

Perfect!!! That was exactly the solution I needed. Thank you very much for taking the time to assist me!

• Christine says:

I am trying to set up Marco from this:

data1 a b c
data2 w s
data3 d q a b
data4 a
data5 g e k d j l

and I want it into this:

data1 a
data1 b
data1 c
data2 w
data2 s
data3 d
data3 q
data3 a
data3 b
data4 a
data5 g
data5 e
data5 k
data5 d
data5 j
data5 l

Can you help please? My aim is to look for a Macro solution(VBA) or formula solution, so in the future, I just need to copy and paste data dump and it will get transpose automatically.

Thanks.

• Nit says:

Hi Ashish, can you please help me understand how to include a 3rd column that repeats the column heading that the value in column b is coming from? My data is laid out the same way, but I also need to know which column did the value for column B come from. Sounds like your solution is exactly what I need but with this small addition.
Regards

• Hi,

• Hi,

The method described in my original post should work. Here it is once again

1. Select range A1:D4 and press Alt+D+P > Multiple Consolidation ranges > Next > I will create Page Fields > Next > select A1:D4 > Add > Next > New Worksheet > Next
2. In the resultant Pivot Table, double click on the Grand Total cell
3. You should now see your desired Pivot Table. Give meaningful column headings.

You may also accomplish this with the help of Power Query (a free add-in from Microsoft for MS Excel 2010 and higher versions)

Hope this helps.

• Christine says:

Thank you

• Thanks Ashish,

Your macro enabled workbook works a treat however, I’d like to be able to include one more column in the result list..

• Susan DeFelice says:

Ashish,

I think I have a similar problem that I am trying to resolve within Excel 2007. I have peer values that I am trying to benchmark within my company, and I want to show the results within a single column. I also want to include a percentile rank, and to show where the peer results fall, but redact the identifying information from my results for all individuals except the one for whom I am providing my analysis.

In addition, I would like to show my results as a single column chart that indicates the value & percentile rank for the individual, a gradient fill for the redacted results above my threshold (with a line indication and numeric/percentile result for the other peer values), and the overall column to show the range of ALL peer values for my company.

I have a pivot table that includes the filtered values above my threshold. Is there a way that I can achieve what I am trying to accomplish? The closest comparison I can find is an Energy Star rating for appliances.

Thank you so much for your help.

• Hi,

• Susan DeFelice says:

Ashish,

Here is what I am trying to accomplish:

I have one agency (CEEE) that I want to compare to their peers using data from a pivot table.

I want to show the information in a single column with the following details:
• The range of the values, which in this case runs from 0.0% to 1.7%.
• The data above threshold marked out in a different color on the bar. For the analyzed data, my threshold is 1.0%, and I have 6 agencies that exceed it.
• The agency for to be clearly indicated on the bar, with their rate percentage (1.7%), ranking (in this case, 2 of 49), and percentile.
• I would also like to have my 5 other agencies that are above threshold indicated with a line and a rate with their identifying details concealed or redacted. However, I also need to embed the chart as a bitmap, and it may not be legible with the extra information.

Again, what I am looking for is a single column, with two colors, that has full details for my CEEE agency and limited details for the range of values (bottom, top, and threshold percentages).

The closest example I could think of was an appliance Energy Star rating chart, which shows an expected electricity cost for a model in comparison to all other similar ones. These are shown in as a single bar chart, with an indication as to where a particular model falls within the range. I want something similar, but in a column instead.

Thank you again for your assistance.

• Hi,

I am absolutely lost. In the Pivot Table appearing in column A:B of O Rate Pivots sheet, I have been able to get the rank (1-6) in column C. What else do you want. Please share your expected result and show me the exact chart you want.

• Susan DeFelice says:

I’m sending you an excel spreadsheet with 2 worksheets. In the first, I tried to mock up something that I am trying to create. The second has a bitmap of the chart that I am attempting to model.

Thank you again!

• Hi,

Thank you for clarifying. Let’s divide the problem into two parts – first is creating the Chart as shown in the “Chart Example” worksheet and next is creating the Graph as shown in the “Bitmap of Energy Star Chart” worksheet. Let’s leave the Graph for now – let’s only focus on the Chart of numbers.

Kindly show the sample result (actual numeric result which you are expecting) in the Chart Example worksheet so that I can understand better.

Before expending effort on solving this problem, I want to be absolutely clear with the result which you are expecting.

Please upload the file with two worksheets – Actual data worksheet and Result worksheet.

• Susan DeFelice says:

Hi Ashish,

The first spreadsheet I sent you originally contained both pieces of information you are requesting. I am sorry for their poor quality, but I am trying to link spreadsheets to you via my home computer (with an ancient version of excel), and my work computer, which is blocking my access to the cloud files. Consequently, I am having a difficult time manipulating what I want to send to you and I cannot confirm what you are receiving, since I can’t see it.

The newest link has a modified version of my last file, which also contained my data source and the results spreadsheet. I had the results worksheet formerly named “O Rate Pivots.” The pivot is pulls out values that exceed 1.0%, which is noted as a decimal in the source worksheet, but my pivot shows the results as a percentage. You should also know that the number is not calculated from my data–it’s provided to me from other company systems.

Again, my concern is simply presentation of the data in a spectrum.

I am hopeful the second sheet and the additional information helps.

Thank you, again.

Susan.

• Susan DeFelice says:

Hello again, Ashish!

I know I am busy making you crazy with my Excel problems, but I thought it might be fun to throw another monkey in the mix. 🙂

Do you think it would be possible to consolidate more than one of these column charts together? When I write my analyses, I usually group more than one measure within my discussions. For example, the first section of my report covers my F, W, O, N, & C rates for my discussions, which all (conveniently!), have different thresholds.

Within my first section, the thresholds are as follows:
• F rate: 10.5%,
• W rate: 6.5%,
• O rate: 1.0%,
• N rate: 7.5%, &
• C rate: 5.0%.

I would assume I would need to lose detail from the main chart, but I could speak to the specifics, i.e., ranking, percentile, & agency rate, as bulleted information below the graphic. If the chart could show the columns in 2 segments, with the agency rate marked with a line for each rate, & bottom/top rates (over all 49 agencies), and 5 bars across (one for each measure), that would be great.

Thank you again for your continued assistance.

Susan.

• Hi,

My apologies for the inordinate delay but has the problem been resolved?

• Susan DeFelice says:

I think my response may not have been as clear as it could have been. When I pivoted my data, I used my Agency code as my Row Label, a Sum of O Rate for my Values, and a Report Filter of O Rate, and I selected all rates over 0.01.

Thank you.

• abhay parashar says:

Hi Ashish,

I have a data in which the employees training records are mantained in a vertical format eg
ashish communication skills
ashish presentation skills
ashish MS excel
Abhay Communication

There are about 700 employees whose training record are listed in thie above fashion. I need to remove the duplication of names and present the data in the horizontal format like (each program in different cell)
Ashish – communication skills presentation skills ms excel

I think “transpose” wont work as the data size is huge
Pls suggest if this can be done

• […] MVP Ashish Mathur has written a very interesting and useful article about how to handle this type of data […]

• math8 says:

Hi Ashish. I was on the Microsoft forum for excel and you asked me to post my question here. So here it goes:

I have data as output which are dumped into an excel spreadsheet in the following form:

Question1: I would like to automatically plot those data each time I have a new output. However, I don’t know beforehand how many rows or how many columns the output will have. I have a template with plots for my data, however, as of now, it only selects the current number of rows and the current number of columns. So when there are more than 5 columns or more than the current number of rows, the data outside the initial range are left out of the plot. Is there a quick and efficient way to handle this problem (i.e. to make sure excel selects the WHOLE new range and not just the previous range for plotting)?

Question2: I also need to produce some tables in other worksheets. typically, I use the transpose() formula, or = formula when I want to select an array. In the template, I need to use ‘CTRL+SHIFT+ENTER’. Otherwise I get an error. But this shortcut is not automatically applied with new outputs. I have to manually go in each array and double click a cell, and use ‘CTRL+SHIFT+ENTER’. Which is unacceptable for any other user. Is there a better way to have my arrays transposed or pasted without having to always manually use the ‘CTRL+SHIFT+ENTER’ shortcut?

• Hi,

2. With A1:E7 selected, press Ctrl+T . OK

Now as and when you add data by rows or columns, the Graph will keep updating.

I am not clear with your second question.

• math8 says:

Thank you for your response. As I explained in my last post at the link you provided, because it has been a while since I posted my first question, it has evolved due to new constraints and requirements.

• Thanks Ashish, worked great. Appreciate your help and prompt response.

• Hi,

Please refer to the video which I have shared in the Blog post. I am not sure if this will work in Google Spreadsheets.

• Hi,

Please clarify your question. What do you want to show under the DST Name1 and DST Name2 columns?

• Divi says:

Hi Ashish,
I need to convert a matrix into .data format so that I can use it for SOM toolbox. I need to label the features of the data through u-matrix in matlab. I am doing a project on intrusion detection system and using kdd data set with attacks for that when am using the data set with som toolbox the label column is empty and I dont know how to figure that out. if you have any idea about this and if you can help.

• Divi says:

Hi I am not getting how to label the clusters in Unified matrix of self organising map . I am using som toolbox and kdd cup 99 data set.

• Larry Mickelson says:

I have Excel 2007 and, sadly, connect use the Power Query tool. I walked through the workflow that you gave for “All versions of MS Excel”. Everything made sense except crunching the multiple columns (row labels from Pivot Table) with concatenate into cells G7:G35. So I downloaded the linked Excel file at the top of this page and went to the “Source data adjusted”, only to sadly discover that G7:G35 does not have this nifty concatenating-crunching bit done. I’m lost as to what to do.

Could you please add this to the Excel file and repost? Thanks.

• Larry Mickelson says:

Clicked submit too soon. Need an edit comment feature.

“cannot” not “connect”

• Hi,

In cell G7, enter this formula and copy down

=A7&”:”&B7&”:”&C7&”:”&D7&”:”&E7&”:”&F7

Hope this helps.

• […] Converting a matrix data layout to a tabular layout – Convert a matrix like data layout to a tabular data format to ultimately create a pivot table…. […]

• Kishore says:

Hi Ashish,

My question is similar to Christine who has posted the following question

“I am trying to set up Marco from this:

data1 a b c
data2 w s
data3 d q a b
data4 a
data5 g e k d j l

and I want it into this:

data1 a
data1 b
data1 c
data2 w
data2 s
data3 d
data3 q
data3 a
data3 b
data4 a
data5 g
data5 e
data5 k
data5 d
data5 j
data5 l”

I have downloaded the macro and it works very well for smaller data.

But the data that I have is huge one. My data range is from C1:AJ30008

Everytime I run the macro I get the error “Run time error ‘6’: Overflow”

Can you pls help me on this?

I am very new to excel macros. I dont know edit the macro codes!

Pls help me Ashish.

• Hi,

Kindly use the other two techniques outlined in the Original Blog article.

• neha says:

Dear sir,

Pls share macro for converting a list with 3 columns into cross table/matrix.

like
ID Prize Year
A X 9
A Y 10
B X 9
B Y 9
B Z 10

into

ID 9 10
A X Y
B X, Y Z

• Saurabh says:

Hi Ashish,

I have a matrix that has %s in the cells and a quantity at the end. I need to convert to rows. The matrix is:

MatA MatB MatC Quantity
Mat1 20 0 80 1000
Mat2 50 50 0 2000
Mat3 15 35 50 1000

I need to convert this to rows as:

123 ABC 20 200
123 IJK 80 800
456 ABC 50 1000
456 DEF 50 1000
789 ABC 15 150
789 DEF 35 350
789 IJK 50 500

• Hi,

Did you follow the Power Query solution which I have shown in the YouTube video clip of this Blog article?

• Saurabh says:

Thanks for this, Ashish. I’ll work with the Power Query solution you demonstrated here.

• mehmet says:

A brilliant solution, excellent video. Thank you mate.

• Rhea deDios says:

Sir Mathur,

I need your kind help if possible. I have 3 columns of data that I need to transpose using formula or macro. From the below example I need it to be 8 columns to look like this. I have over 20,000 rows of data to convert. Thank you in advance.

Connector Gender Connector Quantity Connector Shape Mounting Location Terminal Gender Terminal Quantity Terminal Type
ALS1041 Female 1 Rectangular Wheel Male 2 Blade
ALS1043 Male 1 Rectangular Wheel Female 2 Blade
ALS1044 Female 1 Rectangular Wheel Male 2 Blade

DATA EXAMPLE to CONVERT
ALS1041 Connector Gender Female
ALS1041 Connector Quantity 1
ALS1041 Connector Shape Rectangular
ALS1041 Mounting Location Wheel
ALS1041 Terminal Gender Male
ALS1041 Terminal Quantity 2
ALS1043 Connector Gender Male
ALS1043 Connector Quantity 1
ALS1043 Connector Shape Rectangular
ALS1043 Mounting Location Wheel
ALS1043 Terminal Gender Female
ALS1043 Terminal Quantity 2
ALS1044 Connector Gender Female
ALS1044 Connector Quantity 1
ALS1044 Connector Shape Rectangular
ALS1044 Mounting Location Wheel
ALS1044 Terminal Gender Male
ALS1044 Terminal Quantity 2

• Hi,

I have solved this problem with Power Query (a.k.a. Query Editor in Excel 2016 and higher versions). You may download my solution workbook from hereherehere.

Hope this helps.

• Rhea deDios says:

Sir Mathur,

You are absolutely amazing. Thank you so much for your help. You’re the best! Take care

• […] MVP Ashish Mathur has written a very interesting and useful article about how to handle this type of data […]