Excel Mastery: Data Analysis, Automation & Dashboards

Discover how productivity and efficiency relate to output over input and apply the right method, scalability, keyboard-only workflows, continuous improvement, and automation in Excel.

Choose a good mouse and adjust settings to improve Excel workflow. Disable enhanced pointer precision, set a slower, comfortable mouse speed, and apply changes in Windows mouse options.

Learn to navigate and select cells in Excel using keyboard shortcuts, including Ctrl+Arrow, Shift+Arrow, and Ctrl+Shift, to move through data and quickly select entire tables even with null values.

Learn to move between Excel sheets and tabs using mouse actions, Ctrl+Page Up, and right-click navigation, then switch across workbooks with the Windows taskbar and Alt+Tab.

Master Excel row and column management using mouse and keyboard shortcuts. Learn to select, insert, delete, duplicate, move, and hide rows and columns with keystrokes like Ctrl+Space, Shift+Space, and Ctrl+Plus.

Enter data in Excel by selecting a cell, typing, and using Enter to move down or Tab to move right; fill cells with Ctrl+Enter or Ctrl+D from the Home tab.

Learn to customize the quick access toolbar in Excel by adding tools like filter, data sorting, pivot table, and text to columns, and activate them with keyboard shortcuts.

Master alt shortcuts in Excel to access tools quickly and unhide rows or columns using alt, h, o, then o to unhide rows or h? Wait.

Learn to freeze panes in Excel to keep headers visible while scrolling, and freeze the top row, first column, or multiple rows and columns.

Create, rename, delete, and duplicate Excel sheets using on-sheet controls and shortcuts, with warnings when deleting data. Copy sheets by move or copy, or hold Ctrl to duplicate.

Learn to sort data in Excel by a column or multiple levels, using the sort tool to arrange by make then model, and to sort columns left to right.

Learn to use Excel’s filtering tool to display data by category, apply number filters such as year ≥ 2020, clear filters, and explore unique values by column for data analysis.

Learn to enforce correct data entry in Excel with data validation, creating rules for positive numbers and dropdown lists to restrict inputs and reduce errors.

Explore Excel find tool to locate values across columns, activate via home find or alt+F, and search for Ferrari to reveal matches; for large data, use the filter tool.

Learn to select objects in Microsoft Excel using mouse clicks, Ctrl+click for multiple objects, and Ctrl+A to select all; explore select objects mode, find and select, and exiting with escape.

Apply cells formatting in Excel by adjusting font, size, color, and fill; add borders, merge cells, center text, and remove grid lines for neat data presentation.

Apply the format painter to copy formatting across large tables using Ctrl+Shift+Down Arrow and Alt H f p, while unmerging cells to avoid issues when selecting non-empty columns.

Explore clear options in excel: clear all, clear formats, and clear contents, and see how unmerged cells and drag-down formatting relate to these actions.

Apply conditional formatting to highlight cells by value, use color scales and data bars, and manage rules to visualize data and build heat maps.

Explore how numbers are displayed in excel through number formatting, including general, number, currency, accounting, date/time, and percentage formats, plus custom codes for zeros, hashes, and thousand separators.

This lecture covers date and number formatting in Excel, showing format codes such as M, D, and y to display dates, month and day names, and formats, with Ctrl+1.

Explore how to pre-process data in Excel by using formulas and functions, including cell references, the equals sign, and sum, with examples of range inputs and updates.

Explore how relative and absolute cell referencing affects formulas when copying in Excel, and learn to fix ranges with dollar signs to preserve sums and percentages.

Learn how to use mixed referencing in Excel to multiply matrix elements by fixing either the column or row with absolute references, then drag the formula to fill the matrix.

Master mixed referencing by anchoring column with a dollar sign to fix the second number when dragging right, and anchoring the row to fix the first number when dragging down.

Learn to reference data across sheets and workbooks in Excel by using equal and selecting cells, with sheet names in single quotes; note absolute versus relative references.

Master paste special in Excel to paste values, formatting, and formulas with shortcuts, preserve column widths, paste as image, and keep calculated values independent.

Master the concat function to join text from cells or ranges, producing concatenated emails by company and highlighting the upcoming textjoin solution for inserting separators.

Learn how text join adds separators when combining cells to fix the glued emails from concat, and complete the assignment to practice text join and its arguments.

Master text join in Excel by joining a range of cells with a delimiter, ignoring empty values, adjusting the range, and pasting as values for stability.

Explore the left, mid, and right functions in Excel to slice text by characters from the left, mid, and right, extracting the country code and year with practical fill-down steps.

Split data into columns in Excel using delimiter or fixed width. Adjust data types to text to preserve leading zeros and decide whether to keep or remove the split columns.

Practice using text split to separate first and last names in Excel by writing the formula in the first cell and dragging down, and inspect the function's arguments and description.

Use text split in excel to divide text by selecting a cell, typing text split, and choosing a delimiter (single space) to create two columns for first and last names.

