Master the Excel subtotal function from the data tab to quickly subtotal data by day after sorting, choosing sum or average, and applying replace and summary below data options.

Learn to apply the subtotal function manually on a dataset to compute turnover based on visible records, using filters to sum only selected days or walk-ins above a threshold.

Apply the subtotal average function to a retail dataset to compute daily and monthly averages, compare days like monday and sunday, and analyze turnover and footfall within date ranges.

Apply the max function under subtotal to identify maximum sales across filtered data, using Samsung and Apple as examples and demonstrating relative references.

Learn how the subtotal minimum function identifies the lowest sales, compares with the maximum, and uses Indian handset brands and Sunday sales as examples.

Master the subtotal count function in Excel by counting days meeting sales criteria in a handset dataset, with April examples like days above 80 units.

Learn to map warehouses to their DSMs using VLOOKUP with exact match, selecting the lookup value as the warehouse name and using an absolute table array for reliable results.

Demonstrate using vlookup with approximate match (true) to assign grades from a marks table, illustrating 15 to 20 yielding b plus and 25+ yielding a plus.

Build a dashboard that uses vlookup with mixed references and a names dropdown to show a student's marks across subjects, using column() for dynamic index, exact-match, and absolute references.

Learn to use an if function with vlookup to calculate employee incentives based on years of association and salary brackets, applying two rate tables with absolute references and approximate match.

Explore horizontal lookup with hlookup to retrieve loan rates from a horizontal table, using an exact match and absolute references, and apply data validation to create a dropdown.

Explore how the lookup function retrieves the territory sales manager name from a warehouse name using a lookup value and absolute references. Compare it to vlookup and practice insights.

Apply match to locate the row for women's fashion and the column for new arrivals, then use index to return the sales output at their intersection.

Learn how the index function retrieves a cell value from a range by row and column, and combines it with match to analyze sales data in large tables.

Apply the match and index combo formula to fetch sales output from a consolidated array, using exact matches for product and classification and leveraging data validation.

Learn to build pivot tables in Excel to summarize large data sets, organizing sales by product, salesperson, year, and region, with step-by-step guidance on rows, columns, values, and filters.

Learn to use value fields in pivot tables, adjust value field settings, and compute percentage of grand total to reveal product-category contributions and yearly sales patterns, including 2018.

Learn how to refresh pivot tables to reflect data changes, update employee names, and remove decimals for a cleaner view, using right-click refresh or the analyze tab.

Learn to update a pivot table by changing the data source when the parent table gains new records, using the change data source function and refreshing to include new data.

Create a pivot table and group dates into months or quarters to summarize sales, using a new sheet and right-click grouping for days, months, or quarters.

Learn to use the timeline feature in pivot tables to filter sales data by date and analyze monthly sales by salesperson and product, with the date column requirement.

Learn to use slicers with pivot tables to filter data for dashboards by inserting slicers, selecting fields such as salesperson and year, and formatting them.

Master running total calculations in data analytics by using mixed references and dollar-sign locking to accumulate totals after each entry, contrasting with the grand total.

Learn to compute total sales by multiplying selling price with units sold using the sumproduct function and frequency, and ensure matching array lengths to avoid errors.

Explore scenarios of sumproduct with frequency using price and items sold to compute totals. Learn how text values and a missing second array affect results, and why numeric form matters.

Apply the sumif function with number criteria. Learn conditions such as less than or equal to 22 and greater than or equal to 22, using cell references for sums.

Learn to sum sales by product batch with the sumif function using number criteria. See three scenarios: direct number, a cell reference, and using another column as the criteria.

Explore four variants of sum if with text criteria, learn to set the range and criteria in quotes, use not equal to GM, and sum the corresponding sales.

Explore sumif with text criteria 2 using wildcards to match patterns; use ? for one-character variants and * for zero or more characters, summing kick, kick one, and kick two.

Learn to use sumif with date criteria to sum sales on a specific date, under a date threshold, and on today's date, using date functions for precise totals.

Learn to use sumif with or criteria to sum sales for bread or jam by adding two sumif results, illustrating range, criteria, and sum range, with a total of 43.

Apply sumif with not equal to criteria to sum sales for a given product set while excluding empty product names, and analyze revenue across streams.

Learn to use sumifs to sum values across multiple criteria, using dates, numbers, and text, with logical operators and wildcards for partial matching.

