Example_Transcript.txt

Playlist Name: Excel Introduction - Learn Excel Basics

Video URL: https://www.youtube.com/watch?v=0tdlR1rBwkM
In this Excel for beginners tutorial, I'll give you a quick
introduction to Excel, so you can start to feel
comfortable inside the application. You'll have an idea where things are, how the spreadsheet is set up
and how you can input value. (upbeat music) When you open Excel on your
desktop, you get to this view. You can decide between a blank workbook or if you've opened a
recent file in Excel, you will see it pop up here. In this case, I'm just going to go ahead and create a blank workbook. This workbook isn't saved
until you decide to do so. To save the workbook, just go here, click on save and then you can decide where you want to save it. You can also use the
shortcut key, control S. A single Excel file is also referred to as a workbook or a spreadsheet. A workbook consists of at least one sheet. You can easily add more sheets
by clicking on the plus here. When you double click on a sheet name, you can change the name
and then press enter. A sheet contains many cells,
this box here is a singe cell. You can input numbers or text in here, just start typing and then press enter. If you want to replace this value, just go to that cell and start typing, you don't have to double
click, you can type over it. Each cell has an address,
what do you think the address of this cell is, it's D4, which means column D, row four. The intersection of a
column and a row is a cell. Up here, you have the formula bar. Once we input text here, we
can see it in the formula bars. If I move to the cell to the
side, there is nothing there. Now if instead of text, you have formulas, so if I go to the side
here, type in an equal and then go with my arrow
keys to D4 and press enter, notice what's in the formula bar. It has the formula and not the text. In this case, there is no
formula, it shows the text. On the left here, we have the name box, this also shows us the
active cell address, so in his case it's D4,
when I go here, it's F4. Now here's a useful tip for you, you can also use this to
jump to a specific cell. Just type in the cell address. So let's say I want to jump to A100, I'm going to type it in, press
enter and I jump to that cell. Now to jump back to the first cell, you can use the shortcut
key, control home. On the Excel grid, you can select multiple rows or multiple columns. Just go with your mouse
and highlight the rows or highlight columns,
just select and drag. There are also a lot of
shortcuts in Excel you can use. So for example, the
shortcut key control space, selects an entire column. If I hold down the shift
key and the arrow keys, I can select multiple columns. Now I have a separate
video in Excel shortcuts and I've added the link to
the description of this video. Now here, if you right mouse click, you're going to get a lot of options. You can for example, insert
columns in between these. If I right mouse click again, I can delete the columns
that are in between these and since we're talking about shortcuts, a great shortcut is to use control plus to insert columns or rows and control minus to delete them. An Excel sheet has more than 16000 columns and over a million rows. Now you can see that by jumping
to the bottom of the sheet, if you use the shortcut key control and then the arrow down key. This jumps to the last row in Excel. If I use the control key
and the right arrow key, I jump to the last column. Now the way to get back, do you remember where that shortcut was? That was control, home. Another term you need to know is range. A range is a group of cells. If I highlight this area, so just click on the first cell and then drag. If you want to use your keyboard key, just select the first cell
and then hold down shift and highlight the other
cells with your arrow keys. Now how do I reference this range? What's the address of it? You reference it by
the address of the cell on the top left hand corner and the bottom right hand
corner separated by a column. So this would be D4 until F7. So you would write that as
this, D4, to with a column, F7. Now this is something that's
going to be used inside formulas. So whenever you see a syntax like this, this is referencing a range of cells. If I wanted to move the value of this cell to another cell, I could
use the move handle. So this here that you
see, this mouse icon, this is the selection icon. So you just go and click
and you're selecting a cell, if you drag, you select a range. But here, if I move it to the boarder, I get this four arrows,
that's the move icon. When you see this, you can click and drag and you move the content of
that cell somewhere else. You can also use the
shortcut key control X and control V, to cut and paste. Okay, so we have the selection handle, we have the move handle,
there is another handle that's called the fill handle. So when you go to the side,
to the corner of the cell, you get this cross that symbol, if you drag that, you fill the content of the cells below with the cell above. So in this case, because I have text, I am filling them up with the text. If I drag the formula,
I'm going to fill these up with the formula, so if I go here, it's the formula that's been copied down. Cells have a default size
here but you can change them. So for example, if I input
text that's too long, it's going to go over to the next cell. So it looks like the contents of this is in these cells as well but it's not, it's only sitting in this cell. You can see that from the formula bar, when I'm here, there is
nothing in that cell. To expand the column, I can hover my mouse over here and just drag. Or, I can double click to
make the column expand, to be big enough to fit
the content of my cell. Now you might not want to
expand it horizontally, you might prefer to
let it flow vertically. So instead of it going
all the way to the side and your column being so wide, you want to keep the width like this but you want the row to expand. Now even if I pull down the row, this is not going to wrap inside the cell. There is a setting for
this and it's right here, it gives you the ability to wrap the text. Now if you see that the whole
text is not fitting here because the new part is missing, just double click on the row
to make it expand to fit. If you want to unwrap this text, just click on the wrap setting here and it's going to unwrap it. You'll find a lot of widely
used options in Excel if you right mouse click,
so if you right mouse click on a cell, you get the ability to insert, delete, you
can clear the contents, add a comment and so on. If you right mouse click on a column, you get similar options
and some additional ones that only apply to columns. Now let's take a look at Excel's menu. This is commonly referred
to as the ribbon. It consists of different tabs, every time you click on a tab, the options in the menu bar change. The most common one is the home tab, here's where you can do formatting and common tabs like inserting, deleting or sorting and filtering data. If I go to the insert tab,
I can insert a chart here or insert pivot tables. If you hover over a feature,
you get more information about the feature and
also the shortcut key you can use for that
feature if it has one. Now it does pay off, if you take some time and go through the different tabs and the different options you have. One comment option I use is for example, to hide the grid lines. This is something you can
do from the page layout tab under grid lines, take
away this check mark and the grid lines are gone. So this is good for creating
reports and dashboards. Click it again and the
grid lines are back. Some of the groupings
have additional options. You can see that from
this tilted arrow here. So if I click this, I
come to this dialogue box. Now a lot of these options
might be duplicates, it's just these options
organize differently but you might have some additional
options in here as well. If you want to increase
the space of your grid, you can collapse the ribbon. Just go to this arrow here
and collapse the ribbon or use the shortcut key, control, F1. Every time you go and select a tab, the ribbon appears, when you
click away, it disappears. To make it stick again, you can pin it or use the shortcut key, control F1. The file tab is different to the rest. This is also referred to
as the backstage view. Here you can see
information about your file, you can save it as PDF,
you can print it out, or you can check which
version of Excel you have by going and checking your account. To go back to normal view, either use the arrow here or press escape. Now let's go to the data tab
and there is some shortcuts when it comes to inputting values. So here, I'm going to input
headcount by department, for a few different months. I want to give this a
title, so I'll call this, headcount by department, and press enter. Now let's say I want to
go from Jan till June, I can just type in Jan here. Now when I press tab,
I go to the next cell. By default, if I press enter,
you go to the cell below. If you want to press enter
and stay on the same cell, you have to press control enter. Now if I go to the next cell, I can type in Feb and then March and so on but Excel has this already built in. So if you type in Jan, it recognizes that this is a month, now
when I pull this to the side, notice I can see May
and then at the bottom I have June here. So now the first department is marketing, I'll just type it in. If you ever need to go to
edit mode, you can press F2. This is going to take you to the end of the text that you have,
so if you want to continue with that text, you can just start typing. So and sales for example. If you just start typing over the cell, remember, it's going to replace everything that was in the cell. I'm typing marketing here
then I'm going to press tab, let's say type in 20 and then 20 and 20 and in case this is 20
for all these months, again, don't forget that you
can use the filler handle. So you can click and drag and if you want a shortcut for this, there is one, just highlight with the keyboard keys and then press control R. This is going to fill to the right. Now when you're on the end
here and you press enter, you jump to the cell below. One way to make it easier for you to input a bunch of data, is
to highlight the area first. So in this case, when I highlight this, notice when I press tab and I get here and I press tab again,
it goes to the first cell of the next row, so now I can type and then press tab, tab, tab and so on. To get out of this,
you can just click away or use the arrow key to
move out of the space. Now I'm just quickly going
to add some data to this and then let's do some formatting. Okay, so now we have some more data, let's adjust the formatting of this. I want to center this across my report. So highlight the range, go to
home and merge these cells. When you click it, it automatically merges and centers the cell. We can make these bold,
highlight and click on bold. And while we're here, let's
also adjust the cell width, to match the content of the cell. So I'm going to highlight
all of these together and then double click to make this fit. Now if it's too tight for you, you can expand this a little bit. If you just select any of these while the rest are highlighted, it's going to make the same
adjustment to the rest as well. Now you can go ahead and add color to this or add boarders, so here
you have a bottom boarder, you'll have thick bottom
boarder, so let's go with that. In case you want to move
something to be in the middle, so let's say I don't
want HR on the bottom, I want HR between marketing and sales, you can select it and
then use the move handle to move it but while you're moving it, don't just drop it because
if you try to do that, it says there's already data in there, do you want to replace it? No, we definitely don't
want to replace it. So what we want to do is bring it and put it in between marketing and sales. So while you're dragging this, hold down the shift key and
then you see that line changes, it shows you where it's going to drop it. Now if you let go of the mouse, it's going to drop HR in
between marketing and sales. Now you have other options
to do this as well, if you don't want to use this combination, just right mouse click and cut or use the shortcut key control X and then go to where you
want this range to sit, right mouse click and insert cut cells. Let's give the title some color as well and if you ever adjust
the formatting of the cell and you want to apply that
formatting to other cells, you can use the paintbrush icon. So for example, let's say for
some reason I made this red and I want to apply
that to the other ones, I can just go and click on the paintbrush, click on the cell I want and it automatically
applies the formatting. Now this is not just for color, it's any type of formatting you have, it can be a combination of bold font, cell boarders and so on. If you want to apply that
formatting to multiple cells, double click on this paintbrush and then you can reuse that formatting and apply to multiple cells. Once you're done, press
escape to remove it. If you want to sum up these values, you can go to home and sum them from here. There's also a great shortcut key for this which is alt equals. When you click it, it
tries to pick up the range, this is correct, you
just have to press enter. Now you can fill the rest in and the formula will be copied over and the ranges will reflect
the direct ranges above. If you want to do this
in one go, you can first select your area and just
use the shortcut key, alt equals and you're done. This wraps up our introduction to Excel in case you like to learn more about Excel and other features and functions, check out the other videos
I have on this channel. Now if you really like to master Excel and learn by solving actual problems, check out my Excel essentials
course on Excelplus.com. Link to it is below the video. I hope you enjoyed this tutorial, if you did, hit that thumbs up and do subscribe to this channel in case you haven't subscribed already, so you can improve your Excel skills and learn some tips and tricks
when it comes to Office apps. Thank you for watching and
I'll see you in the next video. (upbeat music)

Video URL: https://www.youtube.com/watch?v=FRu48zy-Djk
In this video, I'm going to show you how 
and when you should use Absolute, Relative,   and Mixed cell referencing in Excel.  This is the sample data set that we have. Let's   say we have sales information for these three 
different types of app for each quarter. First   thing we're asked to do is to get the total for 
the year for each app. So, I'm going to go to   this cell and I'm going to use the shortcut 
key Alt and the equal sign. This automatically   inserts the SUM formula and it's kind of smart 
because it realizes where it has numbers and it   sums those values. So, in this case, it's 
correct. I just have to press Enter. Now,   I'm going to push this down by double-clicking on 
the edge here. Let's take a look at our formula.   If I click on the first row here and I click 
inside the formula, it's summing B5 to E5. Now,   if I go to the last one here and I click inside 
the formula, you can see that it's summing B7   to E7. This is called Relative Referencing because 
the referenced cells are moving down as my formula   moves down. So, now let's say my boss comes to 
me and says, "Give me the percentage of each app   in comparison to the total for the year." So, first 
thing I need to do is to calculate the total. So,   I'm going to come here and again use the same 
shortcut key combination Alt and the equal sign.   And again, this time it's smart, it notices that 
it has numbers on top, so it's going to sum those.   All I have to do is to press Enter. So, now comes 
the tricky part of calculating the percentage. So,   I'm going to come here, type in the equal sign, 
click on the first total for the game app and   divide that by the total here. Okay, I'm going 
to change this to a percentage. Now, can I pull   this down just like I did before? Well, let's try 
it. I run into a problem, why? Let's click inside   the formula it says, this divided by this, right? 
So, the total value here shifted down which is   correct because this percentage should reflect 
the utility app, but the total value shouldn't   move down. I need that to be fixed. This is when 
you need Absolute Referencing. That means, you need   to add dollar signs to the row, as well as to the 
column reference, and you can do that quickly by   using the shortcut key F4. That automatically puts 
in the dollar signs. You can of course also put   in the dollar signs yourself, you don't have to 
use the shortcut key. It's just much faster to   use the shortcut key. Let's pause for a quick tip. 
If you click on F4 once, you get the dollar sign   for both the column, as well as the row. If you 
click on it again, you get the dollar sign only   for the row. Once more, it puts the dollar sign 
only for the column, and click it again, it goes   back to relative referencing. We're going to see 
the mixed referencing part in a second and why you   might need it. So, when I press Enter now and I 
pull this down, I get the formula to work properly   because what these dollar signs do is they fix 
the column reference as well as the row reference.   It doesn't move down. So, when I'm here, we can 
see the first part is relative, the second part   of the formula is fixed to this cell. Now let's 
say my boss comes to me and says, "Calculate the   percentage of each quarter for each app, so that 
we can quickly see which quarters we have the most   sales and which quarters we have the least sales 
for each of these apps." What I'm going to do is   to copy this information, so Ctrl+C, go here, 
and paste this. Now, I'm going to remove these   because instead of these numbers, I want to see 
percentages here. And that percentage is going   to be the value I have in quarter one divided by 
the total I have in quarter one for that app. So,   what do we need here? Relative, absolute, mixed 
referencing. Let's test it out first. I'm going   to say this equals to this number divided by 
this number. Right, can I leave this like this?   Not really. Right, I mean for this value, it's 
going to work fine, but when I pull this over,   what happens to this? The same thing that happened 
before when we calculated the totals here. This   number is going to keep shifting this way. I 
don't want it to shift, so do I need to use full   referencing here? So, absolute referencing. Well, 
let's try it. What happens if I do? I'm going to   click on this F5 here and press F4 to get it fully 
fixed. Now, I'm going to pull this over here and   let's take a look. Let's go to this cell, click 
on this. This looks great, right? It has this cell   divided by this cell, so perfect. Let's just pull 
this down. Is that our report? Could we give this   to our boss? Let's check, is this correct? No, 
because this doesn't even add up to 100% right,   so there's something wrong here. Let's just 
click on this cell, click inside the formula,   and we can see it's taking this number. That part 
is correct, but it's dividing it by the total for   game app. We don't want that. And it's doing 
it because we fully fixed the reference to F5,   so that doesn't move at all. But we do want it to 
partially move, right? So, what part of this do   we want to move? The row part, right? Because we 
want the number 5 to become number 6 since we're   on row 6 here and then to become number 7. So, 
the part that you want to move shouldn't have a   dollar sign. The dollar sign is for the part that 
you want fixed. So, right here, what do we want   fixed? We want the column to be free. We want F to 
be fixed, that shouldn't move to G, shouldn't move   to H, it should always stay on F, but we want the 
row to move. This means that the dollar sign stays   with the part that we want fixed. So it stays with 
F, and I'm going to remove the dollar sign from   the row here. Now, if we pull this across and 
we pull this down, we get it to work properly.   And the way that you can test this is always go 
to the end of your data set and then just double   check that your formula referencing is correct. 
So, in this case, it's dividing this by this,   which is correct. And here, we are using mixed 
cell referencing. That's how you can use absolute,   relative and mixed cell referencing in Excel. If 
you liked this video, don't forget to give it a   thumbs up, and if you want to receive updates 
when new videos come out, consider subscribing.

Video URL: https://www.youtube.com/watch?v=SgXiepZUumc
How familiar are you
with Excel's cell modes? I didn't even know this was
a thing until I realized Excel behaves in a different way when I am in a certain dialog box. So, for example, if I'm in
a data validation dialog box and I'd like to move around
with the keyboard keys, in the dialog box, it
automatically inserts a cell reference in there. That's because I'm in point mode in Excel, which is different to edit mode. So what type of modes do we have and how can we switch between these modes? Let's find out. (upbeat music) The default cell mode state for Excel or let's say the normal
state is the ready mode, and that's what you can see here on the bottom left hand side. Now in this state, nothing happens, you can click on this cell, move around, click on these dialog boxes,
you'll stay in ready mode. Now, the moment you
start to input something so let's say I input a number
here, I go into enter mode. Because I didn't start this
input with an equal sign or a plus sign or a minus sign, Excel knows that I'm
not inputting a formula, so if I use now the arrow
keys to move around, it's actually going to leave the cell and input that number in the cell. So if I go here and
again, input let's say 60, I'm in enter mode, I either click Enter or use the down arrow key
and I move down a cell. But it's going to behave
differently now when it sees that I'm inputting a formula. So I'm going to start with the equal sign, I'm back in enter mode but this time, I'm going to use the arrow keys. Again, I'm going to go up and
notice which mode I'm in now, it's in point mode, so
it switched directly from enter mode to point mode the moment I used the
arrows and it's only because it realized that I am inputting a formula because I started the
input with an equal sign. Now even if you start with
the minus sign or a plus sign, it's going to act the same way. So now let's say I click on the 50, I'm going to do a plus and
again, I'm in enter mode so I'm going to go and
use my arrow keys again and go and select the
60 and now press Enter and I'm back in ready mode. Okay so I managed to switch
from enter mode to point mode by using the arrow keys because
I was inputting a formula. But now let's say I want
to go and edit this. Now let's actually try
and include a new number so I'm going to put 30 in here. Now to edit this, I'm going
to use the F2 shortcut key or you can also double click in the cell. Now we're in edit mode, that's
different to the enter mode because in edit mode, we
can use the arrow keys to move around inside the formula and make changes if we wanted to. So I can switch this to a minus
and I'm still in edit mode and let's go and now add something to it. So from edit mode, now if I want to go and use my keyboard keys to select B2, I can't just press the up key
right now because what happens if I do that, it just
jumps to here, right? If I click up, it just
jumps to the beginning. So I need to go back to that point mode. That's when I can use F2 key again. Now I'm in enter mode
and now I can move up because it's realized that
I'm editing a formula. To switch back to edit mode,
I'm going to click on F2 again and I can go and switch
between these two modes as much as I need. Just remember that from edit mode, you can't use the arrow
keys to switch another cell, you'll have to click F2 again
to go back to enter mode so that you can go and
make your cell selection. Okay so now I'm going
to go back to edit mode and delete that last
entry and press Enter. Always take a look on the bottom to see which mode you're in. Now the part that can
get quite frustrating is when you're inside dialog boxes like conditional formatting
or data validation or the name manager in formulas or even if you're adjusting a chart range. So in the Data tab here, I
have just some sample data and on this side, I have a
conditional formatting rule. So let's just go and check it out. Let's go to edit rule. That's the rule, it's
just looking at cell F5, checking if it's less than
G5 and then it's formatting this cell to an orange color. Now, let's say I wanted to update this. What happens now if I use the arrow keys? Take a look at my status
bar, I'm in enter mode and there is an equal
sign in front of this. So the moment I start to move around, it's going to go to point mode
because it's thinking that I want to reference the cell but that's not what I want to do, I want to actually edit this, I want to adjust the cell reference. So I need to click on
F2 to go to edit mode. Now, let's say at this
point I actually want to go and reference the cell from edit mode, I want to switch to point mode. So I have to click on F2
again so I go to enter mode. And now Excel realizes that
because I'm in enter mode and I'm writing a formula, if
I want to use the arrow keys, it's going to show that cell reference. So I'm going to go and
select this and now let's say I want to delete the dollar
sign beside the five. I need to go to edit mode so
I'm going to click on F2 again, the status changes to edit. Now I can move around with the arrow keys, delete this and press Enter. Now in this case I'm
just going to press skip because I don't want
to change that formula. Okay, so just remember, whenever you go to these dialog boxes,
if you don't want Excel to go to point mode, click
on F2 so you go to edit mode. Now the same is true if you're creating data validation lists. So if I create a list that's
let's say referencing this and now I want to go and adjust this, if I click on the arrow
keys, I'm in point mode, I need to click on F2 to go to edit mode. So now I'm going to click
on F2, I'm in edit mode and now I can move around. Okay so anytime you are going
to any of these dialog boxes and you want to make an
adjustment, click on F2 to go to edit mode and then move around. This is going to save you
from a lot of headaches. In case you don't see the status bar here, it may be disabled for you so
just right mouse click here and all the way on top,
you see the cell mode. If you click it, it's a toggle, it will disappear from
the bottom, now it's gone. If I click it again, then
I can see the cell mode on the bottom. So those are Excel's different cell modes. Keep an eye on which mode
you're in and switch them if you need to. I hope you enjoy this video. I know it's a basic one but it's just something
that's easy to miss. If you did enjoy it, don't
forget to give it a thumbs up and consider subscribing
if you aren't subscribed to this channel, I'm going to
see you in next week's video. (upbeat music)

Video URL: https://www.youtube.com/watch?v=wMlTDXPEjag
in today's video we're going to take a look at the basics of Excel filter options Excel filter options can come in really handy when you work with large data tables now there is a lot of options there so I'm gonna split this into two videos in this video we're going to cover the basics and in the next video we're gonna take a look at more advanced options here's a sample list of data we have information on channel product date and sales value when this data said we'd like to place a filter now the quickest way of putting a filter in place is just click anywhere inside your data set and then do ctrl shift L and you get these icons on the header to clear the filter press control shift L again another way of putting the filter is to go from home to sort and filter and click on filter I'm just going to press control shift L again to get rid of the filter and yet another way of just putting a filter here is just by right mouse clicking so let's say I'm only interested to get the list of shirt blue I just have to right mouse click here go to filter and say filter by selected cells value so this filters out only the lines that have shirt blue in there now you can see that when you get this blue font on the row numbers the other cells are actually there they're not deleted they're just hidden and you can see on the bottom that filter gives 6 out of 26 records you can also make individual selections so if I go here and clear this filter from product let's say I'm interested in website sales and affiliate site sales so I will click this I would deselect everything then click on affiliate site and website and I say ok I get my list here then if I'm interested on let's say anything that for example would start with shirt I can go here and instead of deselecting and looking for all the ones that have shirt here I could actually use a wild card directly in the search box so I could put that star sign okay that's the sign of the wild card and I can put shirt and I need to put the star sign again to give me anything after shirt a star sign before means that it could also be called blue shirt or white shirt so shirt doesn't have to be the first word and a star sign after means that there can be other things coming after this now I can see that it automatically put a tick mark for everything that had shirt in it and I can do that right here with the site as well because the word site is also an affiliate site and in website if we select everything and here I just put site it automatically makes that selection for me to take away this filter from here you can go through each one and say clear filter but this can be quite annoying so one way is to go to home to sort and filter and clear this filter or another way is just to press control-shift l two times so let's say I have this filter on and I have this filter on then I would just press control shift L to take away the filter and then control shift L to put back the filter but this way I know that my filters are all clear you can also filter by color so if for some reason you want to highlight these cells in let's say this color and you want to highlight these in the say green color and then you want to filter by this green color you just have to go to the filter arrow here and then to filter by color and it already picks up the colors that you have underneath it so I just click this I get my list if you want to copy the result of a filter all you have to do is press ctrl a to highlight the area and then press control C you can see that only these lines are copied then you go to a new tab then you press control V right so copying this way only copies the filtered rows and there is nothing here that's hidden in the middle press escape and then control shift L to open up the filter and I'll just do control shift L again if you're working with dates you have a lot of automatic date filter options in here you can say just show me the data for this month all right so I'm in February right now and that's the cells I mean let's say in February I can also say show me data for last month or even show me data year to date right so that would give me in this case January and February data right or you can even look between two dates so I can select between from here I get directed to custom auto filter it says if date is after or equal to now the good thing is you don't have to try to figure out in what format should I put in the date you can make the selection from here let's say I want anything from the 10th of January until the 15th of February you have the end option already selected which in this case is correct because it should be after or equal to this date and before or equal to this date and say ok that gives me only those lines of data now let's take a look at how we can use the or condition so I'm just going to clear the filter here and let's go to channel for example remember how I put the star sign here to look for site right to use a wild-card you can also go and select a specific filter option for that so under text filter you have the contains option so for contains I can also just put in site in here then you don't need to use the wildcards now let's take a look at the or option let's do an example on product I'm gonna go to text filter - custom filter and look for blue or white products ok so we can see it contains blue or it contains white now let's say I was interested in the totals that I see here so if I highlight this range I can see on the bottom that that's the sum of my filtered area if you use the normal sum function here right and we go from all the way from 5 to 30 you see the entire sum of the data set which also includes those hidden cells right and this is not something that you might want in that case you can use the subtotal formula or the aggregate formula so subtotal is very simple to use you just have to select what type of total you want this case it would be number nine for some and then the range that you wanted some and this by default is going to exclude any hidden cells okay so it's only going to total what you can see here what I suggest though is that if you have your data set up like this it's always best to create an Excel table out of it the table is going to give you a lot more benefits so you'll have the filtering benefits that we saw right now but in addition you have a lot more benefits to make a table out of this data what I'm gonna do is it's just one filter and go back to our raw data set I'm just gonna press ctrl T and this data set does your table have headers I can see the area it selected is correct and my table does have headers so I'm gonna say okay that is gonna activate a new tab for you that's called table tools and first of all what it does is that it gives you this default look for a table which you might not want so you can immediately go and clear that from here to go back to the look you had before if you forget this shortcut key to insert a table you can go to insert and click on table okay so in this case it's deactivated because it's already turned into a table one advantage of the table is that you don't need to remember that subtotal formula you can very easily add a total row to your table here and you can select how you want the total to be in this case for the cells we want it to be summed let's say I also want a total here but I just want the count of shirts and this total and count are based on the filter so let's say we just want to filter for blue that's the count and that's the total the tables have more benefit than just this because if your formulas are referencing the table and you add more data to the table the formula gets updated if you have charts that are referencing the table the chart gets updated or pivot tables that are referencing the table your pivot table data range automatically gets updated so really recommend that you use tables because with tables you don't just get these filtering options that we saw before but you get a lot more than that there in the next lecture we are going to take a look at how we can use advanced filter advanced filter is not sitting on the Home tab instead it's sitting on the data tab and is sitting right here in the next tutorial I'll take you through the benefit of this I hope you liked this video if you did don't forget to give it a thumbs up and for more videos like this one don't forget to subscribe to this channel so that you can get updates when new videos come out [Music]

