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"
|
|