Excel Interview Questions for Practice & Exam Preparation

Count rows that contain specific values using a sum by row formula with a lambda, covering a named range to determine how many times 19 appears in the data.

Count rows with or logic in Excel by counting groups where color 1 or color 2 equals red using a simple formula, demonstrating the array and criteria approach.

Learn to count total, sold, and remaining units in Excel using simple count formulas and a total minus sold calculation.

Count total matches in two ranges using Excel formulas like sumproduct with a double dash and is number match with a lookup value and lookup array for exact match.

Count unique dates and total trades using counta and unique to reveal five trading days, then apply the frequency formula with data array and bins array to produce five.

Learn to count unique numeric values in a range using Excel's frequency function, data array, and bins array with a double dash to return the result.

Demonstrates counting unique numeric values with criteria using counta and unique, then applies an if formula with frequency and bins array to produce a two-value unique count.

Count unique text values in a range using a sumproduct formula of 1 divided by countif(range, range) to obtain the unique count.

Count unique text values with criteria by filtering the names array for project omega and returning the unique project names.

Learn to count unique values in a range using a sumproduct with 1 divided by countif in Excel, applying countif to obtain a correct unique count.

Count visible rows with criteria using a formula that combines subtotal, offset, index, and row. Subtract min to ensure only unhidden rows are counted.

Learn to count values greater than 50 across non-contiguous ranges using COUNTIF with indirect and a curly-brace array of ranges, yielding a result of 9.

Master counting with multiple criteria and or logic in Excel using countifs and sum. Count complete or pending statuses from the status column.

Learn to compute a running count of blue occurrences in a list using an Excel formula with if, range, and criteria, returning a blank for nonmatches.

Learn to compute a summary count with a percentage breakdown using a countif formula, absolute references, and division to produce a 100% total across priorities.

Learn to count with multiple or criteria in Excel using sumproduct and match, applying exact match lookups across two ranges.

Count two-way summaries by department and group using a countifs formula, yielding department-wise counts for group a and group b in one shot.

Calculate a running total in Excel using a sum formula and an if condition to skip blanks, returning blanks when data is missing.

Count cells that contain formulas and non formulas in Excel using simple formulas to identify each type and verify the results.

Learn how to create a subtotal by invoice number in Excel using if, countif, and sumif, displaying sums for unique invoices and leaving duplicates blank.

Learn to sum data while ignoring errors in the amount column using Excel formulas. Use aggregate with ignore error values, or sum with iferror returning zero to get correct totals.

Learn to sum the bottom n values in Excel using sumproduct with a boolean mask, and compare array formulas like row indirect and sequence to achieve the same result.

Sum bottom n values with criteria in excel: use filter, small, and sum to compute the three smallest values in group a and replicate for group b.

Demonstrate group wise subtotals in Excel by using sumif with a criteria to blank duplicates, then compute group totals with sum range and F4 references.

Compute monthly totals by applying a sumifs formula with a sum range and two criteria ranges, using edate to filter by month and show January and February totals.

Learn to compute monthly totals from dates and amounts ignoring year using a sumproduct and month function, applying a fixed range so totals stay correct when years change.

Demonstrate summing by quarter with Excel's sumifs, selecting the sum range and a fixed quarter criteria to produce totals for quarters 1 to 4.

Use sumifs to aggregate data by week, specifying the sum range and two criteria ranges, with a start date and date plus seven to create weekly intervals.

Learn to compute totals by weekday in Excel using sumproduct and the text function to extract the day from dates, producing daily outputs.

Learn to sum amounts by year in Excel using the sumproduct formula, selecting dates to compute yearly totals from the data.

Learn to sum columns by adjacent criteria using a sumproduct formula with row constants, applying ranges B to H and C to I, and replicate for A and B.

Learn how to sum every nth row in Excel using an offset-based formula to total blocks of five values in the same column.

Learn how to sum the first n matching values in Excel by combining take and filter formulas, using color criteria to obtain the sums.

Learn to sum the first n rows in Excel using a simple sum with take approach, calculate the first six values totaling 575, and see the result.

