Scribd Logo
Upload

Welcome to Scribd!

  • 97 views

    03a Sortfilterpivot

    Uploaded by

    api-200202067
    Sort Data by Column B (Short Name) alphabetically from a to Z Filter data to show only Banks with a P / E ratio Greater Than 10 Click on ICB Sector Dropdown box Uncheck the (Select All) box Select Banks Click on Insert -> Pivot Table Check data range and click OK.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PPTX, PDF, TXT or read online from Scribd

    03a Sortfilterpivot

    Uploaded by

    api-200202067
    97 views54 pages
    Sort Data by Column B (Short Name) alphabetically from a to Z Filter data to show only Banks with a P / E ratio Greater Than 10 Click on ICB Sector Dropdown box Uncheck the (Select All) box Select Banks Click on Insert -> Pivot Table Check data range and click OK.

    Original Title

    03a-sortfilterpivot

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    PPTX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Sort Data by Column B (Short Name) alphabetically from a to Z Filter data to show only Banks with a P / E ratio Greater Than 10 Click on ICB Sector Dropdown box Uncheck the (Select All) box Select Banks Click on Insert -> Pivot Table Check data range and click OK.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    97 views54 pages

    03a Sortfilterpivot

    Uploaded by

    api-200202067
    Sort Data by Column B (Short Name) alphabetically from a to Z Filter data to show only Banks with a P / E ratio Greater Than 10 Click on ICB Sector Dropdown box Uncheck the (Select All) box Select Banks Click on Insert -> Pivot Table Check data range and click OK.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    You are on page 1of 54

    03.

    Excel Sort, Filter and Pivot

    File -> Open -> 03b-datastart.xlsx

    Sort Data

    Press Ctrl + Shift + * on keyboard to select whole dataset

    Whole dataset should now be highlighted

    Click on Data -> Sort

    Sort by Market Cap

    Order Largest to Smallest

    Press OK

    Sorts companies by Market Cap

    Filter Data

    Press Ctrl + Shift + * on keyboard to select whole dataset

    Click on Filter

    Provides Dropdown boxes for each column

    Want to select Banks with a P/E ratio greater than 10

    Click on ICB Sector Dropdown box

    Uncheck the (Select All) box

    Select Banks

    Click OK

    Shows only Banks

    Click on P/E Dropdown box

    Select Number Filters -> Greater Than

    Select P/E is greater than 10

    Click OK

    Shows any Banks with P/E greater than 10

    Challenge
    Sort data by Column B (Short Name) alphabetically from A to Z
    Filter data to show any companies from Germany (DE) with a share price above 30

    Pivot Tables

    Deselect Filter to return to full dataset

    Press Ctrl + Shift + * to select dataset

    Click on Insert -> Pivot Table

    Check data range and click OK

    Drag ICB Sector Name to Row Labels

    Drag Cntry of Domicile to Column Labels

    Drag P/E to Values

    Shows the number of observations for each combination of Industry and Country

    Click on Count of P/E -> Value Field Settings

    Change from Count to Average

    Click OK

    Shows Average P/E of each industry in each country

    Double-Click on Banks + GB

    Opens a new sheet with only Banks in GB

    Rename Sheets

    Right-Click on Sheet 3 -> Rename

    Rename as BanksGB

    Right-Click on Sheet 2 -> Rename

    Rename as PivotTable

    Format Cells Decimal Places

    Select all cells with numbers

    Right-Click on cells and select Format Cells

    Select Number -> 2 Decimal places

    Press OK

    Neatly formatted pivot table

    File -> Save As -> 03c-datacompleted.xlsx

    Challenge
    Calculate the maximum Market Cap for each Sector and Country
    Calculate the minimum Price for each Sector and Country

    You might also like