Video URL: https://www.youtube.com/watch?v=VqQACB_69SQ
In this lecture, we're going to take a look at 
Advanced Excel Filter Options. This is   our sample Dataset, though in the previous video 
we saw how to put a quick filter on this using   the shortcut key Ctrl+Shift+L. Now, the one 
disadvantage of the normal filter is that   if I for example, put a filter on this and let me 
also just select these two, but you don't exactly   know what the filter is on until you hover your 
mouse over it and then you can see that Channel   has been set to Store and Product is set to Pants 
Black-Pants Blue. If you want to have full control   over your filter criteria, you might want to use 
Advanced Filter because there you actually put   your filter criteria in separate cells, so you know 
what they are. To do that, go to Data to Advanced,   let's just take a look at the options that we have 
here. We can either filter the list in place, which   is just like the way we've been doing so far, or we 
can copy the results to another location. You need   to define the List Range which is normally smart 
enough and it picks up the correct range, then we   can define the Criteria Range. So, this is where 
you are going to type in the cells, you also have   the ability to extract Unique Records and that's 
something I'll show you at the end of the lecture,   but let's see how we can use this Criteria Range. 
What you need to do is to always start with the   Header of the Criteria that you want to filter. 
So, let's say I want to create a filter only on   store here, okay I'm just going to unfilter this by 
pressing Ctrl+Shift+L. What I need to do is to   put the Header where Store is in, so in this case 
is Channel. Now, you can either type it in or copy   it over, it's not case-sensitive. Underneath it, 
I'm going to type Store. Now, we're going to go   to Advanced Filter, the list is fine we're going to 
leave it to also filter the list in place. Now, the   Criteria is this one and this includes your Header 
because this way Filter knows where to look. Okay,   that automatically filters the list based on Store. 
The great thing about this is that you can use   And/Or Conditions you can also use Wildcards 
here. So, let's take a look at an And Condition   first. Let's say, I want to filter for Store and 
for Pants Blue, I'm going to put Product because   that's the header and I'm gonna put Pants Blue. 
When you put them beside one another, it means   it's an And Condition. So, let's go back to Advanced, 
now we need to expand our Criteria Range to both   of these, and say ok. Ok so, that filters for Store 
and Pants Blue, now you can also say if it doesn't   equal Pants Blue. So, Smaller and Greater Sign 
here, and press Enter, and now let's reapply the   Filter and we get the list of everything else 
that's not Pants Blue. Now, let's take a look at   the Wildcards, what if we just wanted to look at 
everything that has the word Pants in there? We   can put the StarSign+Pants and then StarSign 
after. So, this means that Pants could also be in   the middle of our label, and if you just reapply 
this we get everything that has the word Pants   in there. Now, let's say I wanted to look for Store 
and Pants or Store and Shirt White. Okay so, this calls   for an Or Condition, this means that I need to type 
the Second Criteria below this, but I also need to   make sure that I have my other criterias also set, 
because one criteria is Store and anything with   Pants or its Store and anything that has the title 
Shirt White in there. Okay so, let's apply Advanced   Filter again, our range needs to be updated to this 
and we say ok. Okay so, we have Store and Shirt White   and Store with anything that has Pants in there. 
Okay so, just remember that typing in below is an   Or Condition, typing beside an And Condition. Let's 
do something else, let's look for Sales that are   greater than 200, I can expand on this. I can add 
in Sales Value should be greater than 200, okay   and also for this one it should be greater than 
200, unless I want to define something different   for this combination. What does this mean now? It 
means it shouldn't show this and it shouldn't   show these and that's it right? So, you should be 
hiding these two lines. Let's go back to Advanced   and let's expand our Filter. Okay so, they're gone. 
Now, let's take it one step further as well and   say don't show me anything that's above, let's do 
above 400. So, basically the List Filtered should   show these values that are between 200 and 400. 
Okay so, we can copy this here and just change   this to less than 400. Okay so, let's see which ones 
this one should hide, this shouldn't be there, this   one shouldn't be there. Okay so, let's test this 
and let's update this to include all of these.   Okay so, that's how you can use Advanced Filter, you 
can also repeat the columns like we did here and   put in different conditions in there. As long as 
they make sense and the And Condition applies to   them, like it does with numbers or with dates. What 
I'm going to show you last is how to copy Unique   Records into a separate location. So, for example 
let's say you were interested to get the Unique   List of Products out of the entire list. So, let 
me just clear the filter that we have in place   and let's go back to Advanced Filter. We're only 
interested in Products, so I'm just going to highlight   that entire column where I have Product in. Now, 
criteria range in this case, I don't need to define   that because my criteria is unique records only. 
Now, let's say I want to copy into another location,   so let's copy it right here, and say okay, and I 
get the unique list of my products. So, these are   the different ways that you can use Advanced 
Filter in your Excel reports. I hope you like   this video, if you did, don't forget to give 
it a thumbs up, and for more videos like this   one, don't forget to subscribe to this channel so 
that you can get updates when new videos come up.

Video URL: https://www.youtube.com/watch?v=Aw4ZHT3SYzc
let's take a look at the different source options we have in Excel and how you can potentially unsourced your data to get back to your original row order you actually have quite a few options to sort your data apart from the usual ones like sorting numbers by ascending descending order or searching text alphabetically you can add in your own custom lists so you can tell Excel the sort order that you'd like to have you can also add in multiple levels and sort by color you can even sort by icon let's do a few examples in these and then let's take a look at how we can unsorted at ASET here's our sample dataset we have information on division region apps and revenue and let's say we just received this and we quickly want to look at the app that had the highest revenue all we have to do is to click anywhere inside the column that we want to sort right mouse click select sort and select largest to smallest Excel is smart enough to recognize that these other columns belong to this data set and that's because they're right beside each other without any gaps in between if I click anywhere inside this data set and I press ctrl-a I can see that all the states have belongs together so whenever I use the sort option on any of these Excel is going to sort this entire data set and it's also smart enough to recognize that this data set has a header as well so it doesn't sort the header with it you can also sort alphabetically so for example if I wanted to sort based on division all I have to do is right mouse click somewhere inside the division column go to sort and select either HC or Z to a so let's go with a to Z I get game productivity and utility now what happens if that wasn't the case so for example let's see I had some text written here to some random text and I didn't want these to be sorted because now check this out if I click anywhere in this data set I press ctrl-a they got highlighted so they're also automatically gonna be sorted so let's just test that if I right mouse click on revenue and a sword from smallest to largest you can see that they get sorted I'm just gonna press ctrl Z to go back if you don't want them included you actually need to highlight the area that you want sorted so I'm gonna highlight this use the shortcut key ctrl shift down to jump to the bottom of this data set and now I'm gonna right mouse click here go to sort now the alternative way of getting to sort is to go through the Home tab you see sort and filter right here you can directly choose how you want to sort where you can go to custom sort you can also get to custom sort by right mouse clicking here right on the bottom you see custom sort this gives you full control over the data set you want to sort and in case Excel doesn't recognize that your data has headers you can make sure it does by putting in a tick mark right here now if I sort this data set let's say it's my revenue column and let's go with smallest to largest the data gets sorted but it doesn't impact my other columns now let's say I wanted to sort division but I didn't want to sort it by HSE or Z to a because that's what it gives me utility productivity game ages that gives me game first but I said I don't want either of these I want my own personal custom sort I want productivity to always be first then utility and game for this I can add in my own list all I have to do is to go back to custom sort just right mouse click sort and then custom sort select the column that you want to sort on so that's division is correct I do want to sort on the values but I want to use a custom list now all I have to do is to type in my personal list in here put in productivity first then I want utility game then click on add you can see right here ok and we can see the order right here as well I get productivity first utility and game now I want to add in more levels to this after I sort division I want to sort the region I don't want easier to be scattered in here I want easier to be together and then I want the apps within that division to be sorted as well so this calls for multiple levels and again we can right mouse click go to sort or this time let's go from home sort and filter and click on custom sort now the first level is fine I wanted to use the custom list I just added but I'm gonna add a level after this after it takes care of division I wanted to sort the region I do want it to sort on values now for order let's just go with HSN so I could add in my custom list as well if I wanted to see region in a certain way now after this I'm going to add another level and I'm gonna sort by the apps and just keep that data set so now I get them organized that Asia is all together and then the apps are also sorted alphabetically within the region let's see how we can sort based on color and icon so let's say some people have highlighted different numbers here in yellow because we told them if something happens make sure you highlight those lines in yellow and if something else happens highlight them in green all right so let's say these are some things that we want to pay attention to and when we receive this file we want to immediately sort it based on color first and then based on this original sort order that we defined so all I have to do now is go back to custom sort and add in two other options for the colors and notice that you can push down or up the orders using the arrow keys so I'm just going to click on add a level right here and then later push it to the top what I want is to have the cells that are in yellow to be on top so this is not based on the value of the cell anymore but it's based on cell color so from sort on I'm going to say like cell color and right here Excel recognizes the different colors that I'm using in this column so I'm gonna say yellow I want it to be right on top now I'm going to push this all the way to the top and right below this add another level that uses the cell color green and after that I wanted to go back to my original source order that I defined so this way I get the yellow cells first then I get the green ones and then I have the sort order that I defined before now let's say in addition to this I was using conditional formatting here I'm just gonna highlight the area add a quick conditional formatting for the sake of this tutorial just to get in some icons in here I'll select icon sets let's quickly go to more rules and let's select the flags here now here you get the opportunity to define how you want them applied it's comparing my number to the rest of the numbers within this range and can say I'm fine with that and I get some green flags red and the yellow flags here now once you're using icons and conditional formatting you can also sort them right mouse click here I'm gonna go back to custom sort let's just delete these levels and add in a sort for these so to delete levels you just click on what you want delete it and click on the delete button that's a sort based on the icons after I sort on cell color I'm gonna add a level select my revenue again and now instead of selecting cell color I'm gonna go with cell icon put everything that's let's say green first and then put everything that is red and after that it can put everything else which is the yellow icon so let's go with okay so we get our cells first and then we get the green ones than the red ones and last the yellow icons so what do we do now if you want to go back to our original sort order I can't get it back the reason is Excel is not a database it doesn't remember which cell was originally associated with this line the moment the order changes here the previous one is forgotten right unless you press ctrl-z to go back but if you save this file and then you open it later and you want to go back to your original sort order you can't go back but there is a way around this and let me show that to you on this data set what you need to do is to add in a column either before after in the middle just somewhere to your data set so let me just cut this and push it to the side so I have some space here and I'm going to add indexes here type in and one let's just push this all the way down I don't want to copy the one I want to field series so I get unique values in here all I have to do is to always sort back my helper field to get back to my original sort order so if I now sort revenue let's say from largest smallest we can see that these numbers shifted to get back to my original sort order all I have to do is to sort on this helper column smallest to largest and I'm back to my original sort order but that's a decision that you have to make up front otherwise you're going to lose the original source order that you had at the beginning so these were the different methods you can use to sort your data if you find this video useful I'd appreciate it if you gave it a thumbs up and if you're interested in these type of videos consider subscribing [Music] you

Video URL: https://www.youtube.com/watch?v=FRiFfKb_B_A
Today's video is more than just the basics 
of Excel's data validation. I'm going to show   you how you can get the content of the Drop-Down 
to be dynamic. So, if you add new categories they   automatically show in the Drop-Down, then I'm going 
to show you how you can validate for specific   entries. For example, let's say the input date 
should fall in the future or numbers input should   fall within a range or the input values should 
contain a specific number of characters. Imagine   this, imagine you work for the headquarters of a 
company that creates different types of apps and   you have three divisions, you have the game, productivity, 
and utility divisions. Now, you're responsible for   collecting the information and the different types 
of projects that each company is working on. You've   created this template that includes the basic 
information you need to collect from them. In the   first column you have division, you need to make 
sure that they type in the correct division, that   they don't make any spelling mistakes, because 
some people could spell games instead of the   game division right? And you don't want to spend 
a lot of your time correcting these mistakes. So,   here you want to add a Drop-Down List where the 
people can actually select the divisions. For this,   you can add a Data Validation. To do that, you have 
to go to the Data Tab and underneath Data Tools   you see something called Data Validation, just 
click on it. The first tab here is the Settings   tab and you can see that by default Excel allows 
you to input any value in any of these cells, but   you can change that. You could add in additional 
restrictions. Now, you can see we have different   options here, you can also get very creative by 
adding in a Custom Check here using formulas,   but in this specific case we want to add a list, 
this means we're adding our own Drop-Down. Now,   under Source we have different options, we could 
use Cell Referencing to keep things dynamic or   we could input something directly in here. So, let's 
say these divisions are super static and they're   hardly ever going to change, I could actually type 
in Game Productivity and Utility here and I just   have to use my usual Excel separator between 
these. Okay, I'm just going to go with okay and I   get this little down arrow here, when I click it 
I can choose between the three different types   of divisions. Now, if these divisions were likely 
going to change, so let's say it could be that I   rename the Game Division to the Games Division, 
it's better to use Dynamic Cell References and   have these divisions somewhere in Excel Cells, but 
in this case I've added them to my Master Data   tab and I've typed in the name of the division 
right here. So now instead of fixing them, I'm   going to use Cell References, so let's go back to 
Data Validation and for source I'm just going to   say Equals and let's go to Master and highlight 
this area and say okay, and now I have made three   different divisions. So, if I change the name of 
the game division to the games division, it will   automatically reflect in here. What happens if I 
add in another division to this? Is my list gonna   update automatically? So, let's say I'm going to add 
in the Health Division on the bottom here. I'm   going to go back, click on this it's not included 
right?, because my list was restricted to that   range that we saw here. So, to include it I need to 
update this range but there are ways around this   where you don't need to update this range. I'm 
going to show you two different ways here. One   is that when you add something to this list not to 
add it on the bottom here, but to add it somewhere   in the middle. So, let's say I'm just going to insert 
a row in here and then push Health in between. Now,   if I go back I can see Health shows up here, but 
you can also do that by using the Shift Key and   bringing something up. So, let's say I'm going to 
add a new one here, now it's not going to show   up on this list yet. If I go back here and I use 
the Shift Key and then I bring it up here, you   basically insert a row before this. So, when I go 
back here I can see that new has been added to   the list. Okay, so that's one approach but that's 
something that you have to remember to do every   time you add in a new division, and let's say this 
is something that is gonna update quite often and   you don't always want to think about doing this. 
You just want to add something to the bottom of   this list and you want your data validation to 
update automatically. To do that, you can turn   this into an official Excel Table, all you have 
to do is to click somewhere inside this Data Set   and then press CtrlT. We get this pop up where it 
as does your table have headers, in this case it's   recognized that I have a header, so I'm just going 
to go with okay. Now, automatically Excel assigns a   table style here, I'm just going to go and clear that 
Table Style. Right here on their table tools, Excel   has also given this a default table name, so in 
this case it's called Table1. So, I'm just going   to update that name and call this TableDiv and 
press Enter. Now, the advantage of Excel tables is   that your ranges generally updates automatically.
So, now if I make a formula, I just say this equals   and I'm going to highlight this area. You can see 
that Excel uses this type of table referencing.   TavleDiv is the name of my table and division 
is the name of the header and is putting it in   square brackets and that's the type of reference 
saying, that I want to use in my data validation.   So, I'm just going to copy this press escape to 
leave and go back to my data validation here.   For source here, I want to use that table reference 
things, I'm just going to say equals and I'm going   to paste that in. Now here's the problem, detail 
validation doesn't like the table referencing   style directly in here, the way around it is to use 
the Indirect Function. I have separate video that   explains how indirect works and I'm going to put 
the link to that in the description of this video.   Basically what it does is that it turns a text 
into an address, so I'm going to give the indirect   function this reference as text, all it does is 
that it translates it into a range reference   and I'm gonna say okay now, I can get the data 
validation to work and now the great thing is   if I add something below this, so let's say I add 
in my health division, I don't have to worry about   bringing it in the middle of this data set. I can 
see that my table here expanded automatically and   if I go back here, when I click on this drop down 
I automatically see health in here. There is also   another way around this and that's to use the 
Name Manager. So, instead of using the indirect   function you can give that table reference a name 
inside name manager and use that name inside the   Data Validation. The Indirect Function just helps 
avoid this one extra step. Now, let's improve on   this a little bit, let's add in some text here so 
that the user knows what they should be doing in   here. Under Data Validation, we have some options 
to add in an Input Message and an Error Alert   Message. Select Division and the input message is 
"Please make a selection from the list". Under error   alert, you can define a custom message, in the first 
step let's just not define a custom message let's   just go with okay, we can see SelectDiv, "Please 
make a selection from the list". If they don't make   a selection and they just type in "prod" here, they 
get this default Excel error alert message and it   says this value doesn't match the data validation 
restrictions defined for this cell. So, you could   either just go ahead with that one or you can 
customize it by putting in your error message   in here and say okay. So, you don't necessarily need 
to add in a title. So, I'm just going to go with "prod",   now I see, "please select from the list". Okay, so that 
takes care of the division. Now, let's just take a   quick look at the other options that we have under 
data validation. Let's say that for Project Start   Date, we've added a comment here that says "Date 
must be larger than today's date", so whenever they   open this spreadsheet and they want to input, they 
need to pick a future date and we don't want to   rely on the people reading this comment because 
from experience we've seen that they don't read   these comments, so we just want to make sure that 
we add in checks inside the cells. We're going to   go back to Data Validation, we're going to go back 
to Settings, make another selection here. In this   case, it doesn't really make sense to add a list 
because it could be any date that's bigger than   today's date. So, which one do you think we can go 
with? Let's go with the Date Option here, we can   choose that the date is between two different 
dates. So, let's say we want the dates to be in   2018, we could make sure that the start date is the 
first of 2018 and the end date is the last date in   2018, but in this case we just want to make sure 
that it's greater than today's date. So, our start   date is going to be dependent on the date of today 
and for that we can use the today formula. You just   have to open and close the Bracket because it's 
a function. Here we can add in an Input Message,   "Please input project start date", and under our 
alert, "Make sure it's a future date" and say okay. So,   "Please input project start date", now if I input a 
date from the past, let's do 7/7/2017, say make sure   it's a future date, so I'm going to retry change 
that to 2018 and it works. Next is the project   number, so we want to make sure that the project 
number is four digits and that those four digits   are between 1,000 to 2,000. Let's just say that 
these are the project numbers that our company   has decided to sign to the projects that we're 
doing in the current year. Let's go back to Data   Validation, under Settings which one do you think 
we can go with? Let's check whole number, we can   go with between and we can define a minimum and 
maximum and the minimum was 1,000 and the maximum   was 2,000 and I'm gonna mention that under error 
alert. Okay, so if I input 1,000 it's fine, if I   input this it's not okay. Next one is Company Code, 
so the code must be five characters. I want to make   sure here that the people don't input the name of 
the company but the code of the company. I could   give them a list to choose from or I could just 
do a basic check that the code that they enter   is five characters long, because that's how we have 
defined the codes in the company. So, I can go back   to Data Validation here, go back to Settings, and 
which one do you think I can pick for this? Text   Length. I can make sure that the Text Length is 
equal to five and say okay. Okay, so it must be   five characters, so I would say "S" let's say "20" 
"10", that's okay but if I do "SE2010" it's not okay.   Now, what you can do is to take it a step further, 
you could also make sure that the first character   is a text and the remaining characters are numbers 
and that's when you can use the Custom Function   and use formulas for this. You could even take this 
step further and make sure that the first two or   three characters or text followed by a number 
in between, followed by text in the end right? As   long as there is some logic you can program that 
inside the custom function. I'm not going to show   it in this lecture because that's a more advanced 
topic that needs to be addressed separately, so if   you're interested to find out how that could be 
done just make a comment underneath the video.   Last is for Project Description, here I could just let 
them input any value but just to make sure that   they do elaborate a little bit on this project, I'm 
going to make sure that the text that they input here   is at least greater than let's say 20 characters 
and say okay. So, if I just say "it will be amazing",   I get an error, "because it's for our health", then 
this is accepted. Now, of course for that you can   also add in an error alert so that the people know 
why you're not accepting their input. So, now we've   created all these data validations for the first 
lines, but they're not applicable to the next lines.   To copy and paste the data validations here, all we 
have to do is to highlight this area, press CtrlC,   then just go down until where you think that 
people are going to input. So, let's go to here and   I'm going to highlight this Right-Mouse Click, go 
to Paste Special, and click on Validation because   we only want to copy and paste the validations and 
not the contents of this. Okay, so now we can see   them everywhere, so if I go back to Validation 
here we can see that my Validation is in place.   Okay, so that's how you can use Data Validation 
checks to make sure that your template has less   mistakes. At least, you can take care of the major 
ones by restricting the choices that the users   have. This actually ends up making both your lives 
easier. Now I'm curious, do you use Data Validation   in your files? If yes, let me know how you use it in 
the comments below and as always if you liked this   video, I'd appreciate it if you gave it a thumbs up 
and for more of these videos consider subscribing.

Video URL: https://www.youtube.com/watch?v=bDXQy60BcT4
in this tutorial I'm going to show you a way to create complex data validation checks in Excel so that you can make sure the users of your file who are inputting data in your template that they don't make any mistakes especially if you need to make sure that they input the correct combination of text and numbers in a Cell so in the previous video we covered the basics of excels data validation if you miss that I'm gonna put the link to it below the video but what if we have more complex cases [Music] to get a hang of how excels custom data validation works let's try and solve for these three cases in the first case we want to create a data validation right here where the user can input company code but we have to check that the first character is any text followed by four digits how do we do this check we can use a formula for it so if we go to data validation right here under in the data tab on data validation we can select custom and we can input our formula right here but I'm not going to do that I'm actually going to input my formula in the cells right here because when it inputted in the cells I get Excel help with my arguments right so whenever you're dealing with more complex formulas it's always easiest to write it in the cell first and then once you're happy with it copy and paste it in here okay so let's go back and think about our formula we need to do a few checks here what are those checks well one check is that we need to make sure that the total number of characters equals five gray so that's one check so I'm just gonna write them here and we can do each single check separately and then we can combine them into one check what's the formula for this one well we can use the Len function right that gives us the number of characters into text we're gonna have our data validation here so I'm just going to click on this close bracket press enter so now let's just put in some dummy text here so I'm going to put C 2 3 4 5 ok so total number of characters is 5 ultimately I want these to result in true and false values so I'm actually going to write out the test here blend a 5 equals 5 okay in this case it's true if I delete any of these numbers it's going to revert to false okay what's the second test let's do the numbers first we want to check that the last four characters are numbers which also means that the combination of these four numbers must be a number which formula can they use to strip out the last four characters I can use the right function I just have to click on my text and decide the number of characters I want to show from the right-hand side of the full text here and I want to get four out I see my number here and I could check if this is a number right that's something that's going to give me true and false values so let's wrap this up in the is number formula now I get a false here so it doesn't think two three four five is a number why well let's take a look at it I'm gonna highlight this and press f9 we can see our number but it's inside quotation marks it actually sees it as text I'm gonna press controls that to go back to turn this into a number we can perform some mathematical operation on this so we could multiply by one we could add a zero to it or there is also a function that allows us to do that it's called the value function we could wrap this up in the value formula okay so now let's check this it's true it does see it as a number right so if I change one of these to let's say a character I'm going to put teen there it's fall okay so this part is fine as well now for the last check we want to see if the first character is non-numeric there is a function in Excel and it's called is text but similar to is number this text is also going to return yes four numbers inside attacks so I'm just going to keep it simple and instead of checking if it's text I'm just gonna check if it's not a number so if it's not numeric but first let's strip out the first character of this what formula can I use here the left function that's my text I just want to get the first character out so I put one in there and I get a C now if I put this in is number and again I put the value function in there yes I need another bracket I get false I want this to revert to true if it is text so I'm just gonna put not so basically if it's not numeric then it should return a true so let's just test this if I change this to a number this one is false if the total number changes this one is false so basically the way we can bring these together is with an and function only if each single result reverts to true this means that the value input has passed the validation to combine them to one formula I'm just going to activate my clipboard click on this little icon here you can also do the shortcut key ctrl C C if you already have a tick mark here so let's start copying just going to press ctrl C on this press escape to leave and repeat the same thing for the other formulas right here I'm gonna combine them all together I'm going to say equals and and go through my clipboard so just click on this Excel separator click on this operator and click on this close the bracket press ENTER ok so that's my final formula I'm gonna highlight copy press escape go to where I want to have my data validation go back to data data validation select custom and paste in the formula let's check that doesn't work that doesn't work let's just put all numbers as well that doesn't work that works ok so once I'm happy with it I can copy and paste my data validation to wherever I want so I'm just going to highlight these cells right mouse click paste special and select validation I can also add in and input text here so going back to the data validation I can put an input message here just to make it clear what the people should input and also why they're getting an error and this is what I showed you how to do in the basics of data validation so I'm just going to move on now to the next one here we want to have the first two letters fixed and we want them to be PT followed by four digits how could we set this one up in this case our checks are similar to this one right except that the full length of this should be 6 again I'm just going to put one example here so let's say PT 4 5 3 4 that's one check the other check that the last 4 characters are numeric that's the same so I'm just going to copy and paste this here now the only thing that's quite different to what we did before is that we are actually checking what the first two characters should be so what formula could we use here we can use the left function but this time we're going to make sure that the two characters from the left-hand side equals PT ok and that's it I'm just going to clear these and copy and paste them back to my clipboard now again I'm gonna combine them with the ant function so just click on this excel separator click on this and click on this one what was the next step we need to copy our formula press escape go to where we want to have the data validation go back to data validation settings under custom we're gonna paste in our formula okay so let's just quickly check if I have TG okay so PT any number it's gonna do the job now obviously once you're done with this you're gonna remove all of these helper cells this is just to help you write the formula for the custom validation next one for this case the first two letters can be anything how do we account for that the part that's the same I'm just gonna copy and paste here for the last check where we're checking for the two letters how do we do that could I actually just use this formula that we originally had where I'm just checking the one letter could I just update this to check two letters and see if they're not numeric let's try that basically everything is pretty much identical to what we did in the first case except our length is six instead of five the only other thing I changed is that we're gonna check the first two characters and see if they're not numeric I'll put TT and a number this looks good I'm gonna change these to two numbers this looks good because the last one should give me false but now what if I have one letter and one number this should also give me false but it doesn't it gives me a true and the reason for that is that when I take this part and I press f9 on it I get false because the combination of B 4 is not numeric and that's the problem here what I could do is to add another condition here and check for the second character and make sure the second character by itself is not numeric I'm going to keep the original condition the same I'm just going to check the first character right so that shouldn't be numeric but now I'm gonna do a similar thing for the second character except here there is a better function for this the mid function the mid function allows us to take any specific text from this whole text here and we can decide where we want to start first argument is our entire text then we can specify where we want to start where do we want to start now number two right the second character and then we can decide how many characters we want to strip out we just want to strip out the second character only so I just need one character and here I get a four so now I can do a similar thing like in this formula use naught is number and value so I'm gonna be a little bit lazy and copy this part and paste it in here and I need a few brackets and one more okay so now I get one false here which means that my full combination is going to be false if the first one is a number and the second one is not this one is going to be false and if both are numbers they're both going to be false right so you can also use other array functions here so you could use the aggregate function but the data validation box doesn't like that in there directly so you'll have to go through name manager and then to data validation writing it in this way avoids your using an extra step of going through name manager okay so again we need to do the same thing I'm just going to copy these formulas combine them with an ant function and just go through my clipboard and then close the bracket this entire formula I'm gonna copy it go to where I want to have my data validation go back to custom and paste my formula so now let's check this out it's good there's a problem that looks good okay so that's how you can use custom data validation to add more complex checks inside the templates that you send out to your users if you found this video useful I'd appreciate it if you gave it a thumbs up and for more videos like this one don't forget to press the subscribe button so that you can get updates when new videos come out [Music] you

