IT

ALL IT Technology Information

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

                

 

































3 comments:

  1. Proud to work here as we are headed as a profitable and values-driven organization. Continuing the momentum of employee engagement around issues like sustainability, diversity, and inclusion, and making work meaningful excites me for the future.
    Would highly recommend it to a friend and whoever is looking to grow and learn about the upcoming technologies and discover new strategies to help in the company's growth and success.

    ReplyDelete
  2. Company has given fliexibility of work from home with 1 day per month as additional day off to take care of mental health.Infinite PTO policy is very helpful.

    ReplyDelete
  3. Good experience to work in indeed . Very professional and good security is promising.no salary issues i will recommend to join this company without any hesitation.

    ReplyDelete