eBook
Available on Compatible NOOK devices, the free NOOK App and in My Digital Library.
Related collections and offers
Overview
Product Details
| ISBN-13: | 9781615473380 |
|---|---|
| Publisher: | Holy Macro! Books |
| Publication date: | 03/01/2014 |
| Series: | Excel for Professionals series |
| Sold by: | Barnes & Noble |
| Format: | eBook |
| Pages: | 250 |
| File size: | 39 MB |
| Note: | This product may take a few minutes to download. |
About the Author
Read an Excerpt
Excel 2013 for Scientists
By Gerard M. Verschuuren
Holy Macro! Books
Copyright © 2014 Dr. Gerald M. VerschuurenAll rights reserved.
ISBN: 978-1-61547-338-0
CHAPTER 1
Navigation in Excel
Excel 2013 has plenty of space for your scientific work. Each workbook (or .xlsx file) can hold an unlimited number of worksheets (provided that your computer memory permits), and each worksheet has a capacity of 1,048,576 rows by 16,384 columns. Hopefully, you won't use all this space before retirement.
Scientific spreadsheets can be huge — filled with many numbers. So you need ways to quickly navigate around and to create formulas for giant ranges of cells in a swift and efficient way. That's what this chapter is about.
Most sheets in this book have a modest size, so it is easy to practice with them. But in real life, you probably deal with much larger collections of data. The basic techniques discussed in this chapter will benefit you even more when your data sets become larger and larger.
Navigation Shortcuts
The following keystrokes are some important navigation shortcuts:
• Ctrl+Home takes you to the origin of the sheet, which is usually cell A1.
• Ctrl+Arrow key jumps between section borders. (A border is an empty row and/or column.)
• Ctrl+Shift+Arrow key jumps and selects what is between the section borders.
• Shift+Arrow key expands or reduces whatever has been selected.
Let's use Figure 1.1 to see how these shortcuts work. Based on Figure 1.1, the following would happen:
[ILLUSTRATION OMITTED]
Note
All files used in this book are available from www.genesispc.com/Science2013.htm, where you can find each file in its original version (to work on) and in its finished version (to check your solutions).
• Starting in A1: Pressing Ctrl+Down Arrow takes you to A24 and then to A1048576. Pressing Ctrl+Up Arrow takes you back, with the same stops on the way.
• Starting in B1: Repeatedly pressing Ctrl+Down Arrow jumps to B10, B14, B23, and finally the end.
• Starting in B1: Pressing Ctrl+Shift+Down Arrow selects the entire range B1:B10. Pressing Shift+Down Arrow once expands the selection with one more cell. Instead pressing Shift+Up Arrow shortens the selection by one cell. The Shift key keeps all in-between cells included in the selection.
• Starting in J1: Typing J24 in the Name box just above column A and then pressing Shift+Enter selects all cells between J1 and J24 (thanks to the Shift key). With the range J1:J24 selected, typing =ROW() in the formula bar and then pressing Ctrl+Enter causes all the selected cells to be filled with this formula (thanks to the Ctrl key).
Creating Formulas
Figure 1.2 shows an example of how to create formulas:
1. Select cell F11 and press the fx button (located just in front of the formula bar).
2. Choose the function STDEV.S and start the first argument by clicking cell F10 and then pressing Ctrl+Shift+Up Arrow; this selects the entire range above cell F11. Often, Excel finds the correct range automatically — but not necessarily so; when it does not, you have to be in charge yourself!
3. Press OK in the dialog box, and the cell shows the actual standard deviation of these cells.
4. In cell B24, use the function COUNTIF and follow these steps:
1. For the first argument, click cell B23 and then press Ctrl+Shift+Up Arrow.
2. For the second argument, type >=5 (which changes into a string: ">=5").
3. Finalize the functions by pressing Ctrl+Enter.
Figure 1.3 shows a quick technique for calculating the sum or mean of certain measurements per strain of bacteria and per test:
1. Select cell A1 (or press Ctrl+Home).
2. Press Ctrl+Shift+Down Arrow and then Ctrl+Shift+Right Arrow.
3. You need an extra row and column for the calculations of the means, so to expand the selection with an extra row and column, press Shift+Down Arrow and Shift+Right Arrow.
4. From the Formulas tab, use the drop-down button next to the Sum button, and choose the option Sum, Average, Min, or Max.
All calculations are done automatically at the end of each numeric row or column. You could do this more tediously; go ahead if you like that route better!
Note
There is also a shortcut key for the SUM function: Alt+=. There is no such feature for the mean.
Figure 1.4 shows the same data set as Figure 1.3, but this time in the dedicated table structure available in Excel 2013. Follow these steps:
1. Click the Table button on the Insert tab to create a table structure with a striping pattern for easy reading.
2. Specify whether your table has headers (also called labels) on top or not.
3. After the table structure is implemented, use the Design tab to change table settings as desired.
4. To create calculations again for sum and mean, use the same technique you used earlier.
CHAPTER 2The Fill Handle
One of the best-kept secrets in Excel is the fill handle. This tool allows you to copy cells over a contiguous range of cells or to fill such a range with a series of specific values. In addition, it helps you copy formulas over huge ranges.
The fill handle is located in the lower-right corner of your selected cell(s). Whenever you move your mouse to that point, the cursor changes to a small + sign (not to be confused with a crosshairs). That very spot holds the fill handle.
Figure 1.5 shows examples that help you explore some of the features of the fill handle:
• Cell A2: Click and drag the fill handle downward to cell A6 in order to stop at Friday. If you keep going, the fill handle goes into Saturday, and so on. If you stop at Friday and then start the fill handle again (with A2:A6 still selected), you can just copy the previous section by holding the Ctrl key down until you are finished.
• Cell B2: To insert the number 8 in column B for every day of the week, double-click the fill handle of cell B2. A double-click on the fill handle copies the content down to the first empty cell in the previous or next column. So the double-click does not work when there is no column with data to the immediate left or right.
• Cell C2: Double-clicking the fill handle of cell C2 gives you a series of 1s. To change this into an incrementing series, click the button that has popped up and select®Fill Series. Now the series increments by 1.
• Cells D2 and D3: For a series that needs to increment by a value different from 1, create a pattern in the first two cells, select both cells, and then double-click the fill handle.
• Cell E2: If you do not want to create a pattern ahead of time, double-click the fill handle of the first cell only. Now go to the Fill button drop-down (located under the [summation] button on the Home tab) and then choose the option Series. Specify any step value (for example, 2).
• Cell F2: To multiply D2 by E2, follow these steps (you will appreciate them someday!):
1. Select cell F2.
2. Type the equals sign (=).
3. Press Left Arrow twice to get to D2 (that is, do not type D2).
4. Type the multiplication sign (*).
5. Press the Left Arrow key once to get to E2.
6. To finish, press Ctrl+Enter (not just Enter).
Note
What is the advantage of pressing Ctrl+Enter instead of Enter only? You stay in the same cell, so you can just double-click the fill handle to copy the formula all the way down. (Otherwise, you would have to go back to the previous cell first.) Another advantage is this: When you discover a mistake in your formula — after you have copied the formula down and all the cells are still selected — you just correct your formula in the formula bar once, and hitting Ctr+Enter will propagate the correction in all the selected cells
• Cell G2: If you always work with the same analysts in the same order, type their names once, select them all, and double-click the fill handle. If you want to use this same list over and over again — especially if it's a long list — use the following technique:
1. If you have a listing on your sheet already, select that listing first.
2. Click the File tab in the left-top corner.
3. At the bottom of the new box, select Options.
4. In the left panel, choose Advanced.
5. Click Edit Custom Lists (in the section General).
6. Accept the highlighted list.
7. Click the Import button.
8. Click OK twice.
Now you can use this list anywhere in Excel. Just type the first name of this (potentially long) list and double-click the fill handle — provided that there is a column with contents to the left or right. Excel does the rest!
CHAPTER 3Relative vs. Absolute Cell References
Each cell on a sheet has a certain position. When you copy a cell that contains a formula to another position, the formula's cell references automatically adjust. Those references are called relative. Sometimes, you do not want formula references to adapt to their new location; in that case, you "lock" them and make them absolute.
To see how relative and absolute cell references work, take a look at Figure 1.6. Cell C1 has a formula in it: =A1*B1. You can copy the formula in cell C1 down by double-clicking because, in this case, you do want the cell references to change!
How can you see all formulas at once? Use the shortcut Ctrl+~ (the tilde is located under the Esc key). Notice that Ctrl+~ tells us that all cell references are relative here — which means: "Multiply two-cells-over-to-the-left by one-cell-over-to-the-left."
In cell F2, however, you want to find out what the value in cell E2 is, as a percentage of the mean in cell E11, using the formula =E2/E11. You accept the formula by pressing Ctrl+Enter and then double-click the fill handle downward. This time, you get into trouble! Ctrl+~ reveals the problem: The reference to E11 should be absolute; otherwise, the adjusted formula in the downward cells attempts to divide by empty cells, which is an invalid division-by-zero error.
Let's start over in cell F2:
1. Type the equals sign (=) in cell F2.
2. Press the Left Arrow key once to get to E2.
3. Type / (in order to divide).
4. Press the Left Arrow key once and then Ctrl+Down Arrow to get to E11.
5. Press the F4 key to make E11 "locked" or absolute (that is, $E$11).
6. Press Ctrl+Enter.
As a result, the copy behavior of the cell references is correct now: It is partly relative (E2) and partly absolute ($E$11). $ is a string sign that "locks" the column number and/or the row number, making them absolute. F4 is a cycle key that works like this:
• Pressing F4 once changes E11 to $E$11.
• Pressing F4 twice changes E11 to E$11.
• Pressing F4 three times changes E11 to $E11.
• Pressing F4 four times takes changes the cell back to E11.
You select the range C14:F23 in order to calculate what the new concentration of a certain solution is if you dilute certain concentrations (in column B) with a particular factor (in row 13). Then you follow these steps:
1. Enter the formula =$B14*C$13 in cell C14.
2. While building the formula in the formula bar, select a cell and press F4 immediately. If you do this at a later stage, you need to click or double-click the cell address that needs to be changed before you press F4.
3. Accept this formula with Ctrl+Enter so it goes into all the selected cells, where it behaves partially as relative and partially as absolute.
Another way of creating this kind of tables is using Excel's often overlooked Data Table feature as we used it in Figure 1.7. The formula in cell B4 calculates the population size after 30 generations (B3), if the growth rate is 2.3 (B2) and the population starts with 2 organisms (B1): =B1*B2^B3. We could create a two-dimensional table based on the two variables growth rate (in column B) and number of generations (in row 4). Then you follow these steps:
1. Select the entire table: B4:H15.
2. Make sure there is a formula at the origin (B4).
3. Go to: Data tab | What-if Analysis | Data Table.
4. Set in the dialog box the row input cell to B3 and the column input cell to B2.
5. Click the OK button
The end result is fabulous. All calculations are done in accordance with the formula at the origin of the table. The table itself (C5:H15) has an array formula in it: {=TABLE(B3,B2)}. Do not type the formula, nor its braces. Each cell is now part of an array and cannot be deleted or changed on its own. You will use Excel's feature of Data Table extensively in Chapter 47.
CHAPTER 4Range Names
A cell address is like a street number — and both can be difficult to remember. You might want to replace a cell number with a more meaningful address: a cell name. A cell name basically acts like an absolute cell address, but when used in formulas, it may be more informative. You can also name a range of cells. So you can have cell names and range names, but because a cell is basically also a range, though consisting of only one cell, the term range name is more comprehensive.
The top of Figure 1.8 shows a list of readings that several analysts found during several tests. The bottom table marks each combination of a specific analyst and a specific test with a plus sign (+) if that reading was above the grand mean. Instead of comparing each individual reading with the grand mean in $G$12, you could also give cell G12 a more meaningful name — a range name. Here's how you do it:
1. Select cell G12.
2. In the Name box, found to the left of the formulabar, typeGrandMean. Here are a few rules for naming:
* Don't include spaces in a range name; underscores are okay.
* Names are not case-sensitive, so GrandMean is the same asgrandmean, GRANDMEAN, and so on.
* Unique names function in the entire workbook. If you create a duplicate name, the second name will be assigned only to the specific sheet you are in.
3. Press Enter. If you don't, the range name does not exist.
Now the name GrandMean has become official, so you can access the cell GrandMean through the drop-down list of the Name box — no matter where you are in this workbook — and Excel will take you there!
From now on, you should be able to call the IF function in cell B15. Its first argument is B2>GrandMean. You can just type the new rangename, or you can click cell G12 to have Excel insert its name automatically. The end result in cell B15 is this:=IF(B2>GrandMean,"+",").
Unfortunately, the previously installed range name does not kick in when you select multiple cells to fill them with the same formula. You must use an absolute cell address again, or you could use an extra tool: the Use in Formula drop-down located on the Formulas tab.
Try getting a copy of the grand mean in the cells G15:G24. When you just type the formula in the Formula bar, notice that the range name nicely pops up once you start typing =Gr ...
To find all your range names listed, follow these steps:
1. Select the Formulas tab.
2. Click the Name Manager button.
3. Select the name of your choice.
4. Delete that name (using the button to the right) or expand/change its reference (at the bottom).
Figure 1.9 shows that you can also name ranges of multiple cells. For example, you could name the first range Analysts, the second one Strains, and the third one Readings. Instead of doing all this manually, you can use a handy Excel tool:
1. Select the entire data set by selecting A1, pressing Ctrl+Shift+Down Arrow, and then pressing Ctrl+Shift+Right Arrow.
2. From the Defined Names section of the Formulas tab, select Create Names from Selection.
(Continues...)
Excerpted from Excel 2013 for Scientists by Gerard M. Verschuuren. Copyright © 2014 Dr. Gerald M. Verschuuren. Excerpted by permission of Holy Macro! Books.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.