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
Common Excel Formulas
||=sum(first cell coordinate:last
- 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
- 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.
- 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
- Formula in the cell below >
(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
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
- Go to Sheet 1, Cell B2 >
sheet should appear with the correct value in B2
- Select all states and paint sales for 2000
- Chart Wizard
- Column > Next
- Series Tab > Name > 2000 >
- 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:
click sheet 1 tab > Format > Tab
Colour > Select Blue from the colour pallet.
click Sheet 1 > Rename > Overtype the black
highlighted tab text with the word "Data"
click sheet 2 tab > Format > Tab
Colour > Select yellow from the colour pallet.
click Sheet 2 > Rename > Overtype the black
highlighted tab text with the word "Charts"
click Sheet 3 tab > Format > Tab
Colour > Select red from the colour pallet.
click Sheet 3 > Rename > Overtype the black
highlighted tab text with the word "Summary"