Interactive Excel Dashboard Tutorial in 3 Steps (+ FREE Template)
Summary
TLDRThis tutorial video guides viewers through creating an interactive sales dashboard in Excel within 20 minutes. It covers designing cohesive elements, analyzing key metrics, and adding interactive features for data exploration. The example uses 11 months of fictional outbound sales call data, showcasing how to format, insert KPIs, pivot tables, and conditional formatting for a visually appealing and informative dashboard. The video also offers a 20% discount on Excel and Power BI courses, encouraging viewers to enhance their data analysis skills.
Takeaways
- 📊 The video demonstrates how to create an interactive dashboard in three main steps: design, data analysis and visualization, and adding interactivity.
- 🔍 The dashboard is designed to visualize key metrics from 11 months of fictional outbound sales call data, including total calls, calls reached, conversion rates, and deal values.
- 🎨 A cohesive design is established by setting a color theme and using consistent design elements such as shapes and icons to represent KPIs.
- 📈 The script guides through the process of inserting and formatting shapes, icons, and text boxes to display KPI values and labels.
- 📝 The use of Excel tables and pivot tables is emphasized for data organization and easy updating of the dashboard.
- 🔧 The tutorial includes tips on how to format data, apply number formatting, and use slicers for filtering data based on salesperson names.
- 📉 Charts are created to visualize data trends, including a column chart for calls reached and deals closed, and an area chart for the call drop rate.
- 👀 Conditional formatting is used to highlight selected salespersons in the KPI table, enhancing the dashboard's interactivity and data exploration capabilities.
- ⚙️ The dashboard is made interactive with elements like slicers that allow users to filter and explore data specific to their interests.
- 🔄 The script mentions a time-limited discount on Excel and Power BI courses, indicating a promotion to enhance data visualization skills.
- 🚀 The dashboard is designed to be easily updated with new data, showcasing the efficiency of using pivot tables in data management and visualization.
Q & A
What is the main objective of the video?
-The main objective of the video is to demonstrate how to transform a dataset into an interactive dashboard in three steps: cohesive design, data analysis and visualization, and adding interactive elements, all within 20 minutes.
What type of data is used in the video for the dashboard?
-The data used in the video is fictional outbound sales call data spanning 11 months, which includes metrics such as total calls, calls reached, average duration, deals closed, conversion rate, deal value, and dropped call rate.
How does the video suggest changing the color theme in the dashboard?
-The video suggests changing the color theme to 'aspect' on the page layout tab to quickly access a new color palette and default colors.
What is the purpose of using shapes in the dashboard design?
-Shapes are used to store KPIs (Key Performance Indicators) and provide a visual structure for organizing and displaying data in the dashboard.
How are icons used in the dashboard?
-Icons are used as visual indicators for KPIs, representing different metrics such as calls, calls reached, deals closed, and deal values.
What is the purpose of inserting a pivot table in the dashboard?
-A pivot table is inserted to analyze and summarize the data, making it easier to update the dashboard and filter data by different criteria such as salesperson's name.
How does the video suggest updating the dashboard with new data?
-The video suggests updating the dashboard by adding new data to the source table and then using the 'refresh all' feature on the data tab to update the dashboard.
What is the significance of using a slicer in the dashboard?
-A slicer provides an interactive way to filter the data displayed in the dashboard, allowing users to view data specific to a selected salesperson or other criteria.
How does the video handle the formatting of the pivot table and charts?
-The video demonstrates formatting the pivot table and charts to match the theme, including setting the vertical axis to start at zero, applying number formatting, and using conditional formatting to enhance visual interpretation of the data.
What is the final step to enhance the dashboard's visuals before completion?
-The final step is to add an outer shadow to the charts for an embellishment and to insert a shape with a title for the KPIs table to improve readability and visual appeal.
How does the video ensure the dashboard is interactive and easy to navigate?
-The video ensures interactivity by adding a slicer for data exploration and making sure the dashboard updates with a single button click, providing a user-friendly and dynamic experience.
Outlines
📊 Dashboard Creation Overview
This paragraph introduces the process of creating an interactive dashboard using a three-step method. It starts with the design elements, then moves to data analysis and visualization, and concludes with adding interactive elements. The video promises to complete this process in under 20 minutes, with an option to adjust playback speed if needed. The data set used is a fictional sales call record spanning 11 months, covering various metrics such as total calls, reached calls, average duration, deals closed, conversion rates, deal values, and dropped call rates. The speaker guides the viewer through setting up the dashboard canvas, including changing color themes and inserting shapes for KPIs, and ends with a promotion for Excel and Power BI courses.
🖌️ Customizing Dashboard Aesthetics and KPIs
The speaker continues by customizing the dashboard's aesthetics, setting fill colors for rows and inserting a heading and subheading with specific font sizes and colors. Shapes are added to represent KPIs, and icons are inserted to symbolize calls, reached calls, closed deals, and deal values. The paragraph details the formatting of these elements, including color changes, resizing, and alignment. The speaker also demonstrates how to create a pivot table for data analysis and introduces a slicer for filtering the data by salesperson's name, all while maintaining the dashboard's color theme.
📈 Analyzing Sales Data with Pivot Tables and Charts
The paragraph focuses on analyzing sales data using pivot tables and creating various charts to visualize KPIs. The speaker explains how to format data as an Excel table, rename it for easy reference, and insert a pivot table with specific fields. Number formatting is applied, and a slicer is used for interactive filtering. The speaker then creates textboxes linked to cells in the pivot table to display KPI values and formats them accordingly. The paragraph also covers inserting and formatting a pivot table to display KPIs by salesperson, using conditional formatting to enhance data interpretation visually.
📊 Chart Creation and Dashboard Finalization
This paragraph describes the creation of various charts, including a column chart for calls reached and deals closed, a line chart for total sales with a trend line, and charts for average call duration and call drop rate. Each chart is formatted to match the dashboard's theme and includes specific details such as axis settings, color schemes, and data label placements. The speaker also discusses adding shadows to the charts for a polished look and creating a title for the KPI table. Conditional formatting is applied to highlight the selected salesperson in the KPI table. The paragraph concludes with instructions on how to update the dashboard with new data and a teaser for a future video on automating data preparation tasks.
Mindmap
Keywords
💡Dashboard
💡Cohesive Design Elements
💡Key Metrics
💡Data Visualization
💡Interactive Elements
💡Pivot Table
💡Conditional Formatting
💡KPIs (Key Performance Indicators)
💡Slicer
💡GETPIVOTDATA Function
💡Data Refresh
Highlights
Transforming a sea of data into a sleek, interactive dashboard in under 20 minutes.
Using cohesive design elements, analyzing and visualizing key metrics, and adding interactive elements for data exploration as the three-step process to create the dashboard.
Utilizing fictional outbound sales call data spanning 11 months with various metrics like total calls, conversion rate, and deal value.
Customizing the dashboard with a color theme to enhance visual appeal and accessibility.
Inserting and formatting shapes to store KPIs with a consistent design approach.
Adding icons as visual indicators for KPIs to enhance dashboard readability.
Creating an Excel table for easy referencing and updating of the dashboard.
Using a pivot table to analyze and summarize key sales metrics dynamically.
Implementing a slicer for interactive filtering of salesperson data on the dashboard.
Linking KPI values to cells and using GETPIVOTDATA formula for dynamic updates.
Applying number formatting and conditional formatting to the pivot table for better data visualization.
Inserting and customizing a pivot table to display KPIs by salesperson with data bars for visual data interpretation.
Creating charts like column and area charts to visually represent sales data trends over time.
Using trend lines and gradients in charts for enhanced data presentation.
Adding shadows and outer glows to charts for a polished and professional look.
Implementing conditional formatting to highlight selected salesperson in the KPIs table for quick location.
Effortlessly updating the dashboard with new data by refreshing the pivot tables.
Providing a 20% discount on Excel and Power BI courses to enhance data analysis and dashboard creation skills.
Transcripts
In this video, we're going to transform this sea of data into this sleek, interactive dashboard.
We'll craft this dashboard in three steps,
starting with cohesive design elements. Then we'll analyze and visualize key metrics. And
lastly, add interactive elements for data exploration. And all in under 20 minutes.
Now, if you find the pace too fast, click the cog
icon in the bottom right and select a slower playback speed. Okay, let's get started.
The data we'll be using is some fictional outbound sales call
data spanning 11 months. It tracks the salesperson's total calls, calls reached,
average duration, deals closed, conversion rate, deal value, and dropped call rate,
which is the percentage of people who hang up before the sales rep can speak to them. We've
all done that, right? Right. Let's start by getting the dashboard canvas ready.
Insert a new sheet and I'll just drag it to the front and rename it dashboard. And then
on the page layout tab, I want to change the color theme to aspect. This just changes the
color palette and default colors, making them quicker to access. Selecting rows one through
eight. I'm going to set the fill color to purple and then from 9 through 40. I'm going to set
it just to a paler shade of purple and I've got a custom color that I'll enter in here,
which is f2eff5. Click 'Ok'. And that's done.
I'll insert a heading 'Sales Dashboard'. And I'm going to set the font size here to 36. I will make
it white. And then I'm going to add a subheading that describes the objective of the dashboard,
which is evaluating sales agent performance. And I'm going to make that 16 point and yellow.
Let's just make column A a little bit narrower to bring the heading over to the left. Next,
I'll insert some shapes to store my KPIs, and I want this shape here with the rounded
top corners. This draw on and then I'm going to set the height and width, which is 6.5 and 3.5,
and I'm just going to rotate it 90 degrees. I will bring it roughly into place. Let's change
the fill color to gold, and I'm going to get rid of the outline. I'll reduce the corner radius
just slightly. Okay. Holding control and shift left click and drag to make a copy. This one is going
to be white and I need to flip the orientation and align them like that. All right. Next,
I want to insert a vertical line. And this is just going to divide my value,
and icon in my KPI placeholder. Let's change the outline to this gold color and we'll change the
weight to just a half point. All right. Let's hold down, shift and select the shapes right click and
group them together. That's just going to make them easier to move around. With it selected,
holding control and Shift. I'm just going to copy it three times by left clicking and dragging and
that's done. Next, I want to insert some icons as a visual indicator for my KPI. First one
I want is a phone to represent the calls. Then I want a target for the calls reached and then I
want a prize for the calls that get closed. And then we want some money for the deals. Alright,
I've got 4 selected, let's insert them while they're selected. I'm going to change the height and we'll
change the color to purple. All right, let's move them into place. Just left click and drag
them. Roughly place them. You can always use the alignment tools if they're not perfect. Okay,
we're ready to do the analysis for the KPIs and charts. But before we do, I want to let you
know that we currently have 20% off all our Excel and Power BI courses until February 15. So if you
want to skyrocket your skills, check out the link to the courses in the video description below.
On the data tab of the ribbon. The first thing I want to do is control T to format the data in an
Excel table. My table has headers. So click okay. And let's rename this sales data. This
will make it easy to reference and more importantly, update the dashboard. Let's insert a pivot table.
We'll put it on a new worksheet and I'll call this analysis. Let's bring the field list over
closer so that we can see it in context. So here I want total calls, calls reached, deals closed
and the deal value. Let's drag the values into the rows and with these cells selected control+1 and
I'm just going to apply some number formatting and let's repeat that for the deal value this
one is going to be different with currency, this formatting feeds through to the dashboard, so it's
best to do it here. Next, I'm going to insert a slicer, right click add a slicer for the name. And
now if I select a name in the slicer, you can see the pivot table filters accordingly. Let's format
the slicer purple, in keeping with the theme and we'll set the height and width. 15.48, 3.35. And
let's change the height of the button to 0.48 so that we don't need to scrollbar. All right, with it
selected Control X to cut it out and we're just going to paste it on the dashboard. Next,
I'm going to insert the textbox for the KPI values under shapes and under basic shapes, we've got Text Box,
I'll just roughly draw it in here. And then with the outer edge of the text box selected,
click in the formula bar equals, go to the analysis tab. Now, if I click on the
value in the pivot table, you can see it puts the GETPIVOTDATA formula in for me. Now,
textboxes can't have a formula. They can only link to a cell. So I'm going to just select the
cell beside and change the reference to B4, press Enter. And now it's got the value. Let's
center it and let's changed the font size to 18 and we'll change the font color to purple. Alright,
that's that done. Let's insert a new text box for the label and this is going to be CALLS. Let's
apply the same formatting we'll make this 18 point holding down shift. Let's select both of the text
boxes and then on the formatting. Going to get rid of the fill and the outline holding down control
and shift. I'm going to left click and drag to copy them across 3 times. With them selected,
I'm going to control+A to select all of the objects. Control+1 and then in the formatting I want
to set the properties. Do not move or size with cells. This is just going to help these
stay in place when we insert pivot tables. So let's rename this. This one will be REACHED and
we need to change the reference here to B5 and you'll notice it's lost the formatting. This
one's B6. Don't worry, I've got a shortcut for that. This one's B7 and this one is
CLOSED and this one is VALUE. All right, let's copy the formatting from over here. So with it
selected on the home tab, double click the format painter and then just left click once
to apply the formatting. Actually, I'm going to make the font in these a bit bigger. Let's
change that to 32 point. That's better. Now this one doesn't quite fit,
so we need to make it wider. Let's just select it and see how wide it needs to go. And then
with the white box selected, I'm just going to make it slightly wider to accommodate the
bigger number and we'll just bring this over slightly to make sure it's centered. Okay,
that's our KPIs done. Let's take a look at the finished dashboard.
The next visual I want to insert is this pivot table that breaks the KPIs down by salesperson.
So let's go back and do that on the data tab, insert pivot table and this one's going on the
dashboard and we want to put it here. Click, OK. So this will have the name Total calls,
calls reach, deals closed and deal value. All right, let's close that down. Let's do some
formatting here. In keeping with our theme, it should be purple, but you'll notice it has a
white background. So I'm just going to change the fill color to the purple that I created earlier.
Let's change the headers. This is name and just add a space after it to differentiate it and then
control H. We want to replace 'Sum of' with just a space, Replace all. So that gets rid of four of them
in one go. And now I can double click to resize the columns. Let's apply the number formatting.
So here I want a number, comma separator and no decimal places. And this one, remember, is my
sales value. So we want a currency with no decimal places. Let's add some conditional formatting to
help visually interpret the data more quickly. So we use data bars. Just select the cells that you want
to format. It's automatically going to apply it to the relevant fields in the pivot table and it will
copy that formatting should the pivot table grow. This one, I'm going to go with a gradient fill.
Alright. Let's just modify the formatting because they're all the same. So we're going to manage
rules and double click on the first one, which is the deal value. And this one instead of blue,
we want purple in keeping with our theme and no border. Next is closed, so this one can be
a lighter shade of purple. This is the calls reached. Let's go gold in keeping with our
theme and this one is calls made. But let's just differentiate this slightly by making it
paler. All right. Let's go with that. And that's done. The other thing we can do is sort the pivot
table, more sort options, and we'll go descending order based on deal value, because that's our most
important metric. And now we can see the higher salesperson through to the lowest salesperson's
deal value. Alright, that's done. Now we're ready to create the pivot tables for our charts.
And the first one is Sum of calls reached and Sum of is closed. Let's go and create the
analysis for that. So I'm going to copy this pivot table because it's already linked to the slicer
and then let's right click and bring up the field list. So this is calls reached and deals closed,
and I want to see it based on the month. Let's move the values across into columns and you'll
notice it's automatically grouped the dates into days and months. I only want it grouped into month
so let's get rid of days and that's ready to go. And now I can insert a column chart - 2D column
chart. Let's get rid of the field list. And the first thing is to right click and hide all the
field buttons. And then I'm going to use the legend as my title. So we're going to move it
to the top. We'll get rid of the grid lines, with the chart axis selected, control+1 to open
the formatting. And in here I want to make sure my minimum is always zero. So I'm going to hard key it.
Column charts should always have that vertical axis start at zero. I don't actually need to see
my vertical axis because I'm going to add data labels, but I actually want those data labels
inside the end. Now the columns aren't wide enough to display them, so let's fix that. We'll change
the series, overlap to 100 and we'll reduce the gap width to 50, and that just gives the label space.
Let's format the columns changing the field to a solid color, but this should be yellow for calls
reached and then this one can be purple for deals closed. Now I need to change the font color here
so that we can see it on the purple. Okay. And that's done. Control X to cut it out and we'll
place it on the dashboard. I'm just going to pop it here roughly. All right. Let's take a look the
next is the total sales chart. So let's go and build the pivot table for that. On the analysis
tab, I'm going to again copy this pivot table because it has the formatting and the fields that
I need. Right click show the field list. We don't want total calls, calls reached or deals close
this deal value and we want to see it by month. Let's go and insert a column chart and again
rinse and repeat. Hide the field buttons. Again, I need to set the vertical axis to start at zero,
and then I'm going to add a trend line and I'll move the legend to the top. We're going to move
the chart title over to the left and we'll move the legend over to the right. That will just give
us a bit more room for our chart. Let's change the chart title to total sales and let's go about
formatting. So here I want to change the width of the column, so we'll change the gap with to 50,
and then let's set the color to a solid fill in purple, which is in keeping with our theme
and then the trend line. Let's make it yellow. Just to contrast, we have to make it a bit wider
and we'll give it a thicker dash line. Alright, we don't need the grid lines, so let's turn them off
and we're ready to control X and place it on the dashboard over here. All right, that's two done.
Let's take a look at the next one, which is our average call duration in seconds. Let's close that
down. And then on the analysis here, I haven't used call duration before, so I can just copy
this pivot table and we want to get rid of deal value and put it in call duration. Now I need to
set that to show the average of the averages and let's format this number to no decimal places.
Let's insert another column chart and again, setting the vertical axis to be fixed at zero,
get rid of the field buttons. I also don't want grid lines or a legend. Let's change the color
and width so we'll make it 50% gap width and we'll make it yellow in keeping with our theme,
let's give it a title 'average call duration (seconds)', and we'll just move it across to the
left. Okay, Control x to cut and we'll paste it on our dashboard just here. Okay. The last one is
our average call drop rate. So let's go and build that. On the analysis tab, again, we haven't used call
drop rate, so I'm just going to copy this pivot table, paste it here, and instead of call duration,
we want call drop rate. And I want to change the summarization to average. Let's
also format it in a percentage with two decimal places. Ok, this one is going to be a little
different. We're going to go with an area chart, but we still want to hide all field buttons, change
the title to call drop rate % and move it across to the left. This time we don't need grid
lines or a legend, but let's format the color here in keeping with our theme. So this time I'm going
to go with a gradient fill. It's remembered the gradient stops from my earlier example file,
but you can click on them and change them using the dropdown here. So that chart is
done. Lets control x to cut, and paste it into the dashboard. Alright. That's all of our visuals done.
Let's just add one little embellishment here. We're going to format the chart with a shadow,
just an outer shadow, and I'm just going to select each one and press F4 to rinse and repeat that
quickly. Now we need a little title for this table here, so I'm going to insert a shape and
I'm just going to use this rounded top corner shape, holding down AltI'm going to draw in.
That's going to snap it to the grid behind. Let's get rid of the outline and we'll change the fill
color to the custom purple we created earlier. And I'm just going to add a shadow to the top.
The title for this is Sales Agent KPIs and let's fix the font so we can read it. We'll make it purple
and a bit bigger. One last thing I want to do is set some conditional formatting that highlights
which salesperson in the sales agent KPIs table is selected in the slicer because as you can see,
if I select Jake, Jake is hard to find because the list here is not alphabetical,
but the list in my slicer is. So what we can do is I'll go into the analysis sheet and I'm just going
to copy this pivot table because remember, this one is connected to the slicer and we'll bring up
the field list and I'll get rid of all these fields. I don't need them. What I want is the
name. All I want to know is what name is selected in the slicer, and that's what's present here.
So I can use that in a formula to apply some conditional formatting. So select the table.
Conditional Formatting, New rule. Here I want to use a formula and I want to know whether this
cell here, and I'm going to F4 twice so that I'm only locking on the column and that's going
to check all of the names in this column to see whether they equal the name in this pivot table,
which is the name that's been selected in the slicer. So let's go into formatting. And
I'm just going to format it with top and bottom border in purple. Let's see how that looks. All
right. So now when I select a name in the slicer, you can quickly locate them in the table. So far,
the dashboard only has data for January to November, but because we built it using pivot tables,
it's super fast to update. All I need to do is copy this December data here and on my data
sheet in the table in the very next blank row control+V to paste and you can see the table
has expanded to include the new data. And now to update the dashboard, keep an eye on this
chart here. All I need to do is go to the data tab and click refresh all. And just like that,
my report is updated and there you have it, an interactive dashboard that enables data
exploration through the slicer and updates with the click of a single button. Pretty cool. Now,
if your data isn't in a ready to use format like I had for this dashboard, for example, you might
need to gather it from multiple files and clean it check for duplicates, add calculated columns,
then check out this video next on how to automate those boring tasks. I'll see you in the next video.
5.0 / 5 (0 votes)