Excel Cheat Sheet/Shortcuts, Cheat Sheet of MS Microsoft Excel skills

Download Excel Cheat Sheet/Shortcuts and more Cheat Sheet MS Microsoft Excel skills in PDF only on Docsity! 1 K201 Excel Fact Sheet Cell referencing and formatting Relative cell addressing Both the column and row reference change when the formula or function is copied. =B11*C10 Absolute cell addressing Both the column and row reference stay the same when the formula or function is copied. =$B$11*$C$10 Mixed cell addressing Either the column or the row reference stays the same when the formula or function is copied. =$B11*C$10 Conditional formatting Formatting appears in a cell when data in the cell meets specified conditions. Select Conditional Formatting on the Home tab. Mathematical and statistical functions MIN(range) Finds the lowest/earliest value in a range. =MIN(B2:B17) MAX(range) Finds the highest/latest value in a range. =MAX(B2:B17) AVERAGE(range) Finds the average of the values in a range. =AVERAGE(B2:B17) COUNT(range) Counts the cells in a range that contain numeric data. =COUNT(E12:E15) COUNTA(range) Counts all non-empty cells in a range. =COUNTA(C12:C15) COUNTIF(range, criterion) Counts the cells in a range that meet the criterion. =COUNTIF(D12:D15,">=90") SUM(range) Finds the sum of the values in a range. =SUM(B2:B17) SUMIF(range, criterion,[sum_range]) Calculates a sum based on a given criterion. =SUMIF(A14:A34,”Yes”,B14:B34) ROUND(number, decimal_places) Rounds a value (or cell reference, formula, or function) to the number of decimal places specified. =ROUND(A4, 2) INT(number) Rounds a number down to the nearest whole integer. =INT(SUM(G11:G13)) Financial functions PMT(rate, nper, pv) Given an interest rate (rate), number of payment periods (nper), and amount borrowed (pv), calculates the payment amount. =PMT(E16/12,F16*12,-D16) =PMT(E16/4,F16*4,-D16) PPMT(rate,per,nper,pv) Calculates the amount of principal paid in each individual payment period (per) of a loan. =PPMT($E$16/12,A19,$F$16*12,- $D$16) IPMT(rate,per,nper,pv) Calculates the amount of interest paid in each individual payment period (per) of a loan. =IPMT($E$16/12,A19,$F$16*12,- $D$16) FV Returns the future value of an investment based on periodic, constant payments and a constant interest rate. =FV(rate,nper,pmt,[pv],[type]) 2 PV Returns the present value of an investment. The present value is the total amount that a series of future payments is worth now. For example, when you borrow money, the loan amount is the present value to the lender. =PV(rate, nper, -pmt, [fv], [type]) NPER Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate. =NPER(rate,pmt,pv,[fv],[type]) RATE Returns the interest rate per period of an investment. =RATE(nper, pmt, pv, [fv], [type], [guess]) Date functions TODAY() Returns the date from the computer’s clock as a serial number (which can be formatted to appear as a date). =TODAY() DATEDIF(start_date, end_date, unit) Returns the number of whole days, months, or years between two dates. Start_date must be earlier than end_date. Use “Y” for years, “M” for months, and “D” for days. To find weeks, first find days, then divide by 7. For whole weeks, also use INT. =DATEDIF(C11,D5,“Y”) To calculate weeks: =INT(DATEDIF(E11,D5,“D”)/7) Logical functions IF(logical_test, value_if_true, value_if_false) Evaluates a specified condition, performing one action if the condition is true and another action otherwise. =IF(C13>=124,"Y","N") Nested IF IF(lt, vt, IF(lt, vt, vf)) Used when there are more than two outcomes. If you have four outcomes: =IF(lt, vt, IF(lt, vt, IF(lt, vt, vf))) =IF(E12>=3.5,"High Honors", IF(E12>3.2,"Honor Student","Average")) AND (test1, test2...) Evaluates to TRUE when all logical tests are true. =IF(AND(I18="A",H18<=3),"Y"," N") OR (test1, test2...) Evaluates to TRUE when at least one test is true. =IF(OR(I18="A",H18<=3),"Y","N ") Pivot Tables On the Insert tab, click PivotTable, then follow the steps in the wizard. Recommendation: Add the Values item last. Data retrieval functions MATCH(lookup_value, array, match type) =MATCH(C17,$A$5:$K$5,0) Returns a number indicating the position of the lookup_value in the array. lookup_value = the value whose position in the array you want to find. array = a single column or single row of data. match type = to require an exact match, enter 0 or FALSE; to allow a close match, enter 1 or TRUE if the array is in A-Z order and -1 if the array is in Z-A order.