Advanced Excel Formulas, Functions & PivotTables

Discover how pivot tables calculate, summarize, and analyze large data sets to reveal month-by-year trends and product-wise totals in sales data, with a practical worksheet example.

Learn to create a pivot table in the same worksheet, format your data as a table with a name, and refresh the pivot table to reflect added or deleted rows.

Create pivot tables in a new worksheet by formatting data as a table and dragging fields into rows, columns, and values; refresh to reflect added or deleted rows.

Learn to group pivot table data by selecting months, using the group selection option in the pivot table analyze tab, and naming groups like group one, two, and three.

Modify pivot table calculations in Excel by adding the sales field to the value area and choosing average, count, or percentage of grand total, with custom names for clarity.

Format pivot table values in Excel using two methods: apply currency formatting via the home tab, or adjust value field settings (sum of sales) to set currency options.

Explore the drilling down effect in pivot tables to view detailed April sales data by product, using double-click to open a new worksheet for analysis.

Create a pivot chart from a pivot table in Excel, using a column chart, and see how pivot table changes automatically update the chart with design and format tabs.

Master the filter option in pivot table data and its effect on pivot charts by dragging fields into the filter section to apply year and salesperson filters with automatic updates.

Explore the slicer tool technique to filter pivot tables and pivot charts in Excel, selecting single or multiple years and regions to dynamically update results.

Explore what Power Pivot does in Excel and how to add the Power Pivot tab to the ribbon, enabling powerful data analysis and modeling in supported versions.

Create a data model in Power Pivot to relate the customer info and order info worksheets by customer ID, enabling complete order lists in Excel.

Learn to create relationships after building a data model in Excel, linking customer info and order info by customer ID, using diagram view or alternative data view methods.

Create a pivot table from a data model using Power Pivot relationships between customer info and order info to analyze countrywise orders, freight, and rates.

Discover how a designed excel list uses clear column headers to help Excel identify data, locate records with control F, and maintain data integrity by avoiding empty rows or columns.

Sort an employee record by a single base column, using the first name as the key, and apply ascending or descending order with A to Z or Z to A.

Master multi-level sorting in excel by ordering data first by first name and then by last name, using up to 64 columns and adding sorting levels as needed.

Learn how to use custom sorts in an Excel list to order months by long form names. Select the month column, open sort options, and apply a custom list.

Use the AutoFilter tool to quickly view data by month or buyer in Excel. Learn how to apply filters, use dropdowns, and clear to restore the full data.

Learn to create subtotals in a list by sorting products alphabetically, applying the subtotal function to sum sales per product, and using the outline to show subtotals and totals.

Learn to use the freeze panes tool in Excel to keep headers visible while scrolling, with steps to freeze and unfreeze headers and tips on selecting the correct row.

Group and outline data in Excel to hide or reveal columns and rows. Learn to create groups for months and divisions using the data tab outline tools.

Link worksheets in Excel to consolidate town data on a summary sheet by summing B4 from 2019, 2020, and 2021 using equals formula and plus signs, drag the formula down.

Link worksheets with the consolidate tool in excel to summarize town and circulation from 2019–2021 into a summary worksheet, using sum and left-column labels.

Master Excel print options for large data sets by repeating header rows across all pages, adjusting pages with page break preview, and ensuring proper print sequence.

Learn to create and use name ranges in Excel, using week one (B5:B9) for sum, average, and printing, with navigation benefits and absolute vs relative reference drawbacks.

Master editing name ranges in Excel using Name Manager: edit existing ranges, update references such as B5:B8, and add or delete name ranges in the advanced Excel master class.

Learn to use the if function to compare sales totals to a monthly goal and return yes or no. Freeze the goal cell and use a named range for accuracy.

Master nesting multiple functions in Excel by using the if function with the and function to test monthly and weekly targets of 8000 rupees or more, via logical arguments.

Explore using the and function with the if function to award a bonus only when both monthly and weekly targets are met, returning bonus or no bonus.

Learn to use the count if function in Excel to count only yes entries within a range, such as H5 to H9, by setting the criteria 'yes'.

Master the sumif function in Excel by calculating total units and total sales for store 3000, using range criteria and sum_range with ctrl-shift-down-arrow shortcuts for quick selection.

Learn to use the iferror function for safe division in Excel, returning a custom message like 'something is wrong' when division errors occur.

Master the vlookup function to perform vertical lookups, retrieve last names by exact match from the master employee list, using the table array and column index number, then drag down.

Learn how to use the edge lookup function for horizontal lookups on a master inventory list, retrieving warehouse inventories for product exp 200 with exact match.

Learn how to use the index function in Excel to return values or references from a table or range, covering single and double dimensional use, absolute references, and practical examples.

Learn how to use the match function to locate an item's position in a range and return its index in Excel, with exact match (zero) and software and laptop examples.

Learn to use hlookup with match to retrieve student scores by month and create dynamic row indexing for exact matches.

Explore how to use the left, right, and mid functions in Excel to split text into supplier ID, part, and product code, including function arguments and dragging to apply.

Learn how to use the len function to count characters in a cell, including spaces, with a practical example in B3 and extend the result across a table by dragging.

Learn to use the concatenate function in Excel to combine first and last names into a full name, with spaces, and apply the formula down a column.

Explore trace precedent in Excel to see which cells affect a selected cell, using the formula auditing tools to view and remove arrows.

Master the trace dependents function in Excel to identify where a cell’s value is used in formulas via formula auditing arrows.

Master the show formula function in Excel to quickly reveal all formulas in a worksheet and inspect correctness with formula auditing. Print worksheets with formulas for review.

Learn how to use the watch window function in Excel to monitor cells across worksheets, add watches, and track changes for formula auditing.

Explore how to protect a complete worksheet and selectively unlock cells, using password protection, unprotecting, and applying the protection tab's locked setting to ranges like B5:E9.

Learn to protect the workbook structure with a password, preventing renaming or moving sheets, and remove protection by unprotecting with the password.

learn how to protect an Excel workbook with a password, how to encrypt with password, test access by reopening, and remove password when needed.