Using a pivot table calculated field is the key
to using formulas within pivot tables in excel, in that way you can create a spreadsheet such
as this. So you in this spreadsheet you can see we've got the the profit here, there we go and
we've got the percentage of the profit as well. So I'm Simon a real life excel trainer
and this excel accounts tutorial will show you how to use formulas within
your pivot tables. Let's get going. Okay so if you've been following along with this
series of excel accounts tutorials this I think is the eighth one. We have created in
a pivot table and done a financial year, this time
we will need to work out the profit, because at the moment we can just see sum of the
received, so if you haven't been following along you just come straight to this tutorial then don't
worry! I'll give you a link down below and I'll give you a link to the supporting web page where
you can download the starter file, the completed file as well, and you'll also find step-by-step (written)
instructions there - if you prefer to use that. Let's do this shall we? So we're going to drag
across this nicely created financial year slicer, that's what we did last time. I'm going to click
on this pivot table. The first thing I need to do is add the expenses, so I'm going to go and just
uh let's have a look here, there we go, just find expenses. I'm going to drag expenses down and
just drop it underneath sum of received in values, and now I can see my expenses
have been added to the pivot table. So now it's is in the wrong format so I need to
change the format of this in the pivot table to pounds and pence. So right click on the actual
field sum of expenses and go to number format and I like accounting so I'm just going to double
click on accounting, so that it's formatted in a currency format. Just drag financial year
to the right, I'm going to click here and as I said before the key to this is using a pivot
table calculated field, that's how you would write formulas within a pivot table. What do I
mean by that? Well if you look at the top of your ribbon in excel you'll find the top of the screen,
you'll find the pivot table analyze tab, so give that a click and then there's a drop down menu to
the right, if I get that right there we go called fields, items and sets, and we're going to click
on calculated field and in this calculated field box we can type in the formula for the calculated
field. First thing we need to do is give it a name. It's highlighted in grey so it's ready to start
typing and this calculated field is going to be called Profit, so I'm going to now get
rid of the zero in the formula, leave the equal sign in there, and so the formula is going to
be Received, so I double click on Received here, press plus on the keyboard, and then expense. The reason I'm going to
receive plus expense is because the expenses are negative numbers, So once that's done I click
on ok and now I can see my sum of the profit. It's automatically picked up the currency format
or rather I should say the accounting format in the pivot table but if you want to change you can
right click and change the format. Great looking good! But let's go into more detail
with this pivot table calculated field by working out a percentage. So we're going to use this sum
of profit as a basis to work out how much is that as a percentage of the sum of received. So we
can have a quick look to see when we made a good amount of profit as a percentage. So let me just
drag financial year to the right we'll make sure we've got our pivot table selected, make sure we've
clicked on pivot table analyzed, you can see at the top, click on fields items and sets, and go down to
calculated field. Let me just get in close, so this I'm going to call this one Profit Percentage,
and the formula in this is going to be, the profit divided by the received. So how much
profit divided by the received. I'm going to click on ok and yeah it's looking good it's just
formatted incorrectly it's formatted as accounting or currency we want it formatted as percentage, so
right click anywhere in the sum of profit column go to number format, double click on percentage.
So there you go, now you know how to use formulas within pivot tables, and the key is using the pivot
table calculated field facility. Now if you've got anything out of this tutorial give us a thumbs
up it would really be appreciated! Thank you very much. Of course if you're on Facebook or Twitter
or Instagram then please reach out to me it would be great to hear from from you. If you have
any comments or any suggestions on how you use calculated fields within pivot tables then put
them in the comment section below. Now the next thing we're going to look at is, of course we've
got the sum of profits as we can see just here but say for instance we want to have a minimum
profit of 90% and when that occurs, then we get the entire row highlighted in red. That's right we're
going to have a look at conditional formatting within a pivot table, how to do that. So make sure
you subscribe and make sure that you click on the notifications button so you don't miss a thing. I
hope everyone's keeping well and I hope everyone's okay. I just really want to say
thank you so much for watching.
