Achieve Success in the Microsoft 77-727 Exam: A Detailed Excel 2016: Core Data Analysis, Manipulation, and Presentation Guide

1. Click on cell C3.

2. Enter the following formula: “=HYPERLINK(john@bellowsollege.com, C3)

References:

https://support.office.com/en-us/article/HYPERLINK-function-333C7CE6-C5AE-4164-9C47-7DE9B76F577F

1. Select the cells below the rows and to the right of the columns you want to keep visible, which in this case is row 10 and column H.

2. ClickFreeze Panesfrom theFreeze Panesgroup in theViewtab.

References:

https://support.office.com/en-ie/article/Freeze-panes-to-lock-rows-and-columns-dab2ffc9-020d-4026-8121-67dd25f2508f

1. Select the cells in the “Weight” column which you want to apply the number formatting to.

2. ClickIncrease Decimaltill the desired decimal places of three is selected, this is found on theHometab.

OR

1. Select the cells in the “Weight” column which you want to apply the number formatting to.

2. On theHometab, in theNumbergroup, click the arrow next to the list of number formats, then selectMore Number Formats.

3. Select the type of data you have in theCategorylist, in this case it would beScientific.

4. Enter the desired amount ofdecimal placesyou want to display in the Decimal places box, in this case it would be three.

References:

https://support.office.com/en-us/article/Round-a-number-to-the-decimal-places-I-want-49B936F9-6904-425D-AA98-02FFB7F9A17B

1. On the “Customers” worksheet, click theSelect AllbuttonORpress Ctrl+A.

2. Click the arrow next toConditional Formattingfrom theHometab in theStylesgroup, then selectNew Rule.

3. ClickSelect a Rule Typein theNew Formatting Ruledialog box, then selectUse a formula to determine which cells to format.

4. In theFormat values where this formula is truebox, enter =MOD(ROW(),2)=0.

5. Click onFormat.

6. Click theFilltab from theFormat Cellsdialog box.

7. Select the desired shade background and then clickOK, you are able to view how the background would look in thePreviewwindow in theNew Formatting Ruledialog box.

8. Once satisfied, apply the formatting to the cells by clickingOK.

References:

https://support.office.com/en-us/article/Apply-shading-to-alternate-rows-in-a-worksheet-A443B0F5-2025-42F6-9099-5DE09C05E880

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. SelectFormatfrom theHometab then chooseHide and Unhidethen selectUnhide.

OR

1. Hold on the Ctrl button on the keyboard and choose all the worksheet tabs then choose theUnhideoption in the right menu.

2. Click on the first worksheet tab, then hold down the Shift key on the keyboard as you click on the last worksheet tab, then choose theUnhideoption.

OR

1. From theViewtab, clickCustom Viewsin theWorkbook Viewsgroup.

2. Click the “Summary” view which is hidden, then clickShow.

References:

https://www.isunshare.com/blog/hide-or-unhide-sheets-in-excel-2016/

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. On the “Adult Program” worksheet, select the area you want the sparkline to be inserted.

2. Click Column in theSparklinesgroup from theInserttab.

3. Enter the range of cells in theData Rangebox, in this caseA10:A19.

OR

3. Select the range of cells by temporarily collapsing the dialog box and manually selecting the cells.

4. ClickOKand theSparkline Toolswill appear, theDesigntab can be used to customize the sparklines.

References:

https://support.office.com/en-us/article/analyze-trends-in-data-using-sparklines-be6579cf-a8e3-471a-a459-873614413ce1

1. Select the rows you want to hide, in this case rows number 29 and 30 by clicking on them.

2. Right-click on the selected rows then click Hide.

References:

https://support.office.com/en-us/article/Hide-or-show-rows-or-columns-659c2cad-802e-44ee-a614-dde8443579f8