Google MSheet MExercise

Welcome, candidates
We recommend that you use a computer when taking this exam.

The data used on these sheets are for TESTING PURPOSES ONLY.
This spreadsheet contains three tabs that will be used to assess your ability to complete sample
Google Sheets / Excel tasks that we commonly perform here at Coalition Technologies.
Please enter your name in the cell below

Name
To complete this exercise in Google Sheets:

Go to File > Make a copy > Complete the test > Download as "Microsft Excel (.xlsx)" > Upload Ex
To complete this exercise in Microsoft Excel:
Go to File > Download as "Microsoft Excel (.xlsx)" > Complete the test > Upload Excel file into yo
Below is a basic description of each tab and its contents. More detailed instructions are located on ea
Worksheet 1
This worksheet requires the use of basic cell formatting, conditional formatting, and the use of COUN
Worksheet 2
This worksheet requires the use of PIVOT tables
Worksheet 3
This worksheet requires the use of VLOOKUP/INDEX, LIST, and conditional formatt
ility to complete sample
Technologies.
ft Excel (.xlsx)" > Upload Excel file into your skills test
st > Upload Excel file into your skills test
nstructions are located on each tab.
matting, and the use of COUNTIF for

Name Bike Type Day Date Time
Customer 3 Gravel bike Thursday 1/10/2030 2:45 PM
Customer 10 Gravel bike Wednesday 1/9/2030 1:30 PM
Customer 11 Gravel bike Thursday 1/17/2030 4:00 PM
Customer 1 Gravel bike Wednesday 1/2/2030 11:45 AM
Customer 4 Gravel bike Monday 1/14/2030 11:45 AM
Customer 2 Gravel bike Sunday 1/6/2030 4:45 PM
Customer 5 Mountain bike Friday 1/18/2030 4:45 PM
Customer 13 Mountain bike Thursday 1/3/2030 1:30 PM
Customer 15 Mountain bike Friday 1/11/2030 4:00 PM
Customer 14 Mountain bike Monday 1/7/2030 9:30 AM
Customer 16 Mountain bike Tuesday 1/15/2030 1:30 PM
Customer 12 Mountain bike Tuesday 1/1/2030 9:30 AM
Customer 7 Road bike Tuesday 1/8/2030 11:45 AM
Customer 6 Road bike Friday 1/4/2030 2:45 PM
Customer 19 Road bike Saturday 1/19/2030 9:30 AM
Customer 9 Road bike Wednesday 1/16/2030 2:45 PM
Customer 17 Road bike Saturday 1/5/2030 4:00 PM
Customer 8 Road bike Saturday 1/12/2030 4:45 PM
Customer 18 Road bike Sunday 1/13/2030 9:30 AM
Status Count
CONFIRMED 10
COMPLETED 9
Utilization Total 19
This is a datasheet for bike utilization monitoring.
INSTRUCTIONS: Format Row 1
1 Freeze and format the cell, font should be in Bold; fill cell A1 to H1 color should be blue.
Create a filter that will sort the Bike Types in column B.
INSTRUCTIONS: Set up conditional formatting
2 Set up conditional formatting so that the "Status" column (column F) shows:

Yellow for "CONFIRMED" and Green for "COMPLETED"
INSTRUCTIONS: Create a formula to fill in the missing data in cells D25 to D27
3 Use the COUNTIF formula in cells D25 & D26

Use SUM for the Utilization Total
Status Email Phone
COMPLETED sample@sample. (555)-555-5555
CONFIRMED sample@sample. (555)-555-5555
to H1 color should be blue.
lumn F) shows:
ta in cells D25 to D27

Project Category Bid Amount Won or Lost Potential Revenue
MKTG ONLY $103.00 Won $10,800.00
WEB ONLY $51.00 Lost $36,764
MKTG ONLY $77.00 Lost $26,460
MKTG + WEB $80.00 Won $19,251
WEB ONLY $100.00 Lost $14,908
WEB ONLY $73.00 Lost $14,908
MKTG ONLY $0.00 Won $21,600
MKTG + WEB $51.00 Won $34,375
MKTG + WEB $51.00 Won $38,348
WEB ONLY $208.00 Lost $16,748
INSTRUCTIONS: Build onto Pivot Table in cell A3.

Display the Project Category and Potential Revenue.
Show the winning Bid Amount per Project Category.
1
Table A
Project Category Bid Amount Won or Lost Budget Tier Potential Revenue
MKTG ONLY $103.00 Won $5,000 to $10,000 $10,800.00
WEB ONLY $51.00 Lost $15,000 to $20,000 $36,764.00
MKTG ONLY $77.00 Lost Above $20,000 $26,460.00
MKTG + WEB $80.00 Won $10,000 to $15,000 $19,251.00
WEB ONLY $100.00 Lost $15,000 to $20,000 $14,908.00
WEB ONLY $73.00 Lost $15,000 to $20,000 $14,908.00
MKTG ONLY $0.00 Won Below $5,000 $21,600.00
MKTG + WEB $51.00 Won $15,000 to $20,000 $34,375.00
MKTG + WEB $51.00 Won $5,000 to $10,000 $38,348.00
WEB ONLY $208.00 Lost Below $5,000 $16,748.00
This is a list of the marketing bids that we have placed for the services we offer.
MKTG ONLY - Marketing bids only
MKTG + WEB - Marketing and Web bids
WEB - Web bids only
al Revenue
Bid Amount Potential Revenue Status
$103.00 $10,800.00 Won
$51.00 $36,764.00 Lost
$77.00 $26,460.00 Lost
$80.00 $19,251.00 Won
$100.00 $14,908.00 Lost
$73.00 $14,908.00 Lost
$0.00 $21,600.00 Won
1
INSTRUCTIONS: Insert Data Validation - Dropdown list, in cell B3-B9
Create a "List of items" referencing the Bid Amount from Worksheet 2.
INSTRUCTIONS: Create a VLOOKUP formula to display Potential Revenue in cell C3
2 Using data from "Worksheet 2", create a formula in cell C3 that will display the Potential Revenue
according to the Bid Amount in cell B3.
INSTRUCTIONS: Create a VLOOKUP formula to display Status in cell D3
3 Using data from "Worksheet 2", create a formula in cell D3 that will display the Won/Lost Status
according to the Bid Amount in cell B3.
INSTRUCTIONS: Conditional Formatting
4 Use conditional formatting to fill the row with "Lost" status, in gray.
Formula = Apply to range columns B3 to D9
3-B9
l Revenue in cell C3
display the Potential Revenue
n cell D3
display the Won/Lost Status

Google MSheet MExercise

Uploaded by

Copyright:

Available Formats

Google MSheet MExercise

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Google MSheet MExercise

Uploaded by

Copyright:

Available Formats

Welcome, candidates

We recommend that you use a computer when taking this exam.

Please enter your name in the cell below

To complete this exercise in Google Sheets:

st > Upload Excel file into your skills test

nstructions are located on each tab.

matting, and the use of COUNTIF for

This is a datasheet for bike utilization monitoring.

INSTRUCTIONS: Format Row 1

2 Set up conditional formatting so that the "Status" column (column F) shows:

3 Use the COUNTIF formula in cells D25 & D26

to H1 color should be blue.

ta in cells D25 to D27

INSTRUCTIONS: Build onto Pivot Table in cell A3.

You might also like