Trung tâm giáo dục 10 Education MOS, IC3, IELTS, TOEIC

MOCK TEST 1
Mỗi projects tương ứng với một file thực hành. Hãy mở đúng file thực hành để hoàn
thành các câu hỏi bên dưới.

PROJECT 1:

Task1: On the "Orders" worksheet, configure the table style options to automatically shade
every other table row.

Task 2: On the “Orders” worksheet, perform a multiple column sort on the data in the table
to order the records by "Country" with orders in United States coming first and orders from
Canada coming second. Then sort the orders alphabetically (from A to Z) by the "Customer"
field. Finally, sort the orders in ascending order by the "Order Date" field.

Task 3: On the “Orders” worksheet, enter a formula in cell J2 that uses an Excel function
to return the average order amount based on the values in the "Order Amount" column.

Task 4: On the "Order Amount Totals" worksheet, use conditional formatting to apply a
Yellow Fill with Dark Yellow text to the cells in the "Net Amount" column that contains
above average values.

Task 5: On the "Order Amount Totals" worksheet, use an Excel data tool to remove all
records with duplicate "Inv #" values from the table. Do not remove any other records.

PROJECT 2:

Task 1: In the “Profits” worksheet, add a new column to the left of column G (Total Profit)
with the title “Difference”. Configure the width of this column to automatically fit the
contents.

Task 2: On the “Profits” worksheet, adjust the height of row 30 to 90.

Task 3: Insert a new worksheet named "Expenses" worksheet between the Picture and
Profit worksheet.

Task 4: On the "Profits" worksheet, extend the formula of cell H3 to the end of the column
without changing fill color.

Task 5: Inspect and remove all personal information from this workbook.

1
 Trung tâm giáo dục 10 Education MOS, IC3, IELTS, TOEIC

PROJECT 3:

Task 1: On the "Data" worksheet, in the Color column, use the Find and Replace tool to
find all occurrences of “Gold” and replace with “Yellow" without changing the other
column.

Task 2: On the "Data" worksheet, create a named range from the cell ranges A1:F663 with
the name “Range_Data”.

Task 3: Add a row to the Table2 table that automatically calculates the total Sales. Use an
available feature of table. The result should be shown in cell F33.

Task 4: Rename the “Table2” table to “Sales_2017”.

Task 5: On the "Sales" worksheet, convert the "Report" table to range. Keep the formatting.

Task 6: Display the formulas in the "2016" worksheet.

PROJECT 4:

Task 1: Locate to the Retail table, then change the price of the first Item # to 1700.

Task 2: On the “Q1 Sales” worksheet, create hyperlink in the cycling man picture with the
follow: http://www.mountainbike.vn/ with a ScreenTip that displays “Mountain Bike
Website”.

Task 3: On the "Title" worksheet, insert a header that contains the filename on the left side
and sheet name on the right side. Then return to Normal view.

Task 4: In cell C4 of the "Type" worksheet, use a function to show the leftmost value in B4
in capitalize form.

Task 5: On the “Type” worksheet, insert a new column before column C with the title of
Code. Use a function to join the text from “Type” and “Style” column, separated by a space.
Fill the function to the entire column. Configure the width of this column to automatically
fit the contents.

PROJECT 5:

Task 1: On the "Input" worksheet, create a table with the headers based on the data range.
Change the style to Medium 3.

Task 2: On the "Input" worksheet, add a row to the table that automatically calculates the
total revenue. The result should be shown in cell E2002.

2
 Trung tâm giáo dục 10 Education MOS, IC3, IELTS, TOEIC

Task 3: On the "Summary" worksheet, modify the "Sales by month" chart so that Months
are displayed as Horizontal Axis labels and Sales Channel is the Legend series. Display
the values as data labels on the top of each data bar. Apply Style 8 for this chart.

Task 4: On the "Summary" worksheet, add legend to the right of the "Percentage" chart.

Task 5: Move the line chart on the "Summary” worksheet to a new chart sheet named
"Total Sales”.

PROJECT 6:

Task 1: On the "Vehicles" worksheet, enter a formula at I2 that uses an Excel function to
return the Price value of the individual order that has the highest price.

Task 2: On the "Vehicles" worksheet, set the cell range A2:F16 to print area.

Task 3: On the “Vehicles” worksheet, create a reference from cell ranges A3:A16 to
“LotNumber”.

Task 4: Expand the chart data range to include the Sum of Price values to the Item Type
chart of the Sold worksheet.

Task 5: On the “Vehicles” worksheet, create a 3D Clustered Column Chart to show the
Price for each Lot # of the Type of Automobile. Apply Layout 8 and Monochromatic
Pallete 2 to the chart. Place the chart to the right of the table.

PROJECT 7:

Task 1: On the "Region" worksheet, display data of Sales column to number with 3
decimals with the Euro symbol (€) right aligned.

Task 2: On the "Description" worksheet, format content in column "Indicator Name" so

Task 3: Create a new worksheet named "Sales Number" at the end of the workbook.

Task 4: Beginning at cell B2 in the "Sales Number" worksheet, import file Source.text
located at MOCK TEST 1 folder as tab delimited. Use the first row as the header and add
the import data to the data model. Accept all other settings.

Task 5: On sheet "Region", create a Pareto chart based on Sales and Region column. Add
the alt text description “New data” to the chart.

Task 6: Change the chart title in the above "Summary".

3
 Trung tâm giáo dục 10 Education MOS, IC3, IELTS, TOEIC

Task 7: On sheet “General”, insert a page break so that the value “AIM Item” in the
"Product" column on the first page.

Task 8: Attach the keywords (Tags) "Spending" to the workbook.

10 Education chúc các bạn ôn luyện tốt và đạt kết quả thật cao nhé!!!