Skip to content

GS Challenge 8 – Data Cleaning

Can you believe we’ve done eight of these so far? Well, no reason to stop now!

Welcome to your 8th Spreadsheet Life Google Sheets challenge!

In this article, you will get four questions from easy to hard to test your skills in cleaning data in Google Sheets. You can download the spreadsheet by clicking on the link below.

Google Sheets Challenge 8 Spreadsheet

That way, you can try your hand at each question as you move through this article. That said, let’s jump in!

Question 1

For question number one, your task is to remove all empty rows from the dataset.

Question 1 – Solution

To solve question one, start by selecting all the data in the range B4:G38. Once you’ve selected the data, go to Data Create a Filter.

 

Next, click on any of the filter buttons, click on Clear, select Blanks, and then click Ok. This action will hide all the rows with data.

With all the data hidden, select the blank rows, right-click, and delete the selected rows.

You can then go to Data Remove Filter.

 

All of the empty rows are now gone.

Question 2

For your second question, your task is to remove any duplicate records from the dataset.

Question 2 – Solution

To solve question two, select cell B4 and press CTRL+A to select all the data. With the data selected, go to Data Data Cleanup > Remove Duplicates.

In the window that appears, check the box next to “Data has header row.” Then click the Remove Duplicates button.

Google Sheets removes the duplicates and lets you know how many.

Question 3

For question three, your task is to right-align and adjust the number formatting of the start date and salary fields.

Question 3 – Solution

To complete question three, start by selecting columns F and G. Then use the toolbar to change the horizontal alignment to right.

Next, select all the start dates in F5:F25. Then click the More Formats button in the toolbar, and change the number format to Date.

After that, select all the salary numbers in G5:G25. Then click the Format as currency button in the toolbar, and decrease the decimal places by two.

Question 4

For question number four, your goal is to split the work location field into three fields of data. (City, State, and Country.)

Question 4 – Solution

To begin solving question four, start by selecting columns D and E. Then right-click and insert two columns to the right. These new columns will contain the new data fields.

Next, go ahead and change cell E4 to City. Also, enter State into cell F4, and type Country into cell G4.

After that, select the range E5:E25, and go to Data Split text to columns.

Google Sheets will pick the delimiter to split the data automatically and place the data into the respective cells.

You can then decrease the column size of EF, and G to complete question number four.

How Did You Do?

How did it go? Leave me a comment below to let me know how you did.

Were all of these questions pretty easy, or were some tasks more difficult than others? I would love to get your feedback!

Also, if you missed the first few Google Sheets challenges, you can find them listed below.

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top