⏲ TIMESTAMPS 0:00 Introduction 1:00 The Data 3:44 Headline Figures and Images 11:33 Doughnut Chart 14:41 Dual Column Chart 19:20 Line Chart 21:33 Treemap Chart 24:34 Waterfall Chart 28:06 Top 5 Conditional Formatting Data Bars 30:08 Slicers 33:05 Dynamic Chart Labels 39:25 Updating with One Click!
AI Generated·May contain errors
Video Summary
AI Generated
This tutorial provides a comprehensive guide to building a professional personal finance dashboard in Excel. It covers the entire workflow from importing raw bank data and organizing it with XLOOKUP and Excel Tables to creating advanced visualizations using pivot tables, dynamic formulas, and custom UI design for a one-click update experience.
In this video I'm going to teach you how to build
this personal finance dashboard from scratch,
including the essential steps you must take so you
can update the dashboard with the click of a single button
Now, if you're not interested in reporting
with the click of a single button.
on your personal finances, you'll find there
are a load of Excel tricks
and techniques in this dashboard that you can apply to any
data.
For example, waterfall charts, trend lines,
automatic updates, and more, so be sure to
stick around.
The Excel file is available to download
from the link in the video description, so you
So you can use this file for your own reporting.
And if I go too fast for you,
you can click the cog icon in the bottom right of the video
and change the playback speed. All right, let's get started.
The first sheet in the file will contain my dashboard.
This is just a regular sheet and
I've simply adjusted the row heights
in a few places to save me some time later
but otherwise there's nothing fancy about
this sheet you don't need an add-in or anything like
that. So let me name this dashboard
then on the transactions sheet I've got my bank transaction
data. Now you might import this from your bank statement
or enter it manually.
Obviously there are a lot of different banking systems
so the process will differ from one
to the next and I can't cover them all
but most will have an export to Excel or CSV or
text file button enabling you to download the transactions
and bring them into Excel.
Now your bank should export the first five columns
of data containing the account it
came from, the transaction date,
the description and the debit or credit amounts.
I've then added a formula for the amount.
And this simply takes the credits minus
the debits to get a single column of values that
stores expenses as negative values and income as positive values.
And this column is simply required for the waterfall chart.
I've then added subcategories
for each transaction via a data validation list.
If you're not familiar with how to insert
a data validation list, then you can click
Click the link at the top of the video to see a tutorial.
The date validation list is stored on the date validation tab.
You can see it in this table here.
It's also got columns for the category and
category type. Now you can modify this table to suit
your own needs, adding and removing rows as required.
The next thing I need to do is populate the category
and category type fields.
And I'm going to use a lookup formula for this. I'm using XLOOKUP,
but if you have an earlier version of Excel,
say 2019 or earlier, then you can use
VLOOKUP or INDEX AND MATCH.
So what I want to do is look up the subcategory
on the date validation list in the subcategory column
and return the category. Close my parentheses.
And there's my category completed.
Now it's copied down the entire table
because my data is stored in a table
and I'll mention more on that in a moment.
First let's do the category type.
I'm going to Ctrl R to copy the formula across
and then all I need to do is replace the category
with the category type and that's done.
Now these fields enable me to group the transactions
and understand where my money's going.
I've currently got data from January through to October in this table
and at the end of the video
I'm going to show you how you can update
the dashboard with November's data with one click,
so be sure to watch to the end.
Now to enable updating with one click it's essential
that you format your data in an
excel table using the keyboard shortcut control T. My data
is already in a table and you can see
here it's called transactions.
All right I'm ready to start analyzing and summarizing the data for my
dashboard. Let's just take a quick look at it
so we can familiarize ourselves with what we need to build
The first values I need are for the headline figures
at the top of the dashboard.
Now one thing I want to point out
before we get started is that I've set
this page to use a specific color palette,
in this case the paper color palette.
And this alters the default colors to those that you see here.
And that's why you'll notice they appear different
to the defaults you might be familiar with.
You can even create your own custom color theme
and that will save you time when you're formatting your reports.
Let's go back into the file I'm working in.
You can see I've also selected the same color palette.
In this file here, it's set to paper.
You get a little preview as you hover over
the different color palettes.
You can play around with that in your own time.
Now I'm going to be using pivot tables for my analysis
because they're super fast to build
and when they're linked to data
in an Excel table like I have here they automatically include any
new data upon refresh.
So let's go ahead and insert a pivot table on a new worksheet. I'll
start by giving this pivot table a name we'll call it PT Headline
and this will help me identify it
later when I connect the slices.
I'll drag the pivot table field list over closer to the pivot table
So you can see it in context.
So here I want the category and I want the debit amounts.
Now I don't want the salary this is just looking at expenses
so let's filter that out.
I'm going to sort values in descending order so largest to smallest.
Let's go ahead and format the numbers.
I want them set to a currency data type
with no decimal places. This is going to feed through to my report.
Now let's rename this sheet Analysis.
Now I'm going to put all my pivot tables on this sheet
but when you create your own dashboards
I recommend you put each pivot table on its own sheet
so that there's no risk of them overlapping one another
because this will break your dashboard.
So we're ready to go to the dashboard sheet and bring in those numbers.
First of all I need some formatting. Now you can do this
with cell fill colors but I want rounded corners on my background.
So I'm going to use a shape.
So insert shapes and it's this rounded rectangle here
And let's just roughly draw it in.
I know the size that I want. So on the format tab
this rounded rectangle here.
I'm going to type in the size 1.93 by 40.72.
Okay let's go ahead and format the shape. So I'm going
to control one with the shape selected.
That opens the formatting pane. We can drag it out and move
it somewhere more convenient.
So in my fill I want a gradient fill and down here I want an extra stop
And we'll move it to the end
and this one here let's just make this a bit bigger put that in the
middle it's pretty much there anyway.
So I want to start with the dark purple which it is then move
through to this color and end again with the dark purple.
It's not going in the direction I want I
actually want it to go horizontally
so I want this and that's pretty good. Okay let's give it some text
Let's change the size of that font to 30 point.
Now I need some icons for the different expense
types to make the numbers quicker to interpret.
So on the insert tab we've got icons. These are
available in Excel 2019 onward
so you may not have them in your version. I want a house and
shopping. This will be for my discretionary expenditure
and I want a car for my transport
Support expenses.
And dining for my restaurant expenses
and I want one for charity and lastly one for my medical
expenses. So let's go with this one here.
You can see in the bottom corner I've got six selected so
let's insert those. With them all selected I'm going
to change the size to 1.2 and I'm going
to change the color to this pale purple.
That'll stand out better on the background.
So let's just roughly align them.
So I want one there, charity, and then we have medical.
Transport, discretionary. I'm not too worried about their position
because what I'm going to do is
on the home tab we're going to go into find
and select and select objects.
Then I'm just going to lasso them.
With them all selected I can now use the alignment tools.
I'm going to align them to the top
and then distribute them horizontally, just spaces them
evenly. Then with my arrow keys I'm just going
to nudge them up towards the top because I need
I need space below the icons for my text.
All right the next thing to do is to link some text boxes
with the values in my pivot table.
So we need to insert a text box
and we'll just do the living expenses first. So with the text
box selected in the formula bar I'm going
to equals go to my analysis sheet. Now if I select
the pivot table I get the get pivot data function.
Now a text box cannot be linked to a formula.
So I need to link it to the cell beside
and then just change that to column B.
That just gets around the Get Pivot Data formula
and links it to the correct cell in the pivot table.
So press Enter.
There's my value linked to the pivot table.
Let's center that vertically.
And we'll go ahead and get rid of the fill color
and the outline on that shape.
And then we can format the font in the same light purple.
Change the font size to 14.
Actually might make it 16. There we go.
All right. So that's that one done.
The next thing I need is a text box for the labels.
So let's do that. Another text box
underneath. This is my living expenses.
And again, we want to format the text box with
no fill and no outline.
On the Home tab, we'll format the font in the lilac color
and we'll make it bigger,
which means my text box needs to be bigger as well.
Okay, so there's the first two text boxes done.
Holding down shift to select them both and then holding ctrl and shift
I can left-click and drag to duplicate them
and I'm just going to do it for each of the categories
and then we'll edit them.
So this one is going to pick up cell B5
and you'll notice the formatting gets lost when I make that change
but I'll show you a shortcut on how
to put that formatting back in a moment.
This is 8, so now with the formatting gone,
I'm just going to select the one that's still formatted correctly,
double click the format painter, and now I can just simply tap it onto
those text boxes that need formatting.
Press escape to clear the format painter,
now I can just modify these, discretionary,
this one is transport, this one is dining out,
this one is charity, and this one is medical.
Okay I won't spend too much time on formatting
but let's just tidy those up a little bit.
Right using my lasso tool again select these ones
and it hasn't picked up those two that's fine.
You have to lasso the entire text box for it to work
so we'll move them over.
Okay that's good.
Now we just need one more text box to say spend year to date
and we'll resize that.
Okay let's take another look at the dashboard to see what's next.
So I need a pivot table for the donut chart. Let's tackle that one.
Go back into the file
and we're going to insert a new pivot table onto the analysis sheet.
I'm just going to pop it below this one here.
So I need account and the debit amounts.
Let's format the numbers. Currency with no decimal places.
Let's give the pivot table a name ptdonut
and we're ready to insert the pivot chart.
So under pie, you'll find donuts.
I'm gonna right click and hide all the fill buttons.
I don't want those.
And I'm going to move the legend to the bottom.
I'm going to add labels,
but I want to go into more options.
And here I want percentage and no leader line.
I wanted to wrap down onto a new line.
Now let's make the donut hole smaller.
So instead of 75, we'll make it 50%.
And let's change the colors.
I prefer this colour scheme.
We'll give it a new title, Total Spend by Account.
Now let's cut, Ctrl X to cut and paste it into the dashboard.
I need to resize it and we'll take it out to column J.
Let's close the pivot chart field list so we've got more room to work.
So what I want to do with this is also give it
a fill color for the background.
So again gradient fill.
This time I only want two gradient stops so let's remove one.
And we'll move that to the top.
Instead of the direction it's currently going in, I want it to go
vertically. And now I need to recolor the text so that it's readable.
Unfortunately, I have to
select them one by one and apply it.
All right, let's just nudge this up here. And we're going
to send it to the back so it sits underneath the header.
I want to change the chart borders.
So let's scroll down and expose the border options
and at the very bottom I
want rounded corners and that's just going
to be in keeping with the rest of
my rounded corners. It looks like this has a border on it
so let's get rid of the outline on that one.
That looks better. Probably nudge it up a little bit more.
Okay so we'll move this down slightly
and we'll make the donut hole or the
donut bigger. Now one more thing I need is the total
So let's insert.
A text box. It's going to sit in the middle of my donut.
I'm going to link it
using the formula
to the grand total here. Remember I can't select
the cell of the pivot table
so I'm going to select the one beside and type in B.
Let's change this to have no outline and no fill.
Go back here and make the font white.
Center
And make it a bit bigger.
Okay so there's my donut chart done.
it.
We'll go back and check what the next chart is.
So this one is for all expenses and net income.
This is actually two charts formatted to look like one.
So what we need is two separate pivot tables.
Let's go back and insert those on the transactions.
I want to insert a pivot table on my analysis sheet
and pop it in there.
So this pivot table is going to look
at the category type in the filter.
We want the date. Now notice the dates are grouped.
We can tell they're grouped because they've got the
plus beside the month name.
If I right click and group, I can remove the grouping on days
and just leave it grouped as months.
And then I want the sum of the debit amount.
Let's give this pivot table a name.
So it's my pivot table for my expenses column chart.
Let's format the numbers
so they feed through to the chart the way we want.
No decimal places and I'm ready to insert a clustered column chart.
That's the first one
by default anyway so let's pop that in.
I don't need my field list anymore so let's bring it
up here. I'm going to right click
and hide all the field buttons and then here I don't want the
legend there's only one series.
I don't want the grid lines because I'm going to add data labels.
Now notice the vertical axis starts at 2800.
It should always start at zero in a column chart.
So let's go and fix that.
In the axis options we're going to make the minimum zero and the
maximum 3200. Now I've done that.
I don't need to see the vertical axis because I've got my
data labels. Let's make these columns a bit wider
so we'll fix the gap width to 50%.
50%.
And we'll just move the chart title to the left.
I'm going to leave it as is.
See you later on, I'm going to create some dynamic chart titles.
The last thing I need to do is I want to get rid
of the shape fill and the shape outline
because these charts overlap one another.
So let's cut it out and paste it in here.
And we can make it fill the space.
And we'll just bring it down to about row 14.
Okay, that's better.
Now let's get rid of the grid lines on the dashboard.
And it look more like a dashboard.
Okay, that one's done.
Let's rinse and repeat for the net income chart.
So for that, I'm just going to copy this pivot table.
And let's bring the field list back.
Right click, show field list.
And here, I don't want the debit, sum of debit.
Instead, I want the amount.
And I don't need the category type anymore.
Let's change the grand total to net.
And we'll do the formatting with comma, separator, no decimal places.
And we'll give the pivot table a name, PTNetColumnChart.
Let's insert the column chart, get rid of the field list.
Okay, so again, right click, hide all the field buttons.
We don't need the legend or the grid lines
because we're going to have data labels.
We need to fix the vertical axis
so that it matches that of the expenses column chart.
That will just make them more easily comparable.
So for that we want a maximum of 3200.
And that changes the minimum so let's change that to zero.
All right now we've done that we don't actually need the vertical axis
so let's turn that off.
We'll make the gap width 50% like we did with the other chart.
Let's change the fill color to this pink
and let's also add a trend line.
And I'll just select that and make it pink as well so it's consistent.
All right this is our net income.
And we'll get rid of the shape fill and outline
and we'll ctrl x to cut it out and pop it on the dashboard.
So we'll make it the same width
and then I can bring it up so that it sits closer.
Bring that down so it's not overlapping
and in fact I'll make the font smaller in that.
I'll make the font smaller there so they're consistent.
So there's our two charts.
Now to make them look as though they're one we're just going to
surround them in a rounded corner rectangle.
Just draw it on and then we'll change the radius,
let's get rid of the shape fill and we'll give it
this pale purple color. Let's take a look at the dashboard.
The next chart is this one down here,
the line chart by expense category.
So let's go ahead and build the pivot table for that.
I'm actually going to copy this one and paste it here.
So bring back the field list and we'll just put it on the left side.
So the category type, the date, sum of debit,
but I want to see it by category going across
the columns. We'll get rid of the grand total,
right click, remove grand total. Now I want to
sort it in descending order based on the amount.
So in category, more sort options, I want to see
it in descending order based on the debit amount.
So now we have living expenses down to salary.
In fact, I want to make sure I'm only looking
at expenses, so let's filter it.
I don't need the grand total either so let's remove that.
All right we're ready to insert the line chart.
Let's do it from here.
You can do it from the pivot table menu or from the insert tab.
I'll right click and remove all the fill buttons or hide them.
Let's pop the legend at the top.
It's going to act like my chart title.
I'll just make the chart wider so that it's on one line.
Okay now we can't see the medical expenses
so I'm just going to select charity and then hold
down control and up arrow
and you can see now I've selected a series we can't actually see.
So let's control one to open the formatting.
We'll go into here and we'll add a marker one
of the built-in markers just the dot.
The reason we don't see the medical expenses is because
they're not in every month so there's no line for them
but with the dot we can see when they occur.
Right, let's select the chart, control X to cut,
go into the dashboard and paste it in.
Let's get rid of that field list
because we don't need that anymore and we'll resize it.
Now this chart we can actually set
the chart border to have rounder corners.
So in the formatting down the bottom we've got rounder corners.
Let's just change the color
so it's consistent with the others and that one's done.
Okay let's go back to the dashboard and see what's next.
So the next chart is the tree map
and tree maps suitable for displaying hierarchical data.
Let's go ahead and build that.
I'm going to insert a pivot table on the analysis sheet and we'll just
pop it beside. Now let me just check I didn't rename this pivot table
so let's do that first.
First, this is PT line chart,
and while we're naming, let's name this PT tree map.
So this pivot table is going to have category type,
the category subcategory and the debit amount.
Now I only want to see expenses
and I don't want this layout. I don't need
the subtotals so right click and turn them off
and then on the design tab we're going to change
the layout to tabular.
I also don't want the grand total so right click and remove that.
So there's the data that I need for my tree map.
Now if we try and insert a pivot chart
You'll see that we can't create this chart type
with the data inside a pivot table.
So we have to trick Excel into creating a tree map chart
and referencing the pivot table.
So I'm going to copy the data and then just alt E S V to paste as values.
Now that we have the data in the format we want
that is not in a pivot table,
we can insert the tree map. Let's make it a bit bigger.
Now all I need to do is right click the tree map, select data,
And edit the cell references so instead of referencing that sum
of debit I want it to reference that cell and this one here instead
of those numbers I want it to reference the pivot table
and we'll do the same for this one so instead of those labels we want
these ones and now you can see my tree map chart is referencing
the pivot table I don't need this data anymore let's delete that.
So let's go ahead and do some formatting here.
I don't want the legend and the data labels.
I want to add the value.
So that's my tree map done.
I'm going to leave the chart title for now
because like some of the other charts
this one's also going to have a dynamic chart label
which we'll do later.
So ctrl x and ctrl v to paste it in.
Let's make it a bit bigger
and we also want to hide
Turn off the outline. Now these tree map charts
do not have the option to give the border rounded
corners. So let's hide the outline. No outline.
And then we'll insert, actually what I'll do is
copy this shape holding down control and shift left click
and drag to copy it across. And then
I'll just resize it to surround the tree map.
So there's the tree map done. Let's go back to
Back to the dashboard and see what's next.
So last chart is the waterfall chart
and this illustrates how you get from the starting point
of your income to the amount you have left at the end of the period.
So let's go and insert
a pivot table. So insert pivot table on the analysis sheet
and we'll just pop it underneath
the tree map. Okay
so this pivot table is going to have the date in the filters, the
Category, down the rows, and the amount.
Let's give this pivot table a name, PT Waterfall,
and we'll format the numbers with no decimal places
and the comma separator.
Now, ideally, I'd have this sorted starting with salary
and then the biggest expense going down.
But if I sort these amounts in descending order,
from largest to smallest,
you see it starts with the smallest negative value,
and actually what I want is living expenses up here.
If I try and manually rearrange that it won't let me.
So let's undo that.
I'm just going to manually sort this so that salary's at the top.
You can go ahead and manually sort the others if you want.
I'm going to leave them at that for now.
Now just like the tree map, the waterfall chart
does not have an equivalent of a pivot chart
and we can see that if we go in here
we get the same message.
So just like the tree map we're going to copy the data
and paste it as values.
And then we can insert a regular waterfall chart
and then go about modifying it. Right click,
select the data and we'll edit where it's referencing.
So instead of there we're going
to reference the pivot table and then likewise
for the labels and okay. Let's change this to
net instead of grand total.
Let's make this chart bigger so we can see it more clearly.
Now because we have labels we don't need the grid lines
and we don't need our vertical axis.
We already know what the amounts are.
Now with a waterfall chart you need to tell Excel which
column is a total amount and that in this case is this net column here.
So selecting it once
and then once again with the left click selects just
that column right click set as total.
So we can see now that we came from $40,000 in salary
and we've had all these expenses
that leave us with a net of $9,355.
Now I want to change the colour style of this table
so on the design tab we're going to
change it to this fourth one here.
Alright, let's CTRL-X to cut.
And paste it in the dashboard.
Now just like the tree map we do not have the luxury of
being able to round the corners of this chart
so I'm going to hide the chart border. I'll
just resize it first
and then on the formatting tab we're going to get rid of the outline
and then I'll just copy the shape from above down
and resize that for this chart.
One more thing I want to do actually is put the legend at the bottom.
Now this one is also going to have a dynamic chart label
so I'm going to leave the chart title for now.
Okay we've created all the charts.
The last visual we've got left, let's just take a look
at the completed dashboard,
is this table at the bottom with our top five categories.
So let's go ahead and build the pivot table required for them.
On the analysis sheet I'm
going to go to the start and just copy
the very first pivot table we built to the headline figures.
So in here I don't want the grand total let's remove
that right click remove grand total
and then we can filter this for our top well I want top five
so let's go into top 10 and we'll
change that to five top five items based on the sum of debit amount
so there's our top five let's
select those cells and ctrl c to copy
and then in the dashboard in cell j30 I'm going to paste links
Now these cells here I actually want to move them across.
Let's format them, control one to format them with currency formatting
and no decimal places.
Now I actually need these numbers again
so I'm just going to reference them
and control enter. I'm not too concerned about
the formatting here that's fine because what
I actually want these for is to apply
some conditional formatting data bars.
Doesn't matter what color so select blue
and then let's go in and manage rules.
Double click on the rule here to edit it.
Here I only want to see the bar in this column
because I've already got the numbers here.
Let's change the colour to purple.
Click OK and OK and there's our table.
Let's give it a title.
Top five categories in selected period.
And we just need an underline for that.
I'm going to use a shape.
Just a line shape because I don't want
the line to go all the way across so if I put
a border on the cell it would go all the way across
and touch the charts on either side.
So I'm just holding down shift while I draw in the shape
and then let's change it to this
thicker one here.
We could probably nudge it across a little bit more.
So there we have all of our charts and our top five categories table.
We're ready to insert the
slicers which allow the user to interact
and filter for different categories and months.
So on the analysis tab I'm just going to select this pivot table here
which is the tree map pivot
table. It doesn't matter really
which one you choose as long as it's one that you want to
I want to filter with the slicer.
And then on the Pivot Table Analyze tab I'm going to Insert Slicer
and here I want Category
and Date. I want two slices, holding down shift to select them both.
I'm going to format them purple
and let's give the date slicer, go into the slicer formatting,
we'll give it two columns and we need
to do some more formatting in the settings.
So right click slicer settings at the very bottom
And here I want the header to say month or mth
and I want to hide items with no data. It's going
to hide these four down here that are paler purple.
Click ok. Those buttons will only come
back if we have data for them.
This one we need to do some similar changes so go into
the slicer settings. Here I want to hide items with no data.
That'll hide the salary item.
Okay let's select them both, holding down shift ctrl x to cut them out.
We'll paste them in the
dashboard. So this one's going to go on this side,
can be a bit narrower, maybe not that narrow,
and this one's going to go on this side
and we'll make it fit in the space. Alright so
I'll just select them both and then we'll do some alignment.
So we'll align them to the top
We'll move them up and then let's make the buttons a bit bigger
so they take up all the space.
Okay so there's our slicers. At the moment they're only connected to
the tree map chart. So what I want
to do is connect them to the other charts they need
to be connected to. I'm going to right click on the slicer,
go into report connections.
And this is when naming the pivot tables becomes really important
because now I know which pivot
table I want to connect to.
So this one for category I want to connect it to the expense
column chart and you can see up there I've missed one
of the pivot tables probably the last one I
built and also the tree map which is so click ok.
Let's go in and fix the name of this pivot table
There it is, PT top five and we'll go back and we'll finish with
The month slicer connections right click report connections.
This one's going to be connected to my top five tree map
and the waterfall.
So now when I select different periods you can see
the top five categories the waterfall and the tree map
update accordingly and likewise when I select different categories
the tree map and this total expenses over here
updates accordingly.
Now that we've got the slicers set up I'd like
to create custom chart labels that include
information about what's selected
and if we go back to the original dashboard
you can see as I select items in the slicer the label up here,
here and here, update
to reflect what I'm selecting. So let's go and set those up.
Go to the analysis sheet. Now the
first thing I need is a pivot table that displays
the items selected in the category slicer.
So let's insert a new pivot table,
insert pivot table onto our analysis sheet up here and this
is just going to contain the categories.
I don't need the grand total and let's add a filter in
for the category type so I only want the expense categories.
So this is my slicer pivot table,
let's name it PT slicer and we can now connect it
to the category slicer in the report connections.
We'll check the box for PT slicer.
So now if I select only three categories,
we can see only three are selected here.
So this pivot table now tells me
what I've selected in this slicer.
We can reference that with formulas
to build dynamic labels.
We need three separate labels.
We'll start with the label for the expense column chart.
So this is going to use the ifs function.
If you don't have ifs, you can use a nested if formula.
Works in the same way.
So first I want to check that this cell here is not blank.
This is the last category in my list. So if this cell is not blank,
it means that all categories in the slicer are selected.
So if it's not blank, then I want to return the text
all expenses. However, if it is blank,
then I need the next result returned.
So we simply enter true here.
And then what I want to return is a list
of all of the categories selected.
And I can use the text join function to do that.
Now I should point out that the text join function
is available in Excel 2019 onwards.
So if you don't have that you can't create
this dynamic label in the same way.
So the first argument is the delimiter.
Well I just want a comma and a space between each piece of text.
I want to ignore empty cells so we'll tab to select true.
And then the text is just the text in these cells here.
Close parentheses, press enter.
So currently I have all expenses selected. If we go and select just
three you can see it now lists them.
So I want to link this cell to my chart title. So selecting the
chart title in the formula bar equals
and then in the analysis sheet I simply want this cell here.
And now we have a dynamic chart label that updates
as I select different items in my slicer.
Okay the next label I want is my waterfall chart.
Now it is only filtered by the month slicer so I
only need a title that reflects what month is selected or months.
So let's go and build that.
We can grab that information from this cell here.
So in this cell here I'll write my dynamic label.
I want to start with the text expense by category
and I want to concatenate to that text some
Text that depends on what's selected in the slicer.
So we're going to use ifs again.
If you don't have ifs you can use nested if.
So if this cell here contains the text all
then I want it to return the text year to date.
However if this cell here contains the text
multiple items as it now does
then I want it to return the text multiple periods.
However, if it contains a specific month,
then I want it to simply return the value
in this cell here. So my last logical test is true,
close parentheses,
and now we have expense by category, multiple periods.
So we can link it to this chart.
Now, the waterfall and the tree map charts will
not allow you to link the chart title to a cell.
If I select the chart title, you can see, well you probably can't see
because I simply cannot click in the formula bar.
So what we need to do is insert text box over
the top of the chart title.
And I'm going to just make it the width of the chart.
And then in the formula bar, equals.
And we'll reference the cell here.
Now text boxes can't have a formula entered in them.
They can only reference a cell that contains a formula.
That's why we write a label in a cell
and then reference that cell in the text box.
So let's format the font in this chart title.
Give it a slightly dark gray color.
And we want to get rid of the outline on that text box.
So no outline.
Okay so there's the title for the waterfall chart.
We've got one more to do for the tree map.
It's actually a combination of chart titles.
The tree map is filtered by both the category slicer
and the month slicer. So it needs to contain this text
and what period is selected which we used in
the waterfall. So we'll start by saying include this information
and we'll add a hyphen and some
I'm just going to add in some spaces.
And then I'm just going to leave
that popped there. I want to go and copy
this. So and the if switch returns the period
and we'll just add it to this formula. So now we get,
and you can't quite see it there,
we get the category selected and the period.
If we just go and I'll grab this text box, paste it up here.
Let's change the reference
here from analysis 23 to this one. And now you can see it's picking up.
The category names and the period that's selected.
Let's format the font in this shade of grey.
Now when we select items in both slices,
you can see all of the labels are updating accordingly.
Okay, so we've built our dashboard and it's interactive.
The next best thing to be able to do is update it really easily.
So on the November data sheet I've got my November data. I've already
classified it into its categories so it's ready to go.
Obviously you're going to download your
data from your bank and then go ahead
and classify it but for now just to demonstrate
I'm going to copy this data
and then at the very bottom of the transactions table
On the very next row, I'm going to just control V to paste it in.
You'll see this blue marker here in the bottom right
of the table will extend the table range
down to include my new data.
So there's my new bottom of the table.
And that means all of the pivot tables here
in my analysis sheet that are linked to that
table will automatically pick up that new data.
So if we go to the dashboard
and you keep an eye on these charts here, which have the
month listed.
On the data tab I can simply click refresh all.
So one button and my dashboard is now updated to
include my November data. I haven't had to do any more work.
Now one thing I have just noticed that
I forgot to do is set the fields for the waterfall chart
to show items with no data. We can see here
with the chart filtered to show only June's data
that there's no item for medical expenses.
So the next column has now moved across to the left
and it's not displaying as a total column.
So let's go and fix that in the Analysis tab.
All we need to do is select a category field,
right-click, go into Field Settings.
And then on the layout and print tab we want to check the box
show items with no data. Click OK.
You can see now we have the medical item
even though it's empty and the net value is now in cell S35.
We go back to the dashboard you can see our net column is now correctly
displaying. And if I select for example November's
data, which also doesn't have any medical
expenses, it's correct. If I clear all the filters we can now see
I see some medical expenses.
I hope you enjoyed this tutorial
and found some techniques and ideas you can
use to build your own dashboards.
You can download the file for this lesson
containing the data and completed dashboard from the link here.
If you like this video please give it
the thumbs up and subscribe to our
channel for more and why not share it with your friends
who might also find it useful. Thanks for watching.