Scribd Logo
Upload

Welcome to Scribd!

  • 10 views

    Presentation1-Excel Notes

    Uploaded by

    Hema Natchiar R
    The document discusses electronic spreadsheet advanced functions including data consolidation, subtotals, scenarios, goal seek, and solver. Data consolidation allows gathering data from separate worksheets into a master sheet. Subtotals use statistical functions to total arrays of cells. Scenarios, goal seek, and solver are what-if analysis tools, with scenarios saving cell value sets, goal seek discovering values to produce a wanted result, and solver optimizing results under defined rules. Linking consolidated data sources updates the target range if source data changes.

    Copyright:

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

    Presentation1-Excel Notes

    Uploaded by

    Hema Natchiar R
    10 views7 pages
    The document discusses electronic spreadsheet advanced functions including data consolidation, subtotals, scenarios, goal seek, and solver. Data consolidation allows gathering data from separate worksheets into a master sheet. Subtotals use statistical functions to total arrays of cells. Scenarios, goal seek, and solver are what-if analysis tools, with scenarios saving cell value sets, goal seek discovering values to produce a wanted result, and solver optimizing results under defined rules. Linking consolidated data sources updates the target range if source data changes.

    Original Title

    Presentation1-excel notes

    Copyright

    © © All Rights Reserved

    Available Formats

    PPTX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document discusses electronic spreadsheet advanced functions including data consolidation, subtotals, scenarios, goal seek, and solver. Data consolidation allows gathering data from separate worksheets into a master sheet. Subtotals use statistical functions to total arrays of cells. Scenarios, goal seek, and solver are what-if analysis tools, with scenarios saving cell value sets, goal seek discovering values to produce a wanted result, and solver optimizing results under defined rules. Linking consolidated data sources updates the target range if source data changes.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    10 views7 pages

    Presentation1-Excel Notes

    Uploaded by

    Hema Natchiar R
    The document discusses electronic spreadsheet advanced functions including data consolidation, subtotals, scenarios, goal seek, and solver. Data consolidation allows gathering data from separate worksheets into a master sheet. Subtotals use statistical functions to total arrays of cells. Scenarios, goal seek, and solver are what-if analysis tools, with scenarios saving cell value sets, goal seek discovering values to produce a wanted result, and solver optimizing results under defined rules. Linking consolidated data sources updates the target range if source data changes.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    of 7

    Unit II Electronic

    Spreadsheet (Advanced)
    z

    Sit Dolor Amet


    Data Consolidation
    z

     Data Consolidation allows you to gather together your data from


    separate worksheets into a master worksheet.
     In other words, the Data Consolidation function takes data
    from a series of worksheets or workbooks and summaries it
    into a single worksheet that you can update easily.
     The data from the consolidation ranges and target range are
    saved when you save the worksheet.
     If you later open a worksheet in which consolidation has been
    defined, this data will again be available.
     It is available under Data menu > Consolidate option
    z
    SUBTOTAL
     SUBTOTAL is a function listed under the Mathematical category when you
    use the Function Wizard (Insert > Function).
     Because of its usefulness, the function has a graphical interface.
    SUBTOTAL, totals/adds data arranged in an array—that is, a group of
    cells with labels for columns and/or rows.
     Using the Subtotals dialog, you can select arrays, and then choose a
    statistical function to apply to them.
     For efficiency, you can choose up to three groups of arrays to which to
    apply a function.
     When you click OK, Calc adds subtotals and grand totals to the selected
    arrays, using the Result and Result2 cell styles for them.
     It is available under Data menu > Subtotal option
    What-IF Analysis Tools
    z

     Scenarios:

     Scenarios are a tool to test “what-if” questions.

     Each scenario is named and can be edited and formatted


    separately.
     It is essentially a saved set of cell values for your
    calculations.
     You can easily switch between these sets using the
    Navigator or a drop-down list which can be shown beside
    the changing cells.
    What-IF Analysis Tools
    z

     Goal Seek:
     It is used to calculate a result based
    upon existing values.
     Using Goal Seek option under Tools
    menu, we can discover what values
    will produce the result that we want.
    What-IF Analysis Tools
    z

     Solver:
     It is more elaborated form of Goal Seek. The
    difference is that the Solver deals with equations
    with multiple unknown variables.

     It is specifically designed to minimize or maximize


    the result according to a set of rules that we
    define.
    z
    What is the meaning of “Link to Source data”?

     Data Consolidate Tools SUM Select Link to


    source data to insert the formulas.
     If you link the data, any values modified in
    the source range are automatically updated
    in the target range.

    You might also like