Excel Final Test

Copy the format for the month, Jan, to the word Month in cell A3. Then, wrap the text inside cell A10.

1.) Select cell B3 2.) In the Clipboard group on the Home tab, select Format Painter 3.) Select cell A3 4.) Select cell A10 5.) In the Alignment group, select Wrap Text

Use the ribbon to transpose cells B2 through K2 from the Current worksheet to the Summary worksheet, starting in cell A5. Then, return to the Current worksheet and paste the data, starting in cell 02, so the column widths are maintained from the original data.

Step 1: Highlight cells B2 through K2 Step 2: In the Clipboard group, select Copy Step 3: Select the Summary worksheet tab Step 4: Select cell A5 Step 5: In the Clipboard group, select the Paste dropdown arrow Step 6: In the Paste group, select Transpose Step 7: Select the Current worksheet tab Step 8: Select cell 02 Step 9: In the Elipboard group, select the Paste dropdown arrow Step 10: In the Paste group, select Keep Source Column Widths

Group the two worksheets in the current workbook, apply bold formatting to cells A3 through M3.

Step 1: Holding down Ctrl, Select each of the Worksheet tabs Step 2: Highlight cells A3 through M3 Step 2: In the Font group on the Home tab, select Bold

Unmerge cell A1. Then, merge and center the selection across the columns that hold the data below the selection. Then, center- align the months and apply the Angle Counterclockwise orientation to those cells.

Step 1: In the Alignment group on the Home tab, select the Merge & Center dropdown arrow Step 2: Select Unmerge Cells Step 3: Select Merge & Center Step 4: Select cells B3 through M3 Step 5: In the Alignment group, select Center Step 6: Select the Orientation dropdown arrow Step 7: Select Angle Counterclockwise

Add the Accounting Number Format to the Quick Access Toolbar.

Step 1: In the Number group, select and hold (or right-click) Accounting Number Format Step 2: Select Add to Quick Access Toolbar

Remove the conditional formatting rule that is applied to this worksheet.

Step 1: In the Styles group on the Home tab, select the Conditional Formatting dropdown arrow Step 2: Select Manage Rules Step 3: Select the Show formatting rules for dropdown arrow Step 4: Select This Worksheet Step 5: Select the existing rule Step 6: Select Delete Rule Step 7: Select the OK button

In cell C5 of the current worksheet, generate a random number between 1 and 10 and copy the value to Cell D5. Ensure the number does not change when activity occurs on the worksheet. Then, starting in cell C3, add a sequence of numbers that starts with 10 and increases by 10 for each month through December.

Step 1: Select Cell C5 Step 2: Type: =RANDBETWEEN(1,10) Step 3: Select the Enter key on the keyboard Step 4: Select cell C5 Step 5: In the Clipboard group on the Home tab, select Copy Step 6: Select cell D5 Step 7: In the Clipboard group, select the Paste dropdown arrow Step 8: Inlthe Paste Values group, select Values Step 9: Select cell C3 Step 10: Type: =SEQUENCE(1,12,10,10) Step 11: Select the Enter key on the keyboard

Using the data in cells B4 through M4, add a count function in cell T4 that counts all the scores. Using the same data in cell UA, add a count function to count all the scores and retakes. Using the same data, in cell V4, add a function to count all the cells containing no text or numbers.

Step 1: Select Cell T4 Step 2: type: =COUNT(B4:M4) Step 3: Select the Enter key on the keyboard Step 4: Select col 114 Step 5: Type: =COUNTA/R4-MA) Step 6: Select the Enter key on the keyboard Step 7: Select cell V4 Step 8: Type: =COUNTBLANK(B4:M4) Step 9: Select the Enter key on the keyboard

On the current worksheet, move the January and February data down by inserting two new cells to sit above the months. Then, delete cell N3 to move the December data backup to be even with the rest of the months.

Step 1: Select Cells C2:D2 Step 2: n the Cells group on the Home tab, select the Insert dropdown arrow Step 3: Select Insert Cells Step 4: Select the Shift cells down option Step 5: Select the OK button Step 6: Select cell N3 Step. 7: In the Tells group, select the Delete dropdown arrow Step 8: Select Delete Cells Step 9: Select the Shift cells up option Step 10: Select the OK button

In the current table, add a column to the right of the Senior column. Then, delete the row containing no dollar amounts.

Step 1: Select and hold (or right-click) a table cell in column F Step 2: Select Insert Step 3: Select Table Column to the Right Step 4: Select and hold (or right-click) a table cell in row 9 Step 5: Select Delete Step 6: Select Table Rows

