Excel Flashcards
Final Exam Review
=Sum(range)
Adds the numbers in cells. Example: =SUM(A1:A5)
=SUMPRODUCT
Multiplies corresponding cells from two ranges and adds the results. Example: =SUMPRODUCT(A1:A3, B1:B3):
=SUMIF(A1>10, “Yes” , “No”)
The SUMIFS function adds values that meet multiple conditions. Returns “Yes” if A1 is greater than 10, otherwise returns “No”. Example: A1=12 → returns “Yes”
=VLOOKUP(101, A2:B5, 2, FALSE)
Looks up the value 101 in the first column of the range A2:B5 and returns the corresponding value from the second column. Example: If 101 maps to “Apple”, the result is “Apple”.
=HLOOKUP()
The HLOOKUP function in Excel is used to search for a value in the top row of a table or array and then return a value in the same column from a specified row. It’s particularly useful when you have data arranged in a horizontal format (rows and columns) and you need to look up values horizontally
=XLOOKUP()
XLOOKUP is a relatively new function introduced in Excel that provides enhanced lookup capabilities compared to traditional lookup functions like VLOOKUP and HLOOKUP. XLOOKUP allows you to search
for a value in a range or array and return a corresponding value from the same position in a different range or array
=UNIQUE()
The UNIQUE function returns a list of unique values from a range or array. It filters out duplicate values, leaving only one occurrence of each unique value. This function is particularly useful for data analysis, as it allows you to easily identify and work with distinct values within a dataset
=TRANSPOSE()
The TRANSPOSE function in Excel converts rows into columns and vice versa. It is useful when reorganizing data for better readability or analysis
=FILTER()
The FILTER function in Excel allows you to extract a subset of data from a range based on specific criteria or conditions. It returns an array of values that meet the given conditions. This function is particularly useful for data analysis and manipulation tasks, as it helps in filtering and extracting data dynamically
=SORT()
The SORT function is used to sort the contents of a range or array based on the values within it. It returns a sorted array without modifying the original data. This function is particularly useful for organizing and analyzing data in a structured manner
Array Formula
An array formula in Excel is a formula that can perform multiple calculations on one or more items in an array. Unlike regular formulas that operate on a single cell at a time, array formulas can operate on multiple cells simultaneously, returning either a single result or an array of results.
Goal Seek
Goal Seek is a tool that finds an input value needed to reach a desired output by adjusting a single variable in a formula. It’s useful for financial forecasting and break-even analysis
FV()
The FV (Future Value) function calculates the future value of an investment based on periodic payments and interest rates.
Excel tabs
Tabs appear at the bottom of the Excel window and allow you to navigate between different worksheets within the same Excel workbook. Excel allows you to have multiple sheets within a single workbook, making it easy to organize and manage different sets of data or calculations.
References
Absolute
Relative
Mixed
Absolute($A$1)
Fixed cell reference. Always refers to cell A1, even when copied
Relative(A1)
A dynamic cell reference. Changes depending on where it is copied
Mixed($A1 or A$1)
Partially absolute and partially relative. Locks either the row or column
Two Methods to Use Data from another worksheet
Directly Typing the Reference
Using the Selection Window (Like in Data Validation, insert graph)
