Microsoft Advanced Excel Dashboard : Zero To Hero (Complete)

Build three Excel dashboards: a pivot-table based dashboard with parameters, charts, and control buttons; a formulas-based dashboard with inputs via dropdowns; and a macros-driven dashboard.

Understand the data for your first Excel dashboard by creating a pivot table, exploring fields like unique ID, country code, salary in US dollars, and job type.

Learn how to use the text function in Excel to extract dates, format them for dashboards, and derive day and weekday for pivot table analysis.

Convert static Excel data into a dynamic table with Ctrl+T, enabling automatic expansion as new rows or columns are added and supporting formulas that reference table fields by name.

Turn data into a table, name it in the name box with underscores for spaces, confirm with enter, then reference this dynamic data to fetch the salary in us dollars.

Create the first pivot on a new worksheet from dynamic data, place job title in rows, experience in columns, and salary in values to see cumulative totals; refresh after updates.

Learn to enable classic view in pivot tables, fill blanks with zeros, and summarize values by average to analyze data like salaries, while adjusting formats and layout.

Create a chart from pivot table data, rename the heading, and generate a stacked column chart using Alt+F1 or pivot charts; build the base pivot reports for the dashboard.

Learn to create a second pivot with a trend line, convert time-based data into yearly and quarterly reports, and group dates by quarters while refreshing connected pivots.

Create a slab report from a pivot and three connected pivots by converting non numeric values to zero, grouping salaries into slabs, and turning the results into a pie chart.

Bring all charts into a single dashboard sheet by copying each chart, aligning and snapping to cells, applying borders, and grouping them for a clean, interactive overview; controls follow.

Learn to customize slicer controls by connecting multiple pivot tables so a single selection updates all charts. Explore clearing data, hiding items with no data, and adjusting slicer settings.

Transform raw, multi-year data into a dynamic Excel dashboard using formulas, with currency format selection, trend line charts, and automatic updates of averages, maximums, and country highlighting.

Import data from the internet to fetch currency exchange rates for the dashboard, convert US dollars into the selected currency, and refresh automatically when rates change.

Create a currency converter table from line item data by naming a range, using lookup with a country code to convert US dollars to multiple currencies, and highlight mappings.

Convert currencies in an Excel dashboard using index and match, mapping country code to country currency and multiplying by the user-selected rate to update across sheets.

Learn to create experience slabs in an Excel dashboard by using vlookup with the true parameter, mapping data to zero to forty years of experience, and grouping data for clarity.

Create a quarter column and map quarter values with vlookup and appropriate match, using a named range, to support financial or calendar quarter selection on a dashboard.

Learn to compute country-wise salaries using the averageif function, freeze ranges, and handle errors (zero if error) on the 2012 data sheet to build a dashboard.

Learn to substitute errors with zero using IFERROR, understand the value and value_if_error parameters, and apply the fix across cells to prevent division by zero errors.

Explore how the indirect function redirects to cell addresses stored in named ranges, enabling references with name manager and data validation, and supporting vlookup, match, and index-match lookups across months.

Leverage indirect to retrieve averageif results from the sheet named by the user input year, using a dummy year cell, dynamic references, and zero on missing.

Develop a dynamic Excel dashboard formula using countif and indirect to count employees by country for a selected year, with full-column references and year-based totals.

Calculate India’s share of employment by dividing India’s employees by the total employees, format as a two-decimal percentage, and keep the reference constant using a named range.

Learn to use name manager to define a named calculation, such as addition, and reuse it in dashboards. See how dragging updates references and keeps formulas on the current sheet.

Learn to implement name ranges with formulas in an Excel dashboard, using indirect and name manager to create country from data and converted salary from data, updating with year.

Explore the offset function for dynamic referencing in dashboards. Learn how to start from a cell and move down and right to define a data area with height and width.

Learn to use the offset function in Excel to create dynamic ranges that automatically update totals as new data is added, using sum and count to extend the range.

Use the offset function to build a dynamic Excel dashboard, with indirect kept outside, and counta to count country occurrences like India.

