Exercise
Use Excel formulas for Scenarios

Data Entry and Formatting


Excel formula and charting practical showing paint
sales by state.
Start Excel and enter your
own example data under each state by year only. No totals yet.

Formulas in Cells
start with selecting the cell where you want your answer and tapping
the "="
Common Excel Formulas
Addition 
=sum(first cell coordinate:last
cell coordinate) 
Multiplication 
* 
Division 
/ 
Equals 
= 
ADDITION
 In cell coordinates litres x 2000
type the following: =sum(
 In cell coordinates WA x 2000 hold your
mouse button down and drag to NT x 2000
 Enter.
 Check the resulting sum is roughly right.
COPY FORMULA DOWN
A COLUMN (Fill Down)
 In cell coordinates litres x 2000 > Hold
mouse button down and drag to coordinates litres x 2005
 Alt > E > I > D This
should copy the formula down.
MULTIPLICATION
(Product *)
 Atop the "Sales" column > =I2*J2
(litres paint x $/litre) > Enter >
Check against your mental arithmetic.
 Fill formula down > Check
DIVISION ( / )
 In cell L1, enter how many dollars
and cents one US dollar will buy. eg
1.3
 Formula in the cell below >
=K2/L1
(divides 2000 sales by exchange rate)
 In cell K2 hold mouse down and drag
down the column till you reach the 2005 coordinate. >
Let go. > Check. You should see
errors.
LOCKING A CELL
REFERENCE INTO A FORMULA $
 Click the top cell with the correct formula.
 In the formula edit line (above the alphabet
row) insert "$" in front of K2 and "$"
in front of L1. After the formula looks like
$K2/$L1 > Enter.
 Now fill down.
LINKING DATA IN OTHER SHEETS
=
 On Sheet 1, click A to select column A >
Ctrl+C to copy it.
 On Sheet 3, click A to select column A >
Ctrl+V to paste it.
 On Sheet 3, cell B1 type "$US"
to head the column > type
= >
Enter
 Go to Sheet 1, Cell B2 >
Enter. Your
Summary
sheet should appear with the correct value in B2

Charting
 Select all states and paint sales for 2000
 Chart Wizard
icon
 Column > Next
 Series Tab > Name > 2000 >
Next
 Chart title > After 2000 type "Paint
Sales by State"
 X Axis > State > Y
Axis > "Litres of Paint" > Next > Finish
 If your chart covers cells with data, hold
the mouse button down and drag your chart away. If there is
not enough room for data and chart/s, cut (Ctrl+X) your chart and
past (Ctrl + V) on Sheet 2. (middle tab, bottom left of screen)
 Format Tabs:
 Right
click sheet 1 tab > Format > Tab
Colour > Select Blue from the colour pallet.
 Right
click Sheet 1 > Rename > Overtype the black
highlighted tab text with the word "Data"
 Right
click sheet 2 tab > Format > Tab
Colour > Select yellow from the colour pallet.
 Right
click Sheet 2 > Rename > Overtype the black
highlighted tab text with the word "Charts"
 Right
click Sheet 3 tab > Format > Tab
Colour > Select red from the colour pallet.
 Right
click Sheet 3 > Rename > Overtype the black
highlighted tab text with the word "Summary"

