excel shortcuts 1

excel basics

1upper  right cornerribbon display options
2upper left cornerquick access bar can choose show below the ribbon
3undo/ redoCtrl + Z/ Ctrl + Y in other platform --redo : Ctrl+ Shift + Z
4fix column width for double clickHome -> Wrap Text
5switch sheetsCtrl + Page up/down
6bold/italic/underlineCtrl + B/I/U
7find/replaceCtrl + F/H
8texts are left aligned / numbers are right aligned
9insert comment to a cellShift + F2
10show all commentsReview -> show all comments
11save as F12
12count non-empty cells =COUNTA(selected cells)
13count numbers alone =COUNT(selected cells)
14find second largest NO. =large(cells, 2)
15all column/ rows to same widthselect all target columns/ rows -> drag any edges
16"Enter" within a cell
new paragraph
Alt + Enter
17format cellCtrl + 1
Ctrl + Shift + F
18convert to tableCtrl + T
19slicer have table already
insert slicer
20keep ratio of inserted shapeshold Shift
21print with titles (columns /rows)page layout -> print titles
22page break previewbottom right corner
23print area selectionpage layout -> print area
24create a chart in a new sheetselect data then press F11
25create chart in the same sheetselect data then Alt + F1design tab
26sparklinesquick analysis -> sparklines
insert -> sparklines
27divide sheet by rowsgo to column one -> splitdouble click to cancel
28divide sheet by columnsgo to row one -> split
29only see summary of large datadata -> group-> auto outline
30hide/create outline symbolCtrl + 8
31create a new sheet on the leftShift + F11
32if func return string values must be quoted by double quote "string"
33vlookup for approx match,
input table
value must be ascending order
34 =function, auto completeTab
35vlookup default value is 1 approx matchthe fourth para set to 0 to exact match
36 =countif() =countif() =sumif() =averageif()
37format cell -> protectionReview -> show all comments
38when save add passwdsave as -> tools -> general options
39track changesReview -> track changes
40sortdata -> sort
41subtotal data -> subtotal
42Only select visible data
(before copy, or etc)
Alt + ;
43Refresh (for pivot table)Alt + F5
44pivot table see detailsdouble click
45pivot chartsinsert -> pivot chart
pivot table Alt+F1
46what if analysisdata -> what if analysis ->
goal seek
47solverdata -> solver
options -> add-ins
48scenario what-if analysis -> scenario manager
49sensitivity analysiswhat-if analysis -> data table
50strike throughCtrl + 5
51select(highlight) cells with formulashome -> find and select -> formulas
52Macrosview -> Macros -> record
and set shortcut
53select(highlight) cells only contains numbers/ text/ othershome -> find and select -> go to special
54stop recording Macrobottom left corner stop
55assign Macro to a button(shape, icons, pictures, etc)right click shape -> assign macro
56repeat last one actionAlt + Enter
57redoCtrl + Y
or F4

multiple worksheets/workbooks managing

description  shortcuts
insert hyperlink to a cellCtrl + K
mouse to lower left cornerCtrl+left clickScroll to the first/ last sheet
show how many sheets the workbook havemouse to lower left corner, right click
go to the first row of one columndouble click the top edge of any cell of that columnCtrl + up arrow
switch worksheetsCtrl + page up/ page down
go the first cell of the sheetupper left cornerCtrl + Home
jump to the last cell of this work sheetCtrl + End
go to the end of one columnleft /right is the samedouble click the bottom edge of any cell of that columnCtrl + down arrow
move one screen to the right/ leftfor ultra wide sheetsAlt + page down/
Alt + page up
if hold arrow will go to the very end
insert rowsgo the left most row number --> right click --> insert
merge cellsHome -> merge
wrap cellsHome -> Wrap text
add new sheets to the left of the current sheetselect current sheet and press
Shift + F11
add new sheets to the right of the current sheetselect current sheet -> right click the '+' icon on the right
group select multiple sheetscan then right click to insert contentson the bottom -> Shift + left click sheets
Ctrl + any single sheet
create a copy sheetselect sheet Ctrl + Drag to target position ;
right click sheet -> move or copy
color sheetright click sheet -> Tab color
unhide hidden rows / columnsright click rows / columns -> unhide

or double click row/column's edge
select all -> upper left triangle ->

or select certain rows / columns
sheets can be hide/ unhideright click sheet/sheets
lock workbook manipulationReview -> Protect Workbook
if open multiple workbooks (windows), arrange positions(view -> New window)
view -> Arrange all (current window will be the main)
hide ribbon double click current ribbon
switch windowview -> switch window
open the same workbook in a new windowchange any of the two will change allview -> new window
mouse drag/ copymove: select -> drag
copy: select-> Ctrl+drag
move/ drag to another worksheet or self worksheet in new windfast drag selected data to another window
Insert row/ column
mouse +
make change to multiple sheetsselect (group) multiple sheets, then make changes
best fit space of multiple columns /rowsselect columns/ rows first, then double chick any edges
(if grouped will change all)
copy interactive data/ formula/ results to another sheet/placecopy then go to destination, right click ->paste special -?> paste link
then drag e.g. right, that cell will be mapped to the original worksheetcan combine with double click + F4
absolute address
reference to another sheet!ERROR! illegal character '!'
show formula (content after '=')put a space before =
create formula
string concat
!ERROR! illegal character '&'cellref reference the cell with value of the name of destination worksheetuse & to paste cellref to string

string must be quoted with double quote "string"
display formula in another cell single cell, select, F2Ctrl + `
or formuls -> show formulas
!ERROR! undefined variable 'formulatext'
manipulate multiple sheets. sum(one cell from a sheet for 100 sheets)"3D" formulaformula ->autosum
within =sum()
sheet selection, first sheet, then Ctrl chick right arrow will go to the last sheet, Shift click
do not want to move the active cell but comfirmCtrl + Enter
Consolidate Data -> Consolidate
grand total select cells with blank edges
Alt + =
or formulas -> autosum
input value to multiple cells at once select multiple cells, input value-> Ctrl + Enter
pivot table wizard or quick access toolbar
customize toolbar
more comments
Alt+ D then P
multiple consolidation ranges
with pivot tabledesign tab
navigate multiple workbooksview -> switch windows
add anything to quick access toolbarright click target button, add to quick access toolbar
navigate tabs to the rightCtrl+F6Ctrl+Tab
navigate tabs to the leftCtrl+Shift+F6Ctrl+Shift+Tab
open a new workbookCtrl+N
close all open fileshold Shift and mouse right click upper right 'X'
fill multiple selected cells with formulafrom formula cell, Shift arrow select cell, then Ctrl+ D/ Ctrl + Rfrom formula cell, Shift arrow select cells, press F2 go back to formula cell, then Ctrl+ Enter
paste specialCtrl + Alt + E
or Alt + E + S
format cellsCtrl + Shift + F
exam data link from other workbookdata -> edit links
select all sheets and findbottom left corner, right click any sheet -> select all sheets, then upper left corner right click select all cells, then Ctrl + F (Home -> Find & select)
if linkage to another workbook, formula contains '['
pivot table insert pivot table
clear content delete