# Excel Formula Training Exercise

Computer Training Perth

## ExerciseUse Excel formulas for Scenarios

3. ### 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.

Common Excel Formulas

 Addition =sum(first cell coordinate:last cell coordinate) Multiplication * Division / Equals =

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

1. 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"
 Enrolment INTERNET TRAINING:htmlUploadingWeb MarketingBeginners NetXMl Java IT ArchitectureMultiMediaHow to Start an Ezine BUSINESS SOFTWARE TRAININGExcel TrainingMS Word for word processingWindows and file management MYOBDatabaseOutLook PowerPointPhotoshopProfessional DevelopmentAdobe Training Index Perth Links School Holidays PrincipalComputer Training / Internet Training Perth194 The EsplanadeScarborough Western Australia Australia 6922 EMAIL Perth Computer Training Instructors

Next:  Open Book Microsoft Office Test.  You'll be able to use these Excel training exercise notes and F1 for help.

Excel Training Home

Error reports to excel@computertrainingperth.com