excel formulas cheat sheet pdf, Cheat Sheet of MS Microsoft Excel skills

Download excel formulas cheat sheet pdf and more Cheat Sheet MS Microsoft Excel skills in PDF only on Docsity! Commonly Used Excel Formulas and Functions Supplement to “Excel for Budget Analysts” Version 1.0: February 2016 Commonly Used Excel Functions Page 2 Table of Contents Introduction ...................................................................................................................................... 4 Formulas and Functions .................................................................................................................. 4 Math and Trigonometry Functions ............................................................................................... 5 ABS .......................................................................................................................................... 5 ROUND, ROUNDUP, and ROUNDDOWN .............................................................................. 5 Statistical Functions ..................................................................................................................... 5 COUNT, COUNTA, and COUNTBLANK .................................................................................. 5 AVERAGE, MEDIAN, MIN, and MAX ...................................................................................... 6 QUARTILE................................................................................................................................ 6 RAND and RANDBETWEEN ................................................................................................... 7 Date and Time Functions ............................................................................................................. 7 DATE ........................................................................................................................................ 7 YEAR, MONTH, and DAY ........................................................................................................ 8 WEEKDAY................................................................................................................................ 8 DATEDIF .................................................................................................................................. 8 Lookup and Reference Functions ................................................................................................ 8 VLOOKUP ................................................................................................................................ 8 HLOOKUP ................................................................................................................................ 9 INDEX ..................................................................................................................................... 10 MATCH ................................................................................................................................... 10 Text Functions............................................................................................................................ 12 LOWER, UPPER, and PROPER............................................................................................ 12 CONCATENATE .................................................................................................................... 12 LEFT, RIGHT, and MID .......................................................................................................... 12 LEN and TRIM ........................................................................................................................ 13 TEXT and VALUE .................................................................................................................. 13 Logical Functions ....................................................................................................................... 14 COUNTIF ................................................................................................................................ 14 SUMIF .................................................................................................................................... 14 IF ............................................................................................................................................ 14 AND ........................................................................................................................................ 15 OR .......................................................................................................................................... 15 Shortcuts ........................................................................................................................................ 16 Formatting .............................................................................................................................. 16 Editing ..................................................................................................................................... 16 Calculations ............................................................................................................................ 17 Commonly Used Excel Functions Page 5 Math and Trigonometry Functions Several math functions can help expedite analysis. This section highlights on a few. ABS When there is a need to get the absolute value of a number, the ABS function is helpful. ROUND, ROUNDUP, and ROUNDDOWN There are various options with rounding, depending on the need. The functions’ argument tooltip specifies how many decimal places or to which nearest integer it should round.  ROUND – This function helps users to round to the nearest value.  ROUNDDOWN – This function helps users to round values down to the nearest value based on the desired decimal place or integer.  ROUNDUP - This function helps users to round values up to the nearest value based on the desired decimal place or integer. Statistical Functions When presented with large datasets, it is helpful to sort and summarize the information at hand. COUNT, COUNTA, and COUNTBLANK The counting functions are especially helpful with large datasets to identify anomalies and to get general summary statistics.  COUNT – This function counts the number of cells that contain numbers.  COUNTA – This function counts cells containing any type of information, including error values and empty text (as shown in the example below, it counts the cell marked “VOID”).  COUNTBLANK – This function counts only the empty cells within the dataset, with no information contained in the cells. Commonly Used Excel Functions Page 6 AVERAGE, MEDIAN, MIN, and MAX With large datasets, it is often helpful to run basic summary statistics before doing further analysis.  AVERAGE – This function calculates the average value in a dataset.  MEDIAN – This function calculates the median, middle value, in a dataset.  MIN – This function returns the lowest value in a dataset.  MAX – This function returns the largest value in a dataset. QUARTILE The quartile function helps users to understand the distribution of values. The first argument identifies the values or cells that users want to calculate and the argument tooltip identifies which quartile (0 – minimum value; 1 – first quartile or 25th percentile; 2 – median value or 50th percentile; 3 – third quartile or 75th percentile; and 4 – maximum value). Commonly Used Excel Functions Page 7 RAND and RANDBETWEEN This function is helpful when needing to create random values. Note that the random values Excel generates will recalculate as the fields are altered.  RAND – This function generates a random value between 0 and 1.  RANDBETWEEN – This function generates a random value between a specified range of values. Date and Time Functions Sometimes when we export data from a database system, the date does not extract as neatly. Other times, we are looking to calculate the duration from one date to another. DATE This function is useful when information related to year, month, and date are in separate cells and the preference is to have the date in one cell. Commonly Used Excel Functions Page 10 INDEX This function can take two forms. The first form is a reference where users instruct Excel to return values in a table based on headings. The argument first identifies the values in the table (B3:F8 in the example below). The first argument tooltip identifies the row number within the table of inquiry (4 for April and 5 for May, respectively) and the third tooltip identifies the column within the table of inquiry (1 for Dept_01 and 2 for Dept_02). The array form returns the entire row or column of table. Note, it is important to first select the cells you want to contain the returned values (in the example below, B11:F11 were selected as the formula was entered). The argument first identifies the values in the table (B3:F8). The first tooltip identifies the row of inquiry (3 for March). The following argument tooltip references the column from the reference to which Excel should return (0 for no columns). Note to run the function in array form for a selected group of cells, click Ctrl + Shift + Enter to return values and not Enter. MATCH This function shows users where in a list they can find their values by providing Excel with search parameters. The example below is identifying where the break is in the list for those earning less than $5,000. The argument first identifies the search value (5,000 in the example below). The first tooltip identifies the column or row of inquiry. In this example, the first tooltip identifies the cells with the salary information (D2:D16). The second tooltip identifies whether an exact (0) or approximate match (1 or -1) is desired. In this example, 1 is used to denote less than $5,000. Please note that if the inquiry is for less than the search value (1), then the column or row needs to be sorted in ascending order. Conversely, if the inquiry is for greater than the search value (-1), then the column or row needs to be sorted in descending order before proceeding with the match function. Note that the salary column in the example is sorted in ascending order. Commonly Used Excel Functions Page 11 The return information is 7 to identify the position in the cell range (D2:D16) that contains the information, e.g., the split in the list of those earning less than $5,000. GETPIVOTDATA To avoid copying and pasting information from a pivot table, this function helps to return values using appropriate commands. The example below shows the level of details that can be captured using this function. In the first example, we are identifying the grand total of revenues from the pivot table. To do so, the first argument is the data field of inquiry where the data we want is contained, e.g. “Sum of Revenues ($000). The first tooltip is the reference cell in the PivotTable to help determine which report to Excel should pull from (this is especially useful when you are entering this function in one worksheet and have multiple PivotTable reports in the workbook.) The second example builds off of the first, but wants to identify the total for February. This requires additional tooltips on the field name (Month), which is field heading in original dataset, and the actual item name (February). The third example is more specific than the second and contains additional tooltips to identify sales tax revenues in March. Other tooltips for field name (Source) and item (Sales Tax) is included. Commonly Used Excel Functions Page 12 Text Functions Text functions are helpful when we seek to only get a portion of the information contained within a cell or we are trying to combine information contained in multiple cells. LOWER, UPPER, and PROPER Formatting can be an issue when exporting data. Information may be in upper cases, lower cases, or a combination of both. To make clean tables and charts can require formatting or use of the following functions:  LOWER – This function returns text values in a cell in all lower case.  UPPER – This function returns text values in a cell in all upper case.  PROPER – This function returns text values in a cell with the each of the first word capitalized. CONCATENATE CONCATENATE is helpful to combine information contained in separate cells into one cell. The function contains arguments referencing the cells containing the information. The example below shows how the function returns values. In the first example, first name and last name is combined, but look closely because there isn’t a space. By adding to the argument by including spaces (“ “), commas (“, “), and other desired punctuation or words (“ of “) the appropriate desired value can be returned. LEFT, RIGHT, and MID These functions are helpful to extract a portion of a larger string. The example below shows is an account structure. The first four digits represent the department/division. In order to retrieve the first four digits, we use the left function. The first argument identifies the cell containing the account information (A2) and the tooltip identifies how many digits from the left do we want to extract (4). The RIGHT function follows a similar structure. In the example below, the last 5 digits represent the object. Thus, the argument identifies the cell containing the account information (A2) and the number of digits from the right we want to extract (5). The MID function requires more details. Similar to LEFT and RIGHT, the argument identifies the cell containing the account information (A2). The first tooltip identifies which digit to start the extraction (5 to represent the fifth digit in the account string) and second tooltip identifies the number of digits to extract (5). Commonly Used Excel Functions Page 15 return a value of “Not Overpayment.” Please note the attention to the value we selected to return if the statement is false because the logic statement is also capturing instances where the payment amount equals the invoice amount (Rows 3 and 6). AND Similar to if statements, the AND function returns a value of “TRUE” if the statement is true and “FALSE” if the statement is false. The function tests multiple criteria. In the example below, the statement is testing two criteria. The first is whether there has been an underpayment (Column E is less than Column C) and whether the payment took more than 30 days from time of invoice (Column D minus Column B is greater than 30). Both conditions must be true in order for Excel to return the value “TRUE.” If only one of the conditions is true, either there is an underpayment or payment took over 30 days from time of invoice, then Excel returns the value “FALSE.” OR This function tests multiple logic criteria and returns a value of “TRUE” if one criterion is true. The example below is similar to the one for the AND function. However, if one of the conditions, e.g., underpayment (Column E is less than Column C) or payment taking more than 30 days from time of invoice (Column D minus Column B is greater than 30), is met, then Excel returns the value “TRUE.” If both conditions are not met, then Excel returns the value “FALSE.” Commonly Used Excel Functions Page 16 Shortcuts Formatting Shortcut Name Keystrokes Purpose Border Ctrl+Shift+7 Places border around selected cell(s) Remove Border Ctrl+Shift+– Removes border around selected cell(s) Clear Alt+H+E Opens clear editing features. Keying additional letters will perform the functions listed below: Paste Special Ctrl+C, Alt+H+V Opens paste special features Keying additional letters as indicated in the underlined word performs the functions listed below: Change Font Size Alt+H+F+S Goes to font size dropdown Format Cell Ctrl+1 Opens format cell window Group rows or columns Alt+A+G+G or Shift+Alt+→ Opens group window Ungroup rows or columns Alt+A+U+U or Shift+Alt+← Ungroups grouped rows or columns Highlight Row Shift+Spacebar Selects entire row Editing Shortcut Name Keystrokes Purpose Replace Ctrl+H Opens Find and Replace Window Redo Ctrl+Y Redos last edit Undo Ctrl+Z Undos last edit Commonly Used Excel Functions Page 17 Calculations Shortcut Name Keystrokes Purpose Auto Sum Alt+= Summarizes column or row information depending on location of cell Edit Cell F2 Edits formula or function Naming Shortcut Name Keystrokes Purpose Name Cell Ctrl+F3 Opens Name Manager window Rename Worksheet Alt+H+O+R Renames worksheet Navigation Shortcut Name Keystrokes Purpose Go To F5 or Ctrl+G Opens Go To window to go to a different cell Go To End of Continuous Range Ctrl+↓ Goes to last cell in the range Select to End of Continuous Range Ctrl+Shift+↓ Selects cells to the end of range Highlight Column Ctrl+Spacebar Selects entire column Toggle Workbooks Ctrl+Tab Navigate between open workbooks Move Between Worksheets Ctrl+Page Up, Ctrl+Page Down Page Up moves to workpages to the right and Page Down moves to workpages to the left Reference Shortcut Name Keystrokes Purpose Anchoring Cells F4 Locks reference cells Trace Precedents Alt+M+P Identifies cells that are used to calculate a given formula using arrows Trace Dependents Alt+M+D Identifies cells where a formula contain information on a given cell using arrows Remove Trace Arrows Alt+M+A+A Removes arrows from trace precedent and trace dependent features