Return to IFS 105 Notes!

Excel
Project 2

Formulas, Functions, Formatting, and Web Queries

Formulas

• Formulas begin =, +, -

Can use uppercase or lowercase in formulas

• Two-Digit Years - Excel interprets the years as follows:

1. 00 through 29 as years 2000 through 2029
2. 30 through 99 as years 1930 through 1999

• Use four-digit years to ensure that Excel interprets year values the way intended

Excel Arithmetic Operators

• Addition +
• Subtraction -
• Multiplication *
• Division /
• Percentage %
• Exponentiation ^

Order of Operations

1. Negation (-)
2. Percents (%)
3. Exponents (^)
4. Multiply (*) and Divide (/)
5. Addition (+) and Subtraction (-)

Note: Parentheses used to override order

Smart Tags

• smart tag - is a button that automatically appears
  • click it to display menu of options to modify previous operation or obtain additional information
• smart tag indicator - small triangle located in one of the corners of a cell
  • if you select a cell with a smart tag indicator, the smart tag button displays

Using Functions

• Point mode - allows you to select cells to be used in a formula using a mouse
• Function - prewritten formula that takes a value or values, performs an operation, and returns a value or values
• Argument - value given to function to perform an operation on

Verifying Formulas

• Range Finder - used to check which cells are being referenced in the formula assigned to active cell
  • Advantage: allows immediate changes to the cells referenced in a formula
• Formula Auditing command (Tools Menu) - displays a menu of auditing commands, give detailed analysis and different ways to view formulas
• Error Checking command - (Tools Menu) - checks all formulas to ensure referencing valid data

Number Format Buttons
(Formatting toolbar)

• Comma Style Button - inserts commas and adds two decimal places
• Currency Style Button - same as comma style button except adds $ to left of number
• Percent Style Button - displays number in Percent style with no decimal places
• Decrease Decimal button - used to eliminate decimal places
• Increase Decimal button - used to increase decimal places

Note: ### indicates cell not wide enough

Conditional Formatting

• conditional formatting - Excel applies the formatting that appears only when the value in cell meets the specified conditions

Note: - can be applied to a single cell, a range of cells, or the entire workbook

Example: - change the background color of a cell less than zero

• condition - made up of two values and a relational operator, is true or false for each cell in the range

Cell value is less than 0

2 Ways to Change Widths of Columns

1. Change width one column at a time
2. Change width of a series of adjacent columns

Best fit - width altered so that the widest entry will fit in the column

3 Methods of Changing Widths

1. Best fit - double click on border-line to the right of column
2. Dragging - drag the right border of a column heading (or group of selected cells)
3. Menus
       Format Menu - choose Column command
       Shortcut Menu - choose Column Width

Hiding Rows and Columns

• Hiding Set Column Width or Row Height to zero
• Column width (0 - 255) default (8.43 characters)
• Row height (0 - 409) default (12.75 points)
• Unhiding - drag width border to right or height border down

2 Ways to Change Height of Rows

1. Format Menu - choose Row command
2. Shortcut Menu - choose Row Height command

Note: Excel automatically adjusts row height to best fit

• Switch back to default - double-click row border

Print Preview Command

• Print Preview command - previews outputs
  • will only preview selected sheets
• To select additional sheets, hold down SHIFT key and click any sheet tabs to be included in the preview or printout
• Portrait orientation - printout is across the page width of 8.5 inches
• Landscape orientation - printout is across the page length of 11 inches

Printing Formulas in the Worksheet

• Values version - printed exactly as it appears on screen
• Formulas version - prints what was originally entered into the cells
      Note: toggle between values and formula versions by pressing (CTRL+ ‘)
• Debugging - finding and correcting errors
• Fit to option - fits spreadsheet on single page

Using a Web Query

• Web query - retrieve data stored on WWW site. (real-time - no more than 20 minutes old)
• Excel Web Queries 
  • MSN MoneyCentral Investor Currency Rates - Currency rates
  • MSN MoneyCentral Investor Major Indices - Major indices
  • MSN MoneyCentral Investor Quotes - Up to 20 stocks of your choice
• External Data toolbar
  • Refresh All button - refresh data for all stocks
  • Query Parameters button - change the stock symbol parameters

Changing Sheet Names

• Sheet names can be up to 31 characters in length, changed by double-clicking the sheet tabs
• Tab split box - can be dragged to increase the number of tabs that show
• Tab scrolling buttons - used to scroll between sheet tabs

Note: tab scrolling buttons do not select sheet tabs. Click a sheet tab to select it.

E-mailing Workbook within Excel

• one of the features of e-mail is ability to attach Office files, such as Excel workbooks to an e-mail
• In the past, if you wanted to send a workbook you saved it, closed the file, launched your e-mail program, then attached the workbook to e-mail before sending it
• A new feature of Office 2000 is capability of e-mailing worksheet or workbook directly from within Excel

Note: must have e-mail address and one of the following e-mail programs: Outlook, Outlook Express, Microsoft Exchange Client, or another 32-bit e-mail program compatible with Messaging Application Programming Interface 

In the Lab 1 (HOMEWORK #5)

• Page Ex 132-133

End Excel Project 2

Computer Assignment 5

Return to IFS 105 Notes!