Excel Mastery: Data Analysis, Automation & Dashboards

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.

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 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 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.

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.

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.

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 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.

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 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.

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.

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 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.

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.

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.