Interactive Excel Dashboard Tutorial in 3 Steps (+ FREE Template)

Summary

This 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

00:00

📊 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.

05:01

🖌️ 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.

10:02

📈 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.

15:04

📊 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.

Keywords

💡Dashboard

A dashboard is a user interface that organizes and presents information in a way that is easy to read and understand at a glance. In the context of the video, the dashboard is being created to visualize and interact with sales data, making it a central tool for data exploration and analysis. The script describes the process of transforming raw data into a 'sleek, interactive dashboard' within a short timeframe.

💡Cohesive Design Elements

Cohesive design elements refer to the consistent use of visual components that work together harmoniously in a layout. The video mentions starting with cohesive design elements to create a unified look for the dashboard, which includes setting a color theme and choosing consistent fonts and shapes to make the dashboard aesthetically pleasing and easier to navigate.

💡Key Metrics

Key metrics are the critical measurements used to evaluate performance or success. In the video, the script discusses analyzing and visualizing key metrics such as total calls, calls reached, average duration, deals closed, conversion rate, deal value, and dropped call rate to assess the performance of sales agents.

💡Data Visualization

Data visualization is the presentation of data in a graphical format to make it easier to understand and analyze. The video script includes steps to create charts and graphs for the dashboard, such as column charts and area charts, to represent the sales data visually, allowing for quick interpretation and exploration of the data.

💡Interactive Elements

Interactive elements are features within a digital interface that allow users to interact with the content, such as sliders, buttons, or filters. The video mentions adding interactive elements to the dashboard for data exploration, specifically using a slicer to filter the data by salesperson's name, enhancing the user's ability to engage with and analyze the sales data.

💡Pivot Table

A pivot table is a tool used in data analysis that summarizes and organizes data, making it easy to aggregate and analyze large datasets. In the script, the creation of pivot tables is a key step in preparing the data for the dashboard, allowing for dynamic filtering and summarization of sales data.

💡Conditional Formatting

Conditional formatting is a feature in spreadsheets that applies specific formatting to cells based on certain criteria or rules. The video script describes using conditional formatting to visually interpret the data more quickly, such as applying data bars and gradient fills to cells in the pivot table to highlight values and trends.

💡KPIs (Key Performance Indicators)

KPIs are quantifiable measures that are used to track performance against objectives. In the video, KPIs like calls reached, deals closed, and deal value are extracted and displayed on the dashboard to evaluate the sales agent performance. The script details the process of inserting shapes and text boxes to represent these KPIs visually.

💡Slicer

A slicer is a filter tool in data analysis that allows users to select items from a list to display only the related data. The script mentions inserting a slicer for the salesperson's name to filter the pivot table, making it easy to view and compare the performance of individual sales agents on the dashboard.

💡GETPIVOTDATA Function

The GETPIVOTDATA function is used in Excel to extract data from a pivot table into another cell or range. In the script, the function is mentioned when linking the KPI values to the pivot table data, allowing the dashboard to dynamically update the KPI values as the underlying data changes.

💡Data Refresh

Data refresh refers to the process of updating the data in a report or dashboard to reflect the most current information. The video script explains that updating the dashboard with new data is as simple as refreshing the pivot tables and charts, demonstrating the ease of maintaining an up-to-date dashboard.

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.