Pass the Microsoft MOS-Microsoft Office Specialist 77-727 Questions and answers with CertsForce

1. Select cell A5 of the “$5,000 Donors” worksheet where you want the source file to be imported to.

2. Click on File, then selectOpen.

3. SelectText Filesfrom theOpendialog box.

4. Locate the contributors.txt file in the Documents folder, then double-click it.

5. ClickFinishto complete the operation.

References:

https://support.office.com/en-us/article/Import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b-339e391393ba

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

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-f467c9f4eb2d

1. ClickDesignfrom the Ribbon onTable Tools.

2. In theToolsgroup, click onConvert to Range.

OR

1. Right-click thetablethen click on Table then pressConvert to Range.

References:

https://support.office.com/en-us/article/convert-an-excel-table-to-a-range-of-data-0b326ff1-1764-4ebe-84ea-786265d41c77?redirectSourcePath=%252fen-us%252farticle%252fRemove-a-table-without-losing-the-data-or-table-format-ADF88635-90F5-4FAA-9417-19862F38CCE8 &ui=en-US&rs=en-US&ad=US

1. Select a cell in the “CountryOrRegion” field on the “Customers” worksheet.

2. Select Sort from theSort & Filtergroup in theDatatab.

3. In theSortdialog box, underColumnin theSort bybox, Select the “CountryOrRegion” field.

4. Under theSort Onoption, select the type of sort desired, in this caseValues, to sort by text.

5. UnderOrder, selectCustom List, then set it for customers in the United States to appear first, then the customers in Canada.

6. Then to add another column to sort by, clickAdd Level.

7. In theSortdialog box, underColumnin theSort bybox, Select the “StateOrProvince” field.

8. Under theSort Onoption, selectValues.

9. UnderOrder, selectA to Z.

10. Add a final column by clickingAdd Level.

11. In theSortdialog box, underColumnin theSort bybox, Select the “PostalCode” field.

12. UnderSort On, selectValues.

13. UnderOrder, selectSmallest to Largest.

14. ClickOK.

References:

https://support.office.com/en-us/article/sort-data-in-a-range-or-table-62d0b95d-2a90-4610-a6ae-2e545c4a4654

1. On the “Car Inventory” worksheet, clickPrint Titlesfrom thePage Setupgroup, situated on thePage Layouttab.

2. UnderPrint Titles, on theSheettab, type the reference of the row you want to reappear (row 9) in theRows to repeat at topbox.

References:

https://support.office.com/en-us/article/Print-rows-with-column-headers-on-top-of-every-page-D3550133-F6A1-4C72-AD70-5309A2E8FE8C

1. Select cell N2 on the “Customers” worksheet, which is where you want the Excel function to return the result to.

2. In theAddressbar, type the average function syntax;=AVERAGE(K1:K101).

References:

https://support.office.com/en-us/article/AVERAGE-function-047BAC88-D466-426C-A32B-8F33EB960CF6

1. In cell M9, on the “Summer Bookings” worksheet, insert the followingCOUNTIFformula: “=COUNTIF(G6:G24, >= 12”)”

References:

https://support.office.com/en-us/article/count-numbers-greater-than-or-less-than-a-number-453b0ccc-cfaa-4332-ad02-6e148e01aa0a

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-BF957C6CE29C

1. Right-click the text title “Adult_Program” and clickFormat Objectthen clickAlt Text.

2. Type “Adult Enrollment” in theTitlebox as desired.

3. ClickOK.

References:

https://support.office.com/en-us/article/add-alternative-text-to-a-shape-picture-chart-smartart-graphic-or-other-object-44989b2a-903c-4d9a-b742-6a75b451c669#bkmk_o2016_2013