How to create a financial year in Excel? This Excel account tutorial will not
only show you how to generate a financial year in Excel, but if you've ever wondered
'can I use a pivot table with a financial year?'
Then you're in the right place! I'm Simon a real life Excel trainer and this is Computer Tutoring.
Okay so if you've been following along in this Excel account tutorial series then you will have
created this spreadsheet, if you haven't got it and you want to follow along precisely then I'll
give you the link that you can download. Now what we're going to do to create this pivot table
with financial year, is first create a formula to work out the financial year, and then apply it to a
pivot table. We'll then see a sorting problem which means that that we want April at the top and we'll
have January at the top so we'll use a custom list to overcome this problem, and then finally I'll
show you how to create a financial quarter formula. Let's get started. What I'm going to do here is
go to sheet 1, right click on description, go down to and we're going to insert table columns
to the left, and then we will start off with the financial year. Okay and then this is the formula -
now you can see what I've done here, is I have, if I just zoom back, I will use this little
drop down list on the right hand side to expand my formula bar, and I'd suggest you do this for
particularly complicated calculations or ones that you're going to put down on extra line -
hopefully this one's not too complicated but I'll keep it slow and steady. So what we're going to do
is create an 'if statement' and the logic of this is basically if the month is January February or
March it's the previous financial year in this one (at this time) and if it's anything from April onwards it will
be this year and next year. So we're going to start off with an = type in IF open bracket, and
then what I need to do is know what the current month is. So for instance
I'm going to click on date here and basically I'm checking to see what the month is for that date.
Now that's what month number that date is - so is it 1 is it 2 is it a 3 whatever, so what
I then need to do is to check if it's less than 4. So if the month is
less than 4 we know it's January February March is the previous financial year, and if its April onwards this financial year.
So I'm going to hold down ALT and press ENTER on the keyboard, so it's ALT
and ENTER and that wraps the codes around to the next line or the function down to the next
line - I like to separate them up it makes it easier! Just press space a few times to bring it in.
So now I'm going to type in Year and click on the date again,
type - 1, and then I'm going to what's known as Concatenate, I know it's a big word that basically
means joining together. I'm joining together whatever the previous year is
here and a little dash in between and then I'm going to concatenate that with the
to the current year. If you want to know where the ampersand is or the little &
symbol it's SHIFT & 7 on your keyboard, and I put that in double quotes. Great excellent so
what I'm do is press comma and ALT Enter to go down to the next line because,
say for instance if it isn't less than 4, say the month is 4 or onwards.
So what I'm going to do here, is going to be the current year so Year and the date again,
brilliant! Concatenate (&) you remember that word there concatenate - we're going to bring
it together with next year - so that will be Year again and click on that there, that's great plus
one (+1) and then I'm gonna close off the bracket. So there we go. So here we have our logic there -
so either if it's less than 4 we know it's done in February March so the financial year will be
well 2019 - 2020 and if not, it would be 2020 - 2021. if I press Enter let's have a quick look
and see what I've got. As you can see I've got the financial year. Looks good. If I scroll
down until I get to April dates, let's have a look that should be a crossover - there you can see
if I just move in a bit - so there we go there's the 31st March financial year 2019 - 2020 but now
the 1st April is 2020 to 2021. Great so now we need to apply this to a pivot table, apply this
financial year to a pivot table. So this is how you do it! I'm going to click anywhere in the table, I'm
going to then click on Table Design at the top of the screen and then going to click
on Summarize with Pivot Table, that's great. I'm going to do this on a new worksheet, so
just make sure it's picking up the name of the data, Sales Data - go into a
new worksheet, click on OK, brilliant so now we're going to apply our financial year. Now if
you don't see financial year in the list you see it's not there at the moment so oh it's gone so
what I need to do is just right click on the pivot table and go to Refresh to refresh the pivot table.
it will then update the pivot table and there I can see Financial Year so I can drag Financial
Year down into rows and it's appearing there in the pivot table. Let me just use this little
zoom slider to just make this spreadsheet a bit bigger, and then I'm interested in Received so
I'm going to drag Received into Values and now I can see this broken up by financial
year. It's looking good. Let me just reformat this number of the received amount,
right click on the Sales go to Number Format and I'm going to format that as Accounting.
That's great. Now I want to add in Month. I don't have a month field, but I can extract the month
from the date. So if I get in closely here and drag Date all the way down and just underneath
Financial Years you see it comes up Financial Year and it's also got Years and Dates, well
I don't want Year there, so I'm just going to drag Year out. I hope you can see that there.
Excellent now as you can see there's a tiny little problem here we can see January is the first month
and it shouldn't be, it should be April, and that's where we would use Custom Lists. That's how we can
use custom lists to sort data or to sort months by financial year. So first thing we're going to
do is let's create our custom list or the order of the list so instead of typing it in manually
we can do that a little quicker in excel, I'm going to type clicking I1 one it doesn't matter which
cell, we're just using this to import. Click I1, type in Apr for April, I then use the Auto
Fill down here to drag this all the way down to March, let's get to March - there we go that's great.
That's going to be our custom list. Then to use the custom list, first I'm going to get
you to click on File just in the top left hand corner and then we're going to click on Options
just down in the bottom left hand corner and then from the Categories on this left hand side here
we're going to click on Advanced, if we do that and then we're going to scroll all the way down to the
to the Edit Custom Lists button so we can see that, so we're going to click on Edit Custom Lists now
this is one I've done earlier so I'm just going to delete that one.
So what I can do is, you can see if I just zoom back a tiny bit but you can
see that the custom list here, is already picking up this list here, like so. If not, you could just
reselect it if you need to, but all I need to do is click on this Import button which will bring it in,
that looks good. Click on OK and now I've got a custom list. That's great - click on OK and
that's brilliant. Now I've got this custom list I can get rid of that, press delete on the keyboard -
don't need it anymore. So now I'm going to go across and click on this drop down list just
here, and I want to sort by the Date which is the Month in this case because we don't have any date
lower in the hierarchy, and then I'm going to go to More Sort Options and then click
on the More Options button at the bottom and then going to untick the 'sort automatically
every time the report is updated' and I'm going to choose my custom list here , April through to
March and I'm going to click on OK and then I can then sort it. Let's say for instance I
sort it Ascending by Date, that's great - click on OK and now I can see April is the first month.
Now you may have remembered in the introduction to the right of me I think there was a pivot
table that had the financial year as a slicer. That's a good option - so let's do that. So I'm
going to click in the pivot table, go up to Pivot Table Analyze at the top and then click on the
Insert Slicer button that you can just see here and then I'm going to choose the financial year
option, go down to the bottom and click on OK and there we have our financial year. Just to make it
look a little better, I'm going to change the order of the financial year, I want the latest one at the
top, so I'm going to right click on the slicer and go down to and click on Slicer Settings and then
I'm going to change the sort order to Descending and click on OK and now I can see the
most recent financial year is at the top, let me use the resize handles around the slicer and I'm
going to drag the slicer using the header at the top just to the right here and now I can just select
my particular financial year that I'm interested in. The last thing I'm interested in and want to do is
to work out the financial quarter using this pivot table. Now just like with the financial year we'll
first have to go back to the table and create a financial quarter calculation. So let's go back
to sheet 1 where our data is, going to right click on description and go to Insert because I want to
insert columns to the left oops that's in and out there we go, interested in financial quarter so
I'm going to call it Financial QTR there we go, and then what I'm going to do is take advantage
of what they call the CHOOSE Function in Excel. Now the Choose Function, it's a nice simple function,
it's actually so simple sometimes we over complicate it. Now the way it works basically is, first let's
type the name of the function =CHOOSE ( , now the first argument or instructions
for this choose function is a number any number, so for instance I'm going to need
to know what month it is, so I'm going to type in Month and then click on Date just over here,
so i know okay whatever number month it is, so if it's January it will return 1, February would return 2
and March would return a 3. Now based on that what would happen here, is if it was a 1
it would choose the second argument in this list , so for instance if I typed a 4 in here, if the
month was January which would be 1, the quarter would be 4, so I would type a 4 in this list
here. Same with February. if it's February it'd also be a quarter 4 in the financial year,
and the same with March so the three 4s in there. The same as well if it was going to be April
May or June, that would be the first quarter in the financial year, so I'm typing 1 1 1 and so
on, the second quarter going on to July August Sept and 3 3 3 (Oct, Nov, Dec) and close that one
off there. Brilliant. So just to explain a little bit with this one,
lets zoom in close, brilliant excellent - so depending on
what this number here returns, so if this returns say for instance a number 2 yeah it would then
select this number here, so if it was then a number 3 or so on, let's just go back here
and let me just see if i can do my typing in again, so if it chose a number let's say
for instance let's do a harder one let's say it was a number 5 that shoes there, then what
this one would go into is it would count along 1, 2, 3, 4, 5 it would then return
this number, when I say return display that as an answer. Great I can press enter, let's just zoom
back and see if this one works. Now at the moment you can see it sucked the formatting across, I love
that term, thanks to whoever said that one. I'm just going to CTRL + Shift and the down arrow to select
all of my financial year there, and there's a nice little shortcut to convert the format
of that back to general it's CTRL + Shift + and the tilde or hashtag key If you're using a UK keyboard
or the tilde key, and that basically converts the formatting to general, so you can see there we go
the formatting at the top is now general.
Let's use our Concatenate (&) because I want a Q in front of the actual number
so let's just get in close here so we can actually be clear, click on this and then i
want a Q in quotation marks and then concatenate (&) whatever the result of the choose function is.
That looks good let's just press enter, now we can see our financial quarters. Let's go and apply
that to the pivot table, so we're going to go back to our sheet with the pivot table.
I'm going to right click on the pivot table and go to Refresh so we need to do that because we
want to see financial quarter, and there we can see financial quarter, so let's get in really
close with the financial quarter and we can drag that all the way down and we can wedge it hope you
can see that there let me get other way in between financial year and financial quarter - hopefully you
can see that there. So if you just look let me just move myself a little bit out of the way and then
let me just get in a bit closer over here so now you can see financial year, financial quarter date
and more importantly in the pivot table it's separated by financial year and financial quarter.
There we go how do you get along with that? Hopefully you've been able to follow along but if
not i'll give you all the calculations below so if you just want to copy and paste or take your time
then please do so. Have a look at our website computertutoring.co.uk for the completed file
and also if you want to download the starter file as well. So now you know how to create
a financial year in excel, and you also know the answer to the question 'can I use a pivot table
with a financial year' and have also looked at the sorting problem using custom lists
to overcome sorting problem so these custom lists you can put your own sort order in, so in
this instance we had April as the first year (beginning of the financial year) and you also know the formula to create a financial
quarter in excel. So if you've got anything out of this tutorial give us a thumbs up.
Now the next tutorial we're going to look at is the profit.
So at the moment we've got the received, so if I just go over here, here we go we've got
the sum of received, we don't have how much money we've made, why? Because we haven't included the
expenses and a calculation within the pivot table. That's right we're going to look at
calculations within pivot tables and how that can help you with your accounts pivot table
or your account spreadsheet. So I'm Simon from Computer Tutoring I hope everyone's keeping safe
and I just want to say thank you so much for watching.