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

Microsoft Excel

Microsoft Excel is a spreadsheet program used to calculate data, organize information, and perform detailed analysis. It structures data into rows and columns, allowing users to perform mathematical operations and create charts.

Excel User Interface

This entails the parts that the user interacts while using Microsoft Excel.

1. Ribbon - A large panel near the top holding tools categorized across tabs like Home, Insert, Page Layout, and Formulas.
2. Quick access toolbar - A small, customizable toolbar at the very top-left corner that holds frequent commands like Save, Undo, and Redo.
3. Name box - Located to the left of the formula bar, it displays the cell reference or address of your currently active cell (e.g., A1).
4. Formula Bar - A long input bar next to the name box used to type, view, and edit data or formulas inside active cells
5. Column Bar - The horizontal gray bar running across the top of the worksheet grid that displays letters (A, B, C, etc.) to identify each vertical column
6. Worksheet tab - A small clickable label at the bottom of the Excel window that represents an individual sheet inside your workbook.

Data Formatting in Excel

Changing visual presentation of data without altering its actual values.

Number Formatting

This involves changing the how the value looks in a cell without altering the actual value stored in the cell.

• Select the Home tab
• Go to the Number ribbon
• Select from the dropdown options the appropriate format to apply to the data set.

Data Sorting

This is arranging the data set in a particular order, either from largest to smallest in numeric values and from A to Z in text form.

Data Filtering

This allows specific data to be isolated from a large dataset

• Click any single cell inside a dataset
• On the data tab, in the sort and filter section, select filter

Freezing Panes

This allows the selected column and row to be visible when scrolling through a large dataset.

• Go to the view tab
• Select the row and/or column that should be visible
• Head to windows ribbon under the view tab and click on freeze panes

Data Cleaning

Process of ensuring that the data does not not have incorrect information, duplicates, inconsistencies and errors.

Step 1. Remove Duplicates

Expunge the repeated data from the dataset

• Select the whole dataset through ctrl+A
• Go to the Data tab
• Under data tools ribbon, select remove duplicates
• Check the box "My data has headers" and press Ok

Step 2. Formatting

Ensure the respective columns have the correct format type, that is numerical and text formatting to align with the values in that column.

Step 3. Find and Replace

Use this to remove inconsistencies in each column

• Select the specific column and click ctrl+H
• Input the category that is duplicated and replace with one term to refer the selected category
• Replicate the process in the other columns

Step 4. Remove Blanks

Utilizing the find and replace method, remove the blanks from each column and replace with either 'unknown' or 'not provided'

Text Functions

1. =UPPER() Converts the text to uppercase
2. =LOWER()Converts the text to lowercase
3. =PROPER() Capitalizes the first letter of each word and makes the rest lowercase

Aggregate Functions

1. =SUM() Adds all numbers in a range together
2. =AVERAGE() Calculate the mean of a range
3. =MEDIAN () Finds the number that appears in the middle when arranged in a particular order
4. =MODE()Find the most repeated value in an ordered set
5. =MAX() Finds the highest value in a data set
6. =MIN() Finds the lowest value in a data set

Date Functions

1. =Today() Returns the current date
2. =Now() Returns both the current date and time
3. =Year()Extracts the year from a given date
4. =Month() Extracts the month from a given date
5. =Day() Extracts the day from a given date
6. =Datedif() Calculates the difference between two dates