Créer une présentation
Télécharger la présentation

Télécharger la présentation
## Excel Spreadsheets

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -

**Excel Spreadsheets**The Basics ICS100 – Spring 2007 D. Pai**What to Know**• What is Excel • What is a Spreadsheet • The Excel Window • Toolbars (Shortcut Bars) • The “HELP” Functions • Basic Operations in Excel**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**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)**The Excel Window**Title Bar Active Cell Columns (Letters) Menu Bar Rows (Numbers) Active Sheet Selector Status Bar**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**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)**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**Office Assistant Allows you to Ask for Help in the Form of a**Question**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**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**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**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**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?**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**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**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**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**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**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**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**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**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**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**Changing Row Height and Hiding Rows**• Same as for columns except select the row options**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**Some Practice**• Starting with a Blank Worksheet • In Column A, Starting at the First Row enter the following data:**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**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,…)**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:**Now Add Some Extras**• Format the Fonts, Cell Colors, Borders and Other Items to Brighten Up the Spreadsheet!**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”