Excel for HR & Operations Professionals

This video explains the VLOOKUP function with the help of an example.

This video provides another example of VLOOKUP to improve learners’ understanding of the Excel function.

This video dives into the fourth parameter in VLOOKUP.

This video explains the HLOOKUP function.

This video explains how to use the LOOKUP function and how it differs from the VLOOKUP and HLOOKUP functions.

This video explains the INDEX function with the help of an example.

This video provides a practical understanding of the MATCH function in Excel.

This video explains two-way lookup with the INDEX and MATCH functions.

This video helps in understanding how to utilize the ISERROR and IFERROR functions for error handling purposes.

This video explains the CHOOSE function in Excel.

This video helps learners understand how to utilize the INDIRECT function with the help of an example.

This video provides another example of using the INDIRECT function to help individuals better understand it.

This video explains the OFFSET function in Excel with the help of an example.

This video explores the XLOOKUP function from a theoretical standpoint and explains how it differs from VLOOKUP.

This video shows how the XLOOKUP function works with the help of a practical example.

This video continues to explain the XLOOKUP function and explores the match mode and search mode.

This video explains how to use the XLOOKUP to do a two-way lookup.

This video dives into the meaning of pivot tables, their uses, components, and advantages.

This video explains how individuals can create pivot tables in MS Excel.

This video provides a clear understanding of how to work with pivot tables.

This explores the grouping capabilities of pivot tables.

This video explains how data is filtered in Excel with the help of pivot tables.

This video dives into calculated fields and calculated columns.

This video shows how to filter data using timelines and slicers.

This video explains the process of creating pivot tables from multiple ranges in different worksheets.

This video provides practical understanding of how to use the GETPIVOTDATA function

This video shows how to get the other values using the PIVOTDATA function.

This video explores the visual representation of data utilizing sparklines.

This video provides a practical understanding of pivot charts.

This video talks about the drill down feature of pivot tables.

This video provides an introduction to the course and gives an idea of the different topics that will be covered.

This video explains the different benefits of using macros. For instance, the automation of repetitive tasks and the minimization of errors.

This video shows how one can enable the developer tab in Excel.

This video provides a clear understanding of the differences between macros and VBA.

This video dives into the different methods that one can use to start recording macros.

This video shows how individuals have to save a macro-enabled Excel workbook.

This video explains how to run macros with an example to develop a practical understanding.

This video helps us understand the Visual Basic Editor.

This video shows how individuals can use relative referencing for macros.

This video explores dynamic macros utilizing the special cells option.

This video dives into debugging errors when working with macros.

This video dives into the meaning and importance of Visual Basic for Applications (VBA).

This video covers the core building blocks of VBA programming — Objects, Subs, and Functions.

This video is the second part of the explanation regarding Objects, Subs, and Functions.

This video explores the concept of variables in VBA, explaining why they are important and how one can use them with examples.

This video dives into the concept of loops and their importance in VBA programming. It also provides a practical understanding of how one can utilize them with the help of an example.

This video continues explaining loops in VBA programming.

This video provides an explanation regarding conditional programming in VBA, which enables one to make decisions and automate tasks on the basis of certain conditions.

This video explains arrays, which can significantly improve Excel VBA coding skills.

This video helps learners understand events in VBA.

This video provides practical knowledge regarding debugging.

This video provides a practical understanding of how to handle errors that may arise during the execution of macros.

This video dives into use cases and provides examples to improve the understanding of macros.

This video continues the explanation regarding use cases and shows examples to understand the real-world applicability of macros.

Power Query in Excel helps import, clean, transform, and reshape data from various sources. Learn the 4 phases: connect, extract, transform, and load, plus techniques like append and merge for combining data.

Exploring Power Query Editor for data transformation and cleaning, including how to load, view, and modify data before loading it into Excel

Learn how to filter data, change data types, and remove columns using Power Query to clean and transform your data before loading it into Excel.