Video URL: https://www.youtube.com/watch?v=PiHO5TzHjrk
in this video I'm gonna show you how you can set your excels default theme color to be the theme color that you want by default every time you open Excel it always reverts to the office theme color in your company you might already have defined theme colors that you can browse for and bring in as your custom themes right so if you always want to use your own custom themes you will always have to come here and then click on this so that it switches the theme color but let's say you want to avoid this because 90% of the time you want to use your own theme color and not the office theme colors every time you create a new workbook you want it to take this now unfortunately Excel doesn't have any option here that says set as default theme color unlike for example words so if I switch to word you can see under design you can select the theme that you want and you have this tick mark set as default we don't get that in Excel to do that you need to save an empty Excel spreadsheet with this theme as an Excel template inside your Excel Start folder how do we do that well first of you will define a theme a theme is basically the combination of this this and this that's what makes the theme and the reason this is important is especially for your charts why because let me just highlight this and insert just any chart okay that's the color set that comes in and where are these colors coming from well let's take a look at this if I go to customize color it's coming from accent 1 accent 2 accent 3 so the first series that is in your chart is accent 1 the second series is accent 2 and so on so defining this saves you the time of always going back and chain changing your series colors and by default the office colors are these and that's not the colors that you always want to use once you define your theme colors you can do it by customized color so we can quickly do one let's do accent one is black accent two is dark gray X and three is orange so I'm going to set my own colors I'll call it LG default one and save it's right here you can also change the fonts and define your own fonts and once you're happy with this set then you can save it as a theme so come here and say save current theme and I'm gonna call it theme LG default okay office theme it's fine it's saved by default on their document themes in my templates folder so I'm just going to save that you open a blank workbook then you go to themes and you select the color that you want so that's the only change that you make to this workbook it's all blank and I'm gonna go to file and save as template you can save it directly under your Excel Start folder but it might sometimes be difficult to find it from here if you're lucky you just have to go a few steps back and you see that but if not you actually have to locate that folder what I do is that I'm just going to save it on my desktop and then search for that folder in Explorer the other important thing is that you delete that number I'm just gonna press save I can close this let's just close all my Excel I'm not gonna save that I'm gonna go to explore and what you can do is find your excel stop folder by searching for it here because I've already done that it took a couple of seconds or so and it found that folder and I came here I just need to grab that book templates I'm gonna do ctrl X and go here and paste it in it's sitting in that folder I'm just gonna go open some excel file so let's go to open this one something I had opened last check this out I'm gonna press ctrl n and go to page layout and it's showing my theme color that I chose every time you press ctrl + 4 new or if you already have it in your quick access toolbar so I have that one if i press this i go to page layout i see my theme color that's how you can create your own default Excel theme three important things is 1 saving it in Excel start folder 2 is that you take away that number when you're saving it so if it's book 1 book 2 you take away the number and 3 is that you save it as an Excel template and don't forget that it should be a blank Excel spreadsheet I hope you enjoyed this video and I hope it helps you save some time when you're creating new Excel reports

Video URL: https://www.youtube.com/watch?v=Q6b315vRNrg
In today's video, I'm going to show you how
you can add a header and footer to your worksheets. For example, let's say you want to add in
your company logo to the header of each printed page, or you want to add in page numbers to
the footer of your printed pages or to your pdf documents and also how you can tweak the
logo or the picture to make sure it fits properly inside your header. One question I get often is how to apply the
same header that you take the time and you create it for one of the tabs, so how can
you apply that to other tabs at the same time? This is a sample spreadsheet that I have open. I have a few tabs here I'm currently in tap
T2. Let's see, for this one, I wouldn't add in
a header. Now there are different places you can do
this. One option is to do it from page layout so
you can go here and under page setup, you have this little icon here, just click on
this. Then you just go to header and footer, and
right here you can add in a custom header or you can add in a custom footer. Another way to add headers and footers is
to go to the view tab, and this one is actually more user friendly. I'm going to show you why. So in the view tab, click on page layout. Here you can see your page as it would look
once it's printed right here, you get the option to add in your header from the new
design tab that you see pop up here. So once you click inside here and you activate
the header, you get a new tab here. This way you can immediately see how your
changes are going to look on the final page. I have the option to add in the headers, either
a header on the lefthand side, the middle or the right hand side. I can use all three, or I can just use one. Let's say in this case on this side, I want
to add in the file name. So all I have to do is click on the filename. I see this code right here, I'm just going
to click away and I can see the file name. Now I also have the ability to add in a second
line. If I just press enter, let's say I put in
the sheet name and I click away. I see the sheet name, which is T2 right here. Now you have other options so you can add
in the current time, current date, page number and so on, or you can add in a picture. So let me just remove this, I'm just gonna
press delete and instead I'm going to add in a picture. Let's go and select the file. I'm going to pick this logo and click on insert. Now when I click away, I'm going to see that
picture. So in this case, my logo is not that big,
but let's say I still want to get this a bit smaller. This is where I can format it. So right beside the picture icon, I get format
picture and I can change the height. Let me change that to 70 percent. Just make sure that you leave the tick mark
for lock aspect ratio. This makes sure that your picture remains
intact, that we don't distort it in any way. So I'm going to click on, okay, click away
and I can see the picture got a bit smaller. Now let's say it's a bit too high. I want to push it a bit down. I'm going to go back to design, go back to
format picture and inside the picture tab, I can make some adjustments to this. So for the top I'm actually going to bring
it down by 0.2 points. I'm going to move away and I moved down a
little bit. Let's now go to the footer. I have the ability to scroll down or I can
just click here and jump to the footer side. Now on this side you also get some presets,
so now since I'm in the footer, let's take a look at the footer options. I can pick one of these so some of them have
page numbers. Then I have the word confidential here. Let's just see how this one looks. Let me click away and we see confidential. We see today's date and we see the page number. So let me change this, just type in training
material here. Instead of to date here. I'm going to take the sheet name and for the
page numbers I'm going to actually get the page, but I want it to be from the total number
of pages. So I'm going to put the dash line here and
put number of pages and click away. I see page one of two. Okay. So if I scroll down, I can see here page two
of two and that's tab T2 training material. Let's go to print preview up here. We can see how everything looks on the printed
page I guess. So let's say I'm happy with this layout and
I actually want to apply the same layout to different tabs, because take a look at T3
currently. If I go to print preview, it doesn't have
a header. If I go to T1, this one also doesn't have
a header, right? So I want to apply that same header to these
ones as well. Here comes the trick. Make sure you have your originals clicked
on, so highlight it and then click on the other tabs that you want this to apply to. In this case, I'm going to click on T1 and
T3. Now if you want us to select everything, you
just have to right mouse click and select all sheets. That will automatically everything. Now what you need to do is to activate the
header or the footer and then just click away, and this makes sure that it applies it to
the other ones as well. So now I'm just going to click away by clicking
on another tab and then going back to T3 now, and let's take a look at print preview and
we see that header and footer is applied to these as well. So if I hold all three of them and go to print
preview, says page one of four. Now I'm in tab T2. You can actually see it in the footer right
there. The next page of T2, that's T3, that's page
four of four. That's how you can apply the same header,
to many pages. You can also do that from the page layout. Just make sure that you highlight the different
tabs first. Now let's say if I wanted to take away to
header from all of them with them all highlighted, I'm actually going to do this one from the
page layout. Click on More options. Go to header and footer, and from this dropdown
select none, right? So we want nothing for the header and we also
want nothing for the footer. Let's say Okay, now all of the headers and
footers should be gone. Let's to to print preview. Everything is gone. Okay, so that's how you can work with headers
and footers in Excel and how you can also apply the same header or footer to many different
tabs at the same time. So I hope you learnt something new. If you liked this video, don't forget to give
it a thumbs up and do subscribe to this channel if you haven't done so already and you want
to become more advanced in Excel.

Video URL: https://www.youtube.com/watch?v=2PhaaEWY1pQ
Let's talk about Excel Comments and how we can work with
them better and faster. And then we're gonna
get into the fun part, which is how can we customize
the comment background and how we can use icons as our comments. Just a note, Microsoft recently
announced a new feature called Threaded comments. This means you can reply
to one another in Excel, and you can keep track of it, and they still kept the existing comment feature though, thankfully. But this resulted in a
change in feature names. So, what me and you
know as an Excel comment is gonna be called an Excel note. The new comments have a Reply box, which is actually another
great feature of Office 365. So, in this video, I'll be talking about what will be known as notes in the future, and comments for now. So, let's jump in. (upbeat music) Shortcut keys So, in this data set here, I have a few comments. You can recognize them with this red icon on the top right-hand side of the cell. Now, to insert a comment
using the shortcut key, all you have to do is press Shift + F2. So, remember, F2 edits the cell contents, and Shift + F2 edits or
inserts the comments. And then all you have
to do is start typing. And if you wanted to edit this, press Shift + F2 and continue typing, or edit the existing content. Now, you can also insert
comments from the menu, just go to the Review tab
and click on New Comment. You can jump between comments
by clicking on Previous or clicking on Next. You can also insert a comment
by right mouse clicking and selecting Insert Comment. To quickly show all the comments that you have on this sheet, you can go to the Review tab and select Show All Comments. It's a toggle. So, if I click it again,
it hides all comments. If I'm on a specific cell and I just wanna show that comment, I can select the first
one which is Show/Hide this one specific comment. If you wanna print your comments, you have two different options. You can print them in this way, or you can print them at
the bottom of your sheet. To define which method you
want, go to Page Layout. Under Sheet Options or under Page Setup, click on this little arrow
here to see all the options. Under Sheet here, you can decide how you want the comments to be printed. So, if you select As displayed on sheet, they're gonna get printed if
they're visible like this. So, if I go to Print Preview, I can see only this
comment will be printed because only this one is visible. Now, instead of this, if I select print them at the end of the sheet. Let's go directly to
print preview from here, I'm just gonna scroll down, we can see all the comments
are printed separately here. If you wanna copy a specific comment and paste it to another place, all you have to do is
Control + C that cell, then go to the other
cell or different cells where you want that comment pasted, right mouse click, go to Paste Special, and select comments from here. Now, notice when you do that, the cell content is not impacted. The only thing that's copied
and pasted is the comment. You have some options to
customize the comments. So, once you show it, just right mouse click and Format Comment, or use the shortcut key Control + 1 to format the comment. You can change its background color by selecting a different color from here. I can take away that
border by selecting No Line and changing the font color as well and the font type if I like. So, if I just press OK, I get this effect. Now you also have the ability
to add background images to your comments. So, let's say I wanted
to create something fancy and I wanna have a specific
icon as my background image. What I'm gonna do first is
to modify the icon slightly and save it separately as a picture, so I can bring it into the comment here. I'm gonna do that with PowerPoint. So I have PowerPoint open right here. I'm gonna go to Insert Icons. Let's go with this one. Let's also take this one, and click on Insert. This icon right here is
an icon that I'm gonna use as a background icon, and I'm gonna use this
one as the comment itself. So, let's say I agree with my colleague that anytime they see this
comment in my workbook, it means that they need to
add that to the task list. Now, all I have to do is
save this as a picture. But first, you can see it has a lot of white
space in the background, so I'm just gonna crop that out, and I'm gonna save this as a picture. So, right mouse click, Save as Picture. Instead of Vector Graphic,
I'm gonna select PNG. And let's call this screen comment. Okay, so, while I'm here, let's also save this one as a picture. I'll just leave that as is, and let's change this to PNG as well. So now I'm gonna go back to Excel. Let's activate the comment, click on the border of the comment to select the entire comment, and press Control + 1 to format it. Now, under Color and Lines, just the same place where
we change the color, we can also go to Fill effects and we can select a picture. So, I'm gonna bring in
the picture I just saved, click on Insert, Lock
picture and aspect ratio, and click on OK. Now, for that line background,
I have it as No Line, which is good because I
don't want this to have an additional border. So, let's just quickly take
a look at how this will look. Okay, so, it's just a bit cropped. I can expand that. Now, my text is being
covered by that dark border. So, I can format that as well. So, let's just go back
and click on Control + 1. Under Margins, let's go with 0.25 for all of them. Let's just copy and paste. So this brings in the
text more in the middle. So you can adjust that as you see fit. Now, if you wanna apply this
comment to different cells, again, all you have to do is Control + C, click on the different
cells, right mouse click, Paste Special, and just select Comments. I can of course toggle them out of view. So, I'm gonna click on Show All Comments and click on it again, and they are hidden. Now, we also brought in
that check list symbol. So let's say to this cell, I'm gonna bring in that symbol. I'm gonna right mouse
click, Insert Comment, click on the border of the
comment, press Control + 1. Go to Colors and Lines, Fill Options, Picture, bring in the picture, lock aspect ratio, say OK, and remove the border from this. Okay, so, I'm just gonna
delete my name from there, and we can see my symbol in here. So, we can adjust the size as we see fit, and then we can copy and paste this just like we did before. So, those are the different ways you can work with Excel notes. Let me know in the comments below if any of this was new for you. I'd also appreciate a thumbs up. Be sure to subscribe to this channel if you want to improve your Excel skill. (upbeat music)

Video URL: https://www.youtube.com/watch?v=UN5PckRADyQ
Today, let's take a look at calculating percentages in Excel. We're gonna take a look
at the formula you need to calculate percentage change and we're gonna do it in a way that's gonna help you remember it. If you're working as a financial
analyst creating reports, this is a formula you need to get right. We're also gonna take a look
at how we can do calculations that involve percentage
increase or decrease. So for example, we have a price. We want to increase price by 15%. What's gonna be our end price? Let's get to it. First off, let's take a look at calculating percentage change. Assume your boss gives you an Excel file that contains actual sales
data and budget sales. Your task is to calculate
percentage change. Let's take this to the board. So you have actual and you have budget. To calculate the difference, you're gonna do actual minus
budget divided by budget. An alternate way of writing this formula is actual divided by budget
minus budget divided by budget, which turns into actual
divided by budget minus one. Now it's all clear, right? So our formula is actual
divided by budget minus one. Double click on the bottom right side and send the formula down. (light percussive music) Now let's move on to calculating percentage
increase or decrease. Our starting point is in column A. We want to increase the price by the percentage we see in column B. And if it's minus, we want to decrease it. Let's take this to the board. So we have our price. To calculate the price after we add the increase
or decrease to it, we're gonna add this with price multiplied by that percentage. An alternate way of writing this is to factor out the price, so it's price times one
plus the percentage. So that's another way
of writing the formula. So in Excel, this would be starting price multiplied by one plus percentage change, close bracket, press enter. So if my starting price was 100, I increased price by 10%, I get 110. If this was 5% instead, I get 105. In Excel, there's one thing
you have to be aware of and that's how you input your percentages. One method to input percentages
is to add it while you type. So if I come here and type in 20 followed by the percentage
sign and press enter, the cell is automatically
formatted as a percentage. Another option is to
input it as a decimal, so 0.05 translates to 5%. So if I was gonna pull this formula down, we can see it adds up correctly as 5%. Now I can change this to
percentage and I get 5%. Now another thing you can do is to format your cell as percentage first before you input your full number. But don't do it the other way around. So for example, here,
if I was gonna input 30 and then I decide, well,
this should be a percentage and I click to format
this as a percentage, I get the wrong number. So if this was already
formatted as a percentage, I go in and input 30, then it works fine. Okay, so let's just
pull this one down too. So that's it. Just remember, for percentage change, it's new divided by old minus one or end divided by start minus one. For percentage increase,
we have the formula your value multiplied by one
plus the percentage change. And if it's a percentage decrease, it's your value times one
minus the percentage change. But if you input your
percentage as negative in Excel, you can use the same formula. Just drag it down. So the next time you're asked to calculate the monthly or yearly change,
you know how to do it. If you like this video,
click that thumbs up. And if you want to
improve your Excel skills, consider subscribing to this challenge. (mellow music)

Video URL: https://www.youtube.com/watch?v=3naynygx_dU
Today's video is about
Find & Replace in Excel. Now you're probably
familiar with this feature from Office applications, so you will be wondering is
it even worth doing a video on Find & Replace? Isn't it super straightforward? I think you're gonna be surprised. (upbeat music) Let's use these demo
files to do some examples. First question, how do we
get to the Find dialog box? We can go to Home, Find & Select and click Find from here or we use the shortcut key Control + F. You're probably familiar
with the Find functionality, not just from Excel but also
from other Office applications so I'm not gonna bore you with that. What I just wanna show you are some additional options
that can come in really handy in Excel and they're hidden
under these tab options. So, if you're looking for something, let's say I'm looking for leila, I can decide if I wanna look for it within the sheet or within
the entire workbook. So, by setting this to Workbook, it goes through every single tab and it looks for the name
that you typed in there. You can search by rows or by columns, that just decides the search direction, so if it should go this way or if it should go this way. This part, look in, can come in handy especially if you're looking
for something inside Comments. So, let's say I wanna look
for leila inside Comments. I just have to switch this to Comments and I'm gonna switch this as well and click on Find All or Find Next. The advantage of Find all is
that it gives you the list of all the occurrences and Find Next just jumps to the next one, so there is a comment here and it has my name in there. There were two other options in there which was Formulas and Values. These are pretty much the same except that formulas also
looks at the formula text. If I had this on Formulas and I wanna look for leila, and I click on Find Next, it goes through every tab in the workbook and it doesn't find there because leila only exists
inside the comment. The list that you get here can also come in really handy, so let's say instead of leila, I'm gonna look for game and I'm just gonna look
for it on this sheet and not in Comments but in Formulas and I'm gonna click on Find All. This gives me all the occurrences that game has occurred on this sheet. The good thing is this. I can press Control+ down
to highlight everything here and it doesn't only highlight it here but notice it also
highlighted it on my sheet. So, if I leave this, I can see
all of these are highlighted and I could change the color of the cells to yellow or do any
other type of formatting that you'd like. Another thing that can come in handy is if you're searching
and replacing something and you also wanna change the formatting, let's go back to our dialog box but this time let's jump to the
Replace dialog box directly. So, what's the shortcut for that? It's Control + H. But here let's replace game with Health but instead of just
replacing the words here, let's also change the formatting, so on the right-hand side, we have the ability to decide
the format that we want. Click on Format here, I'm gonna go to fill and I'm gonna select New
Color and go with that. Now I'm just gonna click Replace All for everything that's on this worksheet. And it made 12 replacements. This part of searching and replacing for specific formatting
can come in really handy. For example, let's say
that your company decided to change its logo color and you need to replace the
colors that you used before with the new colors. So, let's say in this case, I have this light blue color used here. I'm using it on a lot of tabs and I need to replace these
with a light green color. Here I can use Find & Replace to do that. I'm gonna go back with Control + H. Since I'm not looking for specific words, I'm just gonna delete them from here but instead I wanna look
for a specific formatting. I'm not exactly sure what
type of blue this is, so I'm gonna click on Format and say Choose Format from Cell. So, I can be lazy here
and just click on this, I picked up that format, now I'm gonna go and decide the format that I wanna have instead. Let's go with this lighter green. On this sheet, I'm gonna replace it all, it made five replacements. It didn't touch these
and it didn't touch this. Why? That's because it picked up all attributes of the cells, it wasn't just
the color that it picked up but it picked up the number formatting, so notice this one has percentage and not number, this one is bold, so it also has a different
formatting to this. This means that if you
just wanna change the color and not take a look at the
other aspects of the cell, then make sure that you actually just pick that specific color from the dialog box. So, let's go and check what
color blue this actually is. I'm just gonna go to the Fill Color here, go to More Colors, under Custom here, I need this code, so that's the RGB, let's memorize this and let's go back to our Find & Replace,
I'm gonna go Control + H and the first thing I'm gonna do is to completely clear
this formatting here, now I'm gonna go to Format and pick up that color that we saw. So, under Custom, let's type in that code. Now let's do this for the entire workbook, click on Replace All. That looks through every single tab and wherever it found that color, it replaced it with the new color. So, you see, Excel's Find
& Replace can do a lot more than just search for text. I can also search and replace formatting which can come in really handy too. Let me know in the comments if you've used this aspect before. And if you like this video, click on that thumbs up and do consider subscribing
if you would like to get updates when I
post new videos here. (upbeat music)

Video URL: https://www.youtube.com/watch?v=LN6BB5p6lGc
How can we change the
case of text in Excel? So, for example, switch from
lower case to upper case so we have everything in all caps or clean up mixed case text so it's either all lower,
upper or proper case. (bright music) I'm gonna show you three different ways of getting this done. Each has its own advantages
depending on the situation. So first let's take a look
at the formula version. I have here a list of names. Some are lower. Some are upper. Some are a hot mess. What I'm gonna do here
is to convert them all, first to upper case. The formula is really simple. You just have to type in upper. Put in the cell reference
where your text is sitting on. Close bracket. Press enter and you get everything
in upper case letters. Now if you don't want the
formula in there, If you just want to
convert them to values, you can just control C, so copy and then paste special as values or just highlight the area,
right mouse click on your range, pull it to the side and push it back, let go and then select
copy here as values only. So this will take away the formula that's behind this. For lower case, very simple. Just type in lower. Click on the cell that has
your text and send it down. Everything is in lower case letters. Proper case: Guess what the formula is? It's proper. Close bracket and send down. There is one
thing I noticed here James Willard seems to
have an extra space in there so when I click here, it has two spaces. There is a way you can get
rid of all extra spaces whether it's in the
middle, before or after. So if you take a look at Gary Miller, right after R, there
is also an extra space. The way you can get rid of it
is to use the trim function. Trim. So just remember when you trim your hair, when you trim something, that trims the additional spaces as well. James Willard looks good. There is no additional space in there and if I wanted to turn
this into proper case, I can either trim the
proper format of this or I can just trim it first
and then put the proper formula in there and then send this down. Another option you have
is to use flash fill. This is good for cases that
you don't need to be dynamic. So basically for one time fixes. All you need to do is type
in one or two sample data and Excel is going to look for a pattern. Once it recognizes that pattern, it's gonna apply the pattern
to the remaining cells. So in this case, I just
typed in the first one. I'm gonna highlight the area I
want to apply flash fill to and then press Control E. You can also get to it
by going to the home tab and selecting flash fill from here. So in this case, it did
recognize everything pretty good so let's see if flash fill
can also get rid of the space. Let me just add an extra space
in here to see if it works. Now this is static, right. So anytime you make changes here, it's not gonna pull through. You will actually have
to repeat flash fill. What I'm gonna do is
now for the second case, type in again James Willard
but without the space and I want to see if flash fill is gonna recognize this pattern and take away that additional
space here as well. So we're going to highlight this area, shortcut key, control E. It did. It did recognize it and it
removed that extra space. But let's try this quickly for lower case. It figured something out but it's again giving me that extra space in there. The rest looks good so let's say I don't want that extra space. Now I'm gonna apply flash fill to this and it looks like it's figured it out. Proper case: It worked as well. Flash fill can come in super handy for these type of changes. Just double check your data because it does work pretty well. It is highly reliable, but
of course, there can be times that it doesn't recognize a
pattern and it makes mistakes. Now let's take a look
at another situation. What about page titles? Let's say you want to
have your page titles always in upper case and
you don't want to worry about how you actually input them. What you can do is to use a font that's by default, upper case. So it doesn't even have
a lower case version. And if you look here in your fonts, you might find a few
that are in upper case and you're gonna notice them
if you see it listed here as upper case only. It means that there's only an upper case version of that font. Now I put some of the ones I
saw in my Office version here, but you're not restricted
to what you have in Office. There are a lot of great
fonts that are available that you can download and
a lot of them are for free. So I've put some of the font websites that I've used in the past here. Now if you click on one of
these and go to the website. This is dafont.com Here you can download a lot of fonts. But just be careful under usage. So here it says, this
one is free for personal use. Free for personal use. Let's just switch to top fonts here. So this one is public domain and take a look at the font. It's all caps, right. So all you have to do is click on download and then just run this and then by default
it's going to install it for all of your Office applications, not just Excel but everything else, and then when you're in Excel, you click in your fonts, you should be able to find it here so that one was BEBAS
NEUE and it's right here. So whatever you type. Whether you type in
lower case, upper case, mixed case, doesn't matter, it's always going to show
it to you as upper case. So if I say kpi report. Notice that I'm typing it in lower case, but I'm seeing it as upper case. so you can use these
type of fonts as your title. Now sometimes it's
difficult to scroll through this huge list to find the
font that you're looking for. So one hint is to use
cell styles for that. Here you have a list of default styles that Excel has for you. What you can do is to customize
them for your own need. So for example, this heading one, let's just right mouse click
on it and modify it. Then just click on format and then decide what type of format you want to have. So for this one, I want
it to be the BEBAS NEUE that I just imported. I want it to be a bit bigger. Regular, so I can also change that border that it had before. So instead of the blue, I
want it to be gray and OK. And OK. So right now, let's say
I'm gonna add a title here. My new fonts. And I want to apply that cell style. I go here. It's my new heading one and I can see my new fonts here. Okay, so don't forget
that you can use this if you want all caps without formulas. Just use a font that
fits best to your report. Okay, so that's it for today. I hope you like this video. If you did, give it a thumbs up and if you want to become
more advanced in Excel, if you want to improve
your Excel knowledge, consider subscribing to this channel. (bright music)

