Return to IFS 105 Notes!

Excel
Project 1

Creating a Worksheet and Embedded Chart

What Is Excel ?

4 Major Parts

1. Worksheets - enter, calculate, manipulate and analyze data
2. Charts - pictorially represent data
3. Databases - manage data
4. Web Support - save workbooks in HTML format to be viewed in browsers, access real-time data using Web queries

Task Pane

• task pane - separate window that enables users to carry out tasks more efficiently
• As you work through creating a worksheet, certain Excel operations result in displaying a task pane.
• Can display or hide a task pane by clicking the Task Pane command on the View menu

4 Task Panes in Excel

1. New Workbook task pane
2. Clipboard task pane
3. Search task pane
4. Insert Clip Art task pane

Can activate additional task panes by clicking the down arrow to the left of Close button on task pane title bar and selecting task pane in list

The Workbook

• Workbook - like a notebook, Excel creates an empty workbook when it starts (Book1)
• Worksheets - sheets inside the workbook (3 sheets (default) max is 255)
• Sheet tab - tabs at bottom of Workbook containing each sheets name
• Tab scrolling buttons - can be used to scroll through the sheet tabs

The Worksheet

• Worksheet - rectangular grid of rows (numbers) and columns (letters)
• Cell - intersection of row and column
• Cell reference - unique address, coordinates of the intersection of a column and row (B7)
• Gridlines - horizontal and vertical lines

The Worksheet

• Active Cell - is the one in which you can enter data
  • heavy border surrounds cell
  • active cell reference displays in Name box
  • column and row headings of active cell highlighted
• Active cell reference - address of active cell displayed in Name box
• Name box - above column A in formula bar

Worksheet Window

Note: worksheet is 256 columns and 65,536 rows (16,777,216 cells)

• Worksheet Window - small fraction of active worksheet displays
• scroll bars, scroll arrows, scroll box - move window around active worksheet
• tab split box - drag to increase or decrease length of horizontal scroll bar

Menu Bar

• Worksheet menu bar - displays when you start Excel

• short menu - displays listing most recently used commands when menu name clicked on menu bar

• full menu - lists all commands associated with menu choice on menu bar

• hidden command - recessed command does not display on short menu

• dimmed command - displays in faint type, not available for current selection

Note: Menu bar can change to include other menu names depending on the type of work being done (charting)

Standard and Formatting Toolbar

• ScreenTip - identifies the button on toolbar when mouse pointer is positioned over it
• Toolbars on Separate Rows - click the Show Buttons on Two Rows command in the Toolbar Options list
• Sizing Toolbar Buttons - click View on menu bar, pointing to Toolbars, click Customize on submenu, click Options tab, click Large icons, click Close
• More Buttons button - can be used to display the hidden buttons

Status Bar

• status bar - gives brief description of selected command in a menu, or the function of a button in a toolbar pointed to by the mouse
• Mode indicators - such as Enter and Ready, display on status bar specify current mode of Excel
  • Ready - Excel is ready to accept next command or data entry
  • Enter - Excel is in the process of accepting data through keyboard into active cell
• AutoCalculate area - can be used in place of calculator to view sum, average, or other types of totals of a group of cells
• Keyboard indicators - NUM, CAPS, and SCRL show which keys are engaged

Speech Recognition and Speech Playback

• Office Speech Recognition software - when installed with microphone, can speak names of toolbar, buttons, menus, menu commands, list items, alerts, and dialog box controls, such as OK and Cancel. Can also dictate cell entries, such as text and numbers.
• Language bar - used to indicate whether to speak commands or dictate cell entries

2 ways to display Language bar

1. click Language Indicator button in taskbar tray status area, click Show the Language bar on menu
2. click Tools on menu, point to Speech command, click Speech Recognition command on the Speech submenu

3 Kinds of Cell Entries

1. Labels - headings identify cells
2. Data - Numeric and Alpha
3. Formula - Math functions (built-in)

Selecting a Cell (make active) - point to cell click left mouse button or use arrow keys

Data and Functions

• Label data - left justified into cell
• Numeric data - right justified into cell
  • Numbers include 0-9
  • Special Characters + - ( ) , / . $ % E e
• Sum function - first identify cell to hold Sum use AutoSum button on Standard toolbar
      example: =SUM(B3:B5)
• Range - series of two or more adjacent cells in a column or row, or a rectangular group of cells (B2:D7)

Using the Fill Handle to Copy a Cell to Adjacent Cells

• Copy area - the cells being copied
• Paste area - range of cells receiving the copy
• Relative reference - when copying cell addresses, Excel adjusts them for each new position
• fill handle - small black square located in lower right corner of heavy border around active cell
• Copying using Fill Handle - select copy area, point to fill handle, drag through the paste area

Table Formats and Charts

• Table formats - Excel has several customized format styles that allow you to format body of the worksheet AutoFormat command on Format menu (17 table formats)
• Embedded Chart - graph part of worksheet
• ChartWizard button - used to draw graph after selecting cells to chart (Standard toolbar)
  • legend - identifies each column in chart
  • default chart type - two-dimensional column chart (Excel supports 14 different chart types)

Saving Options

• Tools button - on Save dialog box allows more advanced save options
• General Options command - on tools list box gives ability to save a backup copy of workbook and creating a password and to perform other advanced functions
• backup workbook - means each time a workbook is saved, Excel copies current version to a file with same name, but with the words, Backup of, appended to front of file name
• password - is case sensitive up to 15 characters long
  • case sensitive - means Excel can differentiate between uppercase and lowercase letters
• AutoSave - on Tools menu can be used to automatically save worksheet every 10 minutes
• Saving Worksheet as Web Page - click File, click Save As Web Page, can save as static or dynamic Web Page
  • static - can view worksheet but can not change it
  • dynamic - can modify worksheet using a browser

AutoCalculate Statistics

• Average - average of selected cells
• Count - number of nonblank cells in range
• Count Nums - number of cells with numbers in range
• Max - greatest value in range
• Min - least value in range
• Sum - sum of values in range
• Obtain information about numbers in range by using AutoCalculate area on status bar.

2 Ways to Correct Errors

1. Select the cell - Retype
2. Edit mode (in-cell editing)
       Double-clicking the cell with error switches to Edit mode (cell contents appears in formula bar)

• F2 function key can also be used to select a cell to edit

4 Ways to Clear a Range of Cells

1. DELETE key
       Select range to clear press DELETE key
2. CLEAR command
       Select Range, select Edit menu, choose Clear, choose All
3. Using Shortcut Menu
       Select range, right-click selection, click clear contents
4. Using Fill Handle
       Drag fill handle back into selected range release button

Note: Never press the SPACEBAR to enter a blank character to clear a cell. (text)

In the Lab 1
(HOMEWORK #4)

• Page Ex 58-59
• due

End Excel
Project 1

Computer Assignment 4

Return to IFS 105 Notes!