Red<\/a><\/p>\n\n\n\nMost Common Excel Errors<\/p>\n\n\n\n
DIV\/0! Divided by zero<\/h1>\n\n\n\nREF! Referencing a previously deleted cell<\/h1>\n\n\n\nNUM! Number not valid<\/h1>\n\n\n\nNAME? Text not valid (ex. incorrect function name)<\/h1>\n\n\n\nVALUE! Incorrect arguments (ex. Using text as a number)<\/h1>\n\n\n\n# Column not wide enough<\/h6>\n\n\n\n
Find and Replace
Ctrl+F<\/p>\n\n\n\n
Write what you want to find<\/p>\n\n\n\n
Click Replace<\/p>\n\n\n\n
Write what you want to replace<\/p>\n\n\n\n
Use Options to restrict where you’re searching, match the case, etc.<\/p>\n\n\n\n
Note: You can replace references in formulas as well by finding “[cell name]” and replacing with “[new cell name]”; Ex. Find “j7” and replace with “b2”<\/p>\n\n\n\n
Preparing a Page for Printing
(1) Go to Page Layout > Page Setup<\/p>\n\n\n\n
(2) Change to Fit to 1 pages wide by [however many you want] pages tall or change from landscape to portrait<\/p>\n\n\n\n
(3) Change the margins and center on page horizontally and vertically in the Margins tab<\/p>\n\n\n\n
(4) Add custom headers and footers in the Header\/Footer tab (page numbers, date, timestamp, file name, etc.)<\/p>\n\n\n\n
(5) Limit the print area in the Sheet tab, or add rows to repeat at the top of each page<\/p>\n\n\n\n
What are all the ways to trace precedent cells?
Ctrl+[
Alt+M+P
Alt+T+U+T<\/p>\n\n\n\n
Create a Dropdown Menu Command
Alt+D+L<\/p>\n\n\n\n
Change Text Color
Alt+H+F+C<\/p>\n\n\n\n
Change the Cell to a Number Format
Ctrl+Shift+1<\/p>\n\n\n\n
Functions
Pre-built combinations of operations in Excel that facilitate spreadsheet analysis<\/p>\n\n\n\n
Structure of Functions
Every function starts with an “=” sign, then the function name, and then the cell range (bound with parentheses)
All functions follow the same syntax:=functionname(argument1,argument2,\u2026,argumentx)
Within the parentheses, functions can have 0, 1, or many arguments, separated by commas<\/p>\n\n\n\n
Now Function
=NOW()
Spits out the current time and date<\/p>\n\n\n\n
SUM Function
In the SUM function =SUM(A1:A10), there is only one argument, which is telling Excel the specific cell range that needs to be summed up<\/p>\n\n\n\n
SUM Function Autosum Command
Alt+=<\/p>\n\n\n\n
(Cmnd+Shift+T)<\/p>\n\n\n\n
Average Function
=Average([number 1],\u2026,[number x])
Gives an average of the range<\/p>\n\n\n\n
Logical Functions: IF
Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE
Use IF to conduct conditional tests on values and formulas
All IF statements follow the same structure =IF(x, y, z), where\u2026 (see picture)
Text output is designated by quotation marks around the outputs<\/p>\n\n\n\n
Greater Than or Equal to Functions
<=<\/p>\n\n\n\n
Nested IF statements
Generally follows the structure:<\/p>\n\n\n\n
=IF([First Criteria for Yes], =IF([Second Criteria for Yes],”Yes”,”No”),”No”)<\/p>\n\n\n\n
OR<\/p>\n\n\n\n
=IFS(Criteria 1, Value if Criteria 1 is True, Criteria 2, Value if Criteria is True, \u2026)<\/p>\n\n\n\n
Note: There’s no longer a value if false with the IFS statement; However, you can make a criteria TRUE, which becomes the if false argument<\/p>\n\n\n\n
Error-Trapping Function IFERROR
=IFERROR(value, value_if_error) returns a value you specify if a formula evaluates to an error
If the formula does not result in an error, IFERROR returns the result of the formula<\/p>\n\n\n\n
Creating Dynamic Headers by Combining Cell References with Text (“&”)
Excel allows users to combine (or “concatenate”) cells with a text string in them with other text strings, creating one text string by using the “&” function<\/p>\n\n\n\n
Ex. =”Income Statement for “&A1, where A1 is the company name<\/p>\n\n\n\n
Date Functions (EOMONTH)
=EOMONTH(start_date,months) allows you to create monthly date headers by outputting the last day of a specified month<\/p>\n\n\n\n
start_date represents a starting date reference<\/p>\n\n\n\n
months represents x number of months before or after the start_date<\/p>\n\n\n\n
Note: To output a date x months before a start_date, x should be negative<\/p>\n\n\n\n
EDATE Date Functions
=EDATE(start_date, months) is a similar function to EOMONTH
However, EDATE returns the exact date, x months from the start date<\/p>\n\n\n\n
Dates in Excel as Serial Numbers
When using dates in general in Excel, understand that Excel stores dates as serial numbers – they must be formatted as dates (use Ctrl+1) in. order to look like recognizable dates to the user<\/p>\n\n\n\n
Using IF statements to see whether a cell has anything in it
You can use IF statements as a test of whether a cell has anything in it
For example, =IF(C1,C2,C3) would see if there is anything in cell C1; if there was, it would output C2, otherwise C3<\/p>\n\n\n\n
ISNUMBER and ISTEXT Functions
Functions typically embedded within an IF statement that test whether there is a number (ISNUMBER) or text (ISTEXT) inside a cell
Ex. =IF(ISTEXT(A1),”Error”,A1\/A3)<\/p>\n\n\n\n
YEARFRAC
YEARFRAC(start_date, end_date, basis) returns the proportion of the year between two given dates, the start_date and end_date
Basis is an optional parameter
Useful in financial modeling when projecting future cash flows or obligations for a fraction of a year
We often term the fraction of a year a “Stub Year Fraction”<\/p>\n\n\n\n
DATE, DAY, MONTH, and YEAR Functions
DATE(year,month,day) is a function that combines distinct year, month, and day elements into a valid date function in Excel
Combining this function with DAY(serial number), MONTH(serial number), and YEAR(serial number) is sometimes useful for creating date functions out of disparate data<\/p>\n\n\n\n
Ex. =DATE(YEAR(C4+1),MONTH(C4),DAY(C4))<\/p>\n\n\n\n
AND Function
=AND(logical1,logical2, \u2026) evaluates to true if all its arguments are true; false if one or more argument is false<\/p>\n\n\n\n
OR Function
=OR(logical1,logical2,\u2026) evaluates to true if at least one argument is true<\/p>\n\n\n\n
HLOOKUP
=HLOOKUP(lookup value, table range, row number) searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array
Use this when your comparison values are located in a row across the top of a table of data and you want to look down a specified number of rows<\/p>\n\n\n\n
VLOOKUP
=VLOOKUP(lookup value, table range, column number) searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table or array<\/p>\n\n\n\n
Use this when your comparison values are located in a column to the left of a table of data, and you want to look across (and to the right) a specified number of columns<\/p>\n\n\n\n
If looking up text, add a 0 as the [range lookup] value for the fourth argument<\/p>\n\n\n\n
Range Lookup<\/p>\n\n\n\n
INDEX
=INDEX(Array, Row Number, Column Number)
Selects a value from an array of values with the appropriate row number and column number of the array<\/p>\n\n\n\n
CHOOSE
=CHOOSE(Index Number, Value 1, Value 2, \u2026)
Selects a number of values (the index number) out of a list of delineated values<\/p>\n\n\n\n
OFFSET
=OFFSET(Reference, Rows, Columns, [Height], [Width])
Define a reference point (the top left corner); this function spits out a result that is x rows below and y columns to the right of the reference point<\/p>\n\n\n\n
Common Errors with HLOOKUP, VLOOKUP, CHOOSE, OFFSET, & INDEX
If you add a row into a table, the HLOOKUP, INDEX, and OFFSET are instantly screwed up, as they depend on the rows
If you add a column into a table, the INDEX, VLOOKUP, and OFFSET are messed up<\/p>\n\n\n\n
MATCH Function
The MATCH function returns the relative position (number) of an item in an array that matches specified lookup value<\/p>\n\n\n\n
Syntax: =MATCH(lookup_value,lookup_array,match_type)<\/p>\n\n\n\n
It does NOT return the value within the cell itself (as opposed to the HLOOKUP and VLOOKUP functions)<\/p>\n\n\n\n
Match type is an exact match (0), greater than (-1), less than (1) — we only really use 0<\/p>\n\n\n\n
Combining MATCH Function with Lookup & Reference Functions
Combining MATCH with functions like HLOOKUP, VLOOKUP, OFFSET, INDEX, and CHOOSE makes formulas more durable and dynamic
See Lookup & Reference sheet on Practice Sheet for examples<\/p>\n\n\n\n
INDIRECT
=INDIRECT(reference text) returns the reference specified by a text string
=INDIRECT(“B4”) will output the value of what is in cell B4
The most common way to get value out of this function is to combine with concatenate (&)
Ex. When creating a model for a flexible user defined start and end date for calculating a cumulative EBITDA result, you can use INDIRECT and &<\/p>\n\n\n\n
Data Validation & Creating Drop-Down Menus
A utility in Excel whose most frequently used feature is its ability to create simple and quick drop-down menus<\/p>\n\n\n\n
(1) To create a dropdown menu, with the cell where you want your drop-down menu active, open the data validation form Alt+D+L or Alt+A+V+V (Data>Data Validation)<\/p>\n\n\n\n
(2) Within the Settings tab, select list from the dropdown menu<\/p>\n\n\n\n
(3) Within the ‘Source:’ field, identify a contiguous cell range containing the data you want to include in your dropdown, and hit OK and you should see your dropdown menu appear (note: it only appears when you are on the active cell)<\/p>\n\n\n\n
Combining INDIRECT with MATCH
To combine INDIRECT with MATCH, you can use INDIRECT’s second argument: TRUE is in the form A1, FALSE is in the form R1C1, or row 1 column 1<\/p>\n\n\n\n
Ex. =INDIRECT(“R1C1”,FALSE)<\/p>\n\n\n\n
Now, you can use INDIRECT, MATCH, and & to create a dynamic function<\/p>\n\n\n\n
Note: With INDIRECT, you have to start the MATCH array from the very edge of the worksheet (first column and first row) for the correct row and column number; or, you can add the number of rows above or columns to the left of the beginning of your array to the MATCH function<\/p>\n\n\n\n
The Address Function
=ADDRESS(row_number,column_number)
With INDIRECT and MATCH, you can dynamically find a value in a table using the ADDRESS function (see picture)
Also makes working between worksheets easier — add the relevant sheet name in quotations with =ADDRESS(row_number,column_number,,,sheet_name)
Ex. =ADDRESS(1,2,,,”Sheet1″) for R1C1 of Sheet1<\/p>\n\n\n\n
COLUMN and ROW Functions
=COLUMN() gives you the current column and =ROW() gives you the current row
=COLUMN(reference) and =ROW(reference) gives you the column and row of a reference point
=COLUMNS(array) and =ROWS(array) gives you the number of columns and rows in an array<\/p>\n\n\n\n
Using COLUMN and ROW Functions as Counters in Complex Formulas
Use COLUMNS(array) and ROWS(array) to act as counters when using INDIRECT w\/ MATCH to create a dynamic counter in the function<\/p>\n\n\n\n
Evaluate
Ctrl +<\/p>\n\n\n\n
Data Tables
Allow us to examine a piece of output data – such as a company’s EPS – and how it is impact by changes in input variables such as revenues and gross margin assumptions
Output the results in a presentation-friendly matrix
Often used for sensitivity analysis (i.e. EPS’s sensitivity to changes in gross profit margin) and is used widely by analysts to illustrate a range of possible output values<\/p>\n\n\n\n
Building a Vertical Data Table
Layout assumptions on the LEFT<\/p>\n\n\n\n
(1) Identify the output variable<\/p>\n\n\n\n
The variable you are trying to sensitize is the output variable<\/p>\n\n\n\n
Must be referenced from your analysis into the top right corner of the data table<\/p>\n\n\n\n
(2) Hard-code the input variable sensitivities<\/p>\n\n\n\n
The variables whose impact on the output variables you want to analyze are the input variables<\/p>\n\n\n\n
Input variable assumptions should not be referenced from the analysis, but rather be hard-coded and arranged in the column to the left of the output variable<\/p>\n\n\n\n
(3) Run the data table<\/p>\n\n\n\n
Hit Alt+D+T (Data>Table\u2026) to access the Data Table dialog<\/p>\n\n\n\n
Row Input Cell: Not needed for vertical tables<\/p>\n\n\n\n
Column Input Cell: Reference the input variable from the model<\/p>\n\n\n\n
Highlight the entire range (including the output variable) and hit OK when done – the data table should populate<\/p>\n\n\n\n
You may need to hit F9 if Excel is set to “manual” or “automatic calculations except for data tables”<\/p>\n\n\n\n
Important: Data tables must always be in the same worksheet as the input variables<\/p>\n\n\n\n
Get inside a drop-down list
Alt+Up\/DownArrow (Option+Up\/DownArrow)<\/p>\n\n\n\n
Building a Horizontal Data Table
From a substance standpoint, it’s the same as vertical<\/p>\n\n\n\n
(1) Referenced output variable from your analysis into the bottom left corner of the data table<\/p>\n\n\n\n
(2) Input the input assumptions in the row above and one cell to the right of the output reference<\/p>\n\n\n\n
(3) Highlight the entire range (including the output variable) and hit Alt+D+T; the Data Table dialog will appear<\/p>\n\n\n\n
Row Input Cell: Reference the input variable from the model<\/p>\n\n\n\n
Column Input Cell: Not needed<\/p>\n\n\n\n
Hit OK when done – the data table should population; if not, hit F9<\/p>\n\n\n\n
Building a Two-Sided Data Table
Same as vertical data table, but allows for 2 inputs instead of one<\/p>\n\n\n\n
Output variable must be referenced from the model into the top left corner of the data table<\/p>\n\n\n\n
Get Rid of Borders
Ctrl+Shift+-<\/p>\n\n\n\n
When Data Tables Fail: Self Referencing IF Statement
When a table if on a separate worksheet, it doesn’t work<\/p>\n\n\n\n
To solve this, you can use a self referencing IF statement<\/p>\n\n\n\n
Using IF and AND, you can create a self referencing IF table where you manipulate the original data to populate the table with the appropriate values<\/p>\n\n\n\n
Ex. See picture and sheet “Self Referencing IFs” in Practice Workbook<\/p>\n\n\n\n
Note: In order for this to work, iterations must be turned on and you cannot go into the cells once you populate the table<\/p>\n\n\n\n
XLOOKUP
=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],\u2026)<\/p>\n\n\n\n
Allows you to choose the lookup value (what you want to look for), the array the lookup is located in, and the corresponding array the return value should be located in<\/p>\n\n\n\n
Also allows you to return an IFERROR message if not found<\/p>\n\n\n\n
Note: Does not break if columns are added by decoupling the lookup array and the return array, like OFFSET MATCH function but simpler<\/p>\n\n\n\n
Nested (Two-Way) XLOOKUP
If you are finding a result from two different drop-down menus, you can use a nested XLOOKUP to get a dynamic function with two variables
Ex. =XLOOKUP(variable1,variable1_array,XLOOKUP(variable2,variable2_array,wholetable))
(See XLOOKUP workbook)<\/p>\n\n\n\n
Using XLOOKUP to Generate Multiple Values
If you set up your search returns as the same number of columns as the data table and then set up the return array in the XLOOKUP function as the full data table, it will automatically occupy the full search returns<\/p>\n\n\n\n
Scenario Analysis Using XLOOKUP
You can use XLOOKUP’s ability to generate multiple values to create a scenario analysis for something like an income statement<\/p>\n\n\n\n
Where XLOOKUP Loses to INDEX MATCH
When trying to create a master formula that can be copied across an entire range, XLOOKUP loses to INDEX MATCH or INDEX XMATCH XMATCH
(See last worksheet of XLOOKUP workbook for example)<\/p>\n\n\n\n
XMATCH
=XMATCH(lookup_value,lookup_array) only gives you an exact match, so you only have to define two arguments instead of three!
Use this instead of MATCH<\/p>\n\n\n\n
SUMPRODUCT
=SUMPRODUCT(array1,array2,array3,\u2026) multiples corresponding components in two or more arrays and returns the sum of those products
A lesser known features is the ability to embed criteria directly into the arrays<\/p>\n\n\n\n
Booleans in Excel
When Excel spits out a TRUE or FALSE (see picture), you can convert them respectively into 1 or 0 by applying any operator on them
Interestingly, multiplying a TRUE (or FALSE) by another TRUE (or FALSE) also has the effect of converting it into a 1 or 0, respectively<\/p>\n\n\n\n
SUMPRODUCT with Embedded Criteria
A lesser known feature of SUMPRODUCT is the ability to embed criteria directly into the arrays<\/p>\n\n\n\n
For example, we can directly calculate proceeds on options that have an exercise price less than the share price so you no longer need to calculate option proceeds for each tranche:<\/p>\n\n\n\n
We have 2 criteria — the options # and the exercise price per tranche, which is multiplied against a TRUE or FALSE criteria for each exercise tranche<\/p>\n\n\n\n
Tranche 1 evaluates to TRUE, so Excel multiplies the TRUE by the Tranche 1 exercise price, and then by the # of options<\/p>\n\n\n\n
Tranche 2 & 3 evaluate to FALSE, and become 0 when multiplied by the exercise prices<\/p>\n\n\n\n
Note: See MATH sheet of Excel Practice workbook for more examples<\/p>\n\n\n\n
When Are Options Exercised?
When their exercise price is less than the strike price (they’re “in the money”)<\/p>\n\n\n\n
SUMIF
=SUMIF(range, criteria, sum range) adds the cells specified by a given criteria
The range is the range that you want to evaluate with the criteria, whereas the sum range is what is actually summed
Criteria can either be hardcoded which requires quotation marks as you see in the picture, or a direct cell reference (which would not need quotes around it)<\/p>\n\n\n\n
SUMIFS
=SUMIFS(range1, criteria1, sum range1, range2, criteria2, sum range2, etc.)
Same as SUMIF but can handle multiple criteria and sum ranges<\/p>\n\n\n\n
AVERAGEIF and AVERAGEIFS
Identical to SUMIF and SUMIFS but instead of summing, this function averages the data in the range<\/p>\n\n\n\n
Absolute Value
=ABS(cell or number) gives you the absolute value<\/p>\n\n\n\n
Ceiling
=CEILING(number, significance)
Rounds up to the nearest x amount with a certain level of significance
Ex. If you want it rounded up to the nearest 10th, the significance = 0.1<\/p>\n\n\n\n
Floor
=FLOOR(number, significance)
Rounds down to the nearest x amount with a certain level of significance
Ex. If you want it rounded down to the nearest 10th, the significance = 0.1<\/p>\n\n\n\n
Combinations Function
=COMBIN(number, number chosen)<\/p>\n\n\n\n
Ex. =COMBIN(4, 2) gives you the number of two person combinations out of a number of 4 people<\/p>\n\n\n\n
Note: Given the useful life of an asset, you can find the return sum of years’ digits with =COMBIN(useful life + 1,2)<\/p>\n\n\n\n
Round Functions
=ROUND(number,number of digits) rounds the number to the specified number of decimal places
=ROUNDUP rounds up
=ROUNDDOWN rounds down<\/p>\n\n\n\n
MIN Function
=MIN(number 1, number 2, \u2026) returns the smallest number in a specified set of values<\/p>\n\n\n\n
MAX Function
=MAX(number 1, number 2, \u2026) returns the largest number in a specified set of values
A classic use in financial modeling is to use a max function to prevent a revolving credit line balance from dipping below 0 when there is a cash shortfall<\/p>\n\n\n\n
COUNT, COUNTA, and COUNTIF Functions
COUNT =COUNT(value1, value2, \u2026) counts the number of cells that contain numbers within the list of arguments; cells with text are disregarded<\/p>\n\n\n\n
COUNTA Same as COUNT except cells with numbers and text are counted<\/p>\n\n\n\n
COUNTIF =COUNTIF(range, criteria) counts the number of items in the range that satisfy a specific criteria – similar to the SUMIF function<\/p>\n\n\n\n
COUNTIF Syntax
If you want the criteria to be “equals a cell”, just input the cell number
If you want it to be less than or greater than, use quotation marks and &:
Ex. =COUNTIF(A1, “<“&A2)<\/p>\n\n\n\n
PV Function
Returns the present value of a series of future payments<\/p>\n\n\n\n
Syntax: =PV(rate, nper, pmt, fv, type), where\u2026<\/p>\n\n\n\n
Note: Rate represents the rate per period<\/p>\n\n\n\n
If the future value is 0, then omit the fv argument<\/p>\n\n\n\n
FV Function
Returns the future value of an investment based on constant payment and interest rate<\/p>\n\n\n\n
Syntax: =FV(rate, nper, pmt, pv, type), where\u2026<\/p>\n\n\n\n
NPV Function
=NPV(rate,value1,value2,\u2026) returns the net present value of an investment based on a discount rate and a series of future payments (negative values) and income (positive values)
Values are assumed to occur at the end of each time period and must be referenced in the order in which they occur<\/p>\n\n\n\n
NPV vs. PV Functions
PV assumes constant payments, while NPV cash flows can vary from period to period
NPV does not require user to explicitly identify number of periods and simply assumes equal periods based on the number of values
NPV assumes payments occur at the end of the period (but the formula can be adjusted to simulate payments occurring at beginning of period)<\/p>\n\n\n\n
XNPV Function
An improvement on NPV for when the timing of cash flows is uneven
=XNPV(rate, values, dates) returns the net present value for a set of cash flows that do not necessarily occur at equal time intervals
Unlike NPV, the first cash flow is not discounted, while dates of subsequent cash flows can be included in the formula in any order<\/p>\n\n\n\n
Hacking NPV to Calculate Beginning of Period
Add the first payment and then take the NPV of the remaining payments
Ex. =1000+NPV(10%,1000,1000,1000) for four payments BOP<\/p>\n\n\n\n
IRR Function
=IRR(values, guess) returns the IRR for a series of values
IRR is the rate corresponding to an NPV of 0
While values do not have to be identical each period, the periods are assumed to be equally far apart
‘Guess is an optional argument; Excel needs a starting point to iterate to the right IRR. If you choose not to input a “guess”, Excel will automatically use 10% as the guess<\/p>\n\n\n\n
XIRR Function
=XIRR(values, dates, guess) returns the IRR for a series of values which may not be periodic
The date of each cash flow must be referenced in the formula (the “dates”)<\/p>\n\n\n\n
Text Functions
For professionals that have to work extracting data from large inconsistent and poorly formatted data sets, text functions can be absolutely critical
We will cover:<\/p>\n\n\n\n
LEN
Identifies the length of the string of text or numbers
=LEN(text)<\/p>\n\n\n\n
LEFT
=LEFT(text, number of characters)
Inputs the first however many characters of a string<\/p>\n\n\n\n
RIGHT
=RIGHT(text, number of characters)
Inputs the last however many characters of a string<\/p>\n\n\n\n
MID
=MID(text,start number,number of characters)
Starts with the start number on the string and then outputs the corresponding number of characters starting at that number<\/p>\n\n\n\n
PROPER
=PROPER(text)
Converts strings into the proper\/upper case<\/p>\n\n\n\n
UPPER & LOWER
=UPPER(text)
Converts strings into entirely upper case letters
= LOWER(text)
Converts strings into entirely lower case letters<\/p>\n\n\n\n
TRUNC
=TRUNC(number, number of digits) takes a number and carries it to the specified number of digits<\/p>\n\n\n\n
SEARCH
=SEARCH(find text, within text, start number)
Finds the text within the string text starting at the start number position and outputs the position of the text in the string<\/p>\n\n\n\n
FIND
=FIND(find text, within text, [start number])
The same as SEARCH but is case sensitive!<\/p>\n\n\n\n
SUBSTITUTE
=SUBSTITUTE(text, old text, new text, [instance number]) replaces old text within a string of text (“text”) with new text
If there are several instances of the old text, you can identify which instance with instance number<\/p>\n\n\n\n
REPLACE
=REPLACE(old text, start number, number of characters, new text) replaces a portion of a string with another string\/number, where the portion of the string being replaced is identified by the starting number position and the number of characters<\/p>\n\n\n\n
Note: To replace with text, use quotation marks!<\/p>\n\n\n\n
Flash Fill
Excel 2013 has introduced a real improvement to working with large data sets in the form of Flash Fill<\/p>\n\n\n\n
Tries to guess at to what kind of data you’re trying to get at<\/p>\n\n\n\n
Shortcut: Ctrl+E or Alt+A+F+F (Data > Flash Fill)<\/p>\n\n\n\n
Text to Columns
Data > Text to Columns<\/p>\n\n\n\n
Usually use delineated<\/p>\n\n\n\n
Note: If there is a column that has spaces within it and the columns are delineated by spaces, you can fix this by concatenating the columns with the corresponding data within them, separating each column by a space (” “)<\/p>\n\n\n\n
Then, copy and special paste the values into the correct column<\/p>\n\n\n\n
If there are several columns included in this, you can use flash fill to separate the columns<\/p>\n\n\n\n
Remove Duplicates
Data > Remove Duplicates<\/p>\n\n\n\n
Highlight the relevant data range and then click remove duplicates<\/p>\n\n\n\n
Select all columns to ensure true duplicates<\/p>\n\n\n\n
VALUE Function
Allows Excel to recognize a value as a number when it was previously recognized as text
Ex. “Iphone – $499” in A1: =VALUE(RIGHT(A1,4)) to get $499 as a number<\/p>\n\n\n\n
DATEFUNCTION
Allows you to consolidate separate date data points and have Excel recognize it as a date
Ex. “December” “21” and “2015” in cells A1, A2, A3: =DATEVALUE(CONCATENATE(A1,” “,A2,” “,A3))<\/p>\n\n\n\n
Sorting Data
Highlight an entire table or have the cursor in one of the cells in the table<\/p>\n\n\n\n
Hit Alt+D+S (Data Tab > Sort) to bring up the ‘Sort’ meenu<\/p>\n\n\n\n
You can choose to sort various columns by value, cell color, or font color, and in ascending or descending order<\/p>\n\n\n\n
Note: You can sort multiple levels, so if you wanted to sort by industry first and then by revenue within each industry, you just need to click ‘add level’<\/p>\n\n\n\n
Combining Sort & Subtotal
Combining Sort with Subtotal – Alt+A+B (Data > Subtotal) – can add further clarity to data sets<\/p>\n\n\n\n
Sort by category and then sum<\/p>\n\n\n\n
Autofiltering
Alt+A+T (Data > Filter)<\/p>\n\n\n\n
A tool that enables you to filter by a wide range of criteria<\/p>\n\n\n\n
You should now see drop-down arrows in the column headings of your table<\/p>\n\n\n\n
Notice that the filtered rows have been hidden but not deleted; fortunately, if you copy and paste the filtered range to another area of the worksheet or a different worksheet altogether, it does not copy over the hidden rows<\/p>\n\n\n\n
Once a filter is applied you can tell which column was filtered via the funnel icon<\/p>\n\n\n\n
To remove individual filters, click on the filter icon and ‘Clear filter’<\/p>\n\n\n\n
To remove all filters, hit Alt+A+T again<\/p>\n\n\n\n
Pivot Tables
An efficient, visual tool for analyzing, exploring, and presenting large amounts of data<\/p>\n\n\n\n
It is particularly useful when your data needs to be sliced and diced in a variety of ways<\/p>\n\n\n\n
Answers Questions Like:<\/p>\n\n\n\n
(1) Which property type is most expensive\/most common in a specific region?<\/p>\n\n\n\n
(2) Which REIT had the greatest sales within a specific region or for a specific property type?<\/p>\n\n\n\n
Has the ability to pivot, or quickly swap out different row and column parameters to observe data in different ways<\/p>\n\n\n\n
Creating a Pivot Table
Use your arrows to move to any cell inside the table of data and hit: Alt+N+V or Alt+N+V+T (Insert > Pivot Table)<\/p>\n\n\n\n
Excel will automatically highlight the entire table of data. Select to open the Pivot Table in a new worksheet.<\/p>\n\n\n\n
A new worksheet will open showing the Pivot Table Field List. This shows a list of the data’s categories (REIT, property type, etc.) and the 4 areas of the Pivot Table: Filters, Columns, Rows, Values (the outputs).<\/p>\n\n\n\n
Using your mouse, you can drag any of the data categories into one of the 4 areas of the table, the Pivot Table itself will automatically reorganize to accommodate the newly added category<\/p>\n\n\n\n
Clicking the drop-down arrow allows you to sort and filter the column headers in various ways (ex. if you wanted to show data only for BRE Properties, you could unselect the other REITs)<\/p>\n\n\n\n
Note: Values are automatically summed. If you want to get something else (count, average, max, etc.) you can find this under the Value Field Settings by clicking on the corresponding value. You can also change what the values are shown as (% of grand total, etc.) under the same menu under Show Values As.<\/p>\n\n\n\n
Distinct Count
Prior to 2010, a challenge was capturing distinct counts of duplicate items presented in large data sets
In Excel 2013, Distinct Count has been added under Value Field Settings in a Pivot Table<\/p>\n\n\n\n
The Fiscal Half Date Problem
If you want to find the fiscal half date for a given transaction date, you can use EOMONTH and ROUND functions together to solve it
Ex. For fiscal halves May 31st and Nov 30th and transaction date in cell B2: =EOMONTH(B2,6+6*ROUND(MONTH(B2)\/12,0)-MONTH(B2)-1)<\/p>\n\n\n\n
COUNTIF as an array
If you want to see if any values in an array equal any values in the criteria, you can use COUNTIF. However, just using COUNTIF gives you 0 and simply stores the proper array.
To properly count, you can use SUMPRODUCT and COUNTIF: =SUMPRODUCT(COUNTIF(range array, criteria array)<\/p>\n\n\n\n
The Olympic Event Problem
If you want to count how many events a country or countries received a medal, you can combine SUM, COUNTIF, and MIN functions to find this from a list of medalists
Ex. To see how many events the US, Germany, or France won medals, see the picture\u2026
See Sheet5 on the Excel Practice Sheet<\/p>\n\n\n\n
Using Wildcards
When doing a COUNTIF function, you can use asterisks to find a word within a cell or array of cells
Ex. To find cells with the word united somewhere in them, you can do: =COUNTIF(array,”united”)
For cells that start with the letter A, you only need one asterisk: =COUNTIF(array,”A*”<\/p>\n\n\n\n
LAMBDA
Use a LAMBDA function to create custom, reusable functions and call them by a friendly name<\/p>\n\n\n\n
=LAMBDA(parameter_or_calculation,parameter_or_calculation, \u2026)<\/p>\n\n\n\n
Note: You must identify the parameters you need upfront before defining them<\/p>\n\n\n\n
LAMBDA for GRATE
For a growth rate function:<\/p>\n\n\n\n
=LAMBDA(base,growthrate,base*(1+growthrate))(Cell for Base,Cell for Growth Rate)<\/p>\n\n\n\n
Then, in another cell: Copy the LAMBDA portion of the function =LAMBDA(base,growthrate,base*(1+growthrate))<\/p>\n\n\n\n
Hit Alt+M+N (Formula > Define Name) to invoke the name manager<\/p>\n\n\n\n
Create a new name GRATE and paste the LAMDA function in ‘Refers to’<\/p>\n\n\n\n
=GRATE(base,growthrate)<\/p>\n\n\n\n
LAMBDA for CAGR
LAMDBA for CAGR: =LAMBDA(vfinal,vbegin,t,(vfinal\/vbegin)^(1\/t)-1)(vfinal,vbegin,t)<\/p>\n\n\n\n
New Function named CAGR: =LAMBDA(vfinal,vbegin,t,(vfinal\/vbegin)^(1\/t)-1)<\/p>\n\n\n\n
vfinal = final value, vbegin = beginning value, t = time period<\/p>\n\n\n\n
=CAGR(vfinal,vbegin,t)<\/p>\n\n\n\n
LAMBDA for DSO
For Days Sales Outstanding Calculations<\/p>\n\n\n\n
LAMBDA for DSO: =LAMBDA(AR,Revenue,DaysinPeriod,DaysinPeriod\/(Revenue\/AR))(AR,Revenue,DaysinPeriod)<\/p>\n\n\n\n
New Function named DSO: =LAMBDA(AR,Revenue,DaysinPeriod,DaysinPeriod\/(Revenue\/AR))<\/p>\n\n\n\n
AR = Accounts Receivable<\/p>\n\n\n\n
=DSO(AR,Revenue,DaysinPeriod)<\/p>\n\n\n\n
LAMBDA for IMPLIEDG
For implied terminal growth rate<\/p>\n\n\n\n
LAMBDA for IMPLIED G: =LAMBDA(rate,cashflow,value,(rate-cashflow\/value)\/(1+cashflow\/value))(rate,cashflow,value)<\/p>\n\n\n\n
New Function named IMPLIEDG: =LAMBDA(rate,cashflow,value,(rate-cashflow\/value)\/(1+cashflow\/value))<\/p>\n\n\n\n
rate = discount rate (WACC), cashflow = first period cash flow, value = present value of the terminal value (annuity)<\/p>\n\n\n\n
=IMPLIEDG(rate,cashflow,value)<\/p>\n\n\n\n
LAMBDA for EOQUARTER
For end of quarter: This function takes a user-defined date, converts it to the nearest upcoming end of quarter date, and allows for incrementing to future quarters<\/p>\n\n\n\n
LAMBDA for EOQUARTER: =LAMBDA(date,quarters,EOMONTH(EOMONTH(date,(ROUNDUP(MONTH(date)\/3,0)3-MONTH(date))),quarters<\/em>3))(date,quarters)<\/p>\n\n\n\nNew Function named EOQUARTER: =LAMBDA(date,quarters,EOMONTH(EOMONTH(date,(ROUNDUP(MONTH(date)\/3,0)3-MONTH(date))),quarters<\/em>3))<\/p>\n\n\n\n=EOQUARTER(date,quarters)<\/p>\n\n\n\n
quarters = Number of quarters from the quarter end date<\/p>\n\n\n\n
LAMBDA for TSM
For Treasury Stock Method: For a company that has issued options to its employees, how diluted are those options? Should they be included in the share count?<\/p>\n\n\n\n
LAMBDA for TSM: =LAMBDA(numoptions,strikeprice,currentprice,SUMIF(strikeprice,”<“¤tprice,numoptions)-SUMPRODUCT(numoptions,strikeprice*(strikeprice<currentprice))\/currentprice)(numoptions,strikeprice,currentprice)<\/p>\n\n\n\n
New Function named TSM: =LAMBDA(numoptions,strikeprice,currentprice,SUMIF(strikeprice,”<“¤tprice,numoptions)-SUMPRODUCT(numoptions,strikeprice*(strikeprice<currentprice))\/currentprice)<\/p>\n\n\n\n
=TSM(numoptions,strikewprice,currentprice)<\/p>\n\n\n\n
numoptions = array of options, strikeprice = array of strike prices for options, currentprice = current share price<\/p>\n\n\n\n
LAMBDA for SHEETNAME
For Sheet Name: Grabs the name of the current worksheet<\/p>\n\n\n\n
LAMBDA for SHEETNAME: =LAMBDA(reference,RIGHT(CELL(“filename”),LEN(CELL(“filename”))-FIND(“]”,CELL(“filename”))))(reference)<\/p>\n\n\n\n
New Function named TSM: =LAMBDA(reference,RIGHT(CELL(“filename”),LEN(CELL(“filename”))-FIND(“]”,CELL(“filename”))))<\/p>\n\n\n\n
=SHEETNAME(reference)<\/p>\n\n\n\n
reference = any cell reference on the corresponding worksheet<\/p>\n\n\n\n
How to Use Lambdas Across Multiple Workbooks
To use lambdas across multiple workbooks, make a blank worksheet in the workbook with your lambdas
Then, copy and paste it into another workbook to add the lambdas over<\/p>\n\n\n\n
Recording Macros
Excel allows you to record a sequence of instructions, and assign a keyboard shortcut to invoke them as desired<\/p>\n\n\n\n
These instructions are called macros<\/p>\n\n\n\n
Go to File > Record Macro<\/p>\n\n\n\n
Recording a Macro (Blue Color, 1 Decimal Place, Comma Delineated, Yellow Background)
(1) Select ‘Use Relative References’<\/p>\n\n\n\n
(2) Place the cursor in any cell<\/p>\n\n\n\n
(3) Alt+L+R brings up ‘Record Macro’ menu (File > Record Menu)<\/p>\n\n\n\n
(4) Input a name and shortcut key (Ctrl+Shift+Z); as soon as you hit ‘OK’, you are starting to record this macro<\/p>\n\n\n\n
(5) Format the cell using Ctrl+1<\/p>\n\n\n\n
(6) When finished, hit Alt_+L+R or stop recording to stop recording<\/p>\n\n\n\n
(7) Try going to any cell and hit Ctrl+Shift+Z; those cell’s contents should be formatted per your preferences<\/p>\n\n\n\n
Problems with Macros
After you run your macro, you lose the ability to undo all the prior work, so be careful!<\/p>\n\n\n\n
Custom shortcuts override pre-existing shortcuts<\/p>\n\n\n\n
If you want your macro to run every time you use Excel, select Personal Macro Workbook instead of ‘This Workbook’<\/p>\n\n\n\n
After the macro is created, you can always delete or rename a macro or change shortcut keys assigned to it by clicking ‘Macros’ in the Developer tab<\/p>\n","protected":false},"excerpt":{"rendered":"
Keyboard Versus the MouseAlmost everything that can be done in Excel using a mouse can also be done using the keyboard shortcutsBest way to learn is to disconnect the mouse and work through Excel using only the keyboard Name BoxTells you what cell you are in (top left below the ribbon) Formula BarWhen you insert […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[],"tags":[],"class_list":["post-109890","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/posts\/109890","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/comments?post=109890"}],"version-history":[{"count":0,"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/posts\/109890\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/media?parent=109890"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/categories?post=109890"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/tags?post=109890"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}