Video URL: https://www.youtube.com/watch?v=PiRVgAZnGGA
Today, let's take a look at how we can lock and unlock specific
areas on an Excel sheet. So we're going to take
a look at the basics, but the case that we're
dealing with today has a twist. This was a question from
one of my students, Anya, who asked, "I'd like to leave
yellow cells unprotected. "Is there a way without a
VBA macro to get this done? "This way, I can move from
one unprotected yellow cell "to the next one with the Tab key, "and it would save me a
lot of time entering data." Can we do this without VBA? (mellow hip-hop music) First off, let's cover
the basics for protecting and unprotecting your worksheet. You do that in the Review tab. Under Protect here, you can
protect the entire sheet by clicking on this. You probably would want
to give this a password, and you have to confirm that password. Everything is fully protected, so if you try to input
anywhere in this sheet, you get this popup that it's protected, and the user can only unprotect it if they know the password. Now in addition to this, you have the ability to
unprotect some of the cells and leave the other ones protected. We can do it in different ways. One method is to use Allow Edit Ranges. Now this gives you some additional options so you can set different
passwords for different ranges, but in case we just
want a simple solution, all we have to do is to select the cells that we don't want
protected, right mouse click, go to Format Cells, or use the shortcut key, Control + One. Under Protection here,
you need to take away the check mark beside Locked. Once you do this, nothing happens until you
protect the worksheet, so at this point, nothing is protected. I can input everywhere. So I can input here, and I can input here. This kicks in the moment
you protect the sheet, so now I'm going to go click on Protect. This time, I'm just not
going to give it a password and click on OK. So when I attempt to input something here, it's still protected, I can't input, but I should be able to
input here, and I can. And of course, you can
select different areas and take away that tick mark to have the different areas unprotected once the sheet is protected. So let me unprotect this and just put back that check
mark for all the cells. So when you get this symbol in there, it means there is a mix. Some cells are protected,
some cells aren't. I'm just going to put back the check mark to go back to the original default state. Now let's come to the complex case. My aim is to protect all the cells here except the yellow cells. These cells are going
to be my input cells, and normally, when you have an Excel table and you're inputting data, so let's say I put 23, I press Tab, that takes me to the next cell. Tab takes me to the next cell. When I protect these gray cells and I leave the yellow ones unprotected, this means when I press
Tab inside a yellow cell, it's going to jump to the
next unprotected cell, which is going to be this yellow cell and then this yellow cell. What I want to do is to
unprotect all the yellow cells. The time-consuming way of doing this is to manually select
all these yellow cells, so hold down the Control key, select them, and then, take away that check mark, but this is going to cost me a lot of time if I have to do this on a
large area like this one. The faster way of doing this is this. We're going to highlight the range where we're going to be inputting data, and we're going to use the Find feature. So let's press Control + F. Under Options here, we get the ability to add a specific format. Select Choose Format From Cell and go to one of these yellow cells. Now just keep in mind that when you take the cell
formatting in this way, it doesn't just take
into account the color, but the entire formatting of the cell, so also the number formatting
that's behind this. Click on Find All. It finds everything here, and I want to highlight
it in my spreadsheet, so I'm going to press Control + A to highlight these results, which is going to end up highlighting them on the sheet right here, and let's just close our dialog box. Now what I'm going to do is use
the shortcut key Control + 1 to go back to Format Cells, go to Protection, take away
the check mark beside Locked. Click on OK. Now let's protect the sheet. This time, I'm not going
to give it a password, and let's test. Let's input a number here and press Tab. Jumps to the next yellow cell. Tab, Tab. This way, I can input data so much easier. So this was my approach. If you can think of any other methods, share it with me in the comments below. This was our locking and
unlocking Thursday fun. If you liked it, give it a thumbs up, and if you haven't
subscribed to this channel and you want to improve your Excel skills, consider subscribing. (upbeat percussive music)

Video URL: https://www.youtube.com/watch?v=SZBRFRoGSFY
I've seen this over and over
again in many companies. Someone sends an Excel file in an email, a very important report that management is already waiting for. The person receiving the
email opens the file, they hit print to get the
paper copy of the report to the manager, and then this happens. The wrong bits come out
in the wrong places. Now, I can't tell you how often I've seen managers sitting in meetings, frustrated with their printed reports, when they only had column
headings on the first page, missing page numbers, so they can't even tell
if something is missing. Or even a report where important
parts of the spreadsheet never made it to the printer. Now, I think we both agree
it's quite frustrating, but the great news is it
can easily be avoided, plus we don't waste valuable resources. Let me show you how you can optimize your Excel sheets for printing. This video is brought to you by Excel Essentials For the Real World. Learn Excel from scratch, or fill in the gaps to
become immediately confident. Discover hidden tips and tricks which get you working smarter, not harder. Click on the link below, or
type in xelplus.com/courses. Excel's default print settings generally don't work to your advantage. Default orientation is portrait, but your data sets might
be wider than they're tall. It will make automatic page
breaks based on paper size, scale options, and your margin settings. All of these settings, you can change. And you probably should change before you hit that print button. Let's start off with some
useful printing options. So let's say I have this report. I want to get it printed. First thing I'm going to
do is to take a look at it in print preview. We can get there by going
to your quick access toolbar and using print preview and print. If you don't have this option here, click on this dropdown and add it by putting a check mark here. What it does when you click it, you go to the printing options. Another way to get here
is to go to file, print. Now immediately I notice this report is going to be printed on two pages. This is not what I want. I want it to fit on one page. I can make the adjustments
either directly here in this view or I can go to the worksheet and make the adjustments directly there. You pretty much have the same options. You just have to pick the way you feel most comfortable with. So I'll show you both. Here let's say I wanted
to reduce the margins to save up some space. I can switch this from normal to narrow. It's still fitting on two pages, though. I can change the orientation
directly from here, and select landscape instead of portrait. This is again not something I want. To make this fit on one page, I need to go to scaling here. And I can select "fit sheet on one page." Now, let me show you the
options on the page layout side. There are some useful ones there, too. So let's go to page layout. This is where you're going
to find the printing options. These are what we saw in backstage view. Now, if you click on
this tilted arrow here, you get two more options. Some of it is what you see here. Some of it are stuff that
they couldn't fit here, so you have additional
options available in here. For example, one option
I personally use a lot is the margins, and that's
how I want my report to be centered on the page, because by default, the
report is always centered to the top left-hand side. Sometimes I want it to
be centered horizontally and vertically so it sits
in the middle of the paper. So these are the options that you need, and the margins horizontally/vertically. And I got to it by going
to more options here. You can also get to this
from backstage view, and I'll show you how in a second. But before we go back to backstage view, let's just check some more options here. By default, reports
don't print grid lines. If you want grid lines to be printed, put a check mark for
printing of grid lines. The other thing I like about this view is that I can immediately
see the percentage that's associated with scale. Remember, when we fit this to one page, we basically made it shrink. You don't want it to shrink too much so that everyone can read it. Now, how can you influence this percentage and increase it further? So one way we did was
to adjust the margins, but there's only so much
you can do with margins. Another way is to reduce the space, so maybe you hide any rows or
columns that you don't need. So the more condensed your report gets, the higher this percentage
is going to get, so this already jumped up to 80%. Now it's up to 82%. So let's just go ahead and
see how this report looks. I can get to print preview from here, but let me also show you another option. Remember when we were in this view, we can also directly go to print preview, or directly print from there. So now grid lines were activated, this is how the report is going to look, and it still fits on one page. Now remember how I centered this? I told you I'm going to
show you how that works from backstage view. You can get there by going
to custom scaling options, and you come to the same dialogue box we saw before. This is the type of
report we're dealing with. So in the first step, let's
say we're just interested to print parts of this. We just want the list of
apps and sales for 2019. One way to do this is to highlight the area you want printed
and go to file, print, change this dropdown to print selection. The moment you do that, it shows you what you've
selected on the Excel side, and that's what is going to print. It's one of one. So notice when I go back to active sheet, it's actually by default
going to print this on four separate sheets. Now what if you always
wanted to just print those two columns whenever
someone pressed print? You don't want them to
remember to highlight and then go select print selection. You just want them to
be able to press print and it only prints those two columns. The way you can set this
is to go to page layout, highlight the area that you want, and then set the print area by clicking on print area and then set it. This forces the print area to always only be this area, so now anytime we go to print preview, we only see what we selected, so notice this side is set
to print active sheets. It sees the active sheet
as only these two columns. It completely ignores everything else. So that's the advantage of
setting your print area. Let's remove this print area. Clear print area. Now let's say I want to
print this full document, and I want to have my table in one page and the two charts on another page. How can I make Excel format
this properly for me? I have some options
here from scale to fit. I could say try to fit this on one page, and the height should
be probably two pages. I can try one page. Scale is really low,
which means everything is going to be super small. If I go to print preview, I
don't really want it like this. I want them sitting on their own pages. Now, this also changed
the orientation of this. See if that improves. That reduces the scale even further. How do I get Excel to
actually fit the page breaks to where I want? This is where you can
insert your own page breaks. What I'm going to do is just
set this back to automatic. Let's go and insert a
page break right here. Now let's go and take a look at how this is going to look until now. The scale is still set to 54%. We don't want that scaling,
so let's change that. This part looks good, but
this part doesn't look good. It's cutting through one of my charts. I want both charts to fit on one page. This is where you can directly
control the page breaks. To do that, go to the view tab, and click on page break preview. This takes you out of
normal view and shows you how Excel is planning to print. The dark blue borders are
the ones that we've set. And the dashed ones are the
one that Excel set for us. Now this one I'm not so happy about, so I'm going to pull it that way, because I want it to force the page break to be here so the full chart can fit in. I can also reduce that column. And notice here behind
there is another page break. That's where it was cutting my chart. I'm going to push that down as well to make this part fit on the second page. And you can even expand
this further, as well, and then adjust my charts. So it takes a little bit of adjustment to get it fit the way you want. Now let's go back to normal view. We can see the other page
breaks have adjusted. Let's go to print preview, and we can see everything fits well. So that's how you can use page breaks to get full control over your printouts. So these are my Excel
printing tips for today. Who knows, your manager
might be so impressed with your neat printouts, they might just give you that promotion. Now this video is a part of my Excel Essentials for
the Real World course. If you're interested to find out more, go to xelplus.com/courses. That's it for today. In case you're new here, don't forget to subscribe
before you leave, so that you can get updates when I put out new videos. And I'll see you in the next video. (bouncy music)

Video URL: https://www.youtube.com/watch?v=-tHUljHP92c
I'm sure this is something
you've come across before. You have a big report in a
worksheet in Excel like this one. Now when you print the spreadsheet, you only get the headers
on the first page. On all the following pages, you now have to guess which column header the number belongs to. It's very inconvenient to always
flip back to the first page to find out what column
10, row 20 refers to. Fortunately, there is a
handy little feature in Excel that lets you automatically repeat rows as well as columns on
every page of your printout or your PDF document. Let me show you. This video is brought to
you by Excel Essentials for the real world. Learn Excel from scratch or fill in the gaps to
become immediately confident, discover hidden tips and tricks which get you working smarter, not harder. Click on the link below or
type in xelplus.com/courses. So here, I have some sample
profit development data for these apps. They go from January 2019
all the way to December 2020. So notice they take up a lot of space in the rows as well as the columns. If I go to print preview, this is going to print on four pages, which is not bad, but check this out, on the last page here, I have no clue what this 800 belongs to. I need to get the headers
on both the row side as well as the column side
repeated on each single page. There is an option for this. Let's go to page layout to page setup, and let's go to more options here. Select sheet, right here, we need to select the
rows to repeat on the top. So whatever we select here is going to be repeated
on every single page. We definitely want to
have our months there, and we also want to get
our columns repeated because, remember, on the last page, we don't see the columns either. Now let's go back to print
preview and see how this looks. First page is nice. Second page, we get our headers here. Third page, we get our headers and we get our columns repeated, and the last page, we know
what that 800 belongs to. Now one thing I don't like about this is this page here, where I selected the entire column, I can also see that
writing that I had on top. Now I don't want this in my report, it's enough to see profit development, so how can I get rid of
that without deleting it? I can set my print areas. I'm just going to highlight the data only and the header that I want, go to print area, and set it. Now let's go to Print Preview. This page looks great and
everything fits properly. Now one adjustment to this
is to add page numbers. So whenever you're printing something that's more than two pages, it's good practice to
add page numbers to it. Now you can do it really quickly by going to page layout view. There are different ways we can get there. A fast way is to go down here
and click on page layout. So this is the default,
it's the normal view. This is the page layout view. In this view, you can
see how your printed page is going to look and you can
easily add headers and footers. So let's just click here to activate the header and footer options. You can type directly in there, and you can use some of these elements that are directly available. So, for example, you
can put the file path, you can put the file
name, you can add a logo, you can put the current
date or the current time. Now to add the page
numbers, we need these two, let's do that in the footer, so I'm just going to scroll down here, go back to header and footer
and click on page number. It's also nice to see how
many pages are in total. So I'm going to add a space, type of, and put in number of pages. So this way I see the current page and how many pages there are in total. How do I go back to normal view? I just have to click here. Another way of getting there
is to go to the view tab here. Notice we're on page layout. I can switch to normal view also here. Now let's go back to print preview and we see one of four, two of four, and four of four. That's today's printing tip. If you liked it, give
this video a thumbs up. Thank you for watching and
do consider subscribing if you haven't done so already so that I'll see you in the next video. (bright music)

Video URL: https://www.youtube.com/watch?v=E7gQ-PgYkMc
The main purpose of these two functions is to
look up a value in a big data table and give you   a corresponding value back, that's sitting in the 
same row. Now, we're going to keep in mind is that   the value that you're looking up has to be on 
the left most column and the value that's being   returned has to be on an adjacent column. Now, left 
is the key word here, so you can't look up a value   on the right side and return a value on the left 
side. So, VLOOKUP and HLOOKUP work in the same   way, except that V is for vertical lookups and H is 
for horizontal lookups. So, if your data table has   column headers, you're going to need VLOOKUP,  and 
if your data table has row headers, you're going to   need HLOOKUP. So, let's have a look at an example 
for each. For this demo, imagine you've been given   a large set of data, and you've been told that
these are the sales agents, the quantity they   sold, the money they got, the price they charged, 
and their customers. What you like to do is to   create an overview agent report, where you select 
the agent and you find the price and the customer   from this table. As a first step, we're going to add 
our drop-down, it's what we learned in an earlier   lecture on data validation. We're going to go to 
Data, Data Validation, instead of any value, select   List and the source is our agent. So, press Control+Shift and the down arrow key.    And now, we get to select our agents from here. Now, for the agent we select, we'd like to find the price. That's where   our VLOOKUP formula comes in. The first argument 
in the VLOOKUP formula is the lookup value. What   we're we looking up. In this case, we're looking up the 
agent, which is in this cell. Next, comes the table   array, you have to remember that this is the full 
table. So, the full range where your lookup values   in and your answers are in, and it's one range. So, 
you're not supposed to select different ranges by   holding Ctrl-down and going like this, but instead, 
you select the entire range, which, in this case, is   from here to here. I'm going to press again Control+Shift+down, that's my range. And keep in mind, that the key,   the main thing is that, this Agent D, is the first 
one in this range. It has to be on the left hand   side. Even if I don't need quantity and revenue, 
I have to highlight them, because they're in my   way, they're in the way of me getting to price 
and customer. So, you always have to select the   entire range, even if there's stuff in there that 
you don't need. Next is the column index number,  and that basically means how much should it move. So, 
it assumes that the first one is a 1,2,3,4,5. So  basically, in which column is the answer that you 
want. We want the price and that makes it 1-2-3-4,   the fourth column, we have to put 4 in there. And 
this last argument is important to get right. If   you're look up value is text and not a number. If 
you leave it empty, it defaults to true, which   means that, an exact match is not necessary 
but it requires that your lookup column is   sorted in ascending order. If it's not sorted in 
ascending order and you leave this argument empty,   then you're probably going to receive an error. 
In our case, we have it sorted as ascending but   generally, I don't want to be bothered to think is 
this text, this is a number, is it sorted or not? So, I always put false in here, meaning that, these 
should be an exact match. I recommend that you   generally, put this on false as well.  Okay, so let's check this Agent D, the price here is 39, rounded 39. Now, what about the customer? I can follow the 
same logic again, my lookup value is Agent D,  the table, I'm looking this up, is here, and my 
customers should be included. Which column  is the customer? The last one, which makes it 1-2-3-4, the fifth column and false is the last argument. Customer D, for Agent D.  Customer H, 37 for Agent H.  I have a very simple dynamic using VLOOKUP.  You can also use VLOOKUP
within formulas, so they don't have to be used   on their own alone, like this. So, for example, if 
I wanted to find price here, and let's assume I   didn't have price in my source data, I actually 
need to calculate it. I could write a formula   on this, and I could say, well, divide the revenue 
by the quantity. And to tell it, which revenue to   divide by which quantity, I will use the VLOOKUP
formula and say, look up Agent H in this area. I want the revenue first, so that's number 3. 
False, as the last argument, and divide this by...    the result of the VLOOKUP formula for quantity. 
Quantities number 2  and that gives me my price. So, Agent H is 37. I think in C, VLOOKUP 
doesn't have to be used on its own, but can be used   with in other formulas.  Agent N is 42, Customer N.  And now, let me show you the HLOOKUP version of this. So, in case your original source data is 
not provided in this way, but instead in rows.   So, I'm going to copy this. We're going to move down 
here. I'm going to paste this as transpose.  Okay, so in case you have your data provided to you in this way, which is less likely but it's possible,   and you want to do the same type of look up and 
the same type of overview report, you will have   to use the HLOOKUP function. So, in the same way, 
if you have our sales agent, let me just copy this... down. You write new formulas. So, in the same manner, if you want to find 
the price of Agent N from this table, we're   going to write HLOOKUP, the lookup value 
is this, our table array is this. Don't forget   that what you're looking up here, needs to 
be the first in your table array.  Next is the row index number, instead of the column
index. So, where is price in here, 1-2-3-4, it's the   fourth row and again, do false. The same 
with customer I can actually copy this. The same with customer. I can actually copy this... paste this in here. Because this remains the same, 
that's what I'm looking up. My table is the same,   instead of 4, what number do I need?  5. Customer N.  Agent P is here right at the end.  40 is my price and Customer P is there.   Okay, if instead of price, I wanted to find quantity,  the only thing I need to change is my index number.  and quantity is number 2. So, I get 8.    That's the HLOOKUP function. To recap one major limitation of the VLOOKUP and 
the HLOOKUP formulas, is that they can't look to   the left. So, what if you wanted it the other way 
around? that instead of selecting the agent here,   we were going to select a customer and get back 
to the agent here. We wouldn't be able to use the   classical VLOOKUP formula. We're going to need to 
have to change the table, by bringing customer to   the left hand side, because the value we're looking 
up, always has to be on the left hand corner.

Video URL: https://www.youtube.com/watch?v=y1126PQ5zRU
When you use Excel it's important to use formulas
and functions correctly. In this video, I'm going
to cover common functions that you're going to need. It's great if you're in Excel beginner, or if you generally aren't comfortable using functions in Excel. I'll show you a trick that makes it easier to use any type of function. Let's get to it. (playful upbeat music) We're going to be using this
sample data set to practice on, we have name, department and salary. Now in Excel when you
want to type in a formula, you start with the equal sign. Then using your mouse or the arrow keys, you can move over to the
cell you want to select. So let's see, I just
want to add two numbers, I'm going to select this
cell, type in a plus sign then with my arrow keys
on the keyboard, go and select another cell and then when I'm done, I can press enter. Now you can use the typical
mathematical operations that you're used to. So here I could go ahead and add a minus and then
go and select this cell and when I press enter
I get the final results. And all of this is dynamic,
so if something changes here my result is going to
update automatically. I'm just going to press
Control + Z to undo this. Now, in addition to plus and minus, you can also divide and multiply. So let's say I want to remove
this part of the formula and then I just want to divide
this cell with the other cell. Type in a slash and press enter. To multiply two values together, you have to use the asterisk sign. Now you can also use brackets. So let's put these inside brackets and then let's divide the results with let's say this
number and press enter. Okay, so your typical
mathematical operations work in Excel formulas as well. But if you wanted to add
all of these numbers here, you don't want to go in and
do a plus for each number. This is where Excel
functions come into play. So there are pre-programmed functions that do the plus for you. So for example, if I want to
add all the salaries together, I can use the sum function. So just start typing sum and you can see the list of
functions popping up here. You can select it with your mouse or you can use the tab key
to select the first one. And then here you can add different cells but in this case, we don't
want to do different cells, we want the entire range. So with your mouse, you can select this you can also go with the arrow keys and hold on to shift key
and select this range. And then you close the bracket
and when you press enter, you have to sum of this entire range. So functions in Excel
are programmed machines that do a certain task. The sum function sums up different values, then we have the count function which is going to count different values. Now we're going to see this in a second but I want to show you one
thing before we get there. How do you know what a function is called? If I'm new to Excel, I'm not going to know about this function. I might be typing in add
and there is no add function you can get help from the formulas tab. Now talking about being new to Excel, if you're also new to this channel welcome and consider subscribing so you can find these tutorials easier. So under formulas tab,
you have insert function, this is something you can use
to find a specific function. So let's say I want to add values. I'm going to press enter
and I get suggestions here and when I click on them, I
can read about this function. I see the SUM function here, it adds all the numbers
in a range of cells. Now we're going to get back to
insert function in a second, I'll just press cancel on this now. Let me show you another
way of summing values and that's using AutoSum. When you click on this drop down, you have the option to select
the type of function you want. So in this case, we want to sum so I'm just going to select this and it does the whole work for me. All I have to do is check whether the range it
selected is what I want, in this case it is, so all
I need to do is press enter. Now, there is also a
shortcut key for this. You might have caught it
when I clicked on AutoSum it's Alt and the equal sign. So when you're here, just
type in Alt and equal, press enter and you have your sum. Now, this also works horizontally. So if I have a number here, I can go and do Alt + Equals and it
picks up the correct range, and all I have to do is press enter. Because summing values or
getting to average of values is such a common task. You don't just find it
under the formulas tab here, you have AutoSum
available in the home tab. So directly from here on the
editing side, you have AutoSum and you can select the
function that you need. So now let's take a look at getting the average of our values. Now, on the side here, I'm
just going to type in sum so we know what each function represents. Let's get the average here
and now again I can start by either typing in the function directly and then selecting it with
tab, highlighting my range, closing the bracket and pressing enter. Or I just go to the AutoSum options here and select AVERAGE, but
now take a look at this, it picked up this value as
well so I need to correct this. So instead of C11, I want
C10, I'm going to press enter. Next let's count numbers, so
I'm going to type in count, on the side here I get some information about what this function does. So it counts the number
of cells in a range that contain numbers. That's what I want so I'm
going to open the bracket or press tab and it
opens the bracket for you then select this range,
close bracket, press enter and that's the count of
cells that have numbers. So if one salary information is missing my count is going to be reduced. So I'm going to press
Control + Z to go back, now just to know what we're
doing this was count salary. Can I use this to count names? Well, let's try it. I'm going to use the COUNT function, this time I'm going to press tab. Let's select this, close bracket
press enter and I get zero. So the COUNT function only counts numbers, it doesn't count texts. If you want to count texts you need to use a different function and that's the COUNTa function. This function counts the
number of cells in a range that are not empty. So it doesn't care whether
it's texts or it's a number, it counts both of them. Now I'm going to press
enter and I get eight. So if one of these happens to be a number I'm going to put a zero,
it's going to count it, it only doesn't count it if it's empty, just press Control + Z
a few times to go back. Another two super useful functions are the MIN and the MAX functions. So let's say I want to get the
minimum salary in the range. I need the MIN function, select the range, close bracket, press enter and it's going to give me the
smallest number in that range. In a similar way, we can
get the maximum salary from this range, close
bracket, press enter and that's our number. Now, what if you wanted to
do something more complex like getting the average salary of people who are in the sales department, how would you go about this? Well, we can start off
by typing in average to see the different options we have. And then we can check this description to see if it does what we want it to do. So here we have AVERAGEIF which looks good because it takes into
account a given condition. And we have something called AVERAGEIFS which is a given set of conditions. To be keen include more than
one condition in this formula. Now let's say I'm interested in this because I might add in other columns here and I might want to have
different conditions. So I'm going to double click
this, or you can press tab and then let's go to the formula
bar and see what we need. It says we need average
range criteria range one, criteria one. Now all of this doesn't really mean much to me if I'm new to Excel. So here's what you can do. Click on this button, that's
the insert function button and it takes you to this dialog box which gives you a bit more information. So for average range here
this is the actual cells to be used to find the average. So this means these are my number cells that's the range I need to select. I can get a preview of
what I selected right here then what this criteria range one mean, this is the range of
cells you want evaluated for the particular condition. That's my range. Next criteria one. This is the condition or criteria in the form of a number
expression or texts that defines which cells will
be used to find the average. So that's sales. Now I can do a cell reference here if I had sales sitting
in a separate cell here or I can just directly type it in. And notice something
the moment I click away, it's going to put my
texts in quotation marks, because if you're using
texts inside a formula, you need to put quotation marks. Now take a look at this, I already see the answer here. So I have a good idea that my
function is working properly and then all I have to do is press on okay and the function is inserted here. Okay, so this is a great feature to use when you're new to Excel and don't really understand
what Excel wants from you for each of these different requirements. So that ends our introduction to Excel formulas and functions. I have many videos on the channel covering
different functions in Excel. So make sure you check them out if you ever need to write
more complex functions to get your analysis done. If you like this video don't
forget to hit that thumbs up and do consider subscribing
if you aren't a subscriber, it would be great to have you
as a part of our community. Thank you for watching and
I'll see you in the next video. (upbeat music)

