Tutorials‎ > ‎

MAE3 Excel Tutorial

Spreadsheet Tutorial for Engineering Analysis and Documentation

by Nathan Delson (ndelson@ucsd.edu)


Spreadsheet Advantages and Disadvantages

 

Spreadsheets are powerful tools for engineering calculations, and easy to use. In fact it has been claimed that spreadsheets were one of the original "killer applications" that convinced businesses to invest in personal computers. Spreadsheets were originally developed for accounting applications, but now include many engineering, statistics, and database functions.

 

Key advantages of spreadsheets for engineering applications are that they are relatively easy to setup and modify. It is possible to change one design variable, and quickly see the effect on all other variables in the design.

 

However there is an important disadvantage of spreadsheets in terms of its ability to document design calculations. When one prints out a spreadsheet the default format only shows the numerical results, and not the equations that have been used. Even with the spreadsheet on-line, it can be difficult to read and verify long equations. Thus it is not easy to have someone verify and check the analysis (real world engineering errors have occurred because of errors in a single spreadsheet cell). In this tutorial a method is presented to name variables, and printout both the numerical values and the equations in an easy to read fashion. This approach helps offset this disadvantaged of spreadsheets (an alternative software tool for engineering documentation is MathCAD).

 

 

Excel Basics (other spreadsheet programs are similar)

 

Each cell has a designation for row and column, such as A1 or B5

 

Cell types can be: text, numeric, or dates

 

  • Any cell that is entered as text is automatically treated as a text type
  • If numbers are entered, then the cell is treated as a numeric type. One can force a cell to be treated as a numeric type by starting it with: =, +, or –
  • Dates are entered in most standard formats

 

Equations can be written by typing in cell locations. For example:

If one types in a cell the following: =A3+B5

Then the numeric value of Cell A5 plus B5 will be displayed

 

When writing an equation, one can simply move the cursor to the desired cell that is being used in the equation, rather than typing in the cell location

 

When copying equation, the values of the row and column increment automatically according to the location of the new equation. One can prevent the automatic update of rows and column by typing “$” in front of the row or column that should be fixed (the F4 key will also do this). For example:

In a cell equation typed as: =$A$3+B5

only B5 will be incremented automatically when copied, and $A$3 will remain fixed.

 

Displaying Equations

To display equations type "ctrl ~". This will toggle back and forth between displaying equations and values. You can then printout or save the spreadsheet with the equations visible.

 

The problem with just creating equations as shown above, is that it is difficult to read and check the equations, because it is hard to read all the row and column numbers.

 

For example, look at the equations in the sample file without variable names (you can see the equations with “ctrl ~”, but it would be hard to certify that all equations are correct, especially for long equations). To solve this problem, follow the approach described below for named variables.

 

Displaying Equations with Named Variables

 

Use the method described below shows equations more clearly and allows someone to really verify a spreadsheet’s calculations.

 

Create columns as shown below:

  • First column has the variable description
  • Second column has a unique variable name with no spaces
  • Third column has values and equations
  • Fourth column has units

 

Variable Description

Variable Name

Values

Units

Area

A

15.40

in^2

Thickness

t

0.34

in

Volume

Vol

5.24

in^3

Density

p

0.69

oz/in^3

Estimated Weight

W_est

3.59

oz

Actual Weight

W_act

 

oz

Percent Difference

 

 

 

 

Defining Cell Names

 

Select region that includes columns of variable names and values, as shown below.

 

 

 

Select Commands: Go to Formulas tab => Name Box=> Create from selection => Left Column => OK

            create names from selection

 

When equations are written, the variable names will be automatically shown instead of the cell number (see variable name example). If variable names are added or changed, the above step needs to be repeated. If one names the cells after the equations are written, then names need to be applied by executing: Formula Tab => Defined Names => down arrow of Define Names: Apply Names=> (select all names) => OK

apply names

Displaying Named Equations

To display equations type "ctrl ~". This will toggle back and forth between displaying equations and values. You can then printout or save the spreadsheet with the equations visible.

 

 

