# Excel On Fire

• ### Dynamic Array: Unstack Data with SEQUENCE ... just 2 steps

46,532 views 1 year ago
Here we are again!
I was playing around more with the Dynamic Array functions, especially after Leila Gharani posted her video that made unstacking data pretty easy with a substitution trick and finding a pattern in the dataset. Here is her video:

Well, I found a 2-step process that REALLY simplifies unstacking a column of data. SEQUENCE, COUNTA and INDEX get us there, taking advantage of spilled arrays and Excel's new calculation engine. Alas, not everyone has the Dynamic Arrays yet. If you don't have them, and you need to unstack some data, use Leila's method. 👍

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:

Website: http://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerr......

My old blog: http://datascopic.net/blog-2-2 Show less
• ## Excel Play all

• ### The New Fuzzy Matching Feature in Excel's Power Query - Duration: 5 minutes, 12 seconds.

• 2,392 views
• 5 months ago
• ### Power Query: Split columns between digits and non-digits - Duration: 4 minutes, 34 seconds.

• 2,147 views
• 6 months ago
• ### Using a Transformation Table in Power Query's Fuzzy Merges - Duration: 6 minutes, 18 seconds.

• 2,135 views
• 6 months ago
• CC
• ### Emojis, Excel, Power Query & Dynamic Arrays. It's true! - Duration: 6 minutes, 12 seconds.

• 2,189 views
• 7 months ago
• ### 5 Dynamic Array Gotchas in Excel - Duration: 5 minutes, 22 seconds.

• 2,470 views
• 1 year ago
• ### Pass Parameter (Drill Down) to Pick Name Format with Power Query - Duration: 11 minutes.

• 2,011 views
• 1 year ago
• ### Use Power Query to Identify 2 Consecutive Months of Underperformance - Duration: 9 minutes, 14 seconds.

• 2,348 views
• 1 year ago
• ### A Case Study on Data Quality: Find & Clean Skunk Data with Power Query - Duration: 6 minutes, 5 seconds.

• 6,383 views
• 1 year ago
• CC
• ### The New Geography Data Type in Excel: Let's Play With It - Duration: 10 minutes.

• 1,834 views
• 1 year ago
• ### Using Parameters in Power Query to Generate Random Data + Data Validation - Duration: 6 minutes, 26 seconds.

• 3,028 views
• 1 year ago
• ### Replace Each x in a Matrix With Column Headers in PowerQuery - Duration: 7 minutes, 23 seconds.

• 1,415 views
• 1 year ago
This item has been hidden
• ## Uploads Play all

• ### ComputerGagaChallenge 2: Data Normalization and Missing Entries - Duration: 7 minutes, 49 seconds.

• 595 views
• 4 days ago
• ### ComputerGaga challenge: Convert Columns into Normalized Rows - Duration: 7 minutes, 33 seconds.

• 989 views
• 1 week ago
• ### Reconciling Christmas Payments 2: Merging a Power Query Query with Itself - Duration: 6 minutes, 36 seconds.

• 1,153 views
• 3 weeks ago
• CC
• ### Reconciling Christmas Spending with SUMIFS, IF and Good Spreadsheet Layout - Duration: 6 minutes, 8 seconds.

• 2,063 views
• 1 month ago
• ### ExcelHash 2019: Snakes & Regs - XOR, Dynamic Arrays, Linked Picture, Icon - Duration: 7 minutes, 54 seconds.

• 3,514 views
• 1 month ago
• CC
• ### XLOOKUP v3.0; Power Query Import from Web; Intellisense, Text.Length - Duration: 6 minutes, 56 seconds.

• 1,255 views
• 1 month ago
• CC
• ### The Majesty of DUPLICATE QUERY in Power Query - Duration: 5 minutes, 35 seconds.

• 1,597 views
• 2 months ago
• CC
• ### Creating Fake Data in Excel: random dollar amounts & dates using RANDARRAY & TEXT - Duration: 4 minutes, 54 seconds.

• 897 views
• 2 months ago
• ### Full Anti-Join and the New XLOOKUP with "if not found" - Duration: 6 minutes, 6 seconds.

• 1,006 views
• 3 months ago
• ### Use Power Query to Get Top X Scores and Tie Scores - Duration: 6 minutes, 1 second.

• 959 views
• 3 months ago
• ### Extract a column of data from a Power Query table - Duration: 4 minutes, 42 seconds.

• 1,573 views
• 3 months ago
• CC
• ### XLOOKUP: Search from the bottom and top of a list - Duration: 4 minutes, 36 seconds.

• 1,000 views
• 4 months ago
• CC
This item has been hidden
• ## Hot Stuff! Play all

• ### Carolina Reaper & Ghost Pepper Sriracha Reviews - Duration: 17 minutes.

• 572 views
• 5 years ago
• ### Ghost Pepper Bourbon Infusion - Duration: 3 minutes, 40 seconds.

• 1,148 views
• 8 years ago
• ### Peanutbutter Dragon Cookies - Duration: 5 minutes, 12 seconds.

• 1,227 views
• 8 years ago
• ### Ghost Pepper Bourbon - Duration: 5 minutes, 29 seconds.

• 489 views
• 8 years ago
• ### Nicole Challenges the Peanutbutter Dragon Cookie - Duration: 5 minutes, 31 seconds.

• 398 views
• 7 years ago
• ### Kate, Oz & Peanutbutter DragonCookie - Duration: 8 minutes, 24 seconds.

• 282 views
• 7 years ago
This item has been hidden
• ## Popular uploads Play all

• ### Excel 2013: Slicers & Tables - Duration: 4 minutes, 4 seconds.

• 88,138 views
• 6 years ago
• ### Dynamic Array: Unstack Data with SEQUENCE ... just 2 steps - Duration: 3 minutes, 40 seconds.

• 46,532 views
• 1 year ago
• ### Connect Power Query to a Dynamic Dropdown List - Duration: 6 minutes, 11 seconds.

• 45,304 views
• 2 years ago
• ### Excel Tutorial: Select an image based on a cell value - Duration: 5 minutes, 48 seconds.

• 15,492 views
• 4 years ago
• ### Excel Inventory Control System DEMO - Duration: 7 minutes, 29 seconds.

• 13,394 views
• 7 years ago
• ### How to Stack Columns of Data in Excel: Formula Method, Power Query Method - Duration: 4 minutes, 19 seconds.

• 11,897 views
• 2 years ago
• ### Excel 2016's Get-&-Transform for importing and cleansing Data - Duration: 7 minutes, 2 seconds.

• 10,788 views
• 4 years ago
• ### Presenting data that has a wide range between your largest & smallest entries - Duration: 8 minutes, 2 seconds.

• 9,603 views
• 6 years ago
• ### Running Totals in Excel's Power Query: the easy way - Duration: 7 minutes, 26 seconds.

• 8,137 views
• 2 years ago
• CC
• ### CSV to clear merged cells in Excel - Duration: 6 minutes, 5 seconds.

• 7,868 views
• 6 years ago
• ### Passing a Parameter in Power Query: Find reviews that need to be done in x days - Duration: 8 minutes, 37 seconds.

• 7,805 views
• 1 year ago
• ### Graphing 2 Pieces of Data on 1 Chart (Excel Secondary Axis) - Duration: 2 minutes, 32 seconds.

• 7,630 views
• 6 years ago
This item has been hidden
• ## Created playlists

• ### FunkyFunktionFriday - Playlist

This item has been hidden
to add this to Watch Later