Video URL: https://www.youtube.com/watch?v=iNXqJ-n92U4
good excel knowledge is a requirement for many jobs these days but we all have different understanding of what good or advanced knowledge really is so a lot of employers will ask specific excel questions during interviews today's video will help you prepare for that to give you some context some time ago i asked the community to share which excel questions they got when applying for jobs also if they were the interviewer which questions they asked now to get the most out of it i summarized the responses based on different positions that people were applying for the result was really surprising so i expected the obvious vlookup or what does the dollar sign mean in style references those type of questions but that wasn't the most common question [Music] i'm going to share the type of questions with you in a second so you know what topics to concentrate on but i obviously can't answer all those questions here because that's going to be a beginner advanced and super advanced course all in one so instead i've added links to the description of this video that cover most of the topics that i talk about here also if you prefer to learn in a more structured way with a lot more hands-on practice taking a course may be a good preparation for you so here's the surprising part regardless of position whether it was data analyst financial analyst project manager or systems accountant the two most common topics that were specifically asked and checked were pivot tables and how to remove duplicates now this was then followed up with the lookup function specifically vlookup and then some effects counterfest average if asked questions and nested if functions there was an emphasis on the nested part so not just if functions but nested if functions okay so these topics are topics that you should definitely master now how do they check so how does the interviewer check your knowledge well usually it was a combination of an indirect check and a direct check so indirect is for example this question what's your experience using microsoft excel to manipulate and analyze data so this was a question that someone was asked who was applying for a logistics planner job at amazon now what does the interviewer want to hear they want to hear pivot tables they want to hear how you clean data how you use more complex functions and tools and features to create your reports you can't just do name dropping though you should actually have created these reports and know how to use the features that you talk about now here's the point of view from someone who is the interviewer this is what they said i have one question in the interview that just asks how people use excel in their current or previous position i want to hear about their use of excel in reporting and if they mention pivot tables then it will go a long way for them just remember though that mentioning these keywords is not enough because a test might follow so this person went on to say on top of that then i made an excel test for my candidates that they complete after the interview portion the test consists of four simple exercises sorting pivot tables creating a basic chart from a table of data and entering some simple calculations on the side of some data and then filtering and totaling to get the result i can pretty easily tell how people attempted to answer the excel questions and their level of skill with excel and i give them 30 minutes to complete now here's the interesting part of what was said most people say they use excel extensively in their jobs in the interview but yet when it comes time to do the exercises they bomb that portion so make sure that you practice before going to the interview okay so that was a general look across all positions now let's cover specific positions and what else you might need to know i'll start with business analysts this also includes mis analysts so management information system analyst in addition to the questions that we already covered when you interview for a business analyst position you can expect questions about charts so creating charts from scratch conditional formatting your experience creating dashboards and in terms of functions the aggregate and subtotal functions were mentioned here as well next position let's talk about data analyst if you're interviewing to become a data analyst you should familiarize yourself with these tools as well excel vba and macros power query filters so different ways of filtering your data index and match and sum product if you can also talk about how you use x lookup or other new office 365 functions you're going to get bonus points now as an example one person said who was applying for a data analyst position that required a minimum i think of three years experience they said that they had to know the difference between activex and regular buttons so i assume what they mean with regular buttons is the form control buttons they had to know or explain the use case for arrays and also explain the sum product function so now let's take a look at the financial analyst position so in addition to the five topics that i mentioned at the beginning which were pivot tables removing duplicates vlookup summifs and nested if functions as a financial analyst you should expect questions also about power query simple vba and macros and date functions okay so now let's take a look at more senior positions so positions like team lead finance manager or hr manager here i was actually surprised at the excel knowledge that was expected so in addition to pivot tables and the four other general topics that we took a look at there was emphasis on power bi power query and surprisingly in a lot of cases advanced vba knowledge was required so just to give you an idea a team lead position that required over 10 years of experience needed to answer difference between match index and vlookup questions and different if conditions how to debug errors on a vba code and loop function in vba another one applying for finance manager job was asked questions about power bi and power query and another person was asked what are the three drawbacks of vlookup and how would you look up a combination of two fields using vlookup or indexamatch now i have many videos on index that match on the channel i've added the link together with other relevant videos that can help you with your interview questions also to the description of this video if you'd like to go from beginner to advanced on a specific topic for example vba or power query you can also check out my complete courses we cover many examples and you have lifetime access so you can always come back to them whenever you need okay so that wraps up the most common excel interview questions that you might get thank you so much to the community for sharing your experience with me so that i can share it with you basically by practicing regularly and going through these questions you'll be prepared for your next interview i hope you found this video useful please do give it a thumbs up if you did and consider subscribing if you haven't already done so many thanks for watching and i'm gonna see you in the next video [Music]

Video URL: https://www.youtube.com/watch?v=pi10BjP9qlk
So, Microsoft just launched Office 2021. This is 
a huge jump from Office 2019, especially for   us Excel users. So, if you're already 
on 2016 or 2019, and for whatever reason,   your IT doesn't want to upgrade to 365, 
just hope that they upgrade to Office 2021.   And aside from Excel, at the end of this video, 
I'm going to cover some of the key highlights   coming in PowerPoint, Word, and Outlook. 
So, let's just quickly take a look at all   you're getting depending on whether 
you're upgrading from 2016 or 2019. Excel 2019 came with six new functions. 
So, if you're jumping from 2016 to 2021,   you want to check these ones out too. In Excel 
2021, we have nine new functions that are going   to completely change the way you work with Excel. 
I'm quickly going to explain each of these just to   give you an idea of how they work. I have detailed 
videos about most of these functions separately.   You're going to be spending less time figuring out 
how to combine formulas to get your tasks done.   A formula that looks like this in Excel 2019   is now going to look like this. If you come 
across one of these functions and you think,   'Yes, I can use this,' please comment below 
because I'd love to know. Now, let's get to it. The first amazing function is the 'UNIQUE' 
function. This allows you to get a distinct   list of values, basically get a unique list. 
Let's say here we want to get a unique list for   our departments. We just have to type 'UNIQUE' and 
all we need is to select our range, close bracket,   press enter, and we have our unique list. The old 
way of solving this was quite complex. This is   how it looked. We had to write our formula, when 
we had to drag it down. Just like with standard   Excel formulas, we don't have to drag our formula 
down anymore if we use these new functions, also   referred to as dynamic array functions because 
these functions spill. You have to be careful   though, because if something is blocking them. 
So, if I had some text here, I'm going to get the   '#SPILL' error. So, you just have to remove 
that blockage and let your formula spill. The next amazing function is the 'FILTER' 
function. This is one of my favorites because   it returns multiple match results. So, not 
just the first match but all the matches. So,   here we have the list of names and departments. 
You want to return a list of staff who work in   the selected department. So, we want to see the 
names of the people who work in finance. We want   to grab it from here. All we have to do is start 
with 'FILTER'. The array is what we want to get   back. So, ultimately we just want to see the 
names. Next is what do we want to include? This   is similar to the 'IF' logical test. Only those 
lines or only those names should be included   where department from this range equals this cell. 
Last optional argument is what do we want to see   if there are no matches? Let's just go with 
empty. Close bracket, press enter, and we have   the people who work in finance. If I switch this 
to procurement, these are the list of people who   work in procurement, sales, and so on. So, notice 
my range automatically shrinks and expands because   it's spilling. The old way of doing this was like 
this. You had this crazy function here plus you   had to get help from intermediate calculations. 
It's all over now with the 'FILTER' function. Next up is the 'SORT' function. This function 
allows you to dynamically sort your ranges. So,   let's say I want to get a sorted list of division, 
I just have to start off with SORT. All I need is   my range; this can also be a table range. Then 
just close bracket, press enter, and you have   your sorted list. If you wanted it the other 
way around, because default is ascending, so   if I wanted this in descending order, I just have 
to take advantage of these optional arguments. So,   the first one is which column I want to sort. I 
just have one column, next one is the sort order,   I need -1 for descending. Now I get my list 
the other way round. Now, if you wanted to   get a unique sorted list, you just have to 
put this part inside the UNIQUE function   and then sort this. The great thing is that if 
this happens to be a table, so I'm just going   to press Ctrl+T convert it to a table and 
let's say I want to add something to this,   it's automatically going to get added to my 
list, and it's going to be sorted automatically.   Now, there is so much more you can do with the 
SORT function and with all the other functions   that I'm covering in this video. So, if you want 
to master these functions, check out my complete   course on Excelplus.com, link is going to be in 
the description. Next up the SORT BY function,   this one allows us to get a list sorted by 
another range. So, for example, let's say   we just want to get the list of staff names 
but sorted based on the salary that they get.   All I need to do is start with SORT BY. 
The array is what I want to get back,   I just want to get back a list of names by which 
range am I sorting this by? By this range. How   am I sorting this? I want highest to lowest, so 
I'm going to go with descending close bracket,   press enter and I have my list sorted by another 
column. Next up is the XLOOKUP function, this is   another of my favorites. So, if you've been using 
VLOOKUP and you've struggled with VLOOKUP before,   forget about it, XLOOKUP is the better 
and easier version. It can look to the   left and to the right so you don't have to worry 
about your column order. So here, for example,   we have department name and salary, we have the 
name here. We want to grab their salary and then   we want to grab their department. So, notice 
the position of department is behind name. Now,   here you probably know how to write the VLOOKUP 
for this, the XLOOKUP version is simpler to write   even though it looks like it has a lot of things 
going on, but don't worry, because a lot of them   is optional. We need the lookup value which is 
right here, where are we looking this up? We don't   have to worry about selecting entire ranges, just 
where this name is sitting in, and it's sitting   in here. Then what do we want to return? We want 
to return the salary. We don't need to worry about   an exact match, because that's the default. We're 
going to press enter, and we have our salary.   If we want to account for missing data, so if 
we don't want to get an error when we type a   name that's not there, we can take advantage 
of these optional arguments. So, if not found,   I can put check name. Now, here comes the great 
part. Let's find the department for the person,   the lookup value is right here, the lookup array 
is the same as before. This time my return array   is behind the name, that's no problem. Just select 
it, close bracket, press enter, and we have our   department. So, the old way of doing this, 
especially if you wanted to go backwards, was   to use INDEX and MATCH, which is a function that a 
lot of people struggle with. And then we also had   to put it inside the IFERROR function to account 
for names that don't exist in our data set.   Forget about all this, just use X LOOKUP. Next up 
is the X MATCH function. This returns the relative   position of an item in a list. So, if you used to 
use the MATCH function, you can now use X MATCH.   The difference is that it has one less argument 
to worry about. So, here I have a list of staff   names and the sport that they registered for. Now, 
I just want to know whether they are in this list   or not. I can quickly do a check using X MATCH. My 
lookup value is this name here, my lookup array is   right here. Now, I don't have to worry about the 
zero that you used to use in the MATCH function.   You just close the bracket, because it's 
checking by default for an exact match. When   you press enter, you get the position of this 
name in this range. Now I can pull this down,   but I have to fix my referencing here, or what 
I can do because we can now use field ranges. I   can select this entire list, press enter, and 
I get my complete list with a single click.   So, anywhere I have an a, these people aren't 
on this list. Now I can't filter by this,   get the list, call them, get them to register 
for a sport. If you don't want to see an a here,   you can also put this function inside an IF and 
return an alternate result. Next cool function   is the SEQUENCE function. This function gives you 
a list of numbers in the sequence and order that   you want. So, just for quick demonstration, if 
we start off with SEQUENCE and we say we want 10   10 rows, close bracket, press enter, we get a list 
of numbers from 1 to 10. Now, what if we wanted to   get a list of dates that go from the start dates 
to this end date? Well, we can use SEQUENCE for   that as well. The list of dates we have depends on 
our range. So, I'm just going to go with end date   minus start date. That should give me the number 
of rows of dates that I need. Number of columns,   I just need a single column. So, I can put a 1 
or I can skip this. Start is my start date here   and step by default is one, so I can just close 
the bracket, press enter, and I get a list of   dates. They're just shown as numbers, so I'm 
going to select my column, update the formatting   to a short date. Now I go all the way down to the 
30th, so I just have to add a 1 to this to get to   the 30th. If you don't want it day by day but 
you want it for every 7 days, you can use 7 here   as your step. Next up is the RAND ARRAY function. 
This function gives a bunch of random values   all in one go. So, let's say here I want to get 
random grades for these people for a different   subject. I'm going to start off with RAND ARRAY. 
I want random numbers that go to four rows and   six columns here. Now, if I just close bracket 
and press enter, I'm going to get random numbers   between 0 and 1. And every time I refresh this, 
these numbers are going to change. But I have more   arguments here that I can use. If I wanted minimum 
to be 1 and the maximum random number to be five,   I can just input these directly here. When I press 
enter, I get random numbers between 1 to 5. Now,   these are decimal numbers, but I 
have the option to return integer   values. Just go with "TRUE" as the last argument, 
and you get random numbers between 1 and 5. Last on the list is the "LET" function. This 
function allows you to define names for parts of   your formula. So, this is great for your longer 
formulas, especially those formulas that have   repetitions or they have calculations that are 
repeated. So, for example, here I'm calculating   the threshold only if it's above or below 10. This 
part of the formula is repeated. With the "LET"   function, I can write it like this. So, first, 
I define a name for that part that's repeated   and then I use that name over and over again in 
my formula. And this can help with readability,   but it also helps with performance 
because the formula is only calculated   once. It's saved in memory and then 
the result is reused. I have a more   detailed video about the "LET" function, 
so check that out if you're interested. In terms of features, we get all these 
great tools to improve collaboration.   One of those tools is called "Sheet View." So, 
that's in the "View" tab. In the old days, if you   would filter this list and someone else is in this 
file, they're gonna see that filtered version. So,   you're gonna influence their work when you start 
filtering and sorting, but not if you use "Sheet   View." So, let's say I want to filter for the 
finance department. I can go in "Sheet View" here,   and now whatever I do here doesn't impact the 
other person's experience. I could even give this   view a name, call it the "Finance Department." 
Whenever I'm done, I can exit this view. Another great feature is the "Auto Save." 
So, even if your Excel happens to crash,   you don't have to worry about when you saved 
it last. "Auto Save" is going to save you.   You can of course turn it off if you don't want 
to have it on because you're just testing stuff   and you want to close without saving. It's 
something I actually do more than I should. Another great feature that was added is when it 
comes to adding your own custom colors. Before   we were restricted to the RGB code. Now we can 
paste in a hex code. There were also more images   and illustrations and icons that were added 
to the list that we used to have. So, if I go   to "Icons" here, we have images, we have 
cut out people, we get these funny stickers,   illustrations and if you are in 
PowerPoint, you also get videos. Now, in PowerPoint, we also got a lot of 
improvements in recording. For instance,   now you have a countdown when you press record. 
You don't have to right-mouse click to access   the annotation tools. The ink colors are now 
easily accessible at the lower side here,   and you can read the slide notes as you 
narrate the slideshow and no one is going   to see this. You can also show yourself on the 
screen by turning your camera on or off. Also,   if you're upgrading from 2016 to 2021, you're 
gonna get this amazing 'Morph' feature that's   gonna take your presentations to the next 
level. Word also got improvements in its   'Immersive Reader' so this was in Word 2019, but 
now when you go to the 'View' tab and you select   'Immersive Reader,' you can decide how many lines 
you want to focus on. For Outlook, in my opinion,   the best feature is 'Instant Search.' It's so 
much easier to find stuff now, and if you want   to improve your search results even more, check 
out this separate video I have on this topic. So,   that's what's new in Office 2021. Now, there are 
more features that I didn't get to discuss here;   I just picked the ones that I think are going to 
be most relevant for you. So, there's a link below   this video as well with more information, but my 
recommendation and I didn't get paid to say this,   but my recommendation is to upgrade to Office 365 
because that way you're going to be up-to-date   with new functions, and you don't have to wait 
so long to get anything new that comes out.   That's it for today, I hope you enjoyed 
this video. Thank you for watching,   do subscribe if you aren't subscribed, 
and I'm gonna see you in the next video.

Video URL: https://www.youtube.com/watch?v=3Wkagedga1U
I thought it would be
interesting to do a video, but the items I have on my Excel QAT,
which stands for quick access toolbar, the Excel ribbon is a jungle. And the purpose of the QAT bar is
to help you be more productive, basically to find stuff easier. So it helps to add the things that you
use often to this one place you have get, see about your choices there. You're probably gonna be surprised
about the items I have on mine. I never shared this in my videos
because some of my choices might be shocking to advance the Excel users. And it's gonna take all the attention
away from the actual video in case you're using the QAT bar share with
us in the comments, pillow, what your first item on there is. Or maybe you have a favorite item that
you've added. Let us know what that is. Okay. So now let's jump in. I'm
gonna show you what I have on mine. Okay. So this is my quick access toolbar. Now yours might look
different to mine, obviously, not just in terms of the
items that I have here, but also in terms of the way it looks. So this is the new look of office 365. Notice there's more color
in the icons on the toolbar. Now another option that
you have, which is new, is the ability to show the command label. So if you don't have too many items here, you might wanna turn this on and it
as your default, but in this case, I preferred the condensed version.
So I'm gonna hide the labels. One other thing you can do, which you couldn't do before is to hide
your quick access toolbar from view. If you wanna bring it
back, just write mask, click somewhere on the ribbon and
show your quick access toolbar. You still have the ability to
show, show this above the ribbon. Default has now become below the ribbon. Now the purpose of the QAT bar is to
make your experience easier when you use Excel, because you've probably
noticed Excel has a lot of tabs here. You have to remember where
to get something from. If you don't know the shortcut key, you
need to know where it is on the ribbon. So if there are features that you use, often you can add them
to your quick as toolbar. So let's say you use grid lines a lot.
You turn them off and turn them back on. You can just write mask, click on this feature and add
it to your quick access toolbar. This way you can be on any tab
whenever you wanna turn them off, just remove the check mark
here, or bring them back on. If you don't want it on your
QAT toolbar, write mouse, click and remove it from the ribbon. Now, if you're also a fan
of using shortcut keys, you can access the items you
add here with shortcut keys. Sometimes these shortcut keys
might be easier than the actual shortcut key for that item. At least it might be easier for you to
remember to access the items with the shortcut keys. Just click on the alt key.
Then you're gonna see this 1, 2, 3, 4. If I click on three here, it's gonna
activate that email attachment option. And it's gonna attach my Excel workbook
that I was in to a message from outlook. Now I just have to add the other
details and click on send. Okay? So now that you have an
idea, what the QAT bar is, let me take you through my own settings. The first item I have here is
print preview full screen. Now, this is different to the option that
you see here, which is print, preview, and print. They look identical, but they offer two different experiences. This is the newer version of print,
preview, and print. When you click it, you come to this view, you can adjust your settings with
these dropdowns here and then print. I, I was never a fan of this newer look. I always liked the old legacy way
and thank God I can still access it only through my quick access toolbar.
How does that look? It looks like this. I just find this way so
much easier to manage. I can adjust my settings
here. I click on page setup, go through all the different options. Once I'm happy I can print
or close the print preview. So if you're wondering how to
add this to your QAT toolbar, I'll show it to you in a
second. Next I have spelling. So there is an easy shortcut
key for spelling it's F seven, but the reason I have it
here is for me to remember, to check spelling before I print this, or before I send this as
an email to someone else, its purpose here is more, as a reminder, both these spelling and the email
options are items you can find directly and disrupt on here. You have email
and here you, you have spellings. I'm just gonna remove this print,
preview and print. So to remove items, you can also just write mask, click on an item and remove it from
your quick hack access toolbar. Next, I have this separator and I'll
show you how to add that as well. And then I have some items from
power query and data model. So this one for example, is
to get data from table range. So I'm in this table here. Well,
it's not an official Excel table, but if I wanna send this to power
query, just click on this. In this case, it will convert it to a table. And then it's gonna open up power
query and send it to power queries. I'm just gonna close this. Let's not save. And this actually press
controls said to go back. Next is another power query feature
is to get data from different places. So from workbook, from text and so
on, then I have the data model here. So it's gonna bring up the power pivot
window and add a measure for power pivot. So it's basically an
option that you see here, but I have these because I've
been using them a lot recently. So I've added them to my QAT bar. Then I have a separator and then
comes these pasting options. I have paste values and source
formatting and paste values only. So here, I'm gonna copy this, go to the site here and pace
values and source formats. So this is something I use often. If I just want the values and
I don't want the formatting, I'm gonna copy. And then pace values only. Now you have shortcut
keys for pasting values, but sometimes it's just so much easier
to use the quick access toolbar. Next one is the dollar sign you might
be wondering why would anyone have a dollar sign in their QAT bar? Let me explain. So whenever you reference another cell
and you wanna fix the referencing, you use the F4 key. This is
gonna put the dollar signs there. Then you can toggle this
with F4 key. Now, sometimes I have these very
slim keyboards and to be able to access F4 key, I have to hold down the function
key and then click on F4, which means I can't sip my
coffee while I'm doing this. The dollar sign lets me do that. I can hold my cup and I just click on
this and it's gonna add the dollar sign to my function. Now, the problem though, is that this does not
act like the F4 keys. So I have to place the
dollar sign on both sides. So what I wish Excel would have, especially now that everyone is using
these tiny keyboards and touch screens is to you have a four feature as an
option that we can add either on the ribbon or to our quick access toolbar, it's gonna improve the experience
depending on what type of device we use. Another one that I use is this one to
collapse the ribbon or show the ribbon. Again, this one has a shortcut
key available it's control + F1, but on my slim keyboards, I
have to use control function. F1 it's too much work. I just
prefer to click on this next one. I have the camera. So this is
something I don't use often. It's only when I am creating
special types of dashboards. The camera tool takes a snapshot of
your cells or whatever you have on this cells. And it creates a linked
picture. So this one now is a picture, but it's not a static
picture. Check this out. I'm gonna change this value to one 20
and it's gonna pull through to this picture. But because this
is a separate object, I can resize it and position this
wherever I want on my report, the camera tool is also something
you can't find on the ribbon to add. So I'm gonna show it to you in a
second, where you can find this. The last option is the calculator. So you might be wondering why do you
have a calculator when Excel is a calculator, usually I'm working in a file. And then someone comes by and is like, what was cost that we had of this and
that what's the budget we're planning for this and that. I start calculating
in the open file that I'm working on. And then I forget to remove that
number. It's happened to me a lot. So now, instead of working in that
file, I click on the calculator. If the calculator isn't enough
and I prefer to work in a cell, I press control N and I
open a new workbook. Okay? So these are the current
items I have on my toolbar. I would be very interested to
see how yours compares to mine, which of these do you also have on your, a toolbar and which are some really cool
ones that you can share with us. Now, I promise that I'm gonna show
you where this one comes from. And also some of the other ones, because
they're not easy to find click on this, drop down here and go to more commands. This is gonna give you
access to everything. These are the popular commands by default, but you can change your settings here
and go for commands, not in the ribbon. So that's where you're gonna find that
camera tool. And also this option here, you can also find these
under all commands. So this
is gonna include everything, but under commands, not in the ribbon,
the next challenge is gonna be, where is it? Everything is
sort of it alphabetically. So I have English language. So everything is sorted based on
the name of the item in English, but sometimes the name isn't
as clear as you might think. So in the case of printing, it is. So if I scroll down to P I'm gonna
see print, preview, full screen. So that's the option that I have here
to add it to your quick access toolbar. You just select it and click on add. Now I'm not gonna add it
because I already have it here. Once you have it on this side, you can organize things by
pushing them up or down. Now, finding this was easy, but finding
the pace options is not so easy. You might that they're
gonna be under P right, paste. but all you're gonna find here is
pace. This hyperlink and pace link, those options are under V for values. You have values, pace, values,
values, and source formatting. You're also gonna find some of
these options under K keeps source, column widths, keep source
formatting and so on, right? So it's not always easy to find what you
need. You just have to take your time, discover where it is. Add it. If you
find yourself not using it anymore, remove it. Another way of adding things is to
source everything based on the tabs. If I select the home tab here, I'm gonna get the different
icons available in the home tab. Now some of these tabs are based
on context. So for example, if I have a pivot table, then I
can see the pivot table tools. You'll find all of them on the
bottom here. If you have a table, you're gonna see table tools, find what you need and add it the
way you can separate your items is, is using this separator on top,
wherever you wanna add that, just click on this, add it to this
side and move it around as you need. If you don't need it, get rid of it. Another great advantage of using
the Q a T bar is that you can also add macros. So if you have a bunch of VBA
macros program that you use often, and you've made them globally accessible, you'll find them here and you can add
them to your Q a T bar, as you can see, it's really easy to customize
your selection and the Q a T bar, you can add macros features that are
difficult to get to under ribbon, or even use it as a type of checklist
that you need to remember to perform certain actions on your data set.
So I hope you enjoyed this video. If you did hit that thumbs up
and thank you for being here. Thank you for watching. And I'm
gonna see you in the next video.

