MO-210 Practice Questions

To ensure every other row is shaded and that this formatting updates automatically when new rows are added, you should use theBanded Rowsfeature within theTable Designoptions.

Since your data is already formatted as a Table (indicated by the filter arrows in the header row), follow these steps:

Step-by-Step Solution

Select the Table: Click anywhere inside the data on theSoldworksheet (for example, cellB5). This will make the contextual "Table Design" tab appear in the Ribbon.

Go to Table Design: Click on theTable Designtab at the top of the Excel window.

Enable Banded Rows: In theTable Style Optionsgroup, check the box labeledBanded Rows.

Why this works:

Automatic Updating: Because this is a Table feature rather than manual cell shading, if you insert a new row between existing data, Excel will automatically re-calculate the "every other row" pattern to maintain the shading.

Consistency: This method ensures that even if you sort or filter the data, the alternating stripes remain consistent visually.

Verification

Look at your table; you should see that rows 4, 6, 8, etc., have a different background color than rows 5, 7, 9. Try right-clicking a row and selectingInsertto see how the shading shifts automatically to maintain the pattern.

To completeTask 5, you will create a 3-D Clustered Column chart on theAverage Call Timeworksheet to visualize the weekly performance of each salesperson.

Step 1: Navigate to the Worksheet

Click on theAverage Call Timetab at the bottom of your Excel window to switch views.

Step 2: Select the Data Range

You need to select the names and the call times for all five days (Monday through Friday), butexcludethe "Trend" column:

Click and drag to select cellsA3 to F14.

Note:This includes the header row (Salesperson, Mon, Tue, etc.) and all employee rows down to Michael.

Step 3: Insert the 3-D Clustered Column Chart

Go to theInserttab on the Ribbon.

In theChartsgroup, click theInsert Column or Bar Charticon (it looks like small vertical bars).

Under the3-D Columnsection, select the first option:3-D Clustered Column.

Step 4: Add the Chart Title

Click once on the default text that says "Chart Title" at the top of the new chart.

TypeAverage Call Timeand pressEnter(or click outside the text box).

Step 5: Position the Chart

Hover your mouse over a blank area of the chart (the chart area).

Click and drag the chart so it is positionedbelow the table.

Placement Tip:Usually, starting the chart around row 17 or 18 is ideal so it doesn't overlap your data.

To filter theProfitsworksheet to display only the flavors with aTotal Profitthat isAbove Average, follow these steps:

Step 1: Access the Profits Worksheet

Click on theProfitsworksheet tab at the bottom of the Excel window.

Step 2: Apply the Above Average Filter

Look at theTotal Profitheader (CellG3). If you don't see a small drop-down arrow next to it, go to theDatatab and clickFilter.

Click theFilter Drop-down Arrowin theTotal Profitheader cell.

Hover your mouse overNumber Filtersin the menu that appears.

From the side menu, selectAbove Average.

Step 3: Verify the Results

Excel will automatically calculate the average of all values in the "Total Profit" column and hide any rows that fall below that number.

You will notice the row numbers on the left are now blue, indicating a filter is active.

To achieve the look where the dollar symbol is left-aligned and the decimal points are perfectly aligned vertically, you need to apply theAccountingnumber format. This is a standard requirement for professional financial statements.

Step 1: Select the Data Range

Ensure you are on theQtr 1worksheet.

Using your mouse, click and drag to select the range fromB10 to E14. This range covers all the monthly revenue data for the various locations.

Step 2: Open the Number Format Options

You can do this quickly using the Ribbon or with more precision via the Format Cells dialog:

Go to theHometab on the Ribbon.

In theNumbergroup, click the smallDialog Box Launcher(the tiny arrow in the bottom-right corner of the group). Alternatively, you can pressCtrl + 1on your keyboard.

Step 3: Configure Accounting Format

Once theFormat Cellsdialog box appears:

SelectAccountingfrom theCategorylist on the left.

Set theDecimal placesto2.

In theSymboldropdown menu, ensure$ English (United States)is selected.

ClickOK.

Key Difference: Accounting vs. Currency

Accounting Format:Aligns the$ symbol to the far leftof the cell and thedecimal points verticallyin a column, making it easier to read large lists of numbers.

Currency Format:Places the$ symbol immediately to the leftof the number, which does not allow for uniform vertical alignment of the symbols.

Step-by-Step Instructions

Navigate to the Correct Worksheet:

