ani_gear-2.gif (15548 bytes)

design_tools-spread_sheets.gif (10683 bytes)

ani_gear-2.gif (15548 bytes)

Spread sheets are a great tool you can use to make engineering or other calculations.

Generic Spread Sheets |


This page is not meant to teach you spread sheets, only to introduce you to them.   We will learn how to use them in more detail in class.  Within most spread sheet programs,  you can:

  • Insert and organize data
  • Insert pictures/diagrams
  • Do simple and "steroid" engineering calculations
  • Plot the results
  • Use a built in optimization program (i.e. for your gear pump)
  • Otherwise manipulate your data


The working area of a spread sheet file consists of cells which are designated by which row and column they fall into.  For example there are 72 cells in the 12 rows and 6 columns in the figure below. Although only 72 cells are shown, I have made spread sheets containing over 10,000 cells.

Cells can contain text, equations, images, and other useful things.

The gray boxes are not cells, they are the headings for the rows (1 - 12) and columns (A - F.)

ex_spread_sheet.gif (2828 bytes)

Excel is a spread sheet program which is menu and icon based.  The GUI is shown below.  Following are short explanations of some of the menu items we will use in class.

excel_gui.gif (10278 bytes)


The file menu is used to handle and print files.

excel_file_menu.gif (39252 bytes)


The edit menu is mainly used to edit the content of your spread sheet, for purposes of this class, you will find it most useful for using the copy/paste functions and the find function (will find text/numbers in large spread sheets.)

excel_edit_menu.gif (34006 bytes)


From the insert menu, you can choose to insert/add extra columns, rows, cells, and CHART/PLOTS!!!    We will be using the plotting function in a few of your labs.  The next section will discuss this, but first....

You can use this menu to insert functions/equations (you could type them by hand.)   Typical functions/equations would include trigonmetric, averages, and sums of rows or columns.  There are also logical (and/if/or) functions, statistical functions, and many other equatons.

excel_insert_menu.gif (29773 bytes)


Say you wanted to plot the equation y= x2.

To get the results (see graphic belwo,) I followed these steps:

  1. Click on cell A2 and type "x"
  2. Click on cell B2 and type "y"
  3. Click on A4 and enter "1"
  4. Click on A5 and enter "2"
  5. Continue adding numbers (as seen in graphic below) up to A8
  6. In Cell B4 type  equation "=A4*A4"   (you could also have typed A4^2)
  7. Copy cell B4 to B5    (B5 contents will now refrence cells A5, not A4)
  8. Continue to copy this cell through B8

Your results should look like this
excel_graphing_example_inputs.gif (23353 bytes)


To make a plot, highlight the cells you want to pick.    Then click on   chart_icon.gif (1090 bytes) in the insert menu.

excel_graphing_example_highlight.gif (25683 bytes)


Excel will prompt you for the type of chart you wish to make, we will be using mostly scatter plots (#4.)  To choose this type, highlight XY (Scatter) in the left window, then highlight the chart type labeled #4 in the graphic.

excel_chart_type_choice.gif (25525 bytes)

With respect to why we are using the #4 chart type and not the others, consider the following about each chart type:

1. This type hard to see and communicate trends

2. This type may lead others to make conclusions about trends, slopes, or at points for which you have no real data

3. This type hard for some to see original data points

4. This type works well, you should be able to say why considering types 1 -3...


You can finish the rest of the process by following the on screen instructions.



Spread sheets are very useful for playing what if games, iterative calculations, and optimizing designs.  You can "program" the spread sheet to calculate say for instance, the approximate normal stress in a beam as seen below.  FOLKS WITHOUT SOLID MECHANICS, DON'T WORRY ABOUT THE EQUATIONS USED TO GET THIS, CONCENTRATE HOW THE SHEET IS USED!

The stress depends on the INPUTS:
  • Force, F
  • Beam Length, L
  • Beam Thickness, t
  • Beam Width, w
Moment and stress are the OUTPUTS, or calculated values.

The moment and stress are calculated at different positions along the beam.

The OUTPUTS are then PLOTTED to make their relationship to the INPUTS easier to interpret.

ex_spread_sheet_1st_input.gif (15502 bytes)


Now suppose you want to know what the stress and moment are in the same beam loaded with a different force.

All you have to do is change the value in cell C20. Say we change it from 5 to 2 pounds.  The spread sheet automatically recalculates and replots all the output.

ex_spread_sheet_2nd_input.gif (15517 bytes)




ADMINISTRATIVE:          Home | General Info | Syllabus

STUDENT RESOURCES: Gear Pump Resources | Workshops | Labs | Field Trips | Final Reports | Design Tools

MISCELLANEOUS:         Students | PrizesMachine Gallery | Class Gallery | Comments | Links | Sponsors