MS Excel: Part 1

1. MS Excel: Part 1 Basic Excel Functions Microsoft Office | Mr. Mistler

2. Ribbons • Home • Insert • Page Layout • Formulas • Data • Review

3. What is Excel? • Microsoft Excel is a spreadsheet program which allows one to enter numerical values or data into the rows or columns of a spreadsheet, and to use these numerical entries for such things as calculations, graphs, and statistical analysis.

4. Selecting Cells • Selected cells are outlined in black • The columns and rows change to yellow when selected

5. Autofill • To make typing faster, you can autofill a text or type entry. • Fill in the first cell, then the second cell and drag down to select below. • Ex 125, 130, 140 • You must define the first two cells so Excel can identify the pattern

6. Add a custom list • Click the Office Button • Click the Excel Options button • Click the Popular tab • Click the Edit Custom Lists button • Click import

7. Add columns & rows • Click the Home tab on the ribbon • Click insert • Click Insert Sheet Columns or Insert Sheet Rows • *You can also right click a column heading and click insert (it will insert a row or column to the left of the selected row or column) • TIP: Select 2 rows and click insert, then 2 rows will be inserted. Select 4 columns and click insert, you will get 4 new columns.

8. Delete columns & rows • Click the heading of the column you want to delete. • Click the Home tab • Click delete • Click Delete Sheet Columns or Delete Sheet Rows

9. Turn on Text Wrapping • Click the home tab • Click the Wrap Text button

10. Center Data Across Columns • Select the cell containing the text you want to center, and the cells you want to center across. • Click the Home tab • Click the Merge and Center button

11. Freeze a Column or Row • You can freeze a column or row to keep the labels in view as you scroll through larger worksheets. You cannot scroll the area that you freeze, but you can scroll the unfrozen areas of the worksheet. • Click View • Click the Freeze Panes • Click Freeze Panes

12. Excel Practice 1 (iTunes Rentals) • Download the “ExcelPractice1.xls” Excel file • Text wrap the header “iTunes Rentals – Third Quarter 2011” and center it across the entire table. • Freeze the Movies column • Resize column C • Delete the “D” column • Add a column row after Zoolander and insert your favorite movie. • Add a column before column A and insert an autofill list of the weekedays. • Save your file as “yourlastname.excelpractice1.xls” • Upload the spreadsheet to Google Docs

13. MS Excel Part 2 Formatting

14. Worksheet Formatting • Assign worksheet name • Delete a worksheet • Add a worksheet • Copy a worksheet • Move a worksheet • Format worksheet tab color

15. Find and Replace Data

16. Sort Data

17. Filter Data

18. Insert a Comment

19. Track Changes

20. Page Setup

21. Header & Footer

22. Alignments

23. Fonts

24. Using Multiple Sheets

25. Printing

26. Cell Borders

27. Linking Cells

28. Hyperlinks

29. Save as a Web Page

30. MS Excel Part 3 Formulas

31. Formulas • In math, a formula might be 2+2=4 but in Excel, formulas work a bit differently. • All Excel formulas begin with an = sign • Ex =2+2 • The = sign immediately tells Excel to recognize any subsequent data as a formula rather than a regular cell.

32. Mathematical Operators • + Addition • - Subtractions • * Multiplication • / Division • % Percentage • ^ Exponentiation

33. Operator Precedence • Excel performs a series of operation from left to right, but gives some operators preferences over others. • First Parentheses • For example, If you want to calculate the average in A2, B2, and C2, and you enter the equation as =A2+B2+C2/3, you will be wrong. • Correct: =(A2+B2+C2)/3 • Second Exponential Equations • Third Multiplication and Division • Fourth Addition and Subtraction

34. Creating Formulas • Click in the cell you want to assign a formula • Type = • Click the first cell you want to reference in the formula. • Type an operator for the formula. • Click the next cell that you want to reference. • Press enter • You can also click accept or cancel onthe formula bar. • Note: If you change any of the values in the cells referenced in your formula, the results automatically update to reflect any changes.

35. Excel Practice 2 (Music Sale) • Download Music Sale.xlsfrom the Distribution Folder in Google Docs. • Find the average price of all of the instruments and put it in cell B12. Label cell A12 as “Average Price” • Find the total cost of all instruments and input it into cell B14. Label cell A14 as “Total Cost” • Save as yourlastname.musicsale.xlsx • Upload it into Google Docs

