Your cart is empty.
Keep shopping
Teach on Udemy
Turn what you know into an opportunity and reach millions around the world.
Learn MoreInteractive Microsoft Excel Dashboard for Data Analyst
Rating: 4.5 out of 54.5(300 ratings)
4,253 students
Interactive Microsoft Excel Dashboard for Data Analyst
How to build Interactive Excel Dashboards that Update with ONE CLICK! Creative and outstanding design Excel Dashboard
Created byIsmaila Omeiza Mumuni
Last updated 10/2024
English
English [Auto],
What you'll learn
- Learn how to analyze and design a well presentable dashboard for organizations and businesses
- You will learn how to Monetize your Excel Dashboard skill and make money working as a freelancer or an In-House Data analyst
- You will learn how to use Excel formulas and Pivot Table successfully
- You will learn Excel Power Query for data cleaning and transformation
- You will learn how to design modern dashboards
- You will learn how to use Power Pivot to manage huge rows of data without breaking down
- You will learn how to use DAX measures to speed up analysis and create dashboards
- How to create your portfolio (Online presence)
- How to receive payment from clients
- How to approach clients project
- Will learn how to pitch for jobs
Explore related topics
Course content
17 sections • 168 lectures • 28h 9m total length
- Start watching me first1:26
Watch the watch me first video to understand the project goals and required steps. Follow the series step by step to prevent errors and ensure you complete all videos.
- How to use the practice files2:41
Learn how to use the practice files and project files for the sales dashboard, including renaming extensions, enabling macros, and connecting to external data.
- How to Create Dashboard that Updates Dynamically4:03
Learn to build a dynamic Excel dashboard by loading folder data into workbook tables and refreshing to update visuals, while considering data size and performance.
- Getting Icons for your Projects5:39
Learn to add visual icons and 3D icons in your Excel dashboards by exploring icons, pictures, and illustrations, using categories, search, multiple selection, rotation, and color customization.
- How to Pick Colors for your Projects3:10
Discover practical techniques for selecting colors in Excel dashboards using color pickers, RGB and hex codes, and pulling colors from images or thumbnails to ensure cohesive designs.
- Update broken links1:00
Resolve broken links by refreshing external data sources and continuing through the prompts, without editing links, to update the dashboard data correctly.
- How to Handle Errors in Power Query before you Start5:24
Identify and fix errors in Power Query before building your Excel dashboard by locating the source, editing the query, and updating file parts.
- What Excel Understands8:11
Explore how Excel understands formulas, numbers, and cell references, and use cell references with criteria and ranges instead of hard-coded values in if formulas.
- What do think about the course1:18
Rate the course, share five-star feedback, and continue learning data analytics with interactive Excel dashboards designed for analysts.
- Intro to the Dashboard4:37
Develop an interactive Excel dashboard from scratch using formulas and functions to analyze 2022 sales data from a 2019–2022 dataset, without pivot tables, and generate simple insights and recommendations.
- Analyzing Revenue for 2022 and grouping other years14:20
Build an interactive Excel dashboard to analyze 2022 revenue and group 2019–2021 data, using filters, unique lists, data validation, and pivot table techniques to update charts automatically.
- Creating and sorting Product chart17:24
Craft an interactive Excel dashboard by building a product revenue chart, applying data validation and filters, and using index and match to sort products from highest to lowest.
- Formatting our product chart4:13
- Revenue by means of sales5:09
Explore revenue by order channel, comparing online and in-store sales for 2022, then build and format a chart on the dashboard using Excel calculations.
- Creating map7:06
Build an interactive Excel map that switches between revenue and quantity sold, detailing data cleaning, unique state lists, and dynamic formulas for state-level insights.
- Creating switch for Map chart6:17
Learn to build a dynamic map chart in Excel by using transpose, data validation, and if logic to switch between revenue and quantity sold, with live chart updates.
- Formatting our product chart and creating dynamic header16:54
Format the product chart and create a dynamic header for an interactive Microsoft Excel dashboard. Use the Developer tab and form controls to drive the header and chart by selection.
- Formatting and picking colors7:04
Learn how to format an Excel dashboard by selecting logo colors, applying consistent highlight colors with transparency to 34%, removing lines, sizing elements, and aligning legends for clear data visualization.
- Chart formatting11:46
Learn practical chart formatting to highlight the 2022 focus in an Excel dashboard, including text boxes, area charts, color coding, labeling, legends, and revenue breakdowns by orders and online.
- Creating a caption4:31
Explore building a dynamic Excel dashboard that uses conditional logic to display the product with the highest revenue for year 2022, with references to prior years and adaptable year switching.
- Formatting and making Recommendations25:30
Format charts with color scales to distinguish negative values from positive values and revenue from quantity sold. Build targeted recommendations using top populated states to boost in-store revenue with ads.
- Full dashboard formatting4:47
Master full dashboard formatting in Excel by creating and aligning lines, applying gradients and transparency, and configuring interactive measures like revenue till date and quantity sold till date.
- Creating switchable values24:05
- Dashboard Introduction2:05
The dataset used in this dashboard is data pulled from income and expenses statements; it has 10 columns and 487 rows for 2021.
Your task (insight required from the dataset)
1) What is the stream of income, how much do we make in our income categories, and what are the categories we spent this money on, show the result hierarchically?
2) Inflow and outflow. Make Debit and credit plus amount left easy to read.
3) Analyze the income sources and show how much had been made on each source to date.
4) On a weekly basis show expenses by categories
5) On a single chart ? visualize and transit between income and expenses on a monthly basis
6) What are the top-5 things we spent money on, mostly?
7) Show important KPI’s on cards ? for easy readability and for quick decision making
Active filters needed to interact the data and the dashboard: Month and categories column
- Data Cleaning and Transformations7:12
Week-1
Personal Expenses Dashboard….
The dataset used in this dashboard is data pulled from income and expenses statements; it has 10 columns and 487 rows for 2021.
Your task (insight required from the dataset)
1) What is the stream of income, how much do we make in our income categories, and what are the categories we spent this money on, show the result hierarchically?
2) Inflow and outflow. Make Debit and credit plus amount left easy to read.
3) Analyze the income sources and show how much had been made on each source to date.
4) On a weekly basis show expenses by categories
5) On a single chart ? visualize and transit between income and expenses on a monthly basis
6) What are the top-5 things we spent money on, mostly?
7) Show important KPI’s on cards ? for easy readability and for quick decision making
Active filters needed to interact the data and the dashboard: Month and categories column
- Creating Dashboard Layout28:48
Learn to design an Excel dashboard layout by saving your work, arranging shapes and colors, and building a pivot table to analyze data with mean and max.
- Expenses Flows23:45
Build an interactive Excel dashboard to analyze expenses flows, classify cash outflows by category, and visualize with a waterfall chart and polished visuals.
- Creating Cash Outflow and Inflows19:42
Build an interactive Excel dashboard to show cash inflow and outflow, balance on a single chart, with formatting, colors, legends, and weekly analyses.
- Cards KPI7:02
Learn to create KPI cards in an interactive Excel dashboard by configuring dimensions, measures, and visuals, including color changes, icons, and layout alignment.
- Monthly Expenses Analysis8:24
Create an interactive Excel dashboard for monthly expenses analysis by linking income, expenses, and balance, using pivot tables, form controls, and dynamic transitions to compare month-by-month data.
- Integrating and Formatting Filters22:57
Learn how to integrate and format filters in an interactive Excel dashboard, connecting data fields and pivots, configuring interactions, and styling hover effects, selected items, and kpi visuals.
- Dashboard Overview1:28
Dashboard for shipping status….
This dashboard contained thousands of rows of data with one transactional table and 4 other tables called dimensional tables with data.
The company sells goods like furniture, office supplies, and tech-related products. The company shipped those goods to its warehouse before sales and distribution. They use shipping companies like COSCO, Hapag Lioyd, Evergreen Line, YMMT, APM Maersk, and MSC.
The goal here is to find insight using the historical data provided by the company to make features shipping, sales, or delivery faster and more efficient.
Your task (insight required from the dataset)
1) Using sub-category, what are the quantity ordered, delivered and how many of the orders are not delivered over time
2) From the insight gotten from the first step, make it easy to sort ascending and descending order dynamically by clicking on some option buttons. The idea is to be able to know where we have high and fewer orders delivered, and undelivered.
3) Make chart transition that can display quantity ordered, received, unreceived, and number of transactions over time using the contained transaction years in the dataset
4) Using the shipping companies, how many quantities were ordered and how many of those orders were received. Use a chart that will best show a comparison.
5) Which shippers do we use most to ship those goods?
6) Numbers of goods that are not delivered by the use shipping companies
7) Show these three insights on cards for easy readability: Quantity ordered, Quantity shipped, and Quantity unshipped
The active filter needed to interact with the data and the dashboard: Year slicer/filter and shippers slicer/filter.
- Data Cleaning and Transformations30:53
Master data cleaning and transformations in Excel using Pakiri to shape tables, detect and set correct data types, add custom and conditional columns, and build relationships for an interactive dashboard.
- Saving your Project0:57
Learn how to save an Excel dashboard project, name the file, save as a macro-enabled workbook, and confirm the save to preserve work.
- Creating Dashboard Layout21:47
Create an Excel dashboard layout by preparing a dedicated dashboard sheet, styling visuals with gradients and no gridlines, and building category-based pivot insights with percentages.
- Shipping Insight by Orders11:08
Create a shipping insight dashboard by analyzing orders delivered by shippers using pivot tables and charts in Excel to visualize delivery by shipper.
- Unique Sorting Analysis26:27
- Visualizing Sorted Table11:45
- Transition chart18:36
- Comparing Qty Ordered and Received Orders9:57
Compare the quantity ordered to the received orders using a dashboard that visualizes order data and shipping status.
- KPI12:02
Explore how to set KPI dimensions and chart heights, build and customize a Microsoft Excel dashboard with pivot tables, legends, colors, and percentage calculations for data analysis.
- Conditional Formatting8:48
learn to apply conditional formatting in excel dashboards by using formulas to color-code rows, bold text, and adjust font color for values like 1, 2, or 3.
- Integrating filters and formatting9:02
Learn to integrate filters and formatting in an Excel dashboard by configuring slicers, colors, and hover effects to highlight the selected item and connect all dashboard parts.
- Dashboard Overview1:34
Dashboard for shipping status….
This dashboard contained thousands of rows of data with one transactional table and 4 other tables called dimensional tables with data.
The company sells goods like furniture, office supplies, and tech-related products. The company shipped those goods to its warehouse before sales and distribution. They use shipping companies like COSCO, Hapag Lioyd, Evergreen Line, YMMT, APM Maersk, and MSC.
The goal here is to find insight using the historical data provided by the company to make features shipping, sales, or delivery faster and more efficient.
Your task (insight required from the dataset)
1) Using sub-category, what are the quantity ordered, delivered and how many of the orders are not delivered over time
2) From the insight gotten from the first step, make it easy to sort ascending and descending order dynamically by clicking on some option buttons. The idea is to be able to know where we have high and fewer orders delivered, and undelivered.
3) Make chart transition that can display quantity ordered, received, unreceived, and number of transactions over time using the contained transaction years in the dataset
4) Using the shipping companies, how many quantities were ordered and how many of those orders were received. Use a chart that will best show a comparison.
5) Which shippers do we use most to ship those goods?
6) Numbers of goods that are not delivered by the use shipping companies
7) Show these three insights on cards for easy readability: Quantity ordered, Quantity shipped, and Quantity unshipped
The active filter needed to interact with the data and the dashboard: Year slicer/filter and shippers slicer/filter.
- Connecting to an External Data and Cleaning9:44
Connect external data in Excel, clean and transform from web and files, compute revenue, cost of goods sold (COGS), and profits with custom columns, and build a pivot table dashboard.
- Creating the Dashboard Environment15:34
create and customize a dashboard environment by laying out cards, shapes, and charts, applying color themes, aligning elements, and configuring titles and data labels for an engaging data analysis dashboard.
- Populating Visual Cards8:39
Build an interactive Excel dashboard populating sections for orders, revenue, cost of goods sold, and net profit. Use cross sections, named ranges, and formatting to organize data and visualize metrics.
- Saving with the Right Extension1:00
Choose the right extension to save your Excel workbook on servers, ensuring a reliable backup and quick recovery if the file becomes corrupted.
- Monthly and Quarterly analysis Details24:51
Build an interactive Microsoft Excel dashboard to analyze monthly and quarterly revenue and net profit, using cards, time grouping, and dynamic charts with offset and index.
- Creating Shipping Interval Analysis5:24
Edit the data query to add custom and conditional columns, calculate delivery days from shipping dates, and label shipments as within a week, within a month, or within two months.
- Shipping Interval charts26:09
Learn to build and customize shipping interval charts in an interactive Excel dashboard by region, including data selection, copying, offset adjustments, and dynamic filters.
- Sales Channel and Products Analysis10:35
- Filter Connection and Formatting13:26
Learn to build a dynamic Excel dashboard by connecting filters for year, quarter, country, and region, format charts with color coding, and secure it with a password.
- Dashboard Overview0:58
Profit Analysis Dashboard ….
This is another dynamic and highly interactive dashboard that we built using DAX. DAX stands for Data Analysis Expression, which is a common language used on different software like Power BI and others.
The aim here is to help you get used to some simple DAX language should you have large data that you want to analyze in excel. DAX is always an ideal way to handle large data pushed into power pivot in Microsoft Excel.
The dataset used in this class has its base in a folder, in a single folder we have data from 2019 to 2021. We know the data in this folder as the Fact Table, the Fact Table is the table that houses the transaction details, like purchases, quantity, order dates, and some important IDs that will link the Dim Tables.
There is five or sex Dim Table that will use Power pivot to handle and create relationships in other to get a free flow of data when creating our analysis.
Your task (insight required from the dataset)
1) Need to see the cumulative sales from the beginning of the transaction year to the last transactional year which is 2021. It should be dynamic should there is new data in for the subsequent years to come.
2) Who 3 profitable customers? This will make us know who among our customers support our company growth.
3) Who are the 3 least customers? This will help our company to find why and what we need to do to motivate them to buy more from us in the future.
4) Show the yearly profit and growth
5) What is the trend of our profits on a monthly basis?
6) What is the trend of our profits quarterly?
7) Other insights needed are the numbers of transactions, total customers, numbers of products the company has currently, and numbers of countries that we sell to.
I needed the active filter to interact with the data and the dashboard: Country, products, and months
- How to Handle Multiple Errors in Power Query7:12
Learn to diagnose and fix multiple Power Query errors by refreshing connections, editing sources, and adjusting folder and table transforms to refresh data.
- Connecting and Transforming Data20:50
- Writing DAX in Power Pivot12:30
Explore writing DAX in Power Pivot to build revenue and cost of goods sold using calculated columns and measures, comparing when to use each for dynamic dashboards.
- Creating Dashboard Layout7:00
Design and customize an Excel dashboard layout by adjusting gridlines, highlighting cells, applying custom colors, inserting text and icons, and organizing visuals for key metrics like countries, projects, and customers.
- Creating New Measures for KPI8:54
- Getting Values for Cards Using Name Ranges2:23
Learn to pull card values in an Excel dashboard by creating and naming ranges, linking totals to country and transaction data, and formatting for clear visual displays.
- Creating KPI Cards6:10
Create KPI cards for an Excel dashboard, showing cost of goods sold, revenue, and net profit, with monthly trend lines, precise formatting, alignment, shadows, and color choices.
- Creating Monthly Trend9:40
- Creating Custom Number formatting9:53
Learn to format numbers in custom formats, using rounding and conditional rules to display values in thousands, millions, or billions, and to dynamically concatenate figures for dashboards.
- Creating Profit Analysis Charts10:52
- Getting Values our Charts6:01
Get values for charts in an Excel dashboard by selecting data and handling no-data cases. Format currencies and decimals, and adjust styling to present a clear, auditable data view.
- Adding Percentages4:02
Learn how to add percentages to your data analysis dashboard to display profits, verify values, and ensure percentages appear consistently across the dataset.
- Creating Monthly Profit Chart6:14
- Quarterly Analysis Chart7:28
- Transition Chart Between Top and Bottom Buyers13:08
Explore building an interactive Excel dashboard that highlights top and bottom buyers, creates dynamic captions, and uses top-n filters and cumulative analysis to drive customer insights.
- Formatting Values for Top and Bottom2:18
Format and align values for top and bottom in the Excel dashboard, selecting numbers, applying bold, and inserting illustrations to create a tidy, color-coordinated visualization for cumulative totals and analysis.
- Creating Cumulative Analysis6:27
Create a cumulative analysis of profit within an interactive excel dashboard. Learn to pull yearly data, apply currency formatting, adjust visuals, and add filters for focused insights.
- Adding Filters7:15
Learn how to add and configure filters in an interactive dashboard by selecting country and product filters, applying them to a multi-chart view, and optimizing filter behavior.
- Formatting10:03
Format an interactive Excel dashboard by configuring filters, visibility, and macros for show and hide actions. Link filters and modules to manage country and product views for data presentation.
- Dashboard Overview1:43
Cancer Mortality Dashboard ….
Let’s work on non-financial data and see how we can get more creative creating a dashboard to decide on how to curb and deal with cancers using historical data from 1914 to 2019 with 34 columns and over 3,000 rows of data.
The causalities had been spat into distinct buckets in other to make it easy to spot patterns and decide. I had categorized it into age groups from age-5 all the way to age 90+.
Your task (insight required from the dataset)
1) Show causalities by age groups categories. This will make it easy to read which age group has more causalities.
2) Create a comparison chart that can dynamically compare age groups based on selections.
3) What are the cancers that affect the younger people under the age of 5, 5-9, 10-14, and 15-19
4) Total mortality by gender.
- Creating Age Group Cards8:35
- Creating Homepage Dashboard18:11
Create a homepage dashboard in Excel by building a pivot table, naming analyses, and arranging charts, images, and formatting to visualize mortality by age groups.
- Adding Images to the Cards19:04
- Creating Main Dashboard13:05
Design the main dashboard in Excel by inserting shapes, configuring card dimensions, and customizing colors for a cohesive, visually appealing analytics board.
- Filling up Cards with Values11:40
Populate dashboard cards with poverty-rate data from 1994–2019, focusing on 2013–2019, by creating and formatting a pivot table, calculating percentages, and styling cards for clear comparison.
- Creating Custom Comparison Line Chart20:59
Learn to build a custom comparison line chart in Excel by configuring a dynamic dropdown, using transpose and dynamic arrays, and linking selections via index and offset for age-group comparisons.
- Comparison Line Chart Summary1:54
Learn to build a comparison line chart in Excel by selecting ranges, using color codes to match data, and adjusting height values for accurate totals.
- Formatting Comparison Chart7:27
Format a comparison chart in Excel by linking age-group selections to the corresponding IS group, customizing markers, colors, and data labels, and finalizing with a polished dashboard view.
- Matching the Filter with Dashboard Colors5:09
- Creating A Dynamic Caption12:00
- What are those Cancer that Affect Under Age Analysis15:45
- Creating one More Caption5:33
Create dynamic captions in an Excel dashboard by concatenating data to reflect age groups such as under five, 5–9, 10–14, and 15–19, updating as selections update.
- Gender Analysis7:41
Explore gender analysis by visualizing gender data in an interactive Excel dashboard, building dynamic charts, applying colors, and integrating dashboard elements for clear data storytelling.
- HR Dashboard Overview2:31
Human Resource (HR) Dashboard ….
HR is the field of business concerned with recruiting and managing employees. In other to track and the progress of the recruited employees over time the management will need a good hand to handle the recruitment historical data and the track record of the individual employees in the organization.
The dataset is in CSV format and is very rough which might get you to fret and freeze at first glance. We used an inbuilt tool called Power Query in Microsoft Excel to have the data cleaned and transformed into what we want and what we can analyze in other to help the organization.
Your task (insight required from the dataset)
1) What some criteria provided in the worksheet are how many employees need to be promoted under the Bachelor’s degree holders?
2) What are some criteria provided in the worksheet how many employees need to be promoted under the Doctorates holders?
3) What some criteria provided in the worksheet how many employees need to have salary increment in Tech support department
4) What are some criteria provided in the worksheet how many employees need to be promoted under the Sales and Marketing department?
5) Show total employees by educational background, what are the numbers of male and female employees based on the educational breakdown.
6) Total numbers of employees by job types and salary
7) Employees attrition (active and inactive employees)
8) Total employees by business units and salary pay to those business units. There should be some kind of transition between the numbers of employees and salary in one chart.
9) What are the five departments with the highest employees in the organization?
10) Numbers of employees by gender categories
11) Show numbers of employees by job classifications and on a table-view make it easy-to-read job classifications and with the educational breakdown
The active filter needed to interact the data and the dashboard: Department and gender
- Transform Data with Power Query6:05
Transform raw workbook data by using Power Query to split a single column into multiple columns, set headers, adjust data types, and load the transformed data for dashboard-ready analysis.
- Saving your Project0:54
Save your project to preserve changes as you set up an interactive Excel dashboard for data analysis, then proceed to customize dashboards like human resources and orders.
- Creating Name Multiple Ranges from Headers1:40
- Promotional Analysis11:51
Learn to build a promotional analysis dashboard in Excel, using education filters, conditional logic (if and), job involvement, satisfaction, and performance to identify which employees deserve promotions.
- Creating the Dashboard Layout10:59
Create an Excel dashboard layout by naming the worksheet 'dashboard', turning off grid lines, applying a background, adding a rounded-rectangle shape, and styling a 'human resources dashboard' header for charts.
- Departmental Analysis12:46
Analyze department performance by transforming data, building a pivot table to extract top five departments by employee count, and visualizing results on a dynamic Excel dashboard.
- Gender Details Analysis11:20
Build and refine an interactive Excel dashboard to analyze gender details, using employee types, color coding, and percent of grand total to compare active and inactive workers.
- How to Update this Dashboard1:51
Learn how to update this dashboard by connecting external data and transforming it simply. Ensure new data refreshes the dashboard for updated analytics.
- Marital Status Analysis11:26
- Job Types Analysis19:19
Design an interactive Excel dashboard for job types by building and formatting charts, adjusting card heights, applying colors, and linking salary data with dynamic captions for clear analysis.
- Transition Chart between Total Employees and Employees Salary14:09
Build a dynamic Excel dashboard transition chart linking total employees and salaries by business unit, selecting fields, formatting the chart, and displaying salary as dollars.
- Cards for Educational Background12:23
Create and align educational background cards in the interactive Excel dashboard, style them with color and fonts, and visualize job classifications across educational backgrounds through percentage analysis.
- Display and Hide cards with Values22:31
Learn to build an interactive excel dashboard by displaying and hiding value cards, customizing colors and shadows, configuring table views, and using macros to toggle panel visibility.
- Educational and Gender Breakdown17:24
Create an interactive excel dashboard by organizing education and gender data with pivot tables, percentages, and color-coded cards. Analyze and visualize bachelor, master, and education levels.
- Creating Gender Card8:19
- Integrating filters12:43
Learn to integrate filters and slicers in an Excel dashboard. Apply gender, education, and job-type filters, and automate with macros for a clean, interactive report for data analysts.
- Employees Promotional Analysis11:42
Build and refine an Excel dashboard to analyze employee promotions, selecting education, department, and job metrics, and visualize counts and promotion insights for management.
- Visualizing Promotional Analysis10:56
- Dashboard Overview1:16
International migration Dashboard ….
This is another non-financial dashboard but a dashboard to analyze the migration of people from their countries to foreign countries for several reasons according to the historical data captured.
The dataset captured from immigrants show different visa like student visa, work visa, visiting visa, and residential visa. We only have data for January and February 2020 with over 40 thousand rows and over 13 columns.
Your task (insight required from the dataset)
1) Numbers of male and female immigrants
2) Migration detail on a monthly basis (January and February) makes it dynamic to capture new data if any.
3) How many people migrate with pets and without pets?
4) How many people migrate from these focus countries (Nicaragua, Niger, Nigeria, and Niue)
5) Hierarchically show visa according to how it is obtained by immigrants
6) Age-group by travel history
7) Numbers of final and provisional (Visa status)
The active filter needed to interact with the data and the dashboard: Visa types and status
- Creating Dashboard Layout10:10
- Using Normal Savings0:48
- Gender Analysis16:38
- Monthly Migration Analysis16:31
Develop and customize an excel dashboard for monthly migration analysis, using pivot tables and slicers to compare two months by age group and gender, and visualize outcomes with color-coded charts.
- Visa type by Immigration15:04
Build an interactive Excel dashboard to analyze visa types by immigration with pivot tables and charts. Filter by country to reveal top visa trends and country insights.
- Final and Provisional Analysis5:33
- Making Fancy Filters8:57
- Complete Dashboard Overview3:26
A & B Investment Dashboard ….
This is one of my favorite dashboards ever. This dashboard is about the movement of goods from the seller to the buyers. The company sells seven distinct products and for every shipment, there is always track of the status of the shipment like Shipped, Received, Canceled, In progress, Resolved, Dispute, and on hold.
The status is an area of focus as it is important for the company to know how all the products shipped went.
The dashboard has a search feature to help get a quick look at buyers' or company’s transactions. With a search feature you can get to know about a particular company that buys from you, all you need do is type in the buyer’s company’s name into the search bar and hit enter, there you go, you have the results of all the search transaction.
Your task (insight required from the dataset)
1) Analyze total orders from 2020 to the last years of the transaction with a trend line that show monthly purchases
2) Make the same analysis and show it in percentages
3) What are the min and max orders for every sales year and what month do we have the min and max in?
4) Transactional status is a key to the company in other to track the progress of its purchases, make sure you display orders values to every status and show it up in cards for easy readability and decision making
5) Create a search bar that can quickly search for buyers’ transactions
6) How many do we have on each product and what is the quantity sold. Use images to represent each product.
The active filter needed to interact with the data and the dashboard: Country.
On filter selection, let it display the countries selected, and if there is none, let it display this message: Showing all countries orders, use the filter to get a new view or analysis.
- Dashboard Layout25:59
Create a polished Excel dashboard layout by building cards, charts, and color schemes, while customizing data visuals and applying conditional formatting.
- Adding Values To Top Cards8:28
Learn to build and style top KPI cards on an Excel dashboard by adding values, selecting icons, coloring, and aligning six cards for clear data visualization.
- KPI and Trend lines33:06
Learn to build an interactive excel dashboard for data analysis by creating key performance indicators (kpi) and trend lines, organizing analysis sheets, and presenting percentages of grand total and charts.
- Conditional messages and captions26:34
Learn to build an interactive excel dashboard that uses conditional messages and captions to handle no data, display dynamic text, and summarize min and max sales across months.
- Customizing Search Results20:51
Learn to customize search results in an Excel dashboard by building and styling pivot tables, creating a search bar, and using iferror with index–match to fetch data.
- Making search dynamic with captions5:38
Design a dynamic search in an Excel dashboard by using header checks, lookups, and if statements to display relevant messages when results are empty, guiding dashboard population.
- Visualizing Search Results8:02
Visualize search results in an excel dashboard by formatting, aligning, color-coding, resizing, and organizing data, then apply a simple formula to analyze across countries.
- Visualizing Search Results Messages2:46
Demonstrate visualizing search results messages in a data dashboard by using the search bar to query buyers, handle missing results, and apply formatting, alignment, and grouping to reveal buyer data.
- Show if any Filter is Selected6:36
Learn to configure an Excel dashboard to show a message when a country filter is selected, using ifs and not blank logic to display relevant countries.
- Matching the Slicers with the Background12:57
Match the slicers with the background and tune visuals in the dashboard. Apply color codes, borders, font styles, and images, and set up macro-driven buttons to manage selections.
- Products Line Dashboard4:27
- Adding 3D images and values11:28
Add 3D images and values to an Excel dashboard, align and format visuals, and build product analysis visuals with percentage values.
- Final Touches and Formatting21:42
Master final touches and formatting for an excel dashboard—align visuals, adjust color and shadows, and use named ranges and concatenation to display transactions on the product analysis page.
Requirements
- No pervious programming or Excel knowledge is needed. We got you cover from the scratch to the advance level of the course.
- Just Microsoft Excel
- Excel 2016, Excel 2019 or Excel 365
- Analytics thinking mind-set
Description
This course will take you beyond your expectations, I have put together all you need to master data analytics and dashboard creation. We use real-life datasets that are companies and industry standards.
You will learn from my years of experience as an analyst, data analytics freelancer, and as well learn how to monetize your data analytics skills and make money from them.
We have more projects in this course than you can ever find from other instructors (courses) on this platform. We illustrated all projects using a real-life dataset that applied to day-to-day business activities.
All the dashboards taught in these classes are not only dynamic and interactive but all so have outstanding designs.
By participating in this Microsoft Excel Dashboard course you'll gain the widely sought-after skills necessary to analyze enormous datasets.
You will learn Power Query for data cleaning and transformations, Power Pivot to handle huge datasets like millions of rows of data and have it analyzed in Excel.
You learn how to manipulate data with DAX language in Power Pivot and as well create easy relationships between multiple tables instead of using basic lookups that might slow your analytics down.
Join us today to create your own stories and help businesses make accurate decisions.
Who this course is for:
- This course is for those that are trying to grab a career in Data
- If you want to become a successful Data Analyst Freelancer or an In-House Data Analyst this course is for you
- If you want to a create colorful and never seen Excel Dashboard designs before, this course is for you
- Need to stand-out in your office or organization like I am? This course is for you