Excel 2011 for Mac
- “Data Smart” by John W. Foreman
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 a colum (or row) and then just drag to select many.
Copy formulae quickly
- Click on a cell and enter a formula.
- Drag the right-bottom corner to expand the cells computed. Alternatively, double-click the bottom-right corner to fill a column quickly.
$ in the cell coordiantes, e.g.
$c$2 depending on your needs.
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.
- Select target cells and then go to the “Charts” tab and select a chart. Sections of the chart may be right-clicked to bring up formatting menus.
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
=MATCH( <value>, <range, e.g. a1:a10>, 0) where the trailing
MATCH to give us back the position of the value itself.
Get a value by coordinate
- Use, e.g.
=INDEX(a1:b10, 1, 1)to get the upper leftmost item,
=INDEX(a1:b10, 3, 2)to get the item in the third row, second colum of the range
- Use, e.g.
=OFFSET(<location>, <+row>, <+col>), e.g.
=OFFSET(a1, 3, 0)to get an item three rows down from
a1and in the same column.
nth smallest or largest
=SMALL(c1:c10, 1)for the smallest item in
=SMALL(c1:c10, 3)for the third smallest item in
=LARGE(c1:c10, 2)for the second largest item in
Find a location in a range and return its value
=VLOOKUP(<value>, <table or array>, <column index>, [<range lookup>])
=VLOOKUP(B2, MySheet!$A$2:$B$10, 2, FALSE)where
2is the relative column we want the value retrieved from and
FALSEmeans we won’t accept approximate matches.
Similarly, there is an
Filtering and sorting
- Select a set of rows or columns.
- Click on the “Data” tab and press the “Filter” button (under “Sort & Filter”) to enable “auto-filtering”.
- Once auto-filtering is enabled, we have drop down menus we can use to filter.
- We can disable the filtering by toggling the “Filter” button in the “Data” tab or by just working with the filtering drop-down menus.
- Note that with the filtering drop-downs, we can also sort the set with the filtering layer applied. For more advanced sorting, use the “Sort” button under the “Data” tab with all the data selected. There is a small drop-down menu on “Sort”, and in that menu there is a “Custom Sort” option.
- Select the data region (e.g.,
- From the Data tab, press the
PivotTablebutton and select for Excel to create a new sheet with a pivot table.
- Typically, the table is pre-populated. It is safe to uncheck all the items in the builder first. (You may also remove items by dragging them out the areas and “throwing them away.”)
- Then, construct the table by dragging items from the
Field namearea to the
Row Labelsand then to the
Valuesareas. Click the
ibutton on the item in the
Valuesareas to select an aggregation function (e.g.,
- A typical approach is drag the category of “interest” to the
Row Labelsarea. Then, drag the category you would like to aggregate over to the
Valuesarea and select an aggregation method. Finally, select the category you would like to use as a breakdown to the
Using array formulae
- By default, Excel functions return single values. In order to get them to return an
array (e.g., the output of the
TRANSPOSE()function), you need to enter the calculation with
cmd-returnrather than just
=SUMPRODUCT(B2:B15,TRANSPOSE('Fee Schedule'!B2:O2))must be enterd with
SUMPRODUCT()will fail because
TRANSPOSE()will return a single value.
Using the Solver
Solvermust be added if it isn’t already. Go to
Solver.xlamfrom the menu. This will cause a
Solverbutton to appear in the
Analysissection of the
- Click the
- Fill the
Set Objectivecell and select the
Tovalues for it.
- Set the range for the
- Then, add constraints (values must remain integers, some other cell that is a sum of the variable cells must remain fixed, etc.)
- Select a Solving Method (sublte).
Removing Extraneous Punctuation
Clean up tweets with the following sequence:
=SUBSTITUTE(B2, ". ", " ")
=SUBSTITUTE(C2, ": ", " ")
=SUBSTITUTE(D2, "?", " ")
=SUBSTITUTE(E2, "!", " ")
=SUBSTITUTE(F2, ";", " ")
=SUBSTITUTE(G2, ",", " ")
Break up a string into tokens
- In the Data tab, there is a powerful
Text to Columnsbutton.
Sample from the normal distribution
=NORMINV(RAND(), MEAN, STANDARD_DEVIATION)
(Note, we use the standard deviation here and not the adjusted (N-1) deviation.)
Other useful statistical functions
- Compute the total sum of squares (sum of the squared deviations of each value in the outcome sample in a linear regression from the average value of the outcome sample):