Excel Formula Training Exercise

Computer Training Perth

ExerciseUse Excel formulas for Scenarios

Data Entry and Formatting

Formulas in Cells

Charting from data in cells

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
1. In cell coordinates litres x 2000 type the following: =sum(
2. In cell coordinates WA x 2000 hold your mouse button down and drag to NT x 2000
3. Enter.
4. Check the resulting sum is roughly right.
COPY FORMULA DOWN A COLUMN (Fill Down)
1. In cell coordinates litres x 2000 > Hold mouse button down and drag to coordinates litres x 2005
2. Alt > E > I > D This should copy the formula down.
MULTIPLICATION (Product *)
1. Atop the "Sales" column > =I2*J2 (litres paint x $/litre) > Enter > Check against your mental arithmetic.
2. Fill formula down > Check
DIVISION ( / )
1. In cell L1, enter how many dollars and cents one US dollar will buy. eg 1.3
2. Formula in the cell below > =K2/L1 (divides 2000 sales by exchange rate)
3. 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 $
1. Click the top cell with the correct formula.
2. 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.
3. Now fill down.
LINKING DATA IN OTHER SHEETS =
1. On Sheet 1, click A to select column A > Ctrl+C to copy it.
2. On Sheet 3, click A to select column A > Ctrl+V to paste it.
3. On Sheet 3, cell B1 type "$US" to head the column > type = > Enter
4. Go to Sheet 1, Cell B2 > Enter. Your Summary sheet should appear with the correct value in B2

Charting
1. Select all states and paint sales for 2000
2. Chart Wizard icon
  1. Column > Next
  2. Series Tab > Name > 2000 > Next
  3. Chart title > After 2000 type "Paint Sales by State"
  4. X Axis > State > Y Axis > "Litres of Paint" > Next > Finish
3. 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)
4. Format Tabs:
  1. Right click sheet 1 tab > Format > Tab Colour > Select Blue from the colour pallet.
  2. Right click Sheet 1 > Rename > Overtype the black highlighted tab text with the word "Data"
  3. Right click sheet 2 tab > Format > Tab Colour > Select yellow from the colour pallet.
  4. Right click Sheet 2 > Rename > Overtype the black highlighted tab text with the word "Charts"
  5. Right click Sheet 3 tab > Format > Tab Colour > Select red from the colour pallet.
  6. Right click Sheet 3 > Rename > Overtype the black highlighted tab text with the word "Summary"