learn how to sum formula cells and non-formula cells in Excel, then compute the overall total by adding the two results.

Learn to use the Excel sumif function to sum products whose names start with SHA, using the range, criteria 'SHA*', and sum range, demonstrated by a 30.45 result.

Demonstrate using sumifs to sum values between a start value and end value, using a fixed sum range and criteria for greater than and less than equals to.

Apply a case-sensitive sum for specific codes by using a sumproduct formula with the exact function to total the corresponding quantities.

Apply the sumifs function to total values where a cell contains text from another cell, using sum range, criteria range, criteria, and wildcards to match text such as CA.

Apply the sumif function to total cells that match text criteria, such as red or TX, by selecting the range and the corresponding sum range.

Learn to sum cells containing either x or y by using a sumproduct with text search for red or blue.

Demonstrate a two-criteria sum if approach with wildcards to sum values whose cells contain both red and blue, then pink with purple and green with blue, using F4 references.

Categorize text with keywords using an Excel formula that combines index, match, isnumber, and search to assign a category based on the keywords.

Apply the sum ifs formula to total values between a start date and end date, using two criteria ranges with greater than and less than or equal to comparisons.

learn to use the sumifs formula in Excel to sum values when the date is greater than a given date, such as October 15, 2022.

Learn to sum across multiple columns in Excel by using a simple sum filter formula for different groups, and propagate results by dragging down.

Learn to perform sum if with multiple ranges by combining two ranges with v stack, then group by and choose cols to compute the summed result.

Learn how to use the sum if function with multiple criteria in Excel to total values for red or blue by specifying a sum range and criteria range.

Learn to use the sumifs formula to sum the last 30 days in Excel, using today minus 30 as the criteria.

Learn to sum the last three columns with a simple Excel formula, selecting the array and rows and columns, yielding 303.

Apply a simple Excel formula to sum the last six rows by selecting the array and using the sum function, yielding 7300 as the result.

Practice summing matching columns in Excel using the sumproduct formula, selecting data and proper bracket placement to avoid errors.

Use the sumproduct formula in Excel to sum values where the column code equals B and the day equals Wednesday, applying two criteria across matching columns and rows.

Learn to sum numbers in a cell using a simple sum formula and text split, and apply the technique across other cells for Excel practice tests.

Learn to sum the top three values in a data set using Excel formulas such as sum(large(...)) and an alternative with an array constant {1,2,3}.

Learn to sum the top n values with criteria in Excel using a sum of large with a filter for group A. Replicate the same approach for group B.

Explore using sumifs with a horizontal range to total values by criteria, demonstrating sum range, criteria range, and fixed versus changing references for red data.

Learn to sum values using sumifs with multiple criteria and or logic by summing complete and pending statuses, and create an array formula with curly braces to return both totals.

Learn to use sumproduct with if to sum amounts by color and location, such as red amounts for TX and CO and blue amounts across data ranges.

Learn to compute an average while ignoring errors in Excel by using the aggregate function, selecting the average option, and applying the ignore errors setting on the data.

Learn how to calculate the average by group in Excel using a simple formula, defining the average range and criteria ranges to obtain grouped results.

Demonstrates using averageifs to compute monthly averages from a range, with two criteria ranges and a start date via edate for Jan through Jun.

Compute the average call time per month by using a formula with average and filter, converting duration and dates into a mm yy text format to produce monthly averages.

Calculate the average hourly pay per day in Excel by using the averageif formula, selecting the average range and corresponding date criteria, and applying it across the dataset.

Apply the averageifs function to compute averages for nonblank and blank groups in Excel, using an average range and criteria ranges to separate not blank and blank entries.

In Excel, compute the average of the last three numeric values with a formula that uses filter, isnumber, and minus three to target the most recent data.

Learn how to compute the average of the last n columns in Excel using the average formula, with a concrete example for the last three columns.

learn to calculate the average of the last five rows in an Excel data set using the average formula and a simple array reference.

Learn to compute averages in Excel while ignoring zeros using an average with a not equal to zero criteria, illustrated with a range and result comparison.

