Getting Started with Excel for Data Analytics: From Basics to Data Cleaning.

Introduction

Excel is used in our day to day lives.
Excel is a microsoft spreadsheet used to store,organize and analyze data.
A row is horizontal that is from left to right while a column is vertical that is top to bottom.
A cell is an intersection of a row and a column.
Rows, columns and cells are contained in a worksheet.
One or more worksheets form a workbook

excel ribbon main components

• Tabs-this are the headers on top ie File,Insert,Page layout,Formulas,Data,Review and view In each tab there are subsections,dropdowns theres also a dialougue launcher which is a small arrow at the bottom right corner that opens advanced settings panels.

A sample excel worksheet interface.

Common terms we may hear a lot:

Formatting this is just changing the visual appearance of data in a spreadsheet to make it easier to manipulate data and understand.
Sorting data this is basically arranging the text entries from A to Z or numerical values eg sales, arranging them form largest to smallest
Formulas all formulas start with the = sign to tell excel that the following is a calculation not a text.
Functions this are operations prebuilt in excel thet make calculations easy eg SUM,AVERAGE,VLOOKUP
Cell Referencing this shows the cells highligted or containing data eg A1,B2:B20)

What is Data Cleaning?

This is the process of removing inacurate,duplicate informations,blanks and any errors that may appear inorder for data analysis and reporting to be clear,accurate,seamless and easy'
Why is cleaning data important because we may experience some of the common issues like:

• Duplicates-we basically get rid of duplicates by selecting all,clicking on the ribbon named data and removing duplicates or one could click on the column one wants to remove duplicates,under home ribbon,conditional formatting select remove duplicates or give conditions
• Spelling errors-Select all,under review ribbon,spellings
• Inconsistent spacing-use the TRIM command =TRIM()
• Wrong formatting eg numbers formatted as text-select the column,under the home ribbon,select number format as required eg if its date,use the short date if its salary use accounting format,numbers under general etc or different date format,under the data ribbon,select text to columns and fill the date format you want to use eg DMY,YMD etc

• Messy borders and blanks-to get rid of the blanks,ctrl G(go to shortcut) and click on special,type the replacement value,press ctrl enter it will replace blanks with the values i types eg 0,N/Aor Missing.

• Text not standardized eg under name columns instead of Esther Karanja displays eStheR KaRanja.We use the formula =UPPER()will return ESTHER KARANJA uppercase.
  We can use =LOWER()for all names to be in small case and =PROPER()for all the names in the normal case

After cleaning the data properly one can create a table ctrl T or under insert ribbon create table.

Conclusion

This week i have realized that cleaning data is a very important in the data analysis process and i should be keen and ensure everything is in the right format,no blanks,no redudant data,no spelling errors,spacing errors and many more errors that may be overlooked.