Excel Formulas and Functions Guide, Cheat Sheet of MS Microsoft Excel skills

Download Excel Formulas and Functions Guide and more Cheat Sheet MS Microsoft Excel skills in PDF only on Docsity! move back and forth between the worksheets in a workbook - *Ctrl PageUp* or *Ctrl PageDown* open a file - *alt f o* *ctrl o* save a file - *alt f s* *ctrl s* print a file - *alt f p* *ctrl p* navigating to the toolbars without the mouse - hit *Alt and the appropriate letter* (or use the right/left arrow keys) to get to the desired tab once there, use the Tab and Shift Tab keys to navigate around accessing settings - File > Options (*Alt f t*) *Alt t o* Disabling the Start Screen and adding more worksheets on startup - click on 'Blank worksheet' Hit *Alt f t* go go to Excel options (you can also get there by clicking the file tab) Under 'General' unselect "Show the Start screen when this application starts" Password protecting files - File Info Password protect files: "Encrypt with Password" password protect individual worksheet (so that people can't see or edit formulas): "Protect Current Sheet" highlighting columns and rows - columns: *Ctrl Spacebar* rows: *Shift Spacebar* widening columns and rows - highlight column by pressing *Ctrl Spacebar* *Alt h o i* to auto-fit the column width -if you want a column to have a certain width, press *Alt h o w* and enter the desired width highlight row by pressing Shift Spacebar *Alt h o a* to auto-fit the row height -if you want a row to have a certain height, press *Alt h o h* and enter the desired height format cells - *Ctrl 1* pulls up the 'Format Cells' menu navigating the 'Format Cells' menu - after hitting Ctrl 1 to pull up the 'Format Cells' menu, you can navigate around tabs by hitting *Ctrl tab* each category within a tab can be accessed by pressing *Alt* and the appropriate underlined letter, or by hitting *tab* to move clockwise and *shift tab* to move counter clockwise when on the desired tab, horizontal alignment can be accessed through *Alt h* and vertical alignment through *Alt v* paste special - *Alt v s t* or *Alt e s t* changing zoom size - *Alt w q* or *Alt v z* press *Ctrl Shift +* to insert the row note: Excel will insert the row ABOVE the row that is highlighted inserting columns - *Ctrl Spacebar* to highlight the entire column *Ctrl Shift +* to insert the column note: Excel will insert the row to the LEFT of the column that is highlighted deleting rows and columns - row: *Alt h d r* column: *Alt h d c* OR highlight row/column and press *Ctrl -* Paste special - transpose - this feature allows users to convert a vertical list of data into a horizontal list of data, and vice versa highlight the vertical list of data in an open cell, press *Alt e s e* -your vertical list of data should now be listed horizontally *Ctrl Shift !* - number format: 2 decimals, 000 separator *Ctrl Shift $* - currency format: 2 decimal places *Ctrl Shift %* - percentage format with no decimal places *Ctrl Shift #* - date format with the day, month, and year *Ctrl Shift _* - remove all borders anchoring cells - when you copy and paste a cell containing a formula from one cell to another, Excel automatically shifts the cells being referenced in the formula sometimes this is not desirable, which is why Excel allows you to anchor cells Anchoring (or "Fixing") cells that are being referenced in a formula tells Excel that even if you copy the formula to another cell, the cells in the formula that are anchored should not change fix column: $ before letter fix row: $ before number fix entire cell: $ before letter and before number renaming a worksheet you are working in - *Alt h o r* inserting and deleting worksheets - add a new worksheet: *Alt h i s* or *Alt i w* delete a worksheet: *Alt e l* new tab - *Shift F11* Grouping columns and rows - to group columns: -hit *Ctrl Spacebar* to select the desired column(s) -hit *Shift Alt right arrow key* to create the group -hitting *Alt a h* will hide the columns -hitting *Alt a j* will unhide the columns -hitting *Shift Alt left arrow key* will remove the group to group rows: -hit *Shift Spacebar* to select the desired rows -follow the other steps above grouping vs hiding - another way to hide data is by hitting *Alt h o u r* for rows and *Alt h o u c* for columns, but don't ever use this method because under this method, columns and rows are hidden, but there are no indications (no minus or plus signs as in the first method) as to their location so it's easy to forget which columns and rows you've hidden grouping worksheets - if you want to format data in the same manner or enter the same data across multiple sheets, Excel allows you to do this by grouping multiple worksheets, any data and formatting that you would perform in one of the grouped worksheets would automatically be reflected in all of them to group worksheets: -holding down *Ctrl Shift*, press *PageUp* or *PageDown* to reach the worksheets you would like to group -all of the grouped worksheets are highlighted and the file name on top of the Excel screen should show [Group] after it to ungroup worksheets - press *Ctrl PageUp* or *Ctrl PageDown* until you reach one of the ungrouped worksheets; this automatically groups all of the previously grouped sheets auditing cells - go to the cel and hit F2 Excel allow users to combine (or "concatenate") cells with a text string in them with other text strings, creating one text string by using the "&" function EOMONTH - =EOMONTH(start_date, months) allows you to create monthly date headers, by outputting the last day of a specified month start_date: represents a starting date reference months: represents x number of months before or after the start_date to output a date x months before a start_date, x should be negative EDATE - =EDATE(start_date, months) similar to EOMONTH, but the difference is that EDATE returns the exact date, x months from the start date YEARFRAC - =YEARFRAC(start_date, end_date, basis) returns the proportion of the year between two given dates (the start_date and the end_date) basis is an optional parameter that represents the number of days per month/year used to calculate the proportion of the year: 0 or omitted: US 30/360 1: actual/actual 2: actual/360 3: actual/365 4: European 30/360 DATE - =DATE(year, month, date) 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 helpful for creating date functions out of disparate data cell contents as criteria in IF statements - a surprisingly useful criteria used in IF statements is a test of whether a cell has anything in it ex: the IF statement =IF(C1, C2, C3) would see if there is anything in cell c1, and if there was, it would output C2, otherwise C3 ISNUMBER and ISTEXT - both typically embedded within an IF statement, they test whether there is number or text inside a cell, respectively AND - =AND(logical1, logical2,...) evaluates to true if all of its arguments are true; false if one or more arguments is false OR - =OR(logical1, logical2,...) evaluates to true if at least one argument is true HLOOKUP - =HLOOKUP(lookup_value, table_array, row_index_number, [range_lookup]) 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/array use HLOOKUP 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 VLOOKUP - =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) searches for a value in the LEFTMOST column of a table and then returns a blue in the same row from a column you specify the table/array use VLOOKUP 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 range_lookup - TRUE: an approximate match to the lookup value is returned FLASE: an exact match is returned when lookup value is text or numbers is non-ascending order, the FALSE range is often preferred OFFSET - =OFFSET(reference, rows,cols,height,width) returns a cell value or range of cell values that is a specified number of rows and columns from a cell or range of cells INDEX - =INDEX(array, row_num, column_num) returns a value from a specified row and column from within a table or range CHOOSE - =CHOOSE(index_num, value1, value2, value3,...) returns a number from a specified list of up to 254 values MATCH - =MATCH(lookup_value,lookup_array,match_type) returns the relative position (number) of an item in an array that matches the specified lookup value it does NOT return the value within the cell itself (as opposed to the HLOOKUP and VLOOKUP functions) adds the cells specified by a given criteria criteria can either be hardcoded (which requires quotation marks) or a direct cell reference (which does not need quotation marks) SUMIFs - =SUMIF(range1, criteria1, sum range1, range2, criteria2, sum range2, etc.) same as SUMIF but can handle multiple criteria and sum ranges AVERAGEIF and AVERAGEIFs - identical to SUMIF and SUMIFS but instead of summing, this function averages the data in the range ABS, CEILING, FLOOR - ABS(cell) returns the absolute value of that cell CEILING(cell, x) rounds input cell up to nearest x FLOOR (cell, x) rounds input down to nearest x COMBIN - Returns the number of combinations for a given number of objects useful accounting application: sum of years' digits n(n+1)/2 =COMBIN(cell+1,2) ROUND, ROUNDUP, ROUNDDOWN - ROUND: rounds most accruately ROUNDUP: rounds up ROUNDDOWN: rounds down MIN and MAX - =MIN(number1,number2,...) returns the smallest number in a specified set of values) =MAX(number1,number2,...) returns the largest number in a specified set of values COUNT - =COUNT(value1, value2,...) counts the number of cells that contain numbers within the list of arguments -cells with text are disregarded COUNTA - same as COUNT except cells with numbers and text are counted COUNTIF - =COUNTIF(range, criteria) counts the number of items in the range that satisfy a specific criteria -similar to the SUMIF function COUNTIFS - allows for multiple criteria -similar to the SUMIFS function PV function - returns the present value of a series of future payments =PV(rate, nper, pmt, fv, type) rate: interest rate per period nper: total number of payment periods pmt: payment made during each period (it cannot change) fv: future value type: indicates when payments are due (0 or omitted = end of period, 1 = beginning of period) FV function - returns the future value of an investment based on constant payment and interest rate =FV(rate, nper, pmt, pv, type) rate: interest rate per period nper: total number of payment periods pmt: payment made during each period (it cannot change) pv: present value type: indicates when payments are due (0 or omitted = end of period, 1 = beginning of period) NPV function - =NPV(rate, value1, value 2, ...) 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 NPV vs. PV functions - PV assumes constant payments, while NPV cash flows can vary from period to period NPV does not require the 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 formula can be adjusted to simulate payments accusing at beginning of period) XNPV function - NPV performs equal discounting on each cash flow =XNPV(rates, values, dates) -as you drag a category into one of the 4 areas of the table, the Pivot Table itself will automatically reorganize to accommodate the newly added category value field settings (pivot tables) - VALUES > Field Settings the Field Settings enables you to show outputs in a variety of different ways (ex: to see counts instead of sums) the Value Field Settings also has a "Show Values As" tab, which enables you to present the VALUES in a variety of useful ways recording macros - rather than repeatedly going through the same series of steps, Excel allows you to record a sequence of instructions, and assign a keyboard shortcut to invoke them as desired -these instructions are called macros 1. select 'Use Relative References' 2. place the cursor in any cell 3. *Alt l r* brings up "Record Macro" menu 4. input a name and shortcut key (ex: ctrl shift z) -as soon as you hit "OK" you are starting to record this macro 5. format the cell 6. when finished, hit *Alt l r* to stop recording now if you go to any other cell and enter the shortcut you inputted (in this case ctrl shift z), those cells' contents should be formatted per your preferences repeat - *Ctrl Y* Outline border - *Shift Ctrl &* toggle Excel workbooks - *Ctrl tab* Min/restore ribbon - *Ctrl F1* print preview - *Ctrl F2* close window - *Ctrl F4* move to cell "A1" - *Ctrl Home* move to cell above - *Shift Enter* move to cell to the right - *tab* move to cell to the left - *Shift Tab* show formulas/values - *Ctrl ~* fill selection with entry - *Ctrl enter* recalculate all workbooks - *F9* get inside a drop-down list - *Alt + up/down arrow key* display "Insert Function" box - *Alt I F (Shift F3)* display "Name" box - *Ctrl F3* Enter array formula - *Shift Ctrl Enter* replace - *Ctrl H* insert AutoSum formula - *Alt +" (hold down Alt) start a new line in same cell - *Alt Enter*