IS Practice Exam COPY

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

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.

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(A6,G6)

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. (i dont think we do this BUT On the menu, click fill formatting only.)

Add Olympics as a Title property to the workbook.

Click file, click info and then in properties (top right ish) add your title "Olympics"

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 and click the arrow button and then 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 button. 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 bottom arrow and click "fit to 1 page" and then press ok

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. In the leftmost header, type "Little Turtle Jewelry". In the right header, add current date by clicking the button up top and click off (possibly 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.

Wrap text of text, "Corporate Olympics" in cell A1.

In the home tab alignment group, click the wrap text button.

Find all occurrences of "Profit" and replace it with "Earnings".

In the home tab editing group, click the find & select button (far right). 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.

On the Cash worksheet, apply the Percent Style to cell E5, displaying 0 digits to the right of the decimal point.

In the home tab in number group, click the percent style button, change decimal to 0.

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 home tab styles group, click the conditional formatting button. On the menu, click new rule, then click "format only cells that contain" and then change the between to "greater than or equal to" and put 50, then click format and under font choose red OR On the menu, click new rule. In the select rule type section, click format only top or bottom ranked values.

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 home tab, click the autosum button (far right). Press enter.

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 edit alt text, and type " Salary Expenses for project director and part-time staff" ORRR--- Right click on the chart. Click format chart area. Now click on alt text

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 D23:F23 and then hold down control and select D4:F4. Click the insert tab, under the Charts section look at the first column next to recommended charts and click on the middle one (between the bars and circle), under 2D click line (first one)

For the range N6:N24, add Line sparklines from the data range of K6:M24.

Select N6 and press insert. Press Line. for data range put K6:M24 and for location range put N6:N24 (Select cells. Drag the bottom corner.)

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 tab at top and then click switch row/column button (towards the right)

Modify the line chart title to read Expenses

click the plus sign (chart elements), click chart title, change it to "Expenses" OR maybe--In the layout tab, click chart title. Click above chart and type "Expenses."

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