Add/delete worksheets
alt i w
Move back and forth to different worksheets
ctrl page up and down
Open a new file
alt f n, ctrl
... [Show More] n
open a file
alt f o, ctrl o
save a file
alt f s, ctrl s, alt f a(save as)
print a file
alt f p, ctrl p
Navigating to the toolbars without the mouse
Hit alt and the corresponding letter and then use tab and shift-tab to navigate
Access Excel options
alt f t, alt t o
Bold
Ctrl B
Highlight Column
ctrl spacebar
Auto fit column width
alt h o i, alt o c a
Enter column width
alt h o w, alt o c w
Highlight Row
Shift Spacebar
auto fit row height
alt h o a, alt o r a
Enter row height
alt h o h, alt o r e
Format cells menu
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
alt h v s , alt e s
Move to different sheet
ctrl tab
move the location of sheets
alt h o m
change zoom
alt w q, alt v z
Freeze Panes
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
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
ctrl z/ctrl y
highlight characters one at a time in a cell
Shift arrow-keys
highlight a continuous range in a cell
Shift Ctrl arrow-keys
Clears a cell
alt h e (a=all,f=format,c=content,m=comments)
paste to columns
ctrl r
paste to rows
ctrl d
insert a comment
Shift + F2
add a row
alt i r, ctrl shift +
add a column
alt i c, ctrl shift +
delete row
alt h d r/highlight and ctrl -
delete column
alt h d c/highlight and ctrl -
multiply cells
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
ctrl f3
anchoring cells
f4
name worksheet
alt h o r
add/delete worksheets
alt h i s, alt e l
Group columns
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
f2, ctrl [ and ]
trace precedents
Alt M P
trace dependents
Alt M D
remove trace arrows
alt m a a
SUM function
One argument that adds a range of cells.
AVERAGE function
One argument that finds the average of a range of cells.
IF Function
if(x,y,z) x=criteria y=output if true z= output if false
IFERROR
=IFERROR(value,value_if_error)
EOMONTH
=EOMONTH(start_date,months)
EDATE
EDATE(start_date,months)
YEARFRAC
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
DATE(year,month,day). Can be used with day(serial number),month,year
=IF(C1,C2,C3)
see if there is anything in cell C1, and if
there was, it would output C2, otherwise C3.
ISNUMBER & ISTEXT
Checks if cell has numbers or text
AND/OR FUNCTION
=AND(logical1,logical2, ...) evaluates if everything is true. OR evaluates if at least one is true.
HLOOKUP
=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
=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
=OFFSET(reference,rows,cols,height,width).Subtract 1.
INDEX
=INDEX(array,row_num,column_num).
CHOOSE
=CHOOSE(index_num,value1,value2,value3,...).
MATCH
=MATCH(lookup_value,lookup_array,match_type).
Drop down menu
alt d l [Show Less]