Excel For Data Analytics

Introduction

Excel is a spreadsheet operator developed by Microsoft and used by different operating systems.It features a wide variety of tools from computational tools to graphical tools,infact Excel 2016 has up to 484 functions used to perform different tasks.It is undoubtedly one of the most powerful applications used in Data Analytics

Features of Excel

Data in Excel is stored in a spreadsheet.A spreadsheet is a table made up of rows and columns.A collection of many spreadsheets form a workbook.

A row in a spreadsheet is labelled with numbers i.e 1,2,3 whereas a column is letter labelled from A,B,C...

The intersection of rows and columns form cells where information is stored,a cell in excel is therefore identified by the row and column header e.g A1,C5. This cell name is usually displayed in the name box,shown at the top left of your screen

When you open your workbook,you are met with a command bar interface at the top of your window that we call ribbons.Ribbons categorises tools into logical operations for easy access of formatting tools,data management tools and graphical tools.

Ribbon Structure

• Tabs: Top level headers on top of your screen (File,Home,Insert,Draw...)

• Groups:Tabs' sub-sections that appears when you click on tabs,they usually display various tools i.e Font,Alignment,Data Types,Sort&Filter

• Command Buttons:A variety of buttons that execute commands when clicked upon.Some buttons will reveal lists of selectable choices one can choose from e.g Font Size,Number Format,Conditional Formatting

Data Types

What are Data Types ?

Data Types refer to simply the type of text one can input in a cell.Excel typically allows different types of data types as discussed below

• Text(String) : Alphanumeric characters or words.Excel aligns text data type to the left by default

• Number : Salaries,Age,Quantities or Counts.Excel aligns numeric data types to the right.We have different number formats in excel that changes how values look in your screen i.e Currencies,General,Date&Time

• Date & Time : Another data type used to record dates.Dates in excel are stored sequentially from 1st January 1900 which is recorded as one,the reason behind being to make mathematical operations fast

• Formulas : Different type of functions used to perform computational and logical operations.It is easier to use SUM formulae to find totals than manually having to add them together.To write a formulae ,you start by an equal sign

• Boolean : Logical operations that return True or False

Data Cleaning

Data imported as a csv file generally requires some cleaning.Once or twice you may find some clean data that requires no cleaning.Data cleaning involves you checking for the correct data type,expanding your fields or filling in some blanks.Cleaning your data prepares it for different formatting.

Above is an image showing how raw data often looks like.

The best way of cleaning your data is by first checking up for errors. Looking at our data we can see that current and old price fields both have the numbers aligned to the left as text as opposed to the right.It is also evident that review and ratings fields do have blanks.Apart from those minor errors our data is majorly clean

We can start by highlighting the whole workbook then under home tab,look for format,usually a dropdown will appear if you click on it and then you can choose Autofit Column Width to expand our fields.Ideally we can do this manually by having our "cross-like" pointer between fields but it will take a lot of time

Just after formatting your column width,highlight your workbook and under home tab,apply filter
Your excel should primarily look like this

You can change the headings to be bold and add some background colour to make it more visible.

First things first,we need to remove duplicates from our dataset,we will highlight our workbook and under data tab choose remove duplicates from data tools.Excel will pop up a message of how many unique values are left.

NOTE : The easiest way of highlighting the whole workbook is by clicking on the triangle on top of your first row

Cleaning your Data

Here are some quick steps on how to clean your current and old price fields

• Highlight column "Current Price" by clicking on your column header

• Ctrl + H will pop up Find and Replace dialog box,where you can ask your PC to find KSh and for now do not replace with anything.Leave the replace input box empty.Click on Replace All to Replace

• Highlight column "Current Price"

• Under Home tab,Find the Group Number and click on the arrow,change the type of your number to currency then choose 'KES' as Kenyan symbol

Your column should have your data aligned to the left.Redo the same for Old Price field

For reviews and ratings,here is how to best clean your data

• Click on the filter symbol

• Sort your data from largest to smallest,sorting your data prompts your PC to have the cells with errors to come first,this often is the easiest method for finding errors because some datasets can be very large
  For our data sorting doesn't have the blanks coming first ,so

• Highlight the column "Reviews"

• Ctrl + H for the Find and Replace dialog box,leave the find input box as blank and type Unknown in the replace input box

• Do the same for ratings and discount fields

Above is our cleaned data

This data has been generally clean and did not need too much changes.For some dataset with numerous fields,cleaning will surely take some time.For instance,some data will have different words to mean the same thing e.g NYC and New York City in different cells.We can even encounter misspellings.It is important we correct to have one word to have one meaning all through

For such cases we;

• Ctrl + H for the Find and Replace dialog box and type New York City in the find input box and replace it with NYC

• Click on Replace All to Replace.

Some datasets will have names of people in different cases.You may find some surnames are written in uppercase where as the first names are written in lowercase.We need to change the names to proper cases and here we will invoke text functions

Text Functions
We have different text functions that transform data differently

• Upper : Upper function will change the data to uppercase.Usually written as "=UPPER(cell address)"

• Lower : Lower function will change data to lowercase.Usually written "=LOWER(cell address)"

• Proper : Proper function changes your data to proper case,where the first letter of your word is capital and the rest small e.g John.To invoke this function "=PROPER(cell address)"

Incase you find yourself where you have to format names then follow these steps

• Highlight the column,right click and insert two new columns

• Type out the respective formula you want in one column

• Copy the result of column1 and paste special to column2
  We choose the option of pasting values inorder to be able to delete the column the formula we are referencing otherwise when we don't paste values and delete the column we are referencing,our new column will return an error.

Data Validation

Data validation helps to restrict what data can be entered.It is usually useful when data entry has not been done.Data validation tool is under the Data tab
To validate data,

• Highlight the column

• Under data tab,select data validation,a dropdown of options usually appears and you can choose data validation

• A dialog box pops up that has two input boxes,Allow and Data.You can choose list from the list that appears when you click on the arrow at the end of the allow input box and type the entries you want allowed in the source input box.

• Click Ok

Conclusion

In a nutshell,data cleaning is just finding errors,removing duplicates and missing values in a dataset to make it accurate and ready for analysis.Excel does this perfectly