Excel用法参数详细说明,Excel常用命令一览表,Excel最全使用文档, 使用手册。

Excel 2011 for Mac


Freeze header rows

Choose “Freeze Panes” or “Freeze Top Row” from the “Layout” tab on a Mac. (It is over on the far right, under the “Window” sub menu.) Note, you need to select the row below the row you want frozen - selecting the top (header) row on its own won’t do what you want. Select the row below it.

Quickly move around

Select quickly

Many tricks:

Copy formulae quickly

  1. Click on a cell and enter a formula.
  2. Drag the right-bottom corner to expand the cells computed. Alternatively, double-click the bottom-right corner to fill a column quickly.

Absolute references

Use a $ in the cell coordiantes, e.g. $c2, c$2, or $c$2 depending on your needs.

Conditional formatting

Under the “Home” tab there is a “Conditional Formatting” menu.

Convert forumale to values

Right click the target (cell, column, or row) and choose “Paste Special” and then select “Values” from the menu.

Transpose a row or column

Copy a row or column and then right click the target and choose “Paste Special” and then toggle the “Transpose” button.


Search and replace

There is a search box with a drop-down menu in the upper right-hand corner of the sheet. We may also use Command-f to bring up the menu.

Find a value in a range and return its location

Use, e.g., =MATCH( <value>, <range, e.g. a1:a10>, 0) where the trailing 0 forces MATCH to give us back the position of the value itself.

Get a value by coordinate

Get the nth smallest or largest

Use, e.g.,

Find a location in a range and return its value

Use VLOOKUP, e.g.

Similarly, there is an HLOOKUP function.

Filtering and sorting

Using PivotTables

Using array formulae

Using the Solver

Removing Extraneous Punctuation

Clean up tweets with the following sequence:

Break up a string into tokens

Sample from the normal distribution


(Note, we use the standard deviation here and not the adjusted (N-1) deviation.)

Other useful statistical functions