Channel: ExcelIsFun

Category: Education

Tags: dropunpivot data into proper tableformulas and functionscolumnshighline collegemike girvinemtexcel magic trick 1785rectangle of data into tableunpivot with worksheet formulaexcel is funexcel magicletemt 1785excel array formulaifnaexcel functionsexcel funindexrowspower query unpivottocol array functionexcelsequenceexpandtocoldrop array functionmicrosoft 365 excelexcel 365excel formulasroundupexcelisfun

Description: Download Excel File: excelisfun.net/files/EMT1784-1785.xlsm Learn how to convert a rectangle of data with row headers and columns headers into a proper table of data using Array Functions such as DROP, EXPAND, TOCOL and INDEX. Topics: 1. (00:00) Introduction 2. (00:30) Power Query is easier, but for instant update, Formulas Rule. 3. (01:11) DROP and TOCOL functions to unwind rectangle of numbers into a single column. 4. (03:29) DROP, LET, ROWS, COLUMNS, SEQUENCE, ROUNDUP and INDEX functions to repeat cross tabulated ROW headers into a proper table column by repeating elements to keep records in the table intact. 5. (07:20) DROP, LET, TOCOL, ROWS, EXPAND and IFNA to repeat cross tabulated COLUMN headers into a proper table column by repeating elements to keep records in the table intact. 6. (09:15) Review of three columns. 7. (09:24) Test the dynamic table by adding new data 8. (09:57) Closing and Video Links Power Query formulas in the Worksheet?