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:
1. (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
2. (01:06) Text To Columns to get county names for our report
3. (01:54) TRIM function to remove extra spaces
4. (02:23) Copy Paste Special Values AND Transpose
5. (03:06) MATCH & ISNUMBER functions to create helper column to match countries we need in our final report
6. (04:07) Filter to get Counties and GDP numbers
7. (04:44) Delete Year Columns we do not need.
8. (05:02)Highlight Filtered Table to get Visible Cells Only and paste records to new sheet
9. (05:21) Delete non-adjacent columns in report that are not need in final report
10. (05:33) Display Numbers in Millions using Custom Number formatting: #,##0,,
11. (06:10) Display Years with an M to indicate numbers are shown in millions using Custom Number formatting: 0” M”
12. (06:43) Making sure that we have title that indicates the monetary unit: Constant 2005 US Dollars
13. (07:05) Apply Table Design Principles
14. (07:05) Add Border below Field Names
15. (07:17) Alternate shading for columns with white and light blue to help the visual ease of reading report
16. (08:25) Make sure that text is aligned left and numbers are aligned right
17. (08:39) Page Set Up so report prints correctly

Mr Excel & excelisfun Trick 174: Clean & Transform GDP Data Set: Advanced Filter? Or Power Query?

Excel Magic Trick 1243: Transform GDP Data Set: Power Query 2nd Method or Advanced Filter?

Basic Excel Business Analytics Transforming Data

[mohseenmohammed]

sir massive respect to you, i learnt more in this video than many full length videos, thanks to you

[ursulamclean]

You are a beast with excel. I want to be like you! I tried to download your file example and it's says not found.

[HoangVu-yfix]

Happy New Year ! Wish you all the best !

[elisadiazmorel]

Excellent; wondering if you can play around with daily volume data and the different things you can analyze for reporting purposes - do you offer training?

[gauravbajaj]

Hi there..
Good Video however the file is not available for download.. Could you pls update the link..

[cynthiacoc]

wow this is an expert excel class. wow

[ido]

I started really liking pasting formatting instead of the format painter. It lets you ctrl shift arrow your stuff. :)

[sudheernambiar]

HELLO SIR, THIS PRACTICE FILE IS NOT DOWNLOADABLE, PLEASE VERIFY

[cynthiacoc]

What course did you take to learn excel formulas so well. It must be an advanced class!!

[Sanjay.Ghanekar]

How copy filtered data as it is to other sheet

[catiscute]

Thank you so much, your video helped me a lot

[Sal_A]

Mike - can you create a video on custom number formatting with all the intricate details? I have somewhat of understanding but need to hone my skills on that more.

[narindersharma]

Hello, i need a solution on some thing similar.
List of items purchase in last two years with variation in price, within same some month and month over month.
How to identify the variation at item level.

[ContentedSoul]

Mike, instead of MATCH, why not just use FIND or SEARCH on the original country list string. That saves time parsing the string.

[MrKockabilly]

The title is now incorrect, no longer all countries but selected countries. Just noting it

Imagine he accidently deletes all of it!!

[sudheernambiar]

The page you requested is not available.Sorry, we can't find the page                you asked for.

You were referred to this URL report this error to the Highline IT Helpdesk using the button below.