Learn how to use the sumifs function to sum values based on multiple criteria, by specifying a sum range and multiple criteria ranges such as bread sales in deli.

Discover how to use count formulas to tally numbers, non empty cells, and empty cells. Learn when to use count if for single criteria versus multiple criteria in data analytics.

Discover how the count function in Excel tallies numeric values in a range, including negatives, fractions, dates, times, and percentages, while ignoring empty cells and text.

Use counta to count non-empty cells in a range, including numbers, text, logical values, and errors, while not counting empty cells, as shown in the examples.

Explore the count blank function, which counts empty cells in a range. Two empty cells in a range illustrate the result, and zero-containing blanks are not counted.

Learn to count cells with number criteria using values, ranges, and operators such as greater than, less than, equal to, and not equal to, including a reference cell like F1.

Explore count with number criteria using countif to tally cells equal to 30 and not equal to 30 within a range, using operators and wildcards.

Learn to count cells that contain specific text using countif with text criteria in quotes, and apply operators and wildcards for exact or partial matches, including bread examples.

Learn to count cells that contain an exact text using countif with wildcards, applying question marks for single characters and asterisks for zero or more characters, exemplified with 'bread'.

Learn to count with or logic using the countif function, counting cells that match any of two conditions such as bread or jam.

Understand how countif counts cells with a single criterion in a range, such as text, empty cells, numbers, percentages, or date/time errors, and how countifs applies multiple criteria across ranges.

Learn to count cells with multiple criteria using countifs, combining date and product ranges to count entries on a specific date or by a product such as JAMB.

Generate random decimal numbers between zero and one in Excel using the rand function, and copy the formula across your column. Paste special values to prevent changes when recalculation occurs.

Learn to generate random whole numbers between two boundaries with ran between, using bottom and top like 60 and 90 to create tables and explore repetition with the rank formula.

Generate random decimal numbers between two boundaries by modifying the rand function, using bottom value plus the difference times rand, and freezing results as static values.

Discover how to use the rank formula to rank team performance by monthly achievement, with descending order and absolute references for automatic updates and dashboards.

Learn how to rank team member performance using the rank.average formula to handle ties by averaging ranks, with step-by-step Excel guidance on a new sheet.

Generate unique random numbers without duplication by using rand and rank functions to convert decimals into unique whole numbers with absolute references and paste special for sorting.

learn to convert negative numbers to positive using the absolute value function (abs) in Excel, enabling consistent data analysis and trend calculation when numbers indicate opposite directions.

Learn a second method to convert negative numbers to positive using Excel's Flash Fill above 2013. Copy numbers, then use Flash Fill from the ribbon or data tab.

Compute the weighted average to find the store’s average revenue per sale by multiplying prices by quantities, summing the products, and dividing by total items sold.

Use the and logical formula to filter students who score at least 45% in paper one and at least 60% in paper two, with practical Excel-like examples.

Explore how the if function evaluates a logical test in Excel, returning high or low when project cost is under 40,000 to indicate approval probability.

Apply if with and or logic to determine student pass or fail based on two tests, requiring at least 50 in paper one and 70 in paper two.

Explore or logical formula and its truth conditions, where any true argument yields true and all false yield false, applied to 45+ in paper one or 60+ in paper two.

Explore how the exclusive or XOR logical formula returns true only when exactly one condition is met, and false when both or neither conditions are met, illustrated with exam-mark examples.

Learn to use an if with or logical formula to decide pass or fail based on two conditions, returning pass when either B ≥ 45 or C ≥ 60.

Learn how to use and and or logical formulas to determine discount eligibility for items, specifically ensuring the product is a t-shirt and the color is blue or green.

Explore the not logical formula and how it reverses boolean values, using an eligibility rule: students scoring less than 60 must take paper two, while those above 60 do not.

Master nested ifs and the ifs function in Excel to assign grades from total marks, including 90 for A-plus and 80 for A, across Excel versions.

Explore how to use the round function, including round up and round down, to convert wages from hours worked into clean, whole-number payments by multiplying hours by hourly rate.

Master the roundup function in Excel, which rounds numbers to the next highest whole number using roundup(number,0), and apply it to calculate wages by multiplying hours by hourly rate.

Apply the round down formula to convert fractional working hours to full hours, pay wages based on full hours, and promote discipline while minimizing cash outflow.