English - Module 11 Project 1a | Amara (2024)

  • 0:03 - 0:07

    - [Instructor] This is Excel,
    Module 11 SAM Project 1a.

  • 0:08 - 0:10

    I have already opened the data file,

  • 0:10 - 0:11

    and the instruction file,

  • 0:11 - 0:14

    and I have those ready to
    go, so we will go right

  • 0:14 - 0:16

    into the project steps.

  • 0:17 - 0:19

    Camille Darbonne is the
    director of the Office

  • 0:19 - 0:23

    of Capital Projects for the
    Hempstead University Foundation.

  • 0:23 - 0:26

    She uses an Excel
    workbook to track building

  • 0:26 - 0:29

    projects on three Hempstead
    University campuses

  • 0:29 - 0:31

    in the state of New York.

  • 0:31 - 0:35

    She asks for your help in
    summarizing project data

  • 0:35 - 0:37

    as she prepares for presentation

  • 0:37 - 0:38

    to the Foundation's board.

  • 0:38 - 0:41

    To do so, you will use database functions

  • 0:41 - 0:44

    and advanced PivotTable features.

  • 0:44 - 0:46

    We will go to the Capital
    Projects worksheet,

  • 0:46 - 0:49

    which contains a table named
    Projects, listing details

  • 0:49 - 0:52

    about the university's building projects.

  • 0:52 - 0:55

    In the range J2 through M7

  • 0:55 - 1:00

    Camille wants to summarize
    project information.

  • 1:00 - 1:02

    We will start by calculating
    the number of each Project

  • 1:02 - 1:04

    Type as follows.

  • 1:04 - 1:09

    In cell K3, we'll enter a formula
    using the COUNTIF function

  • 1:09 - 1:11

    that counts the number
    of expansion projects,

  • 1:11 - 1:15

    checking that the column of
    Project Types in the Projects

  • 1:15 - 1:19

    table is equal to the value in cell J3.

  • 1:20 - 1:22

    Okay, so first of all let's just go ahead

  • 1:22 - 1:23

    and save our file.

  • 1:26 - 1:29

    Making sure that mine is on the desktop.

  • 1:31 - 1:34

    And then I just want to
    change the one at the end

  • 1:34 - 1:39

    of the filename to a two
    for grading submission.

  • 1:41 - 1:44

    I'll then go the Capital Projects sheet,

  • 1:44 - 1:48

    specifically cell K3, and
    let's go ahead and do that

  • 1:48 - 1:49

    first COUNTIF function.

  • 1:52 - 1:55

    I will use the function
    argument box to get started.

  • 1:55 - 1:57

    So let's click on the Formulas tab,

  • 2:00 - 2:04

    and in the function library,
    More Functions, Statistical,

  • 2:04 - 2:06

    and locate the COUNTIF function.

  • 2:07 - 2:10

    The range mentions that they
    would like the project type,

  • 2:12 - 2:16

    data, so I'm just selecting cell C3.

  • 2:16 - 2:19

    I'll press Control +
    Shift and the down-arrow,

  • 2:19 - 2:22

    which selects to the bottom of the data.

  • 2:22 - 2:24

    Then I can go ahead and
    click in the criteria box,

  • 2:24 - 2:29

    and specifically select
    cell J3, which will give us

  • 2:29 - 2:32

    the expansion project type.

  • 2:32 - 2:37

    Go ahead and click OK, and
    then you can actually autofill

  • 2:38 - 2:40

    to the other project types.

  • 2:43 - 2:47

    Task two states that in column
    L Camille wants to calculate

  • 2:47 - 2:50

    the total cost of each project type.

  • 2:50 - 2:53

    Determine the total costs as follows.

  • 2:53 - 2:57

    In cell L3 we'll enter a
    formula using the SUMIF function

  • 2:57 - 3:00

    that totals the cost
    for expansion projects,

  • 3:00 - 3:02

    checking that the column of project types

  • 3:02 - 3:07

    in the Projects table is
    equal to the value in cell J3,

  • 3:07 - 3:11

    and that the formula
    totals all the final costs.

  • 3:11 - 3:15

    Then we'll fill the range
    with the formula in cell L3.

  • 3:16 - 3:19

    So if we activate cell L3,
    once again I'm going to use

  • 3:19 - 3:21

    the function argument box.

  • 3:21 - 3:24

    The SUMIF function is in
    the Math & Trig category.

  • 3:29 - 3:31

    So I'll activate SUMIF.

  • 3:31 - 3:34

    The first range deals
    with the project type,

  • 3:34 - 3:37

    so I'm gonna click on cell C3.

  • 3:37 - 3:39

    I'll press Control +
    Shift with my left hand

  • 3:39 - 3:43

    and the down-arrow to select
    all of the project types.

  • 3:43 - 3:47

    The criteria is dealing
    with expansion in this case,

  • 3:47 - 3:49

    so I'll click on cell J3.

  • 3:51 - 3:54

    And then for the Sum_range
    it talked in the instructions

  • 3:54 - 3:55

    about the final cost.

  • 3:55 - 3:58

    So I'll select cell H3.

  • 3:58 - 4:00

    I'll use Control +
    Shift and the down-arrow

  • 4:00 - 4:02

    to select all of the data in that column,

  • 4:02 - 4:04

    and then I'll click OK.

  • 4:05 - 4:09

    Then I can use my autofill
    tool to fill all project types.

  • 4:09 - 4:12

    This time I'm gonna use the
    autofill tool in the lower

  • 4:12 - 4:13

    right hand corner.

  • 4:13 - 4:16

    And if I double-click with a left-click

  • 4:16 - 4:18

    it will automatically fill that for me.

  • 4:22 - 4:26

    Task three states that in
    column M Camille wants us

  • 4:26 - 4:30

    to calculate the average
    cost of each project type.

  • 4:30 - 4:32

    We will determine the
    average cost as follows.

  • 4:32 - 4:36

    In cell M3 we'll enter a
    formula using the AVERAGEIF

  • 4:36 - 4:39

    function that averages the
    cost for expansion projects,

  • 4:39 - 4:41

    checking that the column of project types

  • 4:41 - 4:45

    in the Projects table is
    equal to the value in cell J3,

  • 4:45 - 4:50

    and that the formula averages
    all of the final costs.

  • 4:50 - 4:52

    So in column M, we'll go ahead.

  • 4:52 - 4:55

    This time I'm going to do the one liner

  • 4:55 - 4:56

    using the AVERAGEIF.

  • 5:00 - 5:02

    So let me scroll over just a little bit

  • 5:02 - 5:04

    so you can see what I'm doing.

  • 5:04 - 5:07

    I'll type in equals, I'll
    start to type average,

  • 5:07 - 5:10

    I'll find AVERAGEIF on the dropdown list

  • 5:10 - 5:11

    and double-click that.

  • 5:11 - 5:14

    The range we're looking
    for is the project type.

  • 5:17 - 5:19

    In cell C3 Control + Shift down.

  • 5:20 - 5:23

    I could type a comma at that point then.

  • 5:23 - 5:25

    It's asking me for the criteria.

  • 5:25 - 5:29

    Once again the criteria
    can be found in cell J3

  • for expansion type, comma, and
    the last thing you want to do

  • 5:33 - 5:36

    is average the final costs.

  • 5:36 - 5:40

    So the average range, which
    will always contain values

  • 5:40 - 5:42

    if that helps you keep that straight.

  • 5:42 - 5:47

    Click on cell H3, press Control
    + Shift and the down-arrow,

  • 5:47 - 5:50

    and then you can supply
    the closing parentheses

  • 5:50 - 5:52

    or you can simply press Enter,

  • 5:54 - 5:57

    and then in that particular
    column for average cost,

  • 5:57 - 6:00

    you get the average cost for expansion

  • 6:00 - 6:04

    and can autofill to the
    other project types.

  • 6:07 - 6:11

    Task four states that Camille
    needs to identify the number

  • 6:11 - 6:14

    of projects that have a
    final cost of more than

  • 6:14 - 6:18

    $100,000 and those that
    were completed in 2019.

  • 6:19 - 6:23

    So we'll go to cell K11, we'll
    create a formula using the

  • 6:23 - 6:26

    DCOUNT function to count
    the number of projects

  • 6:26 - 6:29

    with final costs of more
    than $100,000 using the data

  • 6:29 - 6:32

    in the entire Projects table,

  • 6:33 - 6:36

    and counting the values in
    the column of the final costs

  • 6:36 - 6:40

    that are equal to the values
    in the range J9 to J10.

  • 6:41 - 6:44

    So we will be using the DECOUNT function.

  • 6:46 - 6:48

    I think they want us to be in cell K11.

  • 6:50 - 6:53

    All right let's go ahead and
    use the database function

  • 6:53 - 6:55

    from the ribbon first.

  • 6:56 - 6:59

    If you don't know where to
    find the DECOUNT function

  • 6:59 - 7:00

    let's choose on the ribbon

  • 7:00 - 7:03

    in the function library, Insert Function,

  • 7:05 - 7:08

    and then we can type in
    Search for a function,

  • 7:08 - 7:13

    decount and Go, and
    choose it from the list.

  • 7:15 - 7:20

    All right, the database
    instructions said that it wanted

  • 7:20 - 7:23

    to have the entire table
    in the database box

  • 7:23 - 7:26

    so I'm going to include row two.

  • 7:26 - 7:29

    I'll select row two on the table,

  • 7:29 - 7:31

    then I'll press Control +
    Shift and the down-arrow

  • 7:31 - 7:34

    and that will select the entire table.

  • 7:34 - 7:38

    The field that we're
    specifically looking at,

  • 7:41 - 7:43

    then we'll be counting
    the values in the column

  • 7:43 - 7:44

    of Final Cost.

  • 7:45 - 7:49

    So in the Field box you're
    going to type in quotes,

  • 7:49 - 7:52

    Final Cost, and then the criteria itself

  • 7:55 - 7:59

    can be found in J9 and J10.

  • 7:59 - 8:02

    Final cost needs to be
    greater than $100,000

  • 8:02 - 8:04

    and click OK.

  • 8:05 - 8:09

    And they find 16 that meet that criteria.

  • 8:12 - 8:16

    Task four B wants us to create a formula

  • 8:16 - 8:19

    using the DECOUNTA function in cell K15

  • 8:19 - 8:22

    to count the number of
    projects completed in 2019

  • 8:22 - 8:26

    using the data in the entire
    Projects table, once again,

  • 8:26 - 8:28

    and counting the values in the column

  • 8:28 - 8:32

    of completion dates, completed,
    that are equal to the values

  • 8:32 - 8:35

    in the range J13 to J14.

  • 8:36 - 8:40

    So in cell K15 then we'll go
    ahead and use the DCOUNTA.

  • 8:41 - 8:45

    I'm going to use a one liner function

  • 8:52 - 8:53

    using DCOUNTA.

  • 8:55 - 8:58

    Once again it wants the
    location of the database.

  • 8:58 - 9:00

    So make sure that you're
    in column A when you make

  • 9:00 - 9:04

    the selection so that you
    get the entire database.

  • 9:04 - 9:06

    Control + Shift down to select that.

  • 9:07 - 9:10

    Type in a comma, which moves
    you now to the field area,

  • 9:10 - 9:13

    and in that particular
    area you want to type

  • 9:13 - 9:17

    in Completed in quotes.

  • 9:19 - 9:21

    Type a comma to move to the next box,

  • 9:23 - 9:27

    and the criteria can be
    found in cell J13 and J14.

  • 9:30 - 9:33

    You can provide the closing
    parentheses or press enter

  • 9:33 - 9:37

    and you'll find nine projects
    that meet that criteria.

  • 9:40 - 9:42

    Now the reason that we use DECOUNTA here

  • 9:42 - 9:45

    rather than DECOUNT is
    because the criteria

  • 9:45 - 9:47

    had no value in it.

  • 9:47 - 9:50

    It just had completed and a
    year 2019, so you use DECOUNTA

  • 9:51 - 9:55

    as opposed to DECOUNT up
    here because of the value

  • 9:55 - 9:57

    of greater than 100,000.

  • 10:00 - 10:02

    Let's take a look now at task five.

  • 10:02 - 10:05

    Camille also needs to
    calculate the total cost

  • 10:05 - 10:07

    of the building projects
    on the Newburgh campus

  • 10:07 - 10:09

    and the average cost of the projects

  • 10:09 - 10:11

    on the Hempstead campus.

  • 10:11 - 10:13

    We'll create formulas that provide

  • 10:13 - 10:15

    this information as follows.

  • 10:15 - 10:19

    In cell K19 we'll create a
    formula using the DSUM function

  • 10:19 - 10:23

    to calculate the total cost of
    the Newburgh campus projects

  • 10:23 - 10:26

    using the data in the
    entire projects table,

  • 10:26 - 10:29

    once again, and totalling
    the values of the column

  • 10:29 - 10:34

    of Final Cost, in quotes,
    that are equal to the values

  • 10:34 - 10:37

    in the range J17 to J18.

  • 10:38 - 10:42

    So in K19 we will begin that process.

  • 10:44 - 10:49

    So in cell K19 then I'm going
    to go up to the formula bar

  • 10:49 - 10:51

    and click on the Insert Function button

  • 10:52 - 10:55

    and in this case I'm
    going to look for DSUM,

  • 10:55 - 10:58

    which is a database SUM function.

  • 11:00 - 11:01

    We'll choose it.

  • 11:04 - 11:09

    Right, and then the
    database wants us to include

  • 11:09 - 11:10

    the entire table.

  • 11:15 - 11:19

    The field, in this case,
    is going to be "Final Cost"

  • 11:25 - 11:30

    and the criteria will
    include cell J17 to J18,

  • 11:32 - 11:36

    campuses in Newburgh, and click OK.

  • 11:38 - 11:41

    Let's follow that up then
    with a DAVERAGE function

  • 11:41 - 11:43

    to calculate the average cost

  • 11:43 - 11:46

    of the Hempstead campus
    projects using the data

  • 11:46 - 11:49

    in the entire Projects table
    and averaging the value

  • 11:49 - 11:52

    in the column of Final Cost
    that are equal to the values

  • 11:52 - 11:55

    in the range J21 to J22.

  • 11:57 - 12:02

    So in cell K23 let's do a
    one liner for this DAVERAGE,

  • 12:02 - 12:04

    or database average.

  • 12:09 - 12:12

    The database itself is chosen.

  • 12:14 - 12:16

    Type in a comma.

  • 12:16 - 12:21

    The field we're looking for
    is Final Cost in quotes,

  • 12:26 - 12:30

    comma, and then make
    sure that your criteria

  • 12:30 - 12:35

    is campus Hempstead in cells J21 and J22.

  • 12:38 - 12:41

    Once again you can provide
    the closing parentheses

  • 12:42 - 12:45

    or you can just press Enter,
    and you should get that amount.

  • 12:50 - 12:52

    For task six, let's go

  • 12:52 - 12:54

    to the Cost by Green Feature worksheet.

  • 12:54 - 12:57

    Camille has created a
    PivotTable on this worksheet

  • 12:57 - 12:58

    to list the final costs of the projects

  • 12:58 - 13:02

    by green feature, years, and campus.

  • 13:02 - 13:06

    She grouped the year
    data into two-year spans,

  • 13:06 - 13:08

    but wants them listed as separate groups.

  • 13:08 - 13:12

    So we will be un-grouping the
    year data in the PivotTable.

  • 13:12 - 13:14

    And then Camille also
    thinks that the PivotTable

  • 13:14 - 13:18

    looks crowded in its
    default compact layout,

  • 13:18 - 13:21

    so we will change the report
    layout to show the PivotTable

  • 13:21 - 13:23

    in outline form.

  • 13:24 - 13:27

    So on the Cost by Green Feature worksheet,

  • 13:27 - 13:31

    you can see that the dates
    have been grouped here.

  • 13:31 - 13:33

    Click on any one of those dates,

  • 13:33 - 13:38

    and then if you choose
    the PivotTable Analyze tab

  • 13:38 - 13:41

    you have the ability to Ungroup the dates,

  • 13:43 - 13:47

    and it does it to all dates
    in that particular column.

  • 13:47 - 13:52

    Let's also go to the Design tab
    and change the report layout

  • 13:54 - 13:57

    to Show in Outline Form.

  • 13:59 - 14:03

    And it still has our PivotTable
    showing what it needs to,

  • 14:03 - 14:05

    just in a different way.

  • 14:09 - 14:12

    Task eight wants us to go
    the Cost by Campus worksheet.

  • 14:12 - 14:14

    Camille created a
    PivotTable that lists each

  • 14:14 - 14:19

    project by ID and campus and
    then displays its final cost.

  • 14:20 - 14:23

    The data is sorted in
    alphabetical order by the project

  • 14:23 - 14:27

    ID, but Camille wants to
    sort the data from smallest

  • 14:27 - 14:29

    to largest total amount.

  • 14:29 - 14:33

    She always wants to focus
    on renovation projects only

  • 14:33 - 14:36

    which have an ID that beings with RE.

  • 14:36 - 14:40

    So we will change the display
    of the PivotTable as follows.

  • 14:40 - 14:42

    We'll sort the data in ascending
    order by the Grand Total.

  • 14:42 - 14:45

    And we will apply a Label
    Filter that displays

  • 14:45 - 14:49

    the Project ID values that begin with RE.

  • 14:51 - 14:53

    So Cost by Campus worksheet.

  • 14:53 - 14:56

    I am just going to put my insertion point

  • 14:56 - 14:58

    in the Grand Total column.

  • 14:58 - 15:03

    I'm going to choose to
    right-click and sort

  • 15:04 - 15:06

    from smallest to largest.

  • 15:07 - 15:11

    And then we want to
    apply that label filter

  • 15:11 - 15:14

    that displays the Project ID values

  • 15:14 - 15:15

    that begin with RE.

  • 15:15 - 15:18

    So I'm gonna go to the Project ID column.

  • 15:19 - 15:21

    It shows that we've been
    sorting, but lets use

  • 15:21 - 15:25

    the filter here and apply a label filter

  • 15:30 - 15:34

    that begins with the letters RE,

  • 15:37 - 15:38

    and click OK.

  • 15:40 - 15:42

    And we have a much shorter
    list and you should have

  • 15:42 - 15:43

    those Project IDs.

  • 15:47 - 15:50

    Task nine wants us to go the
    Project Budgets worksheet.

  • 15:50 - 15:53

    The PivotTable on this worksheet

  • 15:53 - 15:58

    lists the final cost totals
    and the budgeted amounts

  • 15:58 - 16:00

    by project type.

  • 16:00 - 16:03

    Camille wants to include the
    number of projects for each

  • 16:03 - 16:06

    project type in the second
    column of the PivotTable.

  • 16:06 - 16:10

    So we'll be making the change
    for Camille as follows.

  • 16:10 - 16:13

    We will add the ID
    filed to the Values area

  • 16:13 - 16:15

    of the PivotTable making
    it the first field

  • 16:15 - 16:17

    in the Values area.

  • 16:17 - 16:19

    And then we'll change the
    number format of the Count of ID

  • 16:19 - 16:23

    amounts to Number with
    zero decimal places.

  • 16:25 - 16:27

    So on the Projects Budget worksheet,

  • 16:27 - 16:31

    if you click on the
    PivotTable it should activate

  • 16:31 - 16:35

    your PivotTable fields task
    pane and we can add the ID

  • 16:35 - 16:39

    field to the Values area.

  • 16:43 - 16:45

    Once again it wants it
    to be the first field

  • 16:45 - 16:47

    in the Values area.

  • 16:48 - 16:50

    And then we'll change the number format

  • 16:50 - 16:52

    of the Count of ID field

  • 16:56 - 17:01

    to the Number category
    with zero decimal points.

  • 17:07 - 17:10

    You can see that now in
    column B of the PivotTable.

  • 17:16 - 17:20

    Task 10 states that Camille
    wants to know the difference

  • 17:20 - 17:23

    and the percentage of difference
    between the final costs

  • 17:23 - 17:25

    and the budgeted amounts.

  • 17:25 - 17:28

    Provide this information
    for Camille as follows.

  • 17:28 - 17:31

    We will insert a calculated
    field named Difference

  • 17:31 - 17:33

    that subtracts the Budget field amount

  • 17:33 - 17:35

    from the Final Cost field amount.

  • 17:35 - 17:37

    So let's go ahead and do that.

  • 17:38 - 17:40

    You just have to have
    that PivotTable active

  • 17:40 - 17:42

    as we do this next step.

  • 17:44 - 17:46

    So on the Project Budgets worksheet

  • 17:46 - 17:49

    make sure the PivotTable is active.

  • 17:49 - 17:53

    Make sure the PivotTable
    Analyze tab is active.

  • 17:53 - 17:55

    We're going to the Calculations group,

  • 17:55 - 17:57

    which makes sense to have it there.

  • 17:57 - 18:00

    The Field, Items & Sets dropdown list,

  • 18:00 - 18:02

    choose Calculated Field.

  • 18:02 - 18:05

    The name of the field the
    instructions wanted us to insert

  • 18:05 - 18:10

    is Difference, and then when
    we get into the formula box

  • 18:11 - 18:14

    I simply backspace over the
    zero and I'm ready to do

  • 18:14 - 18:16

    my calculated field.

  • 18:16 - 18:21

    It should be the Final Cost field amount.

  • 18:23 - 18:27

    So I can double-click down
    here in the field section

  • 18:27 - 18:30

    minus the Budget field.

  • 18:33 - 18:37

    And go ahead and click
    OK so that you can see

  • 18:37 - 18:39

    that it does give us that column.

  • 18:41 - 18:43

    Then it wants us to insert
    another calculated field

  • 18:43 - 18:48

    named % Difference that
    subtracts the Budget field

  • 18:48 - 18:50

    amounts from the Final Cost field amounts

  • 18:50 - 18:54

    and then divides the result
    by the Budget field amount.

  • 18:55 - 18:56

    So let's do that.

  • 18:56 - 18:58

    And then we will change the number format

  • 18:58 - 19:01

    of the Sum of % Difference amounts

  • 19:01 - 19:05

    to Percentage with two decimal points.

  • 19:05 - 19:09

    Once again the PivotTable
    needs to be active.

  • 19:09 - 19:10

    PivotTable Analyze tab, Fields,

  • 19:10 - 19:14

    Items & Sets calculated field.

  • 19:14 - 19:17

    The name of this field is % Difference

  • 19:22 - 19:25

    and the calculated field
    is going to have an order

  • 19:25 - 19:29

    of precedence in it, so after
    you backspace over to zero,

  • 19:29 - 19:34

    type in an open parentheses,
    click on Final Cost

  • 19:35 - 19:40

    minus the Budget, closed parentheses,

  • 19:41 - 19:45

    we want that to happen
    first, and then divided by,

  • 19:45 - 19:49

    or forward slash the Budget Field.

  • 19:52 - 19:53

    And click OK.

  • 19:55 - 19:58

    You should now have a % Difference column.

  • 19:58 - 20:03

    Don't worry that it has added
    Sum of to the field name.

  • 20:03 - 20:05

    We're going to leave that as is.

  • 20:05 - 20:09

    But we will be coming down
    to our PivotTable map,

  • 20:09 - 20:14

    specifically the Sum of %
    Difference to change our

  • 20:15 - 20:18

    formatting, our number
    format to Percentage

  • 20:20 - 20:23

    with two decimal points, and OK.

  • 20:32 - 20:35

    Task 11 states that Camille
    also wants to include

  • 20:35 - 20:39

    the average budget for each
    project type as the last

  • 20:39 - 20:41

    column in the PivotTable.

  • 20:41 - 20:44

    So we'll add this information
    to the PivotTable as follows.

  • 20:44 - 20:47

    We'll add the Budget
    field to the Values area

  • 20:47 - 20:49

    of the PivotTable,
    making it the last field

  • 20:49 - 20:51

    in the Values area.

  • 20:51 - 20:55

    Then we'll use the average
    calculation to summarize

  • 20:55 - 20:57

    the Budget field data.

  • 21:00 - 21:04

    So let's add the Budget
    field to the Values area,

  • 21:04 - 21:08

    making sure it is the
    last one, last field.

  • 21:12 - 21:14

    If you have trouble getting
    it to be the last field

  • 21:14 - 21:17

    you can get a double-headed
    arrow here in the PivotTable's

  • 21:17 - 21:19

    task pane and kind of
    size it up so you can see

  • 21:19 - 21:21

    more of what's going on.

  • 21:21 - 21:23

    And then you're just going
    to use the dropdown arrow

  • 21:23 - 21:25

    and the Sum of Budget.

  • 21:25 - 21:29

    Choose the Value field
    settings and set it as Average

  • 21:31 - 21:35

    calculation to summarize
    the Budget field data.

  • 21:37 - 21:40

    And you should have that
    new column now in column G.

  • 21:45 - 21:47

    Our last task wants us to
    go to the Project Types

  • 21:47 - 21:49

    by Year Worksheet.

  • 21:50 - 21:52

    The PivotTable on this worksheet

  • 21:52 - 21:54

    lists the final costs
    of the building projects

  • 21:54 - 21:56

    by year and type.

  • 21:56 - 21:59

    Camille wants to show
    the names of each project

  • 21:59 - 22:02

    and display the information
    about the two project types

  • 22:02 - 22:05

    with the highest total final cost.

  • 22:05 - 22:08

    We'll provide this
    information by expanding

  • 22:08 - 22:12

    the Project Type field to show
    the names of each project.

  • 22:12 - 22:16

    And then applying a filter
    to the Project Type field

  • 22:16 - 22:20

    that displays the top two
    items by the sum of final cost.

  • 22:21 - 22:24

    So let's go to the Project
    Types by Year sheet.

  • 22:25 - 22:29

    It would like us to
    expand our Project Types.

  • 22:29 - 22:31

    There are several ways to do this.

  • 22:32 - 22:37

    If you wanted to do it one
    by one you can use the icon,

  • 22:37 - 22:41

    the plus icon to do that, or
    you can be in that column,

  • 22:41 - 22:45

    in the project types column
    and you should be able

  • 22:45 - 22:48

    to expand up here as well.

  • 22:54 - 22:57

    I don't see it right
    away on the Design tab

  • 22:57 - 23:01

    or the PivotTable Analyze
    tab but you can right-click

  • 23:01 - 23:05

    on one of the project
    types and choose to Expand,

  • 23:05 - 23:08

    Collapse, and Expand.

  • 23:12 - 23:14

    That just does it one by one,

  • 23:19 - 23:21

    or Expand Entire Field.

  • 23:26 - 23:29

    And then you, the last part
    of that step wants us to

  • 23:29 - 23:34

    once again, apply a Value
    filter to the Project Type

  • 23:35 - 23:39

    that displays the top two
    items by the sum of final cost.

  • 23:39 - 23:42

    So I've got a tool to help me do that

  • 23:42 - 23:44

    at the top of the Project Types field,

  • 23:45 - 23:47

    and I can choose Value Filters.

  • 23:48 - 23:50

    Lots of options there.

  • 23:50 - 23:53

    I'm gonna go down to the Top 10 option

  • 23:53 - 23:57

    and instead change it to the top two items

  • 24:00 - 24:03

    by Final Cost totals and click OK.

  • 24:03 - 24:07

    And I should be able to see
    that the two top Project Types

  • 24:07 - 24:10

    are New Construction and Renovation.

  • 24:12 - 24:15

    So your workbook should
    look like the final figures

  • 24:15 - 24:16

    on the following pages.

  • 24:16 - 24:17

    Go ahead and compare.

  • 24:17 - 24:20

    Save your changes, close the workbook,

  • 24:20 - 24:25

    and then upload the project to be graded.

  • 24:32 - 24:33

    Thank you.

  • English - Module 11 Project 1a | Amara (2024)
    Top Articles
    Latest Posts
    Article information

    Author: Jonah Leffler

    Last Updated:

    Views: 5853

    Rating: 4.4 / 5 (65 voted)

    Reviews: 88% of readers found this page helpful

    Author information

    Name: Jonah Leffler

    Birthday: 1997-10-27

    Address: 8987 Kieth Ports, Luettgenland, CT 54657-9808

    Phone: +2611128251586

    Job: Mining Supervisor

    Hobby: Worldbuilding, Electronics, Amateur radio, Skiing, Cycling, Jogging, Taxidermy

    Introduction: My name is Jonah Leffler, I am a determined, faithful, outstanding, inexpensive, cheerful, determined, smiling person who loves writing and wants to share my knowledge and understanding with you.