MOS EXAM
PROJECT 5: On the "enrollment summary" worksheet, apply the style 7 chart style and the monochromatic palette 6 color to the chart.
Click and press chart design. Press chart 7. Click change colors.
PROJECT 3: On the "classes" worksheet, convert the table to a cell range. Keep the formatting
design, convert to range
PROJECT 1: On the "New Policies" worksheet, in the "Inactive Months" column, use a function to count the number of months with no new policies, by agent.
=COUNTIFS(***
PROJECT 2: On the "authors" worksheet, in the "bonus" column, use a function to display "500" if the "books sold" is greater than 10,000. Otherwise, display "100".
=IF(books sold>10,000, "500", "100"). Select and drag to apply to other rows
PROJECT 4: On the "prices" worksheet, in the "inventory notice" column, use a function to display "low" if the "inventory level %" is below 15%. Otherwise, leave the "inventory notice" blank.
=IF(inventory level<15%), "low", ""). Select and drag to apply to other rows
PROJECT 1: On the "contact" worksheet, in the "email address" column, use a function to construct email addresses for each person using the first name and @humongousinsurance.com
=LOWER(LEFT(FIRST), LAST), "@domain.com"
PROJECT 2: On the "sales" worksheet, in the "city code" column, modify the formula to display the letters in uppercase.
=UPPER(text)
PROJECT 4: On the "prices" worksheet, in the "tax" column, enter a formula that multiplies the value in the "unit price" column by cell L2.
=_*_
PROJECT 1: On the "new policies" worksheet, change the elements displayed on the chart by changing the chart layout to layout 3.
Click chart and press "chart design" click style 3.
PROJECT 4: On the "inventory" chart sheet, insert a chart title above the chart, at the top of the plot area. Display the percentage values as data labels to the right of each data bar.
Click chart, press layout, click chart title and add it. Press add chart element, data labels, more detailed options, uncheck value and check percentage.
PROJECT 1: On the "New Policies" worksheet, add a total row to the table. Configure the total row to display the number of new policies for each month and for the 6-month total
Click design and press total row. Click and drag.
PROJECT 3: On the "instructional hours" worksheet, modify the chart to display "hours" as the primary vertical axis title.
Click on the green plus sign and press axis titles.
PROJECT 6: On the "region 1" worksheet, perform a multi-level sort. Sort the table data by "Product" (A to Z) and then by "total sales" (largest to smallest)
Click sort and filter. Press custom sort and add levels to sort by.
PROJECT 5: On the "last semester" worksheet, configure A3:F3 to automatically wrap the text.
Click wrap text.
PROJECT 6: On the "summary" worksheet, in cells F4:F11, use conditional formatting to apply the yellow fill with dark yellow text format to cells that contain values greater than "$5,000,000".
Conditional formatting, highlight cell rules, greater than and then pick the color.
PROJECT 2: Remove hidden properties and personal information from the workbook. Do not remove any other content.
File, check for issues, inspect, remove all. Click close.
PROJECT 3: On the "substitutes" worksheet, beginning at cell A1, import the data from the substitutes text file in the documents folder. Use the first row of the data source as headers.
File. Open.
PROJECT 3: On the "instructional hours" worksheet, adjust the width of the colums B:G to exactly 12.
Home tab, cells group, click format. Click column width.
PROJECT 1: on the "New Policies" worksheet, change the horizontal alignment of the "Agent" names to Left (Indent). Set the indent to 1.
In the alignment tab, click increase indent.
PROJECT 2: On the "summary" worksheet, from the existing text in cell A2, insert a hyperlink to "http://www.nodpublishers.com" with a screentip that displays "company website"
Insert, hyperlink.
PROJECT 4: On the "orders" worksheet, filter the table data to display only the orders from "alpine ski house".
On the data tab, click filter. Display only alpine.
PROJECT 5: On the "last semester" worksheet, remove the table row that contains "agriculture" data. Do not change any content.
On the hoem tab, click find and select and click find. Type agriculture and press find all. Select all and then delete them with the home tab by clicking delete sheet rows.
PROJECT 6: On the "comparison" chart sheet, swap the data over the axis.
Right click and open axis options. Check the box that says values in reverse order.
PROJECT 2: On the "summary" worksheet, expand the chart to include the "current year" data.
Right click inside chart. Press select data. Press add. Add series name and series value (make sure to delete previous data.
PROJECT 3: From the "graduation" worksheet, move the chart to a new chart sheet named "Graduation Chart"
Right click on the chart. Click move chart. Click new sheet and rename it.
PROJECT 3: On the "enrollment" worksheet, in cells G5:G25, insert Column sparklines to compare the last, current, and next term values for each class period.
Select a blank cell and press insert. Pick column sparklines. Select the data range. Click and drag the bottom right corner for more.
PROJECT 1: On the "New Policies" worksheet, in cells J5:J13, insert win/loss sparklines to compare the January through June values.
Select a blank cell and press insert. Pick win/loss sparklines. Select the data range. Click and drag the bottom right corner for more.
PROJECT 5: On the "revenue" worksheet, convert cells A3:B7 to a table with headers. Apply the green, table style light 14 style
Select a cell within your data. Press home and then format as table. Click the table style and select your range.
PROJECT 4: On the "quote" worksheet, move the cells E8:F32 up to correct the alignment by deleting cells E7:F7.
Select and press delete on the home tab.
PROJECT 5: On the "enrollment summary" worksheet, name the range A3:B7 "enrollment"
Select the range and type the name
PROJECT 2: On this "historical sales" worksheet, display the formulas instead of the values.
options, advanced. Display options for this worksheet, press show formulas in cells instead of their calculated results.