Click on the tab labeled Top 20 Videos at the bottom of the Excel window to ensure you are on the correct sheet.

Select Column C:

Click on the letter C in the column header to select the entire column.

Open Column Width Settings:

Method A (Ribbon): Go to the Home tab. In the Cells group (towards the right), click Format. Select Column Width... from the dropdown menu.

Method B (Right-click): Right-click directly on the column header "C". A context menu will appear. Click Column Width...

Enter the Width:

A small dialog box labeled "Column Width" will appear.

Type 2 in the box.

Click OK.

Visual Verification

Before: Column C (containing the "Year" data) is likely wider, fitting the four-digit years comfortably.

After: Column C will become very narrow. The "Year" header and the dates (like 2011, 1984) might display as hashes (####) because the column is now too narrow to display the numbers, or the text will simply be cut off. This is expected behavior for the exam task; do not widen it back out unless instructed to do so.

Here are the step-by-step instructions to add theOpencommand to your Quick Access Toolbar in Excel 365:

Step-by-Step Solution

Look at the very top-left corner of your Excel window (above theFileandHometabs). This area is theQuick Access Toolbar.

Click the smalldownward-pointing arrow(Customize Quick Access Toolbar) located at the right end of the toolbar. 3. A drop-down menu will appear with a list of common commands.

Find and click onOpenin the list.

A checkmark will appear next to "Open," and theOpen icon(a yellow folder) will now permanently appear in your Quick Access Toolbar for easy access.

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.

To move the pie chart to its own dedicated chart sheet without using the "New Sheet" button, follow these steps:

Step 1: Select the Pie Chart

Click anywhere on the Pie Chart located on the Qtr 2 worksheet. This action will activate the contextual chart tabs on the Ribbon.

Step 2: Open the Move Chart Dialog

Once the chart is selected, look at the top of your Excel window:

Go to the Chart Design tab (this tab only appears when a chart is selected).

On the far right of the Ribbon, in the Location group, click the Move Chart button.

Step 3: Configure the New Chart Sheet

A Move Chart dialog box will appear. To move the chart to its own sheet:

Select the New sheet: radio button.

In the text box next to "New sheet:", delete the default text and type Qtr 2 Chart.

Click OK.

Result

Excel will create a new sheet tab at the bottom of your workbook named Qtr 2 Chart that contains only the pie chart, scaled to fill the entire page.

To completeTask 2, you need to enable theTotal Rowfeature for the table on theEmployee Hoursworksheet. This is the most efficient way to automatically calculate sums in an Excel table.

Step 1: Select the Worksheet

Click on theEmployee Hourstab at the bottom of your Excel window to ensure you are on the correct sheet.

Step 2: Activate the Table

Click anywhere inside the data table (for example, click on cellA4where it says "emilio"). Once you click inside the table, a new tab calledTable Designwill appear at the very top of your Ribbon.

Step 3: Enable the Total Row

Go to theTable Designtab on the Ribbon.

In theTable Style Optionsgroup, check the box labeledTotal Row.

Result:A new row labeled "Total" will immediately appear at the bottom of your data (Row 15).

Step 4: Configure the Total Hours Calculation

By default, Excel might place a count or sum in the last column. You need to ensure theTotal Hourscolumn (Column J) is calculating correctly:

Click on the cell at the bottom of theTotal Hourscolumn (J15).

A smalldrop-down arrowwill appear next to the cell. Click it.

SelectSumfrom the list.

Final Result

Your table will now have a dedicated row at the bottom that automatically updates the total hours whenever you add or change employee data.

To completeTask 3: Remove the hyperlink functionality from cell C27 on the Feed Inventory worksheet, follow these specific steps to ensure the text remains while the link is deleted.

Step 1: Select the "Feed Inventory" Worksheet

Ensure you are on the correct sheet by clicking theFeed Inventorytab at the bottom of the window.

Step 2: Navigate to Cell C27

Locate cellC27in the Description column. Based on your project files, this is likely a URL or a linked piece of text at the bottom of the table.

Step 3: Remove the Hyperlink

You want to strip the "clickable" link without deleting the actual words inside the cell.

Right-clickdirectly on cellC27.

From the context menu that appears, selectRemove Hyperlink(it is usually located at the very bottom of the list).

Step 4: Verify the Text Remains

The text should change from blue/underlined back to standard black text, but the characters themselves will stay in the cell.