in this video we'll go over 10 Excel tricks and rank them in terms of secrecy from most known
one star all the way to Top Secret of 5 Stars let's go first up in level 1 we've got filtering
by cell value so if we take a look at the Excel file over here which you can download for free
in the video description you can see that we have this database with all of the customers what they
bought more specifically let's say we want to filter by Nike so only the ones that actually
bought something from Nike for that we would just activate the filter by going to Ctrl shift l
once you have that drop down there you would click on it and say deselect all of them and find Nike
now there is a faster way to go about that so let me show you let's suppose you find the Nike
anywhere within this in this data set all you have to do is go to right click then towards the bottom
you're gonna find the filter and you just need to go to filter by selected cells value click on
that and just like that it's activated the filter for us in no time next up in level one we've got
moving rows or columns so if you go to control page down here you can see that we have the same
data set and suppose we want to move this brand column all the way over here so how people would
usually go about it is just creating a new column here so control space Ctrl shift plus and then
moving this one along now that's obviously a bit tedious so instead first we'll press Ctrl Z to go
back there all you need to do is actually select this column say we want column e at the very front
here and then you're just going to press the shift key when you do that when you hover over it you'll
see these Arrow Signs so just drag that and say we move it over here and just like so you're able
to change it a lot faster all right now moving up to level two and here we've got comparing sheets
so if we look at the Excel file you can see that we have the week one financials by country and
just below it we have the week two financials and suppose we want to try to compare both of them now
to do that it's not very easy because they're on different sheets so instead as a workaround
what you can do is go under the view tab and from here go under new window so click on that
and so now you can see that it's actually made the same file again so just go window right and
then we'll select these two and now you can see that we have the exact same file that we
can actually compare to so if I go control page down over here we can compare week one and week
two a lot more easily similarly if I want to make a change on one of them like let's say I put an X
instead of Canada here you can see that that scene change it's actually gonna automatically happen on
this file as well similarly we have making changes across sheets so for example let's say that here
we're actually missing a top border so we want to add a few more rows now we can do this one by one
so do it on this sheet for week one and then this other one for week two where instead what we can
do is actually select both just by hitting the control key and selecting both of them and from
here if I make a change so I press shift space and then Ctrl shift plus say I do that three times so
I add a few more rows there if I go to the the next page so to page two you can see that it's
done the exact same action so that's what's nice about hitting that Ctrl key there moving on to
level 3 and here we've got importing web data so suppose in this blanket Excel file we're supposed
to put each country and the respective population now there's around 200 countries so that's going
to be a very tedious task but instead what we can do is actually get that data from the internet
so we'll go under the data tab from here you can see that it says from web so we'll click on that
and all we need to do is type the URL so let's suppose that I have Wikipedia's population data
I'm just gonna copy the whole URL and then take it back and just paste it over here and hit OK
once it's loaded I'm just going to go to the relevant table which in my case is this one
over here and hit load and just like that we have the population for each country in the world and
you can very easily modify this data set if you wanted to create to importing web data we have
data types for geographies so over here if you go to control page Zone you can see that we have this
list of countries and we want to find out their GDP now to do so you can just go under the data
tab from here you're gonna find this these data tabs and the one we're interested in is geography
so first we'll select all the countries so Ctrl shift down just click on geography there once
and you can see that we're gonna get this icon on the side now from here if you press this plus sign
just click on that once you can see all of the things that we can actually look up and they're
gonna Auto populate so for us we're just gonna type GDP there which is this one down over here
click on that and just like so we have the GDP for each country and similarly you can really add just
about anything here let's say I go for the CPI and I'll get that auto populated there as well next
up in level 4 we've got sparklines so this is the data set that we have as you can see we have each
country and their respective sales by month now if we want to try to get a visual for this we can go
ahead and select it all so control shift down Ctrl shift right from here go to insert and let's see
the recommended charts that we're gonna get and overall you can see here that there's simply just
there's too much data and so we can't really get a good visual out of it so just hit Escape there
for now all we can do instead is find the trend line which is going to represent all of the data
for Spain same thing for Italy and so on now to do so all we need to click on is this line here
from here the data range is the data that we're interested in so for Spain it's this whole data
here so Ctrl shift right and then the location is where the output is going to be and we're fine
with it being there so we'll hit OK now you can see what that trend line is going to be looking
like and then just drag that down all the way to the bottom there and from here you can actually
change this to a column for instance if you think that's a bit better and you can even highlight the
high point or the low Point as well with this it's very easy for us to tell that Spain's had
a very good Trend while the total here seems to be quite on the low next up in level 4 we've got
creating chart templates so as you can see over here we have this Excel chart that we've just
made let's say that our brand colors are aligned with this and they also like this dashed line and
they also like it not to have border is and so we want to try to save this as a template so we don't
have to reformat it every single time for this you can simply just right click on the chart and over
here you can see that it says save as template feel free to name it whatever you want and just
hit save there once we do that let's go ahead and delete it so we can actually test it out let's say
that we remove the last bit of data so we have a bit of a different data set here let's say we
only have all of these so from here let's select the relevant area so to us it's the names and we
also want the sales and the commission amount so then we're just going to go to insert and again
we're gonna go over it towards the bottom here to this recommended charts part just click on that
and under all charts we want to go to the part that's going to tell us our templates so over here
now you can see what our template looks like we'll hit OK just like so you can see that it selected
the right data and it's kept that consistent look with the colors and having no border as well
alright now we're getting into top secret territory and first up we've got formatting
numbers in thousands or in millions so as you can see over here we have the Toyota sales and
we have it in actuals but they're actually really big numbers so instead we want to try to convert
that into thousands or into millions like it's typically the case in a financial statement
so for the thousands one first let's select everything here so Ctrl shift down then we're
just gonna hit Ctrl 1. from here under custom you can see the number format that's currently being
applied so what we want to do instead is actually at the very end of it we're just gonna hit a comma
and hit OK there now you can see that we've converted the actions all the way to thousands
over here now let's try to do the same thing with Millions so we'll select them Ctrl shift down
then we'll hit Ctrl 1. and from here at the very end this time instead of hitting one comma we're
gonna hit a comma a second time now you can see what that looks like but actually let's say we
also want to put a decimal somewhere so what I'm going to do is in front of the two commas
I'm just gonna hit 0.0 and then hit OK there just like so we have 14.4 million but if we
actually look inside of the cell you can see that we have the full number as it should be the case
and finally for the last trick we've got tracing precedence so if you go to the very last sheet
you can see that we have the sales data by country on a specific day with their total is down below
now suppose we want to find out how this formula Works where it's getting the data from for that we
can just go on the formulas tab from here click on Trace precedence now you can see exactly how this
formula Works where it's dragging the information from and where the final output is same thing goes
if I look for the average here just click on Trace presence I'm gonna get that same data similarly if
you want to see where the formulas are located you can click on show formulas and you're going to be
able to see which cells are formulas and which other ones are hard coded to continue learning
Excel check out this video over here to learn 10xl formulas or this link over here to take or Excel
for business and finance course hit the like and then subscribe and I'll catch you in the next one