Introduction
Excel is spreadsheet software tool that is wildly and commonly used to organize, format, calculate and analyze data in row and columns. It is widespread due to it's user friendly interface and powerful inbuild functions and features that makes it easy for beginners and professional alike to do day-to-day data related tasks. This article takes you through a step by step guidelines on how to use and navigate excel from basic navigation to practical data cleaning methods that are vital for data analytics.
Excel Workbook
1) Workbook and Worksheets
An excel workbook is the main file that can contain one or many worksheets. A worksheet is the single grid that contains multiple rows and columns. The worksheets are mainly found on the bottom left of the workbook. The below image indicates the different worksheets and a worksheet can be added by clicking the +
2) Cell
A cell is an intersection of arow and column.
3) Ribbon
A ribbon is a toolbar at the top part of a worksheet that has buttons and icon. It organizes tools into groups for quick formatting and data management.
Data Cleaning
Data cleaning is the process of finding and fixing errors so that the data can be easy to work on for accurate information and analysis. Before starting on data cleaning i copied the original worksheet to another worksheet and renamed it to clean and the clean worksheet is where i will be working on cleaning the data.
1) Test Functions
Upper - Changes text characters to Uppercase
Lower - Changes text characters to lowercase
Trim - Removes spaces in characters before and after and also in between
Concat - Can be used to join characters of two cells like using the first name and last name to create an email address
2) Find and Replace
Find and replace is the initial step in data cleaning the dataset by merging similar data that were enter incorrectly such human resource and HR which basically mean the same thing but were not keyed to the dataset as one. Find and replace tool will be used to merge them into one.
3) Conditional Formatting
Conditional formatting is a tool that changes the appearence of cells, it let me use colors or databars to highlight high values or errors without having to look at rows or columns manually.
4) Sorting and Filtering
The first step to data cleaning is sort each column from the largest to smallest for numeric columns and from A to Z for text columns to be able to find cells that have wrong date keyed in them. As highlighted below the age column has thirty keyed in as a text instead of number.
To change it from text written 'thirty' and correcting it into a number format
5) Removing Duplicates.
The second part is to remove duplicates which tends to distort data. On the ribbon the data part has the tool for removing duplicate and it displays a dialog box which then i selected the primary column which is unique to make removing duplicates effective.
6) Blank and Missing values
On the columns that have blanks i changed them to '0' on the numeric columns and on the text columns i changed them to 'Unknown'. This is done to reduce inaccuracies.
7) Aggregate Functions
Power () - Raises the a number to a specified power
Product() - Multiplies numbers
Min() - Finds the smallest number in a row or column
Sum - Adds a range of numbers
Max() - Finds the largest number
Median() - Returns the middle number from a set of given numbers
Mode() -Returns the most occuring number in a set of numbers
Average() Adds a group of numbers together then divides the sum by count numbers
All the functions and formulas must start with an (=).
8) Statistical Functions
Count - Counts numerical cells only
Counta - Count the number of cells that are not blank
Countblank - Counts all the blanks in a columns or rows.
9)Conditional Aggregations
Countif - In the dataset I used countif to find the number of staff in IT department
Countifs - Used it to find the number of female staff that are above 30.
Sumif - Used it find out the total amount of salary for female staff.
Sumifs - Used it to calculate the total salary of male staff that are above 30
Averageif - Used it to calculate the average age of male staff.
Averageifs - Used to calculate the average age of male staff that are in the HR department
Conclusion
Excel serves as a vital software tool that is used in data analytics due to its reliability and easy to use features that makes complex tasks easy to manage. Data cleaning is the corner stone of analytics that determines whether can be trusted and used for making bold decisions that shape outcomes of a business.
Using the above techniques and guidelines assures you of more reliable way of data cleaning.
Top comments (0)