1 / 67

02. Excel Formulas

02. Excel Formulas. File -> Open -> 02b-datastart.xlsx. Find and Replace. Click Find & Select -> Replace. Find What: N.A. Click Replace All. Click OK. Click Close. All instances of N.A. have been replaced . Delete Rows. Click on Cell A2. Click Delete -> Delete Sheet Rows.

warren
Download Presentation

02. Excel Formulas

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 02. Excel Formulas

  2. File -> Open -> 02b-datastart.xlsx

  3. Find and Replace

  4. Click Find & Select -> Replace

  5. Find What: N.A.

  6. Click Replace All

  7. Click OK

  8. Click Close

  9. All instances of N.A. have been replaced

  10. Delete Rows

  11. Click on Cell A2

  12. Click Delete -> Delete Sheet Rows

  13. Row 2 has been deleted

  14. Resizing Columns

  15. Click on Cell I1

  16. Type Number of Shares

  17. Click on right side of Column I and drag to the right to widen column

  18. Column I is now wide enough to show all of the text

  19. Using Formulas

  20. All formulas begin with an =Can use maths operators + - * /

  21. Click on Cell I2

  22. Type =E2/F2This means the number in cell E2 will be divided by the number in cell F2

  23. Calculates Number of Shares = MarketCap/Price

  24. Copying Formulas:Relative References

  25. Click on Cell I2

  26. Press Ctrl+c on keyboard to copy cell

  27. Click on Cell I3, then drag mouse to bottom of column

  28. Press Ctrl+v to copy formula to all cells in this column

  29. Have now calculated the number of shares of every company

  30. All of the cells in this column are formulas. If you change the original data the result changes.

  31. Go back to top of dataset by pressing Ctrl + Up arrow on keyboard

  32. Copying Formulas:Absolute References

  33. The formulas used so far have been relative references, meaning that they change when they are copied and pasted

  34. Click on cell I2Its formula refers to cells E2 and F2

  35. Click on cell I3Its formula refers to cells E3 and F3

  36. When copying and pasting: • If we want to refer to the same cell all the time we use $ signs before both row and column $A$1 • If we want only the column to always stay the same we use a $ sign before the column letter $A1 • If we want only the row to always stay the same we use a $ sign before the row number A$1

  37. Select cell I2, and press Ctrl+c to copy

  38. Select cells J2 to M8, and press Ctrl+v to paste

  39. Click on cell M8Original formula was E2/F2The reference has changed both column and row numbers

  40. Select columns J to MPress DEL on keyboard

  41. Click on cell I2

  42. Change formula to =E2/$F$2

  43. Press Ctrl+c to copy

  44. Select cells J2 to M8 and press Ctrl+v to paste

  45. Select cell M8

  46. The original reference was E2/F2The new reference is I8/F2

  47. Any column or row with a dollar sign does not change

  48. Select columns J to MPress DEL on keyboard

  49. Have deleted the sample cellsThe cells in Column I are still formulas

  50. Paste Values

More Related