Use MINIF in dashboards by building an array formula with IF, enter with Ctrl+Shift+Enter, to compute the minimum salary for the user-selected country from the 2016 data.

Use max if to power a dashboard that returns the maximum salary for a selected country and year from the data.

Learn to build a designation experience dashboard in excel using average ifs, with criteria ranges for designation and experience, handle missing data with iferror, and format results with two decimals.

learn to build a max if with multiple conditions, using designation and salary slabs, with named ranges, offset, and an array formula to return the converted salary.

Learn to use minif with multiple conditions by adapting the max if approach, enter as an array formula with control shift enter, and apply to department salaries for dashboard groundwork.

Create a dynamic dropdown in an Excel dashboard using offset, count, and a named range, enabling year selection that updates the map and currency data with index and match.

Learn to plot country data on a dashboard by obtaining country-wise longitude and latitude from a file or online, and using Excel functions like index and match to map data.

Learn to use the REPT function to compute a dataset percentage and build a dynamic mini graph in an Excel dashboard, formatting as percentage with two decimals.

Activate the developer tab and insert a scroll bar to dynamically control dashboard data by linking it to a cell. Adjust min, max, interval, and page change to refine results.

Plot a trend line chart from the percentage data table, using a line or marker line, and add chart title and data labels. Ensure the chart updates with parameter changes in the dashboard.

Master dynamic Excel dashboards with advanced index and match across multiple references, pulling average, max, and min data based on user selections from dropdowns.

Learn to insert sparkline charts in Excel to create dynamic mini dashboards, selecting data, choosing sparkline type, and customizing with high and low points.

Insert a world map into the scatter chart by copying the image to the clipboard and applying from clipboard fill, then crop or edit as needed before plotting data.

Plot data on a map chart by mapping longitude to the x axis and latitude to the y axis, with marker size based on percentage.

Learn to control map preview in Excel dashboards from a dropdown by naming country filter and linking it to data with an IF function, using a slider to update charts.

Remove grid lines, hide unused columns and rows, and adjust the view to create a clean Excel dashboard driven by a map sheet, grouping, and a 2017 dropdown.

Learn to create a macros-driven dashboard showing global and admin trends with a blue current line, a grey trend line, and a spider web chart of average, max, and min.

Learn to build a dynamic excel dashboard using the offset function to create a master data range, feed two pivot tables, and apply job type filters for user selection.

Create a trend line from two pivot tables in an Excel dashboard, using a formula-driven header and user selections to filter and compare average salaries.

Modify chart markers and colors in excel dashboards to visually emphasize average data, using format data series, marker options, and border and fill color changes for a darker emphasis.

Learn to record macros in Excel to update a pivot chart from a dropdown filter, save the file as macro-enabled, and automate chart updates with VBA in the dashboard.

Use a VBA macro to update the pivot based on the selected job type, syncing dashboard and trend chart data across sheets, with screen updating toggled for smooth execution.

Learn to automate Excel dashboards by triggering macros on any worksheet change, using change events and sheet-specific code to update data and charts.

Learn to create dynamic named ranges from data, and use array and conditional formulas to compute average, minimum, and maximum salaries by country in a Microsoft Excel dashboard.

Use maxif and minif with single and multiple parameters to determine finance salaries by country and job type, showcasing maxifs, minifs, and ifs alongside average calculations.

Create a chart from a table to compare India and the world data. Format the data series, adjust overlap, and prepare for changing the chart type in the next video.

Adjust chart settings to display multiple parameters by converting bars to markers and configuring lines, markers, borders, and colors; align the graph on the dashboard for a clear, multi-series visualization.

Prepare and visualize the average salary by experience using a spider web chart, building pivot tables, VLOOKUP, and a dynamic chart title to drive an HR dashboard.

Leverage a macro-driven Excel dashboard to dynamically hide and unhide sheets based on country and field filters, delivering a resettable, user-driven, data-driven report.

Insert and format trendlines in an Excel dashboard to display exponential and linear trends, customize color and style, and compare data across countries and categories.

Learn to convert raw data into dynamic dashboards with pivots and slicers, then automate updates across sheets using mappings, lookups, and macros for trend visualization.