Transcripts
1. Introduction - Supercharge Your Productivity: Hi there, and welcome. Are you ready to unlock the full potential
of Microsoft Excel? Whether you're a
beginner, just starting out or someone who's been
using Excel for years, this class is packed with ten powerful essential tips that will transform
the way you work. My name is Blake Charles, and I've been using
Microsoft Excel professionally for
over ten years now. These tips will elevate
your Excel spreadsheets, supercharge your efficiency, and boost your
confidence in Excel. Now you don't need to
be an Excel master to follow along with my tips. This is aimed at
complete beginners. I break down each tip one by one with real
world examples. By the end of this class, you'll not only save time but also feel empowered to tackle
even the most complex Excel tasks with ease. So grab a coffee,
fire up your Excel, and let's dive into my top ten tips to supercharge
your productivity. I'll see you in
the first lesson.
2. Lesson 1 - Formatting Tables the Right Way: So the first tip I'm wanting
to show you is how we use something called
format as table. Effectively, what this does
is it takes a table of data and it formats it in
what Excel calls as a table. The benefit of doing
this is instead of just running reports of a series of rows and
columns with your data in, it actually effectively
ring fences your data and you can name
that as a table itself, which means if you go to update it or add to it in the future, it's easy to change things such as pivot tables that
are running off that. Let me show you how that works. In front of you here, you'll see there's an
example set of data. This is example
headcount data from a fictional company that
has employees' names, date of birth, start date, job titles, and annual
salary as an example. So to put this
data into a table, all you need to do is select a cell somewhere in the
table, select select here. If you then hold down Control A, now what that will do is
that we'll select all of the rows and columns
of our data. Then if we go to the
ribbon at the top, you'll see there's an option
that says format as table. If you select that,
you'll see that there's various different colors and combinations of
table you can pick. Now, they all do
exactly the same thing. We just need to pick
one that we like. I'm going to go for this
orange one in the middle. If I select that,
you'll see that it comes up this little
window says create table. Now, it simply just effectively has your columns
and rows highlighted there. Now, all you need to do is
select Okay and you'll now see that it has formatted
our data into a table. If I zoom out slightly, you'll see you've got this
orange bar along the top, and there's this orange
rows and columns. Now to make this bit clearer, if I go to view top and
I untie grid lines, you'll see that the table's
bit clearer to see. Now, what are the
benefits of doing this? Well, the benefits are
that if we go to add additional rows or
columns to our data, it will automatically
expand that table. As an example, if I go
to the bottom here, and I type in another
employee number. Let's say it's 116, seven, you'll see that it will automatically populate
a new row of our table. This would be the same if
I added another column. If I wanted to say comments
on here as an example, I type in comments,
and you'll see that it would automatically
expand that table. Now when I go on to
the next part of this lesson and show you how
to create a pivot table, this is beneficial
because if you go to add additional data, let's say you do another
download of employee data and there's new rows and you copy it into your Excel table. By having it
formatted as a table, all you need to do is refresh your pivot table and it will automatically pull
through the new data. If you didn't have that, you would have to go
into the pivot table and add new rows or columns
if they've been added in. A top tip that I always have is when you're working with
data tables in Excel, is always format it as a table. Remember, when you
have your table, select all the data and then on the top of the ribbon
under the style section, there's an option
to formats table, select that and put
it into a table. Now, one last thing you can do, and this is handy if you go
on to manipulate your data in a power query or you
go to use it to build Power BI reports is actually to name the table so that
we can easily find it. Now, to do that, all
you need to do is select anywhere in
your data table. And at the top under the
formulas ribbon here, you'll see that in the middle, there's an option that
says name manager. Now if you select this, you will now see when
the window opens, it only has one table in here at the moment. It's
just says table one. And you'll see it
as a sheet one, and it's got your rows
and columns highlighted. Now what I'd want to do is actually rename this so
it's something that's a bit more obvious because if
you're querying this data, you want to easily
be able to find it. You don't want to know, my data is table one or table two, you want to call this something
else like headcount data. Easy change. All you need to
do is have it highlighted. Go to Edit and where
it has the name, just type in there headcount. You don't want any spaces,
but underscore data. If I select Okay, this table is now known as headcount
underscore data. I won't be covering it in
this lesson in particular, but if you go on to
build Power queries or Power BI reports and you
need to access your data, when you link those programs
to this Excel table, it would easily come up and say, this table is formatted
as headcount data. Again, if you go to
update that data, add new rows or columns, it will automatically pull through all within
that single table. This is a very good
top tip number one, I'd like to show you.
3. Lesson 2 - Pivot Tables: So now that we have our
data formatted as a table, my next top tip,
I'm going to show you is how you make
a pivot table. And a pivot table is a great way for summarizing data
that's in a table. So where you have
lots of rows and lots of columns and you
want to consolidate it down into an easy
to view snippet, pivot table is the way to go. Now, one thing I'm
quickly going to do just so we can easily
read the columns of our table here is I'm just going to format
them so they're nicely spaced and you can read each of the column headers. To easily do that
is a good trick. If you go to top left and
you select this arrow here at the top left that selects all of the rows
and all the columns. If you just pick one of
the columns and you just double click on the
line between the two, you'll see that they all snap to a point when they're evenly spliced to effectively what is the largest width
item in there. Sows easily read it. So top tip. Now to make a pivot
table, normally, if you had unformatted
data table, you would have to effectively
select all the data, but because this is formatted as a table itself, we
don't need to do that. We can just select
anywhere on the table. And then what we do is up
the top on the ribbon, we go to Insert, and
on the far left, you should see an option
that says pivot table. If you select this option, now there's a window that's
come from a different screen. If I just pull that down,
you'll see that we have this box opens here, it says, select a table or range, and you can see here it doesn't say like it did before
the rows and columns. It actually says our table name, headcount underscore data, and it gives you two options
to either open it in a new worksheet or open it
on an existing worksheet. If you select this one here, it says existing Worksheet. It allows you to select where you want the pivot table to go. So as example, if I wanted it on this current sheet in column P, I can select a cell there and that's where I insert
the pivot table. But for this example, I want to create it on a new worksheet. So if I select
that, select Okay. You'll see now it'll create
a new sheet number two, and then I'll have
this little window here and this option on
the right hand side, it says pivot table Filds. Now, this is where you
can simply drag and drop the columns that you want to display in
your pivot table. So let's go back
to the table and pick a view we
might want to see. So first off, let's
say we wanted to see by department name the
total salary costs. Now, as an example, if I wanted to say view manufacturing's
total annual salary, I would have to go
on department name. I would have to click the drop
down for manufacturing and then sum the whole
column and you can see there, it says 980,000. If I go to my pivot table, what I can do is I can select the options for
department name and annual salary and see all of
them summarized together. If I go to the Sheet two here, click on the pivot table
option and all I need to do is then take
what I have here, department name and it's
very simple to do this. You can effectively highlight the name and drag it to
the field that you want. If I take it down here and
I'm going to put it in rows, and I'll tell you why I'm
going to put it in rows because you'll see now
in our pivot table, if I make it slightly larger
and select back into it, you can see I've got my rows here are all department names. Now the value I want
is the annual salary, and the other options
I can put this in are values, columns or filters. Because I'm wanting
to sum the values, I want to put annual
salary in the values box. If I take annual salary, click on that and I can
drag and drop it to values, you'll now see, I have the total annual
salary by department. I I highlight column B and I
can format this in currency. I'll drop it down a couple
of some more places, re select and here, you'll see, I'm now easily summarized by department name, the
total annual salary. You'll see here
manufacturing 980,000. The great thing about pivot
table is effectively, we can now cut and slice
this into a different way. If I go back to the sheet, let's say I wanted
to view the split of total salary by
department, by gender. If I go back to the pivot table, what I can do is I can actually then put in
my columns, gender. If I go to gender and put this
into columns, you now see, I have a table and the total
value is still the same, but it splits it between male, female, and the department name. Again, another powerful
tool using the pivot table. Now let's just cover off this
last field, the filters. This is where we can
effectively change the whole of the pivot table to be
filtered to what we select. Again, if we go back
to my sheet here, now we can probably
pick a column. Let's go for employee group,
permanent, fixed term. They're the only two
options we have in there. If I go back to sheet two, and I will go to employee group and I'll put this in filters. Now, if I wanted to
view everyone who is fixed term or actually, I've got contractor in
as well, permanent. If I want to view my dataset filtered just by one of these,
I can easily select it. Now, to bring up the
multiple selection box, just need to tick this box here, it says select multiple items. Now if I untick all and I
just tick contractor and O, you'll see it now filters all of my data to those who are
class as a contractor and it shows me that
it's only males because female is now gone and it's
got the department name. If I click on fixed
term, untick contractor, you'll see now it
gives me those who are just fixed term,
male or female. If I wanted to switch this around and actually
put the gender as the filter and then
the employee group in columns, that's
easy enough to do. Let me just take
this off so you can see the total there's 2 million. If I just take gender on the
right hand side from columns and stick down to filters and
drag employee group across, you'll now see, I've
got it split by department name and
employee group with gender. Then I can simply filter, again, click select multiple
items option. Who is female? And who is Maya? As I said, pivot tables
are a really great way to display your data when you have lots of it and
you want to summarize it. Now, you may remember in the
first part of this lesson, I was saying about how you
want to put your data into a data table because
if you go to add additional rows or columns, it will automatically pull
that through for you. Now, let me show
you how that works. If I wanted to let's create
a fake new employee and I'm going to give them a new
department name and a role. If I go back to
sheet one here and I take my filter off that I had, if I go to data, I can actually go to clear and clear
all my filters. If I create a new
employee and let's call them number 10167. I won't worry about a
name for the moment, but I'll put a job title in, I'll call them a manager
and employee group, I will put them as permanent. I will say they are male
and department name. I'm going to call it.
Let's call it something. Let's call it head
office as an example. 40 hours a week and
they can be on 60 K. We just put in there.
Don't need to fill in all the rows because we're not showing all of those
in the pivot table. But now what I've
done is I've added a new field here and if I go back to my pivot
table on sheet two, if I now right click
this and hit refresh, you'll now see at
the bottom there, head office has appeared. Head office, permanent role, 60,000 a year and if I filterus on mail because
I put them down as mail, you'll see they stay there. And that is the key
thing about having your data formatted
as a data table. Imagine you work in
a company and you're responsible for doing
payroll as an example. You have to download your payroll data from
the system you use. This could be changing weekly
or even sometimes daily. And you want all your reports
that you might build off your Excel spreadsheet just
to automatically update. You don't want to be
changing each graph or table or pivot table you might have for the new rows and
columns that come through. So by having this
formatted as a table, you can add as many as you want, rows or columns, and it will automate pull through to anything
you have running from it. So that's my second top tip
is how to use pivot tables and why to use them and how beneficial they
are to your reporting.
4. Lesson 3 - Lookup Tables: Now my next top tip
I want to cover off is how you can use something called a lookup table and combine that with
a great formula in Excel called V lookup. This is what I find a really useful skill to know to how you can
transform the way you work and it allows you to build tables and documents
that can easily be updated and quickly
refined and edited. What I'm wanting to
do is I'm wanting to add new column in
to my table here. That I'm going to call bandin
which will give me a band. Based on the
individual job title, it will tell me what
band in they are, and I'm going to call
them either band A, band B, band C,
band B, et cetera. As I said, this is going
to be based off job title. This is going to be a variable, the job title drives
what the band is. Now to do this, I
will create something called a lookup table. Sometimes when I
build my reports, I have these on separate tabs, but for Es, I'm going to put this onto the same table here. Now, let me show you how
I'm going to do this. Firstly, I want to take
all of the job titles here and insert them into a
column as a separate table, and then to the right
of it, I'm going to give each job title a band. Now there's different
ways you can do this. You could go down and
individually pick out each different job title
and copy them across. But a quick way you can do
this is if you highlight the whole column and control C to copy it.
Let's go over here. If I just paste these
as values for now, if I then go to data at the top, and there's an option
that I have here, which will say effectively
remove duplicate values. It's this one here I know this is hidden the way
this is summarized it down, but there's an option here under DataTols called
remove duplicates. If I select this and
just click Okay, it will now remove all
of my duplicate values. There you go. What
I'm going to do, I'm just going to simply
centralize this here. I'm now going to
put to the right another column I'm going
to call this band. There you go. Let me just
centralize this now. Again, like I did earlier where I formatted this as a table, I want to format this as a
table and call it job band. I highlight it there.
Go format is table. Let me just put it down as
a different color, selecto. Now actually this
is interesting. You can see what it's done is, it's shifted down my rows here, the top row and it's just called this column one and column two, which isn't what
I want it to be. I want this to be
job title on band. Let me just shift
this up Control C, override that, and I'll remove
security as the bottom. Now, I want to give each
of these their own band. Manager, I'm just going to call, let's call it band A,
quality manager band B, quality control can be a band, C, can a team leader, supervisor can be a D grade. Logistics E, technician E, admin E, Office E, and security, I'll
put them as D again. I've just assigned randomly
these different bands, and I want these to be looked
up into this table here. Now, the best way I can do
that is via lookup formula. Now to do that, I'm going
to create a new column here and I'm going
to call this band. I call it band in actually. I like column N. Let me
just centralize this, expand it slightly.F click. And what I want to do is I want to select the
first available cell. Now, I want to
install say install. I want to write here
the Vu formula. There's two ways you
can do it. I can write it manually by code, and if you do this a lot, you've become very
proficient with it, or you can use the in built
formula bar in Excel. This Insert function that's
here, it's at the top. And I'm going to use
this to do my V lookup. So if I select this
function button here, you might have it
near the top as a formula that's used often
or I can type it in here. I'm just going to simply
type in, I delete this. I'm going to simply type
in V L up my Muster. If I select go, you'll see it says Select function
V L. I double click on this. What this does is it brings
up a function box for you to effectively type
in your formula. This is an easy way to do
it because this is a step by step guide on how
to build the formula. Firstly, I want to look up a value and the value
I want to look up is in column G. I'm going
to select cell G two. And you'll see here because I formatted my table
as a data table, instead of saying cell G two, it says here in square
brackets at job title. I can leave it like that and I will leave it like
that for this purpose, or you can, if you
want to actually put in the column
reference G two. Now, table array, I want to select the table that I'm
going to have my look up in. Now you need to select it
from a column perspective, and I need to select
both columns, job title and band, so it's column R and S. And tip here, if you want
to lock these columns. For example, if I inserted new
columns before this and it shifted it across with
the F four function, if I select on RS, you'll see you'll see here the little
pound signs come up, and what that does is that locks the columns in this table to column R and column
S so they won't move. Column index, this is
effectively how many columns to the right from the column I'm looking up in in the table I want
the output to be. So for example,
I've got column R here is the first column,
that's column one. Column S is column two. This band column
S is column two. I want to put in
column number two. If I had a data in column T
and I want to look at that, I would need to
put in here three, but I would also need to
move the table array to be R to T. Range look. Now I can say 99% of the times you just want
to put in here a zero. Effectively, it
just tells you what the output is going to be
whether it's true or false, and if it looks at the value. Now if I click Okay,
you'll now see that what's happened is all of this band columns have now
been populated and you can see they change based on
the job title here. Based on this lookup table. Now again, the great benefit of using a lookup table
like this is number one, if I put more data in below, this lookup will
just continue and it will continue to look
up based on this table, you don't have to keep manually going in and changing these. But also number
two, if I want to the change the banding let me just give it another letters like let's call manufacturing, sorry, let me call supervisor. They can be an R as an example. You can see that
the R just changes here automatically if I
say, let's call it a Q, that changes to Q. I want
to put it back to say E, I want to E or D, whatever, it changes it automatically, and that is the benefit
of having a lookup table. So again, here's my top tip
when you're working with lots of data and you
download a dataset, and then you want to
either manipulate it or add to it
and you want that to be repeatable going forward when you update
it for new data, always use a lookup table and the V lookup
formula together. It's a great and
powerful combination for you to use and learn.
5. Lesson 4 - SUMIFS: So my next top tip I want
to show you is how you use a SummiF formula and more
specifically a SumFS formula. It's an incredibly powerful
formula that I use daily in my work life
when I'm using Excel. The key difference
between a SummiF if you use a four and a
Sumi S is you can add more variables
into it and you can keep building the variables
to look up a value from. Easiest way is let me
show you how it works. So what I'm going to do is I
am going to as an example, I'm just going to use the
data we've been using so far. I'm wanting to look up the total annual salary for
the different departments. So what I want to do
is I'm going to put all the department
names into a column, and I'm going to use a Saif
to look up a value from it. So again, like we
did previously, I'm going to highlight
a whole column. Control C, go to a column
over here, taste as values, go to the data tab to remove
duplicates. Move duplicates. You see here I've now got all of my department
names summarized here. Let me just take a couple
of these blank columns out so it moves it across. Now what I'm wanting
to do is include a column here that
says total salary. Now you might be
thinking to yourself, why are you doing
this and why are you not using a pivot table? Because I could easily
just pivot this data and have it by department
name and total salary, like I showed you in
lesson number two. But I'm just using
my data and this has a purpose to show you the
SumifS and how it works. The Sumi formula is great for if you're
looking up data between two separate documents or if you're wanting to build reports where variables
constantly change. I'm just using my data to
show you how this works. So now I want to
effectively include here total salary
that's going to look up and sum column L to salary
data for department name. Now what I'm going to do is
use the formula bar again. I select the formula
bar. I'm going to type in the formula I want. I want sum I click Go. The reason I'm doing that
is because you'll see here, you've got two values, you've got Sumi and sum
if S. You can see here sum adds the cell specified by a given
condition or criteria. Sum if S adds a cell specified by a given set of
conditions or criteria. I would always use
in every scenario a suis over a Sumi
because if you want to, you can always add more
variables if you need to. So I'm just going to click Okay. This is a really
simple formula to use. All you need to do some range, highlight this column you
want to sum, column L. Criteria range, I
want department name. The criteria, the department
name I'm looking up. So here, administration
first. Click Okay. And you can see, now
what it's done if I centralize it and just
format it into currency. It's now summing
up everything in column L for the department
name administration. Again, this is very much like what we did with
the pivot table. But if I filter administration, you see the total there comes to 110,000 and have 110,000 there. I just take off the filters. If I then drag
this formula down, you'll see it summed up the
total of all of the areas. The total there is 2,012,500, which is I highlight the
column L is exactly the same. This is a really powerful
tool to use the sum. Now, where I was saying earlier, the benefit of using a suf is that it allows you to
put more variables in. If I then wanted to say, well, how many by department name
are male as an example. If I then include a column
in between these two here, and I put in here gender, and I say, I just want
each of these to be male. Spell that right. Male.
And I'll drag this down. If I now click into my formula cell and I go
back into this formula, so I can click this
FX button at the top. If I go back into here and I
go to the bottom criteria, you see it now opens
up another option which has criteria range two. So this is for my
second criteria. So now what I want to do is
select criteria range two, which is gender column E, select Column E. Criteria
two is now going to be male. So if I click Okay, again, if I drag this down
to the bottom, you'll see it's now updated
all of the formulas. The total is now 1.1492500. If I fill to gender
just by male, you'll see the total
annual salary 1,492,500. Again, let me just take the
filter of all of these. Again, you can see,
I've added a criteria. If I want to add another
criteria into this and I wanted the employee group,
employee group. I just wanted to know all of the fixed term heads in
each of these areas. Copy fixed term
there. Let me let me take that off and paste
as values that's neat. If I just move to
the right a bit. Again, if I want to add another variable one to
here, I'm good to FX. I can go to criteria
two at the bottom here. You'll see actually it
won't allow me to add any more onto this screen here. There we go. Let me just
scroll down. There you go. Criteria free I want to
have the fixed term, so I want column I highlight
column I and criteria free, I want to say fixed term there. Again, if I click Okay, now, see this number has dramatically dropped only down to 102,500. Because if I select everyone
who is fixed term and male, total salary is 102,500. So you can see that's
how a sumIF works, and it's really
useful for if you're looking up to various
different Excel tables, you want to quickly summarize your table where you don't
want to use a pivot table. As I said, you could get
this exact same result if you're using this
data on a pivot table, and I would always propose
use that as a solution, but this is a great way
to show you how the sum IF works and how
you should always use a Sumi S formula
just because you can keep adding more
criteria when needed.
6. Lesson 5 - Concatenate: Now my next top tip I'm
wanting to show you is how you use a formula
called concatenate. And what this allows you to do is join two different strings of data or more than two
different strings of data together to
create a new data set. This is incredibly beneficial
if you're looking to create a unique value in a dataset to
perform a look up on, or you're wanting to make your life easier if you're doing some analysis
or reporting. For example, what
I'm going to do to demonstrate this
to you is take someone's first
name and last name and combine it together
into their full name. For example, first individual
here, Luke Spencer. If I wanted their forename, I want Luke Space Spencer. Now, it's quite a tedious
task if you to go manually down and either type it in
or copy and paste it across. So I want a simple format and this is where concatenate works. In column N here, I'm
just going to type in the heading fname. And what I'm going to do is, as we've done with
our other functions. I'm going to go to the
FX function up here. I'm going to type in Cat. You don't need to
spell the whole word, but here we are, comes
up with concatenate. Again, this is another really
simple function to use. All you have to do
is keep selecting the text boxes that you
want. So I want to go here. I want to pick first name, text one, text two, last name. Now, this may seem the
obvious thing to do, but you can see it gives you this little preview here
and what it's done is it's given me Luke Spencer but with no space in the middle. It's combined it to be one word. But I want a space between
first name and last name. To do this, what I want text two to be is
effectively just a space. Now, I can insert that by simply putting in here
using the speech marks, space, and then
another speech mark. And then for text free,
select last name. Now you can see in
the preview here, it's got Luke Spencer for space. Now, if I click Okay, again, great benefit of this being
formatted as a table. It runs a formula all the way
down to the bottom for me. What you can see is it now has everyone's
full name included. There you go. Now, again, you can keep adding to
this as much as you want. If you said, I'd also like to have the employee
number at the end. But instead of it
just being a space, I want space, hyphen
space employee number. Again, that's easy to do. Let's click on the
first cell again, go to the FX button,
select this. Then where I've got text four, I want that to be a member because we're not picking
a value, we want a space, I want the apostrophe,
space, hyphen space, then speech marking
then text five, I want this to be
the employee number. Again, click Okay. Now we have the individual's full name, hyphen, and then their
employee number. Mm if you're building
a report and you want everyone's full names in there or you want
their full name, department added or
something like that, you can easily concatenate your data so it
gives you this view. Think how much time that saves you versus you having
to do it manually. Again, another top
tip that I'd like to show you you can use in
your daily work life.
7. Lesson 6 - F4 Function: The next tip I'd like to show
you is a really quick one, and it's something I
do find really useful, and it's using the F
four function key. And what that does is it
repeats your last action. So for example, if I say I had a random cell and I wanted to highlight it and
I highlighted it yellow. If I then selected another
cell and pressed F four, you see highlights yellow. If I do it over here over here, it effectively remembers
your last action. And this is really useful. So if you have a series of data, so let's say we
have some numbers here, down, one, two, three, and then let's say I want
to pick out some numbers, I would select that
as yellow first, and then if I wanted to
pick this number, F F four. It's a really quick
way to remember your last step without say, in this instance, going up to color fill and back down again. But it can be used in any
other action that you make. So for example, let's say, I have the column here
and I make this column width slightly larger and I
want column L to be the same. I can literally highlight
column L and press F four, and it remembers that action. If I specified the width
in numerical terms, it would remember
that. Same with rows. And then I highlighted this
other one press F four. I remember the last action. I do. So it's a really
useful feature to know. And I said, it's just handy when you're doing something
that's repeatable and you're having to select various different elements and you want to effectively do the
last transaction you did, then yeah, F four,
it's great to know.
8. Lesson 7 - Conditional Formatting: Now another top tip that
I find incredibly useful and use all the time is something called
conditional formatting. And what that
allows you to do is effectively apply
certain conditions to your data and it will visually show you what
they are in color format. Let me show you what
I mean. For example, I have a column
here in column E, which is gender,
male and female, and I want to make it easy for that to
stand out what it is. So I want male to be one color and female
to be another color. Now, this is really
easy to do use an in built formula on format they have in Excel
called conditional formatting, which is up here on the ribbon. Now if I highlight column E, gender, the whole column there, and I go to conditional
formatting, you can see here there are various different
options that I have. But the one I'm going to
pick and show at the moment is one here that says equal two. If I select that, you can see it comes
up this option says Format cells are equal to, and it says with
light red fill with dark red text there's some
preset color options in here, or you can actually do
your own custom format. But for now, I'm just going to go with the preset options. For example, if
this equals male, you can see immediately it
highlights everything that's male with light red
fill with dark text. I click Okay. If I then want female to
be a different color, keep in column E highlighted, conditional formatting
highlight cells, equal to. If I type in the female you can see it immediately
turned them red. But if I want to be
another color like yellow, you can see, highlights
down and Okay. Now this is great
because it allows you to when you're visually
just looking at the data, you can it glance at it and go, Oh, yeah, it's female,
female, female. It really allows you to
quickly see what's going on. And this works for any
data you tell it to. So for example, in
the employee group, if I want to just clearly
see while scanning through the dataset who was
fixed term, again, highlight cells equal to, and I can type in
there fixed term, you see highlights hid in red. But as you notice, when I selected conditional formatting, there's also different
options there. For example, greater than, less than between. Let's
look at one of these. With salary, if I
wanted to highlight who earns more than
40,000 easily. Again, I can highlight column L, go to conditional formatting, highlight sales and I want
the greater than option. If I select that, funny enough, it already
has it in there. Highlight cells
which are greater than 40,000 and you can see that it's highlighted all of those amounts which are
greater than 40,000. I can also do the opposite and highlight any amounts which are less than 30,000. I go there and type
in less than 30,000, and I want them highlighted
in green as an example. You can see it highlights
this in green. Now, you can play
around with these because there's various
different options on there, but they all do exactly
the same thing. You can also then remove any conditional formatting
you might have. So for example, if
I want to take the conditional formatting
off this column and go to conditional
formatting, I can clear rules either
from selected cells, entire sheet or this table. I just want to take
it off the column, if I just go selected cells,
you can see it clears it. If I want to take
all conditional formatting off this whole sheet, I can go conditional
formatting, clear rules. From entire sheet and
there, it clears them all. Really powerful inbuilt function of Excel conditional
formatting and I use it all the
time and hopefully it works for you in
what you use Excel for.
9. Lesson 8 - IF Function: Now in this lesson, what
I'm going to show you is another great formula to learn
and that's the I function. Now, to show you this,
we're going to go back into the data we
were using earlier in these lessons and to
demonstrate the formula, what I'm going to do
is insert a column in column N and have a condition
on our salary data here. Now, the conditions
going to be if the salary is greater than
35,000, it will say yes. If it's under 35,000 or
35,000, it will say no. Let's go into the
data and I'll show you how we use the I function. In column N, what I'm going to do is I'll type something
like salary benchmark. Expand column N here and I'll
just centralize the data. Now, I'm going to insert a
formula here, the I formula. As I said, if the
annual salary in column L is over
35,000, it was a yes. If it's 35,000 under,
it will say no. To do that, as
we've done before, select the cell we want
to put the formula into. Go over to the formula,
the insert function. You'll see here,
I've already got the I formula at the top, but if you need to search
it, you can just type in I into the function
bar, you go go. You'll have various
different options there, but we're just going to use
this one it says I function. Then you can see the function arguments
box opens up like we've had before and it's really simple to
write this formula. All we need to do is say what we want the conditions to be. Then if the conditions
are met and they're true, what we want the output to be, if they're not, what we
want that output to be. Starting on the first one,
what is the condition? Well we want it to
be that if column L in the annual salary
is over 35,000, we want to have yes, if
not, we want it to be no. So all we need to do is
select cell first cell. You can see it comes up there.
Then this is where we need to use the notation for
greater than or less than. These are the two left or right facing arrow brackets
on your keyboard. The annual salary
is greater than, you can see here we have
the greater than sign. 35,000. All we need to
do is put that in there. Then we go to the next
box. I want to say yes. Now because this is text, we need to put this
in the speech marks. If I open speech marks, type in yes, close
the speech marks. If it's force,
open speech marks, no, close speech marks. Then all I need to
do is select yes. Now you can clearly
see what it's done is where we've got the
salary over 35,000, it's given us a yes, where it's 35,000 or under,
it's given us a no. You can see here we have some of the salaries over 35,000 here. Now what we can do is actually to make this a bit
more visible and clearer, is we can use what we learned in the last lesson on
conditional formatting. If it's yes, I want
it to be green, if it's no, I want it to be red. Again, to do that, I just need to highlight
the whole column. I go to conditional formatting, highlight cell rules,
and then here equal to. I type in yes. You see it already comes with red,
but I want it to be green. If it's no, highlight
cells equal to, no red. As I said previously,
if you want, you can do any other color you want on the custom
format cell here. It opens up your options to select different font or fill, et cetera, but I'm just
going to use the preset red. Again, it makes it really clearly visible
to see your data. Now we can make this
bit more dynamic. Whereas if we wanted
to easily change what the benchmark is where in
this formula, it says 35,000. If I said I want to
change this to 30. Now you can just go into the
formula and change it there. Let's say this was part
of a larger formula or someone who doesn't regular use your spreadsheet
would open it up. You want a simple box
where you can have the variable in very much like what we did in the earlier lesson
in the lookup table. Now, to do that, very simple. What you need to do is let's put the value in a cell somewhere
so we call it 30,000. Let me centralize
that. I'm going to format that as a currency. I'm going to give
it a nice title, call it salary
benchmark variable. And for the purpose of this, I'm just going to put
it in bold and then highlight it in yellow
so we can easily see it. Now, all we need to do is
instead of having 35,000 here, we just need to change this so the variable
is cell co quarter. It's very easy to do. All you need to do is go into
the formula bar. We can actually now
just delete the 35,000. We can select cell odquar two. Then what we want to do is we
actually want to lock this. This is where we want to use the FN function where it
comes up the dollar signs. It has $1 in front of the Q
and $1 in front of the two. That means it's locked
on seco quarter. If I now press Enter, you can see our formula
has now changed. Every salary that's 30,000 or over, it's now shown as yes. Now, if I just put
in here a low value, call it 100 pounds, you'll see that everything
now goes to yes. I can change this to be 50,000. You can see that I've only
got a few yeses down here. Again, hopefully this
really shows you how powerful an if function is. You can expand this further. I won't do it now in the lesson, but what you can do
is you can embed further if functions
in an if function. Effectively, you can say
if a condition is met, put yes and instead
of it being no, you can then have another if
function on something else. You can keep
embedding I functions and one another to
make it quite complex, but I would say it's really
powerful for your day to day work to use an I function for the purpose of
something like this. If you're reviewing data
that you copy and paste in, you can have the I
function running and it easily allows you to see
the output of your data, particularly when it's combined with conditional formatting. Let's move on to
the next lesson.
10. Lesson 9 - Left, Mid & Right Functions: Now something that I want to
show you now is formula I love and it's a really
powerful one and actually it splits down into
three separate ones, and it's the left, mid and right function. If I just write down what
they are here functions, it's the left, mid
and right functions. Now, what these are
used for is effectively taking a set number of characters from a
cell either on the left, in the middle or the right. Now to show you how this works, I just have some example
part numbers here. Is completely made up,
but this is useful to show you how
this formula works. Now, let's say you in a
business or in your daily life, you deal with part
numbers like these, or another example where you
could have something with multiple text and number fields and they may mean
different things. For example, here, you can see the first three characters are the same down to row seven, ABC, then eight to 11, it's DEF. Then you can see in the middle, it says AB, AB, AB, then it goes BC, B, C, then DD and at the end, there's random
numbers and letters. Now, it could be a case that the first
three letters here, for example, mean the location. The middle two characters
mean the part type, and then the last seven
characters mean the part ID. For example, let's say
then we have location, we have part type and
then part ID, let's say. Now, I want to be able to extract those different bits of information via a formula. Because then one of the things
we discussed earlier on in this lesson series was how
we can have a lookup table. Let's say I extract
the location, ABC or DEF from here, I can then have this looking
up to a different table, which then tells me the
postal address as an example, or the part type can be
the group in, et cetera. So this is really powerful way. Again, if say you
extract data from an ERP system or you download
it or sent the data, you could just copy and paste
that data across and then these formulas will act to extract the data that you want. Let me show how they're used. Location. Column
B, I want to take the first three characters from the beginning
here and for this, I want to use the left function. Now the reason I want to use
the left function is because these characters on
the left hand side and I want to take a set
number of characters in. To do this, select
cell going to B here, got to the insert
function option. Then let me just type in
here left and I'll go go. First one that comes up says
left, double click on this. It is really simple
function to use. All I need to do
is select the text left and then the
number of characters. I want the first free, so
I'll just type in free. You can see it gives me the
little preview here, ABC. Click Okay. Because this
isn't formatted as table, I'm just going to
drag this down. Now you can see here, I've just extracted ABC ABC and DEF down to the bottom here. For the part type, because this sits in the
middle of this part number, I need to use the mid function. Again, easy to use. Go to insert function,
search the function, type in mid Go and you can see the first
one, it comes up mid. Now, like we did
with the first one, I need to select the
text. Set text, two. Now, difference with mid is either now need to pick the
start number of characters, how far in I want it to start and then how many
characters I want it to take. If we count this in, you can see if I started from the left, I need to go one, two, three, four characters in. And then the number of characters
I want to take is two. Now, you'll see that
immediately what I've done here is I've said I only
needed to start four characters in,
four spaces in. But remember, this is the
number of characters in. You can see in the
little preview, it's actually got the hyphen A because it started at
the fourth character. I actually wanted to start the fifth character, which is A. If I then just
change this to five, now you can see it starts
at the fifth character, where I got this A, and it
takes two along A and B, and you see the preview is AB. That's all I need to do
so clo drag this down. Now you'll see the mid function. Takes the middle section here. Now, to make this easier
and the location, I'm just going to put
in here in brackets, left function, part type. I type in here mid function just so we know which
ones we're using, and then for D right function. If I just expand those columns. Now for the right one, this is going to
be very similar to the left function except it's
just going to say right. Now in this instance, let's just write the formula instead of
using the insert function. We can see that in
the left function, it simply equals
left open brackets. Select the text we want to have comma and then the
number of characters. Now, for the right function, instead of starting on
the left hand side, it's actually start on
the right hand side. It's going to start this side on the right hand side of the
text and work its way back. Now, the numbers
we want to take, for example, we use
this first option, you can see here there's
actually seven characters, seven X free Z 92. I want to go from the
right and seven along. In cell D two, I can
type equals, right. You see, it comes up
with the preview here. Open brackets,
select cell A two, and you can see it
gives me a little preview of what I need to input. I'm already on the
text function. If I click comma, it will take me to the number
of characters. Now it highlights
number of characters. I want it to be seven
from the right hand side, closed brackets, equals done. And scroll this down. Now, you can see it takes then the part ID I want from these
part numbers automatically. So you can see now
it's perfectly worked. Using the left, mid
and right functions, it's extracted the information I want from these part numbers, and if these were to change
for whatever reason, the formula would
automatically update. So just as an example, if I at the bottom one here, copy that, and I'll
paste it at the top. You can see these
automatically update. If you're using downloaded data, you could have hundreds,
even thousands of rows. You could just copy it in here. This will extract the
information you want. Then as I said, if we combine it with formula from earlier
with a lookup function, if I had on the right
here location and I said that ABC and DEF, let say that the location ABC, country is USA and DEF
is UK as an example. If I then wanted to insert that, all I can do is simply
I would go country. Then what we learned
earlier with the V lookup, I go insert function. Here's the V look up already. I can double click on that.
All I need to do is say, Well, I want to
look up this value. Table is my location
and country. Column two, because I want to go to the
second column along. I want it to be a perfect
match on zero. Click Okay. Immediately, you can see
how combining what we learned earlier with
the V lookup and the lookup table and the left
mid and right functions, we can really expand our data. Now, you would want to formalize this and
make things neater and you could have
this on a separate tab and in a nice table format. But this is a quick way
to show you how what I've covered in these
lessons so far, we can then combine together to create really
powerful spreadsheets. Let's move on to
my next top tip.
11. Lesson 10 - Customise Toolbar: Now my top tip I'm going to show you now isn't necessarily a formula or function
or handling data. It's actually just to do
with how you utilize Excel, something I found has really made my Excel use more efficient my daily life is customizing the quick access
tool bar and it's this section along the
top here, this green bar. Now you may have noticed I have some popular commands up here. Now, what this
allows you to do is effectively the key commands that you use are hidden
away in the ribbon. For example, under insert, we've got the pivot table here. I use this quite often. Instead of selecting data, then go and insert pivot data. I have it already
at the top here and using the filters and the
clear all filters function, which you may have
seen me use earlier, if I've got a load of data
and there's a filter on there and I want to just not clear filter in a single
column of all filters, I have the option to
do it at the top here. Can put any of the commands within the ribbon up to your Quick Access toolbar. Now let me show you
how you do that. When you're in the
ribbon at the top, if you just right click somewhere and go
customize the ribbon, it will now bring up this box. Now, there's various
different options you can do to customize the
ribbon and change settings. But what I'm going to do
is go to this one here, it says Quick Access Toolbar. Now you'll see you've
got a box on the left, which is currently filtered
on all the popular commands, and here you can actually filter all the commands but let's go back to popular command to show
you how these work. On the right hand side, is the quick axis
tool bat as it is. You can see here on my screen, I've got the save and
I've got the undo redo. I've got the automatic manual
calculator options here. I've got a macro
that I've already specified and macros I can cover in a complete
separate different lesson. Then also, I've got
my apply filters and clear filters hit. Now it's really simple
to update this. All you need to do is
select what you want. You click Add, and
then it appears. For example, let me select something that I
use quite often, which is the format painter. If I select that, I simply
go add, it copies it across. You can see it hasn't
appeared yet at the top. If I click Okay,
Wila there we go. It appears the format
painter is now there. Now to go back to this,
right click on the ribbon, customize the ribbon,
quick access toolbar. If I said, actually, I don't want that
anymore, I want something else such
as paste special. You select that format painter, remove, select what you
might want, add Okay. It appears now
they're pay special. This is really simple skill, but it's one of the things
I find does help me in my day to day Excel life. Particularly the key ones
I use as head filters and clear filters because if you're
anywhere on a big table, you can literally just
click it straight away and it's done for you. You don't have to go up to
the ribbon and then find a filter because when you're using a lot of
different commands, all that time does
add up eventually. I hope you find that
one very useful because it's something I use all the
time and once it's up there, it's hard to do without it.
Blake Charles, Chartered Accountant