36. Define a Range Name • You can assign names to the cells and ranges of cells that you work with to make it easier to work with. • A range is simply a rectangular group. It can also consist of a single cell. • Example: B24:C24 • Naming ranges can help you decipher formulas • Ex: a name such as Sales_Totals, is much easier to recognize than a generic reference such as B24:C24

37. How to define a range name • Select the range you want to name • Click inside the name field on the formula bar. • Type a name for the range. • Must start with a letter or an underscore. And you cannot use a hyphen or space.

38. Reference Ranges in Formulas • Referencing ranges in formulas can speed up calculations. • 1.Click in the cell that you want to apply a formula • 2.Type the formula you want to apply. • Ex: =AVERAGE(Quarter1, Quarter2, Quarter3)

39. Reference Cells from Different Worksheets • Must specify the worksheet name, followed by an exclamation mark, and then by the cell address. • Sheet2!D12 or Sheet2!Quarter1

40. Applying a Function • If you want an easier way to enter a formula, you can use built-in formulas, called FUNCTIONS. • Because all functions are formulas, they must start with an = • Click the function wizard button (fx) or on the formulas tab. • Click the function you want to apply.

41. Common Functions • SUM • INT • ROUND • ROUNDDOWN • COUNT • AVERAGE • MIN • MAX • MEDIAN • PMT • RATE • TODAY • IF • AND • OR • ****See “commonfunctions.docx” in the Office Distribution folder for more details

42. Common Function Errors • #NULL! • #DIV/O! • #VALUE! • #REF! • #NAME? • #NUM! • #N/A

43. Excel Practice 3 (Office Sales) • Download Office Sales.xls from Google Docs • Change the colors of the worksheets • In the Computer Items worksheet, find the average cost for each year. • In the Desk Supplies worksheet, find the average cost for each year. • Copy the “Desk Supplies” worksheet and then copy all of the computer items sales so that both worksheets look like their merged into this new worksheet. Make sure to skip a row. • Rename this new worksheet to “Total Office Sales” • Delete worksheet 3 • The rest of the directions refer to the “Total Office Sales” worksheet • Find the min, max, and median of sales for each year. • Use the sum function to add together all of the sales for all objects and for every year. (Finding the total sales for 02, 03, and 04 for all products) • *****Make sure to label all of your calculations • Save as yourlastname.officesale.xls and upload it into Google Docs

44. PMT Function • =PMT (rate, nper, pv, fv, type) • PMT means payment for loans at a fixed rate of interest. • Rate=interest rate (per period) • nper=number of periods • pv=present value (or what it’s worth currently) • fv=future value or what you want the number at the end to be • type=is the value 0 for payments made at the end of the period or the value 1 for payments made at the beginning of the period (if you omit the optional type argument, Excel assumes that the payment is made at the end of the period).

45. Excel Practice 4 (Bank Loan) • Excel Practice 4: Download Bank Loan.xls from the Office Distribution folder in Google Docs. • Find the monthly payment using the PMT function. Click here for directions on how to use the PMT function. • Then fill in the total paid and total interest in the worksheet. Insert a new row before row 1 and insert your name into cell A1. • Upload the file as yourlastname.bankloan.xls into Google Docs.

46. Bank Loans. xls • =PMT (rate, nper, pv, fv, type) • nper=number of periods (60) • pv=present value(10,000) • fv=future value or what you want the number at the end to be (Don’t enter anything because you want to pay it off) • type=is the value 0 for payments made at the end of the period or the value 1 for payments made at the beginning of the period (if you omit the optional type argument, Excel assumes that the payment is made at the end of the period).

47. MS Excel Part 4 Formatting Worksheets

48. AutoSum • Autosum automatically totals the contents of cells. For example, you can quickly total a column of sales figures. Autosum works by guessing which surrounding cells you want to total. • Click in the cell where you want to insert autosum. • Click the Formulas tab on the ribbon • Click the Autosum button • If you click the down arrow in Autosum, you can select other common functions, such as Acerage.

50. Audit a Worksheet for Errors • When dealing with larger worksheets, it is not always easy to locate the source of a formula error when scrolling through the many cells. To help you with errors that arise, you can use Excel’s Formula auditing tools to examine and correct errors.