1 / 48

Excel Formulas

Excel Formulas. File -> Open -> 02b-datastart.xlsx. Delete Rows. Select cell A2. Click Delete -> Delete Sheet Rows. Find and Replace. Click on any blank cell then Click Find & Select -> Replace. Find what: N.A. Click Replace All. Click OK. Select Cell I1. Type Number of Shares.

clyde
Télécharger la présentation

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. Excel Formulas

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

  3. Delete Rows

  4. Select cell A2

  5. Click Delete -> Delete Sheet Rows

  6. Find and Replace

  7. Click on any blank cell thenClick Find & Select -> Replace

  8. Find what: N.A.

  9. Click Replace All

  10. Click OK

  11. Select Cell I1

  12. Type Number of Shares

  13. Resize Columns

  14. Click on the line between Columns I and J and drag to the right to expand the column

  15. Can now see Number of Shares

  16. Using Formulas

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

  18. Click on Cell I2

  19. Type =G2/H2This means number in cell G2 is divided by number in cell H2

  20. Calculates Number of Shares = MarketCap/Price

  21. Copying Formulas:Relative References

  22. Click on Cell I2

  23. Press Ctrl+c on keyboard to copy cell

  24. Click on Cell I3and press Ctrl+v

  25. Have now calculated the number of shares of the next company

  26. Double-Click on the bottom right corner of I3

  27. The rest of the column is filled with the same formula

  28. 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

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

  30. Paste Values

  31. To keep just the values you can copy and ‘paste values’

  32. Click on the top of Column I to select the whole column

  33. Press Ctrl+c on keyboard to copy column

  34. Click on Home -> Paste -> Paste Values -> 123

  35. The cells now contain values which will not change

  36. Keyboard Shortcuts tomove around

  37. Click on cell A1

  38. Press Ctrl + Right Arrow on keyboard to move to right of dataset

  39. Press Ctrl + Down Arrow on keyboard to move to bottom of dataset

  40. Press Ctrl + Up Arrow on keyboard to move back to top of dataset

  41. Press Ctrl + Shift + * on keyboard to select whole dataset

  42. Freeze Windows

  43. Click on cell B2

  44. Click View -> Freeze Panes -> Freeze Panes

  45. Press Ctrl + Down Arrow to move to bottom of dataset

  46. The top row and first column are always visible from everywhere in dataset

  47. File -> Save As -> 02c-datacompleted.xlsx

  48. Challenge • Calculate a column showing the price of 10 shares in each company • Copy and paste values

More Related