MO-210 Microsoft Excel (Microsoft 365 Apps) Questions and Answers

n Excel 365, the most efficient way to do this is using theAutoFillfeature or simply dragging the fill handle. Here is your step-by-step solution:

Step 1: Switch to the Correct Worksheet

Ensure you are on theAverage Call Timeworksheet. Click the red tab at the bottom of the screen to switch from the "Employee Hours" view (shown in your image) to the call time data.

Step 2: Identify the Sparkline

Locate the first cell in theTrendcolumn that already contains a Sparkline (likely cellF4or similar, depending on your layout).

Step 3: Use the Fill Handle to Copy

Hover your mouse over thebottom-right cornerof the cell containing the sparkline.

Your cursor will change from a thick white cross to athin black cross (+).

Click and dragthe fill handle down to the bottom of the column to fill all the vacant cells in the "Trend" column.

Pro Tip:Alternatively, you can simplydouble-clickthe thin black cross (fill handle), and Excel will automatically fill the sparklines down to the last row of your data.

Alternative Method: Using the Ribbon

If you prefer using the menu interface:

Select the cellwith the existing sparkline and all theempty cells below itwhere you want the sparklines to appear.

Go to theHometab.

In theEditinggroup, click theFillicon (downward arrow).

SelectDown.

To completeTask 3, you will use theIF functionto create a logical test that checks the total hours worked and returns a specific text result.

Step 1: Navigate to the Correct Cell

Ensure you are on theEmployee Hoursworksheet and click on cellH4(the first cell under theOvertimecolumn).

Step 2: Enter the IF Function

Type the following formula into cellH4:

=IF(J4>40, "Yes", "No")

Breakdown of the formula:

J4>40: This checks if the value in theTotal Hourscolumn is greater than 40.

"Yes": The value to display if the condition isTrue.

"No": The value to display if the condition isFalse.

Step 3: Copy the Function (H4:H16)

Since your data is formatted as an Excel Table, the formula might automatically fill down. If it doesn't, follow these steps to copy it to the remaining cells:

Click on cellH4to select it.

Hover your mouse over thebottom-right cornerof the cell until the cursor turns into athin black cross (+).

Click and dragthe handle down to cellH16.

Release the mouse button to apply the logic to all employees.

Final Check

Emilio (J4 = 38): Cell H4 should now displayNo.

Evan (J5 = 50): Cell H5 should now displayYes.

Step 1: Select the Data Range

Navigate to theTop 20 Videosworksheet.

Click on cellA4(this contains the rank "#1").

Hold your left mouse button down and drag diagonally to cellC8(this contains the year "2018").

Release the mouse button. The range from Rank #1 (Minecraft) to Rank #5 (Super Smash Brothers Ultimate) should be highlighted.

Step 2: Open the Cell Styles Gallery

Make sure you are on theHometab of the Ribbon (at the top of the window).

Look for theStylesgroup (usually near the middle-right of the ribbon).

Click theCell Stylesbutton (it may look like a dropdown arrow or a box showing style previews).

Step 3: Apply "60% - Accent 1"

In the gallery that opens, look under the "Themed Cell Styles" section.

Locate the first column of accents (usually blue or the primary theme color).

Find and click on60% - Accent 1.

Result:The background color of the top 5 rows in your table will change to a lighter shade (likely light blue or orange, depending on the workbook theme), matching the style requirement.

Task 1: Insert a new column before column A

To insert a new column at the very beginning of your worksheet, follow these steps:

Select Column A: Click directly on the letter'A'at the top of the first column to select the entire column.

Open the Insert Menu: There are two ways to do this:

Option A (Right-Click): Right-click anywhere on the selected column and chooseInsertfrom the context menu.

Option B (Ribbon): On theHometab, go to theCellsgroup, click the arrow next toInsert, and selectInsert Sheet Columns.

Verification

Once completed, your original "Item Type" column (previously Column A) will shift toColumn B, and you will have a brand new, emptyColumn Aat the start of your worksheet.

To keep rows 1 through 3 visible while you scroll down the "Sold" worksheet, you will use theFreeze Panesfeature.

Step-by-Step Solution

Select Row 4: Click on the row number4on the far left of the screen to select the entire row.

Note: In Excel, "Freeze Panes" freezes everythingabovethe currently selected row.

Navigate to the View Tab: Click on theViewtab in the top Ribbon menu.

Click Freeze Panes: In theWindowgroup, click theFreeze Panesbutton.

Select Freeze Panes: From the drop-down menu, select the first option,Freeze Panes.

Verification

Scroll down the worksheet using your mouse or the scroll bar. You should notice that the "Clearance Sale" header and the "Item Type/Description/etc." header row (rows 1-3) stay locked at the top of the screen while the data in row 4 and below moves.

To display the formulas within the cells of the Qtr 2 worksheet instead of the resulting values, you can use the Show Formulas command.

Step 1: Navigate to the Qtr 2 Worksheet

Open your Excel workbook.

Click on the Qtr 2 sheet tab at the bottom of the screen.

Step 2: Enable "Show Formulas" via the Ribbon

Go to the Formulas tab on the Ribbon at the top of the Excel window.

Locate the Formula Auditing group.

Click the Show Formulas button.

Alternative Method: Keyboard Shortcut

You can quickly toggle this view on and off by pressing:

Ctrl + ` (the accent mark or backtick key, usually located to the left of the "1" key).

What to Expect

Once activated, Excel will:

Expand column widths to accommodate the formula text.

Hide the calculated results and show the actual syntax (e.g., instead of seeing $411,912, you will see =SUM(B10:D10)).

here is the step-by-step solution for Task 1:

Step 1: Navigate to the Correct Worksheet

Click on theQtr 2worksheet tab at the bottom of the Excel window to ensure you are working on the correct data set.

Step 2: Insert the Column Sparkline

Click on cellF10(this is where the first sparkline will reside).

Go to theInserttab on the Ribbon.

In theSparklinesgroup, click onColumn.

A dialog box will appear. ForData Range, select the cells representingApr through Junfor the first row (likelyB10:D10).

Ensure theLocation Rangeis set to$F$10.

ClickOK.

Step 3: Apply the Specific Sparkline Style

With cellF10still selected, a newSparklinetab will appear at the top of your Ribbon.

Click on theSparklinetab.

Click theMorearrow (the small downward arrow at the bottom right) of theStylesgallery to see all options.

Hover over the styles to find the specific name:Gold, Sparkline Style Accent 2, Darker 25%.

Click to apply it.

Step 4: Copy the Sparkline to Remaining Cells

Select cellF10.

Hover your mouse over theFill Handle(the small green square in the bottom-right corner of the cell).

Click and drag the handle down to cellF14.

Excel will automatically generate sparklines for the other locations (Downtown, Eastside, etc.) using the same style and corresponding data rows.