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.
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.