Excel for Scientists and Engineers

By Dr. Gerard Verschuuren

For scientists and engineers tired of trying to learn Excel with examples from accounting, this self-paced tutorial is loaded with informative samples from the world of science and engineering. Techniques covered include creating a multifactorial or polynomial trendline, generating random samples with various characteristics, and tips on when to use PEARSON instead of CORREL. Other science- and engineering-related Excel features such as making columns touch each other for a histogram, unlinking a chart from its data, and pivoting tables to create frequency distributions are also covered.

• Language: English
• Publisher: Independent Publishers Group
• Release date: Aug 1, 2005
• ISBN: 9781615473137

Book preview

(2005)

Prologue

This book can be used on its own or in conjunction with an interactive CD called Excel for Scientists, also available from the Publisher. This book assumes at least a basic knowledge of Excel. Readers new to Excel may want to familiarize themselves with a basic how-to book such as Learn Excel from Mr Excel (ISBN 1-932802-12-6), available from .

Scientists do not want nor do they need verbose explanations. That’s the reason why I tried to be as concise as possible in the chapters of this book. I also attempted to add some meaningful simple exercises, because the proof is still in the pudding.

Since I am a human geneticist myself, most of my simple examples stem from the life sciences. I apologize to all the readers who feel foreign to the cases I use in this book. I hope those readers can look through the surface.

Excel was originally created as a financial application, but it has evolved into a rather sophisticated scientific tool. Although other and perhaps more advanced programs do exist, many of those have a steep learning curve. And that is why Excel still may be your best choice. I hope you will soon discover why.

gmv

Chapter I: General Techniques

There are some general techniques in Excel that every scientist should know about. These techniques will make your work easier and faster, and you will need them in all the chapters to come.

How do you copy information from your spreadsheets?

How do you refer to (the contents of) other cells?

How do you use Names in Excel spreadsheets?

What is behind your values: Appearance or Reality?

How do you manipulate dates?

How do you place functions inside functions?

For a more general coverage of these issues, consult the interactive CD called Join the Excellers League published by MrExcel. This CD is available through www.mrexcel.com or www.amazou.com.

Making Copies and Trends

You are probably familiar with the most common copy and paste routines in Excel. Select the cell(s) to copy from, and then choose one of the following options:

Hit the Copy button, select a new cell, and hit the Paste button.

Choose Edit / Copy, select a new cell, and choose Edit/Paste button.

Press Ctrl+C, select a new cell, and press Ctrl+V.

Right-Click (R-Click) on your selection, choose Copy, R-Click on a new cell, and choose Paste.

But there are also a few other, even fancier ways that we will discuss next.

Using Paste Special

Sometimes, you don’t want an exact replica of the copied cells, but a modified version instead. That’s when you need the Paste Special option from either the Edit menu or from the R-Click menu.

Say you have some values in your spreadsheet based on a certain formula but you don’t want these calculated values to change anymore (see Figure - 1):

Select all formula results.

Copy these cells.

Select Paste Special from the Edit menu.

Choose Paste Values + hit OK.

Now all your formulas have been replaced with values!

Figure - 1

You can replacing your formulas with values using Copy / Paste Special / Values.

The Paste Special menu has many more powerful options. Let’s assume you have replaced formulas with values, but you need to update these static values with a certain factor – say, a multiplication by 1.1:

Type your multiplier 1.1 in a cell somewhere on the sheet.

Copy that specific cell. p.s. Don’t forget this step; Excel can’t read minds!

Select the cells that need to be updated.

Choose Edit / Paste Special.

Select Operation Multiply.

How can you display all formulas at once? By using Ctrl ∼ (the Ctrl key plus the ∼ (Tilda) key). This is a toggle option that allows you to switch back and forth between the value view and the formula view.

Figure - 2

The combination of Ctrl and ~ makes the sheet toggle between value view and formula view.

Filling Adjacent Cells

Excel has a special tool, called the AutoFill Handle, that allows you to automatically fill adjacent cells. This handle is located in the cell’s (or a selection’s) right lower corner. When you move the cursor there, it changes into a small plus sign (+):

You can L-Click and drag this handle down to copy cells

You can R-Click and drag this handle down to get a menu with options to choose from.

Figure - 3

By dragging the AutoFill handle down with a R-Click, you get a menu with many powerful options, particularly the Series option.

Using a R-Click-drag, you have several options to choose from (especially for dates). Series, the last one, is the most powerful because it allows you to specify the step by which you want to increment (see Figure - 3). You can even type – 5 to go five days back, and there is also a choice to skip weekends.

Another way of creating trends is to select two or more cells at the same time and, using the AutoFill Handle with a L-Click drag, to continue the selected pattern.

Excel also has some built in lists (see Table - 1) that the AutoFill handle can use.

Table - 1

Built-in lists supported by Excel

To add your own customized list(s), do the following:

Select Tools / Options / Custom Lists.

Type your entries, clicking Add after each one (see Figure - 4).

Figure - 4

The Tools/Options/Custom Lists dialog box lets you add your own customized list of entries.

Navigating Quickly

Let’s say that you want the same formula in the cells A1:A200. What is the most efficient way of doing such a thing?

Select cell A1.

Select Edit / GoTo / Reference:A200.

Do NOT hit Enter by itself; instead, hit Shift + Enter (or Shift + OK). The Shift key will also select the cells in between A1 and A200, even though you are actually still in cell A1 (white) (unless you click somewhere else, so don’t do this!).

In the formula bar, type: =RAND()

Do not hit Enter, for that would take you to A2 (and you would have to type the formula again). Instead, you want this formula to be in all the selected cells, so hit Ctrl + Enter. The Ctrl key inserts the formula in all selected cells

Let’s say that you want cell B1 to display the sum of A1:A200. When you select cell B1 plus the AutoSum ), you see only: =SUM(). How can you get the total range in there?

Click in A1. Now you see: =SUM(A1)

Hold both Shift + Ctrl and press the Down-Arrow key (↓). Voila!

Table - 2

Important combinations with the Enter key

Understanding Relative versus Absolute

When you copy