When submitting a report one can printout two copies of the spreadsheet; one with the values shown and the other with the equations shown. You can save web pages in both formats as well.

 

Naming Columns

Many equations in worksheets are copied down a column. To use named variables in columns, highlight one column at a time including the name in the top row. Execute the command: Formula tab => Defined Names box => Define Name=> Ok. As shown below the name “Price” is being defined for cells in column B.

 

Define names from column

 

Now each cell in the column will be named according to the text in top row of the column. The cells will maintain their dependence on the row of the spreadsheet, but will display the proper equations for the column, as shown below:

 

 

 

Wrap Text and Paragraphs

 

A long sentence or paragraph will extend on a single line, making it difficult to read. The text can be formatted for easier readying using the "Wrap text" and "Merge cell" options.

 

 

Step 1: Select range of cells that includes the cell with text and empty cells to the right and below this cell, where you want text to wrap into

 

Step 2: Right click on them => Format Cells... => select tab: Alignment => check "Wrap text" and "Merge cells"

 

 

 

Text Tips

 

Once you get a format of a paragraph you like, you can copy it to other cells to start new paragraphs. You can also apply the format from one cell onto another cell using the command:  

Edit => Paste Special => check "Formats"

 

To start a new line of text within a cell at a specific point, click in the formula bar (or cell, if editing in the cell) where you want to break the line, and then press ALT+ENTER.

 

To create additional rows in the middle of a spreadsheet, select a number of rows with the mouse, and use commands: Insert => Rows

 

To delete rows, select the complete row by clicking the mouse on the row numbers on the far left side of the spreadsheet. Then use command: Edit => Delete (the same method works for columns)

 

Spell checking is easy, just us commands: Tools => Spelling

 

 

Additional Tips

 

Formatting Cells

 

The Format => Cell command provides many options and can be impended on a range of cells at once

  • Changing the number of significant digits: selecting the “Number” tab and “Number” on the list allows one to set the number of decimal places

 

 

  • Percentages: selecting the  “Number” tab and “Percentage” on the list displays a fraction as a percent

 

 

Column Width

 

The column width can be changed by the commands: Format => Column => Width

Alternatively one can adjust the column width with the mouse by dragging the column line on the top row

 

If you see ###### in a cell, it means that the column is not wide enough to display the numeric contents; increase the column width.

Saving as a Web Page

 

An Excel spreadsheet can be saved as a web page. Just use: File => Save as Web Page

 

A nice feature of Excel, is that it can open and edit a web page (htm file) created by Excel. Therefore you do not need to have tow versions of your Excel spreadsheet. Just have the spreadsheet as a web page, and open that page from within Excel to edit it.

 

It can be confusing if you forget to save your file as a web page, but still link to it on your web page. A computer with Excel installed will start up Excel and load the file for editing rather than view it as a web page.

 

Paste Special

The Edit => Past Special is a very useful command for copying cells within a spreadsheet

 

The following window will appear

 

 

  • selecting values will copy only the numeric values displayed without the underlying equations
  • selecting format will copy the format of a cell into other cells, but without changing the values in those cells
  • selecting transpose will transpose a column to a row or vice versa

 

What-if Analysis

 

There are many advance analytical tools in spreadsheets, but “what-if analysis” deserves a special mention. Using this approach, one can see what happens when one changes the value of a single cell in a spreadsheet over a range. The results of any other cell in the spreadsheet can be tracked.

 

See Excel help on “One-variable data tables” for an example and detailed instructions

 

 

Other Excel Tutorials on the Internet

 

Concise Guidelines to Common Operations, from Purdue

http://www.chem.purdue.edu/gchelp/tools/operate.html

Short Tutorial on Excel, from Mike Splane

http://www.cob.sjsu.edu/splane_m/ExcelFormulas.htm

A list of spreadsheet tutorials on the Internet, from Quasar Canada

http://www.quasar.ualberta.ca/edpy202/tutorial/spreadsheet/spreadsheet.htm

Comments