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

Row 2 has been deleted

Resizing Columns

Click on Cell I1

Type Number of Shares

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

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

Using Formulas

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

Click on Cell I2

Type =E2/F2This means the number in cell E2

will be divided by the number in cell F2

Calculates Number of Shares = MarketCap/Price

Copying Formulas:Relative References

Click on Cell I2

Press Ctrl+c on keyboard to copy cell

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

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

Have now calculated the number of shares of every company

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

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

Copying Formulas:Absolute References

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

Click on cell I2Its formula refers to cells E2 and F2

Click on cell I3Its formula refers to cells E3 and F3

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

Select cell I2, and press Ctrl+c to copy

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

Click on cell M8Original formula was E2/F2

The reference has changed both column and row numbers

Select columns J to MPress DEL on keyboard

Click on cell I2

Change formula to =E2/$F$2

Press Ctrl+c to copy

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

Select cell M8

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

Any column or row with a dollar sign does not change

Select columns J to MPress DEL on keyboard

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

Paste Values

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

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

Press Ctrl+c on keyboard to copy column

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

The cells now contain values which will not change

Keyboard Shortcuts tomove around

Click on cell in the bottom right of the dataset

Press Ctrl + Left Arrow on keyboard to move to left of dataset

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

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

Freeze Windows

Click on cell B2

Click View -> Freeze Panes

Press Ctrl + Down Arrow to move to bottom of dataset

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

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

Challenge

• Calculate a column showing the price of 10 shares in each company

• Copy and paste values