Excel Formula Training Exercise

Computer Training Perth

ExerciseUse Excel formulas for Scenarios

  1. Data Entry and Formatting


  2. Formulas in Cells


  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.

  1. 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

  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:

html

Photoshop Graphics

Uploading

Web Marketing

Beginners Net

XMl Java IT Architecture

MultiMedia

How to Start an Ezine

 

BUSINESS SOFTWARE TRAINING

Excel Training

MS Word for word processing

Windows and file management

Quicken and QuickBooks

Video Training

 MYOB

Database

OutLook

PowerPoint

Photoshop

Digital Video Production

Professional Development

Adobe Training Index

Adobe Acrobat PDF Writer training PerthAcrobat PDF Converter

Perth Computer Training Certificate

 


Perth Links

Perth Map

Public Holidays

Perth Training Forum

Business Coaching

Training Enrolment

Training Venue

4 Web Marketing

Email
 

School Holidays


Principal
Computer Training / Internet Training Perth
194 The Esplanade
Scarborough
Western Australia Australia 6922

EMAIL Perth Computer Training Instructors



Top Computer Training Perth

 

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