Excel

Question 1

comment in excel

Answer 1

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

Question 2

flick tabs in browser

Answer 2

ctrl + tab

Question 3

paste special formula

Answer 3

alt + h + v + f

Question 4

Delete Blank Rows and Columns in Excel

Answer 4

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.

Question 5

compare rows and copy only matching ones to new sheet

Answer 5

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

Question 6

moving average

Answer 6

data->data analysis,

Question 7

remove empty rows

Answer 7

az filter and select

Question 8

accept auto suggest

Answer 8

tab

Question 9

Round to nearest 0.5

Answer 9

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) .

Question 10

use of array in function large()

Answer 10

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}).

Question 11

conditional format of cells

Answer 11

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

Question 12

absolute cell reference toggles

Answer 12

F4

Question 13

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

Answer 13

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

Question 14

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

Answer 14

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.

Question 15

solver

Answer 15

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

Question 16

MS Windows shift apps

Answer 16

alt + tab