Microsoft Excel for Data Analysts

Learn to navigate Excel worksheets with keyboard shortcuts and color-coded tabs, use the name box to jump to cells, and understand basic cell references and printing basics.

Explore relative, absolute, and mixed cell references in Excel, learn how to lock ranges with dollar signs, and apply formulas across a dynamic range for accurate results.

Explore backstage view to control Excel, customize the ribbon and quick access toolbar, password-protect workbooks, share files in the cloud, and use R-1 sequence reference style with custom lists.

Learn to print Excel worksheets using backstage view, adjust margins, orientation, paper size, header and footer, center horizontally or vertically, set print area, and use fit on one page.

Learn how to protect Excel workbooks and sheets by selecting entire sheets, locking and unlocking ranges, hiding formulas, and applying password protection and encryption.

Learn to save Excel workbooks in multiple formats for online or print use, and master importing and exporting data to text and comma-separated value formats via backstage view.

Explore Excel for data analysts, mastering tabs, toolbars, ribbons, formulas, chart creation, data validation, sorting and filtering, and cloud sharing through OneDrive for online collaboration.

Master how to enter formulas in Excel using functions, distinguish between formulas and functions, and manage references with absolute, relative, and named ranges across worksheets.

Learn best practices for Excel spreadsheets, including autosave versions, renaming and color-coding sheets, converting data to tables, and defining named ranges for reliable formulas.

Learn to work with name ranges in Excel, modify them with the name manager, and use sum, count, min, max, and average on April to June data.

Create and format charts in Excel to visualize data, exploring chart types like clustered column, line, pie, and donut, and customize legends and data labels.

Explore how to use if functions to handle conditional structures in Excel, covering logical tests, true and false outputs, nested conditions, and absolute references on a dataset.

Apply if functions in Excel to automate a 10 percent discount when quantity exceeds a threshold, using nested and dynamic conditions to compute final prices and total sales.

Explore using nested if statements in Excel to handle multiple conditions, such as laptop group and quantity greater than 65, to apply a 10% discount and compute final price.

Master how to simplify nested if statements in Microsoft Excel for data analysts by using and, or, not to combine multiple conditions with proper brackets and absolute references.

Explore advanced Excel functions such as countif, sumif, sumproduct, and sumifs through sales data scenarios, showing how to count transactions and sum quantities under multiple criteria.

Master mega formulas in Excel by combining nested ifs and logical operators to yield complex, single-cell outputs. Use examples with laptops and bucketed quantities.

Explore advanced data filtering techniques in Excel, apply data validation to control inputs, and create dynamic lists, while learning filter criteria and how to copy results to a new location.

Learn to build dynamic drop-down lists with data validation in Excel, using a five-item source to filter items and enforce quantities between 50 and 90 with input and alert messages.

Master data validation in excel, build dynamic lists from named ranges, and use ranges to control inputs. Learn to apply indirect for city-based lists.

Explore data visualization in Excel 2013 and learn to select and customize chart components for effective data analysis. Use recommended charts and pie or column charts to show percentages.

Learn to perform Pareto analysis in Excel by sorting data, calculating cumulative percentages, and creating a two-series chart with a secondary axis to reveal the 80-20 impact.

Learn to plot mean targets and limits in Excel charts by adding a target line on a secondary axis, using 80-20 insights, and displaying mean, median, and minimum/maximum cumulative.

Convert flat data into a formatted table in Excel 2013, enabling dynamic headers, filters, colors, flash fill, and automatic formula propagation for meaningful, interactive data analysis.

Demonstrate flash fill techniques in Excel, showing how to merge fields with and without functions, use control+e to apply, and explore practical examples with concatenation and data design.

Learn conditional formatting in Excel to visualize data with data bars, color scales, and icon sets, while managing rules and applying text and number formats.

Learn to filter and sort by color or icons, use advanced conditional formatting with text and numbers, and control formatting remotely with formulas and data validation.

Explore lookup functions in Microsoft Excel 2013 to perform complex calculations efficiently, including VLOOKUP and HLOOKUP, index and match for reverse lookups, and error handling with tracing formula.

Learn to use vlookup and related lookups to retrieve data from vertical and horizontal tables, choose exact or approximate matches, and handle sorted data and transposed data.

Learn to implement vlookup with fixed ranges using absolute references and dollar signs, so copying formulas across profit and loss and customer data yields exact matches.

Use the lookup function for horizontal data, not vlookup, to get Eric’s number of customers. Set the row or column index for the exact result as data expands.

Explore reverse lookup in Excel using index and match to identify who handles a specific number of customers, then build a nested max-index-match formula to find the top performer.

Learn to handle vlookup errors in Excel by using if error or is error, providing blank outputs or conditional logic to manage missing data.

Learn to trace formula dependents and precedents in Excel, identify which cells influence a formula, view arrows, toggle show formulas, evaluate formulas, and set watches to monitor changes.

Explore formula auditing and error checking in Excel, identify precedents and dependents, resolve circular references, and apply look up, index and match, and is error or if error.