Add/delete worksheets - ANSWER-alt i w
Move back and forth to different worksheets - ANSWER-ctrl page up and down
Open a new file - ANSWER-alt f n, c... [Show More] trl n
open a file - ANSWER-alt f o, ctrl o
save a file - ANSWER-alt f s, ctrl s, alt f a(save as)
print a file - ANSWER-alt f p, ctrl p
Navigating to the toolbars without the mouse - ANSWER-Hit alt and the corresponding letter and then use tab and shift-tab to navigate
Access Excel options - ANSWER-alt f t, alt t o
Bold - ANSWER-Ctrl B
Highlight Column - ANSWER-ctrl spacebar
Auto fit column width - ANSWER-alt h o i, alt o c a
Enter column width - ANSWER-alt h o w, alt o c w
Highlight Row - ANSWER-Shift Spacebar
auto fit row height - ANSWER-alt h o a, alt o r a
Enter row height - ANSWER-alt h o h, alt o r e
Format cells menu - ANSWER-ctrl 1. has six tabs that include number, alignment, font, border, fill, protection. you can navigate around the tabs with ctrl-tab. each category within a tab can be accessed by pressing Alt and the appropriate underlined letter, or by hitting tab.
Paste Special - ANSWER-alt h v s , alt e s
Move to different sheet - ANSWER-ctrl tab
move the location of sheets - ANSWER-alt h o m
change zoom - ANSWER-alt w q, alt v z
Freeze Panes - ANSWER-Click the cell below the desired row and to the right of the desired column where you want to freeze panes and hit alt w f f. To unfreeze panes, press alt w f f again.
Splitting Panes - ANSWER-Click the cell below the desired row and to the right of the desired column where you want to split panes and hit alt w s. To un-split panes, click alt w s again. Press F6 to move from pane to pane in a clockwise direction; press Shift F6 to move from pane to pane in a counter-clockwise direction.
undo/redo - ANSWER-ctrl z/ctrl y
highlight characters one at a time in a cell - ANSWER-Shift arrow-keys
highlight a continuous range in a cell - ANSWER-Shift Ctrl arrow-keys
Clears a cell - ANSWER-alt h e (a=all,f=format,c=content,m=comments)
paste to columns - ANSWER-ctrl r
paste to rows - ANSWER-ctrl d
insert a comment - ANSWER-Shift + F2
add a row - ANSWER-alt i r, ctrl shift +
add a column - ANSWER-alt i c, ctrl shift +
delete row - ANSWER-alt h d r/highlight and ctrl -
delete column - ANSWER-alt h d c/highlight and ctrl -
multiply cells - ANSWER-In another cell, enter the number 1000. Copy this cell, then highlight your list of prices. Press Alt e s m. This will multiply each dollar price on your list by 1000.
name cells - ANSWER-ctrl f3
anchoring cells - ANSWER-f4
name worksheet - ANSWER-alt h o r
add/delete worksheets - ANSWER-alt h i s, alt e l
Group columns - ANSWER-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.
auditing cells - ANSWER-f2, ctrl [ and ]
trace precedents - ANSWER-Alt M P
trace dependents - ANSWER-Alt M D
remove trace arrows - ANSWER-alt m a a
SUM function - ANSWER-One argument that adds a range of cells.
AVERAGE function - ANSWER-One argument that finds the average of a range of cells.
IF Function - ANSWER-if(x,y,z) x=criteria y=output if true z= output if false
IFERROR - ANSWER-=IFERROR(value,value_if_error)
EOMONTH - ANSWER-=EOMONTH(start_date,months)
EDATE - ANSWER-EDATE(start_date,months)
YEARFRAC - ANSWER-YEARFRAC(start_date, end_date, basis) returns the proportion of the year between two given dates, the start_date and end_date. 0 or omitted = U.S. 30/360 1 = actual/actual
2 = actual/360
3 = actual/365
4 = European 30/360
DATE - ANSWER-DATE(year,month,day). Can be used with day(serial number),month,year
=IF(C1,C2,C3) - ANSWER-see if there is anything in cell C1, and if
there was, it would output C2, otherwise C3.
ISNUMBER & ISTEXT - ANSWER-Checks if cell has numbers or text
AND/OR FUNCTION - ANSWER-=AND(logical1,logical2, ...) evaluates if everything is true. OR evaluates if at least one is true.
HLOOKUP - ANSWER-=HLOOKUP(look up value, table range, row number) 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 - ANSWER-=VLOOKUP(lookup value, table range, column number) 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.
OFFSET - ANSWER-=OFFSET(reference,rows,cols,height,width).Subtract 1.
INDEX - ANSWER-=INDEX(array,row_num,column_num).
CHOOSE - ANSWER-=CHOOSE(index_num,value1,value2,value3,...).
MATCH - ANSWER-=MATCH(lookup_value,lookup_array,match_type).
Drop down menu - ANSWER-alt d l
Building a vertical data table - ANSWER-• 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." Important: Data tables must always be in the same worksheet as the input variables.
SUMPRODUCT - ANSWER-=SUMPRODUCT(array1,array2,array3, ...) multiplies corresponding components in two or more arrays, and returns the sum of those products.
SUMIF - ANSWER-=SUMIF(range, criteria, sum range) adds the cells specified by a given criteria.
COUNT, COUNTA, COUNTIF - ANSWER-• COUNT =COUNT(value1, value2, ..) counts the number of cells that contain numbers within
the list of arguments.
• 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 =COUNTIF(range1, criteria1, range2, critera2) allows for multiple criteria - similar to the SUMIFS function.
PV - ANSWER-=PV(rate, nper, pmt, fv, type)
NPV - ANSWER-=NPV(rate, value1, value2, ...) 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).
XNPV - ANSWER-=XNPV(rate, values, dates)
IRR/XIRR - ANSWER-=IRR(values, guess),=XIRR(values, dates, guess)
Flash fill - ANSWER-ctrl e
Sort - ANSWER-alt d s
Autofilter - ANSWER-alt a t
pivot table - ANSWER-alt n v/alt n v t
record macro - ANSWER-alt l r
paste formulas - ANSWER-alt esf
paste format - ANSWER-alt est
paste multiply - ANSWER-Alt esm
convert numbers to negative - ANSWER-alt ess [Show Less]
