Scribd Logo
Upload

Welcome to Scribd!

  • 34 views

    10th Praticals Excel

    Uploaded by

    EA0792 Y Venkatesh
    This document discusses how to consolidate data, create subtotals, use what-if scenarios, and perform goal seeking in Microsoft Excel. It provides step-by-step instructions on how to consolidate data from multiple worksheets, create subtotals by grouping data, set up and modify scenario managers to analyze alternative values, and use goal seek to determine an input value that achieves a desired output target. Screenshots are included to illustrate each process.

    Copyright:

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

    10th Praticals Excel

    Uploaded by

    EA0792 Y Venkatesh
    34 views10 pages
    This document discusses how to consolidate data, create subtotals, use what-if scenarios, and perform goal seeking in Microsoft Excel. It provides step-by-step instructions on how to consolidate data from multiple worksheets, create subtotals by grouping data, set up and modify scenario managers to analyze alternative values, and use goal seek to determine an input value that achieves a desired output target. Screenshots are included to illustrate each process.

    Original Title

    10th_praticals_Excel

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document discusses how to consolidate data, create subtotals, use what-if scenarios, and perform goal seeking in Microsoft Excel. It provides step-by-step instructions on how to consolidate data from multiple worksheets, create subtotals by grouping data, set up and modify scenario managers to analyze alternative values, and use goal seek to determine an input value that achieves a desired output target. Screenshots are included to illustrate each process.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    34 views10 pages

    10th Praticals Excel

    Uploaded by

    EA0792 Y Venkatesh
    This document discusses how to consolidate data, create subtotals, use what-if scenarios, and perform goal seeking in Microsoft Excel. It provides step-by-step instructions on how to consolidate data from multiple worksheets, create subtotals by grouping data, set up and modify scenario managers to analyze alternative values, and use goal seek to determine an input value that achieves a desired output target. Screenshots are included to illustrate each process.

    Copyright:

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

    Ms Excel

    Exp.No:
    Title: Use Consolidating Data
    Aim: To consolidating data help users to automate data and save time where data used
    frequently in different sheet.

    Algorithm

    PT 1 Marks

    Screenshot 1 is having PT 1 marks and Screenshot 2 is having PT 2 marks. Now in sheet 3, I need to use
    formulas on data to compute results. This process is known as consolidating data.

    Steps to Consolidating Data in MS Excel


     Open the workbook in which you have prepared data.
     Now click and place the cursor in the cell in the new worksheet where you want to display
    consolidated data. If the worksheet is not available then insert a new worksheet.
     Click on Data ⇢ Consolidate.

     The consolidate dialog box will appear.

    Data Consolidate MS Excel 2013


    dialog box

      As you can see in the above screenshot consolidate dialog box contains 5 options
    highlighted.

    o Function: In this option, you can select your desired function to display consolidated
    results.
    o Reference: This option allows you to select the reference from another sheet having
    data.
    o Click on the circled button to select a reference. Click on the Add button to use the
    reference. Repeat the same step to add all the references.
    o All references: Display the added references from the workbook.
    o Use labels in: This option allows us to use the row headers and column headers of
    worksheets. 
    o Create links to source data: When you select this option data will automatically
    update the values when any change happens to referenced cells. Finally, click on
    OK button.
     Observe the following screenshot, I have added references for the above-mentioned
    sheets.

    Data consolidate dialog with referenced data


    Exp :
    Title: Creating Subtotals 
    Aim: to Creating Subtotals 
    Algorithm:

    These steps to create subtotals:


    Step 1: Click on Data → Subtotal from the outline group. 

    data –
    subtotals in ms excel 2013
     
    The Subtotals dialog box will appear as displayed in the following screenshot.
     
    Subtotal dialogbox in ms excel
     
     
    Step 2: Select the column header on which you want to apply subtotal. 
    Step 3: The second option is to use a function where you can select a function to display the
    results.
    Step 4: After selecting the function select the values on which grouping is required. 
    Step 5: You can select the option as per your need from different options from the following:

    1. Replace current subtotals: This option is used to replace the values of subtotals.


    2. Page break between groups: It will start a new page from the new group.
    3. The summary below data: It will display the summary below the group.
    4. Remove all: To previous subtotals 

    Step 6: Click on the ok button.

    Observe the result is given in the following screenshot where I have applied subtotal on the class
    group column.

    Data Subtotals results


    Exp :
    Title: Using “What If” scenarios in MS Excel
    Aim: The “What If” scenario feature allows us to create different target-
    based “What If” analysis. Suppose we are playing a cricket tournament
    where we need to analyze our team performance on the net run rate we
    can use “What If” scenarios. 

    Algorithm:

    Step 1: Select data and click on Data → what – if – Analysis → Scenario Manager. Observe the
    following screenshot:
     

    What-if-analysis
    Step 2: Click on the scenario manager.  The Scenario Manager Dialog box appears as displayed
    in the following screenshot.
     
    -

    Scenario Manager dialog box in Excel


    This dialog box has the following options:

    1.
    1. Scenario Details Preview: It shows the details of the scenarios.
    2. Add: You can add a new scenario by clicking this option.
    3. Delete: You can delete an existing scenario that is unwanted.
    4. Edit: You can edit an existing scenario.
    5. Merge: It is useful, If more than one scenario needs to be added 
    6. Summary: It shows a summary of the scenario.
    7. Changing Cells: Select the cells in which values are going to be changed.
    8. Comment: If any additional text is required then you can insert it.
    9. Show: Display the information of the selected scenario.
    10. Close: To close the scenario manager.

    Step 3: Click on the Add button. Add scenario dialog box will appear as displayed in the below-
    given screen shot.
    Add scenario dialog box
    Type the scenario name as I have scenario1, you can change cells by selecting changing
    cells options. Type your comments if you want to type in the comment box. Click on the OK
    button. Scenario values dialog box appears as displayed in the following dialog box. 
     

    Scenario Values Dialog box


    Step 4: Now enter the target values in the respective cells. My target which I want to use for this
    scenario is 120 runs in 10 overs and want to concede 90 runs in 10 overs to see the net run rate
    variations. So I have changed these values respectively. 
    Step 5: Click on the Add button. Repeat the same steps for more scenarios. 
    Exp :
    Title: goal seek in MS Excel
    Aim:  to apply goal seek in MS Excel
    Algorithm:

    goal seek helps to do the same.

    1.
    1. Select the cell where the formula is written. For my example, I have placed the
    cursor in B4.
    2. Click on Data → What-if Analysis → Goal Seek option from the forecast group.

    Data – What if Analysis – Goal Seek

    3. A Goal Seek dialog box will appear as displayed in the following screenshot:
    GoalSeek in MS Excel 2019

    4. Type the desired value i.e. 500 in To Value box and select the cell address for by
    changing cell option as displayed in the following screenshot.

    GoalSeek input

    5. Click on OK button the values will be displayed in your worksheet as displayed in the
    following screenshot.
    6. Click on OK button to accept the result, click cancel to return with original data.

    You might also like