Apply the averageif function in Excel to compute department-wise salary averages using a salary range and department criteria, demonstrating practical data analysis in spreadsheets.

Compute the overall average and the top three scores for four quizzes using Excel's average formula and an array formula with curly brackets.

Demonstrate calculating the average with multiple criteria using averageifs to average by group and region.

Learn to use the Excel filter function to extract rows where state equals TX, using a simple formula and visible results.

Combine data from multiple worksheets in Excel using let, vstack, and filter to collate sheet 17 through sheet 19 (B5:E16) with nonblank rows.

Combine two ranges in Excel using a vstack formula to collate array 1 and array 2, producing the resulting output.

Learn to count unique values and ensure colors are unique using Excel formulas: use =UNIQUE(array) to extract unique colors, then =COUNTA(UNIQUE(array)) to count them.

Count unique values with criteria in Excel using a filter and a formula to return unique names for a given project, such as omega.

students learn how to use the let function to define name and points, apply vlookup to fetch data from a table, and assemble a customized message.

Create a dynamic summary count table in Excel using let, unique, sort, choose, and count to extract data and sort by a descending criterion.

Explore constructing a dynamic two-way average using the averageifs function, with a row constant and a column constant, filtering by age and gender, and copying results for females and all.

Master a dynamic two-way count in Excel by using the countifs function with multiple criteria ranges and criteria, enabling real-time data tally.

Use SUMIFS to create a dynamic two-way sum with city and size criteria, fix the row constant with F4 and the column constant, expanding ranges to F5:F9.

Learn to extract common values from two lists in Excel using the filter function and count if, with a practical approach aligned to interview and exam preparation.

Learn to extract numbers from a text string in Excel using a text split formula with space as the delimiter, and ignore errors to capture numeric output across columns.

Learn to filter and sort data in Excel without errors using the sort and filter functions, applying criteria between a start date and end date to return the matching groups.

Learn to filter data by group and transpose it from horizontal to vertical using a simple transpose and filter formula for the fox group.

Learn to filter data by column and sort results by row using a sort filter formula, focusing on 2017 groups and descending sort order.

Filter data by dates using the month function to select July. Apply the =FILTER formula to the array with month equal to 7 to extract July results.

Learn how to use Excel's filter with case-sensitive criteria to extract red text in uppercase from a data array.

Apply an Excel filter formula to select an array where the values match any of red, blue, or black, using filter, isnumber, and match with an exact (0) lookup value.

Filter data between dates in Excel using the filter function with start date and end date criteria to produce the output.

Learn to use the Excel filter function to extract every third row from a dataset by applying a sequence with a divisor of three, producing the nth row output.

Apply the Excel filter to remove blanks across three columns by using the filter formula with non-blank criteria, returning only non-empty values.

Discover how to filter the first and last n values in Excel using filter, index, and sequence to extract the first three and last three cities.

Learn to extract the top three scores from a data set using Excel’s filter and large functions, building a formula that returns the top three scores.

Apply the filter function to extract a group's top n scores using criteria. Learn to target group B and return the top 3 values.

Use the Excel filter function to extract text containing rd by combining filter with isNumber, search, findText, and withinText to return matching results.

Apply the Excel filter function to extract red or blue items from a data group by selecting the array and criteria, and view the resulting output.

Use the filter function to extract matching values from list 1 and list 2 and identify non matching values with a not condition.

Use a simple unique filter formula on the data, selecting an array and a counter range with a criterion of greater than or equal to 2 to reveal duplicate colors.

Learn to filter data in Excel using the filter function with complex multiple criteria, including text matches and a not month equals to 4 condition.

Apply a multi-criteria filter in Excel using a formula to select records where group equals a and score greater than 80, displaying the resulting data.

Learn to filter data with multiple or criteria in Excel using the FILTER function, array handling, and exact-match logic with isnumber and match.

Apply the Excel filter with partial match by using the filter function with a search text to extract rows where the substring appears in last names.

Generate six-character random strings from A to Z in Excel using a simple tagjoint formula with a delimiter and rand array, then drag down to produce multiple outputs.

