Scribd Logo
Upload

Welcome to Scribd!

  • 66 views

    Q3 - M4 1 Excel

    Uploaded by

    Anne Marie Ampong
    The document provides information about advanced techniques in Microsoft Excel. It defines key Excel concepts like electronic spreadsheets, components of a spreadsheet like cells and tabs, and advanced spreadsheet skills like sorting, filtering, and pivot tables. It also covers formulas and functions in Excel, explaining that formulas always start with an equal sign and can perform complex calculations using arithmetic operators and functions. Functions are predefined formulas that perform calculations on specific cell values and arguments. Common functions discussed include SUM, AVERAGE, and IF.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Q3 - M4 1 Excel

    Uploaded by

    Anne Marie Ampong
    66 views14 pages
    The document provides information about advanced techniques in Microsoft Excel. It defines key Excel concepts like electronic spreadsheets, components of a spreadsheet like cells and tabs, and advanced spreadsheet skills like sorting, filtering, and pivot tables. It also covers formulas and functions in Excel, explaining that formulas always start with an equal sign and can perform complex calculations using arithmetic operators and functions. Functions are predefined formulas that perform calculations on specific cell values and arguments. Common functions discussed include SUM, AVERAGE, and IF.

    Original Title

    Q3_M4-1-Excel

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document provides information about advanced techniques in Microsoft Excel. It defines key Excel concepts like electronic spreadsheets, components of a spreadsheet like cells and tabs, and advanced spreadsheet skills like sorting, filtering, and pivot tables. It also covers formulas and functions in Excel, explaining that formulas always start with an equal sign and can perform complex calculations using arithmetic operators and functions. Functions are predefined formulas that perform calculations on specific cell values and arguments. Common functions discussed include SUM, AVERAGE, and IF.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    66 views14 pages

    Q3 - M4 1 Excel

    Uploaded by

    Anne Marie Ampong
    The document provides information about advanced techniques in Microsoft Excel. It defines key Excel concepts like electronic spreadsheets, components of a spreadsheet like cells and tabs, and advanced spreadsheet skills like sorting, filtering, and pivot tables. It also covers formulas and functions in Excel, explaining that formulas always start with an equal sign and can perform complex calculations using arithmetic operators and functions. Functions are predefined formulas that perform calculations on specific cell values and arguments. Common functions discussed include SUM, AVERAGE, and IF.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 14

    Advanced Techniques in

    Microsoft Excel
    Electronic Spreadsheet
     Refers to a collection of text and numbers laid out
    in a rectangular grid.
     It is an application program commonly used for
    budgeting, inventory management, decision making,
    forecasting and other finance-related tasks.
     It allows you to store, organize, and analyze
    information.
     spreadsheet - a table in which you can enter and
    manipulate data
    Components of a Spreadsheet
    Components of a Spreadsheet
    • Name box/Cell indicator- a tool that shows the active cell address
    • File tab- the box located at the left side of the formula bar which addresses the
    selected cell or group of cells in the spreadsheet
    • Active cell- the selected cell in which data is entered when you begin typing.
    Data can only be entered into the active cell.
    • Insert function
    • Formula bar- a special toolbar at the top of the Excel worksheet window,
    labeled with function symbol (fx)
    • Columns and Rows
    • Gridlines
    • Sheet tab- the tab that is used for displaying the worksheet that is currently
    been edited by the user
    • range - the specification for a series of cells
    Advanced Spreadsheet Skills
    Needed in Life
    1.Data Entry, Sorting, 5.Sort by Column/Rows
    and Filtering 6.Pivot Tables
    2.Applying Data 7.Macros and Scripts
    Validation
    8.Combine Multiple
    3.Protecting Sheets and Functions to
    Locking Cells
    Create Formulas
    4.Custom Filtering
    9.Perform Calculations
    Advanced Spreadsheet Skills
    Needed in Life
    Advance and Complex Calculations in Excel

     Formula – is an equation that performs operation on


    worksheet data. A formula in Microsoft Excel always begins
    with an equal sign (=).1024 characters
     Complex formula- is the combination of more than two simple
    formulas.
    -There are four basic computations that can be used in excel.
    When solving complex problems, Excel follows the PEMDAS rule.
    PEMDAS is an acronym for the words: parenthesis, exponents,
    multiplication, addition, and subtraction.
    Arithmetic Operations
    ARITHMETIC OPERATORS / OPERATION EXAMPLE
    SYMBOLS
    + (Plus Sign) Addition =A1+A2
    - (Minus Sign) Subtraction =A1-A2
    * (Asterisk) Multiplication =A1*A2
    / (Forward Slash) Division =A1/A2
    ^ (Exponentiation) Raises a number to =3^2
    a power

    - (Negation) Negative -11


    % (Percent Sign) Percentage =90%
    Terms related to MS Excel
    Relative Reference
     All cell references are called relative references.
     When copied across multiple cells, they can base on the relative position of
    rows and columns.
     This reference changes when the formula is copied to any other cell or any
    other worksheet. Relative cell references are used whenever calculations
    need to be repeated.
    Absolute Reference
     You can use an absolute reference to keep a row and/or column constant.
     An absolute reference in Excel is a reference that cannot be changed when
    copied, so you won't see changes in rows or columns when you copy them.
    Absolute references are used when you want to fix a cell location. These
    cell references are preceded by a dollar sign.
    Absolute Reference (example)

    $A$2 The column and the row do


    not change when copied.
    A$2 The row does not change
    when copied.
    $A2 The column does not change
    when copied.
    Terms related to MS Excel
    Functions
    ▪ These are predefined formula that performs calculations
    using specific values in a particular order
    ▪ The parts of a function are :

    =SUM(A1:A20))

    Equal Sign Function Argument


    Name
    Function Library

    SUM formula in Excel IF formula in Excel


    The SUM function can add all numbers in a The IF function can perform a logical test and
    range of cells. return one value for the TRUE result, and another
    for FALSE result. More than one condition can be
    Syntax: =SUM(number1,number2,…) tested by nesting IF functions.
    Or =SUM(start_cell:end_cell) Syntax:
    =IF(logical_test,[value_if_true],[value_if_false])

    AVERAGE formula in Excel - logical_test is a value or logical expression


    that can be evaluated as TRUE or FALSE,
    The AVERAGE function can return the average
    (arithmetic mean) of its arguments, which can be - value_if_true (optional) is the value to return
    numbers or names, arrays, or references that when logical_test evaluates to TRUE.
    contain numbers.
    - value_if_false (optional) is the value to return
    Syntax: =AVERAGE(number1,number2,...) when logical_test evaluates to FALSE

    or =AVERAGE(start_cell:end_cell)
    1) What feature of MS Excel that can contain up to 1024
    characters and must always start with an equal sign (=).
    A. Absolute Referencing B. Formula
    C. Functions D. Relative Referencing
    2) What function that adds a range cells.
    A. ADD B. PLUS
    C. TOTAL D. SUM
    3) What do you call the cells that do not change?
    A. Math Operators B. Relative Referencing
    C. Absolute Referencing D. Formula Operators
    4) If cell B18 is 86 and you want to know if this is lesser
    than or equal to 75, what would be the correct
    function so that A10’s result will be written as FALSE?
    A.=IF(B18>=75,”TRUE”,FALSE”)
    B.=IF(B18<=86,”TRUE”,”FALSE”)
    C.=IF(B18<75,”TRUE”,”FALSE”)
    D.=IF(B18<=75,"TRUE","FALSE")
    5) If the cell B19 is 76, what will this IF function of
    =IF(B19>70,”true”,”false”) give you as a result?
    A. true B. false C. 70 D. none of these

    You might also like