Скачать или смотреть Excel - Two-Way Table to Linear: Episode 1572

Microsoft Excel Tutorial: Converting a Two-Way Table to a Linear Table in Excel | MrExcel Podcast.

Welcome to another episode of the MrExcel Podcast. In today's episode, we will be discussing a useful trick from Mike Alexander, co-author of "Pivot Table Data Crunching". If you enjoy this trick, be sure to check out Mike's book, available from Q. Now, let's dive into the topic at hand: converting a two-way table to a linear table in Excel.

This trick was sent in by Kamal, who wanted to know how to take a two-way table with items going down the side and milestones going across, and turn it into a linear table with everything going straight down. Thanks to Mike's trick, this can easily be achieved. We will be creating a special kind of PivotTable, one that is not available in the regular PivotTable dialog box.

To begin, we need to go back to the old PivotTable dialog box by pressing Alt+D+P. From there, we will select the "multiple consolidation range" option and click Next. We will then create the page fields, but make sure to uncheck the option to create a page field. Next, we will select our data range and click Finish. This will give us a PivotTable that looks exactly like our original data.

At this point, you may be thinking, "What's the point of this?" But don't worry, we're not done yet. Let's change the data to a Sum and format the dates. Now, here comes the magic. Double-click on the grand total cell and you will see a view of the PivotTable cache. This multiple consolidation range PivotTable will actually unwind the data, giving us all the milestones and dates in a linear format, just like Kamal wanted.

I remember seeing Mike do this and thinking, "Wow, that is really cool." I'm glad he's on my team. Thank you for tuning in to this episode of the MrExcel Podcast. Be sure to join us next time for more Excel tips and tricks. And don't forget to check out Mike Alexander's book, "Pivot Table Data Crunching", for even more valuable insights. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...


Table of Contents:
(00:00) Two-Way Table to Linear
(00:12) Shout-out to Mike Alexander
(00:26) Question from Kamal
(00:36) Creating a special PivotTable
(01:14) Unwinding the data
(02:07) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial


This video answers these common search terms:
Creating a special kind of PivotTable
Excel tutorial with MrExcel podcast
Formatting dates in PivotTable
Mike Alexander's trick
Multiple consolidation range
Pivot Table Data Crunching book
Two-Way Table to Linear conversion
Unwinding data in PivotTable cash
Using Alt+D+P for the old PivotTable dialog


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...

Microsoft Excel 2010" by Michael Alexander and Bill Jelen. Taking a table that is currently set up as a Two-Way Table, Bill uses the 'Old Pivot Table Dialogue' in Excel to set up a Pivot Table to transform the current Table to a Linear Table. Follow along with Episode #1572 to learn how to access that 'Old Dialogue' and transform the Two-Way Table.

For more information on 'Pivot Table Tricks', Usage and general 'Pivot Table How-To', check out: ""Pivot Table Data Crunching: Microsoft Excel 2010" by Bill Jelen and Michael Alexander http://www.mrexcel.com/2010Books/pvtt...

...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! http://www.mrexcel.com/learn2010/LE20...

"The Learn Excel from MrExcel Podcast Series"

MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!