Excel Magic Trick 1242


Transform Large Data Set to Final GDP Report: TTC, MATCH, Filter & Format
Learn how to Take Large Data Set with Country Economic Data for the years 1970 to 2013 and filter, delete and match just the data we want to create a smaller data set using Text To Columns, MATCH function, TRIM function, Filter, Formatting and Page Setup:
(00:08) Discuss our task of taking a lot of data, removing only what we want, and then formatting and doing page setup on final report
(01:06) Text To Columns to get county names for our report
(01:54) TRIM function to remove extra spaces
(02:23) Copy Paste Special Values AND Transpose
(03:06) MATCH & ISNUMBER functions to create helper column to match countries we need in our final report
(04:07) Filter to get Counties and GDP numbers
(04:44) Delete Year Columns we do not need.
(05:02) Highlight Filtered Table to get Visible Cells Only and paste records to new sheet
(05:21) Delete non-adjacent columns in report that are not need in final report
(05:33) Display Numbers in Millions using Custom Number formatting: #,##0,,
(06:10) Display Years with an M to indicate numbers are shown in millions using Custom Number formatting: 0” M”
(06:43) Making sure that we have title that indicates the monetary unit: Constant 2005 US Dollars
(07:05) Apply Table Design Principles
(07:10) Add Border below Field Names
(07:17) Alternate shading for columns with white and light blue to help the visual ease of reading report
(08:25) Make sure that text is aligned left and numbers are aligned right
(08:39) Page Set Up so report prints correctly