All you need to know
Q: How do you use the VLOOKUP function?
A: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) looks for a value in the first column of a table and returns a value in the same row from a specified column.
Q: What does the IF function do?
A: IF(logical_test, value_if_true, value_if_false) checks whether a condition is met, and returns one value if true, and another value if false.
Q: How do you remove duplicates from a dataset?
A: Go to Data > Remove Duplicates.
Q: How can you combine text from multiple cells?
A: Use the CONCATENATE function or the & operator, e.g., =A1 & B1 or =CONCATENATE(A1, B1).
Q: What is the SUMIF function used for?
A: SUMIF(range, criteria, [sum_range]) sums the values in a range that meet a specified criterion.
Q: Explain how to create a pivot table.
A: Select your data range, go to Insert > PivotTable, choose your data source, and place it in a new or existing worksheet.
Q: How do you apply conditional formatting?
A: Select the cells, go to Home > Conditional Formatting, and choose a rule.
Q: What is the purpose of the INDEX function?
A: INDEX(array, row_num, [column_num]) returns the value of an element in a table or an array, selected by the row and column number indexes.
Q: How can you protect a worksheet?
A: Go to Review > Protect Sheet, set a password, and select the actions you want to allow.
Q: How do you create a drop-down list?
A: Use Data > Data Validation, choose List, and specify the source range.
Q: What does the MATCH function do?
A: MATCH(lookup_value, lookup_array, [match_type]) searches for a specified item in a range of cells, and then returns the relative position of that item.
Q: How do you freeze panes?
A: Select the row below the row(s) you want to freeze and go to View > Freeze Panes.
Q: What is the TEXT function used for?
A: TEXT(value, format_text) converts a value to text in a specified number format.
Q: How do you create a chart?
A: Select the data range, go to Insert > Chart, and choose the chart type.
Q: Explain how to use SUMPRODUCT.
A: SUMPRODUCT(array1, [array2], [array3], ...) multiplies corresponding components in the given arrays and returns the sum of those products.
Q: How do you link cells from different worksheets?
A: Type = in the destination cell, navigate to the source cell, and press Enter.
Q: What is the OFFSET function?
A: OFFSET(reference, rows, cols, [height], [width]) returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.
Q: How do you perform a What-If analysis using Goal Seek?
A: Go to Data > What-If Analysis > Goal Seek, set the Set cell, To value, and By changing cell.
Q: What does the AND function do?
A: AND(logical1, [logical2], ...) returns TRUE if all arguments are TRUE.
Q: How can you use Data Validation to restrict cell input?
A: Go to Data > Data Validation, set the criteria (e.g., whole number, list, date).
Q: How do you add a trendline to a chart?
A: Click the chart, go to Chart Tools > Design > Add Chart Element > Trendline.
Q: Explain the use of the OR function.
A: OR(logical1, [logical2], ...) returns TRUE if any argument is TRUE.
Q: How do you use the LEFT function?
A: LEFT(text, [num_chars]) returns the specified number of characters from the start of a text string.
Q: What is the RIGHT function used for?
A: RIGHT(text, [num_chars]) returns the specified number of characters from the end of a text string.
Q: How do you use the MID function?
A: MID(text, start_num, num_chars) returns a specific number of characters from a text string, starting at the position you specify.
Q: How do you perform a What-If analysis using Scenario Manager?
A: Go to Data > What-If Analysis > Scenario Manager, add different scenarios, and set values.
Q: What is the LEN function used for?
A: LEN(text) returns the number of characters in a text string.
Q: How do you create a dynamic named range?
A: Use Formulas > Name Manager, create a new name, and use a formula like =OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1).
Q: What is the NETWORKDAYS function?
A: NETWORKDAYS(start_date, end_date, [holidays]) returns the number of whole working days between two dates.
Q: How can you apply a filter to your data?
A: Select your data range and go to Data > Filter.
Q: How do you create a macro?
A: Go to View > Macros > Record Macro, perform the actions, and then stop recording.
Q: Explain the HLOOKUP function.
A: HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) looks for a value in the top row of a table and returns a value in the same column from a specified row.
Q: What does the COUNTA function do?
A: COUNTA(value1, [value2], ...) counts the number of cells that are not empty.
Q: How do you use the TRIM function?
A: TRIM(text) removes all spaces from text except for single spaces between words.
Q: How can you transpose data?
A: Copy the data, right-click the destination, and choose Transpose from the Paste Special options.
Q: What is the INDIRECT function?
A: INDIRECT(ref_text, [a1]) returns the reference specified by a text string.
Q: How do you create a custom number format?
A: Go to Home > Number group, click the dropdown, select More Number Formats, and create a custom format.
Q: How do you highlight cells with errors using conditional formatting?
A: Use Home > Conditional Formatting, create a new rule, and set Use a formula to determine which cells to format with =ISERROR(cell).
Q: What is the AVERAGEIF function?
A: AVERAGEIF(range, criteria, [average_range]) calculates the average of the cells that meet a specified criterion.
Q: How do you split text into columns?
A: Use Data > Text to Columns, choose the delimiter, and follow the steps in the wizard.
Q: What does the SUBTOTAL function do?
A: SUBTOTAL(function_num, ref1, [ref2], ...) returns a subtotal in a list or database.
Q: How can you apply a formula to an entire column?
A: Enter the formula in the first cell and double-click the fill handle or drag it down the column.
Q: How do you group data in a pivot table?
A: Right-click on the item to group, select Group, and specify the grouping criteria.
Q: What is the ISNUMBER function?
A: ISNUMBER(value) checks whether a value is a number and returns TRUE or FALSE.
Q: How do you lock cells to prevent editing?
A: Select the cells, right-click, choose Format Cells, go to the Protection tab, check Locked, and protect the sheet.
Q: What is the purpose of the DAYS function?
A: DAYS(end_date, start_date) returns the number of days between two dates.
Q: How do you use the XLOOKUP function?
A: XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) searches a range or array and returns an item corresponding to the first match it finds.
Q: How do you apply a custom view to a worksheet?
A: Go to View > Custom Views, add a new view, and save it with a name.
Q: What is the IFERROR function?
A: IFERROR(value, value_if_error) returns a value you specify if a formula evaluates to an error; otherwise, it returns the result of the formula.
Q: How do you calculate the IRR in Excel?
A: Use the IRR(values, [guess]) function to calculate the internal rate of return for a series of cash flows.
Q: How can you import data from a text file?
A: Go to Data > Get External Data > From Text, and follow the Text Import Wizard steps.
Q: How do you calculate the NPV in Excel?
A: Use the NPV(rate, value1, [value2], ...) function to calculate the net present value of an investment based on a series of periodic cash flows and a discount rate.
Q: What does the RAND function do?
A: RAND() returns a random number between 0 and 1.
Q: How do you perform a What-If analysis using Data Tables?
A: Go to Data > What-If Analysis > Data Table, set the row and column input cells, and fill in the data table.
Q: How can you merge cells?
A: Select the cells to merge and go to Home > Merge & Center.
Q: What is the DAYS360 function?
A: DAYS360(start_date, end_date, [method]) calculates the number of days between two dates based on a 360-day year.
Q: How do you use the EDATE function?
A: EDATE(start_date, months) returns the serial number of the date that is the indicated number of months before or after the start date.
Q: What is the EOMONTH function?
A: EOMONTH(start_date, months) returns the serial number of the last day of the month before or after a specified number of months.
Q: How do you hide and unhide columns?
A: Select the columns, right-click, and choose Hide or Unhide.
Q: How do you use the WORKDAY function?
A: WORKDAY(start_date, days, [holidays]) returns the serial number of the date before or after a specified number of workdays.
Q: What is the RANK function?
A: RANK(number, ref, [order]) returns the rank of a number in a list of numbers.
Q: How do you create a named range?
A: Select the range, go to Formulas > Define Name, and enter a name.
Q: What is the ISBLANK function?
A: ISBLANK(value) returns TRUE if the value is blank.
Q: How do you use the CHOOSE function?
A: CHOOSE(index_num, value1, [value2], ...) returns a value from a list of values based on the index number.
Q: How do you insert sparklines?
A: Select the cell range, go to Insert > Sparklines, choose the type, and set the data range.
Q: What is the ROUND function?
A: ROUND(number, num_digits) rounds a number to a specified number of digits.
Q: How do you apply data bars in conditional formatting?
A: Go to Home > Conditional Formatting, choose Data Bars, and select the style.
Q: How can you use the RANK.EQ function?
A: RANK.EQ(number, ref, [order]) returns the rank of a number in a list of numbers.
Q: What does the HYPERLINK function do?
A: HYPERLINK(link_location, [friendly_name]) creates a shortcut that opens a document stored on a network server, an intranet, or the Internet.
Q: How do you change the axis scale in a chart?
A: Right-click the axis, select Format Axis, and adjust the scale options.
Q: What is the LARGE function?
A: LARGE(array, k) returns the k-th largest value in a data set.
Q: How do you use the SMALL function?
A: SMALL(array, k) returns the k-th smallest value in a data set.
Q: How do you create a dynamic chart?
A: Use a dynamic named range as the chart source, defined by a formula like OFFSET or INDEX.
Q: What is the ISERROR function?
A: ISERROR(value) returns TRUE if the value is any error value.
Q: How do you apply icon sets in conditional formatting?
A: Go to Home > Conditional Formatting, choose Icon Sets, and select the style.
Q: How do you use the DSUM function?
A: DSUM(database, field, criteria) adds the numbers in the field column of records in the database that match the criteria.
Q: What is the TEXTJOIN function?
A: TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...) joins text from multiple ranges and/or strings, and includes a delimiter you specify between each text value.
Q: How do you use the DATE function?
A: DATE(year, month, day) returns the serial number of a particular date.
Q: What is the TIME function?
A: TIME(hour, minute, second) returns the decimal number for a particular time.
Q: How do you create a histogram?
A: Use the Histogram tool from the Data Analysis add-in or Insert > Chart > Histogram.
Q: How do you use the CONVERT function?
A: CONVERT(number, from_unit, to_unit) converts a number from one measurement system to another.
Q: What is the FIND function?
A: FIND(find_text, within_text, [start_num]) locates one text string within a second text string and returns the number of the starting position of the first text string from the first character of the second text string.
Q: How do you use the SEARCH function?
A: SEARCH(find_text, within_text, [start_num]) returns the number of the character at which a specific character or text string is first found, reading left to right.
Q: What is the SUBSTITUTE function?
A: SUBSTITUTE(text, old_text, new_text, [instance_num]) substitutes new_text for old_text in a text string.
Q: How do you use the REPLACE function?
A: REPLACE(old_text, start_num, num_chars, new_text) replaces part of a text string with a different text string.
Q: What is the CEILING function?
A: CEILING(number, significance) rounds a number up, away from zero, to the nearest multiple of significance.
Q: How do you use the FLOOR function?
A: FLOOR(number, significance) rounds a number down, toward zero, to the nearest multiple of significance.
Q: What is the PMT function?
A: PMT(rate, nper, pv, [fv], [type]) calculates the payment for a loan based on constant payments and a constant interest rate.
Q: How do you use the IPMT function?
A: IPMT(rate, per, nper, pv, [fv], [type]) returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.
Q: What is the PPMT function?
A: PPMT(rate, per, nper, pv, [fv], [type]) returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.
Q: How do you use the FV function?
A: FV(rate, nper, pmt, [pv], [type]) returns the future value of an investment based on periodic, constant payments and a constant interest rate.
Q: What is the SLN function?
A: SLN(cost, salvage, life) calculates the straight-line depreciation of an asset for one period.
Q: How do you use the SYD function?
A: SYD(cost, salvage, life, per) calculates the sum-of-years' digits depreciation of an asset for a specified period.
Q: What is the DDB function?
A: DDB(cost, salvage, life, period, [factor]) calculates the depreciation of an asset for a specified period using the double-declining balance method or another method you specify.
Q: How do you use the AMORDEGRC function?
A: AMORDEGRC(cost, date_purchased, first_period, salvage, period, rate, [basis]) returns the depreciation for each accounting period.
Q: What is the AMORLINC function?
A: AMORLINC(cost, date_purchased, first_period, salvage, period, rate, [basis]) returns the depreciation for each accounting period using a depreciation coefficient.
Q: How do you create a combo chart?
A: Select the data, go to Insert > Combo Chart, and choose the desired chart type combination.
Q: How do you apply slicers to a pivot table?
A: Select the pivot table, go to Analyze > Insert Slicer, and choose the fields for the slicer.
Q: What is the AGGREGATE function?
A: AGGREGATE(function_num, options, ref1, [ref2], ...) returns an aggregate in a list or database while optionally ignoring hidden rows and errors.
Q: How do you use the FORECAST function?
A: FORECAST(x, known_y's, known_x's) returns a predicted value based on existing values using linear regression.