Excel Flashcards Preview

keyboard shortcuts > Excel > Flashcards

Flashcards in Excel Deck (15):
1

comment in excel

create/edit: shift + f2, delete: shift+f10 then press m,

2

flick tabs in browser

ctrl + tab

3

paste special formula

alt + h + v + f

4

Delete Blank Rows and Columns in Excel

Press [F5].
In the resulting Go To dialog box, click Special.
Click the Blanks option and click OK. Doing so selects the blank cells (what you might think of as rows) in the selected range. ...
Now you're ready to delete the selected cells.

5

compare rows and copy only matching ones to new sheet

=VLOOKUP(A2, Sheet2!A$2:B$9, 2,FALSE)

6

moving average

data->data analysis,

7

remove empty rows

az filter and select

8

accept auto suggest

tab

9

Round to nearest 0.5

To round a number down to nearest 0.5, use the FLOOR function, for example =FLOOR(A2, 0.5) .
To round a number up to nearest 0.5, use the CEILING function, for example =CEILING(A2, 0.5) .
To round a number up or down to nearest 0.5, use the MROUND function, e.g. =MROUND(A2, 0.5) .

10

use of array in function large()

the formula to identify the k-th largest value in an array is =large(A1:Z1,k). To find the top 10, you could enter it as an array formula, =LARGE(A1:Z1,{1;2;3;..10}).

11

conditional format of cells

search for value "周五" ->conditional format->select all->select color

12

absolute cell reference toggles

F4

13

=COUNTIF(B2:B5,">=32")-COUNTIF(B2:B5,">85")

Counts the number of cells with a value greater than (>) or equal to (=) 32 and less than (

14

=COUNTIF(B2:B5,"<>"&B4)

Counts the number of cells with a value not equal to 75 in cells B2 through B5. The ampersand (&) merges the comparison operator for not equal to (<>) and the value in B4 to read =COUNTIF(B2:B5,"<>75"). The result is 3.

15

solver

文件
选项
加载项
Excel 加载项 转到
tick three boxes
After you load the Solver Add-in, the Solver command is available in the Analysis group on the Data tab