File gt Open gt 02bdatastartxlsx Find and Replace Click Find amp Select gt Replace Find What NA Click Replace All Click OK Click Close All instances of NA have been replaced ID: 514805
Download Presentation The PPT/PDF document "02. Excel Formulas" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
02. Excel FormulasSlide2
File -> Open -> 02b-datastart.xlsxSlide3
Find and ReplaceSlide4
Click Find & Select -> ReplaceSlide5
Find What: N.A.Slide6
Click Replace AllSlide7
Click OKSlide8
Click CloseSlide9
All instances of N.A. have been replaced Slide10
Delete RowsSlide11
Click on Cell A2Slide12
Click Delete -> Delete Sheet RowsSlide13
Row 2 has been deletedSlide14
Resizing ColumnsSlide15
Click on Cell I1Slide16
Type Number of SharesSlide17
Click on right side of Column I and drag to the right to widen columnSlide18
Column I is now wide enough to
show all of the textSlide19
Using FormulasSlide20
All formulas begin with an =
Can use maths operators + - * /Slide21
Click on Cell I2Slide22
Type =E2/F2
This means the number in cell E2
will be divided by the number in cell F2Slide23
Calculates
Number of Shares =
MarketCap
/PriceSlide24
Copying Formulas:
Relative ReferencesSlide25
Click on Cell I2Slide26
Press
Ctrl+c
on keyboard to copy cellSlide27
Click on Cell I3, then drag mouse
to bottom of columnSlide28
Press
Ctrl+v
to copy formula
to all cells in this columnSlide29
Have now calculated the number of shares of every companySlide30
All of the cells in this column are formulas. If you change the original data the result changes.Slide31
Go back to top of dataset by pressing
Ctrl + Up arrow on keyboardSlide32
Copying Formulas:
Absolute ReferencesSlide33
The formulas used so far have been relative references, meaning that they change when they are copied and pastedSlide34
Click on cell I2
Its formula refers to cells E2 and F2Slide35
Click on cell I3
Its formula refers to cells E3 and F3Slide36
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$1Slide37
Select cell I2, and press
Ctrl+c
to copySlide38
Select cells J2 to M8,
and press
Ctrl+v
to pasteSlide39
Click on cell M8
Original formula was E2/F2
The reference has changed both column and row numbersSlide40
Select columns J to M
Press DEL on keyboardSlide41
Click on cell I2Slide42
Change formula to
=E2/$F$2Slide43
Press
Ctrl+c
to copySlide44
Select cells J2 to M8
and press
Ctrl+v
to pasteSlide45
Select cell M8Slide46
The original reference was E2/F2
The new reference is I8/F2Slide47
Any column or row with a
dollar sign does not changeSlide48
Select columns J to M
Press DEL on keyboardSlide49
Have deleted the sample cells
The cells in Column I are still formulasSlide50
Paste ValuesSlide51
To keep just the values you can copy and
‘paste values’Slide52
Click on the top of Column I to
select the whole columnSlide53
Press
Ctrl+c on keyboard to copy
columnSlide54
Click on Home -> Paste -> Paste Values -> 123Slide55
The cells now contain values
which will not changeSlide56
Keyboard Shortcuts to
move aroundSlide57
Click on cell in the
bottom right of the datasetSlide58
Press Ctrl + Left Arrow on keyboard to
move to left of datasetSlide59
Press Ctrl + Up Arrow on keyboard
to move to top of datasetSlide60
Press Ctrl + Shift + *
on keyboard to select whole datasetSlide61
Freeze WindowsSlide62
Click on cell B2Slide63
Click View -> Freeze PanesSlide64
Press Ctrl + Down Arrow to move to
bottom of datasetSlide65
The top row and first column are always visible from everywhere in datasetSlide66
File -> Save As -> 02c-datacompleted.xlsxSlide67
Challenge
Calculate a column showing the price of 10 shares in each company
Copy and paste values