Sparks Fly
[Home] [PBS VideoDatabase] [Social Studies] [TSU] [UVA]

Excel:  Building Spreadsheets 

The Easy Way

Spreadsheets are not just for math purposes.  Spreadsheets can catalog collections, track items, create databases.  Don’t limit your perception of what a spreadsheet can do for you.

 Excel Features

 ·       Excel lets you drag and drop data from one worksheet to another, across workbooks, onto other applications, and even onto your desktop.

·       Excel had a feature called AutoComplete and PickLists which allow easy entry of repetitive data.

·       AutoCorrect automatically corrects most common spelling errors and highlights problem words as you type.

·       Cell Tips allows you to add notes to your cells which automatically appear when you pause your mouse pointer over the cell. (Reminders)

·       Spreadsheet templates allow you to use pre-selected templates rather than starting from scratch each time.  Select  File New to view the template choices.

·       Create your own templates from made from scratch spreadsheets using the Template Wizard and you can categorize the spreadsheet’s fields in a separate database using the Data Tracking feature.

·       The Data Map icon allows you to incert geographical information into your spreadsheet. (Custom Installation necessary)

·       Critical spreadsheet recalcuation formulas perform faster than in other spreadsheets.

·       AutoFilter allows you to work with lists easier by viewing the top 10 items.

·       AutoCalculate feature allows quick track of data totals.  It updates the sum of a range of cells (sum will be seen in the status bar).

·       Has Shared List option which allows seerl people to aadd data to the same worksheet.

To open Excel  

Ø     Open Start menu, choose Programs, Microsoft Excel.

Ø     Click the Start a New Document.  Click the Spreadsheet Solutions tab to bring to the front the New dialog box.  Choose from one of the templates and click OK.

After Excel starts, you are looking at a blank workbook full of worksheets.

Program Control      Title bar                  Menu Bar   Standard toolbar

Menu icon                                                                 Minimize  Maximize  Close

Excel’s Main Screen.

Worksheets

In Excel each individual spreadsheet is called a worksheet.  This is a page where you can enter data, perform calculations, organize information.  Worksheets look like grids with intersecting columns and rows that form boxes called cells.

When Excel opens you get 16 worksheets on your screen at once, all arranged into a workbook file.  You can add more if 16 isn’t enough.  See the tabs at the bottom of the screen.  Each one represents a single worksheet. 

Ø     To move to a new worksheet in the workbook, click the worksheet tab at the bottom of the worksheet area.

Ø     The active worksheet’s tab always appears to be the one on top of the tab stack.

Ø    
To scroll through the tabs, use the arrow buttons to the left of the horizontal scroll bar.

Use arrows to scroll

Moving in Excel

To move in Excel use the keyboard or mouse.  As you move the mouse, notice the pointer changes shape—sometimes an arrow, and sometimes a giant plus symbol.  When inside the worksheet area it takes the plus shape and outside the work area it’s the familiar arrow.  To move from cell to cell simply click and the cell becomes selected (highlighted).  A dark line appears around the cell (this is the selector).  When you select a cell, it is active and ready to accept any numbers or text you type.

Selected Cell   

Using the keyboard is a little harder.  You have to combine keys to move around.

Location

Sometimes you can get lost in the cells on your worksheet.  Its imortant to know the addresses of the cells on you worksheet.  Like a grid, each cell has a reference or address based on the row and column it is in.  Column headings (letter)

This cell is in column F, row 13, so its name is F13.

If you become confused about what cell you are in, look at the reference area below the menu bar, called the Name or Reference box (“You are Here” Marker).

Open a Workbook

Ø     Open the File menu and choose the New command.  This will open the New dialog box where you can select a spreadsheet template or a blank one to create for yourself.

Ø     OR click the New Workbook button on the toolbar.  This will open the blank template that you can modify.

To open existing workbook files

Ø     Pull down the File menu and choose Open.  Select the name of the file from the Open dialog box and click OK. 

Ø     OR click on the Open button on the toolbar.