Apply text before and text after functions to slice names into first and last names, using function descriptions and arguments to practice data extraction in Excel.

Use text before and text after in Excel to extract first names and two-word last names by splitting at a space.

Use the trim function in Excel to remove leading and trailing spaces and collapse multiple spaces within text in selected cells. Verify results by pasting as values.

Learn how to identify uniquely occurring values in Excel using remove duplicates and the unique function, including multi-column results and sorting by category and subcategory.

Learn to use the filter function in Excel to extract specific rows from an array by applying multiple criteria, including the technology category and a specific order ID.

Combine unique, filter, and textjoin to display a chosen category with all its subcategories in a single cell, separated by a delimiter; optionally sort the results to order the subcategories.

Apply the text function in Excel to convert numbers and dates into text with formatting codes, including leading zeros, year, month name, and day, then verify by copying as values.

Learn date and time handling in Excel with shortcuts for current date and time, dynamic today and now functions, and how to use year, month, day, and the weekday function.

Learn to use the if function classify BMI data with if statements, assigning overweight, underweight, or normal categories using operators >, <, =, >=, <=, and Ctrl+D to fill down.

Learn how to use Excel’s find and replace to update data, such as changing 2023 to 2024, adjust formatting, search in cells and formulas, and apply replaces across ranges.

Use go to special to select blank cells across multiple columns and fill them efficiently. Copy only visible cells by selecting visible cells and using the alt colon shortcut.

Unpivot columns to convert comma and space separated sub values into individual rows, repeating the major category for each value, using Power Query split by delimiter and unpivot, then load.

Learn Excel math functions including sum, average, median, mode, r mode multi, count, standard deviation (population or sample), min, max, and absolute value, applied to each column, plus autosum shortcuts.

Learn to perform aggregated calculations by class using sumif, averageif, countif, minif, and maxif, using unique and sort to group data with fixed references.

Generate random data in excel using rand for 0 to 1 and rand between for integers, like 1 to 20, by multiplying the range width and adding the lower bound.

Use the sum product function to multiply two arrays and sum the results. Do it in a single, cleaner Excel operation, comparable to autosum.