Learn how to apply transformations like uppercase conversion, trimming spaces, and replacing values. Also, explore how to edit steps and rename them for better clarity in Power Query.

Learn how to add new columns, format data, and use conditional functions in Power Query. Create new columns like "Age" from birth dates, and categorize employees into age groups. This module covers loading data into Excel, performing transformations, and generating reports using PivotTables.

In this module, we learn how to merge two tables in Power Query based on a common column. This allows us to add columns from one table to another using different join types.

In this module, you'll learn how to combine tables using various types of joins in Power Query, including inner, outer, and anti joins, with practical examples to clarify each type.

In this module, we dive into Power Query joins with practical demonstrations. You'll see how inner, outer, and anti joins work using employee and department data, showing different ways to merge tables.

In this module, we learned about Power Query append operation, which combines two queries into one by adding rows from the second query to the first based on matching column names.

In this module, we learned how to append data from employee Feb to employee Jan while maintaining department names by adjusting merge and append steps in Power Query.

In this module, we learned how to create custom calculations by adding calculated columns, such as total earnings and tax, using existing data in Power Query without altering the original dataset.

In this module, we learned about grouping records in Power Query, like summing salaries or counting employees by department or job, and how to perform advanced group by calculations for summary reports.

In this module, we explored pivoting and unpivoting data in Power Query. Pivoting turns row values into columns, while unpivoting reverses this process, converting columns back into rows.

In this module, we discussed how to clean data in Power Query. Data cleaning involves transforming raw data into a structured format for analysis. We covered removing unwanted rows, filtering out subtotals, filling null values, and unpivoting columns to prepare the data for analysis.

This module covers loading data from various sources into Excel using Power Query: SQL Server, CSV, web, XML, JSON, and Excel tables. Each source has unique formats for importing.

To load data from SQL Server into Excel, go to Power Query, select "From Database," choose SQL Server, enter server and database details, and load the desired table into Excel.

To load data from a CSV file into Excel, go to Power Query, select "From File," choose "From CSV," pick the file, and transform or directly load the data into Excel.

To load data from the web, copy the URL, go to Power Query, select "From Web," paste the URL, choose the relevant table, clean and transform the data, then load it into Excel.

To load data from XML, go to "New Query" > "From File" > "From XML," select the file, and Power Query will automatically convert the hierarchical data into a tabular format for Excel.

This module shows how to load JSON data into Excel using Power Query. JSON data is in key-value pairs, and Power Query automatically converts it into a table for easy manipulation.

This module teaches how to load and manipulate data from an Excel table using Power Query. You convert ranges into tables, load them into Power Query, and apply changes like adding calculated columns.

This video explains how to combine multiple files from multiple or the same sources in Power Query.

This video dives into date and time manipulation in Power Query.

This video continues explaining date and time manipulation in Power Query.

This video discusses some Power Query commands, for example, remove duplicate data, group data, and fix country specific formatting.

Learn what dashboards are, their benefits, best practices, and how to create them using Excel. Master data visualization principles applicable to Power BI, Tableau, and more.

Learn key data visualization best practices, including choosing effective visuals, formatting charts, storytelling, and avoiding common mistakes. Master essential charts like bar, line, scatter, and maps for clear communication.

Discover why data visualization matters! Learn how visuals simplify complex data, reveal patterns, and enhance decision-making. See firsthand how charts improve understanding compared to raw data.

Learn how to choose the right chart by understanding data types, communication goals, and audience needs. Explore essential visuals like KPI cards, line charts, bar charts, pie charts, scatter plots, and maps.

Learn how to improve chart clarity by eliminating noise, formatting axis labels, adding context, and using colors strategically for better storytelling in data visualization.

Enhance data storytelling with clear titles, strategic labels, and effective formatting. Avoid common visualization mistakes like incorrect chart types, cluttered visuals, and inconsistent colors for better insights.