Learn to compute dynamic column totals in Excel by creating a named range and applying a simple sum formula, yielding correct results (for example 59).

Learn to calculate row totals for days using by row with a lambda formula, name the result RR, and sum with RR sum to total all colors.

Explore using the map function with lambda to filter data by color criteria (red or blue) and a quantity greater than 10, returning true or false for matching rows.

Use min with unique to an array to return items that appear exactly once, yielding 300 as the minimum unique value.

Learn to perform a random sort in Excel by using sort by with an array and rows formula to output a randomized dataset.

Learn to remove blank rows from data using an Excel filter formula. Apply the filter to an array to keep nonblank rows and produce a clean output.

Sort birthdays by month and day in Excel with a simple formula using mmdd text format to order a date array.

Sort data by a custom list in Excel using a formula with sortby and match to order items (red, blue, green) as shown.

Learn to sort data in Excel by a specific column using the sort function. The lecture demonstrates sorting by the third column with sort index set to 3.

Sort by two columns using arrays and a specified sort order, applying descending to the first array and ascending to the second to sort the data.

Sort text by length in Excel using a sort by formula with length on an array, arranged in descending order from big to small.

Learn to sum numbers with text in a single cell using a formula that isolates values after the text and a space, yielding the total across the data.

Learn to compute unique values by count using the unique filter and countif, applying criteria greater than one and greater than two to identify red, green, and blue.

Learn to count unique values and tally unique colors in Excel using the unique function and a count formula on an array.

Learn how to extract case-sensitive unique values in Excel using a reduce formula with lambda, exact, and array logic to ensure precise duplicates handling.

Learn to extract unique values from multiple ranges in Excel using the unique function, combining Array1, Array2, and Array3 to generate distinct results.

Use the Excel unique and filter functions on a selected array to return unique values while ignoring blanks.

learn to obtain unique values with criteria in Excel by using the unique filter formula on grouped data, first for group a and then for group b.

Apply unique and filter functions to extract colors that meet multiple criteria, including group equals B and quantity greater than 5, using the include parameter.

Apply a simple max-based formula in Excel to keep a percentage between 0% and 100%, and see the resulting output in that range.

Learn to find the lowest n values in a data array using small, index, and match; fix references with F4 and drag to output the first, second, and third smallest.

Learn to compute first-in and last-out times in Excel for interviews and exam prep using minifs and maxifs with ranges.

Learn to extract the largest three values from group A using a dynamic formula with large and filter in Excel.

Learn how to compute the larger of two values in Excel using the MAX function with a simple formula.

Learn to calculate the maximum value by month in Excel using a maxifs formula with month criteria and edate for date adjustments, and view the monthly outputs.

Compute the maximum value of every 3rd column in Excel using a max filter formula with mod, sequence, and columns. The example shows the resulting maximums for every 3rd column.

Learn to compute the maximum value in a data set while ignoring errors by using the aggregate function with max and ignore error values on an array.

learn how to find the max value for a specific weekday in Excel by using filter and max together with text formatting to match Thursday, yielding 95.

Learn to compute max and min values for green color using the max function with index and match for exact lookup, producing 93 and 16.

learn to compute the maximum value with multiple criteria in excel by filtering group a where temp is under 73, using a max formula, yielding 101.

Apply minifs to find the minimum value for Group A where temp is greater than 72, using multiple criteria ranges and criteria.

Practice using the minifs function to find the minimum value for each group by specifying the minimum range, criteria ranges, and criteria.

Master case sensitive lookup in Excel with XLOOKUP using exact match to retrieve the correct age for a name.

Learn to find the closest match in Excel using an XLOOKUP formula, including abs handling and exact or next larger item options, with a practical example.

Identifies the longest string in a list using a filter and a length function, for interview and exam prep.

Apply xlookup with max and length to find the longest name where the group equals a, returning Jonathan as shown.

Identify missing values in an invoice range using a simple formula by comparing the range to a criterion and returning the missing entries.