Create a table using the numeric data on the worksheet and its row and column headings. Then, apply the Light Blue, Table Style Light 2 style to the table.

Step 1: Select any cell in the range A3 through E12 Step 2: Select the Insert tab Step 3: In the Tables group, select Table Step 4: Select the OK button Step 5: If necessary, select the Table Design contextual tab Step 6: In the Table Styles group, select the More dropdown arrow Step 7: In the Light group, select Light Blue, Table Style Light 2

Convert the current table into a range of cells.

Step 1: Select any cell in the range A2 through E11 Step 2: Select the Table Design contextual tab Step 3: In the Tools group, select Convert to Range Step 4: Select Yes

In cell X4, add a function that will display the word YES if cell V4 is greater than zero; if not, the cell should remain blank.

Step 1: Select cell X4 Step 2: Select the Formulas tab Step 3: Select the Logical dropdown arrow Step 4: Select IF Step 5: Select cell V4 Step 6: type: >0 Step 7: Activate the cursor in the Value_if_true field Step 8: Type YES Step 9: Activate the cursor in the Value_if_false_field Step 10: Type: "" Step 11: Select the OK button

Set cells A1 through M18 as the Print Area for the current worksheet.

Step 1: Select cells A1 through M18 Step 2: Select the Page Layout tab Step 3: In the Page Setup group, select the Print Area dropdown arrow Step 4: Select Set Print Area

Create a pie chart using the data and heading in the Totals row. Then, move the pie chart to its own sheet, naming the new sheet, Totals by Month.

Step 1: Select cells A8 through F8 Step 2: Select the Insert tab Step 3: In the Charts group, select the Insert Pie or Doughnut Chart dropdown arrow Step 4: In the 2-D Pie group, select Pie Step 5: In the Location group, select Move Chart Step 6: Select the New sheet option Step 7: Type: Totals by Month Step 8: Select the OK button

For cells B3 through E6, apply the Bold font style to all numbers over 7.

Step 1: Select cells B3 through E6 Step 2: In the Styles group, select the Conditional Formatting dropdown arrow Step 3: Select Highlight Cells Rules Step 4: Select More Rules Step 5: Select the greater than dropdown arrow Step 6: Select greater than or equal to Step 7: Activate the cursor in the Criteria field Step 8: Type: 8 Step 9: Select Format Step 10: In the Font style area, select Bold Step 11: Select the OK button twice

On the current worksheet, apply the Accounting Number Format to cells B4 through M6. Then, format the tax rate in cell P3 as a percentage carried to one decimal point.

Step 1: Select cells B4 through M6 Step 2: In the Numbers group on the Home tab, select Accounting Number Format Step 3: Select cell P3 Step 4: In the Numbers group, select Percent Style Step 5: In the Numbers group, select Increase Decimal

Apply the 20%-Accent1 style to the cells with dollar amounts. Then, apply the Note style to cell A12 and clear the formatting from cell A3.

Step 1: Select cells B4 through M6 Step 2: In the Styles group on the Home tab, select the Cell Styles dropdown arrow Step 3: Select 20%- Accent1 Step 4: Select cell A12 Step 5: In the Styles group, select the Cell Styles dropdown arrow Step 6: Select Note Step 7: Select cell A3 Step 8: In the Editing group, select the Clear dropdown arrow Step 9: Select Clear Formats

Set the height on the selected row to 30. Then, set the width of column A to 15.

Step 1: Select row 1 Step 2: In the Cells group, select the Format dropdown arrow Step 3: Select Row Height Step 4: Type: 30 Step 5: Select the OK button or select the Enter key on the keyboard Step 5: Select column A Step 6: Select the Format dropdown arrow Step 7: Select Column Width Step 8: Type: 15 Step 9 Select the OK button or select the Enter key on the keyboard

Freeze the first two rows of the current worksheet. Then, open a second window of the worksheet and arrange the windows vertically.

Step 1: Select row 3 Step 2: Select the View tab Step 3: In the Window group, select the Freeze Panes dropdown arrow Step 4: Select Freeze Panes Step 5: In the Window group, select New Window Step 6: Select the View tab Step 7: In the Window group, select Arrange All Step 8: Select Vertical Step 9: Select the Ok button

Import the 112-teachers. json file

Step 1: Select the Data tab Step 2: In the Get & Transform Data group, select the Get Data dropdown arrow Step 3: Select From File Step 4: Select From JSON Step 5: Browse to the GmetrixTemplates folder Step 6: Select 112- teachers.json Step 7: Select Import or select the Enter key on the keyboard Step 8: Select Into Table Step 9: To the right of the Value heading, select the Split icon Step 10: Select the OK button Step 11: Select Close & Load