Kickstart your first project as a data visualization specialist! Learn to choose the right chart, eliminate noise, and tell a compelling story using a stacked area chart in Excel.

Learn how to create a stacked area chart in Excel to visualize music industry trends. Compare line charts vs. stacked charts and adjust data order for clearer storytelling.

Learn how to clean up your Excel chart by removing noise, formatting axes, adjusting labels, and replacing legends with direct annotations for better clarity.

Enhance your Excel chart to tell a compelling story using colors, labels, and annotations. Highlight key trends, market shifts, and the impact of digital formats effectively.

Learn key dashboard design principles, including exploratory vs. explanatory dashboards, selecting the right visuals, adding context, and designing effective layouts for better decision-making.

Learn how to define a clear purpose for your dashboard by identifying the audience, their needs, and key metrics. Discover best practices for selecting visuals and adding filters effectively.

Learn the importance of adding context to dashboards. Discover how comparisons, trends, and benchmarks make KPIs meaningful, ensuring clear insights for better decision-making.

Learn how to design effective dashboard layouts using reading patterns, visual hierarchy, and Gestalt principles to guide users through insights clearly and intuitively.

Kick off your first full Excel dashboard project! Learn how to define objectives, select key metrics, choose visuals, and prepare data for an interactive job market analysis dashboard.

Start building the Excel dashboard by preparing data for visualization. Learn how to create industry filters, remove duplicates, sort data, and set up interactive selections for analysis.

Learn to calculate the average annual wage by industry in Excel using the AVERAGEIFS formula. Format the data properly and verify calculations to ensure accuracy.

Create a bar chart in Excel to visualize average annual wages by industry. Apply formatting best practices, adjust spacing, and customize axis labels for better readability.

Sort bar chart data in descending order for better readability. Fix sorting issues caused by sheet references and adjust axis settings to align the chart correctly.

Calculate total employees by industry using SUMIFS. Format the data and create a doughnut chart to visualize the job market composition. Apply best practices for better readability.

Improve the doughnut chart by grouping industries into "selected" and "others." Use VLOOKUP for dynamic selection, format labels as percentages, and enhance readability with best practices.

Compare wage and employee trends over multiple years. Use AVERAGEIFS and SUMIFS, fix X-axis labels, apply a secondary axis, and create a combo chart for better trend visualization.

Enhance chart readability by splitting the combo chart into two while maintaining a unified look. Format labels, axes, and scales to improve data clarity and visual comparison.

Create a state-wise comparison map for employee distribution. Prepare data, use SUMIFS for calculations, and insert a map chart to visualize workforce concentration across industries.

Normalize employee distribution using per-thousand capita. Use Excel's geography data type for state populations, compute employee ratios, and update the map for a more accurate industry-based workforce distribution.

Enhance the map visualization by adding an average wage metric and a toggle feature. Learn how to dynamically switch views and adjust map colors for clearer insights.

Learn how to design the final dashboard layout by strategically placing visuals, filters, and selectors. Follow a structured approach to ensure readability, alignment, and an effective user experience.

Learn how to use color strategically in your Excel dashboard to improve clarity. Highlight key metrics with consistent color coding, adjust chart formatting, and enhance data visualization.

Enhance your Excel dashboard by dynamically highlighting selected industries in bar charts. Learn to create a new data series, apply conditional formatting, and improve data readability.

Improve user experience in your Excel dashboard with form controls. Learn to sync selections, use spin buttons, apply the INDEX function, and create interactive option buttons.

Finalize your Excel dashboard with formatting, logos, and protection. Learn to secure sheets, enhance visuals, and validate insights for a professional, user-friendly experience.

Start your second Excel dashboard project! Design a sales performance dashboard for Mojo Toys using key metrics, interactivity, and advanced Excel techniques for insightful business decisions.

Define the dashboard’s purpose through a quiz! Identify key metrics, visuals, and filters for effective sales tracking. Prepare to dive into data in the next module.