Video URL: https://www.youtube.com/watch?v=uxdp5Z2I0mg
Today, you're going to learn how to combine 
data from multiple cells. So, for example,   you have values in multiple cells and you 
want to combine them in a meaningful way.   I'm also going to show you a bonus trick 
where you have text and a number combined,   but Excel doesn't see the text 
part, completely ignores it.   This way, you can use this text and number 
combination in your formulas. You can use   it in the SUM function and calculate totals. 
Sounds interesting, right? Let's take a look. Okay, so here we have "Name" and "Amount" 
and we want to combine these in a dynamic way,   so that if our cell values change, the result 
is automatically going to be updated. We're   going to start off with an equal sign, go, 
and select A2. Now, use an ampersand to   combine this value with the value in another 
cell. You're not restricted to two cells,   you can combine this with any number of cells 
that you want. You would just continue and put   in the next ampersand, select your cell until 
you're done with your selection. In this case,   we just want to combine these two values, 
and I get "Bill" and "1000" stuck together.   This doesn't look so good. It's going to be 
better if we add a space in between. To do that,   edit your formula right after the first 
ampersand, put in the quotation marks   in here. You can add any type of text that you 
want. If you just want a space, put in a space,   put in the quotation mark again. But check this 
out, if you try to leave the formula right now,   you're going to get this error. There's something 
wrong with it, it's not complete yet. The problem   is that you cannot combine text directly with a 
cell reference. What you need in between is this   ampersand. Don't forget to add that in there. Once 
you do that, you get "Bill(space)1000." Now, if you want   to add something else in there, if you want to 
add a dash, you can do that or space, dash, space,   and our results updates automatically. Now we 
can easily just double click and send this down   and get these in a dynamic way. Because they're 
formulas, if I update any of these names here,   the result is automatically going to be updated. 
So I'm just going to press Ctrl + Z to go back. Now, in case you want your 
numbers to be formatted properly,   so let's say, in this case, I wanted to 
show that this is a thousand dollars,   I can do that as well. I'm just going to remove that 
dash so we're just going to go back to the space   and just before the number, I'm going to 
type in the dollar sign, press Enter,   and now we have "Bill $1,000" . If 
I send this down, "Jessica $1,000",   and so on. If you want to have more space, 
you can add that inside the quotation marks. Now, what if you wanted your numbers to 
have proper formatting so you want to have   the thousands separator in there? This is where 
things can get tricky. An easy way around this   is to use the TEXT function. So, I'm just going 
to go back to what we had before and right here,   where I'm referencing the cell that has the 
number, I'm going to use the TEXT function.   This function can convert a value and format 
it as a text. The great thing is you can add   any type of number formatting that 
you want. So let's say I want to add   a thousand separator. What I need to do is, 
in quotation marks, define that format. Now,   it can be difficult to remember what these formats 
are, or/and if you're new to Excel, you're probably   not going to know what the format is. So, let 
me show you an easy way to format this. I'm just   going to put a double quotation mark here, just 
so I can close off this formula and leave that,   and I just want to show you what happens when I 
press Enter. I'm going to get nothing because I'm   making this number disappear. I'm formatting it 
as nothing, I'm kind of like hiding that number.   I don't want to do that, I want to show it with 
a thousand separator. But, how do I know what   to type in here? Here's what you can do: just 
right-mouse-click on any cell and go to 'Format   Cells'. You can also use the shortcut key, Ctrl + 1. Go all the way down to 'Custom' and you're   going to see how a thousand separator looks. In 
my case, because I have US regional settings,   my thousand separator is a comma and my decimal 
is a dot, so just pick the number formatting that   you want. I want this one, I'm going to press 
Ctrl + C and copy it, click on OK, go here, and   inside quotation marks, paste this in. Now, this 
hash and zeros are just placeholders for numbers,   but you don't really have to understand what 
these are, you just have to recognize that this   is a thousandth separator. Now, when I press 
Enter, I get my numbers formatted properly. Before we wrap up, I have a bonus tip for you. 
Let's say, in this case, we have two columns: one   is for 'Amount Base', another one is for 'Bonus'. 
On the bottom, I want to show the total value. So   here, I've used the 'SUM' function to calculate 
the total, then I've used 'TEXT' function to   add the thousand separator to this, and then I've 
combined it with the word 'Base'. In the end,   I get my total 'Base' of 7200. I've used a 
similar function here, except that I want this   to be called 'Bonus'. Now, on this side here, 
I just want to sum these two values. What's   the problem here though? Can I just do this? No, 
right? Because I'm just summing text. These cells   are no longer seen as numbers, they are seen as 
texts. Here's how you can disguise these numbers   to look exactly like this, but be recognized by 
Excel as numbers. So, you don't need to do all of   this. All you need is just to get the sum of the 
cells above, pull this across, and now check this   out. I want this to have the word 'Base' in it, 
so I'm going to right-mouse-click 'Format Cells',   go to 'Custom', keep that thousand separator 
formatting, and just go to the side here in   quotation marks, type in whatever text you want. 
In this case, I'm going to put in 'Base:', add   a space, and quotation mark again, so we can see 
how it looks like here. Click on OK and we see the   text 'Base' in there, but this is being recognized 
as a number. It's showing up here. So, we can do   the same thing for this one. You're going to 
press Ctrl + 1, go all the way to 'Custom'.   If I wanted to reuse that formatting, I 
can. You're going to find it down here.   Let's just adjust this and call this 'Bonus'. 
So here, we're disguising our numbers to look   like text but in fact, they are numbers. So those 
are the different ways you can combine text and   numbers together. Let me know what you think. I 
hope you enjoyed this video. If you did, hit that   thumbs up. Subscribe if you haven't subscribed 
yet, and I'm going to see you in the next video.

Video URL: https://www.youtube.com/watch?v=cnCvn6CmP2k
If you're here, you probably use Excel daily, 
and you've developed your go-to routines. Now,   after a while, it almost becomes natural, and it's 
hard to imagine that you're not really doing it   the way you should. Let's end that. I collected 
a few things that many of us are consistently   doing wrong. I'm also going to show you then, 
the better way of doing it. Let's take a look.   One common mistake is to create external links to 
files without intending to do that. So, if you go   to the Data tab here, and you see that Edit Links 
is not grayed out and you click on it and you have   a lot of links there that you don't need, you need 
to clean those up. This was a common thing at my   previous company because we copied over many 
different worksheets from different files to   new files, and while we did that, we brought over 
links that were still referencing the old files,   and we never intended to do that. So, how can you 
copy sheets the correct way? Well, let's take a   look at this example. We have a start file here, 
and I have a calculation sheet and a master sheet.   Now, in this calculation sheet, we have formulas 
that reference that master sheet. Now, I want   to copy the calculation sheet over to another 
file, that's called "LocalFile". In this file,   I also have a master sheet and when I copy 
over my calculations, I want them to reference   this master sheet. Well, let's see how we can 
do that. I'm going to go back to the start file,   right-mouse-click on the tab here, Move or 
Copy, place a check mark to create a copy,   and then select LocalFile. So now, this 
is going to bring over the calculation tab   to the local file. But the problem is the 
formulas are still referencing the old file.   Notice it has the name of the old file in here. 
If that's something that you intended, it's fine.   In this case, I don't want it to do that. I want 
it to reference the master tab that I have here,   so of course, I could go in and update this 
manually. But assume you have a lot of different   formulas here, and you don't want to take the 
risk of doing this manually. What you can do is   go and edit the links. You're going to change 
source, and then select the local file as your   source. So the file that you copied it to, we're 
going to select that as the source. So basically,   it's going to be referencing itself. And once 
you do that, Edit Links is grayed out again   because you have no more formulas referencing 
an external file. If you wanted to remove the   formulas completely, you can break the links. 
You can also do that right here under Edit Links.   Next one is something you shouldn't do, 
if you want to enjoy working in Excel.   So, it's more of a productivity tip. Let's say 
I'm writing out instructions for this page here,   and I notice it's getting too long. I want 
to break it up into multiple cells. So,   I'm going to go and say, okay, right after the 
two here, I want to break that up. That's this   two right here, so I'm going to grab that, cut 
it out, then go to the next cell, paste it in,   then take a look at where I need to do the next 
cut. After this, cut it out, paste it in. And now, let's say I forget to write something 
here, so this cell gets longer,   and I have to repeat that process. No, no. 
Don't do this. What you want to do instead   is this. Decide where you want that cutoff 
point to be; for me, it's right after this   cell. Just highlight the area until where 
you think your text is going to flow to,   and then go to Home. All the way on the side, 
select Fill, and Justify. That's what you need.   Everything automatically adjusts. If you ever 
want to put all of this back, just make your   selection wide enough until where you think it 
fits in the first line, go back to Fill, Justify,   and everything is back. Next one is merging cells 
instead of centering them in the wrong places.   There is a place to use merge cells, and that's 
usually on top of your report. But you shouldn't   be merging cells in the middle of your datasets. 
So, for example, here I have 2019, 20, and 2021;   these cells are merged. If I want to write a 
function here that sums up these values, I can't   just make a selection like this because the merge 
cells cause my range to automatically expand. What   I could do is to go in and manually update this 
to make sure that this is restricted, or another   way would be to manually make my selections. 
Basically, just highlight this, hold down Control,   highlight, highlight, and so on. Now, if you have 
a lot of rows and a lot of merch cells in between,   this is going to be a headache, so a better way of 
handling this is to use center across selection,   so I'm just going to unmerge this again 
just to go back to two separate cells.   What you're going to do is select your range the same 
way you would do if you want to merge but instead   of merging you're going to right-mouse click 
format cells or use the shortcut key Control 1,   go to alignment here, change your setting to 
center across selection, and then click on ok and   this is going to give you the exact same effect 
except that you still have two separate cells   so I'm just going to copy this, let's double click 
this apply it to this one and this one now if I   write my formula again I can make my selection 
like I normally would and I'm not going to run   into any problems. Now I have two tips for you here, 
one is that you can add alignment settings to your   quick access toolbar. You're going to find it under 
format cells options, when you click this it's   automatically going to take you to here and you 
just have to adjust your settings and click on ok.   Another tip is if you want to find out what cells 
are merged, so let's just merge this one let's   merge this one now you are looking for merge cells 
in your workbook you can use the find feature, so   just press ctrl f decide on whether you want to do 
your search within the sheet or within the whole   workbook, and then click on format here, decide on 
the format that you're looking for. Here we just   want to look for merged cells and then click on ok,
and then we can find next we're going to see this   it's going to find the other one, you can find all, 
you can select them you'll see them selected here,   you can add a color just so you can locate them 
better, close this and then go and update the   formatting, and make it center across selection. 
Next one is poor file structure. I often see files   where everything is condensed and thrown in the 
same sheet. So we have the source data on the side,   we have calculations in the middle, and then 
we have some graphics on this side. Don't put   everything on the same sheet, instead split it 
into separate tabs so you're going to have a   separate dashboard or report tab, you can have 
a separate calculation tab, and a separate tab   for your source data in case it's coming in from 
excel. So in this case, the source data is coming   in from power aquarium from the data model so we 
don't keep it separately in excel, but we keep the   calculations separately then these flow in to the 
dashboard side. You might want to add instructions   and a tab where you control the changes that 
you make to your file. You can also add different   colors to make things easier to find, or as a 
way of visually grouping certain tabs together.   Next one is about hiding rows and columns in 
the wrong places. So generally, it's better to   group rows and columns instead of hiding them, 
why?, well let's take a look at this example. So   let's say you received this from your colleague 
and you quickly want to get the total value for   sales, so you just jump to the bottom here and you 
do a quick sum, let's just highlight everything,   and grab our number, and then we report this to 
our boss, problem is this number is wrong. Why?,   because if i select these numbers, the sum of these 
equals 167 000, not the number that I have here.   Why am I seeing this?, well my colleague has hidden 
some rows here, if I select this right-mouse click  and unhide, that's what they've done, they've 
actually done a data preparation for their chart   on the bottom of this data set. I didn't see it 
because I didn't really have any visual indication   that things were hidden. If you look carefully 
you will see it, so if I go and hide these again   notice we see these two very light lines there, 
this is a visual indication that something was   hidden, so if I also select all of these you can 
see that better. A better visual cue is to group   cells instead of hiding them, so let's just 
go ahead and unhide. To group this information   together, you just need to select it and use the 
shortcut key ShiftAlt the right arrow key. You can   also add sub groupings, just make your selection 
ShiftAlt right arrow key. You can get to it from   the menu as well, if you go to the data tab under 
outline here you have group and ungroup. There is a   shortcut key to ungroup as well, it's ShiftAlt and 
the left arrow key. To ungroup these, ShiftAlt left   arrow key. The grouping makes it more obvious that 
something has been grouped together because if I   collapse this, I can see that box here and it makes 
it more obvious that something is hidden than   just those thin double lines there. Now a better 
practice when you're doing such an exercise is   anyhow to split your calculations in a separate 
tab than to have everything in the same tab.  Next common mistake is to input values or hard 
code values in your formulas. This is especially   difficult to track in the more complex models. So 
for example here, I have a financial model where   I'm deciding on whether it's worth it to invest in 
a new printing press or not. Down here I'm using a   discount factor, now I expect this discount factor 
to flow into my model but if I click on the side   here, it's not using it. Six percent has been hard 
coded in this formula so maybe someone came in   and decided to test stuff out and they just input 
directly the value in the formula. Now these are   difficult to track, one way to see where this is 
flowing to is to just select a Cell go to Formulas   and click on Trace Dependence. So now I can see 
the discount factor so this cell is going in   to this calculation here, it's not going into here. 
I can also do it the other way around, I can go to   this cell and trace precedence, this one is not 
going here, let's go and remove the arrows. So the   right way of doing this is to have a cell where 
you input your value, in this case eight percent   and then we're gonna do a cell reference here, so 
we're going to reference this one and since I want to   drag the formula, I'm gonna fix the referencing 
by clicking on F4, and now we can drag this to   the side and now everything is consistent. So if I 
want to change this, I'm going to update this to 6   percent and everything updates accordingly. 
Now if you have values that don't change,   so for example you want to divide something by 7 
because you have 7 days in a week or by 24 because   you have 24 hours in a day, those ones you can hard 
code in your formulas but values that might change,   like a discount factor here is something that you 
want to have in a separate cell and then make sure   your other formulas are referencing that cell. Next 
one is not taking advantage of tables. So if you   have a data set like this turning it into a table 
is what you need if you want your chart to update   automatically once you get new data, if you want 
your formulas to update automatically once you   have new lines added, and if you want your pivot 
tables or your formatting to update automatically   as well. If you don't turn this into a table you 
have to make those updates yourself manually   which you don't want to do. So let's turn this into 
a table by just going to home and formatting it as   a table, you can also use the shortcut key ControlT.
Here you can decide on the type of format that   you want, let's say I'm going to go with that it's 
going to ask me if my table has headers in this   case it does, I'm just going to go with ok and the 
formatting is automatically applied. If I'm not   happy with this formatting and I want to create my 
own, just select none and then apply the formatting   that you want. So, let's say I want to get a light 
blue for the headers and I want this column   to be light gray. Now, the moment I get more data 
added to this, the formatting is going to come with.   What about the formulas? If I have a formula here 
like a Countif formula, I'm just going to count   the Division Column so the moment I select 
division notice I get Table, referencing my   criteria here is Game. Right now, what should I 
get? 1,2,3,4, right? The moment I add   a new Game underneath this table and my table 
expands, it becomes 5. If I add another one it   becomes 6. If you want to delete these table 
rows but you don't want to delete other things   that you might have on your file here, you just 
select the rows that you want. I'm just going to   select these two cells actually right-mouse click 
delete and delete the Table Rows, so not the rows   of the actual workbook but the table rows. Notice 
these didn't get deleted and my table got smaller.   So this is where I know the table ends that's that 
little marker on the bottom right hand side. If you   have a chart based on this data it's also going to 
update automatically, so we can quickly check that.   Let's insert a Column Chart here that last one 
is Squids with 100, we don't see anything let's   just increase that to 3000. Now I'm just going to
add a new one here, I can automatically see that   let's put 5000 and my chart updates automatically,
I don't have to worry about expanding chart ranges,   it's done because I'm using a table. Okay so, 
that's my list of common Excel things a lot   of us go about the wrong way and my suggestions 
on how you can improve. So, I hope you found some   new techniques here, let me know in the comments. 
If you enjoyed this video, hit that thumbs up.   Subscribe, if you aren't subscribed yet, and 
I'm going to see you in the next video.

Video URL: https://www.youtube.com/watch?v=sFn0gE_y1U4
Today, I'm going to take you through seven 
common Excel errors. I'm also going to add a   bonus one for you here that you're going to come 
across at some point and how you can fix them.   So, one fix is just to get rid of them, but yeah, 
don't do that. So, you don't want broken formulas;   you want consistent ones, and if they 
don't work, you want to understand why,   and at least fix it in a way that doesn't 
return an error. Let's take a look. The first error has the easiest fix. When you 
see values like this, it means double-click to   expand the column. Your numbers are too big to 
be reflected in this short space, that's why you   see the hash sign. Excel doesn't want to cut them 
off because then you might end up reporting wrong   numbers. Notice for text, it doesn't do that. It 
does cut off the text, but it doesn't want to take   that risk on your number, so just double click 
and expand the width of your columns. Number two,   "#VALUE!". The "#VALUE!" error generally 
happens when you use the wrong data type.   So here, if I had a simple formula that deducted 
the previous year sales from the actual sales.   Right now, am I going to get an error when I send 
this formula down? I'm just going to double click,   send it down, no errors. These empty cells are 
treated as zeros with this formula. But if I   had text here, so if I typed in "missing", I'm 
going to end up with a "#VALUE!" error. I can   only deduct numbers from one another, I cannot 
have text. Now there are some inbuilt functions   that can handle or ignore text. So, if I didn't 
want to see that "#VALUE!" error, I can just   put this inside the SUM function and send this 
down and I end up with no errors because the SUM   function is able to ignore text. Number three, 
"#SPILL!". The "#SPILL!" error is a new error   in Office 365 and Office 2021. This happens when 
you use a function that spills to multiple cells.   So, for example, let's say I'm using the UNIQUE 
function here, I'm going to get a unique list   of these values, close bracket, press Enter, and 
I end up with a "#SPILL!" error. Why? Because   my results want to spill, and this is blocking 
it. We don't want "Subscribe" to block it, this emoji looks   even funnier in the formula bar. When I remove 
this, my formula spills and the error is gone.   Next one is the "#NAME?" error. Can you 
guess what the problem is with this function?   There is no function that's called ADD in Excel. 
The correct function name is called SUM. So,   if you type the function name wrong and Excel 
doesn't recognize it, it's going to give you   the "#NAME?" error. To correct this, type in 
the right name. Next one is the "#REF!" error.   So, let's say I'm just deducting these two values 
from one another. Let's just send this down.   Now, at some point, I end up deleting this 
column. What I end up with is the "#REF!" error.   The column that this formula was based on has now 
been deleted. This can also happen if you copy and   paste your formula somewhere else. If I press Ctrl 
+ Z to go back, and this time not delete anything,   instead copy this column and paste it here, I 
also end up with the "#REF!" error. This is   using relative referencing. The Sales column, 
which is two columns to the left of here,   doesn't exist on this grid anymore. I end 
up with an error. Number six, "#DIV/0!" error.   The "#DIV/0!" error happens when you divide a 
number with a zero. So, for example here, let's   say we want to calculate the percentage change to 
previous year. I'm going to go with Actual Sales   divided by Previous Year minus one. When I send 
this down, two cells end up having an error. The   reason for this is I'm dividing this number with 
a zero. Now technically, I don't have a zero here.   If I had a zero, I'd end up with the same thing, 
but an empty cell used in a formula is treated   as a zero by Excel. Now, what you don't want to 
do is go in manually and remove these because   later on when you end up getting a number here, no 
value is going to show up because you've removed   the formula. Instead, what you want to do is 
use error handling directly in the formula.   So, once you've written your formula and you've 
sent this down and you've noticed that this is   going to result in errors, you could put it inside 
a function that can handle errors. There are many   different ways that you can do this. You could use 
an IF function and check whether you have empty   cells, or you could use a generic error handling 
function which is the IFERROR function. You're   going to start off your formula with IFERROR, then 
use the formula that you had written before,   and then the last argument is the value you want 
returned, in case you end up with an error. So,   in this case, let's say I want to return nothing, 
I want to return an empty cell which means I   have to put "" (quotation quotation), close the 
bracket, press Enter, and now check this out,   when I send this formula down, my errors are gone, 
but everything remains dynamic. I'm using the same   formula throughout. So, if I end up getting a 
number here, my calculation automatically runs.   Number seven: "#N/A". The "#N/A" error is a common error when you use lookup functions in Excel. It   happens when your lookup function can't find what 
you're looking for. So, in this case, I'm using   the VLOOKUP function. If you want to learn more 
about VLOOKUP, I have a separate video on this,   link to it is in the cards and in the description 
of the video. But if you have Office 365,   forget VLOOKUP, use XLOOKUP instead, and 
I have a separate video on that as well.   Check it out in the description or in the cards 
of the video. Now, the reason I'm getting an   error for here is that "Limbo" doesn't exist in my 
customer master data. The correct one is "Limba".   Now, in case you want to flag these errors or 
you want to return something else if you get the   "#N/A" error, you can use the IFNA function. This 
checks for this specific error. Your value here is   your formula and what you want returned in case 
you have an error. Let's say is "Person doesn't   exist," close the bracket, press Enter, and now 
when I send this down, instead of getting "#N/A"   I get "Person doesn't exist". You can, of course, 
also use the IFERROR function. IFERROR checks   for any type of error, not just the "#N/A" 
error. The "#N/A" error only checks for this   specific error. So, that was my list of common 
Excel errors and how you can fix them. Which error   do you come across more often and do you use IF 
or IFERROR to fix it? Let me know in the comments.   I hope you enjoyed this video. If you did, 
as usual, don't forget to hit that thumbs up,   subscribe if you aren't subscribed yet, 
and I'm going to see you in the next video.

Video URL: https://www.youtube.com/watch?v=xcVLWyEJHwY
So Excel got this great new function called TEXTSPLIT. It takes care of a problem that we've all   been having for years, that's split text.
Especially if it has multiple spaces,   or it has multiple commas, or a mix of different 
delimiters. So, the more splitting you needed,   the bigger your problem became, and actually 
the bigger your formula ended up being.   That's all over now because TEXTSPLIT takes care of it all.   You can even combine it with other functions to 
make Excel do stuff that we could never do before.   I guess we just want to see it, right? So 
let's get to it.  Let's start with the basics. So here, I'm 
collecting different skills from different people,   and everything is in a single cell. I want 
it split into multiple cells. No problem for   TEXTSPLIT. I'm just going to start typing 
TEXTSPLIT, it's right here. I need my text,   which is this one, and then I have to define my 
delimiter, based on what I want to split these.   Well, in this case, it's a comma, so I'm 
going to put a comma in quotation marks,   close the bracket, press Enter, and I get 
everything split. But take a closer look at this,   I have a comma and a space, so there is 
an extra space that comes after these.   Now you have different options to get rid of this 
space. You could put this function inside the   TRIM function and that's going to trim out 
that space, or...just going to press "Ctrl + Z"   to go back...you could adjust your delimiter 
to be a [comma][space], and you end up with the   same result. What's the problem when I copy 
this down? Is that going to work everywhere?   No, because some people used a semicolon as a 
delimiter, and that ends up messing my results.   Well, no problem for TEXTSPLIT because you can 
add multiple delimiters in here. You just have   to put it inside curly brackets. I'm going 
to find my curly brackets on the keyboard,   we need to close this as well. And inside, we are 
going to define our multiple delimiters. I'm just   going to add my separator and inside the quotation 
marks, I'm going to add my second delimiter which,   in this case is a [semicolon][space] and close the 
quotation mark. You can add as many delimiters   as you want. To be on the safe side, why not add 
a colon as well? And press enter. Now, when I   pull this down, everything is split properly. If 
I happen to add a "[colon]Excel" here, it works   properly. Next up, let's look at a case where we 
have to split the name into first and last, but   we don't want to get this "Mr" and "Mrs" in there. 
We're going to use TEXTSPLIT. The text is this,   and we have a space as a delimiter. Now, if I just 
leave this as is and press Enter, I do end up with   that "Mr" and "Miss" in there, but remember what 
we saw before, I can add multiple delimiters. So,   I could treat these "Mr" and "Miss" as their own 
delimiters. So in quotation marks, I'm going to   put "Mr," and let's add another one for "Miss" 
and let's close that curly bracket. Now, when I   press Enter, I get everything split, but I end up 
with this empty cell here. I want to ignore empty   cells. Well, that's not a problem because "Text 
Split" has multiple arguments that are optional.   We have a row delimiter, we don't need that right 
now, but we need this one, ignore empty cells,   we are going to put TRUE for that. And now, when 
we press Enter, we have first and last names. In this case, we have our data written like 
this in a single cell and we want to bring   it into multiple cells that looks like this. No 
problem for TEXTSPLIT. Let's start with TEXTSPLIT,  our text is this. Now, we can make use 
of the column delimiter and the row delimiter.   So, what do we want the separator of the columns 
to be? It should be the equal sign, so let's put   that for our column delimiter. Next is the row 
delimiter. That should be the comma. I'm going   to go with "[comma][space]" and close the bracket, 
press Enter, and I get everything spilled properly.  Next up, let's do something really cool. We 
want to take our skills here and we want to   get them back in a single cell, but we want them 
internally sorted. So this one should have "Excel"   first, and then "Google Sheets," then "Power BI," 
"PowerPoint," and "Word" last. How do we do that?   Well, we're going to use a few functions together 
to get what we want. First, I'm going to start   from the inside. We're going to use TEXTSPLIT 
to split these up. That's our text. My delimiter   here is a comma but here, I noticed I don't have 
a space, whereas I have a [comma][space] there.   So, to be on the safe side, I'm just going to go with 
a comma as the delimiter, but I'm going to put this   inside the TRIM function so that I don't have 
any space at the beginning of the words. Now that   I have everything split separately, I can use the 
SORT function and get these sorted. My array   is this. My sort index is just the one row that 
I have, so I'm just going to skip that argument.   The sort order default is ascending. I'm going 
to skip that argument because I want the default.   But this is something I don't want to skip, and 
I can't skip because I am sorting by columns   in this case, so I need a TRUE for that last 
argument. I close the bracket, press Enter, and I   have everything sorted. But I don't want them 
on separate cells. I want to bring everything   together again into a single cell. So, I'm going 
to use the TEXTJOIN function and stitch these   back together. My delimiter is a "[comma][space]" 
in quotation marks. I can ignore empty cells,   in case I have nothing or just the space between 
the delimiters, and last is my text which is the   formula I wrote before. Close the bracket, press 
Enter and we have everything internally sorted.   We get "Excel" first, then "Oracle," "Power BI," and 
last is "SAP." Amazing functions that help us do  amazing things. So, as you can see, this function 
is like the formula version of the "Text to   Columns" feature that we have in Excel, except 
it's smarter and it's a function, so it's dynamic.   What do you think about this? Are 
you excited about this development?   Let me know in the comments. As usual, thank 
you for watching. Thank you for being here.   Subscribe if you aren't subscribed, and 
I'm going to see you in the next video.