Add three tags to the current worksheet: Social Studies, grades, and winter. Then, show all the available properties for the current workbook without showing any dialog boxes. When done, return to the worksheet and display all the formulas on the worksheet.

Step 1: Select the File tab Step 2: Select Info Step 3: Select Add a tag Step 4: Type: Social Studies; grades; winter Step 5: Select Show All Properties Step 6: Select the back arrow Step 7: Select the Formulas tab Step 8: In the Formula Auditing group, select Show Formulas

Remove all personal information from the workbook. Then, run an accessibility check on the workbook.

Step 1: Select the File tab Step 2: Select Info Step 3: Select the Check for Issues dropdown arrow Step 4: Select Inspect Document Step 5: Select Inspect Step 6: In the Document Properties and Personal Information section, select Remove All Step 7: Select Close Step 8: Select the Check for Issues dropdown arrow Step 9: Select Check Accessibility

Import the 111-teachers.txt file, accepting all the default values while ensuring the first row of data is represented as a data header.

Step 1: Select the File tab Step 2: Select Open Step 3: Select Browse Step 4: Browse to the GMetrixTemplates folder Step 5: Select the All Excel Files dropdown arrow Step 6: Select All Files Step 7: Select 111- teachers.txt Step 8: Select Open Step 9: Select the My data has headers checkbox Step 10: Select Next Step 11: Select Next Step 12: Select Finish

On the current worksheet, use the Go To feature to select the February_All range.

Step 1: Select the Find & Select dropdown arrow Step 2: Select Go To Step 3: Select Feburary_All Step 4: Select the OK button or select the Enter key on the keyboard

Add the following elements to the current worksheet: the file name to the center of the header and the words, Winter 2022, to the left side of the footer. Display these elements after you have added them.

Step 1: Select the Insert tab Step 2: Select the Text dropdown arrow Step 3: Select Header & Footer Step 4: In the Header & Footer Elements group, select File Name Step 5: Scroll to the bottom of the page and hover over Footer. Step 6: Select the left side of the Footer Step 7: Type: Winter 2022 Step 8: Select any visible cell on the page

In cell D3 on the Instructors worksheet, add a function to combine Amy, a space, and Jones, using the cells containing Amy and Jones.

Step 1: Select the Instructors worksheet Step 2: Select cell D3 Step 3: Select the Formulas tab Step 4: In the Function Library group, select the Text dropdown arrow Step 5: Select CONCAT Step 6: Select cell A3 Step 7: Activate the cursor in the Text2 field Step 8: Select the Spacebar key on the keyboard Step 9: Activate the cursor in the Text3 field Step 10: Select cell B3 Step 11: Select the OK button

Using the formula builder, in cell F3 on the Instructors worksheet, convert the data in cell A3 to all uppercase letters. Then, in cell H3, type out the formula used to convert the data in cell A3 to all lowercase letters and display the result.

Step 1: Select the Instructors worksheet Step 2: Select cell F3 Step 3: Select the Formulas tab Step 4: In the Function Library group, select the Text dropdown arrow Step 5: Select UPPER Step 6: Select cell A3 Step 7: Select the OK button Step 8: Select cell H3 Step 9: Type: =LOWER(A3) Step 10: Select the Enter key on the keyboard

Change the margins on the current worksheet to Wide and the orientation to Landscape. Then, center the content of the worksheet horizontally.

Step 1: Select the Page Layout tab Step 2: In the Page Setup group, select the Margins dropdown arrow Step 3: Select Wide Step 4: In the Page Setup group, select the Orientation dropdown arrow Step 5: Select Landscape Step 6: Select the Page Setup dialog box launcher Step 7: Select the Margins tab Step 8: Select the Horizortally checkbox Step 9: Select the OK button or select the Enter key on the keyboard

On the current worksheet, repeat the first two rows at the top of each page. Then, set up to print only the selected data. Do not print the file.

Step 1: Select the Page Layout tab Step 2: In the Page Setup group, select Print Titles Step 3: Activate the cursor in the Rows to repeat at top field Step 4: Select rows 1 and 2 Step 5: Select the OK button Step 6: Select the File tab Step 7: Select Print Step 8: Select the Print Active Sheets dropdown arrow Step 9: Select Print Selection

Make every other column shaded instead of every other row in the current table. Emphasize the first column and remove the filter buttons. Then, display the Totals row for the table and ensure that the Freshman column total is a sum of the Freshman fundraising amounts.

