ASSIGNMENT WEEK 2

Getting Started with Excel For Data Analysis

Introduction

Excel is one of the tools commonly used for working data .It is used to enter, organize ,clean, calculate and analyze information using spreadsheets .learning excel gives good foundation on how data is handled before other advanced tools such as python ,SQL and Power BI are used.
In data analysis ,raw data can have errors, duplicates, missing values or unorganized information.

Excel Basics

1. Dataset- It is a collection of related information organized into rows and columns .Example,

Employee ID	Name	Department	Age	Salary
1102	Marion	Finance	28	45000
1103	Peter	IT	30	70000
1104	June	HR	34	60000
1105	Irene	Finance	32	80000
1106	Dennis	IT	29	65000

Explanation
_column-is a verticle line of data that runs straight from top to bottom
_row-it is a horizontal line of data that runs from left to right.
_cell-it is an intersection of a row and column
_Header-The name of a column.
entering data into Excel
for the case of our dataset, it is entered as,
A1:Employees ID
B2:Name
C1:Department
D1:Age
E1:Salary
It is important to organize data rather than putting two different pieces of information in the same column. For example:
Employee ID
1102,Marion,finance
instead;
Employee ID Name Department
1102 Marion Finance
Hence the data will be easier to analyze.

Formatting in Excel

Formatting in excel involves,

• Bold headings

• Number of formatting

• Alignment

• Adjusting column width

• Dates

• Borders

• Percentages

• Currency
  formatting makes easy to interpret data but it doesn't clean the data.

  Formulas applied in Excel.

  A formula directs on how to perform a calculation .For example when you enter
  =60+85
  Excel calculates as 145
  To calculate average salary the formula is
  =AVERAGE(range criteria)
  =AVERAGE(D2:D5)
  To calculate the highest salary
  =MAX(D2:D5)
  To calculate the lowest salary
  =MIN(D1:D5)
  To count the number of employees
  =COUNTA(A1:A5)

  Sorting

  sorting is organizing data in a particular order.
  Example :Employees can be sort according to their departments,
  Names Departments
  Marion Finance
  Peter IT
  June HR
  Irene Finance
  Dennis IT
  This sorted data enables the analyst to quickly identify an employee which department the employees comes from.

  Filtering of data

  Filtering hides data that does not meet a particular condition.
  For example, since our dataset contains employees from different departments, we can filter the column to only show employees in IT departments ,filtering does not delete the other records .It only hides since it does not match the selected condition.

  Data cleaning

  It is the process of finding and correcting errors in a dataset.
  Common errors in a dataset involves,

• Missing values

• Duplicate values

• Inconsistent information
  In data cleaning ,excel functions are:
  UPPER
  =UPPER(A2) It helps changes the text to uppercase
  LOWER
  =lOWER it help changes the text to lowercase
  TRIM
  =TRIM(A2) It help removes unnecessary spaces from text.

  Preparation of data for analysis.

  It is the process of cleaning, organizing raw data to be accurately ready for analysist avoid misleading results.
  The analyst should ensure that the dataset is well structured, complete ,consistent ,accurate and identify missing values.

GitHub Repository

You can find my Excel data analysis project on GitHub:

[view my GitHub Repository