SILS iSchool

15 Oct 2018

Value Added | daily

Class Schedule

Basics | sessions 01-05

22 AUG | intro
27 AUG | clients
29 AUG | servers
05 Sep | networks
10 Sep | basics lab

Web Development | sessions 06-11

12 Sep | structural layer
17 Sep | presentational layer
19 Sep | working with layers
24 Sep | behavior layer
26 Sep | images & design
01 Oct | website lab

Document Markup | sessions 12-14

03 Oct | object layers
08 Oct | graphics
10 Oct | document markup lab

Spreadsheets | sessions 15-19

15 Oct | spreadsheets | practice exercises | next session

17 Oct | formulas & functions
22 Oct | data display
 18 Oct | Fall Break 
24 Oct | database tools
29 Oct | spreadsheets lab

Relational Database | sessions 20-26

31 Oct | relational databases
05 Nov | tables
07 Nov | relationships
12 Nov | input & output
14 Nov | SQL
19 Nov | complex queries
26 Nov | databases lab
 21 Nov | Thanksgiving 

Presentation | sessions 27-30

28 Nov | presentation design
03 Dec | presentation delivery
05 Dec | presentation lab
12 Dec | 0800-1100 | final in class presentation

Data displayed in an X/Y axis grid,
with cells at each intersection of each row and column,
each of them individually capable of being formatted.

What is a Spreadsheet?

[Dilbert Cartoon, 20070807, by Scott Adams]

Some terms to know

back to top

A Spreadsheet

It is data displayed in rows and columns on a plane and Microsoft's Excel spreadsheet program is one of the most used spreadsheet applications. Excel's parts and its Worksheet are similar to Word and other Office applications. Many of the shortcuts in Word will work in Excel.

  •  cntl+s  to save
  •  cntl+n  for a new document
  •  cntl+c  to copy
  •  cntl+x  to cut
  •  cntl+v  to paste, etc.

OpenOffice Calc or LibreOffice Calc are both reasonable alternatives, but Excel is still the most used and has some advantages.

Use this example as we review some of the basic components.

[MSExcel 2013 components]
Open the image in a new tab to see it in larger format

The same is true in Excel for Mac

[MSExcel 2013 components]
Open the image in a new tab to see it in larger format

 Sheet tabs  for multiple worksheets

Scrollbars right and bottom

 Rows  numbered 1 through 1,048,576 in Excel 2007+

 Columns  lettered A through XFD (16,384) in Excel 2007+

 Cells  are where the data is stored

 Fill handle  is the small black square in the corner of the cell

  • when you point to it, the pointer changes to a black cross
  • you can then drag a copy of the contents of that cell to an adjacent cell or fill in a series
    • to see fill options, right click as you drag

back to top

Getting Started

Before you begin a complex spreadsheet, plan it out

  1. what are the desired outputs? (e.g., charts, professional look, for personal use?)
  2. what data do I need to enter?
  3. how is the data positioned?
  4. what mathematical expressions are used?
  5. who will be using the spreadsheet?

back to top

Formatting your workbook

[how to find MSExcel 2013 set-up options]
[Excel 2013 setup options dialog box]

The same is true in Excel for Mac

[Excel 2013 setup options dialog box]

Tune up your workbook to your needs. Make it work for you, not against you.

back to top

Saving a Worksheet

Save with  cntl+s  or Save As with  alt+f+a , and, as in UNIX and MSWord, ...


back to top

Entering Data

Select the cell where you want to enter data. Start typing. The data will appear at the top of the screen in the Formula Bar, where you can edit it, correct errors, and add text, figures, or formulas. The data is only placed into the cell when you hit the Return key, the Enter key, the Tab key, or any of the direction keys.


Double click in the cell. This will allow you to edit in the cell instead of in the Formula Bar

If you enter a long string of data, Excel will either truncate the display of the label or spill the display over into the next cell. You may see this: ###########

this is OK

to increase a column width, put the cursor on the top of the column where it borders the second column. The cursor will turn from an + to a line with 2 arrows (similar to changing tables in word). Left click and hold the mouse, drag it to the desired position.



  • select cell and drag
  • autofill data or number sequences
  • can create custom lists for Autofill from popular options
[MSExcel 2013 auto fill options]

The same is true in Excel for Mac

[MSExcel 2013 auto fill options]

back to top

Editing Data

Click on the cell you wish to edit, it's contents will appear in the Formula Bar


double click the cell (see above under Entering Data)

[MSExcel 2007+ entering data in cells]

back to top

Dealing with data

Click the cell

[MSExcel 2007+ deleting tools]

The same is true in Excel for Mac

[MSExcel 2013 tools]

If you use the  delete  key, Excel assumes you want to clear contents of the cell(s), but does not clear any formatting you may have added

  • do not use the spacebar to clear cells; it adds a space to the cell rather than emptying it
  • you can also select a cell, right click, and select Clear contents

If you use the right click Delete dialog, Excel assumes you want to remove the entire cell from the worksheet, not just the contents from the cell.

If you add data to a cell by pasting from another cell or another application, you have many options. Pasting from another cell can bring values, formulas, relative references, and many other properties - as you wish.

back to top

Moving from Cell to Cell

  • use mouse
  • use keyboard arrow keys
  • use  tab  to move to the right
  • use  ctrl+g , enter Reference (the cell you wish to go to), press OK

back to top

Moving to Another Sheet

Click the Sheet Tab

back to top