Step 1: Select any cell in the range of cells A2 through E11 Step 2: Select the Table Design contextual tab Step 3: In the Table Style Options group, clear the Banded Rows checkbox Step 4: In the Table Style Options group, select the Banded Columns checkbox Step 5: In the Table Styles Options group, select the First Column checkbox Step 6: In the Table Style Options group, clear the Filter Button checkbox Step 7: In the Table Style Options group, select the Total Row checkbox Step 8: Select cell B12 Step 9: Select the Totals dropdown arrow Step 10: Select Sum

Display the filter buttons in the current table. Then, filter the table to only show subdivisions where the Senior class raised at least $1,000.

Step 1: Select any cell in the range of cells A2 through E11 Step 2: Select the Table Design contextual tab Step 3: In the Table Style Options group, select the Filter Button checkbox Step 4: Select the dropdown arrow to the right of the Senior field Step 5: Select Number Filters Step 6: Select Greater Than Or Equal To Step 7: Type: 1000 Step 8: Select the OK button

Sort the information in the current table, first by Territory and then by Subdivision.

Step 1: Select any cell in the range of cells A2 through F11 Step 2: In the Editing group on the Home tab, select the Sort & Filter dropdown arrow Step 3: Select Custom Sort Step 4: Select the Sort by dropdown arrow Step 5: Select Territory Step 6: Select Add Level Step 7: Select the Then by dropdown arrow Step 8: Select Subdivision Step 9: Select the OK button

Using the Format Cells dialog box, center cell A3 vertically, with the text set to Arial, bold, size 14.

Step 1: Select cell A3 Step 2: On the Home tab, Select the Font Settings dialog box launcher Step 3: Select the Alignment tab Step 4: Select the Vertical dropdown arrow Step 5: Select Center Step 6 Select the Font tab Step 7: In the Font area, select Arial Step 8: In the Font style area, select Bold Step 9: In the Size area, select 14 Step 10: Select the OK button

In cell B8 on the current worksheet, add a formula that will total the three budget items in cells B4 through B6. Then, in cell B9, type the formula that will multiply the total by the tax rate in cell Q2 and ensure that when this formula is copied using AutoFill, the tax rate cell is always Q2.

Step 1: Select cell B8 Step 2: Type: = Sten 3: Selec : cell B4 Step 4: Type: + Sten 5• Select cell BE Step 6: Type: + Sten /• Selec cplbb Step 8: Select the Enter key on the keyboard Step 9: Select cell b: Step 10: Type: =B8*SQ$2 Step 11: Select the Enter key on the keyboard

Use AutoFill to fill the months for March through December. Then, add a blank column between columns A and B and delete row 2 from the worksheet.

Step 1: Select cell D3 Step 2: Drag the fill handle to cell M3 Step 3: Select column B Step 4: In the Cells group on the Home tab, select Insert Step 5: Select row 2 Step 6: In the Cells group, select Delete

On the Inventory worksheet, use the ribbon to extract the two leftmost characters from cell B3 into cell D3. Then, in cell F3, type the formula needed to extract the rightmost two characters from cell B3 and display the results.

Step 1: Select cell D3 Step 2: Select the Formulas tab Step 3: In the Function Library group, select the Text dropdown arrow Step 4: Select LEFT Step 5: Select cell B3 Step 6: Activate the cursor in the Num chars field Step 7: Type: 2 Step 8: Select the OK button Step 9: Select cell F3 Step 10: Type: =RIGHT(B3,2) Step 11: Select the Enter key on the keyboard

In cell F4 on the current worksheet, add a function that will sort the January activities in ascending order. Then, starting in cell G4, list each activity from the January activities without duplicating any activity.

Step 1: Select cell F4 Step 2: Select the Formulas tab Step 3: In the Function Library group, select the Lookup & Reference dropdown arrow Step 4: Select SORT Step 5: Select cells A4 through A18 Step 6: Select the OK button Step 7: Select cell G4 Step 8: In the Function Library group, select the Lookup & Reference dropdown arrow Step 9: Select UNIQUE Step 10: Select cells A4 through A18 Step 11: Select the OK button

Insert a Win/Loss Sparkline in cell F5 to represent the data range in cells B5 through E5. Then, change the sparkline to a Column sparkline and make the high point on the sparkline the Orange, Accent 2 color.

Step 1: Select cell F5 Step 2: Select the Insert tab Step 3: In the Sparklines group, select Win/Loss Step 4: Select cells B5 through E5 Step 5: Select the OK button Step 6: In the Type group on the Sparklines tab, select Column Step 7 In the Style group, select the Marker Color dropdown frrow Step 8: Select High Point Step 9: Select Orange, Accent 2

On the current worksheet, add a link to http://www.weather.com to cell G1 and remove the link in cell H1.

