insert hyperlink to a cell | | | Ctrl + K |
| mouse to lower left corner | Ctrl+left click | Scroll to the first/ last sheet |
show how many sheets the workbook have | | | mouse to lower left corner, right click |
go to the first row of one column | | double click the top edge of any cell of that column | Ctrl + up arrow |
switch worksheets | | | Ctrl + page up/ page down |
go the first cell of the sheet | upper left corner | | Ctrl + Home |
jump to the last cell of this work sheet | | | Ctrl + End |
go to the end of one column | left /right is the same | double click the bottom edge of any cell of that column | Ctrl + down arrow |
move one screen to the right/ left | for ultra wide sheets | | Alt + page down/
Alt + page up |
| if hold arrow | will go to the very end | |
insert rows | | | go the left most row number --> right click --> insert |
merge cells | | | Home -> merge |
wrap cells | | | Home -> Wrap text |
add new sheets to the left of the current sheet | | | select current sheet and press
Shift + F11 |
add new sheets to the right of the current sheet | | | select current sheet -> right click the '+' icon on the right |
group select multiple sheets | can then right click to insert contents | | on the bottom -> Shift + left click sheets
Ctrl + any single sheet |
create a copy sheet | | | select sheet Ctrl + Drag to target position ;
right click sheet -> move or copy |
color sheet | | | right click sheet -> Tab color |
unhide hidden rows / columns | | right 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/ unhide | | | right click sheet/sheets |
lock workbook manipulation | | | Review -> 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 window | | | view -> switch window |
open the same workbook in a new window | change any of the two will change all | | view -> new window
|
mouse drag/ copy | | | move: select -> drag
copy: select-> Ctrl+drag |
move/ drag to another worksheet or self worksheet in new wind | | | fast drag selected data to another window |
mouse
Insert row/ column | | | mouse +
Shift |
make change to multiple sheets | | | select (group) multiple sheets, then make changes |
best fit space of multiple columns /rows | | | select columns/ rows first, then double chick any edges
(if grouped will change all) |
copy interactive data/ formula/ results to another sheet/place | | | copy then go to destination, right click ->paste special -?> paste link |
| then drag e.g. right, that cell will be mapped to the original worksheet | can 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 worksheet | use & to paste cellref to string
string must be quoted with double quote "string" |
display formula in another cell | single cell, select, F2 | Ctrl + `
or formuls -> show formulas | !ERROR! undefined variable 'formulatext' |
manipulate multiple sheets. sum(one cell from a sheet for 100 sheets) | "3D" formula | | formula ->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 comfirm | | | Ctrl + 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 table | design tab | | |
navigate multiple workbooks | | | view -> switch windows |
add anything to quick access toolbar | | | right click target button, add to quick access toolbar |
navigate tabs to the right | | Ctrl+F6 | Ctrl+Tab |
navigate tabs to the left | | Ctrl+Shift+F6 | Ctrl+Shift+Tab |
open a new workbook | | | Ctrl+N |
close all open files | | | hold Shift and mouse right click upper right 'X' |
fill multiple selected cells with formula | | from formula cell, Shift arrow select cell, then Ctrl+ D/ Ctrl + R | from formula cell, Shift arrow select cells, press F2 go back to formula cell, then Ctrl+ Enter |
paste special | | | Ctrl + Alt + E
or Alt + E + S |
format cells | | | Ctrl + Shift + F |
exam data link from other workbook | | | data -> edit links |
select all sheets and find | | | bottom 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 |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |