Excel Formula To UnPivot: Convert Cross-Tab Table to Proper Table. Build Re-usable LAMBDA. EMT 1887

Download Excel File: https://people.highline.edu/mgirvin/A...
Learn how to create a worksheet formula to unpivot a cross tabulated table into a proper table. Use the LET function because we have repeating formula elements in our formula. Use the LAMBDA Function because we want to define a re-usable function. Use the worksheet functions: IFS, IF, TOCOL, SEQUENCE, ROWS, COLUMNS, CHOOSEROWS, TRANSPOSE and HSTACK.
This formula comes from Excelambda at YouTube!!!
Topics:
1. (00:00) Introduction
2. (00:03) What we did in EMT 1886: Power Query UnPivot
3. (00:17) ExcelLambda left comment about UnPivot Formula
4. (00:33) Why we might want a formula to UnPivot
5. (01:05) Three Part Solution: Build Formulas, LET function to remove repetition, LAMBDA function to create re-usable function
6. (01:22) Build formula to unpivot a cross tabulated table, piece by piece
7. (01:22) IFS Function formula element to deliver an #N/A error when data is missing in cross tab table.
8. (02:15) SEQUENCE & ROWS functions used with IFS to deliver row positions from cross tabulated table row headers (row criteria)
9. (02:59) TOCOL function to skip #N/A errors when data is missing in cross tab table and you want to skip a record in the resulting table
10. (04:12) IFS, SEQUENCE, COLUMNS and TOCOL functions to deliver column positions from cross tabulated table column headers (column criteria)
11. (05:05) CHOOSEROWS function to generate a column, a proper field, in the resulting unpivoted table for the row headers (row criteria) in the cross tabulated table.
12. (05:52) CHOOSEROWS & TRANSPOSE functions to generate a column, a proper field, in the resulting unpivoted table for the column headers (column criteria) in the cross tabulated table.
13. (06:31) TOCOL, IF & IFS Functions to generate a column, a proper field, in the resulting unpivoted table for the values on the inside of the cross tabulated table.
14. (07:09) Mash three main formula elements into a single cell formula that uses the HSTACK function. Learn how to use the Clipboard and Ctrl + C, C keyboard shortcut to mash together formulas elements into a single formula.
15. (08:10) LET Function to remove repeating formulas elements and make the formula more efficient. Learn that the LET Function can define variables in a worksheet formula.
16. (08:56) Why we use the LET function
17. (09:42) Testing variables in the LET function
18. (12:18) LAMBDA function to define a re-usable worksheet function. We use LAMBDA to define a new UnPivot function
19. (12:30) Create arguments in the new LAMBDA defined function
20. (14:07) Test the new LAMBDA function
21. (15:40) Paste LAMBDA function into the Defined Name Dialog Box
22. (16:22) Use the new LAMBDA function: UnPivot
23. (16:43) Bonus: Add field name argument to the MABDA function
24. (17:27) Summary
25. (17:35) Closing

Using the gear icon setting button below the video, you can watch subtitles in these 31 languages: Afrikaans, Arabic, Bangla, Chinese, Dutch, Filipino, French, German, Hindi, Indonesian, Irish, Italian, Japanese, Khmer, Malagasy, Malay, Malayalam, Nepali, Persian, Polish, Portuguese, Russian, Spanish, Somali, Swahili, Tamil, Telugu, Thai, Tibetan, Urdu, Vietnamese.
Using the gear icon setting button below the video, you can listen to an audio track in these translated languages: French, German, Hindi, Indonesian, Italian, Japanese, Portuguese, and Spanish.

#excel #microsoft #excelisfun #excelcourse #unpivot #pivot #lambda #excellambda #letfunction #lambdafunction #novba #tocol #chooserows


#excel #excel365 #excelisfun #highlinecollege #mikegirvin #freeexcellessons #excelformulas #excelfunctions