MOS Practice Exam
On the Cash worksheet, in cell B22, enter a formula using relative cell addresses that finds the difference between total income in cell B6 and total expenses in cell B20.
=B6-B20
Add Olympics as a Title property to the workbook.
Click file and then in properties add your title
Wrap text of text, "Corporate Olympics" in cell A1.
In the alignment group, click the wrap text button.
Create a formula in cell H6 using the LEFT function that returns the first name from the entry in cell A6. The formula should use cell G6 which calculates the length of the first name.
=LEFT(
Create a table from the data in cells A3:L23. The first row contains table headers.
Click and drag to select range A3:L23. Click the insert tab on the ribbon. In the tables group, click the table button. Click ok.
Apply the Table Style Light 18 (Light section, row 3, fifth option) to the Pledges table.
Click cell A5. Click the table tools design tab. In the table styles group, click the more button. Apply style light 18.
Use the Format Painter to apply the styles from cell B29 to cells C29 and D29.
Click cell B29. In the clipboard group, click the format painter button. Click and drag cells C29 and D29.
Use AutoFill to copy the formula from cell H6 through range H7:H41.
Click cell H6. Drag the fill handle at the bottom of cell D11 (plus button) to the bottom of cell D19. Release the mouse button. Click the auto fill options button. On the menu, click fill formatting only.
Sort the Pledges table in ascending order based on the Zip Code field.
Click the cell with the zip code. In the editing group on the home tab, click the sort & filter button. On the menu, click custom sort. Click the column sort by arrow. Click Zip code.
Move the "Salary Expenses" chart to a new sheet titled Salary
Click the chart. Click the chart tools design tab on the ribbon. In the location group, click the move chart button. Click the new sheet and rename it to Salary. Click ok.
Insert a new, blank column F. The Projection column and Growth Assumptions data will move one column to the right.
Click the column F column heading. Right-click the selection. On the shortcut menu, click insert.
For the chart titled "Salary Expenses," change the chart type from Pie to Clustered Column. Select the Clustered Column chart that retains a different color for each data marker.
Click the design tab. Click change chart type. Click column and pick clustered column.
Apply scaling options to fit the sheet to one page, but do not print the page.
Click the page layout tab on the ribbon. In the scale to fit group, click the scale box, type 70, and then press enter.
Create a custom header with the text Little Turtle Jewelry in the left section and the current date code using Header & Footer Elements in the right section.
Click the view tab on the ribbon. In the workbook views group, click the page layout button. To add current date, click design and press current date.
Rename Sheet1 to Budget
In the cells group on the home tab, click the format button. Click rename sheet. Press enter to rename.
On the Cash worksheet, copy and paste the formula from cell C4 to cell C5.
In the clipboard group on the home tab, click the copy button. In the clipboard group on the home tab, click the paste button.
In cell R55, use the SUM function to sum the value from the Pledge column.
In the editing group, click the autosum button. Press enter.
On the Cash worksheet, in cell B20, enter a formula using the SUM function to add the values in cells B9 through B19.
In the editing group, click the autosum button. Press enter.
Find all occurrences of "Profit" and replace it with "Earnings".
In the editing group, click the find & select button. On the find & select menu, click replace. Type Profit in the find what box and type earnings in the replace with box. Click find next. Click replace. Click replace all. Click ok. Click close.
Modify the line chart title to read Expenses
In the layout tab, click chart title. Click above chart and type "Expenses."
On the Cash worksheet, apply the Percent Style to cell E5, displaying 0 digits to the right of the decimal point.
In the number group, click the percent style button.
For the range C6:C41, create a new conditional format that changes the font color to red for values that are greater than or equal to 50.
In the styles group, click the conditional formatting button. On the menu, click new rule. In the select rule type section, click format only top or bottom ranked values.
On the Cash worksheet, change the value in cell B4 to 1000.
Just type it in
Delete column P.
Right click and select delete.
Add the following alternative text to the Salary Expenses chart and then close any open panes: Salary Expenses for project director and part-time staff
Right click on the chart. Click format chart area. Now click on alt text
For the range N6:N24, add Line sparklines from the data range of K6:M24.
Select N6 and press insert. Press Line. Select cells. Drag the bottom corner.
Insert a 2D Line chart on the Projections sheet that charts the Total Project Expenses from the range D23:F23 for the three years in the range D4:F4.
Select data. Click the insert tab and press 2D line.
Add a record to the end of the table named Prospects with the following data: First Name: Tony, Last Name: Hosmer, Phone Number: 555-123-1234, Donation: 1000 and then add a total row to the Prospects table and subtotal the Donation field.
Select the cell in the row directly below the table. Type Tony and press tab. Type Hosmer in the next cell. Press tab. Type 555-123-1234. Press tab. Type 1000. Press tab. Press enter. To add total row click the table tools design tab. In the table style options group, click the total row check box.
For the chart titled "Materials and Supplies," on the Projections sheet, switch the rows and columns so that the years are in the legend and the categories are labels on the x-axis.
click design and then switch row/column
Position the line chart so that the upper left corner starts in cell H2.
drag it
Change the order of the Tuition and Cash worksheets so that the Tuition sheet is first.
drag them
