77-727 Excel 2016: Core Data Analysis, Manipulation, and Presentation Questions and Answers

1. On the “Summer Bookings” worksheet, click Header & Footer from the Text group situation on the Insert tab.

2. Click Click to add footer which would display the Header & Footer tools which gets added to the Design tab.

3. Specify where the page number should be by selecting the Center section box.

4. On the Design tab in the Header & Footer Elements group, click Page Number .

5. The placeholder & [Page] will appear in the selected section, to add the total number of pages type the word of followed by the space in the Header & Footer Elements group after clicking Number of Pages , then the placeholder & [Page] of & [Pages] appear.

6. Click anywhere outside the header or footer area to display the page numbers in Page Layout View .

7. Once you are done working in the Page Layout View , click Normal in the Workbook Views group situated on the View tab. OR You can also click Normal on the status bar.

References:

https://support.office.com/en-us/article/Insert-page-numbers-on-worksheets-27A88FB9-F54E-4AC4-84D7-BF957C6CE29C

1. To check the accessibility select the Review tab from the ribbon.

2. Select Check Accessibility .

3. Review the results.

4. Exit the Accessibility Checker .

5. Right-click on the worksheet then click Format and then click Alt Text .

6. Type “Tree Nursery Inventory” in the Description box.

7. Click OK .

References:

https://support.office.com/en-us/article/Use-the-Accessibility-Checker-to-find-accessibility-issues-a16f6de0-2f39-4a2b-8bd8-5ad801426c7f

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

2. Click Increase Decimal till the desired decimal places of three is selected, this is found on the Home tab.

OR

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

2. On the Home tab, in the Number group, click the arrow next to the list of number formats, then select More Number Formats .

3. Select the type of data you have in the Category list, in this case it would be Scientific .

4. Enter the desired amount of decimal places you 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 the Select All button OR press Ctrl+A.

2. Click the arrow next to Conditional Formatting from the Home tab in the Styles group, then select New Rule .

3. Click Select a Rule Type in the New Formatting Rule dialog box, then select Use a formula to determine which cells to format .

4. In the Format values where this formula is true box, enter =MOD(ROW(),2)=0.

5. Click on Format .

6. Click the Fill tab from the Format Cells dialog box.

7. Select the desired shade background and then click OK , you are able to view how the background would look in the Preview window in the New Formatting Rule dialog box.

8. Once satisfied, apply the formatting to the cells by clicking OK .

References:

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

1. Select the cell J2 where you want the highest “OrderTotal” value to be.

2. Type in the formula: =MAX(G1:G550).

References:

https://support.office.com/en-us/article/calculate-the-smallest-or-largest-number-in-a-range-45fe249f-96c3-443b-8e9f-87f16c48462c

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

2. Select Sort from the Sort & Filter group in the Data tab.

3. In the Sort dialog box, under Column in the Sort by box, Select the “CountryOrRegion” field.

4. Under the Sort On option, select the type of sort desired, in this case Values , to sort by text.

5. Under Order , select Custom 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, click Add Level .

7. In the Sort dialog box, under Column in the Sort by box, Select the “StateOrProvince” field.

8. Under the Sort On option, select Values .

9. Under Order , select A to Z .

10. Add a final column by clicking Add Level .

11. In the Sort dialog box, under Column in the Sort by box, Select the “PostalCode” field.

12. Under Sort On , select Values .

13. Under Order , select Smallest to Largest .

14. Click OK .

References:

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

1. Click Design from the Ribbon on Table Tools .

2. In the Tools group, click on Convert to Range .

OR

1. Right-click the table then click on Table then press Convert 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