Excel Spreadsheets

1. Excel Spreadsheets The Basics ICS100 – Spring 2007 D. Pai

2. What to Know • What is Excel • What is a Spreadsheet • The Excel Window • Toolbars (Shortcut Bars) • The “HELP” Functions • Basic Operations in Excel

3. What is Excel? • Excel is the spreadsheet program with the Microsoft Office Applications Suite • Excel also has charting/graphing capabilities to visualize the data • Excel also performs some database functions such as sorting a list of data

4. What is a Spreadsheet? • In the realm of accounting jargon a "spread sheet" or spreadsheet was and is a large sheet of paper with columns and rows that lays everything out about transactions for a business person to examine. It spreads or shows all of the costs, income, taxes, etc. on a single sheet of paper for a manager to look at when making a decision. • An electronic spreadsheet organizes information into software defined columns and rows. The data can then be "added up" by a formula to give a total or sum. The spreadsheet program summarizes information from many paper sources in one place and presents the information in a format to help a decision maker see the financial "big picture" for the company. (Power, D.J., 2000, www.dssresources.com/history/sshistory.html)

5. The Excel Window Title Bar Active Cell Columns (Letters) Menu Bar Rows (Numbers) Active Sheet Selector Status Bar

6. Excel Window (Cont.) Standard Toolbar Formula Bar Cell Reference Formatting Toolbar Fill Handle is the black square in the lower right hand corner of the active region Drawing Toolbar

7. What are Toolbars (Shortcut Bars)? Help Select “View  Toolbars” Toolbars provide common functions used in Excel for single-click access (Shortcuts to things you can do)

8. HELP!!!! • Two Additional forms of HELP with the Microsoft Office Suite • Office Assistant – a visible icon that provides tips and allows for you to type questions in a semi-conversational manner to get answers • The Help Menu – Provides Table of Contents, Answer Wizard and Index forms of accessing help

9. Office Asst is the Paper Clip

10. Office Assistant Allows you to Ask for Help in the Form of a Question

11. Help Menu has 3 Ways to Access Help

12. Basic Concepts in Excel • Entering Text in a Cell • Copying Text from One Cell to Another • Creating a Numerical Series • Entering a Formula in a Cell • Inserting a Row or Column into a Worksheet

13. Entering Text in a Cell To Enter Text (or numbers) in a Cell, Just Click the Mouse in the Cell Where you would Like the Text and Start Typing

14. Copying Text from One Cell to Another • To Copy Text, Just Like in Word, Select the Cell (or Cells), then use: • Edit  Copy • Select new Location • Edit  Paste • Click and Drag to Select Many Adjacent Cells (Range of Cells) • Ctrl-Click to Select Multiple Non-Adjacent Cells or Ranges

15. Creating a Numerical Series • To Create an Arithmetic Series… • 1,2,3… or 1,3,5… or 1,4,7,… • Enter First 2 Numbers in Your Series in Adjacent Cells • Click on Fill Handle and Drag to Desired Length • NOTE: This works with Dates and Anything that Ends with a Number

16. Numerical Series Example • Using the Technique Previously Defined… • Create the following series • Odd numbers from 1-11 • Dates from 3/14/07-3/19/07 • Listing from PO1001 – PO1006 • Try to create • Listing from 1001a – 1001f • What did you get for this last one?

17. Entering a Formula in a Cell • To Enter a Formula in a Cell… • Must Start with… • THE = SIGN • Math Symbols include • +  Addition • -  Subtraction • *  Multiplication • /  Division • ^  Exponent (to the Power of) • Use Parentheses to Make Sure the Order of your Processing is Correct

18. Formatting the Worksheet

19. What is Formatting? • Changing how things are displayed in Excel for emphasis or aesthetic reasons • Can format cells, columns, rows, sheet • Can also use pre-existing formats provided by Excel using Autoformat • Can also define the contents of a cell to be of a certain type

