Microsoft Core Data Analysis, Manipulation, and Presentation - 77-727 Exam Practice Test
Project 2 of 7: Donor List
Overview
You are an executive assistant for a non-profit organization named Bellows College. You are updating a
workbook containing lists of donors.
Arrange the worksheets so that "$5,000 Donors" is first.
Overview
You are an executive assistant for a non-profit organization named Bellows College. You are updating a
workbook containing lists of donors.
Arrange the worksheets so that "$5,000 Donors" is first.
Correct Answer:
See explanation below.
Explanation
1. ClickSort Sheetsfrom theWorksheetgroup in theKutools Plustab.
2. Click theAlpha sortbuttonORtheAlpha Numeric Sortbutton in theSort Sheetsdialog box, the results can be
previewed in theNew sheets tab orderlist box.
3. Use theMove uporMove downbutton to manually change the order of the sheets so that the "$5,000 Donors"
worksheet is first in theNew sheet tabs orderlist.
4. ClickOKonce satisfied with the results.
References:
https://www.extendoffice.com/product/kutools-for-excel/sort-worksheets-in-one-workbook-in-alphabetical-order
Explanation
1. ClickSort Sheetsfrom theWorksheetgroup in theKutools Plustab.
2. Click theAlpha sortbuttonORtheAlpha Numeric Sortbutton in theSort Sheetsdialog box, the results can be
previewed in theNew sheets tab orderlist box.
3. Use theMove uporMove downbutton to manually change the order of the sheets so that the "$5,000 Donors"
worksheet is first in theNew sheet tabs orderlist.
4. ClickOKonce satisfied with the results.
References:
https://www.extendoffice.com/product/kutools-for-excel/sort-worksheets-in-one-workbook-in-alphabetical-order
Project 1 of 7: Tailspin Toys
Overview
You recently opened an online toy store. You have sold products to 500 individual customers. You are
evaluating customer data and order data.
On the "Orders" worksheet, use an automatic formatting method to format cells in the "OrderTotal" column
that contain above-average values by applying Green Fill with Dark Green Text. Use a technique that
automatically updates the formatting if the column values change.
Overview
You recently opened an online toy store. You have sold products to 500 individual customers. You are
evaluating customer data and order data.
On the "Orders" worksheet, use an automatic formatting method to format cells in the "OrderTotal" column
that contain above-average values by applying Green Fill with Dark Green Text. Use a technique that
automatically updates the formatting if the column values change.
Correct Answer:
See explanation below.
Explanation
1. Select the cells in the "OrderTotal" column which is the data range you are required to highlight the cells
which are above-average.
2. ClickConditional Formattingfrom theHometab, then selectTop/Bottom Rules, then select theAbove
Averageoption.
3. A popped up dialog box will then appear, select the Green Fill with Dark Green Text option as desired.
4. ClickOK.
References:
https://www.extendoffice.com/documents/excel/2195-excel-highlight-above-average.html
Explanation
1. Select the cells in the "OrderTotal" column which is the data range you are required to highlight the cells
which are above-average.
2. ClickConditional Formattingfrom theHometab, then selectTop/Bottom Rules, then select theAbove
Averageoption.
3. A popped up dialog box will then appear, select the Green Fill with Dark Green Text option as desired.
4. ClickOK.
References:
https://www.extendoffice.com/documents/excel/2195-excel-highlight-above-average.html
Project 2 of 7: Donor List
Overview
You are an executive assistant for a non-profit organization named Bellows College. You are updating a
workbook containing lists of donors.
Add a new worksheet named "$1,000 Donors" to the workbook.
Overview
You are an executive assistant for a non-profit organization named Bellows College. You are updating a
workbook containing lists of donors.
Add a new worksheet named "$1,000 Donors" to the workbook.
Correct Answer:
See explanation below.
Explanation
1. Select the plus icon at the bottom of the screen.
OR
1. SelectInsertfrom theHometab, then clickInsert Sheet.
References:
https://support.office.com/en-us/article/Insert-or-delete-a-worksheet-19d3d21e-a3b3-4e13-a422-d1f43f1faaf2
Explanation
1. Select the plus icon at the bottom of the screen.
OR
1. SelectInsertfrom theHometab, then clickInsert Sheet.
References:
https://support.office.com/en-us/article/Insert-or-delete-a-worksheet-19d3d21e-a3b3-4e13-a422-d1f43f1faaf2
Project 6 of 7: Bike Tours
Overview
You are the owner of a small bicycle tour company summarizing trail rides that have been booked for the next
six months.
Insert page numbering in the center of the footer on the "Summer Bookings" worksheet using the format Page
1 of ?.
Overview
You are the owner of a small bicycle tour company summarizing trail rides that have been booked for the next
six months.
Insert page numbering in the center of the footer on the "Summer Bookings" worksheet using the format Page
1 of ?.
Correct Answer:
See explanation below.
Explanation
1. On the "Summer Bookings" worksheet, clickHeader & Footerfrom theTextgroup situation on theInserttab.
2. ClickClick to add footerwhich would display theHeader & Footertools which gets added to theDesigntab.
3. Specify where the page number should be by selecting theCenter sectionbox.
4. On theDesigntab in theHeader & Footer Elementsgroup, clickPage Number.
5. The placeholder&[Page]will appear in the selected section, to add the total numberofpages type the word of
followed by the space in theHeader & Footer Elementsgroup after clickingNumber of Pages, then the
placeholder&[Page] of &[Pages]appear.
6. Click anywhere outside the header or footer area to display the page numbers inPage Layout View.
7. Once you are done working in thePage Layout View, clickNormalin theWorkbook Viewsgroup situated on
theViewtab. OR You can also clickNormalon the status bar.
References:
https://support.office.com/en-us/article/Insert-page-numbers-on-worksheets-27A88FB9-F54E-4AC4-84D7-BF95
Explanation
1. On the "Summer Bookings" worksheet, clickHeader & Footerfrom theTextgroup situation on theInserttab.
2. ClickClick to add footerwhich would display theHeader & Footertools which gets added to theDesigntab.
3. Specify where the page number should be by selecting theCenter sectionbox.
4. On theDesigntab in theHeader & Footer Elementsgroup, clickPage Number.
5. The placeholder&[Page]will appear in the selected section, to add the total numberofpages type the word of
followed by the space in theHeader & Footer Elementsgroup after clickingNumber of Pages, then the
placeholder&[Page] of &[Pages]appear.
6. Click anywhere outside the header or footer area to display the page numbers inPage Layout View.
7. Once you are done working in thePage Layout View, clickNormalin theWorkbook Viewsgroup situated on
theViewtab. OR You can also clickNormalon the status bar.
References:
https://support.office.com/en-us/article/Insert-page-numbers-on-worksheets-27A88FB9-F54E-4AC4-84D7-BF95