Excel Data cliening

 

BECOME A GREAT DATA ANALYST

The PCSQT

To be a great data analyst, you need to be strong in the PCSQT skillset. These are,

•              • P for Data Preparation

•              • C for Number Crunching

•              • S for Storytelling

•              • Q for Asking Questions

•              • T for Technology

 

Data Preparation

Good data leads to great analysis. Unfortunately, in most business situations, the data quality is not great. The key data preparation skills you need are,

 

Knowledge of various data sources

o Automated data extraction

o Merging / combining data

o Data transformations (shape, size, or level)

o Cleaning up data

o Dealing with missing values, errors

 

 

my learn

1)    Data Sources

2)    Data cleaning

3)    Joins & mergen

4)    Transformations

 

1= Data Sources

ð  You need understand data , what is source and how to solve .

 

2= Data cleaning

 

#1 First name extraction

 

Show this photo in type first name to excel automatic detected to hit enter to all name automatic fil all first name

 

To same to same you use last name  fell all last name ctrl+ E

Go to Data > flash fill >

To autofill in data

 

 

 

2) Number to percentage

First blank box type percentage value type then , percentage value box copy then your list select and right click >paste special…     >>divide

3) Remove blank rows

               

All blank rows select and hit Ctrl + - to delete this row

Last select shift cells del & row delete you select

5)    Find common values in  2 list

Select firs list than press ctrl and select second list and go to

Select both list in duplicate name word select (color) ok

6)    Your data is like this photo you remove type rule color bold small large fount to use

 

Firs select table Go to Clear >clear Formats ok 

 

 

7)   To remove extra space

Use formula =TRIM(c6)

                                    Select name remove space

>remove any spaces from beginning end also from the extra spaces in the middle

 

 

 

8)   Unstack data

set next table your data stack to use input data in each box

 

Droop down and all value show then remove all black box space

Rules no 4 remove space    

 

Ok then your data clean table show

 

7) Remove duplicate combinations

Select you table data and go to Remove duplicates > and select your row name to remove duplacte combination ok

 

 

9)    Remove yellow cells

First find yellow cells  ctrl + F > go to format >select your color > ok

 

To Select all yellow color cells shift and select all value > ctrl + - > to Delete row and ok

  

10)  Extract number from t3xt

You type your value to extract to show automatic all data but you see include your data in text but you need only $ value extract you remove your spent vale to auto calculation and only show answered value you need ok 

 

11)  file name from full pathtype ctrl + H > replace word and type *\   and blank ok  

 

 

 

Hkh

 

Data preparation resources

•               o Power Query: This feature is available in Excel and Power BI. You can use it to connect to any data sources (text files, databases, cloud platforms, APIs etc.), fetch data, filter & transform it and apply data cleaning steps based on business rules. o How to use Power Query - Article & Video

•                

•               o SQL: Structured Query Language is a commonly used technology for data preparation. You can use SQL to get data from a single table or combine (join) data from multiple tables based on complex criteria. o How to use SQL – Tutorial 1 & Tutorial 2

•                

•               o Excel: Many times, you may end up with data in Excel spreadsheets that is not in ideal shape or format. Excel has many built-in features to quickly clean your data. o Excel data cleaning – Video & Flash fill

•