Crash Course: Excel for Data Analysis
Excel is a powerful tool for Data Analysis. I will illustrate the powerful features Excel has to offer
If you prefer video format: https://www.youtube.com/channel/UC-Ovosl37yuR4zS2byHSFKQ/playlists
Otherwise, let’s start by looking at the SORT function
The SORT function in Excel arranges the contents of a range or array in ascending or descending order. One or more columns can sort values.
Single column sort sorts the record by one column
Example: Using our bank customer dataset, we use the single sort function to arrange the list of customers by surname in ascending order — the sort tool is located in the data tab.
Multicolumn sort sorts by more than one column
Example: We can further sort the bank customers by age.
FILTER Function
The FILTER function is a standard function used for data analysis; it allows users records that meet specific criteria
Example: We can use the filter function to show only female users. The filter function is in the data tab.
Duplicates can easily be removed in excel. First, highlight the record (CTRL + C for windows or COMMAND + C for Mac) under the “Data” tab click “remove duplicate”.
CONDITIONAL FORMATTING
The conditional formatting function allows an analyst to highlight cells based on specific rules
An analyst can use this feature to highlight blank cells or duplicate records during data cleaning.
To highlight blank cells
· Make sure you are on the home tab
· Highlight the entire record
· Click on conditional formatting
· Click highlight cell rules
· Click more rules
· Select “Only format cells that contain blank.”
· Select the colour.
Calculations using Excel
Before making calculations in Excel, the equal to (=) symbol must start any function in excel, that’s what tells Excel to treat anything entered as a formula
The operator used for addition is + (plus sign).
The operator for subtraction is — (minus sign).
For multiplication, * (asterisk).
The / (forward slash) is the operator used for division.
Raising to power is signified by ^ (caret).
Excel has some formulas that help work with data a lot easier because doing calculations like adding all the cells of a record one after the other is hectic and time-consuming. Using the SUM function and referencing the cells saves a lot of time.
Clicking the Fx in excel will bring out the complete list of functions used for analysis.
We will be looking into a few of them in future article.
Going back to our sample data, if we wanted to know the total of the customer’s balance, we used the SUM function. The AVERAGE function finds the average balance.
Pivot Tables
Pivot Tables are one of Excel’s most powerful features. A pivot table summarizes data.
To create a pivot table from your records
· Highlight your records
· Click on the Insert tab
· Click on the pivot table
· If you didn’t previously highlight your cells, select the range of the records
· Choose where to store the pivot table, either on a new sheet or on the current sheet
If we want to know the count of male and female customers from our dataset, we can use the pivot table.
Here’s how to do that:
· Drag the gender column to Rows
· Drag the customer ID column to Values (change to count)
They say a picture is worth more than a thousand words. The same applies to Data analytics. Illustrations using Charts can say more than a sheet full of numbers.
Here is how to create a simple chat
I am using the data from the pivot table to visualize the customers grouped by gender.
Simply highlight the cells to be visualized, click on the Insert tab, click on the 2-d pie chart.
Lastly, the Analysis ToolPak is a fantastic add-in program in Excel that helps in Exploratory analysis.
Here is a helpful article to get you started using the Analysis toolPak
Checkout my Excel tutorial playlist on Youtube: https://www.youtube.com/channel/UC-Ovosl37yuR4zS2byHSFKQ/playlists
The dataset we will use in this article is a modified dataset gotten from Kaggle.
The original dataset used; https://www.kaggle.com/ukveteran/uk-bank-customers
the modified dataset if you want to follow along; https://bit.ly/3IjjPwC
