ALL IT Technology Information

Excel Data cliening






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  






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




  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.

  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.

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