Data: Kickstarter Campaigns Data
Create an interactive dashboard showcasing results of an analysis of Kickstarter Campaigns.
Concepts: CLEAN, TRIM, PROPER, UPPER, Filter, Paste Special Values
- Convert file format from CSV to XLSX to (1) preserve integrity of original data source and (2) ensure that all changes are saved.
- Format for easier viewing (i.e. freezing header row, center-aligning, adjust column widths, etc.).
- Delete unnecessary data columns and rename columns, as needed.
- Format data types accordingly.
- Clean and trim individual string data types (=CLEAN(TRIM(PROPER(A2))) or =CLEAN(TRIM(UPPER(E2))) ). Populate the rest of the rows using the fill handle.
- Copy the cleaned values over to the appropriate columns (Ctrl+Shift+Down Arrow, Ctrl+C, Ctrl+Home, Click on appropriate column, Right click, "Paste Special", "Values").
- Check for null values. Make judgment call, whether to keep or delete data with null values. If deleting, filter, select, delete sheet rows to avoid leaving blank rows in the worksheet.
Concepts: Lookup Functions (VLOOKUP, XLOOKUP), INDEX MATCH, Working with multiple excel files, mapping values, Pivot tables
- Using any of the lookup functions or INDEX MATCH, match the country code to the spelt out country name from another excel file ("Country_Code.xlsx").
- =VLOOKUP(E2,'[Country_Code.xlsx]Country Codes'!$A:$B,2,FALSE)
- =IFERROR(INDEX('[Country_Code.xlsx]Country Codes'!$B:$B,MATCH(E2,'[Country_Code.xlsx]Country Codes'!$A:$A,0)),"Unknown")
- =XLOOKUP(E2,'[Country_Code.xlsx]Country Codes'!$A:$A,'[Country_Code.xlsx]Country Codes'!$B:$B,"Unknown")
-
If you decided to keep the data with null values, there are two ways to deal with it: a) Update the country code file by adding a new entry, Column A = N,0", Column B = Unknown; b) Use IFERROR() formula. Tip: Nest the VLOOKUP function in the IFERROR function.
-
Rename column, copy and paste special values (to hardcode it and to stop it linking it to another file. This makes the file lighter and faster to work with.)
-
Create pivot tables and slicers, in accordance with how your dashboard is supposed to look like.
-
Build dashboard/summary report in a new worksheet.
Email: [may.lacdao@gmail.com]
Linkedin: Connect with me!