Excel Data Visualization Course – Guide to Charts & Dashboards

**Creating a Dynamic Excel Dashboard**

The process of creating a dynamic Excel dashboard involves several steps that require attention to detail and a deep understanding of what makes an effective dashboard. In this tutorial, we will walk through the creation of a dynamic Excel dashboard for an e-commerce company.

**Step 1: Setting up Pivot Tables and Charts**

To create a dynamic dashboard, we need to start by setting up pivot tables and charts that can provide us with real-time data insights. We begin by creating pivot tables for sales, revenue, and profit. We also create a chart to visualize the data, which will be used later in our dashboard.

**Step 2: Adding Interactivity**

Once we have set up our pivot tables and charts, we need to add interactivity to our dashboard. This involves adding slicers that can filter the data based on different criteria such as region, country, and time period. We also add a timeline slicer that allows users to select specific dates or periods for analysis.

**Step 3: Designing the Dashboard**

With our pivot tables and charts set up and interactivity added, we now need to design the dashboard itself. This involves arranging the different components in a way that is intuitive and visually appealing. We prioritize the most important insights in the key performance indicators (KPIs) and place them prominently on the dashboard.

**Step 4: Maintaining Visual Consistency**

To maintain visual consistency throughout our dashboard, we use a consistent color scheme, font family, and sizing for all elements. We also align elements to create a clean and organized look, maximizing white space to avoid overcrowding with too much information.

**Assembling the Dashboard Components**

Once we have designed our dashboard layout, we assemble all the components into a cohesive Excel dashboard. This involves copying and pasting charts, KPIs, slicers, and timelines into new tabs and reorganizing them as needed. We prioritize the most important elements on top of the dashboard and use text boxes to provide context and explanation.

**Adding Text Box**

Finally, we add a text box with the title "Revenue Dashboard" to our tab. We reference the cell range for this text box correctly, ensuring that it updates dynamically with new data. This completes our dynamic Excel dashboard for the e-commerce company.

**Design Principles for an Effective Dashboard**

When assembling our dashboard components, we prioritize several design principles to ensure that our dashboard is effective and user-friendly. These include:

* **Information Hierarchy**: We prioritize the most important insights in the KPIs and place them prominently on the dashboard.

* **Visual Consistency**: We use a consistent color scheme, font family, and sizing for all elements throughout the dashboard.

* **Alignment**: We align elements to create a clean and organized look, maximizing white space to avoid overcrowding with too much information.

**Conclusion**

Creating a dynamic Excel dashboard requires attention to detail and a deep understanding of what makes an effective dashboard. By following these steps and prioritizing design principles, we can create a dashboard that provides real-time data insights and is intuitive for users to navigate.

