YT YouTube2Text

YouTube2Text → Transcripts → Build an Interactive Excel Dashboard Using PivotTables

Build an Interactive Excel Dashboard Using PivotTables — Transcript

by Marg Analytics · 6,376 words · 983 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:01Hi, welcome back to Mark Analytics. In
0:04this video, I'll show you how to turn
0:06raw sales data into an interactive Excel
0:09dashboard like this using pivot tables,
0:12pivot charts, slices, and timeline.
0:14We'll go step by step from understanding
0:17the business question and reviewing data
0:20all the way to build the final
0:22dashboard. And this is going to be a
0:24very long tutorial video, so I've added
0:26chapters below if you want to jump to
0:29specific section. Without further ado,
0:32let's get into it.
0:36Let's start with the scenario. You're
0:38now working in a company that sells
0:41different products across multiple
0:43regions, sales channels, and also
0:45customer type.
0:48and you're given a sales data set and
0:51your manager wants a simple dashboard to
0:54review the overall sales performance
0:57without looking through thousands of
0:59rows of raw data. So this dashboard
1:02should help them to quickly understand
1:05the total revenue, the total orders, the
1:09quantity sold, average order value,
1:13monthly revenue trend, regional
1:16performance,
1:18product category performance, sales
1:20channels contribution, and also the top
1:23products by revenue.
1:28Now let's review the raw data. Before
1:31creating any dashboard, it's important
1:33to understand what data that we actually
1:36have. So first we have the order ID
1:40which identifies each order. Basically
1:43each row represents one sales order. And
1:48next we have the date which tells us
1:51when the order happened. And we have the
1:54region which tells us where the sales
1:58came from. We also have the sales
2:01channels such as online, retail,
2:05marketplace, corporate sales. And this
2:07tells us which channel this order came
2:10from. Then we also have the customer
2:13type which tells us whether the customer
2:16is new or returning customer.
2:19And we have the product category and
2:22product for the product detail.
2:25And then saleserson which tells us who
2:27sold each order. And lastly for
2:31quantity, unit price, discount and
2:34revenue is for the sales number. So
2:38basically quantity is the quantity sold
2:41and unit price is the unit price for the
2:45product and the discount is if this
2:48order has any discount and then the
2:51final revenue.
2:55Next I also want to quickly just check
2:58the data if it's suitable for pivot
3:01tables. So first there shouldn't be any
3:05blank column headers in this data
3:08because pivot tables require a proper
3:12headers. So in this data set there is no
3:14blank headers. So let's move to the next
3:17one. We want to check if there are any
3:21blank rows in this data set. So one
3:24quick way to check this is to just click
3:27on the header or the first data cell and
3:31then on your keyboard press control down
3:34and if there is a blank row inside the
3:37data set Excel will stop before the
3:40blank row. So in this data set I do not
3:43have any blank row. So after I press on
3:47control down it goes to the last row in
3:50the data. And another way to check blank
3:53rows is to turn on the filter. So to
3:56turn on the filter just press on control
3:58shift L on the keyboard and there is
4:00this filter. Just click on the down
4:03button and see if there is blank in the
4:06filter area. So in this data set there
4:09is no blank row. And next the date
4:12should also be formatted correctly. And
4:14a quick way to check if the date is
4:17formatted correctly is to check on the
4:20number format in the home tab. Or you
4:24can click on the down button at the
4:26filter and see if Excel groups the data
4:31to year, month and the day.
4:35And next for text column like region,
4:38sales channel, customer type, product
4:41category and product and as a saleserson
4:44I would quickly check if those data are
4:48consistent like the labels and so on if
4:50there are any typo errors and things
4:52like that. So the spelling for the
4:54region looks consistent and correct.
4:58And same goes with the sales channel
5:01and customer type, product category
5:07and product
5:09and also the saleserson.
5:13And lastly all the numeric data like
5:15quantity, unit price, discount and
5:18revenue. They should be numeric. To
5:22check this, we can just select the data
5:24and take a look at the number format
5:26over here.
5:28And so the unit price is already
5:30formatted as
5:32number and the discount is formatted as
5:36percentage and the revenue is formatted
5:39as number. And another way is to check
5:42the alignment. Usually numbers are right
5:45align like this and text is left align.
5:49For more details about numbers stored as
5:51text, you can refer to my previous
5:53video. I'll leave the link in the
5:55description. And for this sales data set
5:58in this practice file, the data is
6:00already clean, so we can focus on
6:02building the dashboard. But in real
6:05work, this step can take much longer
6:07time. So you may use formulas for
6:10checking data quality issues or use
6:13pivot tables for exploring whether the
6:16data looks reasonable or not. Now that
6:20we have reviewed this data, next is to
6:22convert this data into an Excel table.
6:25This is important because Excel table
6:28makes the data easier to manage. And to
6:30convert this data into Excel table, just
6:34click anywhere inside the data set and
6:36then press CtrlT on the keyboard. And
6:39there's this popup that ask where is the
6:43data for your table. So just make sure
6:45you put in the correct range and then
6:48whether your data has headers or not.
6:51Then click on okay. And this instantly
6:54convert it into table. So I would also
6:57want to change the design.
7:01And next is to rename this table.
7:05So I'll just rename it to sales data.
7:09Renaming table is very important because
7:11it's easier to recognize the data later,
7:15especially if you're handling a lot of
7:17data in one workbook.
7:20Now that we understand the available
7:22data, let's connect the dashboard
7:25requirements that I've mentioned earlier
7:27here to the elements that we're going to
7:30build.
7:32All right. So these are the dashboard
7:34requirements and instead of just
7:37creating pivot tables randomly, I want
7:40to turn this requirement into a simple
7:45dashboard plan.
7:47So let's take a look at the dashboard
7:49element and also the column use. For the
7:52first one, total revenue, we'll create a
7:54pivot table with the total revenue using
7:57the revenue column. And for the total
8:00orders is similar to the total revenue.
8:03We'll be creating a pivot table with
8:06total orders using order ID.
8:10and the total quantity sold using the
8:13quantity column
8:16and the average order value. We'll be
8:19creating a pivot table showing the
8:21average order value and we will be using
8:24revenue and also the order ID. But in
8:27this case because each row represents
8:29one order. So we will not be using the
8:33order ID. We'll just average the
8:35revenue. And next is the monthly revenue
8:39trend. We'll be creating a revenue by
8:42month chart using date and revenue
8:45column. And next regional performance
8:48which is the revenue by region using
8:52region and revenue column. And product
8:55category performance. We'll be creating
8:58a revenue by product category chart
9:00using the product category and also the
9:03revenue column. And then sales channel
9:06contribution. We'll be creating a chart
9:09showing the revenue by sales channel
9:11using sales channel and revenue column.
9:15And lastly, top products by revenue.
9:18We'll be creating a chart showing the
9:19top five product using product and
9:23revenue column. So this table gives us a
9:26clear directions and every pivot table
9:29and pivot chart that we are going to
9:31create later should connect back to one
9:33of these requirements.
9:37Now let's move on to creating pivot
9:39tables. So click anywhere inside the
9:42data set then go to insert then click
9:44pivot tables. And now I'm going to
9:47choose existing worksheet because I'm
9:49going to place all the pivot tables in
9:52this supporting pivots. And then I'm
9:55going to select an address then click on
9:58okay.
10:00All right. So this is the first pivot
10:02table and we are going to create total
10:04revenue. So just drag revenue to the
10:07values area and now we've got the sum of
10:10revenue which is also the total revenue.
10:14And let's also format this numbers so
10:16that it's more readable. So click on the
10:20down button beside the sum of revenue.
10:22Then go to value field settings and then
10:25click on number format. And I'm going to
10:28create a custom format because I wanted
10:31to include both the dollar sign or the
10:34currency sign with the M for millions
10:37and K for thousands.
10:41All right. So
10:44open bracket then more than equals to a
10:47million
10:51then close bracket then I want it to
10:53have a dollar sign and the number should
10:57be having
10:59th00and separator
11:02and I want it to show two decimal places
11:06and then
11:08two commas for
11:11a million One comma means dividing by a
11:14th00and. So two commas means dividing a
11:18th00and two times. Basically it means a
11:20million. Then open quotation mark m and
11:25then close quotation. So this is if the
11:28values is greater than a million then it
11:31will use this formatting.
11:34Then semicolon.
11:36Next is open bracket for greater equals
11:41to a th00and.
11:44So a dollar sign
11:47and then numbers
11:51with two decimal places
11:54and one comma for a th00and for dividing
11:58by a th00and then k
12:01then semicolon.
12:03And if the numbers are less than a,000,
12:06then I want it to just show
12:09numbers and two decimal places.
12:12And let's not forget the dollar sign as
12:14well. And yeah, this is it. And now
12:18let's click on okay. And click on okay
12:21again. And now I got my 2.45 millions.
12:26And now let's move on to the second
12:28pivot table, the total orders, which is
12:31this. So let me just copy and paste this
12:36pivot table and let's remove the revenue
12:40and put in the order. So you notice that
12:44it changes to count of order because the
12:48order ID is not formatted as number. So
12:51the default calculation it's count. So
12:54this is what we want the total orders.
12:58And let's copy this and paste again. and
13:02let's create total quantity s
13:06let's remove the count of order ID and
13:09let's drag in quantity to the values and
13:12this is the sum of quantity
13:15and let's create the fourth pivot table
13:18remove the count of ID and let's create
13:22average order value right so let's drag
13:25in the revenue to the values area again
13:28and this time instead of sum we would be
13:31using
13:32average
13:34and there is no other special
13:36calculation in this because each row
13:39represents an order. So basically the
13:43average it means dividing by each order.
13:46So we'll just use average here then and
13:49I'll just click on okay. And let's also
13:51format this number as well using the
13:54same formatting that we have here in the
13:56sum of revenue. So let me just go back
13:59to the value field settings number
14:01format and let's just copy this
14:06and then paste here in the number
14:08format.
14:14Then click on okay. Then click on okay
14:16again. And you notice that over here
14:19it's 1.23K
14:22because of our number format that we
14:24have done just now. All right. And let's
14:27move on to the revenue by month.
14:32So let me just copy this and paste
14:34below.
14:37Revenue by month. All right. So this is
14:39the revenue. And let's just drag in the
14:42date. And let's remove the days and the
14:45date because we only need the month.
14:51And notice that the revenue has been
14:53formatted nicely because we have copied
14:56the sum of revenue over here and paste
14:58it to use it over here.
15:02Then let's move on to
15:05revenue by region and let's copy this
15:11pivot table again and paste
15:14and let's write in the region.
15:17All right. So this is the revenue by by
15:20region.
15:21And let's do the next one. Revenue by
15:24product category. So let's just drag in
15:27the category to the rows area. And now
15:30we got all the product category and the
15:34revenue.
15:36And next is revenue by sales channel.
15:40Let's copy and then drag in sales
15:43channels. And lastly is top five
15:46products by revenue. And let's paste
15:49again.
15:51And this time, let's drag in the
15:53product. All right. So, there are a lot
15:55of products here, but we only need the
15:58top five product. So, what I can do here
16:01is to click on the down button at the
16:04row labels and then go to value filters
16:07and click on top 10.
16:10And here I'll just type in the five and
16:13five item by the sample revenue. Then
16:15click on okay. All right. So these are
16:18the top five products by the sum of
16:22revenue. But notice that the values are
16:25not arranged in any order. So it's based
16:29on the alphabetical order. So let's also
16:32sort this descending order by the sum of
16:35revenue. Then click on okay.
16:38Now that we have done creating all the
16:40pivot tables, let's move on to creating
16:43pivot chart. And let's start by creating
16:47the revenue by month. So click inside
16:50the pivot table. Then go to pivot table
16:54analyze tab and then click on pivot
16:57chart. And over here you can choose any
17:01chart that you want to insert. And
17:04another way is to go to insert then go
17:06to recommended charts. And it's the
17:09same. You can choose the charts that you
17:11want to insert. For revenue by month,
17:14I'm going to use line chart. Then click
17:16on okay. And here is the line chart. And
17:20let's format this chart so that it's
17:23more readable and cleaner and more
17:25informative.
17:26So first I'll hide all the field
17:28buttons. And let's also remove the
17:31legend.
17:32And let's add data labels.
17:35And as you can see the data labels are
17:38not really readable. So click on this
17:40button beside the data labels. Then go
17:42to more options
17:44and let's format the numbers.
17:48So click on numbers and over here I'll
17:51choose custom format and I'm going to
17:54remove the dollar sign and also the two
17:57decimal places.
17:59So over here I'll just remove the dollar
18:01sign
18:04and the two decimal places.
18:10Then click add.
18:12And you can see that it's formatted
18:14nicely. And I also wanted to add a
18:18background. So click on solid fill. And
18:21over here let's choose white color and
18:2320%
18:25transparency.
18:28And let's also bowl the data labels. So
18:31go to home and click on the ball. Or you
18:34can press on CtrlB to bold the data
18:38labels. And next let's also format the
18:41number format in this axis.
18:45So go to
18:47click on this and then go to numbers and
18:51over here I will just choose the one
18:53that I've modified for the data labels
18:56over here. So click this one and you can
18:59see that the access numbers has been
19:01formatted nicely. And let's also add
19:04access titles.
19:08So let's rename it.
19:11And let's also rename
19:13the title of the chart.
19:16And I'm going to bold the title.
19:19And let's also change the color to
19:21something
19:23darker.
19:26And let's remove the grid line as well.
19:30All right. So now we've done formatting
19:31this chart and let's create the next
19:35pivot table which is the revenue by
19:37region. So click inside the pivot table.
19:40Then go to pivot table analyze tab. Then
19:43click on pivot chart. And I'm going to
19:45use clustered column chart. So click on
19:48okay. And I'm going to format this chart
19:52similar to the revenue by month.
19:56[music]
20:06And next, let's create this revenue boy
20:09category. And I'm going to use a cluster
20:12column chart as well.
20:19[music]
20:27And for this revenue by category, let's
20:29also sort this from the highest revenue
20:33to the lowest. To sort this product
20:36category, I will just click inside this
20:38pivot table and then sort it over here.
20:41So more sort options, then go to
20:43descending order by the sum of revenue,
20:45then click on okay. And you can see that
20:48the chart updated as well because the
20:51pivot chart and the pivot tables are
20:53connected. So anything that you do to
20:56the chart, it will affect the pivot
20:58tables and anything you do to the pivot
21:00table will affect the chart as well.
21:04And next let's move on to creating pivot
21:07chart for revenue by channel. And for
21:10channel I'm going to use a donut chart.
21:13So same click inside the pivot table. Go
21:16to pivot table analyze tab. Then click
21:18on pivot chart
21:21and I'm going to choose this donut
21:23chart. Then click on okay.
21:27So let's format this chart as well.
21:37[music]
21:40And lastly, let's create a pivot chart
21:42for revenue by product. And these are
21:45the top five product.
21:50And for this top five product, I'm going
21:52to create a bar chart. So let's create a
21:55clustered bar. Then click on okay.
21:59And this is our chart.
22:04And let's format it.
22:07[music]
22:12All
22:16right. Now I've done formatting this bar
22:19chart. But notice that the chart is not
22:21arranged from the highest revenue to the
22:24lowest. But in our pivot table, we have
22:26already sorted descending order by the
22:28sum of revenue. So to solve this, what
22:32we can do here is to click at this axis
22:35and then go to the axis option and click
22:38on categories in reverse order. And now
22:42the product are arranged descending
22:44order from the highest revenue product
22:46to the lowest revenue product.
22:50Now we have done creating all the pivot
22:52charts and pivot table. Next let's
22:55rename all the pivot tables and pivot
22:57chart.
22:59Just click inside the pivot table. Then
23:02go to pivot table analyze tab. And over
23:04here I can rename the pivot table. So
23:08just rename anything that you can
23:10understand so that it's easier to work
23:13on all these tables later.
23:16[music]
23:31And remember to also rename the pivot
23:33chart as well.
23:44>> Now we have done renaming all the pivot
23:46tables and there's a pivot chart and now
23:48let's insert slicer.
23:52So just click inside any pivot table and
23:55then go to pivot table analyze tab and
23:58over here I can insert slicer
24:01and let's insert slicer for the region
24:05and product category
24:07sales channels and also
24:11customer type. All right. So these four
24:13slices then click on okay. And now we
24:17have the four slices.
24:20And for now these four slices are for
24:22the sum of revenue. So if I click on any
24:26of these slices you will see that the
24:28sum of revenue changes but the others
24:30remain the same. So what I need to do
24:33now is to go to report connections to
24:37connect all these slices to all of the
24:40pivot tables and pivot chart.
24:43So click in any of the slicer then go to
24:46slicer tab then click on rep connections
24:51and over here you can see that it is
24:54connected to the first pivot tables and
24:57I want it to connect to all of them and
25:00this is why naming the pivot tables and
25:02pivot charts are very important because
25:04sometimes you want your slices to only
25:08be connected to a particular pivot
25:11tables And with the name you know that
25:13which one that you want to select. Then
25:17click on okay. And do the same for the
25:20other slices as well.
25:25All right. And now you can see that when
25:27I click on the slices all of the pivot
25:30tables and the pivot chart changes.
25:35And next let's also insert timeline.
25:39So timeline works similar to the
25:42slicers. Just click inside the pivot
25:44table. Then go to pivot table analyze
25:47tab. Then it's beside the insert slicer.
25:50You'll see this insert timeline.
25:53And there's only one date in my data
25:56set. So just click on it. Then click on
25:58okay.
26:01And for the timeline, I'm going to
26:04create four timelines for the year,
26:07quarters, month, and days. And
26:12why I do that? It's because later when
26:16we have done creating the dashboard,
26:18we're going to protect the worksheet for
26:21the dashboard. And when we protected the
26:25worksheet,
26:26this selection will be locked and it
26:29cannot be changed. So I'll set the
26:31period now so that after the sheet is
26:34protected we can still filter all of
26:36this period. And next is to set the
26:39report connections for the timelines to
26:41be connected to all of the pivot tables.
26:44So what I need to do now is to click on
26:48this timeline then go to the timeline
26:50tab. Then over here you see this report
26:53connections
26:54and similar to slicer just tick on the
26:58pivot tables that you want it to be
27:00connected
27:02then click on okay
27:04and one thing that it's different it's
27:07that once you set for one of this
27:10timeline because they all of this
27:12timeline slicer they are the same
27:16timeline so I do not need to set the
27:19report connections for the other
27:21timelines. They are all the same. They
27:23are all connected to all of the pivot
27:24tables that I've set just now. Next,
27:28let's create a new worksheet for the
27:30dashboard. And let's rename it to be
27:33sales dashboard.
27:36And before moving all the pivot charts,
27:39timelines, and slicer, I'm going to
27:41decide on the dashboard canvas. So, let
27:45me go to review tab and then close the
27:47formula bar and the headings.
27:50And I'm going to highlight one of this
27:52cell
27:54and I'm going to click on this down
27:56button and go to full screen mode. And
27:58this highlighted cell will be my
28:00reference on where my dashboard should
28:02be in.
28:04So I want my dashboard to be within this
28:09yellow highlighted cell. Basically these
28:11highlighted cells are my reference.
28:14And let me set this to always show
28:17ribbon again. and then turn on the
28:20formula bar and the headings. And let's
28:22set the first three rows to be the
28:26title. So, let's highlight this to be a
28:29dark blue for the title.
28:33And let's go back to the supporting
28:35pivot. And I'm going to move all this
28:39timeline slicer and the pivot chart to
28:41the sales dashboard worksheet. And I'm
28:43going to press Ctrl to select all of the
28:45timelines and slicer.
28:51And I'm going to press on Ctrl X to cut
28:54all the slices
28:56to the sales dashboard. And Ctrl + V to
28:59paste all of this slicer and the
29:02timelines. And next, I'm going to do the
29:04same for the pivot chart as well. So,
29:07Ctrl to select all of the pivot charts.
29:12Then, Ctrl X
29:15and Ctrl V to move all of the charts to
29:18the sales dashboard worksheet. And let's
29:21arrange the slicer first. So, I'm going
29:25to put my slicer to the left side of the
29:27dashboard.
29:29So, let's just move all the timelines
29:32away first.
29:37And just make sure that everything is
29:39within this highlighted cell.
29:42And next, I want to make sure that all
29:44of the words on the slicer can be seen.
29:47So over here, this product category is
29:49too small for the word category. So I'm
29:52going to make it a little bit bigger.
29:55And click on the slicer here. You'll see
29:58the width. Let's set it to 5.6.
30:03And let's do the same for all of the
30:05slices.
30:07And next, I'm going to just quickly
30:09align all of the slicer.
30:12So, Ctrl N, select all of the slices,
30:15then go to slicer, and then over to
30:17align, I'm going to align center and
30:20distribute it vertically.
30:25All right. And next, I'm going to place
30:28all the timelines to be at the top of
30:31this dashboard.
30:32So, let's go with the year first and
30:36then quarters,
30:38months, and days.
30:43And I'm going to select all of the
30:45timelines and slicer and then align it
30:48to the top.
30:52And next, select all the slicer
30:57and distribute it horizontally.
31:01And I also want to just quickly rename
31:04this caption on the timeline. So this
31:08should be year
31:10and this should be quarters
31:15and this should be month
31:20and this is the days.
31:24And next I'll arrange the pivot chart.
31:26So there are five charts over here. So,
31:29I'm going to put two and three at the
31:31bottom. And I'm going to put the revenue
31:34by month and the revenue by region side
31:37by side and revenue by product category
31:41and sales channel. And this top five
31:43products will be at the bottom. And I'm
31:46going to make a space for the KPI cards.
31:48So, I'm going to just leave a space
31:51roughly like this over here. And first
31:53I'm going to lengthen this
31:56pivot chart.
31:59And then I'm going to go to format to
32:01see the width. So let's set this to 42
32:04cm and 7.7 cm for the height. And this
32:10length is going to divide by 2 for the
32:13revenue by month and the revenue by
32:15region. And 42 / 2 is 21. So, my top is
32:21going to be 21 cm. And same goes with
32:25this one.
32:34Next, I'm just going to arrange this
32:37align to the left
32:40and revenue by region align
32:44to the right.
32:48And next I'm going to select these two
32:51chart and align middle.
32:56All right. And next is the three pivot
33:00chart below. And since now we know that
33:03the length is 42. So all of this chart
33:06is going to be 42 / by 3 which is 14 cm.
33:12So click on the pivot chart then go to
33:14format and then set this to 14 cm and
33:18the height is 7.7
33:21and same goes with the other charts.
33:25All right. And next let's align to left
33:30and align to the right for the top five
33:32product.
33:37And make sure to also align to the
33:41bottom as well with the slicer
33:49[music]
33:56[music]
33:58and then distribute this horizontally.
34:03And since this customer type slicer is
34:06shifted just now when we align to the
34:08bottom. So I'm going to just distribute
34:11this slicer vertically again.
34:18And let's move these two charts a little
34:20bit down because I want to leave the
34:22space for the KPI cards. So select both
34:25of these chart and on the keyboard press
34:28the down button. So we do not shift both
34:31of this chart to the left or the right.
34:34So we are just moving down.
34:37And now let's create the KPI cuts. So
34:40this KPI cut is for this pivot tables.
34:44The total revenue, total orders, the
34:47total quantity sold and the average
34:50order value.
34:52So first I'm going to insert a shape. Go
34:55to insert and then go to shape. And
34:57let's choose this rounded rectangle.
35:03And I'm going to change the design to be
35:06this one. So a white background and a
35:08light blue outline.
35:10And let's set this height to be 3 cm.
35:15And because there are four of this kpa
35:17card, so 42 / by 4,
35:21it's around 10.5 cm.
35:25And I want it to be a little bit more
35:28spacing. So I'm going to set it to 10
35:30cm.
35:33All right.
35:36And next I'm going to insert text box
35:39for
35:41the value.
35:44And let's put it no outline and no fill.
35:49And this text box is going to show the
35:52value in each of this pivot table. So to
35:55show the value I have to go to the
35:57formula bar and type in equals
36:01and then select on the pivot table then
36:04press on enter and you can see that this
36:08formula
36:10is missing a range reference or a
36:12defined name. All right. Basically if
36:15we're pointing at a pivot table this get
36:19pivot data formula will be entered
36:21inside automatically. So we do not want
36:23that. So I'll just remove this part of
36:26the function
36:29and then also remove the close
36:31parenthesis. So this is the supporting
36:34pivot which is the worksheet name and
36:37then B19. But make sure you're pointing
36:40at the correct cell. Actually B19 is the
36:44header for the pivot tables and the
36:46value is actually in B20.
36:49So,
36:51we'll just put this B20. Then press on
36:53enter. And now we have this value. And
36:57let's format it.
37:02[music]
37:03And I'm going to set this to
37:05maybe 38.
37:11And bold it. And let's change the color
37:14as well.
37:18And I'm going to duplicate this text box
37:20by pressing Ctrl D. And this will be my
37:24title for the KPI card. So this is the
37:27total revenue.
37:31And let's set this to a black color
37:33text.
37:36And also reduce the font size.
37:39All right, let's set it 20.
37:43And I'm going to align both of this text
37:46box to the center.
37:52All right. I'm going to shift it a
37:53little bit here. All right.
37:58And next I'm going to insert icon. So go
38:01to insert and then press on icons.
38:05And this might take a little while to
38:07load. And over here you can search all
38:10kind of icon that you want to put in. So
38:13I'm going to put a dollar sign. Then
38:15select it and press on this insert.
38:19And here I have my icon. And I'm going
38:23to just fill in a different color.
38:28So I'm going to put the same color as a
38:30value over here. And I'm going to resize
38:32it to be a little bit smaller. So to
38:35resize it without changing the shape,
38:39just press on control and then resize
38:42it. And you can also set it over here as
38:45well. So let's put maybe 1.7
38:51or maybe 1.8.
38:55All right. Next, I'm going to group
38:56these two text box together.
38:59And let's
39:02shift it to the middle a little bit.
39:08I think I'm going to make this
39:11icon a little bit bigger,
39:152 cm.
39:18And next, I'm going to align this icon
39:20and the two text box
39:24to the middle. And next, I'm going to
39:27group this text box and the icon
39:30together. And then align with the shape
39:33behind. Align center and align middle.
39:38Right, I think this is good enough. And
39:40let's group these two together as well.
39:44And I'm going to press on Ctrl D three
39:46times for the total orders, the sum of
39:50quantity, and also the average order
39:52value.
40:02And next what I need to do is to double
40:05click inside this text box and to change
40:07the cell reference.
40:09So the total orders is in E20.
40:14So I'm going to change this to E20. And
40:17once I change it, I will need to
40:19reformat this text again. So I'm going
40:22to do this three times for the quantity
40:24sold and also the average order value.
40:26And I'm going to fast forward this part.
40:32And to change the icon, I can just right
40:34click at the icon and then change
40:36graphic and from icon.
40:40And I'm going to do the same for this
40:42two as well.
40:53And since this text is a little bit too
40:55big, so let's set it to a little bit
40:57smaller,
41:01maybe 16. [music]
41:10And let's do [music] the same for the
41:11rest.
41:14All right. And last part is to align the
41:16shape. So first I'm going to align to
41:19the left
41:22and then align [music]
41:23to the right.
41:30And then next select all of these KPA
41:32cards
41:36and align
41:39middle and then distribute it
41:42horizontally.
41:56All right. And next, we're going to
41:57format the title. I would like to just
42:00put a thing called
42:03date updated through.
42:08And let's set this to a white text.
42:12And then I'm going to use the max
42:15function and go back to my sales data
42:18and select the entire date column. Close
42:21parenthesis
42:24and change this to a date format
42:27and white text. Basically what this does
42:30is to tell the audience or the user that
42:33the data or the date for this dashboard
42:36it's updated through this date.
42:40And let's also add an icon.
42:44Let's change it to white color and
42:48resize it.
42:53[music]
43:03Let's also create a title [music]
43:05for this dashboard.
43:16So I'll just merge and center this part
43:18of the cell and let's name it as sales
43:22dashboard.
43:33[music]
43:40And let's also insert a logo. So go to
43:43insert and then go to pictures. And I'm
43:46going to place over the cells.
43:53Right. So this image is a little bit too
43:55big. Let's set it to maybe 2 cm or maybe
43:59one.
44:04Right. So let's place it over here.
44:08And we're almost done. Let's just remove
44:10this cell reference that I have put in
44:13just now.
44:15So I'm going to clear all.
44:17And same goes with this this one.
44:26All right. And the last part is that I'm
44:28going to protect this worksheet and I'm
44:31going to close the formula bar, the
44:33headings and also make this to a full
44:35screen mode. But before that, I'm going
44:38to lock all the slicer and the
44:40timelines. So I'm going to click in this
44:43slicer and then go to size and property.
44:50And over here I'm going to click on the
44:53move and size with the cell. And I'm
44:55going to uncheck the lock. And then for
44:58the position and layout, I'm going to
45:01disable resizing and moving.
45:04And do the same for all the slices.
45:10And next, we're going to set the same
45:11thing for the timeline. So, right click
45:14and then go to size and property. But
45:16notice that we can't disable sizing and
45:18moving. So, I'm going to just uncheck
45:20the lock and then click on the move all
45:23size with cells
45:29[music] and then do the same with all
45:31the timelines.
45:33And the last part, let's go to view and
45:37close the formula bar and the headings
45:40and then go to review to protect this
45:43worksheet.
45:45So I'm going to uncheck the select lock
45:48cells and select unlock cell. And over
45:50here I'm going to check this use pivot
45:52table and pivot chart. And you can put
45:56in password if you want to. Then click
45:58on okay. And lastly I am going to close
46:03the grid lines as well.
46:07And then let's go to the full screen
46:09mode. And this is our Excel dashboard.
46:13And you can see that I could not move
46:15all the worksheets. So this dashboard is
46:17protected. So your users can use the
46:20dashboard while not accidentally move
46:22your pivot chron and things like that.
46:24But one thing to note that because for
46:27the timeline we couldn't really disable
46:30the movement. So you can actually
46:33accidentally resize the timelines. And
46:36this is the flaw of this Excel
46:38dashboard. And I think up until now
46:40there's still no solution for this
46:42issue. But if anyone of you know how to
46:44solve this definitely comment down below
46:47and let me know. But basically this is
46:50it. And let me just save this Excel
46:54dashboard.
46:56Now that we have completed the
46:57dashboard, let's do a quick final walk
47:00through. At the top we have the title
47:02and the data updated date. On the left
47:06side we have the slices for regions,
47:08product category, sales channel, and
47:11customer type. And at the top we also
47:14have the timelines for date filtering.
47:17And the KPI section summarizes the total
47:20revenue, total orders, total quantity
47:23sold and average order value. The charts
47:26below helps us to understand the revenue
47:28by month and compare revenue by region,
47:32category and sales channel and also
47:35identify the top five products by
47:37revenue.
47:39And if I select a slicer, for example, I
47:42selected the east region. So this is the
47:45number for the total revenue for east
47:48region, the total orders. And I can also
47:51have a look at what are the top five
47:53products by revenue for east region. And
47:56if I want a more detailed look, I can
48:00also filter a particular month. For
48:02example, if I click on February, then
48:05this is the total revenue for February
48:09for East region. And to clear filter, I
48:12can just click on the clear filter at
48:15the top right of the slicer and the
48:17timelines to clear this filter. And I
48:20can do multiple filtering as well. For
48:23example, if I click on south region, I
48:26can also click on the computers product
48:29category. So these all the details for
48:31the computers product category for south
48:35region and if I want to have a look at
48:37the second quarters I can do that as
48:40well. So instead of just looking through
48:44the raw data or opening multiple pivot
48:46tables one by one, the user can use this
48:49dashboard to quickly review the overall
48:52sales performance, compare different
48:54regions and identify where to
48:56investigate further.
49:00Before we end, I just want to say
49:02something. In real work, building a
49:04dashboard is not just about inserting
49:06charts or making things look nice. A big
49:10part of the work is to [music]
49:11understand what the dashboard needs to
49:14answer. So if you're building a
49:16dashboard for a team, [music] take time
49:18to understand what they need, what
49:20business question they want to answer,
49:23and what decisions they [music] need to
49:25make from this dashboard. Also, if this
49:28video is around an hour, it doesn't mean
49:30that I built [music] this dashboard
49:32perfectly in an hour on the first
49:34attempt. There are a lot of thinking,
49:37[music] testing, adjusting and designing
49:39behind the scenes. Sometimes you need to
49:41take time to understand the data,
49:44cleaning data, choose [music] the right
49:46pivot tables or pivot charts and decide
49:48on the best layout. So if you are
49:51building your own dashboard and [music]
49:53it takes a longer time, that's totally
49:55fine and normal. Dashboard building is a
49:58skill. [music] The more you practice,
49:59the better you become in turning raw
50:01data into a clear summary.
50:03>> [music]
50:04>> And if you want more hands-on practice,
50:06I'm currently upgrading my pivot tables
50:08practice pack. The updated version will
50:11include more pivot tables exercises
50:13[music] and dashboard practice as well,
50:16so you can practice on turning raw data
50:19into useful business summaries. So stay
50:22tuned and I'll share more details soon.
50:24[music] And thanks for watching. If you
50:27find this tutorial useful, make sure to
50:29like, follow or subscribe me. And I'll
50:31see you in the next one. Bye.

About this transcript

This page contains the full transcript of Build an Interactive Excel Dashboard Using PivotTables by Marg Analytics, generated from the public captions YouTube serves with the video. The transcript has 6,376 words across 983 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to translate the transcript into 100+ languages.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, translation to 100+ languages, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.