Microsoft Core Data Analysis, Manipulation, and Presentation (77-727) Free Practice Test
Question 1
Project 7 of 7: Farmers Market
Overview
You are the Director of a local farmers' market. You are creating and modifying charts for a report which
shows the amounts and variety of products sold during the season.
On the "Summer Sales" worksheet, use the data in the "Products" and "Total" columns only to create a 3-D
Pie chart. Position the new chart to the right of the column charts.
Overview
You are the Director of a local farmers' market. You are creating and modifying charts for a report which
shows the amounts and variety of products sold during the season.
On the "Summer Sales" worksheet, use the data in the "Products" and "Total" columns only to create a 3-D
Pie chart. Position the new chart to the right of the column charts.
Correct Answer:
See explanation below.
Explanation
1. Select the data you would like to use, in this case it would be the data in the "Products" and "Total"
columns from the "Summer Sales" worksheet.
2. Click onInsert Pie Chartsituation on theInserttab then pick the 3-D Pie chart as desired.
3. Format the chart as desired by usingChart Elements, theChart Styles, or theChart Filters.
4. Drag the Pie Chart to the desired location which is to the right of the column charts.
References:
https://support.office.com/en-us/article/Add-a-pie-chart-1A5F08AE-BA40-46F2-9ED0-FF84873B7863
Explanation
1. Select the data you would like to use, in this case it would be the data in the "Products" and "Total"
columns from the "Summer Sales" worksheet.
2. Click onInsert Pie Chartsituation on theInserttab then pick the 3-D Pie chart as desired.
3. Format the chart as desired by usingChart Elements, theChart Styles, or theChart Filters.
4. Drag the Pie Chart to the desired location which is to the right of the column charts.
References:
https://support.office.com/en-us/article/Add-a-pie-chart-1A5F08AE-BA40-46F2-9ED0-FF84873B7863
Question 2
Project 4 of 7: Car Inventory
Overview
You manage the office of a used car business. You have been asked to prepare the inventory list for a big
annual sale.
The discount price is 95 percent of the price. Modify column J to show the discount price for each car.
Overview
You manage the office of a used car business. You have been asked to prepare the inventory list for a big
annual sale.
The discount price is 95 percent of the price. Modify column J to show the discount price for each car.
Correct Answer:
See explanation below.
Explanation
1. In cell J10 on the "Car Inventory" worksheet enter "=G10 *.95" OR "=G10 * 95%".
2. Select cell J10, hold theShiftkey on your keyboard, and use the down arrow key until all the cells the
formula is required to be applied (J40).
3. Hold down theCtrlkey on your keyboard and press D which would "copy down" the formula for the rows
below.
OR
1. In cell J10 on the "Car Inventory" worksheet enter "=G10 *.95" OR "=G10 * 95%".
2. Select cell J10, then Right-click and selectCopy,ORclickCopyfrom theClipboardgroup situated on
theHometab.
3.Pastethe formula by selecting Paste from theClipboardgroup situated on theHometab,ORRight-click then
selectPaste,ORclickPastefrom theClipboardgroup situated on theHometab, then clickPaste Specialand
selectFormulas,ORyou could selectValuesfor the formula results instead.
4. Verify that the cell references in the formula are as desired.
References:
https://www.excelforum.com/excel-general/745891-calculate-discount-for-full-column.html
https://support.office.com/en-us/article/move-or-copy-a-formula-1f5cf825-9b07-41b1-8719-bf88b07450c6
Explanation
1. In cell J10 on the "Car Inventory" worksheet enter "=G10 *.95" OR "=G10 * 95%".
2. Select cell J10, hold theShiftkey on your keyboard, and use the down arrow key until all the cells the
formula is required to be applied (J40).
3. Hold down theCtrlkey on your keyboard and press D which would "copy down" the formula for the rows
below.
OR
1. In cell J10 on the "Car Inventory" worksheet enter "=G10 *.95" OR "=G10 * 95%".
2. Select cell J10, then Right-click and selectCopy,ORclickCopyfrom theClipboardgroup situated on
theHometab.
3.Pastethe formula by selecting Paste from theClipboardgroup situated on theHometab,ORRight-click then
selectPaste,ORclickPastefrom theClipboardgroup situated on theHometab, then clickPaste Specialand
selectFormulas,ORyou could selectValuesfor the formula results instead.
4. Verify that the cell references in the formula are as desired.
References:
https://www.excelforum.com/excel-general/745891-calculate-discount-for-full-column.html
https://support.office.com/en-us/article/move-or-copy-a-formula-1f5cf825-9b07-41b1-8719-bf88b07450c6
Question 3
Project 7 of 7: Farmers Market
Overview
You are the Director of a local farmers' market. You are creating and modifying charts for a report which
shows the amounts and variety of products sold during the season.
On the "Summer Sales" worksheet, add the "Total" data series to the "Top Sellers" column chart. Include the
total only for "Fruits", "Vegetables", and "Jams & Honey".
Overview
You are the Director of a local farmers' market. You are creating and modifying charts for a report which
shows the amounts and variety of products sold during the season.
On the "Summer Sales" worksheet, add the "Total" data series to the "Top Sellers" column chart. Include the
total only for "Fruits", "Vegetables", and "Jams & Honey".
Correct Answer:
See explanation below.
Explanation
1. Select the "Top Sellers" column chart.
2. Click on the particular data point you want to label, clickAdd Chart Element +, from the upper right corner
next to the chart, then selectData Labels.
3. Then clickFormat Data Labels.
4. TheData Label Rangedialog box would appear, select the data range which are the cell values you want to
appear in the labels, in this case it would be the "Total" colunn for "Fruits", "Vegetables", and "Jams &
Honey", then clickOK.
References:
https://support.office.com/en-us/article/add-or-remove-data-labels-in-a-chart-884bf2f1-2e29-454e-8b42-f467c9f4
Explanation
1. Select the "Top Sellers" column chart.
2. Click on the particular data point you want to label, clickAdd Chart Element +, from the upper right corner
next to the chart, then selectData Labels.
3. Then clickFormat Data Labels.
4. TheData Label Rangedialog box would appear, select the data range which are the cell values you want to
appear in the labels, in this case it would be the "Total" colunn for "Fruits", "Vegetables", and "Jams &
Honey", then clickOK.
References:
https://support.office.com/en-us/article/add-or-remove-data-labels-in-a-chart-884bf2f1-2e29-454e-8b42-f467c9f4
Question 4
Project 4 of 7: Car Inventory
Overview
You manage the office of a used car business. You have been asked to prepare the inventory list for a big
annual sale.
Modify the cell formatting of cell J9 to display the text on two lines.
Overview
You manage the office of a used car business. You have been asked to prepare the inventory list for a big
annual sale.
Modify the cell formatting of cell J9 to display the text on two lines.
Correct Answer:
See explanation below.
Explanation
1. Select the cell J9 where you want to modify the formatting.
2. On theHometab in theCellsgroup click onFormat.
3. Specify arow heightby clickingRow Heightand then typing in the desired height of 2 lines in the Row
Height box.
References:
https://support.office.com/en-us/article/Wrap-text-in-a-cell-2A18CFF5-CCC1-4BCE-95E4-F0D4F3FF4E84
Explanation
1. Select the cell J9 where you want to modify the formatting.
2. On theHometab in theCellsgroup click onFormat.
3. Specify arow heightby clickingRow Heightand then typing in the desired height of 2 lines in the Row
Height box.
References:
https://support.office.com/en-us/article/Wrap-text-in-a-cell-2A18CFF5-CCC1-4BCE-95E4-F0D4F3FF4E84