Today I'm going to cover
the top 10 Excel features that you're going to want to
know if you work in accounting. So not Excel functions, because for that I have a separate video. The link for it is in the
description of this video. It's about features
today, so let's jump in. (energetic music) Number one, go to special. With go to special, you get to quickly identify
cells of a specific type. So for example, let's say
when you click quickly see these cells that have constants and the ones that have formulas. We can go to home, find
and select, go to special. And we also get shortcuts to some of the functionalities
of go to special. So let's say I want to
highlight these cells that have constants. I can select this, and
I immediately notice that only two cells are not selected. This is probably because
they have formulas. If I go back to find and select and click on formulas,
these two are selected. If I need to, I can immediately color
them in a different color. I can remove the values, change the font, and apply any type of formatting I need. Now, what else is available
under go to special? Well, this menu here is more complete, you have more options. You can select objects, current region, or even conditional formats. So just to check whether you have some hidden conditional
formatting on your sheet, it doesn't hurt to select
this and click on okay. And now I can see this part of my sheet has conditional formatting applied. When I go to manage rules, that's the formatting behind this. Would go to a special, I get to quickly identify these, but there is a shortcut key
for go to specialist as well. It's Control + G, to get to
the special part use Alt + S. Number two, automatic subtotals. You can get Excel to automatically
add subtotals for you wherever you need it, without you having to
write a single function. So here I have a count day
transaction number and amount. And let's say I want to add a subtotal every time account changes. Before I use that functionality though, I have to make sure that the column I want to add to subtotals to, is sorted. So everything is grouped together. Let's quickly do that, I'm just going to right mouse click sort, sort A to Z, nor did I have
everything grouped together. Let's go to data, under outline, we get to add subtotals. First question is where do we want the
subtotals to be added? We wanted at each change
in not date, but account. Which functions should we use? Well I want to sum the amount column, but I have other options as well, I could count average, max, min and so on. I'll stick to sum, which column do I want to get the sum of, while the amount column. If I have existing subtotals in there, I can replace the current ones, I can also add page breaks between groups. So if I wanted to print
this out on separate pages, I can place a check mark here. I'll leave summary below
data and click on okay. And take a look at this, I get these rows automatically added with the correct function. On the bottom here I get
a grand total as well. All of this grouping was added
by just clicking subtotal. What if you want to get
rid of all these subtotals? Go back to outline,
subtotal and remove all. Number three. Number formatting shortcuts. As accountants you work
a lot with numbers, so it helps to remember some
number formatting shortcuts. For example here, I have a list of dates but they're shown as integer values because they have the general format. Now I can always go here and change the formatting to
a short date or a long date, but I can also use a
shortcut key to do this. Let's select a range with
Control + Shift + Down, and use the shortcut key
Control + Shift + Pound. In case you'd like to have
a different date format, use Control + One, to bring up the format cells dialogue box, and select your formatting from here. We can also switch to date and select your date
format from this list. What about other number
formatting shortcuts? To change the values in the amount column to currency selected,
and use the shortcut key Control + Shift + Four. This changes the formatting to currency. If you want it as number format,
use Control + Shift + One. This adds the thousand separator as well as two decimal places. Number four, formula auditing. When you work with
formulas, it can get tricky to remember which cells
are impacting your results. So for example, there's a function here, and I quickly want to see
which cells are impacting this. Now I don't want to read the
formula and go back and forth, I want to visually see this. I can go to the formulas tab
and click on trace precedents. I can quickly see these two cells are impacting the value in D4. To remove these arrows,
click on remove arrows. Trace dependence on the other hand, shows you which cells are
dependent on the existing cell. So here in D3, I have a
value that's input directly. When I click on trace dependence, it shows me that this cell, this and this, as well as this, are dependent on the value in D3. Now, what do you see
if your dependent cells are on another sheet? Well, let's remove these arrows go to D4 and select trace dependence. This is what you see. So in this case, one cell is here, so this one is dependent on this, but this icon means there
is a cell on another sheet that's dependent on this as well. To see exactly which cell that is, double-click and this black arrow, you get the go to dialog box, you'll see the list of dependent cells, in this case I just have one, so I'm going to select that, click on okay, and I'm
going to jump to that cell, that's dependent on the
cell I was analyzing. Number five, conditional formatting. The conditional formatting is a great way to quickly visualize and
understand your data. You can use it to bring attention to certain cells depending on a condition. So for example, let's say
for the amount column, I wouldn't highlight the top 10%. Select this range, go to
conditional formatting, top bottom rules and select top 10%. This automatically applies a
different format to the South. Now you can adjust these
settings as you need, you can change the 10 to another number, you can also change the color. So let's go with green
fill with dark green text and click on okay. You can of course add
more than one condition. So let's say I also want to
highlight the bottom 10%, select bottom 10%, and let's
leave this with light red and click on okay. All of this is dynamic
when my data changes, if this goes to two, this one
is going to get highlighted. Now, as you probably noticed
in conditional formatting, you have a lot more options than this. You can add data bars,
color scales, icon sets, and you can even create your own rules. If at any point in time you
want to clear the rules, you can clear them from
the selected cells, or clear them from the entire sheet. This is going to remove all your
conditional formatting rules. Number six, remove duplicates. Remove duplicates returns
a unique list of values. Here for example, I have
transaction number and account, and I want to get a unique list, for the combination of
transaction number and account. Go to data, under data tools, we have remove duplicates. Now notice my active cell
is somewhere in the dataset, so when I selected it,
it automatically picks up my data range. There's a check mark
for my data has headers, which is correct. All I have to do is click on okay. Seven duplicate values
were found and removed, nine unique values remain. This is based on the combination
of column A and column B. And of course you can run
this on a single column or more columns as you need. Number seven, Flash Fill. Unfortunately we've
received our account labels in this form, where we have the code and the account description, we need to split these
into two separate columns. We don't have to worry about formulas or using any of the tools up here, we can use Flash Fill, and there is an accessible
shortcut key for this. All we have to do is to
show it what we want, and it's going to try
and figure it out for us. So in this case for code, I wrote 5.1.1, so I'm basically entering what I see here. Now check this out, I'm going to highlight this range, and press the shortcut key
Control + E for Flash Fill. It understands what I want and
it gives me the correct code. We can do the same for description, lets type in costs of goods sold, lets highlight the area, and in case you forget the shortcut key, Flash Fill is available on the home tab. It's right here to this side, under fill options, we have Flash Fill, and we get the description. Number eight, Autofilter. To be able to quickly
filter this data dataset, use the shortcut key Control +Shift + L. We immediately get these drop-down boxes, where we can filter our dataset. I can select a specific field
or I can use text filters. So for example, I want to filter accounts that
contain the word expenses. You can add additional filter levels, so let's say for date, I
can pick a specific month. I can open this up and
pick a specific date or I can use the inbuilt
date filters here. I have this week, next week, last week, so many options here, and you can also add
your own custom filter. So for example, if the date
is after a certain date, how do we enter it? Well, we don't have to worry
about entering dates here because we have a date picker. So I'm just going to select
these date click on okay, and I've added a second
filter to this dataset. To deactivate all filters, just go back to Control + shift + L, to bring it back, press
Control + Shift + L again. Number nine, data validation. If you're creating templates
that you want others to fill, Make sure you add data validation. This is going to help you
reduce the number of mistakes. So let's say I want users
to fill up the account, but the account should match what we have in our central account list. I can add dropdown lists here. To do that go to data, under data tools, select data validation. For allow, the default is any value, but I don't want that,
I want a list instead. This will give you an in-cell dropdown. For source, while I have it right here, so I'm going to select this range. And it doesn't have to
be in the same sheet, it can be in another sheet. Just go to it, selected
it and then click on okay, and you have your dropdown lists. If you want this list
on all the other rows, you can select your entire range, before you go to data validation. Or if you forget like I did here, just copy your cell, then
highlight this range, right mouse click, paste special, and only paste the data validation. This is going to give you to
drop down without the content. Aside from dropdown, you
have many different options. So if you want to make sure that the users input the date correctly, create a date data validation. Go back to data tools,
under allow, select date, you can put a start date and an end date, or you could for example, only allow for dates that are greater than the dates you specify here. You can also make this dynamic, so if you want dates after today's date, you can use the today function, and then click on okay. And then the users can only input a date that's in the future and not in the past. If they try to do this, they get an error. Number 10, pivot tables. Now I left the best for last, because pivot tables are
really easy and super powerful. In this case, I have
account, supplier and amount. And let's say I want to take a look at total amount by supplier, or maybe I also want to
take it more detailed look, at supplier by account. I might want to take a look at
the amount by different months. All of this is easily
doable with a pivot table. With your data organized
like this in columns, go to insert pivot table. Make sure your range is correct, so that it's highlighting the range. I want to be my report on, then all you have to do is choose where you want to have that
pivot table report placed, on a new sheet or an existing sheet. Well, I'm going to pick existing sheet, let's just put it on the
side here and click on okay. This is going to give us a
canvas for our pivot table, I just need to decide what I want to show. I want the amount, so I'm going to place a check mark beside it, is placed it in the values column, what do I want to show in the rows? Let's go with supplier, my report automatically sums the amount. If you want to show something else, so if instead of sum, you want to show the average or the count, you can go to value field settings and change your selection from here. You can also update the number formatting, just right mouse click,
go to number format, change this to a number, I use it as a separator,
zero decimal places, and I have my values here. I can also dynamically sort this, right mouse click, sort,
sort largest to smallest. In case I want to take a closer
look at account by supplier, I just need to drag
account to the rows here. If I want I can also put
account in the columns, to get a wider report, or I can put account
in the filters up here. So I can either take a
look at all accounts, I can select multiple accounts, or just select a single account. Anytime my data changes in the source, so let's change this to a two, all I have to do is right
mouse click and refresh and my data updates automatically here. Now, what if you wanted
to get more information about this number, we'll
just double click on it, and Excel will create a
separate sheet for you with the details of that number. Now, there is a lot more to pivot tables than I can cover here, I have a separate video on this, and I've added the link to
the description of this video. Okay, so that was my list of useful Excel features for accounting. So aside from functions,
what else do you think should be on this list? Comment below and let us know. As usual I hope you enjoyed this video, if you did, don't forget
to give it a thumbs up. And if you haven't subscribed
to this channel yet, consider subscribing so that we can see each other more often. Thank you for watching and
I'll see you in the next video. (energetic music)
