How to Unpivot Static Tables in Microsoft Excel

Download PC Repair Tool to quickly detect & prepare Windows errors automatically

If you are a regular Microsoft Excel user, but non quite familiar with Ability Query Add-in for Excel, then this mail service volition accustom and help yous get started with it. Whatsoever advanced Excel user faces the challenge of analyzing or creating reports with PivotTables and PivotCharts as they lack the expertise in this domain and often terminate upwardly showing static tabular data, or summaries. Though it is easy to use this table for quick data entry, it is difficult to commencement information analysis on such a table or catechumen it into PivotTable and shape it to meet your assay needs.

Await no further, with Power Query technology in Excel, yous can modify this. The new capability added to Excel allows you lot to unpivot static tables, ie. access your tables, perform a transformation and create a PivotTable from the transformed data, in Excel.

Unpivot Static Tables in Excel

Consider you accept an Excel workbook that is dedicated for manual data entry to track the number hours you expend energies on a unmarried relevant project.

Analyzing this data can exist a daunting task. You wish if you could just create charts on this information. Fortunately, through the new Become & Transform section in the Data tab of Excel 2022, you could complete the task. To exercise and then,

Only select any prison cell within an existing table or range and in the Go & Transform department, click From Table.

You will see a preview of your information within the Query Editor window. Nether this window, you can start transforming your data. Each change you make is recorded as a transformation step that is saved with your workbook. All the steps are kept every bit a sequence and can exist performed every time you refresh your data.

In the Query Editor, you will usually demand the assist of the Unpivot transformation to alter your tabular array into a format that tin can exist used by PivotTable.

Equally y'all tin observe in the example below, I tin can select the first column that includes project names and click Unpivot Other Columns to transform my data from columns of Project Name / 2022 / 2022 / 2022… to the desired column structure of Project / Year / Duration.

And here's what you lot will discover as the results in the Query Editor:

I can now rename the columns to Project, Year and Duration within the Query Editor and and so load the data into a PivotTable or PivotChart.

Hope this helps.

At present read: How to Brandish or Format Number as Currency in Excel.

A post-graduate in Biotechnology, Hemant switched gears to writing nearly Microsoft technologies and has been a contributor to TheWindowsClub since then. When he is not working, you can usually detect him out traveling to different places or indulging himself in binge-watching.

Source: https://www.thewindowsclub.com/unpivot-static-tables-excel

Posted by: dawsonyeand1977.blogspot.com

Share this post