Excel Magic Trick 1242: Convert large data sets into a final GDP report: TTC, MATCH, Filter and Format
| Channel | Publish Date | Thumbnail & View Count | Download Video |
|---|---|---|---|
 | Publish Date not found |  0 Views |
Download start Excel file: https://excelisfun.net/files/EMT1441-1442Start.xlsx
Download the finished Excel file: https://excelisfun.net/files/EMT1441-1442Finished.xlsx
Learn how to take large data sets of country economic data for the years 1970 to 2013 and filter, delete, and match only the data you need to create a smaller data set using Text To Columns, the MATCH function, the TRIM function, filters, formatting, and page setup:
1. (00:08) Discuss our task of gathering a large amount of data, removing only what we need, and then doing the formatting and page setup for the final report.
2. (01:06) Text in columns to get county names for our report
3. (01:54) TRIM function to remove extra spaces
4. (02:23) Copying, pasting and transposing special values
5. (03:06) MATCH and ISNUMBER functions to create a helper column to match the countries we need in our final report
6. (04:07) Filter to get counties and GDP numbers
7. (04:44) Delete year columns that we don't need.
8. (05:02) Select the filtered table to keep only the visible cells and paste the records into a new sheet.
9. (05:21) Delete non-adjacent columns in the report that are not needed in the final report
10. (05:33) Display numbers in millions with custom number format: #,##0,,
11. (06:10) Years are marked with an M to indicate that numbers are displayed in millions, using the custom number format: 0” M”
12. (06:43) Make sure we have a title that indicates the currency unit: Constant 2005 US dollars
13. (07:05) Applying principles of table design
14. (07:05) Add frame under field names
15. (07:17) Alternating shading of the columns with white and light blue to make the report easier to read visually
16. (08:25) Make sure the text is left-aligned and the numbers are right-aligned
17. (08:39) Set up the page so that the report prints correctly
Download the finished Excel file: https://excelisfun.net/files/EMT1441-1442Finished.xlsx
Learn how to take large data sets of country economic data for the years 1970 to 2013 and filter, delete, and match only the data you need to create a smaller data set using Text To Columns, the MATCH function, the TRIM function, filters, formatting, and page setup:
1. (00:08) Discuss our task of gathering a large amount of data, removing only what we need, and then doing the formatting and page setup for the final report.
2. (01:06) Text in columns to get county names for our report
3. (01:54) TRIM function to remove extra spaces
4. (02:23) Copying, pasting and transposing special values
5. (03:06) MATCH and ISNUMBER functions to create a helper column to match the countries we need in our final report
6. (04:07) Filter to get counties and GDP numbers
7. (04:44) Delete year columns that we don't need.
8. (05:02) Select the filtered table to keep only the visible cells and paste the records into a new sheet.
9. (05:21) Delete non-adjacent columns in the report that are not needed in the final report
10. (05:33) Display numbers in millions with custom number format: #,##0,,
11. (06:10) Years are marked with an M to indicate that numbers are displayed in millions, using the custom number format: 0” M”
12. (06:43) Make sure we have a title that indicates the currency unit: Constant 2005 US dollars
13. (07:05) Applying principles of table design
14. (07:05) Add frame under field names
15. (07:17) Alternating shading of the columns with white and light blue to make the report easier to read visually
16. (08:25) Make sure the text is left-aligned and the numbers are right-aligned
17. (08:39) Set up the page so that the report prints correctly
Trick 174 from Mr Excel and excelisfun: Clean and transform GDP dataset: Advanced filter? Or Power Query?
Excel Magic Trick 1243: Transform GDP Dataset: Power Query 2. Method or Advanced Filter?
Basic Excel business analytics to transform data
Please take the opportunity to connect with your friends and family and share this video with them if you find it useful.
