Master Data Analysis with Microsoft Excel & Power BI

Learn how to check your Microsoft Excel version via file > account, note 365 or 2021 options, switch modes like dark mode, and explore templates and functions for data analysis.

Explore the Excel ribbon, customize and activate the developer feature, and use key tools for formatting, data, charts, pivot tables, and data sources to build interactive dashboards.

Explore the structure of an Excel worksheet by identifying rows, columns, and cells, and learn how ranges and cell addresses like A1 fit into workbook, sheet, and worksheet concepts.

Understand that an Excel workbook contains all worksheets; a worksheet is a single tab with rows and columns. Choose save options such as workbook, macro-enabled, and csv.

Learn to restore the home tab in Excel, customize the ribbon with new tabs and groups, and add or remove commands and quick access bar items to streamline data analysis.

Know what Excel understands before writing formulas: functions, numbers, cell references (absolute, relative, mixed), logical values, date and time, and use double quotes for items Excel doesn't understand.

Discover what data is and why it matters, and differentiate structured, unstructured, and semi-structured data with real-world examples like weather, financial, health, e-commerce, and social media data.

Understand the difference between normal ranges and Excel tables, and learn to create dynamic, auto-updating data with headers, and manage filters with Ctrl+T and Ctrl+Shift+L.

Identify the 10,000-record HR dataset, recognize the header, and convert to an auto-updating table with Ctrl T, using fields like name, department, salary, and leave balance.

Learn to calculate the total salary paid to all employees using Excel's sum function, selecting salary ranges and formatting the result in currency for clear reporting.

Learn to create name ranges from header rows in Excel, use them in sum and count formulas, and apply consistent formatting with format painter.

Learn to calculate the average years of employee experience in Excel using the average function, which computes the arithmetic mean by summing values and dividing by count, with named ranges.

Explore how the Excel count function tallies numbers and non-empty cells, and compare it with counta, countif, countblank, and countifs using practical examples.

Convert data to a table, create name ranges, and use count functions: count, count a, count blank, and count if to count records, whether numeric or text.

Explore how to use countif to tally employment type values for contract and full-time employees with dynamic cell references, then apply data validation and dropdown lists to build flexible dashboards.

Master countifs in excel to dynamically count employees by type and marital status, using dynamic criteria ranges and dropdowns to switch between categories.

Learn how to use the sumif function in Excel to total salaries by department, converting to a table and using named ranges with a drop-down to switch departments.

Learn to use the sumifs function in Excel to sum salaries for the sales department by multiple criteria such as gender, employment type, and marital status, and verify results.

Master the min and max functions in Excel to retrieve the minimum and maximum values from a column, and see how they integrate with other functions to enhance dynamic reports.

Explore how the small and large functions differ from min and max in Excel, using k to return the smallest or largest values, and apply this to reports and dashboards.

Learn how the Excel if function works as a logical tool returning values based on a condition, with a logical test and a value if true or value if false.

Use the if function in Excel to test if revenue meets the target, and return a bonus of $500 when true or zero when false.

Understand how the and and or functions in Excel evaluate conditions: and requires all true, or requires any true, and how equals comparisons with if decide results.

Explore how to use Excel's IF and AND functions with cell references to award a bonus to sales reps who exceed targets in August for smartphone products.

Use the IFS function to categorize revenue as outstanding above 40,000, average above 20,000, or low, and learn how to handle the default true case.

Learn how to use the Excel switch function to assign sales reps to new managers, as an alternative to nested if statements, including handling default results and avoiding cell locking.

Learn to categorize revenue against targets using the switch true function in Excel, distinguishing below, met, and exceeded outcomes.

Assign seasons from sales months with a switch true and the or function, then compute total revenue for each season using the sum and unique functions.

Explore how vlookup searches first column of a table and returns a value from another column using lookup value, table array, column index, range lookup for exact or approximate matches.

Learn to use vlookup to denormalize location, region, and transaction data, lock references, and create named ranges for dynamic lookups beyond pivot table analysis.

Learn how the match function finds the position of a value in a row or column, and how to pair it with vlookup in pivot tables and date grouping.

Master vlookup with the match function to build dynamic lookups, lock the lookup column with F4, and use named headers for stable, worksheet-based table references.

Compare XLOOKUP, INDEX and MATCH with VLOOKUP to pull region names from the location table into the transaction table using region IDs, and explore denormalization for Power BI readiness.

Explore the offset function to retrieve values from a starting reference by moving rows and columns, with optional height and width, enabling dynamic referencing and a dynamic dashboard.

Learn to compute a dynamic moving average in Excel using the offset function, selecting last or first n days with a date column and revenue data in a pivot table.

Learn to create a dynamic moving average in Excel using offset and XLOOKUP, building last 15 days revenue analysis with dynamic ranges and automatic updates.

Learn to create a dynamic monthly chart in Excel using the offset function and array techniques, switching values for total revenue, transactions, and quantity sold with data labels.

Explore creating a dynamic Excel chart for 63 customers using the offset function and a spin button, with sorting by revenue and conditional formatting for a dashboard.

Explore array function in Excel, generating numbers from 1 to 10000 with arrays spill across cells and use round array for 10 by 5 results with integer or decimal options.

Master the Excel filter function as an array tool, using and/or logic (asterisk and plus signs) to filter by category and subcategory, like furniture and electronics, laptops, gaming.

Explore how to use the sort function in Excel to sort chart data and dynamic arrays, specifying sort index and order (ascending or descending) and sorting by column.

Learn to generate dates with today and now, calculate start and end of month, and explore previous year month shifts with eomonth in Excel and Power BI.

Generate numbers with sequence and custom options for rows, columns, start, and increment; create random numbers with random between; and generate dates from today with date and text formatting.

Master data analysis with pivot tables to summarize customer data and product insights, generate sales performance reports, and analyze time-based trends in time series data.

Connect and transform large datasets using PowerPivot and Power Query, load data into the data model, and establish relationships without traditional lookups for scalable analysis.

Connect normalized tables in excel to create a denormalized data model by mapping product and customer IDs in a diagram view, establishing a one-to-many relationship for efficient analysis.

Create a calendar table in Power Query by duplicating dates, removing duplicates, and adding year, month, quarter, week, day, weekend and weekday indicators, then load to the data model.

Learn to create pivot tables from the data model in Excel, write dax measures, and analyze transactions, introducing dax concepts for data analysis in Excel and Power BI.

Create a Dax measures table with a blank query, rename it to Dax measures, and move the measure from the transaction table to this table for use in Power BI.