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
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 ! -
number format: 2 decimals, 000 separator
Ctrl Shift $ -
-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 -
forgetting the equal sign (=) at the start or inserting a space before the equal sign of the formula is created by Excel as 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)
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
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
SUMIF -
=SUMIF(range, criteria, sum range)
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)
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 -
Shift Ctrl &
toggle Excel workbooks -