"WEBVTTKind: captionsLanguage: enin this Microsoft Excel dashboards course you'll learn how to transform raw data into insightful interactive visualizations this course will walk you through designing Dynamic charts and building professional dashboards that enhance data storytelling with Hands-On exercises you'll Master the tools and techniques needed to create compelling data visualizations in Excel and you'll be equipped to present data in a way that drives informed decision making me here from Office Tech skill develop this course struggling to find the perfect chart to represent your data let's unlock the secret of excel charts together today I'll walk you through the various chart types available in Excel and how to use the right one for your data hi I'm me Kar your ttor at offex skill.com where we specialize in helping professionals upgrade their Tech skills to enhance productivity and efficiency in the workplace let's discuss various chart types that you can Implement within Excel the topics we are going to cover in this section include column chart bar chart line chart P donut chart XY scatter plot chart area chart radar chart stock chart histogram chart paru chart waterfall chart box and whisker chart tree map chart map chart recommended chart now let's discuss what is a column chart column charts are drawn with a rectangular bars of various length representing different values there are different types of column charts such as cluster chart stack column chart and 100% stack Colum colum chart cluster column charts use colored to differentiate the column whereas the Stag column chart use varying thickness or colors for each column column charts are an effective means of displaying data variation over a period or comparing various elements typically the categories are placed on one axis and the value on another allowing for Clear visualization of changes or updates with each comparison point in the narrative So currently we have a data set which has sales by month for two products which is product one and product two so column chart is an excellent candidate for this kind of data set we'll select first the data set press CR a we'll go to insert we'll go to recommended charts we'll go to all charts we'll go to column and then we can select a cluster column chart now we can add the chart and access title so the chart title is already there now we can add the access title as well expand the chart so we can add the chart title as product 1 versus Product 2 sales the Y AIS is sales and xais is the month now we can change the color of the bars so for product one we can just select the blue bar and now change the color right Mouse click on the bar and change this to Red same thing with the orange bar right Mouse click on it and change the color to gray your column chart is ready if you want to change the chart type you can go to the Chart design tab then select change chart type so if you want to change from cluster column to stack column you can simply click on this and press okay or you can change it to some other style that you prefer now let's try to understand bar chart bar chart is drawn with rectangular bars of various lens representing different values it is often used to compare two or more discreete values the size of the rectangle relates to the magnitude of the value it represents so the taller indicates the greater value than shorter ones there are different types of bar chart such as cluster stacked 100% stacked bar charts bar charts are use color to differentiate the bars whereas the stack bar charts use varying thickness or colors for each bar now let's go back to the Excel go to the bar chart time over here we again have the same data which is data for sales by month for product one and product two now we can use this data set to create a bar chart so select the data click on insert go to recommended charts go to all charts click on bar and then select cluster bar chart go to the plus sign add the access title and now you can change the name from chart title to product one versus product to sales the access title is going to be month and the access title will be sales now we can change the color of the bar right Mouse click on the blue bar go to fill and we can change that to Gray and right click on the orange bar select F and we can change that to Red your cluster bar chart is ready now if you want to change from cluster bar to stack bar chart you can just go to the Chart design go to change chart type and then you can select stack bar or you can select 100% stack bar depending on whatever your preferences press okay as you see now the chart type is changed now let's try to understand what is line chart line chart is a type of chart that displays information as a series of data point it is also known as time series chart line charts are a type of chart that expresses information as a series of data points line charts are good for showing how something has changed or comparing two different things there are different types of line charts such as line stacked 100% stacked line with markers stacked line with markers and 100% stack line with markers chart now let's go back to the Excel go to the line chart time again we have the same data by month product sales for product one and product two now we will select this data set and create a line chart go to recommended charts go to all charts go to line and we'll select line chart press okay and now we'll go to the plus sign add the access title we'll change the title for y access to sales and change the title for xais to month and we'll change the color of the line first we'll add the chart title so we'll make it product one versus product two sales so we'll go to line one or product one go to the product two line right Mouse click and go to outline and select gray color color go to the blue line right Mouse click go to the outline Tab and select red and now your line chart is ready this is a simple line chart now let's try to understand Pi donut chart Pi donut chart are typically used to show the proportional contribution of each component to an overall chart the pie chart is a variation of circle graph or py graph also known as circle chart where each slice is drawn as a circle segment based on the angle swep out of its radius to the center point pie charts are used in finance and statistical field certain types of reports such as progress report graphic displays with limited space for text they are often thought be difficult to read by humans because our eyes do not naturally follow an arc still they can be read more effectively if labeled with their respective values let's go back to the Excel go to the pie chart Tab and now we'll insert a pie chart press control okay go to insert select recommended charts go to all charts go to Pi click on the pie chart press okay as you see the pie chart is created now we can add data labels to understand what is the revenue for each Pi now we can also create a donut chart so you can just copy this chart and paste right next to it go to chart design go to change chart type type and you can select donut chart press okay now let's try to understand XY scatter plot chart the scatter plot chart is a graph that shows an association between two variables for example when the graph is called an XY chart you can see the relationship between two variables on one graph when one variable can be plotted on the x axis the other one can be plotted on y- axis the scatter blot can show whether there is a correlation between two variables it also shows how strong the correlation might be making it easier for readers to understand what they're looking at so let's go to excel go to XY chart and now we'll use the same data set which is sales by month for product one and product two press CR a go to insert click on recommended charts go to all charts go to XY scatter plot chart and then select scatter with smooth lines and markers press okay insert the title access title provide the chart name to product one versus product to sales title for y AIS title for xaxis month your chart is ready now you can experiment with other XY charts by creating a duplicate chart so we'll create a duplicate chart we'll go to chart design change chart type and then you can select any chart you want for XY we select this chart for example and now you can compare between two charts and see which one works best for you now let's try to understand area chart an area chart is created by plotting the data on vertical I AIS and the category on the horizontal x axis the total area under each graph can be calculated by adding up all the areas under each rectangle stacked above each other the height of each rectangle corresponds to each relative value or frequency about different rectangles or bars stacked below it it is a graphical depiction on the number of frequency of observation in different categories let's go to the spreadsheet go to the area chart tab now we'll create a area chart for this data set so select contr a go to insert go to recommended charts go to all charts go under area and then select this chart press Okay click on the plus sign and add the access title provide the title name as product 1 versus Product 2 Saed the Y AIS will be sales and the x-axis is going to be the month now if you want to look at other area charts you can just simply copy and paste right next to it now you can change the chart Style so click on this chart go to chart design go to change chart type and now you can select any chart you want so if I want to select stacked area chart press okay now you can compare and see which one suits you need now let's try to understand radar chart radar chart diagram can simultaneously visualize three of more data sets it is typically used to compare qualitative data but also can be used for quantitative data the radar chart may seem intimidating but it is straightforward to use so this chart is helpful when you have multiple time series or different dimensions on the same chart so let's go back to the Excel go to the RAR chart time so now we have the salves for three products per month select all the data go to insert go to recommended charts go to all charts go to radar and click on okay now you can add the title as product comparison sales you can expand the chart to make it more visible you can create a duplicate chart and select other radar chart see a side by- side comparison of which chart puts better so select the duplicate chart go to chart design go to change chart type you can select this chart or you can select radar with markers press okay now let's try to understand the stock chart stock chart is a visual representation of historical data it helps to quickly overview of what has happened and identify patterns and Trends stock chart are handy for investors who want to know how their assets perform they can help them make better financial decisions and react quickly when the market shifts when to use a stock chart in Excel there are various reasons to utilize a stock chart in Excel such as tracking the performance of investment the stock market identifying trend lines and determine support and resistance level using the stock chart can Aid in making informed decisions now let's go to the Exel go to the stock chart T now we have a data of a particular stock CH price point and the market was open the highest value of the day the lowest value of the day closing value of the day and the adjusted closing value of the day also we have the volume and we have the data by dates now we want to create a stock chart which has a value of high low and close and we want to see it by date so we'll select these four columns so it's going to be date high low and close so now go to recommended charts go to all charts go to Stock press okay you can change the chart title to high low close you can include the access title so this is going to be price on the y axis and on the x-axis is going to be the date you can change the chart Style by clicking on the chart go to chart design and you can select style two as an option you can add a line for high and low to see how the trend changes over day so click on the top part of the line and then go to line then you can select a solid line we can change the color to gray go to the bottom part select the bottom part which is now you want to add a line for low select solid line and then select yellow as you see now it has a gray line for high and yellow line for low and the circle represents the closing price of the stock now let's try to understand the histogram chart the histogram chart is a graphical representation of the breakdowns of the data set into different categories a histogram chart is a graph that displays the frequency distribution of data typically split into categories so for example it can show how different sets are distributed in frequency let's go back to Exel go to the histogram chart tag we have the data by customer and by profit so now we can use a histogram in order to put the profit into different buckets because it's a range so it can put into different buckets we can group the customer into that bucket so select the data set go to insert go to recommended charts go to all charts go to histogram and press okay as you see Excel has automatically create the grouping we can add the chart title as customer profit range you can also add the data labels click on the chart go to plus sign and then add data labels So currently now the range is not visible so you can just expand the chart so you can see the highest and the lowest value currently there are six bins now you can customize the bins you can select the lowest and the highest value based on your need so if you click on the values on the x-axis you have the option to select the bin so there are six categories broken into 280,000 so you can change this to 300,000 and then press okay it has changed the range from 0 to 300,000 you can provide the bin width or also you can provide the number of bins and it will calculate and select the range so press enter and now you see there are five bins instead of six so you can customize based on your need let's try to understand what is a parto chart parto chart is a graphical representation of the ad20 principle it plots a cumulative percentage of events or outcomes found on vertical axis and the frequency or rate of these events on a horizontal axis a bar chart is a great way to understand what matters most in a given category it is easy to understand chart that people can use to identify and prioritize their efforts or decide what they want to focus on to achieve better results it helps to determine where to put most of your time and resources by looking at the top priorities we have data by region with total transaction so now we'll use Paro chart to understand which region has the highest transaction so select the data go to insert go to recommended charts go to all charts go to histogram and then select Paro chart click okay you can expand the chart and provide the chart name as transaction count by region as you see Europe has the highest transaction so this will tell a user that they need to place more emphasis on Europe and subaran Africa since they both have the highest transactions now let's try to understand what is waterfall chart waterfall chart is a type of chart that shows how various levels of one or more variables stack up against each other it is also known as hierarchical barcraft because it depicts the data in water form or hierarchical form shareholding companies typically use waterfall charts to Direct Cash Flow during a financial year the chart is divided into months with each waterfall chart showing the available funds for that month now let's go to Excel and implement this chart go to the waterfall chart tab the data set shows the date and the daily profit so now I'll use waterfall chart to understand this data so select the data go to insert go to recommended charts go to Waterfall click okay the chart is created now we can change the chart title to daily profit so click on the legend for increase go to the color icon and change from solid go to solid and now change it to Green click on the decrease Legend go to the coloron go to solid fill and change it to Red the bars are more visible and you can see where the profit has increased or decreased based on the date let's try to understand box and whisker chart box and whisker chart represents the data distribution the vertical line above and below boxes represents the maximum and the minimum value the box shows the value between the 25th through 75th percent and the horizontal line represents the 50th percent and now we will create a box and whisker chart based on this data set so click on insert go to recommended charts go to all charts select box and whiskers click okay and now you see the chart is created we can add the title as transaction by region and we can also add the access title select the plus sign take a mark on the access title the y- axis will be called transaction the x-axis will be called region we can change the color of the bars make it yellow so it's more visible we can also select more data label options go to the number drop-down and go to General and select number you can reduce the decimal to zero and as you see the decimals are removed the minimum value maximum value the median the 25th percentile and the 75th percentile and also the 50th percentile I can expand the chart to make the data more visible and the data labels as you see now after expanding the chart the data looks more visible the line on the bottom shows the minimum value the line on the top shows the maximum value 57 is the 25th percent X is the average 78 is the median 508th percental and 90 is the 75th percenti let's try to understand the tree map chart a tree map chart is a way to visualize the hierarchy for example it is often used to display the proportion of different values in a data set prap can be dra drawn as squares or rectangles and nested within one another to provide greater detail I.E more extensive and smaller rectangles can be drawn inside one another so when to use a tree map chart tree map provides a quick and easy way to summarize large amounts of hierarchical data when plotting nodes the dimensions are configured based on numerical values assigned per node this makes identifying Trends between all categories much more straightforward in comparing one type with another or even among different groups within the same Leaf Branch hierarchic now let's go to the Excel file over here we have three columns this is essentially a hierarchical data the first level is the region the second level is the country and we want to find out the revenue for each country so select all the data click on insert go to recommended charts go to all charts go to tree map click on the Dre map press okay the dream map chart is created you can expand the chart now we can add the title sales by country and we can move the legend to bottom now the chart is more visible the data is broken by region so the yellow one is Europe the navy blue is subsaharan Africa the light blue is Middle East and North Africa the gray is Central America and Caribbean and then the boxes are divided based on the value of the sales so in Europe Serbia has the highest sales in subs and Africa Benin has the highest sales in Middle East and North Africa Libya has the highest sales so anyone looking at this three map chart can easily figure it out which region and which country has the highest sales let's understand map chart map charts are a visual representation of data in a geographical context they help display data comparison between the region and distribution points across the globe map sh provides visual representation of data on geographic regions that are challenging to understand what formats maap charts can show anything from demographics comparative statistics or any other data type that would be difficult to see in a table form map charts are also accessible for USS to read and understand because they give clear and concise information and now select the data we have the same data from the tree map chart which is region country and revenue select all the data go to insert go to recommended charts go to all charts select map and now press okay and now you can see the revenue by country let me expand the charts the country with the darkest color has the highest sale and the country with the lightest color has the lowest sales so now we can change the title to sales by country and we'll change the color go to the brush sign and then select color and then you can select this color palette you can also add the data labels that would provide you with the values now let's try to understand recommended charts the recommended charts feature in Exel is a function that allows user to automatically generate additional types of chart that are not available in the ribbon three things can cause a chart to be generated user selected data data from external Source Dynamic data meaning there's no need for user intervention this feature generates and displays a list of recommended sharts and graph based on your data selection the list is displayed under insert recommended charts so now let's go to the Excel file and now select the data again go to insert go to recommended charts and over here you get a list of recommended chart that Excel provides you and you can select any one of these charts based on your requirement so there you have it a comprehensive tutorial on mastering Excel chart types now I'm going to show you how to customize your Excel chart to make them look professional and impactful you can download the file from the description the topics we are going to cover in this video we'll start start with organizing your data next we'll select the best chart type then we'll apply chart Styles and template then we can adjust the chart elements and the labels we can use data labels and grid lines to enhance your chart we'll apply Color themes and pallets we'll discuss how you can utilize combination chart and secondary AIS we'll show where you can incorporate trend lines we'll discuss different chart elements that you can apply and finally we'll go through some best practices for chart customization so before diving into customization it's essential to understand that you need to organize your data so go back to excel first we want to ensure your data is organized in a tabular format you have the column headers on the top and there are no blank rows or columns in your data set to make your data easier to reference when creating and updating charts you can put this data into a uh name range so we'll just select this data from A1 through C13 we go to the formula time and then click on Define name range and in our case the range is already created so if I select edit you can provide a name change so you can name it as like sales data and click okay so anytime now you want to refer to the data you can just use this the name which is sales data now we want to choose the best chart type for this data so go to insert in our case we'll select a column chart because this stereotype is better suited to show in a column chart so I'll just select a cluster column chart because we want to compare two different products side by side now we want to apply some chart Styles and also we can discuss some templates that you can use so if you click on the chart you can go to the Chart design Tab and over here you have options to select any of the chart Style I'll just keep it default next we'll adjust some chart elements and labels so if you click on this plus sign you can click on access title you can change the legend on the top make it on the top you can remove the grid lines and now we can also change the name of the chart so we'll make it as monthly sales product one versus product two this is called revenue and this is called month now you can enhance the chart look by adding the data labels or data tables in Grid lines so if I click on the chart again if I click on data labels you'll see the data or you'll see the value for each and every bar but then you'll have to expand the chart or you can use data table in of labels you can select data table and this will show all the values on the bottom so it's more organized and clean you can also add a grid line so you know exactly it becomes easier to analyze you can apply Color theme and palletes so Excel has already lot of pre-built colors and palletes that you can utilize so if you click on the plus sign or click on the brush icon you can select go to color and then you can select any of the color palettes that you like you can also go to chart design and select it from there and you can select from here as well it's one the same thing just different ways of accessing let's say if I don't like the color that's being displayed play or shown in the color palette you can also manually change it by clicking on the bar and this will select all the bars and then you can go to home and then you can just simply choose the color of your choice so I'll select black and I'll select the other one as orange or make it gray we can also Implement some Advanced Techniques we can use combination charts and secondary axis to look at the same data in a different way so you can click on this chart go to change chart or go to chart design select change chart type you can make this combo and over here you can select that you want the product one to be a column and product two be as shown as line so this is how your data would show so click okay and as you see you can add secondary access your secondary access is showing here which is product two now you can change the color by simply clicking on the bar and you can also change the color of the line you can go to format go to shape outline and then select any color of your choice you can also incorporate prend line if you want to see how the data is trending over particular period of time so let's say if you want to find out what's the trend for product one you can just simply click on the plus sign click on the trend line and this will give you the option if you want to see the trend line for product one or product two so I'll select product one and as you see the trend line is slightly increasing it's more or less the same if you want to change some formatting of the trend line you can right Mouse click on the trend line and click on format trend line and you will see this diog box will open up currently it's showing linear trend line you can change it to exponential logarithmic polinomial power so depending on your choice I'll stick to linear you can apply additional chart elements or you can customize the chart Elements by just simply clicking on the chart go to chart design and then go to the drop down of the chart elements and over here you have more options based on any changes you want to make to ensure your customized charts are effective and professional you can follow some of these best practices for customization first you want to prioritize Clarity and simplicity by removing unnecessary chart elements such as redundant label and you want to focus on the key message that you want to convey next you to use clear and concise style for your chart and also for your access so the person who's looking at your chart can easily understand the context of your data next you want to choose the appropriate chart type and data ranges that accurately represents your data and insight avoiding distorting or misrepresenting data by using misleading chart types or scale can send the wrong message to the person who's looking at your chart consider your target audience andent tailor your chart design and formatting use colors fonts styles that are easy to read and but now I'm going to show you how to create an engaging and effective Excel dashboard a crucial skill for any data driven professional so you will be building this dashboard as you see in the screen this is a very fastpaced video where I won't go into the nuts and bolts of each and every detail I just going to show you how to build this dashboard from scratch you can download the file from the description the topics we are going to cover in this video we'll start off with understanding interactive Excel dashboard then we'll plan free Excel dashboard then we'll collect and prepare your data we create a pivot table for data analysis we visualize the data through charts and graphs we increase the interactivity with Excel features and will finally assemble all the components into a dashboard interactive dashboards in Excel are a type of dashboard that allows you to track and measure key performance indicators and metrics they provide visual representation of complex data making it easier to understand and analyze with interactive dashboards you can change data inputs filter data drill down into specific details to gain insight and make data driven decisions next we need to plan your Excel dashboard before creating an Excel dashboard it's essential to understand the needs and expectation of your target audience consider who will be using the dashboard and what information they need to make informed decisions clearly Define the objectives of your dashboard and ensure they align with your organization's goal for this tutorial we create a revenue dashboard with a small e-commerce business focusing on key metrics like Revenue order customer acquisition the target audience with this dashboard will be sales and marketing team who will use the insight to optimize the product performance customer acquisition strategy and overall business growth in this tutorial you will use a zip file that contains data related to yearly sales Data customer item type location order priority and sales Channel you will see all this data in a zip folder now let us clean and organize the data so open a blank Excel workbook go to data click on get data click on from file click on Excel workbook click on the zip folder where you've extracted the data and then select 2014 sales data I'll select the 2014 sales data click on import and you will see a dialog box click on sheet one click on transform data and now your power query dialog box will open our power query editor will open now we'll just rename this to 2014 sales data so right M click click on rename and we'll rename this to 2014 sales data click on close and load to and then select only create connection now we'll repeat the same process for each Excel file so go to get data from file from Excel workbook click on 2015 sales data click on import now the dialog box will show up click on sheet one click on transform data this will load the data into parquer editor and now we'll rename this as 2015 sales data again select close and load to only create connection click okay now go to get data from file from Excel workbook now 2016 import click on sheet one transfering data now the data will load into Power query editor rename this file 2016 sales data select close and load to select only create connection from file from Excel workbook 2017 series data import take on sheet one transform data rename this to 2017 series data select close and load to only create connection click okay click on get data from file from Excel workbook 2018 sve data click on import click on sheet one transform data rename this two 2018 sales data select close and load to only create connection click okay click on get data from files from Excel workbook from 20189 saves data click import click on sheet one transform data rename this 2019 sales data select close and load to click only create connection get data from Excel workbook click on 2020 sales data import click on sheet one select transform data rename this to 2020 sales data click on close and load to click on only create connection again get data from file Excel workbook now click on 2021 sales data import click on sheet one transform data rename this make it 2021 sales data click on close and load to now all your sales data are loaded for each and every year now we'll do the same thing for other Excel files so go to get data from file from Excel workbook click on customer click on sheet one click on transform data and we'll call this as dim customer dim is dimension I'll explain the concept of dim and fact once we import the data into the power query so now let us go back to data click on get data from file from Excel workbook let us import item type click import click on sheet one transform data rename this to dim item type select close and load to select only create connection click okay click on get data from file from Excel workbook click on location import click on sheet one click on transform data rename this to demm location click on close and load to select only create connection click okay go to get data from Excel bookbook Select Auto priority click import click on sheet one click on transform data rename this to dim order priority select closeing load to click on only create connection Okay click on get data from file Excel workbook and select sales Channel import rename this to dim or click on sheet one click on transform data rename this to dim sales Channel click on close and load to select only create connection click okay now let us append all this data into a single power query where we'll combine the data from 2014 all the way to 2021 go to get data go to launch par qu editor so click on 2014 sales data query click on openend query click on open query is new and now we'll select all the queries so go to three or more tables and will select 2015 through 2021 click on ADD click okay now this has appended all the data from all these queries into a single query so we'll rename this call it fact sales transaction now now we want to filter out certain columns so we'll go to choose columns click on choose columns let us remove all the duplicates so just select all the data by pressing CR a right Mouse click and click on remove duplicates now we can load all this data into a tra model so right Mouse click on dim customer click on load to and then select add this to the data model click okay and now the data is loaded do the same thing for irm type load to add this to the data model click okay same thing over here same thing for order priority as well click on load to add this to the data model click okay same thing over here and also you want to load this the F fact sales transaction into the data model so click on load to add this to the T model now we'll create a p table for data analysis pivot tables are a powerful tool for data analysis in Excel that allows you to summarize reports and analyze large amounts of data this enables you to organize manipulate visualize your data in a pivot table format so now first you build the data model and write Dax and then we'll build a pivot T so let us go back to excel first you want to make sure that your power pivot tab is enable so you can do that by simply clicking on file options click on addins and then select Comm addins from the drop down click on go and you will see the power PIV option if it's unable or disable so you can just enable it and click okay and then you should see it on the ribbon on the top so now we'll first create relationships between these tables so you can go to the diagram View and over here you will see all the tables that we loaded from Power query to the data model so we'll keep the F table in the mirror and all the other tables right next to each other or surrounding the fact table so now we'll build relationships between the fact table and the dimension table whereas in fact table it stores the measurement metrix or facts related to the business operation so for example over here we see the details related to sales transaction such as unit sold unit price unit cost total revenue total cost total profit star schema which is what we call this a star schema where the fact table is in the middle and all the dimensions are surrounding the fact table and the dimension table contains the description of the objects that are there in the fact table it provides information about Dimensions such as value characteristics keys for example if we connect the customer ID from the fact table to the dimension table now it has created a relationship when I drag this if I want to pull any information about a particular customer I can look at their ID on the fact table and then I can find out their email address their job title and the first name and last name so this ID customer ID is the bridge between the fact table and the dimension table so for example if I want to find out like how many people with this job title purchased a particular product so this is where the customer ID will help to identify help to connect I can simply do a count of customer ID and in the pivot table which will give me the breakdown of count by each job title and this is where customer ID will Bridge the connection the customer ID is the primary key which is unique to each record so you will not find a same customer ID twice in the dim customer table each row will be unique now let us create the relationship between all these tables so I'll drag the item type with the item type ID in the dimension type table do the same for location ID I'll do the same for order priority and do the same for sales Channel ID now this is what we called a star schema because it looks in shape of a star where the fact table is in the middle and the dimension tables are surrounding it so the fact table in the middle helps us to measure different kpis and help us visualize different charts and the dimension table helps us to look at the data from different angles so for example if I want to look at what are the different order priorities so I want a count of different order priorities so I can use the order priority column in the dim order priority table and then I can get a count of the transaction ID so this will break down the total number of order priority by different account of total aut priorities by different order priority category and the same thing with region so if I want to find out what is the total revenue across different countries I can use the location ID to find out exactly to get a sum of the total revenue across different countries as we move on to the next section of uh writing Dax and doing pivot table you will understand the concept of how these tables are linked together and how it helps in terms of generating a pivot table go to the data View and go to fa sales transaction and over here we want to get the total revenue so we'll write the Dax code this is the place where you can write the Dax code so click anywhere in any of the cell and over here we'll type in Revenue colon and we'll type in a sum and type in total revenue you need a equal to after the colon click on the tick mark and as you see the calculation has generated a total revenue so we can format this we can add the dollar sign and we can remove the decimal so this was one of the kpis now we'll write the code for the next kpi which is number of orders so we'll write over here I'll call this as number of orders colon equal to and the formula for this is Count transaction ID so Dax will automatically create intellisense which will give you a suggestions of all the field that you can choose from click on the ti Mark and if you expand this you will see the value I'll just provide th000 separator and remove the decimal and now I'll add the third formula which is average revenue per order so I'll type in average revenue or order and the formula for that is going to be divide Revenue so it's this Dax formula divided by this so that's how we'll get the average order order average revenue per order so revenue and then we'll get the number of orders close the bracket click on the tick mark and as you see the value is generated now let us assign a dollar sign remove the decimal now we'll generate the pivot table directly from the data model or from the power pivot editor the good thing about writing the DX code is you don't need to format every time in the pivot table in the values SE and the good thing is you can use this measure anywhere in the pivot table once we have created the relationship in the diagram view you can use this measure across different tables now let us go through the steps of creating the pivot table so go to pivot table and then select pivot table and this will give you the option to select new or existing worksheet I'll select existing worksheet in the range for this is going to be cell A1 click on Okay click okay and now we will create three different kpis or pivot table for all the kpis so if you open fact sales transaction just drag all the kpis into the values now for our e-commerce sales dashboard let us create pivot table summarizing Revenue by sales channel so again go back to the data model or click on manage click on pivot table again pivot table click on existing worksheet go to cell E1 click okay click okay and you will see the dim sales channel so put this in the row and then you can add the revenue and the values so as you see these are two different tables we are getting sales channel from the dim sales Channel table and we are getting the revenue which is stored in the fact sales transaction table and as you see it has created the pivot table the reason is because we have created the relationship if you go to manage and as you see there's a relationship between the chml ID and the dimension table and the sales Channel ID in the fact table which is here and that's what I was talking to you about once you create the relationship you can use you can mix and match data from different tables in a single pivot table and as you will see you will use this Revenue measure across other pivot tables as well so you can reuse this measure again and again so now click on pivot table again now I'll create our third table which is the revenue by item type so click on existing worksheet put this in cell A4 click okay click okay and click on dim item type in the rows and revenue in the values again as you see we have using two different fields from two different tables that's because of the relationship that we've created and also the measure has been reused in this table in this pivot table and also in this pivot table so that's the be about power pivot where you can create relationships very you can write D where you can reuse this measures again and again so let us go back to manage again go to pivot table pivot table click on existing workchain go to sell each one click on Okay click okay and now we need the revenue by order priority so click on order priority drag order priority in rows and we'll get the revenue in the values field so now let us change the labels so over here we'll make this as sales chano we'll remove the grand too so go to design and off for rows and columns again we'll make this as order priority and remove the grand too again we'll do the same for item type and remove the grand too design off for rows and columns next is we want to visualize the data using charts and graphs visualization is the key important component for creating an interactive Excel dashboard charts and graph provide a visual representation of the data making it easier to understand and to analyze Trends patterns and relationship selecting the appropriate chart type is crucial for effective communication communicating your data Insight different chart types are suited for different purpose and data structures some common chart types and their uses include a column and a bar chart which Compares values across categories or shows Trends over time line chart displays continuous data or show changes over time P chart shows the composition of proportion of the whole the scatter plot chart visualizes relationship between two variables and the area chart demonstrates the magnitude of change over time or across categories for our e-commerce sales dashboard will create a pie chart showing Revenue by sales this will help us to identify click on the which is sales Channel click on insert click on pivit chart and click on buy click okay and this will generate your buy chart now let us create another chart for our sales dashboard now we want to create a column chart to show the revenue by order priority this will help us to identify which order priority is performing better so click on the order priority click on insert click on P charts and then select the cluster column click okay and your cluster column chart is ready and next chart we need is the bar chart showing the revenue of item type this will help us to identify which item type is performing better so click on the pivot table click on insert click on pivot chart click on pivot chart again click on bar click on okay now we want to customize the chart elements for Maximum Impact first We'll add the descriptive titles and access label next we'll adjust the chart colors and style then we'll add data labels then we'll customize the chart layout in the background so click on the pivot table first we will hide all the field buttons and then we'll click on the plus sign we'll make sure all these are enable we'll rename the chart title to sales Channel distribution and insert of the revenue we want this to be a percentage we'll click on the label options and insert of value we select the percentage same thing for this one instead of value we'll select this as percentage and in the number we'll select this as percentage and there will be two decimal and now we'll also change the color so for online we'll select this as yellow so click on the legend click on home and then click on more colors click on custom colors and the hex code we want to apply is F0 A500 click okay and now this will change it to yellow and we want this one as black so we'll change this to more colors click on custom and this will be called 1 191919 Click okay and we'll also change the color of the text of this one to White and your pie chart is ready and now let us customize the column chart so click on the column chart click on the plus sign and we'll add the access title we'll remove the Legends we'll hide all the field buttons and we'll change the name of the chart title we'll call this as Revenue by order priority this will be called order priority and this will be called revenue and we'll change the color of the bar to Black to select the column click on the color that you selected before and now we'll make changes to the bar chart so we'll remove all the field buttons click on the plus sign We'll add the access title we'll remove the legend and we'll rename this to revenue by item type and this will be called item type and this will be Revenue we'll also add change the color so click on the bars click on home and we'll select the yellow color we'll also add the data labels so click on plus sign add data labels and we'll do the same for the column chart add data labels so we need to create three kpis one for Revenue total orders and average dollar per order so we'll use the pivot table to build the kpis so we already have the kpi values now we just need to insert a rectangle so go to insert click on illustration click on shapes and then select the shape We'll add the shape here so we'll copy this three times or two times we need three shapes in total so we'll change the color for each so one will be gray so go to shape format go to shape fill go to more fill colors go to custom now the hex code we want is BF bf b a click okay this will turn into gray color now what now we want this one to be the black so this should be in your recent colors and we want this one to be yellow this should also be in your recent colors the yellow one so now we'll insert the value so click on the formula bar so the first value we want is the revenue so we'll provide the cell reference in the formula bar which is A2 press enter so you want to make this Center align make this bold we'll do the same for this one click on the formula bar equal to and we want this to be the number of orders which is going to be cell B2 press enter the text for this one should be white color so change the text color make this Center aligned do the same for this one click on the formula bar equal to and click on average revenue per order enter make this Center aligned make this bold you should make this one also as bold and now we need to insert the box which will be the name of the kpi so go to insert so we'll insert a text box click on text and then click on box call this as Revenue make this bold in Center Line so copy paste this twice this will be called total orders and this will be called average dollar per order now we need to group these two do the same for this this one as well group these two shapes also we'll do the same for this one the reason we grouping is now because once you move this shape both the shapes will move together so now your kPa is ready in this section we will explore how to enhance interactivity in your Excel dashboards using features such as slicers and timeline these features allows you to interact with the dashboard filter data explore different aspects of the data so slicers and timelines are powerful Excel features that allow you to user to interact with and filter pivot table data quickly and into tively they provide a way to visual way to select and deselect data points instantly updating the connected charts and table sers are used to filter categorical data such as product category or customer segments while as the timeline helps us to filter the continuous data range allowing user to focus on specific time periods there are few benefits of using the slicer and timelines first one it improves the user experience slicers and timelines provide intive and a visual way to the user to interact with data making it easier to explore and analyze information it also increases data flexibility users can quickly filter data to focus on specific subset by uncovering Insight that might be hidden in aggregate data connected charts and tables automatically update when the slices or timeline is selected providing realtime insights also it enhances the visual appeal a well-designed slicer and timeline adds a professional polish look to your dashboard now let us create the stepbystep slicers and timelines so go back to Excel and we'll click on the pivot table item type click on insert click on timeline click on auto date and now this will give you a timeline slicer so we'll change the color from this to gray since we don't have the gray option we create a custom style so we'll right Mouse click on this and make this as duplicate so now we can create our own template so we'll call this as gray timeline we'll select selected time log format this select more colors custom make this as BF BF BF click okay click okay and we'll select unselected time lock we don't need to change anything here then we'll select selected time lock we'll change this to more colors we make this as B BF BF BF click okay and now we'll select this slicer or this timeline as you see it just changed the color to gray now we need to add another slicer for Country so just select this pivot table go to design or go to pivot table analyze click on insert slicer and then we'll select under dim location we'll select the country click okay and we'll change the color this to gray which is this one and now we need to connect all the tables to these slicers and timeline so we'll click on uh slicer click on report connection and you you want to select all the pivot tables so if you filter or if you select any of the country it will filter the data for each and every PIV table and also it will reflect on the chart as well so click okay we'll do the same thing for the timeline slicer so go to timeline click on report connection expand this and select all the B tables click all of them and then click okay now if you select any particular year or month you you'll see the numbers will change and the chart will also reflect that change now all the components of the dashboard are ready now we just need to assemble it now that you have created the pivot tables Spirit charts and added interactivity with places and timelines it's time to assemble all the components into a cohesive Excel dashboard an effective dashboard layout should be intutive visually appearing and optimized for the users needs when assembling your dashboard components consider the following design principles you want to prioritize the information hierarchy place the most important Insight in the kpi in the prominent location use size colors and position to guide the users attention to key elements next you want to maintain visual consistency using a consistent color scheme font family and sizing throughout the dashboard align elements to create a clean and organized look you want to maximize the white space you want to avoid overcrowding your dashboard with too much information for our e-commerce dashboard will'll arrange the component as follows we'll copy paste all the chart slicers and kpis into a new tab and organize all the visuals as shown below and insert a text box with the text called Revenue dashboard so we'll call this tab as pit and we'll create another tab called dashboard and now we'll copy paste all the charts kpis slicers timeline slicer copy and just paste it here and now we'll reorganize this tab so first we'll place all the KPS on the top so we'll place this over here and we'll place the third KP over here we'll put the country slicer over here we'll expand this we'll put the P chart place it here we'll place the column chart over here right next to the P right next to the pie chart and we'll place the timeline slicer on the bottom and we'll add the bar chart on the bottom we'll minimize this and we can expand this now all the elements of the dashboard look organized now we just need to add the text box so go to insert click on text click on text box we'll just add it on the top and we'll call this as Revenue dashboard put Center aligned we'll make this as bold and now we just need to change the reference for this from cell A2 to we need to reference this back to the pivot table which is in pivot chart or in PIV tab so we'll call we'll reference this Insel A2 press enter same thing for this one we'll make sure this is bold and again reference this from C2 to Pivot Dam C2 referencer to PIV C2 press enter and make this bold and now if you filter you should see these values also change previously it was referencing to this sheet all the cells that's why when you click on any one of the countries the data would not filter because we had the incorrect reference now we'll go to the view Tab and remove the grid lines formula bars and headings now your dashboard is ready like select any particular country or select for any particular years or months so if I select let's say 2014 this will filter the data for that particular country and that particular year or you can just select here all for which you will see the data for all the countries and all the years so there you have it your tutorial on creating Dynamic Excel dashboard and before you go I have a special offer for you if you found this tutorial helpful and want to dive deeper into mastering Excel check out my free Excel course the link is in the description below don't forget to like share and subscribe for more tips and tutorials and drop in the comments below if this video was helpful to you so I can create more videos like thesein this Microsoft Excel dashboards course you'll learn how to transform raw data into insightful interactive visualizations this course will walk you through designing Dynamic charts and building professional dashboards that enhance data storytelling with Hands-On exercises you'll Master the tools and techniques needed to create compelling data visualizations in Excel and you'll be equipped to present data in a way that drives informed decision making me here from Office Tech skill develop this course struggling to find the perfect chart to represent your data let's unlock the secret of excel charts together today I'll walk you through the various chart types available in Excel and how to use the right one for your data hi I'm me Kar your ttor at offex skill.com where we specialize in helping professionals upgrade their Tech skills to enhance productivity and efficiency in the workplace let's discuss various chart types that you can Implement within Excel the topics we are going to cover in this section include column chart bar chart line chart P donut chart XY scatter plot chart area chart radar chart stock chart histogram chart paru chart waterfall chart box and whisker chart tree map chart map chart recommended chart now let's discuss what is a column chart column charts are drawn with a rectangular bars of various length representing different values there are different types of column charts such as cluster chart stack column chart and 100% stack Colum colum chart cluster column charts use colored to differentiate the column whereas the Stag column chart use varying thickness or colors for each column column charts are an effective means of displaying data variation over a period or comparing various elements typically the categories are placed on one axis and the value on another allowing for Clear visualization of changes or updates with each comparison point in the narrative So currently we have a data set which has sales by month for two products which is product one and product two so column chart is an excellent candidate for this kind of data set we'll select first the data set press CR a we'll go to insert we'll go to recommended charts we'll go to all charts we'll go to column and then we can select a cluster column chart now we can add the chart and access title so the chart title is already there now we can add the access title as well expand the chart so we can add the chart title as product 1 versus Product 2 sales the Y AIS is sales and xais is the month now we can change the color of the bars so for product one we can just select the blue bar and now change the color right Mouse click on the bar and change this to Red same thing with the orange bar right Mouse click on it and change the color to gray your column chart is ready if you want to change the chart type you can go to the Chart design tab then select change chart type so if you want to change from cluster column to stack column you can simply click on this and press okay or you can change it to some other style that you prefer now let's try to understand bar chart bar chart is drawn with rectangular bars of various lens representing different values it is often used to compare two or more discreete values the size of the rectangle relates to the magnitude of the value it represents so the taller indicates the greater value than shorter ones there are different types of bar chart such as cluster stacked 100% stacked bar charts bar charts are use color to differentiate the bars whereas the stack bar charts use varying thickness or colors for each bar now let's go back to the Excel go to the bar chart time over here we again have the same data which is data for sales by month for product one and product two now we can use this data set to create a bar chart so select the data click on insert go to recommended charts go to all charts click on bar and then select cluster bar chart go to the plus sign add the access title and now you can change the name from chart title to product one versus product to sales the access title is going to be month and the access title will be sales now we can change the color of the bar right Mouse click on the blue bar go to fill and we can change that to Gray and right click on the orange bar select F and we can change that to Red your cluster bar chart is ready now if you want to change from cluster bar to stack bar chart you can just go to the Chart design go to change chart type and then you can select stack bar or you can select 100% stack bar depending on whatever your preferences press okay as you see now the chart type is changed now let's try to understand what is line chart line chart is a type of chart that displays information as a series of data point it is also known as time series chart line charts are a type of chart that expresses information as a series of data points line charts are good for showing how something has changed or comparing two different things there are different types of line charts such as line stacked 100% stacked line with markers stacked line with markers and 100% stack line with markers chart now let's go back to the Excel go to the line chart time again we have the same data by month product sales for product one and product two now we will select this data set and create a line chart go to recommended charts go to all charts go to line and we'll select line chart press okay and now we'll go to the plus sign add the access title we'll change the title for y access to sales and change the title for xais to month and we'll change the color of the line first we'll add the chart title so we'll make it product one versus product two sales so we'll go to line one or product one go to the product two line right Mouse click and go to outline and select gray color color go to the blue line right Mouse click go to the outline Tab and select red and now your line chart is ready this is a simple line chart now let's try to understand Pi donut chart Pi donut chart are typically used to show the proportional contribution of each component to an overall chart the pie chart is a variation of circle graph or py graph also known as circle chart where each slice is drawn as a circle segment based on the angle swep out of its radius to the center point pie charts are used in finance and statistical field certain types of reports such as progress report graphic displays with limited space for text they are often thought be difficult to read by humans because our eyes do not naturally follow an arc still they can be read more effectively if labeled with their respective values let's go back to the Excel go to the pie chart Tab and now we'll insert a pie chart press control okay go to insert select recommended charts go to all charts go to Pi click on the pie chart press okay as you see the pie chart is created now we can add data labels to understand what is the revenue for each Pi now we can also create a donut chart so you can just copy this chart and paste right next to it go to chart design go to change chart type type and you can select donut chart press okay now let's try to understand XY scatter plot chart the scatter plot chart is a graph that shows an association between two variables for example when the graph is called an XY chart you can see the relationship between two variables on one graph when one variable can be plotted on the x axis the other one can be plotted on y- axis the scatter blot can show whether there is a correlation between two variables it also shows how strong the correlation might be making it easier for readers to understand what they're looking at so let's go to excel go to XY chart and now we'll use the same data set which is sales by month for product one and product two press CR a go to insert click on recommended charts go to all charts go to XY scatter plot chart and then select scatter with smooth lines and markers press okay insert the title access title provide the chart name to product one versus product to sales title for y AIS title for xaxis month your chart is ready now you can experiment with other XY charts by creating a duplicate chart so we'll create a duplicate chart we'll go to chart design change chart type and then you can select any chart you want for XY we select this chart for example and now you can compare between two charts and see which one works best for you now let's try to understand area chart an area chart is created by plotting the data on vertical I AIS and the category on the horizontal x axis the total area under each graph can be calculated by adding up all the areas under each rectangle stacked above each other the height of each rectangle corresponds to each relative value or frequency about different rectangles or bars stacked below it it is a graphical depiction on the number of frequency of observation in different categories let's go to the spreadsheet go to the area chart tab now we'll create a area chart for this data set so select contr a go to insert go to recommended charts go to all charts go under area and then select this chart press Okay click on the plus sign and add the access title provide the title name as product 1 versus Product 2 Saed the Y AIS will be sales and the x-axis is going to be the month now if you want to look at other area charts you can just simply copy and paste right next to it now you can change the chart Style so click on this chart go to chart design go to change chart type and now you can select any chart you want so if I want to select stacked area chart press okay now you can compare and see which one suits you need now let's try to understand radar chart radar chart diagram can simultaneously visualize three of more data sets it is typically used to compare qualitative data but also can be used for quantitative data the radar chart may seem intimidating but it is straightforward to use so this chart is helpful when you have multiple time series or different dimensions on the same chart so let's go back to the Excel go to the RAR chart time so now we have the salves for three products per month select all the data go to insert go to recommended charts go to all charts go to radar and click on okay now you can add the title as product comparison sales you can expand the chart to make it more visible you can create a duplicate chart and select other radar chart see a side by- side comparison of which chart puts better so select the duplicate chart go to chart design go to change chart type you can select this chart or you can select radar with markers press okay now let's try to understand the stock chart stock chart is a visual representation of historical data it helps to quickly overview of what has happened and identify patterns and Trends stock chart are handy for investors who want to know how their assets perform they can help them make better financial decisions and react quickly when the market shifts when to use a stock chart in Excel there are various reasons to utilize a stock chart in Excel such as tracking the performance of investment the stock market identifying trend lines and determine support and resistance level using the stock chart can Aid in making informed decisions now let's go to the Exel go to the stock chart T now we have a data of a particular stock CH price point and the market was open the highest value of the day the lowest value of the day closing value of the day and the adjusted closing value of the day also we have the volume and we have the data by dates now we want to create a stock chart which has a value of high low and close and we want to see it by date so we'll select these four columns so it's going to be date high low and close so now go to recommended charts go to all charts go to Stock press okay you can change the chart title to high low close you can include the access title so this is going to be price on the y axis and on the x-axis is going to be the date you can change the chart Style by clicking on the chart go to chart design and you can select style two as an option you can add a line for high and low to see how the trend changes over day so click on the top part of the line and then go to line then you can select a solid line we can change the color to gray go to the bottom part select the bottom part which is now you want to add a line for low select solid line and then select yellow as you see now it has a gray line for high and yellow line for low and the circle represents the closing price of the stock now let's try to understand the histogram chart the histogram chart is a graphical representation of the breakdowns of the data set into different categories a histogram chart is a graph that displays the frequency distribution of data typically split into categories so for example it can show how different sets are distributed in frequency let's go back to Exel go to the histogram chart tag we have the data by customer and by profit so now we can use a histogram in order to put the profit into different buckets because it's a range so it can put into different buckets we can group the customer into that bucket so select the data set go to insert go to recommended charts go to all charts go to histogram and press okay as you see Excel has automatically create the grouping we can add the chart title as customer profit range you can also add the data labels click on the chart go to plus sign and then add data labels So currently now the range is not visible so you can just expand the chart so you can see the highest and the lowest value currently there are six bins now you can customize the bins you can select the lowest and the highest value based on your need so if you click on the values on the x-axis you have the option to select the bin so there are six categories broken into 280,000 so you can change this to 300,000 and then press okay it has changed the range from 0 to 300,000 you can provide the bin width or also you can provide the number of bins and it will calculate and select the range so press enter and now you see there are five bins instead of six so you can customize based on your need let's try to understand what is a parto chart parto chart is a graphical representation of the ad20 principle it plots a cumulative percentage of events or outcomes found on vertical axis and the frequency or rate of these events on a horizontal axis a bar chart is a great way to understand what matters most in a given category it is easy to understand chart that people can use to identify and prioritize their efforts or decide what they want to focus on to achieve better results it helps to determine where to put most of your time and resources by looking at the top priorities we have data by region with total transaction so now we'll use Paro chart to understand which region has the highest transaction so select the data go to insert go to recommended charts go to all charts go to histogram and then select Paro chart click okay you can expand the chart and provide the chart name as transaction count by region as you see Europe has the highest transaction so this will tell a user that they need to place more emphasis on Europe and subaran Africa since they both have the highest transactions now let's try to understand what is waterfall chart waterfall chart is a type of chart that shows how various levels of one or more variables stack up against each other it is also known as hierarchical barcraft because it depicts the data in water form or hierarchical form shareholding companies typically use waterfall charts to Direct Cash Flow during a financial year the chart is divided into months with each waterfall chart showing the available funds for that month now let's go to Excel and implement this chart go to the waterfall chart tab the data set shows the date and the daily profit so now I'll use waterfall chart to understand this data so select the data go to insert go to recommended charts go to Waterfall click okay the chart is created now we can change the chart title to daily profit so click on the legend for increase go to the color icon and change from solid go to solid and now change it to Green click on the decrease Legend go to the coloron go to solid fill and change it to Red the bars are more visible and you can see where the profit has increased or decreased based on the date let's try to understand box and whisker chart box and whisker chart represents the data distribution the vertical line above and below boxes represents the maximum and the minimum value the box shows the value between the 25th through 75th percent and the horizontal line represents the 50th percent and now we will create a box and whisker chart based on this data set so click on insert go to recommended charts go to all charts select box and whiskers click okay and now you see the chart is created we can add the title as transaction by region and we can also add the access title select the plus sign take a mark on the access title the y- axis will be called transaction the x-axis will be called region we can change the color of the bars make it yellow so it's more visible we can also select more data label options go to the number drop-down and go to General and select number you can reduce the decimal to zero and as you see the decimals are removed the minimum value maximum value the median the 25th percentile and the 75th percentile and also the 50th percentile I can expand the chart to make the data more visible and the data labels as you see now after expanding the chart the data looks more visible the line on the bottom shows the minimum value the line on the top shows the maximum value 57 is the 25th percent X is the average 78 is the median 508th percental and 90 is the 75th percenti let's try to understand the tree map chart a tree map chart is a way to visualize the hierarchy for example it is often used to display the proportion of different values in a data set prap can be dra drawn as squares or rectangles and nested within one another to provide greater detail I.E more extensive and smaller rectangles can be drawn inside one another so when to use a tree map chart tree map provides a quick and easy way to summarize large amounts of hierarchical data when plotting nodes the dimensions are configured based on numerical values assigned per node this makes identifying Trends between all categories much more straightforward in comparing one type with another or even among different groups within the same Leaf Branch hierarchic now let's go to the Excel file over here we have three columns this is essentially a hierarchical data the first level is the region the second level is the country and we want to find out the revenue for each country so select all the data click on insert go to recommended charts go to all charts go to tree map click on the Dre map press okay the dream map chart is created you can expand the chart now we can add the title sales by country and we can move the legend to bottom now the chart is more visible the data is broken by region so the yellow one is Europe the navy blue is subsaharan Africa the light blue is Middle East and North Africa the gray is Central America and Caribbean and then the boxes are divided based on the value of the sales so in Europe Serbia has the highest sales in subs and Africa Benin has the highest sales in Middle East and North Africa Libya has the highest sales so anyone looking at this three map chart can easily figure it out which region and which country has the highest sales let's understand map chart map charts are a visual representation of data in a geographical context they help display data comparison between the region and distribution points across the globe map sh provides visual representation of data on geographic regions that are challenging to understand what formats maap charts can show anything from demographics comparative statistics or any other data type that would be difficult to see in a table form map charts are also accessible for USS to read and understand because they give clear and concise information and now select the data we have the same data from the tree map chart which is region country and revenue select all the data go to insert go to recommended charts go to all charts select map and now press okay and now you can see the revenue by country let me expand the charts the country with the darkest color has the highest sale and the country with the lightest color has the lowest sales so now we can change the title to sales by country and we'll change the color go to the brush sign and then select color and then you can select this color palette you can also add the data labels that would provide you with the values now let's try to understand recommended charts the recommended charts feature in Exel is a function that allows user to automatically generate additional types of chart that are not available in the ribbon three things can cause a chart to be generated user selected data data from external Source Dynamic data meaning there's no need for user intervention this feature generates and displays a list of recommended sharts and graph based on your data selection the list is displayed under insert recommended charts so now let's go to the Excel file and now select the data again go to insert go to recommended charts and over here you get a list of recommended chart that Excel provides you and you can select any one of these charts based on your requirement so there you have it a comprehensive tutorial on mastering Excel chart types now I'm going to show you how to customize your Excel chart to make them look professional and impactful you can download the file from the description the topics we are going to cover in this video we'll start start with organizing your data next we'll select the best chart type then we'll apply chart Styles and template then we can adjust the chart elements and the labels we can use data labels and grid lines to enhance your chart we'll apply Color themes and pallets we'll discuss how you can utilize combination chart and secondary AIS we'll show where you can incorporate trend lines we'll discuss different chart elements that you can apply and finally we'll go through some best practices for chart customization so before diving into customization it's essential to understand that you need to organize your data so go back to excel first we want to ensure your data is organized in a tabular format you have the column headers on the top and there are no blank rows or columns in your data set to make your data easier to reference when creating and updating charts you can put this data into a uh name range so we'll just select this data from A1 through C13 we go to the formula time and then click on Define name range and in our case the range is already created so if I select edit you can provide a name change so you can name it as like sales data and click okay so anytime now you want to refer to the data you can just use this the name which is sales data now we want to choose the best chart type for this data so go to insert in our case we'll select a column chart because this stereotype is better suited to show in a column chart so I'll just select a cluster column chart because we want to compare two different products side by side now we want to apply some chart Styles and also we can discuss some templates that you can use so if you click on the chart you can go to the Chart design Tab and over here you have options to select any of the chart Style I'll just keep it default next we'll adjust some chart elements and labels so if you click on this plus sign you can click on access title you can change the legend on the top make it on the top you can remove the grid lines and now we can also change the name of the chart so we'll make it as monthly sales product one versus product two this is called revenue and this is called month now you can enhance the chart look by adding the data labels or data tables in Grid lines so if I click on the chart again if I click on data labels you'll see the data or you'll see the value for each and every bar but then you'll have to expand the chart or you can use data table in of labels you can select data table and this will show all the values on the bottom so it's more organized and clean you can also add a grid line so you know exactly it becomes easier to analyze you can apply Color theme and palletes so Excel has already lot of pre-built colors and palletes that you can utilize so if you click on the plus sign or click on the brush icon you can select go to color and then you can select any of the color palettes that you like you can also go to chart design and select it from there and you can select from here as well it's one the same thing just different ways of accessing let's say if I don't like the color that's being displayed play or shown in the color palette you can also manually change it by clicking on the bar and this will select all the bars and then you can go to home and then you can just simply choose the color of your choice so I'll select black and I'll select the other one as orange or make it gray we can also Implement some Advanced Techniques we can use combination charts and secondary axis to look at the same data in a different way so you can click on this chart go to change chart or go to chart design select change chart type you can make this combo and over here you can select that you want the product one to be a column and product two be as shown as line so this is how your data would show so click okay and as you see you can add secondary access your secondary access is showing here which is product two now you can change the color by simply clicking on the bar and you can also change the color of the line you can go to format go to shape outline and then select any color of your choice you can also incorporate prend line if you want to see how the data is trending over particular period of time so let's say if you want to find out what's the trend for product one you can just simply click on the plus sign click on the trend line and this will give you the option if you want to see the trend line for product one or product two so I'll select product one and as you see the trend line is slightly increasing it's more or less the same if you want to change some formatting of the trend line you can right Mouse click on the trend line and click on format trend line and you will see this diog box will open up currently it's showing linear trend line you can change it to exponential logarithmic polinomial power so depending on your choice I'll stick to linear you can apply additional chart elements or you can customize the chart Elements by just simply clicking on the chart go to chart design and then go to the drop down of the chart elements and over here you have more options based on any changes you want to make to ensure your customized charts are effective and professional you can follow some of these best practices for customization first you want to prioritize Clarity and simplicity by removing unnecessary chart elements such as redundant label and you want to focus on the key message that you want to convey next you to use clear and concise style for your chart and also for your access so the person who's looking at your chart can easily understand the context of your data next you want to choose the appropriate chart type and data ranges that accurately represents your data and insight avoiding distorting or misrepresenting data by using misleading chart types or scale can send the wrong message to the person who's looking at your chart consider your target audience andent tailor your chart design and formatting use colors fonts styles that are easy to read and but now I'm going to show you how to create an engaging and effective Excel dashboard a crucial skill for any data driven professional so you will be building this dashboard as you see in the screen this is a very fastpaced video where I won't go into the nuts and bolts of each and every detail I just going to show you how to build this dashboard from scratch you can download the file from the description the topics we are going to cover in this video we'll start off with understanding interactive Excel dashboard then we'll plan free Excel dashboard then we'll collect and prepare your data we create a pivot table for data analysis we visualize the data through charts and graphs we increase the interactivity with Excel features and will finally assemble all the components into a dashboard interactive dashboards in Excel are a type of dashboard that allows you to track and measure key performance indicators and metrics they provide visual representation of complex data making it easier to understand and analyze with interactive dashboards you can change data inputs filter data drill down into specific details to gain insight and make data driven decisions next we need to plan your Excel dashboard before creating an Excel dashboard it's essential to understand the needs and expectation of your target audience consider who will be using the dashboard and what information they need to make informed decisions clearly Define the objectives of your dashboard and ensure they align with your organization's goal for this tutorial we create a revenue dashboard with a small e-commerce business focusing on key metrics like Revenue order customer acquisition the target audience with this dashboard will be sales and marketing team who will use the insight to optimize the product performance customer acquisition strategy and overall business growth in this tutorial you will use a zip file that contains data related to yearly sales Data customer item type location order priority and sales Channel you will see all this data in a zip folder now let us clean and organize the data so open a blank Excel workbook go to data click on get data click on from file click on Excel workbook click on the zip folder where you've extracted the data and then select 2014 sales data I'll select the 2014 sales data click on import and you will see a dialog box click on sheet one click on transform data and now your power query dialog box will open our power query editor will open now we'll just rename this to 2014 sales data so right M click click on rename and we'll rename this to 2014 sales data click on close and load to and then select only create connection now we'll repeat the same process for each Excel file so go to get data from file from Excel workbook click on 2015 sales data click on import now the dialog box will show up click on sheet one click on transform data this will load the data into parquer editor and now we'll rename this as 2015 sales data again select close and load to only create connection click okay now go to get data from file from Excel workbook now 2016 import click on sheet one transfering data now the data will load into Power query editor rename this file 2016 sales data select close and load to select only create connection from file from Excel workbook 2017 series data import take on sheet one transform data rename this to 2017 series data select close and load to only create connection click okay click on get data from file from Excel workbook 2018 sve data click on import click on sheet one transform data rename this two 2018 sales data select close and load to only create connection click okay click on get data from files from Excel workbook from 20189 saves data click import click on sheet one transform data rename this 2019 sales data select close and load to click only create connection get data from Excel workbook click on 2020 sales data import click on sheet one select transform data rename this to 2020 sales data click on close and load to click on only create connection again get data from file Excel workbook now click on 2021 sales data import click on sheet one transform data rename this make it 2021 sales data click on close and load to now all your sales data are loaded for each and every year now we'll do the same thing for other Excel files so go to get data from file from Excel workbook click on customer click on sheet one click on transform data and we'll call this as dim customer dim is dimension I'll explain the concept of dim and fact once we import the data into the power query so now let us go back to data click on get data from file from Excel workbook let us import item type click import click on sheet one transform data rename this to dim item type select close and load to select only create connection click okay click on get data from file from Excel workbook click on location import click on sheet one click on transform data rename this to demm location click on close and load to select only create connection click okay go to get data from Excel bookbook Select Auto priority click import click on sheet one click on transform data rename this to dim order priority select closeing load to click on only create connection Okay click on get data from file Excel workbook and select sales Channel import rename this to dim or click on sheet one click on transform data rename this to dim sales Channel click on close and load to select only create connection click okay now let us append all this data into a single power query where we'll combine the data from 2014 all the way to 2021 go to get data go to launch par qu editor so click on 2014 sales data query click on openend query click on open query is new and now we'll select all the queries so go to three or more tables and will select 2015 through 2021 click on ADD click okay now this has appended all the data from all these queries into a single query so we'll rename this call it fact sales transaction now now we want to filter out certain columns so we'll go to choose columns click on choose columns let us remove all the duplicates so just select all the data by pressing CR a right Mouse click and click on remove duplicates now we can load all this data into a tra model so right Mouse click on dim customer click on load to and then select add this to the data model click okay and now the data is loaded do the same thing for irm type load to add this to the data model click okay same thing over here same thing for order priority as well click on load to add this to the data model click okay same thing over here and also you want to load this the F fact sales transaction into the data model so click on load to add this to the T model now we'll create a p table for data analysis pivot tables are a powerful tool for data analysis in Excel that allows you to summarize reports and analyze large amounts of data this enables you to organize manipulate visualize your data in a pivot table format so now first you build the data model and write Dax and then we'll build a pivot T so let us go back to excel first you want to make sure that your power pivot tab is enable so you can do that by simply clicking on file options click on addins and then select Comm addins from the drop down click on go and you will see the power PIV option if it's unable or disable so you can just enable it and click okay and then you should see it on the ribbon on the top so now we'll first create relationships between these tables so you can go to the diagram View and over here you will see all the tables that we loaded from Power query to the data model so we'll keep the F table in the mirror and all the other tables right next to each other or surrounding the fact table so now we'll build relationships between the fact table and the dimension table whereas in fact table it stores the measurement metrix or facts related to the business operation so for example over here we see the details related to sales transaction such as unit sold unit price unit cost total revenue total cost total profit star schema which is what we call this a star schema where the fact table is in the middle and all the dimensions are surrounding the fact table and the dimension table contains the description of the objects that are there in the fact table it provides information about Dimensions such as value characteristics keys for example if we connect the customer ID from the fact table to the dimension table now it has created a relationship when I drag this if I want to pull any information about a particular customer I can look at their ID on the fact table and then I can find out their email address their job title and the first name and last name so this ID customer ID is the bridge between the fact table and the dimension table so for example if I want to find out like how many people with this job title purchased a particular product so this is where the customer ID will help to identify help to connect I can simply do a count of customer ID and in the pivot table which will give me the breakdown of count by each job title and this is where customer ID will Bridge the connection the customer ID is the primary key which is unique to each record so you will not find a same customer ID twice in the dim customer table each row will be unique now let us create the relationship between all these tables so I'll drag the item type with the item type ID in the dimension type table do the same for location ID I'll do the same for order priority and do the same for sales Channel ID now this is what we called a star schema because it looks in shape of a star where the fact table is in the middle and the dimension tables are surrounding it so the fact table in the middle helps us to measure different kpis and help us visualize different charts and the dimension table helps us to look at the data from different angles so for example if I want to look at what are the different order priorities so I want a count of different order priorities so I can use the order priority column in the dim order priority table and then I can get a count of the transaction ID so this will break down the total number of order priority by different account of total aut priorities by different order priority category and the same thing with region so if I want to find out what is the total revenue across different countries I can use the location ID to find out exactly to get a sum of the total revenue across different countries as we move on to the next section of uh writing Dax and doing pivot table you will understand the concept of how these tables are linked together and how it helps in terms of generating a pivot table go to the data View and go to fa sales transaction and over here we want to get the total revenue so we'll write the Dax code this is the place where you can write the Dax code so click anywhere in any of the cell and over here we'll type in Revenue colon and we'll type in a sum and type in total revenue you need a equal to after the colon click on the tick mark and as you see the calculation has generated a total revenue so we can format this we can add the dollar sign and we can remove the decimal so this was one of the kpis now we'll write the code for the next kpi which is number of orders so we'll write over here I'll call this as number of orders colon equal to and the formula for this is Count transaction ID so Dax will automatically create intellisense which will give you a suggestions of all the field that you can choose from click on the ti Mark and if you expand this you will see the value I'll just provide th000 separator and remove the decimal and now I'll add the third formula which is average revenue per order so I'll type in average revenue or order and the formula for that is going to be divide Revenue so it's this Dax formula divided by this so that's how we'll get the average order order average revenue per order so revenue and then we'll get the number of orders close the bracket click on the tick mark and as you see the value is generated now let us assign a dollar sign remove the decimal now we'll generate the pivot table directly from the data model or from the power pivot editor the good thing about writing the DX code is you don't need to format every time in the pivot table in the values SE and the good thing is you can use this measure anywhere in the pivot table once we have created the relationship in the diagram view you can use this measure across different tables now let us go through the steps of creating the pivot table so go to pivot table and then select pivot table and this will give you the option to select new or existing worksheet I'll select existing worksheet in the range for this is going to be cell A1 click on Okay click okay and now we will create three different kpis or pivot table for all the kpis so if you open fact sales transaction just drag all the kpis into the values now for our e-commerce sales dashboard let us create pivot table summarizing Revenue by sales channel so again go back to the data model or click on manage click on pivot table again pivot table click on existing worksheet go to cell E1 click okay click okay and you will see the dim sales channel so put this in the row and then you can add the revenue and the values so as you see these are two different tables we are getting sales channel from the dim sales Channel table and we are getting the revenue which is stored in the fact sales transaction table and as you see it has created the pivot table the reason is because we have created the relationship if you go to manage and as you see there's a relationship between the chml ID and the dimension table and the sales Channel ID in the fact table which is here and that's what I was talking to you about once you create the relationship you can use you can mix and match data from different tables in a single pivot table and as you will see you will use this Revenue measure across other pivot tables as well so you can reuse this measure again and again so now click on pivot table again now I'll create our third table which is the revenue by item type so click on existing worksheet put this in cell A4 click okay click okay and click on dim item type in the rows and revenue in the values again as you see we have using two different fields from two different tables that's because of the relationship that we've created and also the measure has been reused in this table in this pivot table and also in this pivot table so that's the be about power pivot where you can create relationships very you can write D where you can reuse this measures again and again so let us go back to manage again go to pivot table pivot table click on existing workchain go to sell each one click on Okay click okay and now we need the revenue by order priority so click on order priority drag order priority in rows and we'll get the revenue in the values field so now let us change the labels so over here we'll make this as sales chano we'll remove the grand too so go to design and off for rows and columns again we'll make this as order priority and remove the grand too again we'll do the same for item type and remove the grand too design off for rows and columns next is we want to visualize the data using charts and graphs visualization is the key important component for creating an interactive Excel dashboard charts and graph provide a visual representation of the data making it easier to understand and to analyze Trends patterns and relationship selecting the appropriate chart type is crucial for effective communication communicating your data Insight different chart types are suited for different purpose and data structures some common chart types and their uses include a column and a bar chart which Compares values across categories or shows Trends over time line chart displays continuous data or show changes over time P chart shows the composition of proportion of the whole the scatter plot chart visualizes relationship between two variables and the area chart demonstrates the magnitude of change over time or across categories for our e-commerce sales dashboard will create a pie chart showing Revenue by sales this will help us to identify click on the which is sales Channel click on insert click on pivit chart and click on buy click okay and this will generate your buy chart now let us create another chart for our sales dashboard now we want to create a column chart to show the revenue by order priority this will help us to identify which order priority is performing better so click on the order priority click on insert click on P charts and then select the cluster column click okay and your cluster column chart is ready and next chart we need is the bar chart showing the revenue of item type this will help us to identify which item type is performing better so click on the pivot table click on insert click on pivot chart click on pivot chart again click on bar click on okay now we want to customize the chart elements for Maximum Impact first We'll add the descriptive titles and access label next we'll adjust the chart colors and style then we'll add data labels then we'll customize the chart layout in the background so click on the pivot table first we will hide all the field buttons and then we'll click on the plus sign we'll make sure all these are enable we'll rename the chart title to sales Channel distribution and insert of the revenue we want this to be a percentage we'll click on the label options and insert of value we select the percentage same thing for this one instead of value we'll select this as percentage and in the number we'll select this as percentage and there will be two decimal and now we'll also change the color so for online we'll select this as yellow so click on the legend click on home and then click on more colors click on custom colors and the hex code we want to apply is F0 A500 click okay and now this will change it to yellow and we want this one as black so we'll change this to more colors click on custom and this will be called 1 191919 Click okay and we'll also change the color of the text of this one to White and your pie chart is ready and now let us customize the column chart so click on the column chart click on the plus sign and we'll add the access title we'll remove the Legends we'll hide all the field buttons and we'll change the name of the chart title we'll call this as Revenue by order priority this will be called order priority and this will be called revenue and we'll change the color of the bar to Black to select the column click on the color that you selected before and now we'll make changes to the bar chart so we'll remove all the field buttons click on the plus sign We'll add the access title we'll remove the legend and we'll rename this to revenue by item type and this will be called item type and this will be Revenue we'll also add change the color so click on the bars click on home and we'll select the yellow color we'll also add the data labels so click on plus sign add data labels and we'll do the same for the column chart add data labels so we need to create three kpis one for Revenue total orders and average dollar per order so we'll use the pivot table to build the kpis so we already have the kpi values now we just need to insert a rectangle so go to insert click on illustration click on shapes and then select the shape We'll add the shape here so we'll copy this three times or two times we need three shapes in total so we'll change the color for each so one will be gray so go to shape format go to shape fill go to more fill colors go to custom now the hex code we want is BF bf b a click okay this will turn into gray color now what now we want this one to be the black so this should be in your recent colors and we want this one to be yellow this should also be in your recent colors the yellow one so now we'll insert the value so click on the formula bar so the first value we want is the revenue so we'll provide the cell reference in the formula bar which is A2 press enter so you want to make this Center align make this bold we'll do the same for this one click on the formula bar equal to and we want this to be the number of orders which is going to be cell B2 press enter the text for this one should be white color so change the text color make this Center aligned do the same for this one click on the formula bar equal to and click on average revenue per order enter make this Center aligned make this bold you should make this one also as bold and now we need to insert the box which will be the name of the kpi so go to insert so we'll insert a text box click on text and then click on box call this as Revenue make this bold in Center Line so copy paste this twice this will be called total orders and this will be called average dollar per order now we need to group these two do the same for this this one as well group these two shapes also we'll do the same for this one the reason we grouping is now because once you move this shape both the shapes will move together so now your kPa is ready in this section we will explore how to enhance interactivity in your Excel dashboards using features such as slicers and timeline these features allows you to interact with the dashboard filter data explore different aspects of the data so slicers and timelines are powerful Excel features that allow you to user to interact with and filter pivot table data quickly and into tively they provide a way to visual way to select and deselect data points instantly updating the connected charts and table sers are used to filter categorical data such as product category or customer segments while as the timeline helps us to filter the continuous data range allowing user to focus on specific time periods there are few benefits of using the slicer and timelines first one it improves the user experience slicers and timelines provide intive and a visual way to the user to interact with data making it easier to explore and analyze information it also increases data flexibility users can quickly filter data to focus on specific subset by uncovering Insight that might be hidden in aggregate data connected charts and tables automatically update when the slices or timeline is selected providing realtime insights also it enhances the visual appeal a well-designed slicer and timeline adds a professional polish look to your dashboard now let us create the stepbystep slicers and timelines so go back to Excel and we'll click on the pivot table item type click on insert click on timeline click on auto date and now this will give you a timeline slicer so we'll change the color from this to gray since we don't have the gray option we create a custom style so we'll right Mouse click on this and make this as duplicate so now we can create our own template so we'll call this as gray timeline we'll select selected time log format this select more colors custom make this as BF BF BF click okay click okay and we'll select unselected time lock we don't need to change anything here then we'll select selected time lock we'll change this to more colors we make this as B BF BF BF click okay and now we'll select this slicer or this timeline as you see it just changed the color to gray now we need to add another slicer for Country so just select this pivot table go to design or go to pivot table analyze click on insert slicer and then we'll select under dim location we'll select the country click okay and we'll change the color this to gray which is this one and now we need to connect all the tables to these slicers and timeline so we'll click on uh slicer click on report connection and you you want to select all the pivot tables so if you filter or if you select any of the country it will filter the data for each and every PIV table and also it will reflect on the chart as well so click okay we'll do the same thing for the timeline slicer so go to timeline click on report connection expand this and select all the B tables click all of them and then click okay now if you select any particular year or month you you'll see the numbers will change and the chart will also reflect that change now all the components of the dashboard are ready now we just need to assemble it now that you have created the pivot tables Spirit charts and added interactivity with places and timelines it's time to assemble all the components into a cohesive Excel dashboard an effective dashboard layout should be intutive visually appearing and optimized for the users needs when assembling your dashboard components consider the following design principles you want to prioritize the information hierarchy place the most important Insight in the kpi in the prominent location use size colors and position to guide the users attention to key elements next you want to maintain visual consistency using a consistent color scheme font family and sizing throughout the dashboard align elements to create a clean and organized look you want to maximize the white space you want to avoid overcrowding your dashboard with too much information for our e-commerce dashboard will'll arrange the component as follows we'll copy paste all the chart slicers and kpis into a new tab and organize all the visuals as shown below and insert a text box with the text called Revenue dashboard so we'll call this tab as pit and we'll create another tab called dashboard and now we'll copy paste all the charts kpis slicers timeline slicer copy and just paste it here and now we'll reorganize this tab so first we'll place all the KPS on the top so we'll place this over here and we'll place the third KP over here we'll put the country slicer over here we'll expand this we'll put the P chart place it here we'll place the column chart over here right next to the P right next to the pie chart and we'll place the timeline slicer on the bottom and we'll add the bar chart on the bottom we'll minimize this and we can expand this now all the elements of the dashboard look organized now we just need to add the text box so go to insert click on text click on text box we'll just add it on the top and we'll call this as Revenue dashboard put Center aligned we'll make this as bold and now we just need to change the reference for this from cell A2 to we need to reference this back to the pivot table which is in pivot chart or in PIV tab so we'll call we'll reference this Insel A2 press enter same thing for this one we'll make sure this is bold and again reference this from C2 to Pivot Dam C2 referencer to PIV C2 press enter and make this bold and now if you filter you should see these values also change previously it was referencing to this sheet all the cells that's why when you click on any one of the countries the data would not filter because we had the incorrect reference now we'll go to the view Tab and remove the grid lines formula bars and headings now your dashboard is ready like select any particular country or select for any particular years or months so if I select let's say 2014 this will filter the data for that particular country and that particular year or you can just select here all for which you will see the data for all the countries and all the years so there you have it your tutorial on creating Dynamic Excel dashboard and before you go I have a special offer for you if you found this tutorial helpful and want to dive deeper into mastering Excel check out my free Excel course the link is in the description below don't forget to like share and subscribe for more tips and tutorials and drop in the comments below if this video was helpful to you so I can create more videos like these\n"