Download Excel Formulas and Functions Guide and more Cheat Sheet MS Microsoft Excel skills in PDF only on Docsity!
101 verified Excel Crash Course
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
hitting Delete does NOT delete the formatting in the cell to truly delete a cell: -Alt h e (Home tab > Clear) -from the drop-down, chose the appropriate actions (Alt h e) a: clears ALL contents of the cell (Alt h e) f: clears only the cell FORMAT (Alt h e) c: clears only the cell CONTENT -this is the same as hitting Delete (Alt h e) m: clears only the cell COMMENTS right and down fills - highlight the range Ctrl R tells Excel to look at the LEFTMOST column of a range of highlighted cells and copy and paste the inputs and formulas in that column to all the columns to the right Ctrl D does the same thing for rows that Ctrl R does for columns, telling Excel to look at the topmost row of a range of highlighted cells and to copy and paste the inputs and formulas in that row down to all the rows below it inserting comments - enter the cell and type Shift F2 hit Esc twice to exit the comment area to edit an existing comment: go to the cell and press Shift F2 to delete a comment: press Alt h e am inserting rows - highlight the row BELOW the row where you want to add a row
press Shift spacebar to highlight the entire row 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 ! -
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 F OR hitting Ctrl [ on a cell will highlight the precedent cells -keep hitting Ctrl [ and it will take you to the next precedent, and on and on hitting Ctrl ] on a cell will do the same thing but for dependent cells Excel's formula auditor - trace precedents: Alt m p -to trace cells that provide data to a formula -a worksheet icon indicates that the precedent cells are in another worksheet -double clicking anywhere with a mouse on the black arrow brings up the 'Go To' screen; selecting either of the precedent cell locations and hitting 'Ok' will take you there trace dependents: Alt m d -to trace formulas that reference a particular cell (the cell highlighted) remove trace arrows: Alt m a a evaluate formula - Alt m v track changes - Alt r g zoom to selection - Alt w g "go to" - F5 Error: formula treated as text -
creating dynamic headers by combining cell references with text ("&") - 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/ 1: actual/actual 2: actual/ 3: actual/ 4: European 30/ 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 C 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)
it does NOT return the value within the cell itself (as opposed to the HLOOKUP and VLOOKUP functions) data validation - data validation is a utility in Excel, whose most frequently used feature is its ability to create simple and quick drop-down menus to create a drop down menu: -with the cell where you want your drop down menu active, open the data validation form (Alt d l -within the Settings tab, select list from the dropdown menu -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 your dropdown menu appear note: it only appears when you are on the active cell building a vertical data table -
- identify the output variable -the variable you are trying to sensitize is the output variable -must be referenced from your analysis into the TOP RIGHT CORNER of the data table
- hard-code the input variable sensitivities -the variables whose impact on the output variables you want to analyze are the input variables -input variable assumptions should not be referenced from the analysis, but rather be hard-coded and arranged in the column to the right of the output variable
- run the data table -hit Alt d t: the Data Table dialog will appear row input cell: not needed for vertical data tables column input cell: reference the input variable from the model -highlight the entire range (including the output variable) and hit OK when done - the data table should populate -you. may need to hit F9 if Excel is set to "manual" or "automatic calculations except for data tables"
NOTE:
data tables must always be in the same worksheet as the input variables building a horizontal data table -
- referenced output variable from your analysis into the BOTTOM LEFT CORNER of your data table
- input the input assumptions in the row above and one cell to the right of the output reference
- highlight the entire range (including the output variable) and hit Alt d t: the Data Table dialog will appear row input cell: reference the input variable from the model column input cell: not needed
- hit OK when done - the data table should populate building a 2-sided data table - same as vertical data table, but allows for 2 inputs instead of one output variable must be referenced from the model into the TOP LEFT CORNER of the data table now both row input cell and column input cell are needed SUMPRODUCT - =SUMPRODUCT(array1,array2,array3,...) multiplies corresponding components in two or more arrays, and returns the sum of those products Booleans in Excel - when Excel spits out a TRUE or FALSE, you can convert them respectively into 1 or 0 by applying any operator on them multiply the TRUE/FALSE cell by 1: will convert a TRUE to 1 and FALSE to 0 multiply the TRUE/FALSE cell by TRUE: will convert a TRUE to 1 and FALSE to 0
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
there must be both positive and negative values Flash Fill - Ctrl e sorting data - Alt d s (Data tab > Sort) combining Sort & Subtotal - Subtotal: Alt a b autofiltering - Alt a t the filtered rows get hidden but not deleted if you copy and paste the filtered range to another area of the worksheet or a different worksheet, id doesn't copy over the hidden rows once a filter is applied, you can tell which column was filtered via the funnel icon to remove individual filters, click on the filter icon and "Clear filter" to remove all filters, hit Alt a t again pivot tables - select any cell inside the table of data and hit Alt n v -Excel will automatically highlight the entire table of data -select to open the Pivot Table in a new worksheet a new worksheet will open showing the Pivot Table Field List -this shows a list of the data's categories and the 4 areas of the Pivot Table (filters, columns, rows, values)
using your mouse, you can drag any of the data categories into any of the 4 areas of the Pivot Table -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
- select 'Use Relative References'
- place the cursor in any cell
- Alt l r brings up "Record Macro" menu
- input a name and shortcut key (ex: ctrl shift z) -as soon as you hit "OK" you are starting to record this macro
- format the cell
- 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 -
