Spreadsheet Skills for Teachers & Staff

The contents of this page are intended to supplement the Spreadsheet Skills workshop session offered by Computing Services. In this session we look at basic spreadsheet features that can be used in MS Excel or Google Spreadsheets.

Components of a Spreadsheet

Rows & Columns

No matter who makes the tool (Microsoft, Google, Apple, OpenOffice, etc.), a spreadsheet is defined by rows & columns.

A column is a vertical group of cells on a sheet.

• Columns are labeled alphabetically (A, B, C) across the top of the sheet.
• You can select an entire column by clicking on the label (letter) at the very top.

A row is a horizontal group of cells on a sheet.

• Rows are labeled numerically (1,2,3) along the left side of the sheet.
• You can select an entire row by clicking on the label (number) at the very left.

A cell is the intersection point between a column and a row.

• Each cell has an address (for example, cell A1 is the intersection point of column A, and row 1).
• The active cell has a highlight around it.
• You can select all of the cells in a sheet at once by clicking the space at the top left corner between the labels for Column A and Row 1.

Time Saving Tips

Keyboard navigation

To commit data to a cell, you can press RETURN, TAB, or any of the arrow keys. The key you press determines which cell becomes the active cell.

• Press the TAB key to move the selection one cell to the right.

• Press the ENTER key to move the selection down one cell.
• Press the ESC key to unselect or undo something before you commit to it by pressing enter.
• Other common keyboard shortcuts:
  • ⌘+C to copy data
  • ⌘+V to paste data
  • ⌘+Z to undo the last change

If you find yourself entering very much text in spreadsheet form, you may appreciate an external keypad. These are available in many places for as little as $5. Search the internet for “USB keypad”.

Click image for a larger view.

AutoFill

• Quickly enter some types of data (lists, sequential data, copy formulas).
• Click on a cell then move the pointer over the lower-right corner of the cell until the pointer becomes a black cross. Then click and drag over cells you want to fill.
• Example AutoFills: Months, Days of Week, Numbers in a pattern
  • In a Google Spreadsheet if you hold down the OPTION key while performing an AutoFill, the results are from a Google search for the original terms you provide.

Working With Your Data

Click to view a larger version of this image.

Sheets in a Workbook

• A sheet (also called “worksheets” or “tabs”) is a single spreadsheet
• A workbook is a group of sheets. Each workbook can have multiple sheets.
• Find the sheets/tabs at the bottom of the spreadsheet window.

Format Data

To make the data you enter easier to read, you can format it. To format data you first have to select the cells containing the data that you want to format.

• Click in a cell and drag the mouse to select a range of cells.
• The HOME tab presents a number of familiar formatting options; bold, alignment, cell highlighting, etc.
• On the HOME tab under NUMBER you can choose to have the data automatically formatted to read as Currency, Percentages,  Dates, Text, etc.

TIP: To automatically resize a row or column, move the pointer over the border between two column headings. When the pointer appears as a double-headed arrow, double-click.

Insert Rows & Columns

After you’ve entered data, you may find that you need another column or row to hold additional information.

To insert a single column:

1. Click any cell in the column immediately to the right of where you want the new column to go.
2. On the Home tab, in the Cells group, click the arrow on Insert.
3. On the drop-down menu, click Insert Sheet Columns. A new blank column is inserted.

To insert a single row:

1. Click any cell in the row immediately below where you want the new row to go.
2. In the Cells group, click the arrow on Insert.
3. On the drop-down menu, click Insert Sheet Rows. A new blank row is inserted.

Excel gives a new column or row the heading its place requires, and changes the headings of later columns and rows.

Sorting Data

When you sort, you are rearranging rows of data into a specified order. You can sort data alphabetically, numerically, by format, or some other criterias.

When you sort on a range of cells, the sort criteria aren’t saved with your workbook, and are not updated automatically. When you reapply a sorting criteria, you may see different results if the data car been added to, or formulas have produced different results.

To do a basic single-column sort:

1. Click a cell in the column that you want to sort. (Data in adjacent columns will be sorted based on the column you sort.)
2. On the Data tab, under Sort & Filter, click the arrow next to Sort.

You can also sort by multiple criteria by going to Sort… in the Data menu.

More information about sorting & filtering data in Excel is gathered here.

Filtering Data

When you have a large amount of data, sometimes it is easier or more useful to work with a specific portion of it. When you filter data, you temporarily hide some of it. Only the data that meets your criteria appears. The data that doesn’t meet that criteria is hidden. After you filter data, you can copy, find, edit, format, chart, and print the subset of filtered data.

To create a basic filter on a set of data:

1. Click a cell in the range or table that you want to filter.
2. On the Standard toolbar, click Filter  .
3. Click the arrow  in the column that contains the content that you want to filter.
4. Under Filter, click Choose One, and then in the pop-up menu, select your criteria.

More information about sorting & filtering data in Excel is gathered here.

Formula Basics 

A mathematical calculation performed on your data is called a formula.

• Begin by selecting the cell in which you would like the results of the formula to appear.
• Enter the appropriate math in the Formula Bar found just above the column headers (by default).
• Formulas always begins with an equal sign (=).
• Clicking and dragging through a range of cells while constructing a formula tells the spreadsheet to include these cells in the calculation.

Function Basics

• AutoSum makes it easy to total a column of numbers.
• Average returns the mathematical mean of a set of numbers.
• Median finds the number in the middle of a set of numbers.

Mail/Data Merge

Once you have your data (class list, for example) in a spreadsheet, you can use this as a data source for a Mail/Data merge in Microsoft Word. This can be an elegant way to produce name tags, labels, and other personalized classroom materials.

For more information here is our LPS page about mail merge in MS Word.

Google Sheets has recently added a new feature called “Add-Ons”. Avery (the label company) offers an add-on that handles mail merge from a Google Sheet. This 3rd party integration is not supported by LPS, but we have heard of many using it with success.

Transpose Data

Sometimes you have great data that is hard to read because of the way it is oriented. A perfect example of this is a Google Form that returns results in a spreadsheet with the questions in columns and results appearing in rows. If you are trying to see how everyone answered a specific question, it would be much easier if the rows and columns were flipped so that each row was a question, and each column was a response. This is easier to do than you might think! In Google Sheets there is a formula that does this for you.

Don’t worry! It is easier than these steps look – I have been very detailed in my directions.

1. Open the Spreadsheet with the difficult data.
2. Create a new sheet in that spreadsheet.
3. In cell A1 of your new blank sheet enter the following formula:
   =Transpose()
   
4. Place your cursor inside of the parenthesis of that formula you just typed.
5. Go to the sheet that has the offending data and click on the cell that selects all (above the row headers, to the left of the column headers) and press return.
6. You should be returned to your new sheet, where nothing will happen for 5-10 seconds, then suddenly your data will be copied from sheet1 and transposed into sheet2.

Printing Tips

I’m not sure a spreadsheet has ever printed the way I wanted it to on the first try. Save yourself a trip to the printer and the cost of paper by previewing the page before printing. (On the Layout tab, under Print, click Preview.)

A number of printing tips are gathered on this page. Learn how to print cell gridlines, add headers or footers to a spreadsheet, repeat column headers at the top of each page, and many other things.

Updated October 12, 2023