This is the first in our series of challenges to put your Excel skills to the test. 

This week, we have a series of tasks to cleanse raw data and turn it into a formatted table. If you get stuck, just scroll below to reveal step-by-step instructions with helpful tips.

Download the raw data here, and complete the tasks below.

Tasks

1. Using the initial raw data, cleanse the Rating Column wherever there is 'NaN' to 'N/A'. Then, remove the '+' at the end of each Installs value. Finally, format the data in the Last Updated column in DD-MMM-YY (for example 01-Jan-21).

2. Format the table so it is in a Blue, Table Style Medium 2 headed format and set the Table name to 'App_Data_New'.

3. Sort the data so the Top Rated Apps are shown in descending order and exclude any Ratings which are 'N/A'.

The end result should look like the table below:

Stuck on the tasks?

+ Reveal Step-By-Step Instructions

 

STEP 1

Highlight column C or select the data in the Ratings column and then choose the "Replace" formatting (found in Excel -> Home -> Editing -> Find (drop down) -> Replace). In the Find box input 'NaN' and in the Replace box input 'N/A'. (💡 The shortcut for Find & Replace in Excel is CTRL + H)

 

STEP 2

Highlight column E or select the data in the Installs column. Find "+" and Replace with a blank entry.

 

STEP 3
Highlight column I or select the data series in the "Last Updated" column and go to Format Cells (Excel -> Home -> Cells -> Format -> Format Cells). In the Number tab select Custom in the format series and then choose the option DD-MMM-YY from the list of choices or type it into the "Type:" box.

 

STEP 4
Highlight the whole table data set and select "Format as Table" (Excel -> Home -> Styles -> Format as Table). When this occurs, a Pop-up will show and ask if you want to set the data set to a Table. Click OK, and then you go into Table Format options. From the Format options look in the Medium section and on the first line will be a Blue 2 headed format. Select this to set the table in the format shown.

 

STEP 5

To set the Table Name (important if you ever want to link the table to another dataset or Macro) go into "Table Design" and in that section will be a "Table Name:" section at the front. Rename the Table to App_Data_New.

 

STEP 6

Use the filter option in Column C and select "Sort - Largest to Smallest" or Descending dependent on the windows version you have. Then, unselect the N/A values in order to filter them out from the dataset.

Sharpen your skills with our range of Excel courses designed to turn you from a table trainee to spreadsheet specialist here >