Step 1: Select cell G1 Step 2: Select the Insert tab Step 3: In the Links group, select Link Step 4: Type: http://www.weather.com Step 5: Select the OK button or select the Enter key on the keyboard Step 6: Select and hold (or right-click) cell H1 Step 7: Select Remove Hyperlink

In cell O4 type the function that will add up the total scrores in cells B4 through M4. Then, in cell R4, use the Function Library to obtain and add the function that will display the highest score in cells B4 through M4.

Step 1: Select cell O4 Step 2: Type: =sum(B4:M4) Step 3: Select the Enter key on the keyboard Step 4: Select cell R4 Step 5: Select the formulas tab Step 6: In the Function Library group, select the AutoSum dropdown arrow. Step 7: Select Max Step 8: Select cells B4 through M4 Step 9: Select the enter key on the keyboard

Create a named range named Tax_Rate, using cell P2 as the reference point. Then, use named ranges to navigate to the range named Future Amounts. Finally, use the Go To feature to navigate to the Tax Rate named range.

Step 1: Select cell P2 Step 2: Activate the cursor in the Name field Step 3: Type: Tax Rate Step 4: Select the Enter key on the keyboard Step 5: Select the Name dropdown arrow Step 6: Select Future Amounts Step 7: In the Editing group, select the Find & Select dropdown arrow Step 8: Select Go To Step 9: Select Tax_Rate Step 10: Select the OK button

In cell Q8 on the current worksheet, use the ribbon to multiply the named ranges future_budget and Tax_Rate

Step 1: Select cell Q8 Step 2: Type: = Step 3: Select the Formulas tab Step 4: In the Defined Names group, select the Use in Formula dropdown arrow Step 5: Select future budget Step 6: type * Step 7: In the Defined Names group, select the Use in Formula dropdown arrow Step 8: Select Tay Rate Step y: Select the Enter key on the keyboard

Display the Page Break Preview. Move the existing page break to the line above the 2nd period class heading. Then, display the Page Layout view and add the page number to the right side of the header. Return to Normal view when done.

Step 1: Select the View tab Step 2: In the Workbook Views group, select Page Break Preview Step 3: Drag the page break to the row separator between rows 18 and 19 Step 4: In the Workbook Views group, select Page Layout Step 5: Select the right side of the header Step 6: Select the Header &Footer tab Step 7IIn the Header &Footer Elements group, select Page Number Step 8: Select any cell Step 9: Select the view tab Step 10: in the Workbook Views group, select normal

Change the column chart on the current worksheet to show the data by grade instead of by month. Then add a linear trendline to this chart for the Senior series.

Step 1: Select the column chart on the current worksheet Step 2: Select the Chart Design contextual tab Step 3: In the Data group, select Switch Row/Column Step 4: In the Chart Layouts group, select the Add Chart Element dropdown arrow Step 5: Select Trendline Step 6: Select Linear Step 7: Select Senior Step 8: Select the OK button

Apply the Style 2 style to the pie chart. Then, add the following alternative text to the chart: Pie chart showing money raised by month. Close the Alt Text pane when complete.

Step 1: Select the pie chart on the current worksheet Step 2: Select the Chart Design contextual tab Step 3: In the Chart Styles group, select the Style 2 style Step 4: Select the Format contextual tab Step 5: In the Accessibility group, select Alt Text Step 6: Activate the cursor in the Alt Text field Step 7: Type: Pie chart showing money raised by month Step 8: In the Accessibility group, select Alt Text or select the X in the upper-right corner of the Alt Text pane

Add the June series to the pie chart on the current worksheet.

Step 1: Select the pie chart on the current worksheet Step 2: Select the Chart Design contextual tab Step 3: In the Data group, select Select Data Step 4: In the Chart data range, place the cursor after the first F Step 5: Select the Backspace key on the keyboard Step 6: Type: G Step 7: In the Chart data range, place the cursor after the second F Step 8: Select the Backspace key on the keyboard Step 9: Type: G Step 10: Select the OK button

Change the pie chart on the current worksheet to a 3-D pie chart, apply Layout 4 to the chart, and then change the Y rotation on the chart to 50 degrees.

Step 1: Select the pie chart on the current worksheet Step 2: Select the Chart Design contextual tab Step 3: In the Type group, select the Change Chart Type button Step 4: Select 3-D Pie Step 5: Select the OK button Step 6: In the Chart Layouts group, select the Quick Layout dropdown arrow Step 7: Select Layout 4 Step 8: Select and hold (or right-click) the chart Step 9: Select 3-D Rotation Step 10: Activate the cursor in the Y Rotation field Step 11: Type: 50 Step 12: Select the Enter key on the keyboard