Choose the 3rd option 'Consolidate Multiple Ranges'.
Hopefully it helped you solve your problem! If you have any further questions or ideas, please feel free to leave a comment below. The 3rd answer works: How to unpivot a table in Mac Excel 2011: Press: Command + Alt + P to bring up the Pivot Table wizard. So this is the end of this short tutorial.
Once complete, click Next >.Ĩ) Select New worksheet and then click Finish.ĩ) In the new PivotTable, deselect the Row and Column options.ġ0) Double-click the number next to Grand Total.ġ1) There you go! A one-dimensional list should be created in a new sheet. If you succeed, the range you selected should appear in the All ranges section. Then, click Next >.ħ) Under Range:, select all the data you need and click Add. When complete, don’t forget to click OK to save the changes.Ĥ) Go back to the Excel window, and click PivotTable and PivotChart Wizard on the Data tab.ĥ) In the pop-up window, select Multiple consolidation ranges and PivotTable. Now the PivotTable and PivotChart Wizard function should’ve been added to Data > New Group (Custom). When the New Group (Custom) item is created, click to highlight PivotTable and PivotChart Wizard in the left list and click Add >. If you already have the PivotTable and PivotChart Wizard function on your top menu, you can skip Step 1 – 3 and head straight to Step 4.Ģ) In the left pane, select Customize Ribbon on the right panel, under Choose commands from, select Commands Not in the Ribbon, and find PivotTable and PivotChart Wizard in the list below.ģ) Expand the Data category and click New Group. To convert a cross table to a flat list, you can perform the following procedure: How to convert a cross table to a flat list If you’re not familiar with cross tables and flat lists, here’s what they look like:Ĭross Table/Two-dimensional Table: a common form to record dataįlat List/One-dimensional List: the ideal form for data analysisĬan you find out the difference between these two forms? In the lower list, you can see that the metric for year (shown as the first row in the upper cross table) has been converted to a single column, which means it’s no longer a metric but a list of data. one-dimensional list) in Excel? Although flat list is the ideal form for analyzing, the data we get in daily life usually appear as cross tables so it’s necessary to know how to do the conversion. If the field and item arguments describe a single cell, then the value of that cell is returned regardless of whether it is a string, number, error, or blank cell. two-dimensional table) to a flat list (a.k.a. If the pivottable argument is a range that includes two or more PivotTables, data will be retrieved from whichever PivotTable was created most recently. One common question is: how can I convert a cross table (a.k.a. Excel is a very useful tool in data analysis, and it’s always a good thing to master as many skills as you can to save time and effort at work.