Name Box

View Youtube video on how to use the name box

My videos are all created on Camtasia.
TechSmith Camtasia Click Here

  • The name box identifies where your cursor is situated. From the illustration above, the cursor is located at cell A1.
  • You are able to move to specific cells using the name box.
  • For Example, select the A1, key in Z99 and press enter.
  • Your cursor is moved to that exact location on your worksheet.
  • Select a range of cells. Click to highlight the value in the name box. Key in Summer_Sales and press enter. There can be no spaces and no punctuation in the name.
  • You have named this range of cells.
  • Move to another location on your worksheet
  • To access the named range of cells click the list arrow to the right of the name box.
  • Select Summer_Sales from the drop-down list and press enter. The Summer_Sales will be highlighted again.



Formula Bar

  • The Formula Bar is located to the right of the Name Box, below the ribbon on each page.
  • The Formula bar shows what data is contained in the selected cell.
  • The Formula Bar is where you are able to create formulas in Excel.
  • Below is a list of operators used in creating formulas in Excel.

Formula Operators

Plus

+

Minus

-

Multiply

*

Divide

/

  • For example, we will create a formula to add two numbers.
  • Select the cell where you would like the result to appear.
  • Every formula in excel begins with an equal sign.
  • Key in an equal sign and select a value from your worksheet, key in a plus sign and select another value from your worksheet.
  • To calculate the formula press enter.



Create a formula using Insert Function

My videos are all created on Camtasia.
TechSmith Camtasia Click Here

Insert Functions

  • Insert Functions
  • The insert Function dialogue box is also available from the formula bar.
  • The insert function dialogue box will guide you through more advanced steps and functions.
  • Select the cell where you would like to have your result appear.
  • Click the Fx
  • The Insert Function dialogue box will be presented.
  • If, for example, we are going to use the average function we would select the cell where we would like to have the average displayed normally it would be close to the numbers that we want to average.
  • Click the

    Fx

    on the formula bar and select average from the dialogue box and click O.K.
  • Excel will suggest which numbers you want to average. If what they have suggested is correct click O.K. to calculate.
  • If Excel’s suggestion is incorrect delete the fields that Excel has in the Number1 field and click the button at the end of the number field to go back to the worksheet and select the correct cells.



    Fill Handle

    View Youtube Video on how to use the Fill Handle

    My videos are all created on Camtasia.
    TechSmith Camtasia Click Here

    • There is a feature to aid in data entry.
    • When you click on a cell there is a small box at the bottom right hand side of the cell known as the fill handle.
    • When your mouse pointer is placed over the fill handle the pointer becomes a thin Plus sign +
    • For example, if you key in Monday and place the mouse pointer over the little,(fill handle), until the pointer becomes a plus sign.
    • Click and hold the click and drag the mouse to the right
    • The days will be auto populated
    • This function works for days of the week or months, dragging both horizontlly or vertcally


    VMware AU - 15% off Education Sale December 2016


    Absolute and Relative Cell Reference

    View Youtube video on how to use the relative and absolute cell reference

    • Creating a formula using values from two colums of data, you are able to drag the formula down.
    • This procedure only works when both values are located relative to each other's position; Relative Cell Reference.
    • To create a calculation with one value multiplied by a column of numbers; Absolute Cell Reference.
    • In our example we will multiply the salary by a tax of 23%
    • After selecting the number that will remain the same in the formula, click the F4 button on your keyboard. This will put $ in front of the column and the row number of the cell reference.
    • The $ will inform Excel that when you drag the formula down, this value will remain the same.



    Linking Sheets

    View Youtube video for instructions how to link sheets

    My videos are all created on Camtasia.
    TechSmith Camtasia Click Here

    • Linking the values from one sheet to another can be achieved by entering the ‘=’ in the destination cell and then selecting the cell from the worksheet desired and pressing enter.
    • For example, if you had four worksheets named total, January, February and March.
    • You are able to calculate the totals for each of the months on the sheets with the months name on them.
    • Then to display the totals on the total sheet you would enter an equal sign = and select the cell from the worksheet required.
    • In the two images below, we have the formulas displayed and then the values from these formulas.
    • Note, in the formula, the sheet name is followed by ‘!’. So that the value in B1 is from the sheet named January and the Cell E6.



    How to Add letters to Consecutive Numbers in a List

    View instructions how to add letters to numbers that appear in a sequence.

    • With the list appearing in column 'C' and row one blank enter the following formula in D2
    • =if(C1=C2,(c2&(char(code(right(D1,1))+1))),(c2&"a"))
    • the following will be dispayed




    How to create a table?

    Create a table

    • Select existing information on your sheet
    • From the insert tab on the ribbon, select Table
    • the Create table dialogue box is displayed, to confirm selection
    • The data will be turned into a table.



    Related Videos

    Insert a formula
    Sorting by multiple columns
    Importing Data from a text file
  • Sign-up for our newsletter and receive a free calendar pop-up for entering dates

    * indicates required