Selecting Cells

  • click the cell, the pointer changes to a big cross
  • to select multiple cells, select and drag
  • when selecting a large area, use the Shift key to extend the selection. Click on the first cell of the range you want to select; then, while holding down the Shift key, click on the last cell in the range you want to select.
  • select discontinuous range of cells by holding down the CTRL key while making the various selections.
  • to select an entire row or column, click on the appropriate row or column heading
  • to select the entire range in the worksheet, click on the box atop the row numbers and to the left of the column numbers

back to top

Inserting and Deleting Cells, Rows, and Columns

  • click in a cell
  • right click
  • select Insert...
  • select the area to be deleted
  • right click
  • select Delete...
  • select the desired option from the dialog box

you can select a cell, an entire row, an entire column, or any group of cells

back to top

Cutting and Copying Cells

Excel does not follow the standard sequence for cutting, pasting, copying.

select the cell or range of cells to cut or copy

choose either  cntl+x  (Cut) or  cntl+c  (Copy)
  • if you choose Cut, the contents of the selected cells do not disappear. The cells are surrounded by a flashing dotted line, and their contents are not actually moved until you click in the cell where you want the Paste to begin and choose the Paste command ( cntl+x )
  • copy does not move the contents of cells; it copies or reproduces those contents elsewhere in the worksheet

If you select a range of cells as the destination of either Cut or Copy, and this range differs in size or shape from the source, Excel will complain. Selecting a single cell is easier, since this method tells Excel where to start the paste and will ensure that the destination is formed exactly like the source

  • to turn off the flashing dotted line, press Esc

back to top

Basic Formatting is quite similar to Word

[MSExcel 2013 cell formatting tools]

The same is true in Excel for Mac

[MSExcel 2013 cell formatting tools]

back to top

But you can do more with Conditional Formatting

Conditional formatting is more flexible, applying specified formatting only when certain conditions are met.

[MSExcel 2016 cell formatting tools]

back to top

Renaming Sheet Tabs

  • right click the tab
  • select Rename
  • type in a new name

back to top

Managing your spreadsheet

Absolute cell references

  • If you need to lock a formula reference to a specific location, use  F4  key to append the  $  sign to a cell reference to lock in a specific location
  • example: $A1 refers to row A column will vary, but $A$1 refers to row A column 1

back to top

Techniques to Help View Data

Freeze Panes

Freeze row or column headings so that with long worksheets you won't lose sight of the labels as you scroll through your data.

[MSExcel 2013 freeze panes]

The same is true in Excel for Mac

[MSExcel 2013 freeze panes]

To freeze a row/column, select the row/column immediately underneath or to the right of the row/column you want to freeze, go to View > Freeze Panes.

Split Window

Select the row/column where you want to place the split, go to View > Split

[MSExcel 2013 split sheets]

The same is true in Excel for Mac

[MSExcel 2013 split sheets]

What's the difference? When is one method preferable to the other?

back to top


To validate data as it is entered, use Data, Validation, and build a data entry rule with the dialog box.

[MSExcel 2013 data validation]

The same is true in Excel for Mac

[MSExcel 2013 data validation]

back to top


Excel comes with additional tools that don't necessarily load unless you ask for them. Look under Excel Options.

[MSExcel 2007+ add-ins]

The same is true in Excel for Mac

[MSExcel 2013 add-ins]

Check the ones you want and they will be added to your Tools menu.

back to top

Page Setup

Also similar to Word, but here you want to set up an electronic spreadsheet for paper applications.

Page  - for orientation & scaling

[MSExcel 2007+ page setup dialog box]

The same is true in Excel for Mac

[MSExcel 2013 page setup dialog box]

Header & Footer  - use the custom tools

[MSExcel 2007+ header setup dialog box]

The same is true in Excel for Mac

[MSExcel 2013 header setup dialog box]

Sheet - rows to repeat puts column headings on each page

[MSExcel 2007+ sheet setup dialog box]

The same is true in Excel for Mac

[MSExcel 2013 sheet setup dialog box]
  • Print comments if needed
  • Page order only necessary if worksheet is wider than a single page

back to top

Formatting is used to identify numbers as currency, percentages, decimals

select cell(s) and use Standard Toolbar for fast formatting of currency/percentage & increased/decreased decimal. For additional formatting, select cell or cells & right click.

[MSExcel 2013 formatting cells]

The same is true in Excel for Mac

[MSExcel 2013 formatting cells]

select Number tab. Modify category, decimal places, symbol, etc.

[MSExcel 2007+ format cells dialog box]

You can also change the appearance of a date number
Oct-09-2018 or 10/09/17
or, my favorites
 09-Oct-2018  or  20181009 

back to top

Group/Move Worksheets using the name tabs

  • hold the CTRL key down while selecting worksheet by clicking on the name tab
  • to ungroup, right click sheet tab and select Ungroup Sheets or click on a sheet that has not been grouped
  • to move the order of worksheets, click & drag
  • to copy a worksheet, click, hold CTRL key while dragging
  • to insert, delete, or select all worksheets, right-click on a worksheet tab

back to top

Adding Comments

select the cell where where the comment will be added and right click

  • select Insert Comment
  • click inside the comment box and type
[MSExcel 2007+ adding a comment to a cell]

The same is true in Excel for Mac ...

[MSExcel 2013 adding a comment to a cell]

... though Excel 2016 for Mac has less functionality in terms of formatting comments than did earlier versions and than do Windows versions.

delete or format the comment

  • right click the comment (the "red" comment marker)
  • select the desired option

back to top