Excel Magic Trick 1242: Transform Large Data Set to Final GDP Report: TTC, MATCH, Filter & Format

Download File: http://people.highline.edu/mgirvin/ex...
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

---

Closed Caption:


Welcome to Excel
Magic Trick 1,242.
Hey, if you want to
download this Excel workbook
and follow along, click on
the link below the video.
Hey, we have this huge
data set Ctrl-down arrow,
so like 3,000 plus records, all
sorts of different countries,
all sorts of different
measurements for it.
So for Afghanistan
we have all sorts
of different measurements.
And then we have from
1970 all the way to 2013.
That's a lot of data.
But we'll only want
these countries.
And those are listed in a
cell separated by commas.
We only want the
years 2000 to 2010,
and we only want the
gross domestic product
number for each one of
those desired countries.
Now, actually, if you go
over to the sheet notes,
Mr Excel and I did
on dual number 174.
And we looked at Advanced
Filter and power query
for solving this exact problem.
But I want to come over
here and show you text
to columns, match function,
and just straight filter.
Now, the first thing is
if I were to filter this,
I could put a filter
here and check each one
of the individual items.
But that's way too many.
I don't want to do that.
So I'm going to break this
comma separated list out
into separate cells.
So I'm going to use data.
And then over here
text to columns
or the keyboard Alt, A,
E, delimited by a comma.
So when I say
comma right here, I
can already see
there's some trouble.
There's a space.
Now, I could do space.
But that won't work
because out here there
are some countries
that have a space,
and it would break
those two words apart.
So I'm actually going to
have to do this in two steps,
comma, and when I
click Finish, boom.
Instantly, I have those
broken out into cells.
Now, I'm going to get
rid of the extra spaces
by using my favorite
haircut function, trim.
Now, actually when I Control
Enter and copy this over,
trim will actually
remove all spaces
except for single
spaces between text.
So that's exactly what we want.
Now, I'm going to insert
one, two, three columns
because I want to
eventually extract and put
the data below this data set.
So I'm going to insert.
And then I'm going to copy.
And notice there's the
trim function there.
Control Shift right
arrow, Control C.
And then I'm going to arrow
just to jump over here.
And I want to jump down to
the bottom set, hit Control
down arrow.
And then somewhere off to
the side, like right here,
I'm not going to
paste or transpose.
I'm going to go up to home
and up to the Paste Special.
Or, I can use Control Alt
V to open up Paste Special.
And what I want is I
want to transpose it
because it was horizontal
and I need it vertical.
But I also want to
say Values Only.
Click OK.
There is my list.
I'm going to type
Countries, Control B
to add some bold, Control
Home to jump to cell A1.
Now, remember we need to
get the countries, GDP,
and the right year.
So I'm going to
start off by adding
a separate column, Countries,
Control Enter, Control B.
And I'm going to
make a formula here.
I'm going to use
the Match function.
Match function can
look something up.
I want to look up that country
name, comma, look up array.
Click in that cell.
Control, down arrow, down arrow,
Control Shift, down arrow,
F4 to lock it.
Those are the countries I want.
Comma, 0, because I'm
doing exact match,
closed parentheses,
Control, Enter.
Now double click
and send it down.
That gives me the relative
position of whatever
the country is in the list.
So Argentina is the first
item in that list down here.
F2, I want to see a true when
there's an actual number.
So I say is number.
Is number will return true
when there's a number and false
when there is not.
Now I can populate this edited
formula all the way down
with control and enter.
Now I'm going to turn on the
filter feature, Control Shift
L. Now, the filter
will only work
on this column and this column.
These other columns, we'll
have to do something else.
I'm going to start here, come
up, unselect all, and select
Gross Domestic Product Only.
Click OK.
Now, actually, I'm going to
scoot this off to the side
because I don't think
you'll be able to see it
unless I go like this.
Now I can select just the true.
Click OK.
The data set is almost there.
Control down arrow.
We have just a few records
here, the exact number
of countries we want.
Highlight the columns.
I'm actually going to delete
everything all the way
over to 1999.
Right click, Delete.
And I don't want the
last three columns.
Highlight Q to S.
Right click and Delete.
And boom, almost done.
Control Asterisk to
highlight everything,
Control C to copy just
the visible records.
Now I'm going to come down
here and insert a new sheet,
and write in cell A1
Control V to paste.
I'm coming down
to the smart tag,
pointing to Keep
Source Column Widths.
Now, I don't need
C and A columns.
So I'm going to click on C and
then hold Control and click
on A. Right click, Delete.
I still need to-- and I'm going
to use Control and Roll to zoom
out a little bit.
And I'd like to
highlight and display
the numbers inside differently.
I want to show them in millions.
So Control 1.
And we're going to
come down to Custom.
And look at that.
There's the number formatting
to show the full numbers
with commas.
But if you come to the
end and type a comma,
watch what happens
to the display.
It's as if we divided by 1,000.
If I type another comma, it's
as if we divided by a million.
Now, the underlined number is
still in the cell over here.
It's just displayed in millions.
When I click OK, boom,
that's looking much better.
Now I'm going to
highlight the actual field
names at the top for years.
These are stored as numbers.
So we can use custom
number formatting
because we need to indicate
that these are in millions.
Control 1 down to custom.
I'm going to highlight
and put a 0 just so I show
the number in double quotes.
Space M and end double quotes.
So that will indicate
that it's in millions.
Come down.
Click OK.
Now I'm going to right click
and insert two rows above.
Insert, go back over to the
original sheet, Control, Home,
because this is an
important label.
Control C-- actually, it'll
be our title in essence.
Control V. So GDT breakdown
at constant 2005 prices
in US dollars, all countries.
So now we have the
indicator up here that this
is constant 2005 US dollars.
And there's the M. Now,
we have a few more things
we'd like to do.
Let's add a border right
below the field names.
Control 1 on borders.
I'm going to select that one.
And we want to set the bottom.
So I'm going to click right at
the bottom there and click OK.
Now, I'd like to alternate
white and blue columns.
So I'm going to highlight
this first one, Home
over to the fill bucket.
And white-- now for the numbers
I want, blue, white, blue,
white.
So I'm going to color this
one blue with fill, maybe
this light one.
And because this is
number formatting,
and I'd like to use
the format painter,
I'm going to fill
this one with white.
And now I can
highlight both of them.
And I want to copy
just the formatting
and paste it multiple times.
So right clicking
on the mini toolbar,
there's that format painter.
I'm going to double click it.
And it actually copies
just the formatting.
So I can click and click.
Notice that paintbrush.
It is loaded up.
If you accidentally click
up here or down here,
it'll misapply it.
Click.
Oh, and I'm one off.
Watch this.
I'm going to click here.
Boop!
I don't need that.
I want to first turn
off the paintbrush.
So Escape, and then
highlight, and then
luckily we have removed
everything, formats
and just contents.
That, of course, is Delete.
I'm just going to
say Remove All.
Now, a few housekeeping
things left.
I definitely don't
want this tech center.
So I'm going to highlight
that first column.
And up under
alignment, I'm going
to say, hey, give me left.
And then all these numbers I
definitely want to the right.
And now if we Control P to
see how this will print out,
it's going to print real
messy across many pages.
Escape, Page Layout, and up
to the dialog box, right there
the dialog launcher,
or Alt P, S,
P. I'm simply going to say
Landscape, Fit To One Page.
Now, this might not work.
I might have to come
back and fix this.
Margins, let's say Horizontally.
Header and Footer, You
I like this one a lot.
But we only have one page.
If you had more,
you could use that.
I'm going to come
over to Custom Footer.
And I can put whatever I want.
I'm going to click in the
middle and put Sheet Name.
Now, our sheet name down
here is probably not
a very good sheet name.
But if we change it down
here, that will update.
Click OK.
Click OK.
Control P. That preview is
not telling us the truth.
That will print out OK.
So I'm going to click Escape.
Wow.
That was amazing.
We went from a huge
data set on the sheet,
1, 2, 4, 2, did text to
columns, trim, match,
and some filtering,
dumped it here, and then
did some formatting.
All right.
We'll see you next trick.