Microsoft Excel for Data Analytics

Master relative and absolute cell references in Excel and learn how copying formulas adjusts references. Apply absolute references to fixed totals and format currencies and percentages for clear analysis.

Explore the structure of an Excel function—equal sign, function name, and arguments in brackets—and see how the sum function uses a range like b4 to b8.

Master the Excel sum function to add cell ranges efficiently, using the formulas library or direct typing, with examples like B4:B11 and D4, F6.

Master the min and max functions in Excel to find the lowest and highest values in a range, using budgets as examples and copying results across months.

Use the average function in Microsoft Excel to compute the mean of a cell range, select the range, enter the formula, and verify the February result.

Explore how the count function in Excel tallies numerical values in a range, ignores text and empty cells, and updates when values change, with practical drag-and-bracket usage and undo.

Explore adjacent cell errors in Excel, indicated by green triangles when summing ranges that may include dates as numbers. Learn to review or ignore these suggestions and prepare for Autosum.

Learn how autosum speeds up summing non-contiguous and continuous ranges, use the autosum button and shortcut alt+equals, and adjust ranges for accurate averages in Excel.

Apply simple formats to Excel worksheets by adjusting fonts, font sizes, bold headings, background colors, borders, and themes to improve readability and visual appeal.

Explore how the if function in excel makes decisions from a condition, handling text and numerical data. Apply it to a sales data table to classify priority and price.

Learn how to use the IFERROR function in Excel to handle errors and return a custom value, illustrated with revenue per sale and a 'no sales yet' message.

Explore assignment 1 by analyzing the sales data to answer two questions, with the data link provided in the video.

learn how the match function returns the numeric position of a lookup value in the employee id column of the main table, using exact match (0) to locate 1054.

Master index–match in Excel to retrieve departments, first names, and other fields by employee ID with exact matches and flexible array references.

Use the index and match function to complete the table, finding first name, last name, department, and pay rate, with charts to come.

Create and format Microsoft Excel charts, including column, bar, and pie charts, from selected data. Adjust titles, axis options, data labels, and chart location, including moving charts to sheets.

Pivot tables in Excel synthesize large sales data by year, month, region, and product type, enabling quick totals, averages, and comparisons.

Master grouping pivot tables in Microsoft Excel for data analytics by selecting months to create quarters, naming groups, ungrouping when needed, and refreshing to reflect new data.

Learn to filter pivot table data by product type and region, sum sales across selected items, and drill down into summarized results for clearer insights.

Explore using slicers to filter pivot tables in Excel. Learn to insert slicers by region and year, and compare data with interactive filters.

Copy and paste visuals into a dashboard in Excel, add a slicer for interactive filtering, and fine-tune visuals with color, outlines, and currency formatting.

Activate the Microsoft Power Pivot add-in in Excel by going to file options, add-ins, and COM Add-ins, then enable Power Pivot to reveal the Power Pivot tab.

Learn to build data models with pivots by linking two related tables via a common field, add to the data model with Power Pivot, and establish a one-to-many relationship.

Create pivot tables from data models by linking customer and order data in Power Pivot, then count orders by country and sum freight costs.

Explore using Excel data table for what-if analysis, replacing the interest rate input to compare monthly mortgage payments across different rates.