Learn to retrieve the address of a lookup result in Excel by building an XLOOKUP formula with the lookup value, lookup array, and return array.

Master extracting the first matching cell contents using an XLOOKUP formula in Excel, with a lookup array, search text, and a not found handling of NA, demonstrated with dragging down.

Learn to obtain the first non-blank value in a list using an xlookup formula with not is blank.

Extract the first numeric value from a range in excel using xlookup(true, isnumber(range), range) or using index(range, match(true, isnumber(range), 0)).

Explore how to get the first text value in a row using hlookup with a wildcard, and reproduce the same result with xlookup and match, demonstrating exact-match lookup.

Learn to retrieve the last match for a name using xlookup in excel, applying a last-to-first search to return the correct Joan row and handle missing data.

Extract the last word from each sentence that matches a list using xlookup, handle not found cases, and apply these techniques in Excel practice tests.

Extract the nth match for a product in Excel by using index and filter formulas, selecting the third occurrence of product A.

Learn to get the date and price from the last non-empty cell in a column using a lookup formula that tests for non-blanks and returns the corresponding results.

Use index and match to perform an approximate lookup in Excel, returning C for scores between 70 and 80.

use index and match to retrieve a cost from data sorted in descending order with a greater-than criterion, returning 383 for a value between 200 and 300.

Apply index and match across multiple columns to group names, using a multi-column lookup with mmult and transpose to return the desired results.

Demonstrate left lookup with VLOOKUP by building a two-column array and returning ratings beside scores, overcoming VLOOKUP's right-only limitation.

Construct an x lookup formula to lookup the entire column for quarter 3, using the specified lookup and return arrays, and confirm the output.

Use xlookup to look up an entire row, selecting the lookup value, lookup array, and return array to retrieve row data for the project Neptune.

Learn to look up and sum a column for the fruit peers in Excel using an index and match based sum formula, with practical practice for interviews and exams.

Use an xlookup to find the first negative value in your data by filtering the lookup array to values less than zero and returning the corresponding result.

Demonstrates how to lookup the most recent file version in a data set using an Excel formula that combines lookup, isnumber, and find functions, returning A-004 as the latest version.

Learn to look up the lowest value in a data range using an index and match formula to return the contractor name for the minimum bid value with exact match.

Learn to find the first error in a data range using the excel formula match(true, iserror(range), 0) for interviews and exam preparation.

Apply index and match to retrieve the next highest value from a data set using lookup value, lookup array, and plus one. See how 2100 points yield 3000 for gold.

Master multiple chained VLOOKUPs in Excel by nesting IFERROR with successive VLOOKUP calls to retrieve the value for a given ID, such as 7, returning orange.

Demonstrate creating a comma separated list of multiple matches with a text join formula, using a comma space delimiter, ignore empty cells, and an if condition for group results.

Learn to use Excel formulas to place multiple matches into separate columns by applying transpose and a filter to group data A, B, C, D and output the corresponding names.

Learn to extract multiple matches into separate rows using the filter formula to pull names by group, and extend the output across columns.

Perform a partial numeric match with wildcards using the Excel MATCH function. See how 99 in a range yields its position as 7 with an exact match value of 0.

Learn how to use VLOOKUP with wildcard to retrieve first names, last names, extensions, and departments from a data table, using exact match and specific column indices.

learn how to find the position of the first partial match for apple in excel using a simple formula, with an output of four in the example.

Learn how to find the position of the maximum price in a list using an Excel formula with match for exact lookup, demonstrated on price data.

Learn how to compute quantity based discounts in Excel using xlookup to fetch discount percentages, then calculate total discount as quantity times price times (1 minus discount).

Learn to use a self-contained Vlookup to map score ranges to letter grades, using a threshold array and approximate match, demonstrated with 88 yielding a B.

Master advanced vlookup with multiple criteria using a choose array to match hoodie, size large, color red, and retrieve the corresponding price.

Apply the unique function to a data range to extract unique values, by selecting the array and pressing enter to view the output.

The lesson demonstrates a two way lookup with index and match to retrieve cost by width and height, guiding the use of lookup arrays and cost data.