Convert data into an Excel table, create filters for region and date, and set up dynamic formulas for automatic updates. Prepare your dataset for a responsive dashboard.

Assign names to key cells for easier formula management. Use the Name Manager to enhance readability and simplify referencing in your Excel dashboard calculations.

Calculate key KPIs like total revenue, previous year revenue, and previous month revenue. Use structured formulas and named references for better readability in your Excel dashboard.

Create dynamic KPI cards for total revenue, year-over-year, and month-over-month changes. Use text boxes and linked images to display values with conditional formatting in your dashboard.

Build a dynamic revenue trend analysis for current and previous years using SUMIFS with mixed references. Format and validate data for accurate trend visualization in your dashboard.

Create a dynamic line chart to visualize revenue trends. Fix axis labels, handle missing data correctly, and format the chart for clear insights and professional presentation.

Prepare store performance data by calculating total revenue, previous month revenue, and month-over-month percentage change. Ensure data accuracy and formatting before visualizing it in the next module.

Visualize store revenue and month-over-month percentage change using bar charts. Format the charts for clarity, align labels, and use color coding for positive and negative values.

Sort revenue data dynamically using ranking and index-match functions. Ensure charts update automatically with data changes, maintaining proper order and formatting.

Identify the top and bottom-performing products driving revenue changes. Learn how to rank products, calculate performance metrics, and display insights effectively in your Excel dashboard.

Set up your dashboard layout for clarity and efficiency. Learn how to position key metrics, format elements, and organize visuals to guide users through a logical data story.

Learn how to bring visuals from the Dataprep sheet to your dashboard. Align elements, format data, and structure your Excel dashboard for a professional look.

Enhance your dashboard with strategic color usage and dynamic highlights. Learn how to emphasize key trends, create interactive filters, and format charts for better insights.

Refine your dashboard with storytelling techniques. Learn how to pose key questions, add contextual insights, and format data for clarity, making your dashboard more intuitive and impactful.

Learn how to securely share your Excel dashboard online using OneDrive. Set permissions, protect data, and enable real-time collaboration for seamless access across different regions.

Fixing linked picture issues in Excel Online by using text boxes and dynamic formulas. Learn how to add conditional formatting, symbols, and finalize your KPI dashboard for seamless online access.

Kickstarting our final project! We'll transform raw hotel booking data into a compelling, explanatory dashboard for leadership. Learn visualization techniques, layout design, and exporting insights.

Understanding the story behind our data! We'll analyze key trends in cancellations, revenue loss, and booking patterns to craft a compelling dashboard for hotel management.

Defining our dashboard's purpose and visuals! Through a quick quiz, we determine the right audience, goals, and best chart types for effective data storytelling.

Building a combo chart to visualize seasonality! We align dual axes, refine grid lines, and use colors, markers, and labels to highlight key trends in cancellation rates and pricing.

Highlighting revenue loss due to cancellations! We use a column chart, format data labels, adjust colors, and add annotations to emphasize the loss in July and August.

Learn a smart trick for conditional formatting in data labels! Format values dynamically to show in millions or thousands based on their size for a cleaner, more professional look.

Visualize cancellation breakdown by lead time using a stacked bar chart! Learn how to switch from a pie chart for better clarity and storytelling while saving space.

Learn how to compare lead time with cancellation rate and average daily rate using bar charts. Discover a trick to display selective pivot chart data for better visualization!

Learn how to design a structured Excel dashboard by setting up a layout for problem and opportunity insights. Prepare for the next step—adding visualizations!

Now that our layout is ready, we’ll place the visualizations onto the dashboard. Watch as we align and adjust them for a clean, professional look!

We finalize our dashboard by adding titles, subtitles, and a compelling narrative. Learn how to effectively present insights and drive strategic recommendations for leadership decisions!

Learn how to save your Excel dashboard as an image for easy sharing. Follow these steps to ensure a high-quality, non-transparent snapshot of your final project.