pandas -4 grouping
Categoricals and groupby
Boolean filter and count
How to make useful and fun interactive data visualization web apps and how to deploy them online for public access?
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 | ||