Working with Data: Test your skills >> Excel Skills For Business: Essential
1. You want to insert 3 columns. Which of the options below will help you to achieve that?
Multiple answers are possible. Partial credit will be awarded.
- You select a column then click the Insert button on the Home tab 3 times
- You select 3 columns, right-click and go to Insert
- You right-click on the column header and go to Insert, then type the number 3
2. Sean has carefully set up some complicated Conditional Formatting to one of the columns in his worksheet. He now wants to apply the same rules to another column in his worksheet. What is the quickest way for him to do this?
- There is only one way, he needs to select the next column and then set up the Conditional Formatting rules again.
- He can use the Format Painter.
- He can copy and paste the original column and then delete the content of the cells using the Clear tool.
3. Open the attached workbook. You will notice that some columns have been hidden. Unhide the columns. What is the Product Name for order 5023-1?
W04-Assessment.xlsx
Technology
4. Order 5024-1 was cancelled. Delete row 11 (not just the data). What is the updated average in cell S1? (Please put the number only, no currency symbols)
1156.53
5. You need to find out about the most recent shipment. Sort the data by Ship Date. What was the Order Quantity for the most recent item?
31
6. You need to check the most recent furniture item. Sort the data by Product Category (A-Z) and then Order Date (Newest to Oldest). What is the Customer Type for the most recently ordered Furniture Item?
- Corporate
- Small Business
- Home Office
- Consumer
7. You have been asked to find out about orders placed by the customer Cindy Chapman. Filter the data to show only her orders. How many were High Priority?
6
8. Clear the previous filter. Use filters to find out how many orders were for Small Business customers, where the Account Manager is Natasha Song, with a High priority.
5
9. Clear the previous filters. Use filters to find out how many orders were for Office Supplies and had a Total over $7,000.
5
10. Clear the previous filters. Apply a new filter to show the top 1% of orders by Total. What is the Average Quantity for the top 1% (cell M1043).
46
11. Use the find tool to find order number 6044-1. What is the Priority for this order?
Medium
12. Tina Carlton has recently married and has chosen to take her partner’s name. Start by clicking in cell A1. Use Find and Replace to replace all instances of Tina Carlton with Tina Shaw. Check the message — how many replacements were made?
On a Mac, go to the Edit menu > Find > Replace.
137