Learn to reveal all formulas in Excel to debug worksheets, using the show formulas option and the Ctrl + ` shortcut to toggle visibility.

Explore window functions and how a row's value is calculated from other rows using sum, min, and max. Excel uses built-in functions, while databases use window functions.

Compute a cumulative sum in Excel to track order fulfillment against produced quantity using a window function with fixed references and an if check.

Apply sumif to allocate produced quantities by category and use vlookup to fetch category data, or filter manually, paving a scalable and optimized solution.

Compute cumulative sums by category with sumifs, fix the first range and criteria, drag to accumulate, and use vlookup plus an if test to flag nonnegative difference.

Define data as records of values stored in tabular tables and explore real-world examples across factories, hospitals, and mobile apps. Learn how data tables enable grouping, joining, and visualization.

Apply the data information knowledge wisdom pyramid to transform raw data into insights in Excel using grouping and pivot tables, improving decision making and efficiency.

Explore how primary keys and foreign keys link product and orders tables, enabling lookups of a product's name and price from order records.

Join the product and order tables via product id using Vlookup, then group by product with a pivot table to summarize quantities and compute revenue.

Identify the aggregation level by examining how data is grouped or summarized, and use grouping, pivot tables, joining, and unpivoting to move between order, product, and email and company levels.

Learn data normalization to create consistent data types, unique primary keys, and no redundant or repeating groups, then split tables to reduce data redundancy and storage needs.

Explore how vlookup, hlookup, and xlookup join data across tables in Excel by matching product IDs to fetch names, prices, and categories with exact and flexible search options.

Learn to use vlookup with match to pull multiple columns from one table to another in a scalable way, by using match to supply the dynamic column index.

Learn the vlookup match technique to perform lookups with one formula that can be dragged across columns, using match for the column index and fixed references for exact results.

learn to group and summarize Excel data with pivot tables, turning air quality data by continents and countries into a concise view using average and percent of grand total.

Master pivot tables to group data by continent and year, switch layouts, and compute average air quality index; group dates by year and month.

Learn to filter pivot table data with slicers and timelines in Excel, selecting continents and years, using multi-select, and narrowing date ranges from November 2024 to end of 2025.

Link data to pivot tables by defining a named range or creating a table, then use the defined name or table as the data source and refresh automatically.

Learn to visualize data with basic excel charts, including column, bar, and line charts, add a secondary axis in combo charts, and customize titles, labels, and colors for clear comparison.

Explore creating and customizing category charts in Excel, including clustered columns, stacked columns, 100% stacked, and a heat map with conditional formatting to compare production departments across months.

Learn to create time series line charts for sales trends, use histograms for distribution, and build scatter plots with a trend line and regression equation to relate sales to revenue.

Build a dynamic Excel financial dashboard using pivot tables, unpivot data with Power Query, and connect charts with GetPivotData, slicers, and timeline to analyze revenue, costs, and profit.

Analyze a social media related mental health survey data set and answer three key questions, practicing data analyst skills to interpret findings and insights from the project.

Analyze a mental health survey in excel using pivot tables, histograms, and charts; clean data, group ages, compare time spent on social media, and unpivot platforms with Power Query.

Learn how automation enhances efficiency by using Excel tools like Power Query, pivot tables, and macros to connect sources, preprocess data, and refresh automated reports and dashboards.

Master importing data with Excel Power Query, loading from file or folder, combining multiple tables across sheets, and applying transforms for preprocessing to support analysis and Power BI.

Learn Power Query pre-processing to clean data, change data types, split columns, and apply transform vs add column. Unpivot invoices to enable linking two tables for future merging.

Merge two queries in Excel with the merge queries tool, choose left outer join on the invoice field, then expand to show visual name, estimated arrival date, and transit time.

Create a data model by loading queries and establishing relationships between tables using a common column. Explore how pivot tables reflect cross-filtering and updates through refresh.

Explore data analysis expressions (DAX) to perform dynamic calculations without extra columns, illustrated by calculating warehouse working time and average sorting capacity with sumx on the products table.

Learn how to automate Excel tasks with macros, recording steps to apply formatting across cells, using absolute vs relative references, and saving in the personal macro workbook for reuse.

See how recorded macros generate VBA code in modules and introduce variables, message boxes, and input boxes. Use basic VBA concepts like strings, integers, and concatenation to customize Excel automation.

Learn how to add single-line and block comments in VBA, describe code for later readability, and use view and customize tools to comment or uncomment blocks.

Explore how VBA left, len, and split functions process input and return results. See how inputbox and messagebox capture input and demonstrate array indexing.

Learn how to manipulate Excel worksheets with vba objects, using set to store sheets, and apply methods and attributes to select, add, rename, and display sheet name in message box.

Explore the range object in VBA to select, merge, and unmerge cells, set values and bold font attributes, and retrieve column and row indices for efficient Excel automation.

Master the VBA cells object by indexing with row and column numbers, setting values with .value, and using .end to reach the last nonempty cell in a direction.

Use VBA to get the last column with columns.count and end, and the last row with rows.count and end, while anchoring on the first column to avoid null values.

Learn how to use VBA if statements in Excel, reading a cell with selection.value, declaring an integer num, applying if, else, and else if with modulo checks and input box.

Learn to use vba for loops to repeat actions, such as displaying a message box five times and filling cells from current selection with i progressing from 1 to 5.

Explore VBA's while loop to process a variable number of cells until value is not null, classify numbers as even or odd with mod two, and compare to for loops.

Learn to format tables of variable lengths in Excel using macros, including recording, editing code, auto-defining table ranges, merging, filling blanks, and applying formatting.

Create an Excel macro to separate orders by inserting new rows when adjacent values differ. Loop through cells, compare values, insert rows, and adjust indices for a clean, readable list.

Learn to combine Python with Excel by running Python code blocks in Excel, importing NumPy and pandas, reading cells and tables as data frames, and returning calculations.

Build a Python machine learning model with a random forest regressor to predict annual salary from age, training on a data frame and displaying predictions in Excel as inputs change.

Explore optimization in Excel using solver to find the best mix of decision variables that maximize revenue or minimize cost under constraints like time and labor.

Use the excel solver to maximize y = -x^2 + 4x by setting the objective to y and adjusting x, then run nonlinear solver to get x = 2.

Maximize revenue by optimizing the product mix under labor, materials, and demand constraints using Excel solver. Apply sumproduct calculations for labor, materials, price, and enforce non-negative and integer production quantities.

Use Excel solver to optimize capital budgeting by choosing projects with binary decisions to maximize net present value while staying within yearly budget constraints.

Allocate production across three factories to minimize unutilized capacity while respecting monthly capacities, using excel solver with integer allocations and nonnegative constraints.

Learn to optimize a complex resource allocation by distributing products across factories, reducing changeovers, and minimizing a combined cost with quadratic counts using an evolutionary solver.

Update weekly production reports for two departments using manual methods, pivot tables, and Getpivotdata, calculating order versus produced quantities and end production dates ahead of automation with Excel solver.

Automate Excel reporting by duplicating sheets, auto naming weeks and titles, and building date-driven texts with weeknum and text functions, then use pivot tables with calculated fields and GetPivotData.

Learn manual ETL in Excel by integrating item, product, and size tables into a single report, using concatenation, vlookup match, and pivot tables to align sizes.

Automate data extraction, transformation, and loading with Python scripts and Power Query to combine multiple data tables, format reports, and boost daily efficiency.

Learn to use ai and chatbots to generate Excel formulas, using Deep Seek, to create a random product code and paste the formula into your sheet.

Explore how to use ai and chatbots to generate a vba macro that loops through excel cells to insert separator rows between values, starting from the currently selected cell.

Use Excel solver to optimize resource allocation between two production departments with binary decision variables. Minimize weekly load differences and balance cumulative workload, and visualize results.