Microsoft MO-210 Dumps

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 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.

Step 1: Select the Table

Navigate to theRecent Releasesworksheet.

Clickanywhereinside the data table (for example, click on the text "A Little to the Left").

Note:You do not need to highlight the whole table; just clicking one cell inside it is enough to activate the hidden menu.

Step 2: Go to the "Table Design" Tab

Look at the Ribbon at the very top of the Excel window.

Because you clicked inside the table, a new tab calledTable Design(or sometimes justDesign) will appear on the far right side of the Ribbon.

Click on theTable Designtab.

Step 3: Click "Convert to Range"

In theTable Designtoolbar, look for theToolsgroup (usually on the left side).

Click the button labeledConvert to Range.

Visual Reference:

Step 4: Confirm the Action

A confirmation dialog box will pop up asking:"Do you want to convert the table to a normal range?"

ClickYes.

Result:The table functionality (like the little filter arrows next to the headers) will disappear, but the cell colors and data will remain exactly where they are. The data is now a standard "Range."

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.

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.

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.

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 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 5: Apply Style 5 to the chart on the Feed Inventory worksheet, follow these exact steps:

Step 1: Select the "Feed Inventory" Worksheet

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

Step 2: Select the Chart

Click anywhere on theTotal Inventorypie chart to select it. When selected, you will see a border around the chart and new tabs will appear on the Ribbon.

Step 3: Open Chart Design Tools

With the chart selected, look at the top Ribbon and click on theChart Designtab (this tab only appears when a chart is active).

Step 4: Apply Chart Style 5

In theChart Stylesgroup, you will see a gallery of different looks for your chart.

Hover your mouse over the styles to see their names, or click theMorearrow (the small downward arrow at the bottom right of the styles gallery) to see all options.

Locate and click onStyle 5.

Note:Style 5 typically features a specific layout of data labels and a distinct background or border style that matches the "Farm Fresh Feeds" theme.

Final Result

The pie chart will update its appearance to match Style 5, adjusting the placement of the percentage labels and the overall color shading to remain consistent with your report's professional look.

Project 1: Video Games - Task 5 Solution

Task:Insert a Footer that displays today's date on the right and then return to Normal view.

Step 1: Open the Header & Footer View

Go to theInserttab on the Ribbon.

Look for theTextgroup (usually near the right side).

Click on theHeader & Footerbutton.

Note:This will automatically switch your screen to "Page Layout" view, and you will see the header area at the top of the page.

Visual Reference:(Look for this icon in the Insert tab) [ Header & Footer ]

Step 2: Navigate to the Footer

You are currently in the Header. You need to go to theFooter.

On theHeader & Footertab (which appears in the Ribbon when you are in this mode), click the button labeledGo to Footer.

Alternative:You can simply scroll down to the bottom of the page until you see the footer boxes "Click to add footer".

Visual Reference:(Button in the Navigation group) [ Go to Footer ]

Step 3: Select the Right Section

The footer is divided into three boxes: Left, Center, and Right.

Click inside theRightbox.

Visual Reference:[ Left Box ] [ Center Box ] [ *Click Here (Right Box)* ]

Step 4: Insert the Current Date

With your cursor flashing in the right footer box, look at theHeader & Footertab in the Ribbon.

In theHeader & Footer Elementsgroup, click theCurrent Datebutton.

You will see the code &[Date] appear in the box. This is correct; it will turn into the actual date once you click away.

Visual Reference:(Icon looks like a small calendar) [ Current Date ] -> Displays as &[Date]

Step 5: Return to Normal View

Clickanywhere inside the worksheet cells(outside the footer area) to apply the change.

Go to theViewtab on the Ribbon.

In theWorkbook Viewsgroup (far left), click onNormal.

Visual Reference:(View Tab > Workbook Views) [ Normal ]

Result:You have successfully added the date to the footer. In "Normal" view, you won't see the footer anymore, but it is there and will appear when printed.