Video URL: https://www.youtube.com/watch?v=jLHqGRpGOPk
When you install programs on your computer, they 
come with default settings. Excel does too. Now,   some of these settings might be working against 
you because you have your way of using Excel.   There are specifically four settings that you 
should review now to see if you need to update   them. Let me show you. So, the first setting 
you might want to update is this here: when I start   to write a formula, just anything doesn't matter, 
when I press Enter, I move automatically to the   next cell. So, every time you press Enter or 
you type a number, you press Enter, you move   automatically to the next cell. Now, I usually 
write a lot of formulas and then I want to stay on   the same cell, not move down, so that I can easily 
copy my formula down. Now, there is a way that you   can do this with a shortcut key. So, instead of 
pressing Enter, you can do Control + Enter, but   I don't always remember to do this. I just want 
enter to stay on the same cell. There is a setting   for that. You go to File > Options > Advanced, and 
right here, you can define what happens when you   press Enter. By default, it moves down. You could 
move right, up, that would be weird, but if you   want to do a prank on your colleague, you could 
do this, or to the left, but if you don't want it   to move anywhere like in my case, you just uncheck 
this and then you click on "OK" and now every time   you press Enter, you stay on the same cell and we 
can just try by just typing any type of formula   and pressing Enter and we stay right here. By the 
way, since we're talking about adjusting settings,   you should also check your YouTube settings 
to see if you're subscribed to this channel.   This way you don't miss out on any other tips 
that you might find useful in the future.   The next setting you might want to 
update is your autocomplete settings.   If you deal with long text, long names or long 
phrases that keep repeating in different Excel   files, you might want to do this. So, let's 
say I need to write the company name a lot,   so "OfficePlus Headquarters" and sometimes I make 
mistakes and I don't even notice it. It's going to   save me a lot of time to have an autocomplete for 
this. We can easily set this up by going to "File"   options. This time we're going to go to "Proofing" 
right here. We can change how Excel corrects and   formats the text as we type. When you click on 
"Autocorrect Options" you are going to see a   big list here and this is going to be dependent on 
your language. The great thing is that you can add   your own stuff here. Let's say every time I type 
in "op" I want to see "Office Plus Headquarters".   Click on "ADD" and then "OK" and "OK". Now, every 
time I type in "OP" and I add a space, it's going   to put in "Office Plus Headquarters" and it 
doesn't matter which workbook I'm in. I'm going   to open a brand new Excel workbook by pressing 
"Ctrl n" and then I type in "op" space and I   get "Office Plus Headquarters". Next, I think 
you might want to review is your custom list.   You know how every time you type in the 
months here and then you drag this down,   you automatically see the next 
months. Where is this coming from?   It's coming from your custom lists. But, it 
doesn't have to be restricted to months only,   you can add any list you want. You could have a 
list of your departments if that's something that   you keep using in multiple workbooks. To do that, 
go back to File > Options > Advanced, scroll all   the way to the end and you're going to see "Edit 
Custom Lists". When you click on this, you're   going to see some predefined custom lists for you. 
So the ones that I just showed you are right here.   You have the ability to add your own custom list 
by just entering here whatever you want and then   clicking on "Add", or since I have my departments 
in different cells here, I am going to import the   list from cells. I'm just going to click on this 
and drag this. I'll include the word "Department"   as well, because it's easy to remember, and then 
I'm going to click on this import. It's going to   add it here, then click on "OK" and "OK". Now 
every time I type in "Department" and then I   drag this down, I can see my list here. And 
if I continue dragging, I just get a repeated   list. Now this isn't just for this workbook, it's 
a global setting that applies to all my workbooks. So let's open a blank workbook by pressing 
"Ctrl + N" and this time I'm just going to   start from the first Department, it was 
Athletics. Let me just make sure I spell   it correctly. Now I'm going to drag this down 
and I get the other departments. What happens   if I start from "Admin"? No problem, just drag 
this down and you get the next departments,   and then it starts repeating itself 
over and over again. If you don't want   the header "Department" in there, just 
skip that when you import your list. Next setting is to define your preferred pivot 
table layout. Now this annoyed me for the longest   time because check this out, if we go ahead and 
insert a pivot table, let's just insert it on a   new sheet. We get this standard layout from Excel. 
I always had to go and make adjustments to this. I   like to have my report layout in tabular form. 
I don't want to see these subtotals. I had to   go and turn this off. I don't want to see that 
button there, so I would go here turn that off   as well and I'd also go to "PivotTable Options" 
and I would turn off "Autofit Column Widths on   Update" because it annoyed me that every time I 
would refresh this, this would jump and autofit.   You can avoid these steps altogether. If you have 
Office 365, you can define a custom layout for all   your pivot tables across all your spreadsheets. 
You go to File > Options. Under Data,   this time, you're going to see "Make Changes to 
the Default Layout of PivotTables" and you can   edit that layout. You could do it manually by 
defining what you want to see for subtotals, for   your grand totals, the report layout, whether you 
want it in compact form, tabular or outline form,   and also your pPivotTable Options. That annoying 
"Autofit" can be turned off globally here. Now you   can do this manually if you want, but you don't 
have to. If you've already created a pivot table   with the layout that you want, all you need to do 
is import that layout here. So you just click on   "Import" and the settings are brought over. Now 
when I go to "PivotTable Options" that tick mark   is gone. I'm going to click on "OK" and "OK", 
and now that's my default layout across all my   workbooks. Whenever I go and insert a new pivot 
table, let's this time just put it down here. I   get the layout that I just defined. In case you 
want to update the colors of your pivot table,   you can do that from the "Design" tab under "Pivot 
Table Styles." You can use another layout, or you   can also customize a layout based on your 
own brand colors. To make this the default,   right-mouse click and set as default, and this 
will remain the default for this spreadsheet.   Okay, so I know I said four settings, but while I 
was recording this video, I remembered two more.  You know how every time you open the Excel 
application, you come to this standard view where   you can pick one of your recent workbooks, click 
on "Blank Workbook," or click on "Open." Here,   now you might personally find this useful, but 
your working style might be different. You might   prefer to work via Explorer, so you might just 
open Explorer and open whatever file that you   want directly from here. And if you open the 
Excel application, you just want to get a   blank workbook, you don't want to go through that 
startup page. You can adjust that setting by going   to "Options," under "General," scroll all the way 
down here for "Start up options," uncheck this box   to "show the start screen when this application 
starts," and click on "OK." Now, every time you   click on Excel, you get a blank workbook. The last 
setting I want to share with you is brand new,   so new that you probably don't have it yet. If 
you go to Excel options, go to "Advanced," scroll   a little bit down, there's a new section called 
"Automatic Data Conversion." It's currently in   beta in Office Insiders. You have the ability 
to change some of the default data conversion   behavior of Excel. One annoying one is removing 
leading zeros when you're entering numbers,   numbers like product codes, phone numbers, and 
so on. One other useful one is this one right   here: "when loading a .csv file or similar file, 
notify me of automatic data conversions." So,   if this is checked and it should be checked 
by default, once you get these settings,   what happens when you open a CSV file is this. 
So, over here in file explorer I have the CSV   file and we can see in the preview these are 
the product codes that I have. The first two   and the last one have zeros in front, this one 
is just a standard number. Now when I open this   with this feature on, I automatically get this 
notification that "by default, Excel will perform   the following data conversions in this file," 
and it's going to remove the leading zeros.   So, this is how it's going to look like by 
default, so by the old default. Now, we get this   notification, and we can choose not to convert. 
This way, the zeros come with and we don't run   into any mistakes or errors when we're analyzing 
our numbers. So, that's the list of my suggested   settings to update in Excel. Let me know in the 
comments if there is a specific one that you   found useful or maybe some other ones that you'd 
like to share with us. That's it for today. Thank   you for being here. Thank you for watching 
and I'm going to see you in the next video.

Video URL: https://www.youtube.com/watch?v=AXNPR5q1y08
Imagine you start work, open your email, 
and you see this from your boss asking,   "See attachment and provide: Quick summary, 
How tobacco's doing, and something I should   know." You open the attachment and you think, 
"You know what? Let me quickly get this out of   the way before I grab a coffee because it is that 
quick." You know what makes it quick and painless?   It's this feature, Analyze Data in Excel. It's 
sitting right here on the Home tab. So this   is my data set. Let's just check how many rows 
of data we have. Just a little over 200 rows,   and each column has a header. So, we have 
Category, Render, Date, and Cost. So, for Date,   we're collecting data on a monthly basis, and 
we get to see the first of every month here. Now, if I was going to create a pivot 
table on this, I would have to think,   "Where do I need to go to insert a pivot table? 
What do I need to drag to the rows? What should   I drag to the values or columns?" It's a lot 
of thinking involved, especially if it's in  the morning. Thankfully, we have Analyze Data, so all 
I have to do is come here and select   Analyze Data. It's picked up the correct range, 
and I get to ask it a question about my data. If   I'm not sure what to ask, I can try one of these 
options, or I already get some suggestions here.   It tells me, "Cost by Date." I could insert this 
as a pivot table. I have the date here and the   sum of cost. My numbers also look like they have 
perfect formatting. Down here, I get, "Cost by   Category." I could just insert this pivot chart, 
and it's going to insert it on a separate sheet. Here I have some additional insights. So, "For 
Vendor: Treasury Wine Estate, cost decreases   over time." Well, this could be interesting 
for my boss. I wasn't specifically looking   for this. Also for this vendor, cost decreases 
over time. I can go to show more and get more   insights from my data. Maybe find out something 
that is going to surprise me and something that   I might need to follow up on. But right now, I 
don't need to worry about this because my boss   asked for a summary. This is a good summary to 
have, so let's insert a pivot table and see what   we get. We got a new tab, Date and Sum of Cost. 
So, I'm going to change that to be Total Cost. Now I also know that my boss is 
interested in finding out what   the average cost is. Since I already have my 
pivot table and I have my layout right here,   it's quite easy to do that. All I have to do is 
grab Cost and drag and drop it in the Values. So,   I have it two times here, and for the second time, 
I want it to show the average. Average value. So,   I'm just going to double click, go to 
Average, change this to Average Cost,   and click OK. All I have to do is 
update the formatting. Right-click,   Number Format, Number, use a thousand 
separator, and that's it. We're done. Now, what about that chart that was interesting as 
well? Because we want to take a look at the cost   by category, so that's the one I want. But I'd 
like it to be the average cost per category. So,   let's see if we can ask it to specifically create 
that chart for us. Average Cost by Category. I   think that should do. Yeah, we get our pivot 
chart. Now, what if I wanted it as another chart   type? So, what if I said "as a Column Chart"? 
Does that work? Yeah, that works as well. So,   this is better. Let me just insert it. I get a 
new tab with a pivot table. That's the underlying   data for my chart. I'm just going to cut this 
and paste it here. So, this ultimately is going   to be my summary sheet. This is going to be my 
underlying data. So, I'm just going to keep it   somewhere. I'll probably hide this in the end. 
Let's quickly just update this so it's clear.   This is the average cost by category. Now, what 
if you also want a line chart here? Let's go back.   Let's just type in "Cost Trend". Let's 
see if it understands. Well, it's giving   me some specific things, "Cost over Months". 
Yeah, that's better. That's what I want. So,   let's go and insert the pivot chart as well 
and we could grab that and bring it over   to our summary sheet. Right, so you'll have 
to format these accordingly if you need to.   Let me also quickly clean things up. I'm going 
to Ctrl + X this and bring it here. So,   all the data is in one place and 
I'm just going to remove this. Now, what about specific insights? So, if we 
go back here, let's take a look at tobacco. So,   I'm just going to type in "Tobacco". It's actually 
easier to pick it from here than to type it in.   And let's go with "Insights". Okay, so for 
category tobacco, February had the maximum   cost. Yeah, this is definitely something my boss 
is going to be interested in. The cool thing is,   it's also conditionally formatted it by 
highlighting the maximum point here. All   I have to do is insert the pivot 
chart and I have it right here. What about if we wanted the vendors as 
well? So, let's go and see "Tobacco Vendor   Cost Breakdown". Okay, I spelled that 
wrong, but it still got it and it looks   sorted as well. What if I wanted to sort 
it the other way around? Let's do "Sorted   Ascending". That works as well. Okay, let's 
insert a pivot table and here's our report.   The category tobacco was already added as a 
filter to our pivot table. How great is that! Now, what about some specific 
insights? So, let's go back   and ask for "Cost Insights". Actually, we 
see it right here. So, let's just select   that "Insights for Cost". That's what 
we saw before as well. So, let's just   add some of these. I even get "Frequency 
of Cost". Okay, this one is interesting.   This is interesting as well. What if I wanted to 
see the vendors that have a cost above a certain   threshold? Let's see if we can get that done, 
"Vendors with Costs Higher than 7 million".   It picked up the 7 million correctly. So, 
all I have to do is insert this pivot table   and then insert some of the other ones that 
I got from the inside and my task is done. Now, you might be wondering, are these 
dynamics? So, if my data changes here,   is everything going to change? Well, let's 
try it out. I'm going to go to "Food" here.   Let's change one of these numbers. I'll 
just add an extra zero. Okay, so here,   it tells me it looks like your data is changing 
and it's re-analyzing my data. So that's great,   I don't have to do any specific type of refresh, 
but if I want my Pivot Tables to update as well,   I have to do a refresh. So, where was that for 
food? Right, if I just update it here, just so   that we can visually see it change, I'm going 
to refresh, and this changes, but it's not only   this table, it's all the other tables that were 
created as well. So, this one changed as well,   so did all my other Pivot Charts. Now, if 
you want to add data to the bottom of this,   it's best if you format your data set here as 
an official Excel Table. This way, your Pivot   Tables will automatically update the moment you 
press refresh and you don't have to update your   range. Isn't that amazing that with just a few 
clicks we get to create a functioning report?   Alright, so that's one place AI can help 
us analyze data in Excel. Use it and let   me know what you think. Thank you for 
watching, thank you for being here.   Subscribe if you aren't subscribed yet, and 
I'm going to catch you in the next video.

Video URL: https://www.youtube.com/watch?v=Y9HF39yAjjw
A common Excel problem is to find duplicates in 
your data. They might be in the same column or   two separate columns that you are comparing with 
one another. Sometimes, it's not enough to compare   one column to another one, but you need to compare 
the entire row's contents with the other rows and   flag any duplicates you come across. Now, also 
sometimes it might not be enough to highlight   duplicates, but instead, you want a dynamic 
formula that returns the duplicate values for you.   I'm going to show you four ways you can achieve 
that. Pick the one that best fits your case. Okay, so in the first case, we want to compare two 
columns with one another and highlight duplicates.   We have one set of product codes in segment one 
and another set in segment two, and we want to   know if segment two and segment one have any 
overlapping product codes. So the first thing   I'm going to do is highlight my product codes in 
segment one. Use the shortcut key Ctrl + Shift + Down,   then hold down Control and click on the first cell 
in segment two, and then Ctrl + Shift + Down. So we're   highlighting our two separate ranges. Next, go 
to Home, Conditional Formatting, Highlight   Cell Rules, and select Duplicate Values. "Format cells that 
contain," by default it's "Duplicate." You can switch   this to "Unique" if you want it the other way 
around. I'm going to go with Duplicate. Now,   you get to decide how you want to highlight 
these. Pick what you like from this drop-down   or create your own custom format. I'm just 
going to go with yellow fill and click on   OK. Now I can see the duplicate values across 
these two segments. Now it doesn't matter that   these are in two separate columns. You would get 
the same result if they were in the same column. In the next example, I want to find duplicate 
rows. So, I don't want to compare cell by cell.   I want to take a look at the entire row's content 
and compare it to the other rows and see if it's   a duplicate or not. Now, I can't use the method 
I used before. Why? Well, because this is going   to happen. If I go back and highlight duplicate 
values, this is going to look cell by cell and see   if a cell is repeated. I don't want that. I want 
to take a look at the entire row's content. So,   we can't go with this method. Let's go and remove 
the rules. This is what you can do instead. You're   going to use the CONCAT function and combine the 
values of each row. This way, you can generate   a unique key. We're going to send this down, and 
now we can go Conditional Formatting > Highlight   Cell Values > Duplicate Values. And now we have 
duplicates. But this doesn't look good, right? So,   we're going to hide our unique key by pressing 
Ctrl + 1 or just right-mouse clicking, going to   Format Cells, go to Custom number formatting, and 
change this to three semicolons. This hides your   values, and when you click on OK, we get to 
flag the rows that have duplicate values. So,   this one and this one are duplicates, 
and this and this are duplicates as well. Okay, until now, we've used formatting to 
find duplicate values. But what if we want   to use formulas instead? Maybe we want to return 
true or false or the number of occurrences of an   item in a list. Well, here's what you can do. 
In the first example, I'm just going to take a   look at this product column and see if we have 
duplicate products. And in the second example,   I'll show you how you can take a look at an 
entire row to find if an item has occurred more   than once in a column. You can use the COUNTIF 
function. We need the range, that's our range,   and we're going to press F4 to fix it. And then we 
need the criteria, and the criteria is the cell   itself. So it's just the cell that's on B4, right 
under my formula, I'm going to close the bracket   and press Enter. I get a 1 because this item has 
only occurred once in this column. And when I send   this down, we can see this one has occurred 
two times, and this has occurred three times. Now you can build on this formula as you want. If 
you want to convert this to true and false values,   we're just going to check if this 
is greater than one, and now we   get a false here. And for this one, we 
have a true, false, true, and so on. Now, how about taking a look 
at an entire row? Here again,   we can use the CONCAT function and stick 
together the values of the cells to create   our unique key. I'm going to send this down. 
I get a unique key for each single row. And   then I can use this as a helper column and 
go back to the COUNTIF function. The range   is this one right here. Let's press F4 
to fix it, and the criteria is this cell. Of course, you can combine this into one 
step, but it's just going to make the   formula more complicated and more difficult 
to follow. It's just much easier to build it   in separate steps. So our duplicate rows 
in this case are these ones right here. Now you could also disguise this to return 
something else. So we are going to keep this   as our logical test and see if this is 
greater than one. Then we should return   (I'm just going to go with some Emoji 
here). We can return this. Otherwise,   just return nothing. And now when we send this 
down, we get our signs for our duplicate rows.   And then you can go ahead and hide this from view 
so we could, for example, just group this column.   I'm going to use the shortcut key Shift + Alt + 
the right arrow key. I'm going to group columns,   and now this is out of view. Now, you might 
be asking, "What if I want to highlight the   entire row as well?" Well, you can do that 
as well. We're going to highlight this,   go to Conditional Formatting, New Rule, Use 
a Formula. It's up to you what formula you   want to use. I already have this column here, so 
I'm just going to go with one cell here and say,   If this is equal to my sign, which I'm going 
to bring up using the shortcut key Windows + Dot,   and we're going to put this in quotation marks. 
Now, the one thing you have to be careful here   is that currently, this is just referencing cell 
F4, but we want it to be dynamic. So, we want the   column to stay the same. It should always be 
the F column, but the row should change. So,   I'm going to remove the dollar sign from there, 
and now it's up to you how you want to format your   cells. We're going to go with this yellow color, 
and now we are highlighting the row as well. Now, you might be asking, "What if I just want to 
return the duplicate rows? What if I don't want   to highlight them, I don't want to flag them, 
I actually want a separate list that returns   them?" Well, here you can use the FILTER function, 
and remember we already added a unique key here,   so that really makes it simpler for us. We can 
start off with the FILTER function. What do we   want returned? Our data set. What do we want to 
include? Well, we can use this helper column   that we first created, or the second one. It's 
really up to you, but let's assume we didn't have   our flag, we didn't have our sign, we just had 
the unique key. So, I can just use the COUNTIF   function. My range is this, and my criteria 
is this. So, I'm going to select everything,   and it's going to go through it one by 
one and compare the values to the range,   and we're going to see if it's greater than one, 
and if it is, it's going to return the duplicate   rows. And if we don't have any duplicate, 
let's just return nothing. Close the bracket,   press Enter, and we just return our duplicate 
rows. It would be nice to have this sorted so the   duplicates are below one another. Well, no problem 
because we have the SORT function. The array is   this. How do we want to sort it? We want to sort 
it by the First Column, and default is ascending,   so I'm just going to close the bracket, press 
Enter, and now we have our sorted duplicate rows. Okay, so these were the different 
ways you can find duplicates,   but if you wanted to remove these duplicates, 
well, I happen to have the video for you on   that. You're going to find it in the 
cards and also in the description below   this video. I hope you enjoyed this video. If 
you did, don't forget to hit that thumbs up,   subscribe if you aren't subscribed yet, 
and I'm going to see you in the next video.

Video URL: https://www.youtube.com/watch?v=5hD5xqokDeQ
When I search for the term "Convert Excel File 
to PDF," all of these free online Excel to PDF   converters pop up. You don't need them. Excel already 
has an inbuilt feature for that. I'm going to show   you how you can use it, and I'm also going to give 
you some tips on creating perfect PDFs from your   Excel files. Let me first quickly show you three 
different ways you can convert your Excel sheets   or files into PDF. Number one method is just use 
the shortcut key, Ctrl + P. This is going to bring   up the Print Preview options, and you might find 
the option Microsoft Print to PDF in this drop   down right here. Then, you can decide on whether 
you just want to print the active sheet, so the   sheet I was on before I used the shortcut key, or 
print the entire workbook, or just print selection.   So, print selection is based on the cells that 
you highlighted before you came to this view.   You might not want to print everything that's on 
that sheet. Now, notice also that here it says "Print   Active Sheets," so you can select multiple 
sheets, and then go to your print option.   So, let's say I'm going to select Index, hold 
down Control, select the second one, and then   go back to Print Preview, and I get both of 
them show up here in the order of the tabs.   Okay, so that's method number one. The second 
method is to go to File, Export, select Create   PDF, and then click on Create PDF. Give it a name, 
and then click on Publish. The last option, and   this is the one I tend to use often, is to go 
to File, Save As, then from this drop down, select   PDF. Give it a name, and then click on Save. All of 
these options provide me with a nice PDF report. Now, all of that is nice, but what if I want to 
print these three tabs? So, right here, I have a   Training tab. I have all these cells with dark 
gray in the background, I don't want them to be   included in the PDF. I just want this white area 
included. Then, I have this report and I want this   to be in landscape format. And this one should 
be vertical but the data goes over many pages,   and people lose sight of the column headers, I 
want those included. If I go ahead and highlight   these and do what we did before, I'm going to end 
up with a PDF that looks like this. Yeah, it doesn't   look professional, values are being cut off here, 
and there are no page numbers. I wouldn't want to   send this to my boss; it looks like I have no idea 
what I'm doing. The solution is super simple. It's   called Page Setup. That's where you need to go 
to make the corrections you need. Let's do that   quickly. Let's start correcting this sheet. If I go 
to Print Preview, notice that the page currently   runs over to 3 pages and I have all these gray 
cells included. I don't want that. I only want to   see the form. The solution is right here. Page 
Layout, under Page Setup, you're going to find   Print Area, that's what we need. But before we 
use this, we need to select our print area. So,   I'm just going to zoom out, and then I'm just 
going to select the area that I want included,   and go to Print Area, Set Print Area. Okay, so, so 
far, so good. Now, if we go back to Print Preview,   we don't see those gray cells anymore. But this 
is running over two pages, and I don't want this   stuff here. I can make this fit onto a single 
page. So notice under Scale to Fit, the scale is   by default 100, and the width and height are set 
to automatic. Now, you can adjust this or you can   adjust the width and the height. So, let's say I 
want the width to be 1, and I want the height to   be 1, and the scale is automatically going to 
adjust to make it fit these requirements. So now,   if I go back to Print Preview, I get everything 
on a single page. Now whenever you're using this   option, make sure that your scale isn't too low, 
right? Because otherwise, everything is going to be   very difficult to read. Now, another thing I want 
to add to this page are page numbers. Because if   you're printing more than one page, it really helps 
to add page numbers. We can do that from here as   well. Just go to More Options by clicking on this 
arrow, Header/Footer. For Footer, we're going   to add page numbers. I'm going to go with this 
version, Page 1 of question mark, and then you   can either just take a look at it in the Print 
Preview. Currently, it looks like this because I   only have one sheet selected. If I had more, I would 
see 1 of 2, 1 of 3, and so on. Okay, so now,   let's move on to the next one. I want this sheet 
to be printed in landscape format. So currently, if   I go to Print Preview, it looks like this. What I'm 
also losing is the information that I have in the   first two columns, right? When I'm looking at these 
numbers, I have no idea what app they're referring   to. So, let's go and fix this. First of all, let's go 
to Orientation and change this to landscape form.   Next, let's go to Print Titles. Here you can define 
what rows or columns you want repeated. So, in this   case, I want the first two columns to be repeated. 
So, we're just going to go and select these two   columns, and while we're here, let's go to Header/Footer, and activate page numbers for this as   well. And then, click on OK. Now, let's quickly take 
a look. We have Page 1 of 2. When I go to the   next page, these are repeated. But now, it looks kind 
of weird when I have so much data on the first one,   and then just two columns on the other page. I want 
to split it so that the first page only shows me   January data, and all of this goes on the second 
page. I can fix that by managing my Page Breaks. I   want to page break right here. Select the column, go to 
Breaks, and insert a page break. Now, when I go back   to Print Preview, notice it looks a lot nicer. Now, 
you can also view all the page breaks in the View   tab, under Page Break Preview, and you can adjust 
them if you need to by just dragging these. Now,   let's go to our last sheet. This one currently 
looks like this. It runs over two pages, and I'm   losing the information on the column headers; I 
want to add these. I'm sure you know by now how   to fix that, right? We're going to go back to Page 
Layout and select Print Titles. This time, I want   to fix the rows that I want to repeat on top. So, 
let's just go and select these ones, let's go to   Header/Footer as well, and activate our page 
numbers, and click on OK. Now, let's take a look at   all of these together. I'm going to hold down Control 
and select these three sheets. Ctrl + P to take a   look at it in Print Preview. So, I have 1 of 5, 
these are the sheets that I having landscape form,   and this is my last one. Okay, so everything looks 
great now. We can go ahead and print this to PDF.   Now pick whichever option you prefer. You can 
save it as PDF, export it as PDF, or just print   it directly from here. I'm going to call this 
one "Nice Report. Let's quickly take a look at it.   So, what it comes down to is proper page setup. 
You can add the company logo as the header as   well if you want, or add other information to 
the footer or header, right? So, you can modify   these as you need. Just keep in mind that 
it doesn't matter whether you're printing   these on paper or you're printing as PDF, you're 
going to need to have a proper page setup before   you do that. As you can see, it's that simple to 
convert your Excel files to PDF. You don't have   to upload your file to another external app to 
get it done, which can also be risky, especially   if you have work data in there. Okay, so that was 
it for today. I hope you liked it. Do give it a   thumbs up, do subscribe if you're unsubscribed 
yet, and I'm going to see you in the next video.

