Quick Menu

Spreadsheets - Getting Started

Spreadsheets are a fundamental tool in both science and engineering for a surprising number of reasons. They not only store and organize data efficiently, but also allow for complex calculations, trend analysis, and even basic modeling. This versatility makes them essential for researchers and engineers across a wide range of disciplines, from plotting experimental results in biology to designing chemical processes in chemical engineering.

For this tutorial you will be using Google Sheets.  Click the following link to open a Google Sheets.    There are other spreadsheet apps that work very similar to Google Sheets including Microsoft Excel and OpenOffice.

Overview of Google Sheet App 

  • Rows: These are the horizontal lines running across the spreadsheet, numbered 1, 2, 3, and so on. They help you organize your data from top to bottom.

  • Columns: These are the vertical lines running down the spreadsheet, labeled with letters A, B, C, all the way to XFD (and beyond in some versions). They let you organize your data from left to right.

  • Cells: Where a row and column meet, that's a cell. Each cell has a unique address based on its row number and column letter (e.g., A1, B3). This address helps you pinpoint specific data points. Think of each cell as a little box where you can enter text, numbers, dates, or even formulas.

  • Formula Pane: This is the bar at the top of your spreadsheet where you build instructions for your data. It's like a mini-calculator where you type formulas. Formulas always start with an equal sign (=) and then use functions, cell references, and operators to perform calculations, analyze data, or manipulate information. The formula pane shows what you're typing, while the actual result appears in the cell you've assigned the formula to.

As an example, suppose you have a list of persons heights and names as follows.  Lets enter this data in a google sheet and perform some math on it.

Name Height (cm)
Carl 190.5
Jane 175.4
Mike 180.6
Lola 187.8

 

Step 1: Open Google Sheets

  1. Start Google Sheets: Click the following link to open a Google Sheets.
  2. Click "Blank Spreadsheet" to start a new spreadsheet.

Step 2: Entering and Organizing Data

  1. Labeling is Key: Lets transcribe the above Name vs Height data in the sheet.  In cell A1, type "Name" and in cell B1, type "Height (cm)". These clear labels will help you understand the data you're entering and make your spreadsheet easier to navigate later.
  2. Enter Information: Click on cell A2 and type the name of your first person "Carl". Press "Enter" to move down to cell A3. Repeat this process to enter names for all the entries. For Height, move to cell B2 and enter the height for the person in A2. Follow the same pattern to enter height for all persons.  Tip:  You can often cut and paste data into the sheet to save time.  Highlight the data in the above table and copy and paste in to you sheet.

Step 3: Mastering Copy & Paste

  1. Highlighting for Copying: Let's say you want to copy the persons names to another location in the sheet, perhaps for another analysis. Click on cell A2 (containing the first person's name). Then, hold down the "Shift" key and click on the last cell with a person name (Lola in this case). This will highlight all the persons names (cells A2:A5).
  2. Copy & Paste Prowess: Right-click within the highlighted area and choose "Copy" from the menu. Alternatively, you can use the keyboard shortcut "Ctrl+C" (Windows) or "Command+C" (Mac) to copy the data.
  3. Choosing the Paste Destination: Click on the cell where you want the copied names to appear. For example, let's say cell D1 is empty. Click on D1 and right-click, then choose "Paste." You can also use "Ctrl+V" (Windows) or "Command+V" (Mac). Now you'll see the person names copied to column D.

Step 4: Exploring Formulas

  1. Convert Height from cm to feet: Let's convert these heights to feet.  There are 30.48 cm in 1 foot (30.48 cm = 1 ft), so we will need to divide each height measurement by 30.48, to convert to feet.  Click on empty cell C2 and type =B2/30.48.  This will convert the 190.2 cm to 6.24 ft.
  2. Drag and Fill Magic: We can leverage the power of formulas to convert all the height to feet as well. Click on cell C2 (containing the height conversion formula). Look for the small square at the bottom right corner of the cell. Hover your mouse over it, and it will turn into a plus sign (+).  Drag down to the end of the data in column B (i.e C5 in this case).
  3. Filling Down the Formula: Click and hold on the plus sign, then drag it down to cell C5 (assuming you have heights in 4 rows). As you drag, a preview of the adjusted formula will appear. In our case, it will automatically change to =B3/30.48) because the formula cleverly references the cell to the left of C2. Release the mouse button when you reach cell C5. Now you'll have the all the heights converted to feet.
  4. Calculating Class Averages: Let's find the average height in feet for theses persons. Click on an empty cell beneath the last height entry, let's say cell C6. In the formula bar (above the sheet) or directly in the cell, type =AVERAGE(C2:C5). This formula instructs Sheets to calculate the average of the values in cells C2 to C5 (assuming your heights are in column C). Press "Enter" and the average height will appear in cell C6.

Google Sheets offers a robust set of formulas just as powerful as Excel for scientific and engineering tasks.

These formulas extend far beyond basic addition and subtraction. You can perform complex calculations, intricate statistical analyses, and even leverage engineering-specific functions. From trigonometric calculations for analyzing forces to running regressions for trend analysis, there's a formula to handle a vast array of scientific and engineering challenges.

To explore the treasure trove of Google Sheets formulas, you can utilize their built-in help function or refer to this extensive online resource by Google themselves: Google Sheets functions: https://support.google.com/docs/table/25273?hl=en This document details a vast array of formulas categorized for easy searching!

Bonus Tip: Formatting for Fun!

Sheets offers various formatting options to make your spreadsheet visually appealing and easier to read. Select the range of cells you want to format (like B2:B10 for scores). Click on the "Format" menu at the top and explore options like changing font size, bolding for emphasis, or applying colored backgrounds to highlight specific data points.

This is just a basic example, but it demonstrates entering data, copying, and using formulas in Google Sheets. Feel free to explore other functions or experiment with formatting the data to make your spreadsheet more visually appealing.

For a more visual explanation, you can check out this resource: