Hi everyone! Now you've clicked on this link because you're wondering
can I use pivot tables to display accounts in Excel? Or maybe you're wondering how to view
monthly received and expenses in Excel? Well if that's so then you're in the right place because
in this Excel account tutorial we will look at how to use a pivot table to view accounts.
My name is Simon a real life Excel trainer and this is Computer Tutoring.
Okay so if you've been following along and this is the sixth in the series of easy accounts
tutorials in Excel. You will have created this spreadsheet, now if you don't have a copy of it - no
problem! I'll give you the link so you can follow along down below. Before creating your pivot table
I recommend you first know how to create tables in Excel, this will make updating pivot tables easier.
To create a table click once on any cell, so I'm just clicking over here on this 5%
for lunch, and then go up to the home tab at the very top
and then click on format as table just on the right hand side here.
So you can now choose any format that you want, so I'm going to choose one of the grey formats,
this light grey table style medium 11. You can see in the box that appears here it says format
as table, it has all of the data automatically selected, and make sure my table has headers is
ticked, click on ok and now you can see your table in Excel. Now we're going to give our table a name.
Why give your table a name in Excel? Well you give your table a name to make it easier to identify.
Let's do that, make sure you click anywhere in your table and then up at the top just
making sure you've got the table design tab selected, do that, and then on the left hand side
you can see the table name box which at the moment says Table1. I'm just going to double click
select Table1 and call that accounts data, it doesn't like spaces, numbers must be put at the end
and stay away from any funny characters. Let's zoom back and there we have it we have named our table.
Do tables speed up your data in Excel? Yes they do! So you should be using tables to speed up your
spreadsheet. So why should you use tables to create pivot tables in Excel? I said before tables make
updating pivot tables easier. Okay so let's get on and create our pivot table. So what I want you to
do is click anywhere inside your data, so I'm just clicking on the 5% in cell C11 but
you can click anywhere. You can see the Table Design tab is available at the top, so just click
on that, and then we can click on the button that says summarize
with a pivot table, oops I've got the wrong one here, there we go summarize with pivot table,
give that a click, and then this create pivot table dialog box appears where you can see the accounts
data table, it's automatically picked that up. So what we just need to do is click on ok
and hey presto there is your pivot table. So now we have created our pivot table let's
answer the how to easily view monthly received and expenses in excel? So firstly I'm just going
to move myself just along a little bit get myself out of the way there and let's say if i want to
see how much money that i made, I'm just going to drag the received down in into values, and then if
you look on your pivot table over here you can see the total amount of money I've received that's 78660.
But now I'm interested in the monthly amount because I want to know the monthly received.
So what I'm going to do is take the date column, if I just zoom in a little bit here and drag date
all the way down in into rows, you can see here that I've dragged date into rows and
I've got received into values and you can see the data is automatically separated into different
components. This is known as a hierarchy. So I've got years quarters and dates.
So let's just go into our pivot table here I'm just going to zoom in using the little
zooming in bar here that's brilliant that's good just make the spreadsheet bigger.
Now I want to see the months and the year, so I'm going to right click on any year here,
I'm going to go down to and go to group and click on group. As
I said I'm interested not in quarters so I'm going to remove quarters and just make sure
months and years are selected, you click once to select and click again to deselect. I then click
on ok, let's just zoom back and now I can see this is how much money I've made or received each month.
How can I easily see a summary of expenses in Excel? Well first I will copy and paste the pivot
table, so I'm just going to go and select A3 all the way down to the bottom of the pivot table
here and CTRL C to copy. Now I like to leave a gap between my pivot table so I'm just going to miss
out the C column here clicking D3 and then CTRL V to paste the pivot table. Now let's just go
into our pivot table fields here on the right hand side, now I'm not interested in the number received
so let me just get rid of that, I'm interested in the expense. I'm going to drag expenses down
like so. I'm not interested in the years or the dates so I'm going to drag out years and dates,
and I'm going to drag description down into rows. If I look at my pivot table now I'm almost there
because i can see how much i spent on gloves and how much on lunch and how much on weed killer,
but I'm also seeing my customers and i don't want to see that. So I can use the pivot table filters.
So I'm going to click on this drop down list to filter a pivot table, I'm going to go down to
and choose value filters, and then I'm going to go down to and choose less than, because all of
my expenses are negative numbers, so when I click on less than, I'm going to type a 0 in there, I'm
going to click on ok and now I can see a summary of my expenses. So it's starting to look good.
There's just two things that we need to do. The first is answering the how to filter two pivot
tables at the same time? question. Before we do that we're going to change the format of numbers in a
pivot table. We need to change the currency formats in these pivot tables. So this is how you do it.
You're going to go over a pivot table and right click on the number that you want to change the
format of. You're going to go down to and click on value field settings. In the value field settings
box we're going to click on number format at the bottom, and then I'm going to choose accounting.
If you need to change the currency symbol then you can do so on the right hand side using the symbol
drop down menu, and then we're going to click on ok at the bottom and again and now you can see
that's changed to currency. Let's do the same again with the summary of expenses pivot table.
Go down to value field settings, number formats, currency okay accounting,
Ok, Ok and now we have changed the format of the numbers in a pivot table the currency. Finally
let's answer the how do I filter two pivot tables at the same time? question. We're going to do that
by means of a slicer. how do you create a slicer in Excel? Well this is how you do it. We're going
to click on the pivot table here so notice I've just clicked anywhere on this pivot table and then
we're going to click on the pivot table analyze tab at the top, and then we're going to go along
and click on this insert slicer button. Now notice the insert slicers dialog box appears and I can
choose which slicer i want to add. Basically I'm asking how do I want to filter this pivot table?
I'm going to choose date and then click on ok and you can see it's added a slicer in with the months
of the year. When I click on Jan you can notice that this pivot table is filtered by the button
I press on this slicer, but also notice if I move the slicer out of the way it doesn't filter the
summary of expenses pivot table. How do I get this slicer connected to both pivot tables? to control
both pivot tables? Well first I'm going to click on this clear button, I just like to clear the slicer,
and then going to make sure that I've got the slicer tab selected at the top
and go all the way to the left until i see report connection, and click on report connection and now
I've got the report connections box, I'm going to check the second pivot table so make sure
your pivot table numbers might be different just make sure they're both selected and click on ok.
Now this slicer controls both the pivot tables. I can click on January and see all the sales
for January, I could hold down the CTRL key on the keyboard and click on March so I can see
my January and Marches sales, or I could hold down the CTRL key and hold drag with the
mouse to select multiple months if I want to. if I want to clear it all I can click on the clear
selection button or the clear the filter button. So I hope you're okay in following along.
So the answer to the question can I use pivot tables to display accounts in Excel?
Is yes of course you can. You can also use pivot tables to display a summary of expenses in Excel.
Now in the next tutorial we will look at the financial year, because I hear your question
you're saying 'Simon this is great but my financial year starts in April through to March so how do I
deal with this in a pivot table?' and that's what exactly what we're going to look at next time.
So if you haven't already done so please click on the subscribe button and then click on the
notifications button so you don't miss it. if you are using Facebook or Instagram or twitter
then reach out to us it would be great to hear from you, and if you've got anything out of this
pivot table tutorial give me a thumbs up means so much thank you very much really appreciate it!
So I just hope everyone's keeping safe and I want to say thank you so much for watching.