Transcripts
1. Introduction: Welcome to this class on
Excel Tips and Tricks. My name is Julian, and I've been working with and teaching XL over the past ten years
as a digital marketer. Exile is an incredibly
powerful piece of software, but often people aren't aware of many of its features that could save them time and make their data analysis stronger. For this class, I've picked
out what I think are some of the best features in Excel that are easiest to learn, quick to use and very powerful. The class is designed
for students who already have
a beginner level of experience using Excel and want to expand
their skill set. If you're completely
new to Excel, I'd recommend my Excel for
beginners class instead. In this class we'll cover Flash Fill and how it can
automate manual tasks. The Goalseek feature,
conditional formatting options. Best practice approaches to
creating and formatting data. And more. Lessons have been designed so you can
watch them in any order. Meaning you can skip
past any lessons that cover topics you
already comfortable with. Once you finish the class, you'll be able to
test what you've learned with the class project. Thanks for enrolling in this
class and let's get started.
2. Automate Manual Tasks with Flash Fill: In this lesson,
we're going to take a look at the Flash
Fill feature. Now what Flash Fill does is it senses a
pattern in the data and automatically fills
the remaining cells based on that pattern. We've got an example here where
we've got firstName is in column a and Person
second name in column D. And what we
want to do is populate all the cells in column
C with the full name, the first name, plus
the second name. Now there's a few ways in which we can get Flash Fill to work. But first off, we
just need to write in the first one. So Amy Larson. Then one of the quickest
ways which will often work, we can just start typing the second
example, just like that. And you can see how Excel is populating all of the cells and the exact way
you want to do, I can just hit Enter and then all of our full
names repopulated. The other option, we don't even need to
type anything here. We can just come up
to the Fill menu in the Home tab of the ribbon
and select Flash Fill. Then again, XL is
populated all of those cells exactly as we
want them to be populated. Lastly, there's a keyboard
shortcut, Flash Fill, and it's Control
E. Just do Control E. And then all of ourselves and populate
it just like that. Next up we're going to look
at an example of Flash Fill. And we're actually going
to do the opposite to what we did in
the first example. Here we've got the full name
written out in column a. And what we wanna do is want to break out
the full name into the first second name in
columns B and C respectively. So what I'm gonna do is just
write out Amy and cell B2. And I'm going to move
cough to cell C2. And right, Lawson. Now of course
there's a few ways I can get the Flash Fill to work. I'm just going to go for
controlling the option here, so Control E. And then you can see all of our first names
have populated in column B, come across the column C and I'm gonna do Control Z again. And now we have it. All of our second names have
now populated down column C. We're going to look at one more example of how
you can use Flash Fill. This one is slightly different. This time we've got
the sales rep name and sales all in one column separated
by a comma and a space. And what we want to do is
put the sales rep name in column B and the sales
figure in column C. Again, all we have to do is just write out that first row. Then hit Enter and I'm gonna
do Control a and Control E. And then incidently you
can see how we populated all of these cells in
a really quick way. So we've got the data we want broken out into two
different columns now.
3. Goal Seek: Let Excel Do the Maths for You!: In this lesson, we're
gonna take a look at the Goal Seek feature in Excel. Now the best way to explain the Goalseek feature
and to show what's useful is to jump
into an example. So we've got a fictional
summer sales plan. Here. We're looking to sell 7 thousand units of
an item at a price of $40 with a 20% discount as it's a sale being
applied to that price. And what that means is, is that if we take the units to sell
times it by the price, but then we apply that
promotional discount of 20% to the $40 would actually be an
effective price of $32. If we get a revenue
outcome of $224 thousand. Now, let's say we've
got a boss that says, after they've taken
a look at this, it's not ambitious enough and
that we actually now have a revenue target
of $250 thousand. Now, there's a few
ways in which we could change this plan in order
to hit that target. For example, we could increase the number of units
we plan to sell. We could increase
the price or we could reduce the discount
that we're giving. Now, figuring out how much
we need to change things by is a case of
maths or guesswork. For example, we could say, well, how about if we
sold 70,300 units? And I enter that, then I can see that I've
taken that guess, but it's still not enough. This is where Goalseek comes in. Let's say we're going to
select this cell and we want this to end up
being 250 thousands. And we want to do that by increasing the
number of units sold. So what I can do is go to
the Data tab and select what-if analysis and then
Goalseek. What we have here. We have this set cell part here. So C6, I've already
selected that. Then I need to specify what I want that value to change too. I want it to be 250 thousand. So I'm hitting that target. Then we can select, and this is the
power of Goalseek, which cell we want to
change to make that happen. I'm going to select cell C3 with the units to sell
in. I select. Okay? Then you can see it does
the math select, okay? And we now know that we
need to sell 7,813 units. And we can see that
the revenue has been updated and we are now
hitting that target. So Excel has automatically done that mass and
calculated this for us. Can do another example. Let's say this time we want to change the price
of the product. I'm going to remain selected on this cell
here because this is the cell where we want to go from two hundred and
twenty four thousand, two hundred and fifty thousand. I'll go up to What-If
Analysis and Goal Seek. And again, I'm going
to put 250 thousand. Then by changing cell,
I accept this time, I'm going to select price.
I'm going to select. Okay. Then select Okay,
again, and you can see we've got $44.64. So we can see how, what we'd need to
change the price by if we want to hit our target. As you can see, Goal Seek is a really powerful
tool in Excel. It basically does the
math for you and it's a great thing to have in
your toolkit to save time.
4. How to Tidy up Your Data with Autofit: In this lesson,
we're going to do a run-through of how
you can format column widths and row heights so they automatically fit
the data that's in them. We've got some historical
weather data here, but as you can see, the formatting is really
messy and inconsistent. So some of our column headings
we can read location B, max temp, but others are concealed and two,
read what's in them. We have to click
on them and then look up in the formula bar. Some of the data itself is hiding away because
the column widths are too narrow and row heights are
also formatted inconsistently. So we have some row 17 where there's lots of empty
whitespace, for example. There's a couple of
ways I can fix this. The first one I can just do Control or Command a on a Mac. Go to the Format
menu in the Home tab and select Auto
fit column width. Then you'll see all of our column widths and our
auto fit it to the data. Then I can do format
auto fit row height. And now we have all of our rows formatted
in a nice tidy way. By just go back. Well, I
can also do is highlight all of the columns and
to apply the auto fit, what I can do is just
double-click on one of them. And then you'll see all of our columns are now auto fit it. And then I can highlight
all of the rows, double-click on one of them. And then again, all of our row heights are now
also fitted as well.
5. Best Practice with the AutoSum Keyboard Shortcut: In this lesson, we're
going to look at the AutoSum feature in Excel. And specifically
we're gonna look at the keyboard shortcut
for autosome. We've got some sales data here. Its sales by officers
Berlin, Edinburgh, Los Angeles, Manchester
person, Singapore offices. And we've got the
sales data for 2018 through to 2021, so four years. Now you'll notice the cells along here and these
cells down here or blank. And that's what we're gonna do, is populate these
cells with the totals. So the cells along
the bottom again contain the totals for all of the officers for
each individual year. And then the cells down the side here are going to contain
the total for all the years, but for one office at a time. And then this cell here, so f 11 will contain the
total for everything. There's a few ways you can
add up totals and Excel. Obviously, a really
slow way to do it is to just select one cell
at a time like this, B5, B6, B7, etcetera, which will take you
quite a lot of time. The next thing you can do is to manually write out equals sum. Then we can select the cells
that we want to add it up, hit Enter, and we get our
total just like that. It's worth being aware
of the AutoSum feature. So when we're in the cell
that we want our total, we could come up here and
select AutoSum and exhale will instantly populate
the sum function and select the relevant cells. We just have to hit enter
and we get our total again. But the purpose
of this lesson is to show the keyboard shortcut. For that we can do alt equals on Windows or Command
Shift T on Mac, which is quicker than grabbing the mouse and going up
to auto sum up here, hit Enter and we get
our total again. Now, what we can do to populate the remainder of these cells
is drag this formula along. And then we could do
AutoSum here again. Hit Enter and we can drag this down and we get
everything like that, which is a pretty quick, pretty efficient way to
add up all our totals. But there is actually
a quicker way in a situation like this. So Excel is really good at understanding what you want to do and
looking at your data. So here we've got a set of data where all of our
values ahead and we've got data we've
clearly labeled and column headings and
clearly labeled rows. So what we can do is we
can just do Control a. You just need to be selected
anywhere in the data range. It doesn't matter. Control a that will select everything
and it's Command a on Mac. Then we just do the same
keyboard shortcut for autosome. So O2 equals on Windows or
Command Shift T on Mac. So I just do Alt equals. And instantly you'll notice all of our totals
had been added in. Now everything's been added up just with one
keyboard shortcut. And two of you include control a or Command
a to start with. That's just a really quick and powerful Excel skill to have in your toolkit that
can allow you to quickly add up totals
without having to do things manually.
6. Conditional Formatting: Make Your Data Easier to Understand: In this lesson, we're
gonna take a look at two types of
conditional formatting. Now, conditional formatting
is really useful because it can make your data easier to
understand at quick glance. And it also just makes the data look a bit more
interesting quite frankly. So we've got our sales
by Office data here, 2018 through 2021 across six different offices
in different locations. And all of the data
is in dollars. And you can see we've got a range of different
values here. So if I go along to the conditional formatting
menu in the Home tab, the two types that we're going
to look at in this lesson are the data bars and
color scales options. With data bars, There's two
default formatting options. We've got gradient
fill and solid fill. And you can see the different
style that these two have. For this first example,
we're just going to use the blue gradient fill option. What you'll notice
is the bar varies in length depending on how
high or low the value is. Our highest value of
Paris 2018 sales, which is a 179407. That's got a data bar which
covers up the wholesale. And then if we look at
our lowest value here, which is Los Angeles 2021 sales, the data bar only covers off a small proportion
of the cell. As you can see, I
can quickly change the formatting to
a different type. So it really just depends on
what kind of style you want, but it's quick and easy to add. Next up, let's have a look
at the color scales options. Like before. There's various different
options in terms of the style of formatting
that you want and default. If we just go with
this first one, I'll select that so the green, yellow, red color scale. What's happened here is the highest values have
the sort of darker green, and then it goes down
to a lighter green, and then the lowest
values have a grad color. So we've got the Los Angeles
2021 sales with 57811. There's various different
options as I showed. So if we go to
color scales again, we can see we've
got this options, which is our green
white color scale. This one here, which is a white, red color scale, etc. So various different options. Something else to mention
is that you can also add this type of formatting
in an even quicker way. And that's by using the
Quick Analysis tool. So once you've
highlighted the cells, you'll see this little
option appears here. We just select that. And instantly we have the
formatting options selected. And there's the data
bars and color scales. Now there's only one type that
you can add through here. In terms of the formatting, there are less options, but it's a super
quick way to add it, so it's worth being aware
of this option two.
7. How to Add Custom Conditional Formatting: In this lesson, we're gonna take a look at adding some custom conditional formatting based
on rules that we've set. So we're going to return to
our sales by Office data. I'm going to highlight
all of the data values. Come to the conditional
formatting menu. And this time we're
gonna go with a highlight cells rule section. And I'm going to apply
some formatting for cells where there is a value of greater than a $140 thousand. So let's say, you know, anything that's above
a $140 thousand is considered very good. And we want to highlight that. I'm going to select greater than and then I'm gonna
write in a 140 thousand. And there's various different
formatting options. For this. I'm gonna
go with green, fill with dark green text,
and then select a k. You can see how we have all of our values that are
above a 140 thousand. So what we are considering, very good performance, highlighted clearly with
the green formatting. Next up, I'm going to
highlight all the cells again. And I'm going to
add some formatting for offices and
years where we've got particularly poor
performance and we're going to set a benchmark of anything below $70 thousand is poor performance that
we want to highlight. For this time, we're gonna
go to the less than option. I'm going to just
write in 70 thousand. We'll leave it as light red, fill with dark red text. Select. Okay? Now you can see we've got two types of formatting applied. So we've got the green
for above a 140 thousand and the red for
below 70 thousand. So it's just a quick way to
visualize where we've got particularly good
performance and particularly poor performance.
8. Editing and Removing Conditional Formatting: Now something else
to bear in mind with conditional formatting
is that it's easy to edit and remove rules
that you've got in place. With our sales data here, where we have all of
the values above a $140 thousand
highlighted in green, and the values below $70
thousand highlighted in red. Let's say I wanted
to change that so the values that are
highlighted green have to be above a $150 thousand. What I can do to change that, It's just highlight
the cells and then come up to the conditional
formatting menu. Go to Manage Rules. You see we have our
two rules here. I need to select the
rule that I want to edit and then select Edit Rule. Then I changed this from a
hundred and forty thousand, one hundred and fifty
thousand, select, Okay? And select, Okay, again, you can see our
formatting has changed. So for example, these
two cells no longer are in green because they're
not above a $150 thousand. Now let's say I
actually just want to remove all of the conditional formatting
that we've got here. What I can do is go to the conditional formatting
menu and do clear rules. And I'm gonna go with
clear rules from entire sheet as I've not
got those cells selected. But if you wanted to remove the conditional formatting
just from some specific cells, you could highlight them
and go with that option. But in this case
I'll just go with clear ultimate entire sheet, select that and then
all of our formatting, the conditional formatting
is now removed.
9. Series: How to Make Big Lists Quickly: So often in Excel
you want to create a list of numbers or dates. And the common way to
do that is to write out the first two numbers that are going to be
in that list, so 12. And then to drag them down and fill out series
of numbers like this. So let's say we want to
go all the way to a 100. We then got list that goes from one to 100. Just like that. This is a fairly
quick way to do that, is certainly a lot
quicker than for example, doing something like this, where we would enter
each one manually. But there's also another
tool to be aware of in excel beyond just dragging
the numbers down. This is particularly
useful if you need to create really long lists. We're going to start
off with a list that starts at one, except this time, we're going to go up
to the fill options in the Home tab
and select series. What we're going to do is create a list of numbers that
goes from one to ten. Thousands are really long list. And to do that, we
need to specify the series in columns because we're going
down in a column. Step value, we're
gonna leave as one and then type linear,
we're gonna leave that. But the stop value, we're
going to put 10 thousand hair. So 10 thousand, then all I
have to do is press Okay. And then instantly I've
got a list that goes from one to 10 thousand
just like that. So as you can see, that's a
really quick way to create massive long lists of numbers in Excel or something
else you can do. You can use the
Fill Series Options to create rows, rows of numbers. So let's say we want to create a row of numbers that
goes up into each time. So 2468, etc. I'm going to start my row
of numbers like that too. Then I'm going to go along
to the Fill Series menu. I'm going to leave the series involves this time
leave that as is. I'm going to change the
step value to two of them. One, I'm going to put
a stop value of 100, and I'm going to select okay,
you'll see what happens. So we've now got the two
times table going 246810, etcetera, all the way
along to a 100 over there. In this next example of
the Fill Series feature, we're going to run through
a more applied use case. So what we're gonna do is create a long list of dates
going from the 1st of January 2019 through to
the 31st of December 2022. Then hypothetically,
what we would then have is a long list of dates where we could populate all of our sales figures down here. What I'm gonna do
is start off with the first of January 2019. And then I'm going
to come along to the Fill Series option. You can see the
Excel has identified that we're working with a
date set selected date. I'm going to swap
series in rows, series in columns because
we're going down. Then I'm going to
specify the stop value, which is the 31st
of December 2022. All I've got to do is select. Okay. Then just like that, we've got all of our dates populated. So we've got all of those
values going from the 1st of January 2019 all the way through to the 31st,
December 2022.
10. Same Change, Different Sheets: In this lesson, we're going
to look at how you can make the same change to
multiple sheets. Now this is really
useful when you've got a range of different sheets and you want the same change done
to all of them, but you don't want
to have to do it manually three times or four times or five times or
however many sheets you have. So here we've actually got
a workbook with 12 tabs, one for each month of the year. We're going to use this workbook to be putting sales data in. What I want to do is have
four headings, date, unit, sold, price per unit, and revenue in each
of these tabs. And I want them all
to look the same. So what I can do, I'm currently in
the January tab and as you can see,
these will blank. What I'm gonna do is just select the January tab hold Shift
and then select December. And then that will now have all of the sheets highlighted. I'm going to start off just by writing in the column headings, just in one of these sheets. So I'm just in
January sheet, date, units sold, price per
unit, and revenue. Now, I'm going to just
tidy this up a bit. So I'm gonna put these in bold. I'm also going to
resize these columns just so all the data fits correctly and we can see
the column headings. Now what you'll notice
is when I click on each of these
individual tabs, you'll see that all of
them are exactly the same. So we've got those four
headings of dates, unit sold, price per unit, and revenue in all
of these 12 times, but we've only had to
enter them in once. It's also worth bearing
in mind that you can reverse these changes. So if we select all
the sheets again, and then if I was
to just highlight the January ones and
then hit Delete. And then if we look
through each of our sheets will see that there is
nothing in these cells. So the change has been
applied to all of them. Again, that's just a great
little time-saving hack and stops you having to repeat the same task over
and over again.
11. Sparklines: What, Why, and How: In this lesson, we're gonna
take a look at sparklines. Now what sparklines are there mini charts that fit
in slide one cell. We've got some data
that we're going to use for the first two examples. Its sales by rep running
from 2016 through 2021. We've got different sales reps and all the different years. So for example, in 2019, animated 67 sales, whereas
Leo made 35 sales. In 2017. Peter made 40 sales and Sheldon made 63 sales, so
on and so forth. Now to bring in the sparklines, what we need to do
is just highlight all of our values
just like that. And then you'll see we have the Quick Analysis tool
option appear here. I'm gonna select that. Then over to the right we
have the sparklines section. And then there's three
different types. And we're gonna start
off with the line. So here we go. In the performance trend column, we have a new little line
chart that's been added. And if we look at Anna's line, what it's doing is basically showing the trend
of this data here. So we start off quite high, 66, then it goes down. Then it comes back
up again where we have stats value of 67, and then it goes down again. If we look at Leo, for example, starts off quite
high, then goes down, then goes up again, then goes down again. I'm Peter starts off very high, drops off, kind of
dips in the middle, but then starts to
climb up again. Something else to be
aware of with sparklines, we can change the
formatting of them. So if I go to the sparkline
option in the ribbon up here, there's various
different options so I can make them
green, for example. And then you'll see the
color of them has changed. Something else I can do is also highlight the high
points, for example. And then you'll notice we get a little marker where
the high points are. Then next up we're going to have a look at another
type of sparklines. So I'm just going to go back. And then we're going to keep all of these values highlighted. Select the Quick
Analysis tools again, go-to sparklines and we're going to have a look at
column this time. Now, we have the same sort of
thing going on here really. So we've got a mini charts, except we've got a column
chart rather than the lines. If we take a look at
pizza, for example, we can see that trend
of starting off hi, going low again, then
climbing up again. Sheldon starting off very low, suddenly jumping up and
then going down again. And again. There's various different
formatting options, so you just have to
go to the menu again. We could select high point
and then you can see, in this case, the columns were the highest values
are colored in red. In this next example, we're gonna look at some
slightly different data. Here we've got profit
and loss data by month for 2018, fruit 2021. What I'm gonna do is just
highlight all of these values. Come along to the
sparklines menu and select win or
loss this time. What you can now see is that
where we've got red markers. So here for example, the third marker,
all of them are red. We can actually come across to the data and we can see that all of these values are
negative for March, the third month, so we've
got minus 50 thousand, minus 23 thousand, etc. And that shows up
in the sparkline. Then other points, you can see mostly we have positive
values of profit, but then other points,
for example here. So that's the second to last
month in 2019, November. We've got a value of minus 736. So that negative value is showing up in
the sparkline too. As you can see, this is
a great sparkline to be aware of when
you're dealing with positive and negative values. And you want to
quickly identify where the positives are and where
the negative values are.
12. Next Steps: Thanks for taking the class
and I hope you enjoyed it. Let me know if you have any
questions and you can find the class project
instructions and files below to test
what you've learned. If you want to learn more
about Excel checkout my other classes and follow me for updates on new classes. Once again, thanks for taking the class and I hope to
see you in the next one.
Excel Classes, Excel teacher