Video URL: https://www.youtube.com/watch?v=9uIxy9auP_o
Leila here, welcome back to the channel. You 
are about to reclaim your time because today   we're diving into some Excel Time Savers. I'm 
going to show you five hidden features for busy   people. These are features that you didn't know 
you needed, but you won't be able to live without   after you see what you have to see. So, let's not 
waste another second and jump right in. Okay, so   number one is Quick Jump Bookmarks. Excel doesn't 
have a built-in bookmark feature. I wish it had,   but there's a cool trick you can use to achieve 
the same results. This is great, especially for   your larger workbooks that have so many different 
tabs. Let's say you consistently need to go to   some specific places in this file. For example, 
you have to come to this Final tab and go all   the way down here to this more important 
stuff. Every time you open this workbook,   you end up somewhere else, and then you have 
to scroll here, locate the tab, find that tab,   then scroll to get to the place that you want 
to go. You don't have to do this if you have   a bookmark for this exact location. How can you 
do that? Well, you just have to use this little   box here. This is a box that you probably look at 
every single day but had no real use case for it,   unless you're an Excel Pro, then you're probably 
using this. But if you haven't used it so far,   you can use it now as a bookmark. Just select 
your cell and then go to the Name Box and type   in a name for your bookmark. So, I'm going to 
call it "BookReport." You can't have any spaces   here, then press Enter. Now, it does help if you 
use some type of naming convention so all your   bookmarks start with something that you easily 
recognize because this Name Box also includes   any tables that you might have in your workbook, and 
you might be using the Name Manager for other   purposes as well. So, now we have a bookmark for 
this cell. What does this mean? It means that we   can be anywhere in our workbook, and all we have 
to do is click on this dropdown to jump to our   bookmark. You can also just type in the name of 
the bookmark. So, here is "BookReport." I'm going   to type it in, press Enter, and I jump directly 
here. This is a cool way of using this Name Box,   which creates names in Name Manager. So, if at 
any point in time you want to delete a bookmark,   you're going to go to Formulas > Name Manager. 
You're going to see your bookmark here. Click on   Delete, OK, and that bookmark is gone. A very 
cool trick to navigate larger workbooks really   fast. Next up is Analyze Data. This is formerly 
known as Ideas. Now, this is your shortcut to   get answers from your data, and it's based on 
AI. Let's just do this quick thing together.   Let's say you just received this data set, right? 
It's the first time you see it. You have Month;   we have Product; we have Total Sales, Units 
Sold, and Customer Ratings for each product and   returns. Now, we quickly want to find out across 
all time periods. Right? So, this is the data for   an entire year. Across all time periods, we want 
to know what are the five products that have   the lowest ratings. How would you go about finding 
this? Well, how about if I told you, you can do it   with two clicks? You just click here once, Analyze 
Data. Sometimes you might find your answer right   here, but this is quite a specific question. So, 
we're just going to type in here, "Give me 5 products with the lowest customer ratings," press 
Enter. "Here's our Answer - Showing bottom 5 in   'Product'..." That's the Product column "...by average 
'Customer Ratings.'" I got the average ratings,   and it filtered it to the bottom five. So, all 
I need to do is insert a PivotTable, and I get   my answer on a separate sheet. I can adjust the 
PivotTable settings if I want from the PivotTable   Fields menu here. If I want to find out the detail 
for this first one, which has the lowest ratings,   I'm just going to double-click, and it's going 
to create a new sheet with all the details. Now,   this here is a standard PivotTable feature, but 
the great thing is we didn't even have to know how   to create a PivotTable. All I had to do was go to 
Home and click on Analyze Data, and the rest was   just a click or two away. It's kind of like having 
a mini data analyst right in your spreadsheet.   Next up is One-Click Forecasting. This is a 
lifesaver for anyone dealing with sales or   revenue data. Just imagine being able to forecast 
your future sales. Well, you don't have to imagine   that because we're going to do it together right 
now. So here, I have Bicycle Helmet Sales data   monthly starting from 2021 all the way to June 
2023. Let's say I want to forecast my sales for   the next year and a half. All I have to do is 
select my data set, I'm going to press Ctrl + A,   go to Data > Forecast Sheet. That's it. My 
future sales data is automatically forecasted,   and it accounts for seasonality as well. Now, it's 
forecasting a lot of future periods, but I can   adjust that right here. So, for Forecast End, I 
can click on this calendar picker and just adjust   it to the end of December. Now, under Options, 
we have the Confidence Interval. It's set to 95%.   This means that 95% of my future data points are 
going to fall somewhere between this lower bound   and the upper bound right here. Seasonality is set 
to detect automatically, but you can also set it   manually as well. Now, once you're happy with what 
you see here, just click on Create, and there you   go. You have your chart with future sales data 
forecasted right here. You can make adjustments   as you need to this. This is a standard Excel chart. 
Update anything you want under Formatting or   under Chart Design. The great thing about this 
is that you automatically get a table that goes   along with this chart. You can see the lower 
confidence bound and the upper confidence bound   of your future months. So, you can also make any 
adjustments that you want directly to this table,   and it's going to reflect automatically in your 
chart. The next time-saver is the Quick Analysis   Tool. This one is like having your own personal 
Excel assistant. So, let's say I have units sold   for different products, and I want to highlight 
the values that are above 500, and I want to do it   really fast. What I'm going to do is press Ctrl + A, 
then Ctrl + Q to bring up the Quick Analysis Tool.   Right now, we can see all the different options 
we have. We could quickly add sparklines, turn   this into a table if you want, add totals for sum, 
average, count, and so on. We could add a chart if   you want. So if I hover over this with my mouse, 
you can see the different charts that we can   immediately insert with just a click of the mouse 
button. If I want to do some formatting, so apply   some conditional formatting, I'm going to go to 
Formatting. If you want to use your shortcut keys,   you can press Tab, and then you can see how things 
look if you added data bars to this, if you added   a color scale to this, I can set and so on. All 
of this is a bit too dramatic for me. So, let's   say I just want to highlight specific values. 
I'm interested in months and products where my   unit sold value is above 500. So, I'm going to go 
to Greater Than, press Enter, change this to 500,   and adjust the color that I want to use. So, I'll 
go with green fill. You can also use your own   custom format if you want, but I'll go with green 
fill in this case and click on OK. My conditional   formatting is automatically applied. Now, what if I 
wanted to add sparklines to this? I'm going to do   Ctrl + A, Ctrl + Q again. You don't even have to press 
Ctrl + Q. If you come to the side and you click on   this little icon, this menu is going to pop up. 
Let's go to Sparklines and add a Line Sparkline.   Now, we can quickly see the monthly trend. If you 
wanted to add running totals to this, again, let's   just highlight. This time I'm going to go down and 
click. Go to Totals and select Running Total. And   automatically, we get a running total added. So, 
check this out. Here we get the sum of January,   and here we have the sum of January all the way 
to March. For February, it's just January and   February. All of these calculations and visuals 
are just a click away. Last but not least,   Power Query. You see, Power Query is the ultimate 
time-saver, and I had to come on camera for this   one because I feel so strongly about it. It's 
not a tool that's reserved for geeky people.   It's a tool for anyone that works with data, 
data from different places, or messy data,   which is practically what anyone using Excel deals 
with. You see, Power Query is in the Data tab in   the ribbon, so you already have it. And if you 
want to import data from text or CSV, you just   have to come here. You want to grab data from the 
web, from a web page, just click on this. You want   to grab data from a picture, just use this, or 
grab data from a PDF file, you can do it from   here. Now, I have separate videos on these, but 
in this example, I want to show you one concrete   case. And if you have your data set up like this, 
you are going to feel the relief that comes with   using Power Query. So, imagine this. Imagine you 
go to the office, and your boss gives you this   file. You have monthly data, so we have the date 
here, but basically, these are just placeholders   for the first of the month for different products 
and their total sales. And your boss tells you,   "Give me this data in this format." Basically, 
we want to have the months in the columns. So,   you take a look at this data set, and you see, 
"Okay, we have over 400 rows of data. How are you   going to go about arranging the data to get it in 
that format? Are you going to first replace these   with the name of the month and then grab the data 
and copy it over to the right column? Is that the   way you're going to go about it? Or if you know 
Power Query, you are going to do this with less   than 10 clicks." Okay, so here comes click number 
one. We're going to go and select From Table   Range. This is going to send our data to Power 
Query. Click number two is to go and add a column.   Go to Date. That's three clicks. Month. Name of 
Month. Okay, so that's four clicks so far. Now,   I'm just going to select the Month column 
to delete it. That's five clicks. Now, let's   select Month Name. Six clicks. Go to Transform. 
Seven clicks. Pivot Column. Eight clicks. We just   have to select which column is our Values column. 
This is the Total Sales column, so I think we're   at eight clicks or nine clicks. I'm losing count, 
but let's say, okay, nine clicks. Click on OK, and   that's it. We've done it. Let's do our 10th click 
to send the data back to Excel. I'm just going to   click on Close & Load, and now we have our data 
formatted as a matrix by month. That's 10 or less   than 10 clicks to go from here to here. How cool 
is that? Now, there's so much that Power Query can   do, and if you want to learn it in a structured 
way, we have a complete course called "Master   Excel Power Query: Beginner to Pro." Now, just 
before I recorded this video, I went to look how   many students we have, and I was shocked, 51,323 
learners, including our corporate customers   learning Power Query from us. I am super grateful 
to have so many people trust us teaching them this   beautiful, wonderful, very useful skill. This is 
a very comprehensive course. It's over 18 hours   of video, and it comes with a lot of bonuses, 
including two very comprehensive downloadable   eBooks. The list of topics you can see here, 
we have a lot of challenges and quizzes and   best practices because it's very important to test 
your knowledge on different sets of exercises and   challenges and data sets. Because we have a lot 
of topics here, we've also created a Transporter   for you. This is a topic transporter where you 
can search based on keywords and tags. This way,   you can quickly find the lecture that you want and 
be automatically transported to that lecture. So,   for example, let's say you want to take a look at 
the Pivot Column technique, right? Something that   we just covered. You can just search for it, 
and then take a look at all the places that we   cover the Pivot Columns. Right? So here you can 
see the tags, and here are the lectures. Now,   let's just jump up here. Let's say you come to 
the Advanced Pivot Techniques, and you want to   be transported here. You just click on this, and 
you're immediately taken to the lecture here. We   also have subtitles for all of our videos, and 
in case something doesn't make sense and you're   stuck, you can just ask a comment. Put in your 
comment here, and you're going to get help from   our friendly teaching assistants. Whether you 
learn Power Query from our course or from one of   these YouTube videos, just remember it's a major 
time-saver that can make your data life a whole   lot easier. So, don't miss out leveraging its 
power. All right, so that wraps up our deep dive   into Excel time-savers. I hope these tips help you 
reclaim some of your valuable time. If you found   this video helpful, go ahead and hit that Like 
button, and don't forget to subscribe for more   awesome content and share it with your Excel 
friends and colleagues. Thank you so much for   joining me today. Your support means the world to 
me. Until next time, take care and keep Excelling.

Video URL: https://www.youtube.com/watch?v=TDphx23AtqM
Here's the thing, if you use Excel, you need to 
know VLOOKUP. It's the most used Excel function   there is. It helps you automate your data 
analysis, obviously, help you save a ton   of time and avoid errors, which is all we want, 
right? It's also the OG lookup function in Excel   because it works in all Excel versions. So here's 
the deal, by the end of this video, you are going   to know what VLOOKUP is, how it works, how you 
can look up a value from any another sheet,   how you can look for an exact match or the 
closest match, and if you run into an error,   you are going to know how to solve it. Okay, so 
this is going to be your ultimate VLOOKUP guide,   and you need to pay close attention because I am 
going to have a test for you at the end to make   sure you understood everything. So now let's start 
with our first example. Here we're going to take a   look at the most common use of VLOOKUP, which is 
to look up related information from another place   and bring it over here. So in this example, we 
have a list of customer names, and we want to find   each customer's profession. That information is 
available somewhere else, that happens to be right   here. So we have customer names, phone number, and 
profession. Now, this list happens to be short,   but it could be, like, in reality, it could 
be thousands of rows of data. You don't want   to do this manually, right? You don't want to 
look each person up here, find the information   that you want, copy it or type it over here. You 
want it automated, and that's what VLOOKUP helps   us do. So the first thing we need to do is to go 
to the cell that we want our answer in, then start   typing in an equal sign and just start writing 
VLOOKUP. As you start to write it in, you're going   to see it pop up here. Use Tab to accept it, and 
it also opens a bracket for you. Now inside here,   we can see what this formula needs to be able to 
work properly. So the first thing that it needs,   or the first argument as we call it, that it needs 
is the lookup value. So what are we looking up?   We're looking up Sid in this case. So we just 
have to select that cell. Next, we move on by   using the argument separator in Excel. So in my 
case, I'm using a comma; you might need to use a   semicolon. That's just going to depend on your 
regional settings, on your locale settings. So   the next thing that Excel needs is a table array 
for VLOOKUP. This basically means the range it   should look it up in. Now that range must include 
your lookup value, and it must include the answer   that you want. So the column that has the answer, 
you can't select individual ranges with VLOOKUP,   you have to select an entire range, which in 
this case is this range right here. There is   one rule that you have to keep in mind: the range 
that contains your lookup value has to be on the   left-hand side of your result column. This means 
that name here in this range has to be on the   left-hand side of profession, and it is. That's 
important for VLOOKUP. Then we move on to column   index number. This is the column number that we 
want returned. So Excel starts counting from one,   so this is the first column, second column, third 
column. What would we put here? Number three,   right? Because we want the third column returned. 
That last argument here is in square brackets, and   in Excel, anything in square brackets means that 
that argument is optional. So here's the place   where a lot of people make mistakes, they ignore 
that last optional argument. Why? Well, because   the formula works if I just press Enter. But is 
it correct? Sid is not head of procrastination,   Sid is keyboard manager. If you're looking for 
an exact match, that optional argument isn't   optional for you, you have to go with FALSE, which 
means you're looking for an exact match. Now when   I press Enter, it works correctly. Just something 
to keep in mind if you are sharing workbooks with   colleagues and you don't see them using FALSE 
in there, instead you see them using zero,   that's not a mistake, that's also correct because 
zero translates to a FALSE in Excel. You either   need to have a zero or FALSE for this exact 
match to work correctly. If I drag this down,   does it work? It kind of works, but it doesn't 
fully work. Why? Well, if I go into edit mode,   notice that my range, that area is shifting 
down. Moltisanti is no longer in that range.   So if you're planning to copy down your formula, 
you have to fix that table array range. And to do   that, just select it and use the F4 key. Now when 
I drag this down or just double-click to send this   down, this works fine, my range isn't shifted. 
That's how you use VLOOKUP for an exact match. Now   let's take a look at looking up value from another 
sheet. So here I have the list of names and I want   to look up each person's phone number. This time, 
that information is available on another sheet   called Data Numbers. So I have the person's name, 
phone number, and profession. Now remember, for   VLOOKUP to work correctly, the lookup column has 
to be on the left-hand side to the result column,   so to what I want returned. That is the case 
here. This means that my VLOOKUP is going to work   without problems. All I need to do is start typing 
VLOOKUP, lookup value is this one right here, my   table array is my range on the other sheet. So all 
I'm going to do is just click to go to the other   sheet, we can see the sheet name popping up in the 
formula bar, and select the range that I want. I   don't need to include profession because I'm only 
interested in phone number, so I just need my name   column and the phone number column. I'm planning 
to pull down my formulas, so I'm going to press   F4 to fix this reference. Next, column index 
number, what number is that? Number two, right?   Name is number one, phone number is number two, 
that's what I want returned. I want to grab each    person's phone number, so I put a two here. The 
last argument here is not optional, if you want   an exact match, we have to go with FALSE or zero. 
Close the bracket, press Enter, and I get Moltisanti's   phone number. Let's just double-click to send 
this down, double-check Uhura starts with 206,   ends with 9988. Uhura 206, 9988. Next, let's take 
a look at using VLOOKUP to find the closest value.   So this is typically used when it comes to looking 
up numbers. Here we have a list of names, we have   each person's score, and we want to translate 
that score into a grade. But in our lookup value,   we don't have each possible number in the grade, 
instead we're dealing with ranges. So anyone who   has between 0 to 54 is going to get an E, anyone 
who's between 80 to 89 is going to get a B. For   Excel, the way we provided the score and the 
grades is like this, we just specify the lower   bound value. So here's a zero, here's 55, 70, 80, 
90, so this is how Excel understands. This part is   just easier for us to understand what's happening. 
All we need to do then is start off with VLOOKUP,   the value we're looking up is the score. Where are 
we looking it up? In here. Next, what do we want   returned? We want to return the grade, that's the 
second column. So we're going to put a two here.   Now, you can happily ignore that last optional 
argument because default Excel behavior is to look   for an approximate match. So when I press Enter, 
I get E. 26 gives me an E. Now can I just pull   this down? No, I need to fix this. I'm going to 
use F4, and then let's just double-click to send   this down. So anyone who has 75 is going to get 
a C, and that is correct. Now, this doesn't mean   that you can't put in a TRUE. In fact, it makes 
it more clear that you are doing an approximate   match. Just one thing to keep in mind is that when 
you're using VLOOKUP with an approximate match,   make sure that your scores, that your values that 
you're looking up are sorted in ascending order.   Now let's take a look at some common errors you 
might run into when you use VLOOKUP and how you   can correct these. It's also a good way to test if 
you paid close attention during the first part of   our video. I have customer IDs here, I've written 
a VLOOKUP formula to return customer names based   on this range, but it's not working. US4556 is 
returning Silvrr, but it should return Blend. Why   isn't it working? You probably guessed the answer, 
right? I've forgotten to add the FALSE in the end   for the exact match. You can also type in zero, 
both is going to result in the correct customer   name, right? Blend for this US company ID. I want 
to show you something interesting. I just press   Ctrl +  Z to go back, right? So here I'm returning 
wrong results for all of these, actually,   but take a look at this. If I leave the formula as 
is, okay, so approximate match, not exact match,   last argument is gone, is missing. If I go to my 
data range here, I right-mouse click, Sort, A to Z,   everything works properly. I get the right 
results. Why? Because for approximate match to   work properly, your lookup range has to be sorted 
in ascending order. But I don't recommend that   you do this because someone could come along and 
just insert another company ID in there and messes   up all the sorting or if an ID doesn't exist. So 
let's say I have DE8364 and this doesn't exist in   my lookup area here, I still return the closest 
match, and that's not something you want, at   least not in these types of cases, right? So I'm 
going to press Ctrl +  Z a few times to go back,   okay? So what we want to do is add FALSE or zero 
as the last argument, and when I send this down,   it works, this one doesn't work. Why? Well, okay, 
I don't expect you to solve this because I did add   a sneaky error here. There is a space right after 
this name, that's why it's not working. For it   to work, I just have to remove the space, press 
Enter, and it works. But if I have a lot of data   and some of them might have a space, some of them 
not, I want to take care of that in the formula.   I could wrap this part, this reference, the lookup 
value reference inside the TRIM function. The TRIM   function is going to trim any spaces at the 
beginning or at the end of the text. So when   I send this down, everything works like a charm. 
Now, let's take a look at another common error   you might run into with VLOOKUP, and that's right 
here. We want to look up the customer ID from here   and return the customer name. I've written the 
function to look up B4 in this range here. I want   to return customer name, which is in the first 
column, and I'm doing the exact match thing. So   why am I getting #N/A? Well, that's because the 
column that includes my lookup values is on the   wrong side. For VLOOKUP to work correctly, this 
column has to be on the left-hand side of what I   want returned. So now if I go to my function 
and just update the range, if I press Enter,   I get company ID back because I have to update 
the column. I won't return it to two now and   send this down, everything works correctly. 
So you have to keep this in mind when you're   using the lookup. If you want a more flexible 
function and you have a later version of Excel,   so you have Excel for Office 365, you can use the 
XLOOKUP function because that function doesn't   have this restriction. Now, another error you 
might come across is this type of error. So   if a value doesn't exist, you're going to get 
an error, and that's good to know because you   can go ahead and correct this, or if you want to 
return something else in case you have an error,   you can use the IFNA function, for example. So if 
your function is returning #N/A, what do you want   returned? You could put it in quotations. We can 
say "Not found". Close the bracket, press Enter,   and send this down. You end up with "Not found" 
whenever something is missing. You can also use   the IFERROR function as well and send this down, 
and we get "Not found" for this one. So how many   of these did you figure out on your own? Let me 
know in the comments. Okay, so that's how VLOOKUP   works. I hope you're super confident, and if you 
run into errors, you know how you can fix it. Now,   if you have newer versions of Excel, if you have 
Excel for Office 365, I suggest you use the new   lookup function that Microsoft introduced, which 
is called XLOOKUP. It's a lot easier to use and   it's more flexible. Now, I have different videos 
on this on the channel, so just check them out.   I'll add the links to the description of this 
video. Thank you for being here, thank you for   watching, and if you enjoyed this video, you want 
to discover more tips and tricks, do subscribe   to this channel if you aren't subscribed 
yet, and I'll catch you in the next video.

Video URL: https://www.youtube.com/watch?v=3mkfF1pNw0U
Hi there, Leila here. So today, I want to 
address a very common question I get, which is:   how do I delete blank rows in my dataset? 
Now, you obviously know how to do this,   right? To select the entire blank row, right-mouse 
click, Delete. But you obviously don't   want to do this manually, especially if 
you have a lot of data. So in this video,   I want to show you three different ways you can 
get this done. My personal favorite is the third   way. I'm curious what yours is going to be, 
so comment below this video and let me know. The first method is super fast; it takes under 20 
seconds. The first thing you need to do, though,   is to find your identifier or identifying 
column. What I mean by that is, for example,   in this dataset, whenever the date here 
is empty—so whenever I have a blank cell   in this Date column, I want to delete the entire 
row. But if I have a blank cell anywhere else,   for example, here, I don’t want to delete 
the entire row. So in this example,   my identifier is going to be the Date column. Now, the next thing you need to do is select 
that entire column. I'm just going to go to the   bottom of this dataset, Ctrl + Shift, and then 
press the Home key to jump all the way back up and   select everything in between. Now, you're going 
to go to Find and Select, Go To Special, Blanks,   OK this. Don't click anywhere, go directly to the 
menu here, Delete, and delete the sheet rows,   and all those blank rows are gone. You can get 
even faster if you use shortcut keys to get this   done. So let me show you that version too. After 
you've selected your identifier column or columns,   press Ctrl + G to get to Go To, Alt 
+ S to get to Special, K for blanks,   press Enter. We can see the cell selected. To 
delete the entire row, press Ctrl + minus,   arrow down, Entire row, and Enter, 
and all those blank rows are gone. Method number two is the filter way. Now, 
this is great whenever you don't have an   identifier column, and you want to make sure 
that every single cell in a specific row is   empty before you delete it. So, for example, 
let's say sometimes the date might be empty,   and I definitely don't want to delete this 
row—I want to keep it. So I can't use the date   as my identifier. And let's say I can't use 
any other column as my identifier because   sometimes location might be empty, sometimes 
campaign name might be empty. I want to make   sure that every single cell in a specific 
row is empty. This is what you need to do. Before you activate Filter, you need to add a 
helper column. So I'll just call this “Check.”   Now, we are going to use the COUNTA function. 
And we're using COUNTA because we want to count   anything that's in a column, whether it's a 
number or text, and I'm going to select all   these cells in the same row, close bracket, press 
Enter. I have seven cells that aren't empty here.   We're just going to copy this all the way down, 
so let's just drag this down to the end of our   dataset. Whenever we're dealing with a blank 
row, we're going to have a zero count here. Now, we're going to activate Filter, 
Ctrl + Shift + L to activate Filter here,   or just go to Data and select Filter. Then, 
let's filter for the zeros. So I'm going to   uncheck Select All, place a check mark 
for zero. Now all the zeros are here,   so let's Ctrl + Shift + down to select 
all of them. Now, you can go to Home,   Delete, Delete Sheet Rows, or you can 
right-mouse click and Delete Row,   or use the shortcut key Ctrl + minus to delete 
all the entire sheet rows. Okay, now we're going   to Ctrl + Shift + L this again to remove 
the filter, and all our blank rows are gone,   and we've left everything else where it should be. 
Now, I can go ahead and delete this helper column. Save the best for last, right? The third way is 
the Power Query way, and as I mentioned earlier,   this is my favorite method because it's dynamic, 
and it's super fast. Okay, so here's how it   works. We are going to go to the bottom of our 
dataset, and in fact, because this is dynamic,   we're even going to account for future data. 
So I'm going to add in some blank rows at   the bottom here, so let's just go to row 200. 
Now, we're going to select the entire range,   so Ctrl + Shift + the Home key to select 
everything. Go to Data, and here we're going to   go with From Table/Range. But here's the problem: 
the moment I select this, Excel wants to convert   my source data into an official Excel table. 
Now, this might be fine for you, and if it is,   just go ahead and click OK. But sometimes 
you don't want that, right? In this case,   I don't want Excel to convert this data into a 
table; I just want Excel to keep this data as is. So what I'm going to do is cancel this. Instead, 
I'm going to go to this box here—this is called   the Name Box—and I'm going to give this range a 
name. So I'll just type in "MyRange." You can call   it whatever you want—just don't put any spaces 
there—and then press Enter. Now we're using a   named range. So the moment you click on this, 
Excel notices that we're using a named range,   so it's not going to convert your dataset 
into a table. If you happen, though,   to click away and your range isn't 
selected anymore, just go to this   dropdown here. You're going to see your named 
range, select it, and now you're going to see   everything selected. Then go and click on From 
Table/Range. Power Query is going to open up,   and it's going to send that named range into 
Power Query. Now, go to Remove Rows and select   Remove Blank Rows. That's it. We're going 
to send this back to Excel, Close & Load,   and it's going to load our clean data on a new 
sheet as an Excel table. We can see it excludes   all the blank rows. Now, you can adjust the look 
of this if you want. I'm not a fan of these table   styles, so I'm just going to go and remove 
this and let's just make the headers bold. The cool thing about this, remember, is 
that it's dynamic. So if we get new data,   if I just go all the way to the bottom here,   and let's just copy this last batch and paste 
it here, I'm just going to put in my name here.   So we have new data added to the bottom of 
this dataset. I can even add another blank   row here in between. Now, I'm going to go to 
my table. This is just called “MyRange,” but   you can rename this to whatever you want. We say 
“Report No Blanks.” We're going to refresh this,   right-mouse click, Refresh, and the new data shows 
up here. That's the beauty of using Power Query. If, though, at any point in time, you want to 
remove the connection to the source data, you just   want to have this as standalone and not dynamic 
anymore, you can go to Queries & Connections   here, right-mouse click, and delete this query, 
and that's going to remove the connection to the   source data. But the great thing about having it 
like this is that you can decide how you want to   consume the end result. Do you want to have it 
as a table like this, or maybe you want it as   a Pivot Table? In that case, you just need to 
right-mouse click here on the query, Load To,   and select PivotTable Report. When you OK this, 
Excel is just going to warn you that if it's   going to put a Pivot Table here, it's going to be 
removing this data, which is fine. We're going to   go with OK, and now we have a clean data source 
to create a pivot table on. How cool is that? I'm really curious though, which method you like 
the best, so comment below and let me know.   Now, I personally love Power Query because I find 
Power Query to be one of those tools where,   with just knowing a little bit about it, you can 
achieve so much. So, if Power Query was like a   food type—and I'm kind of obsessed with food right 
now because I'm doing this training plan to build   some muscle, so I'm watching what I eat—and I was 
thinking, if Power Query was like a food type,   it would be one of those highly nutritious 
foods that you want to consume every day if   you want to be healthy. Just with a little bit 
of Power Query, you can get so much out of it. Now, if you're interested in learning Power Query, 
you might be familiar with the courses that we   offer. We do have a Power Query course that takes 
you from beginner all the way to ninja level. But   the thing with Power Query is that you don't even 
have to go to ninja level—you don't even have to   go to advanced. After you cover the beginner 
sections, you can already achieve so much,   and it just opens all these new opportunities 
that you might have completely missed before. Now,   if you also want to learn about pivot tables 
and see what pivot tables have to offer,   we recently released a new course 
called Pivot Table Essentials. Okay, so check those out if you're interested to  learn more.  Thank you for being here. Thank you for watching all the way to the end and I'm going to catch you in the next video.