20. Format Cells for Numbers • Options available are provided through the “Format  Cells” selection on the menu bar • Some common shortcuts are available on the formatting toolbar • $ - Displays contents of cell as currency and includes a $ in the cell • % - Displays contents of cell as a percentage • , - Displays commas in cell numbers • .0 – Increases # of digits after decimal by one • .0 – Decreases # of digits after decimal by one

21. Aligning Data • Once range of cells is selected (could be columns or rows also), select “Format  Cells” from Menu Bar • Select the “Alignment” tab • You can align Horizontally, Vertically, Rotate • You can also wrap the text in a box, shrink text to fit the box and merge cells

22. Changing Fonts • Same as aligning data except use the Font tab in the Format Cells dialog box • Can change: • Font • Font style • Size • Color • Underline • Effects

23. Changing Cell Borders • In Format Cells dialog box select Border tab • Option Available include: • Presets • Manual Selection of borders • Line styles • Color • Also some limited shortcuts in the formatting toolbar

24. Changing Cell Colors • In Format Cells dialog box select Patterns • Can change the background color of the selected cells as well as the pattern • Can Also change using the “Fill Color” icon in the formatting toolbar

25. Inserting a Row or Column into a Worksheet • To Insert a Row or Column… • First Select the Row below where you want your new row or the Column to the right of where you want your new column • Then select “Insert  Row or Column” from the Menu Bar

26. Changing the Column Width • Basic Methods • Dragging the border of a column by selecting the edge in the column header area (where the letters are) • Select the column and then select “Format  Column” from the menu bar • Double-click on the bar between 2 column letters (or row numbers) to autofit the width of the column (or row) to its contents • NOTE: IF A CELL IS FILLED WITH “####” IT MEANS THAT THE NUMBER IS TOO BIG TO FIT IN THE CELL – INCREASE THE COLUMN WIDTH TO VIEW THE NUMBER

27. Hide a Column • Removes the column from view, Does not change contents of the cells nor any calculation performed using these cells • Once column that you want to hide has been selected, choose “Format  Column  Hide” from the menu bar • To UNHIDE – Select columns on both sides of hidden column and choose “Format  Column  Unhide” from the menu bar

28. Changing Row Height and Hiding Rows • Same as for columns except select the row options

29. AutoFormat • Simple to use • Large selection of formats already prepared • Select range of cells to be formatted • Select “Format  AutoFormat” from the menu bar • Select desired preset format

30. Some Practice • Starting with a Blank Worksheet • In Column A, Starting at the First Row enter the following data:

31. Now…. • Calculate the price per bottle of beer for Brooklyn Brand Beer • Calculate the total calories in a 6-pack of Brooklyn Brand Beer • Using the fill handle, copy these 2 formulas down for all other brands of beer

32. And Now… • Insert a column to the left of Column A • In new cell A1, enter the title of “Item No.” • In the column, add row numbers using the fill handle to generate only odd row numbers for each brand of beer (ie. 1, 3, 5,…)

33. Your final spreadsheet should look like this Hit the “Ctrl” key and then the “`” key to display formulas in cells rather than the result of the calculation:

34. Now Add Some Extras • Format the Fonts, Cell Colors, Borders and Other Items to Brighten Up the Spreadsheet!

35. Assignment 12 • Create a Spreadsheet and determine the monthly payment for a car loan given the following parameters: • Amount of Loan: $29,000 • Annual Interest Rate (APR): 5.4% • Term of Loan: 5 years • Payments made monthly • The formula to calculate a loan payment is: • Where • P = payment amount per period (ie. Monthly payment) • A = Original amount of the loan (amount borrowed) • i = interest rate per payment period (ie. if the annual interest rate is 12% and payments are made monthly, i = 12%/12 or 1%) • N = Total number of payments • All calculations must be performed in Excel and your cells should have formulas in them…not just the answer • Answer should be in currency format (dollars and cents) • Your spreadsheet should appear appeasing to the eye – use colors, borders, etc…to highlight key items and, in particular, the answer to the problem. • To turn in your assignment, email your excel file as an attachment to: paid@hawaii.edu with the subject line “ICS100, Assignment 12 – your name”