excel shortcuts 1
excel basics
| description | shortcuts | comments | |
|---|---|---|---|
| 1 | upper right corner | ribbon display options | |
| 2 | upper left corner | quick access bar | can choose show below the ribbon |
| 3 | undo/ redo | Ctrl + Z/ Ctrl + Y | in other platform --redo : Ctrl+ Shift + Z |
| 4 | fix column width for double click | Home -> Wrap Text | |
| 5 | switch sheets | Ctrl + Page up/down | |
| 6 | bold/italic/underline | Ctrl + B/I/U | |
| 7 | find/replace | Ctrl + F/H | |
| 8 | texts are left aligned / numbers are right aligned | ||
| 9 | insert comment to a cell | Shift + F2 | |
| 10 | show all comments | Review -> show all comments | |
| 11 | save as | F12 | |
| 12 | count non-empty cells | =COUNTA(selected cells) | |
| 13 | count numbers alone | =COUNT(selected cells) | |
| 14 | find second largest NO. | =large(cells, 2) | |
| 15 | all column/ rows to same width | select all target columns/ rows -> drag any edges | |
| 16 | "Enter" within a cell new paragraph | Alt + Enter | |
| 17 | format cell | Ctrl + 1 Ctrl + Shift + F | |
| 18 | convert to table | Ctrl + T | |
| 19 | slicer | have table already insert slicer | |
| 20 | keep ratio of inserted shapes | hold Shift | |
| 21 | print with titles (columns /rows) | page layout -> print titles | |
| 22 | page break preview | bottom right corner | |
| 23 | print area selection | page layout -> print area | |
| 24 | create a chart in a new sheet | select data then press F11 | |
| 25 | create chart in the same sheet | select data then Alt + F1 | design tab |
| 26 | sparklines | quick analysis -> sparklines insert -> sparklines | |
| 27 | divide sheet by rows | go to column one -> split | double click to cancel |
| 28 | divide sheet by columns | go to row one -> split | |
| 29 | only see summary of large data | data -> group-> auto outline | |
| 30 | hide/create outline symbol | Ctrl + 8 | |
| 31 | create a new sheet on the left | Shift + F11 | |
| 32 | if func return string values must be quoted by double quote "string" | ||
| 33 | vlookup for approx match, input table | value must be ascending order | |
| 34 | =function, auto complete | Tab | |
| 35 | vlookup default value is 1 approx match | the fourth para set to 0 to exact match | |
| 36 | =countif() | =countif() | =sumif() =averageif() |
| 37 | format cell -> protection | Review -> show all comments | |
| 38 | when save add passwd | save as -> tools -> general options | |
| 39 | track changes | Review -> track changes | |
| 40 | sort | data -> sort | |
| 41 | subtotal | data -> subtotal | |
| 42 | Only select visible data (before copy, or etc) | Alt + ; | |
| 43 | Refresh (for pivot table) | Alt + F5 | |
| 44 | pivot table see details | double click | |
| 45 | pivot charts | insert -> pivot chart pivot table Alt+F1 | |
| 46 | what if analysis | data -> what if analysis -> goal seek | |
| 47 | solver | data -> solver options -> add-ins | |
| 48 | scenario | what-if analysis -> scenario manager | |
| 49 | sensitivity analysis | what-if analysis -> data table | |
| 50 | strike through | Ctrl + 5 | |
| 51 | select(highlight) cells with formulas | home -> find and select -> formulas | |
| 52 | Macros | view -> Macros -> record and set shortcut | |
| 53 | select(highlight) cells only contains numbers/ text/ others | home -> find and select -> go to special | |
| 54 | stop recording Macro | bottom left corner stop | |
| 55 | assign Macro to a button(shape, icons, pictures, etc) | right click shape -> assign macro | |
| 56 | repeat last one action | Alt + Enter | |
| 57 | redo | Ctrl + Y or F4 | |
| 58 | |||
| 59 | |||
| 60 | |||
| 61 | |||
| 62 | |||
| 63 | |||
| 64 | |||
| 65 | |||
| 66 | |||
| 67 | |||
| 68 | |||
| 69 | |||
| 70 | |||
| 71 | |||
| 72 | |||
| 73 | |||
| 74 | |||
| 75 | |||
| 76 | |||
| 77 | |||
| 78 | |||
| 79 | |||
| 80 | |||
| 81 | |||
| 82 | |||
| 83 | |||
| 84 | |||
| 85 | |||
| 86 | |||
| 87 | |||
| 88 | |||
| 89 | |||
| 90 | |||
| 91 | |||
| 92 | |||
| 93 | |||
| 94 | |||
| 95 | |||
| 96 | |||
| 97 | |||
| 98 | |||
| 99 | |||
| 100 |
multiple worksheets/workbooks managing
| description | shortcuts | ||
|---|---|---|---|
| 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 | ||