Ø     OR click on the file listed at the bottom of your File drop-down menu.  Excel keeps track of files you have worked on in the past and displays them at the end of the File menu.

To close a workbook

Ø     Pull down the File menu and choose Close.  This closes the file but leaves Excel running.  If you haven’t saved the file, you will be prompted.

Ø     For a faster close, click the workbook’s Close button in the upper right corner of the workbook window area.

Ø     OR double-click the workbook’s Control-menu icon located in the upper left corner of the workbook window.

To close the whole program

Ø     Open the File menu and choose Exit.

Ø     Click the Close button in the upper right corner of the screen (THE x)

Ø     OR double-click the program’s Control-menu icon in the upper left corner of the Excel screen.

To save

Ø     Open the File menu and choose the Save command

Ø     Click the Save icon on the toolbar.

Ø     Click the Save As to change the file name or location.

Print it

Ø     Open the File menu and Click Print (this will give you the print dialog box where you can make choices of printer, pages to print, Number of copies, etc.)

Ø     Click the print icon on the toolbar (this prints to the default printer and offers no options to the user)

Multiple Workbooks Open Simultaneously

To copy and paste, or refer to data in other workbooks, you can have multiple documents open and can cycle through them by pressing Ctrl+F6.  Open one workbook and without closing it, open another.  Excel displays the active workbook onscreen.  You can use the Window menu to switch between workbooks also.

If you want two workbooks visible at the same time to compare data or drag and drop between them it is possible. 

Ø     Open the Window menu and choose the Arrange command.

Ø     The arrange dialog box appears.  Select from any of the four options to display the workbooks on the same screen.  The option names descrive how the workbooks will appear. 

Ø     Click OK and the multiple workbooks appear on screen.

The active workbook’s title bar appears bold

Two open workbooks displayed at once

To return to having only one workbook, close the other window(s) and click that active workbook window’s Maximize button.

To move between worksheets simply click the tabs at the bottom of the worksheet page.  To add a worksheet, select the worksheet before where you want to add and use the Insert Worksheet command.  (Insert menu, Worksheet)

Excel will give it the next number after 16 even if it is inserted after sheet number 6.

Deleting Worksheets

Ø     Select the worksheet you want to delete.

Ø     Open Edit menu and click Delete Sheet

Ø     A dialog box will appear to ask for confirmation.   Click OK.

Moving Worksheets

To move to another workbook or copy from one workbook to another

Ø     Select the worksheet you want to move or copy

Ø     Open the Edit menu and choose the Move or Copy Sheet command. A dialog box will appear.

The Move or Copy Dialog box.

Ø     To move to a different workbook select the workbook’s name from the To Book drop-down list.

Ø     In the Before sheet list box, choose the worksheet before which you want the selected worksheet placed. 

Ø     To copy the selected worksheet instead of moving it, do the same theig and click the Create a Copy check box.

Ø     When finished, click OK.

Copying Worksheets

To copy several worksheets into another file

Ø     Click the first tab in the group, press and hold down the Shift key and click your mouse over the last worksheet’s tab.

Ø     To select several worksheets that are not in sequential order, click the first tab in the group, press and hold down the Ctrl key, and click each of the other worksheets’ tabs that you want selected.

Templates

Excel’s spreadsheet templates are available.  You can also begin your own collection of templates.  Depending on the type of installation you selected, you will have a few or a lot of templates to choose from in your New dialog box. 

Ø     Open File, New.  The New Dialog box appears.

Ø     Search through the tabs to find a spreadsheet you want to use.

Ø     Click the spreadsheet icon and preview what the spreadsheet looks like in the Preview area.

Ø     To select a template, double-click its name or highlight it and click the OK.

You can turn an existing spreadsheet into a template using the Template Wizard. 

¨     Open the spreadsheet that you want to turn into a template.

¨     Pull down the Data menu and select the Template Wizard.

¨     You can also save any spreadsheet as a template using the Save As command.  When the Save As box appears, give the file name and select Template from the Save AS Type drop down box.  Click Save and the file is now a template.

Return to Spreadsheets Handout