Excel Basics For Data Analysis — IBM Data Analyst Professional Certificate Review
This is the second post that is part of reviewing the IBM Data Analyst Professional Certificate From IBM and Coursera. You can read the review of Introduction to Data Analytics before reading this post to get yourself familiar.
At first, Here are the course notes I’ve taken during my enrollment. They are tremendously helpful later down the road.
Excel Basics For Data Analysis Course Notes
*Configuring a row to remain on screen
View>Freeze-Panes
*Removing empty rows
select-colmun > sort-and-filter > click on filer arrow > deselect all > select blanks
*Removing Duplicate entries
select-column > conditional-forwarding > highlight cells rules > Duplicate values
*Using proper casing for words
Insert a helper column/row > in the first cell type > =PROPER(A1) > Use the fill handle to drag across the other records
#Copy the helper column and then paste it on the original column/row to replace it.
*Converting Upper to lower/ lower to upper case
Insert a helper column/row > in the first cell type > =UPPER(A1) > Use the fill handle to drag across the other records
#Copy the helper column and then paste it on the original column/row to replace it.
* Removing unwanted spaces with TRIM function
Insert a helper column/row > in the first cell type > =TRIM(A1) > Use the fill handle to drag across the other records
#Copy the helper column and then paste it on the original column/row to replace it.
* Combining multiple columns into one column
Insert a helper column/row > in the first cell type column/row title > in the second cell, type the format of your choice > Use the fill handle to drag across the other records
* Splitting one column into two separate columns
Insert two or three columns/rows beside the original one > the new columns/rows need to have same formatting as the original one >
Select all records in the original column/row > Data tab > Text to columns tool > Delimited > Sppace > Select the destination
#or
#The below formulas apply to a case of splitting name and surname. Modify it based on the scenario.
=LEFT(A2,SEARCH(“”,A2,1)) > In the next cell type > =RIGHT(A2,LEN(A2-SEARCH(“”,A2,1))) > Fill handle
*Conditional Function IF
Insert a helper column/row > in the first cell type > = IF(G2=”shipped”,”1",”0") > Use the fill handle to drag across the other records
* Conditional Function IFS
Insert a helper column/row > in the first cell type > = IFS(G2=”shipped”,”1",G2=”Not-shipped”,”0") > Use the fill handle to drag across the other records
* Statistical COUNTIF function
#Count the number of cell values that meet specified criteria
#Number of cells with France as a value from x2 till x115
=COUNTIF(X2:X115,”France”)
* Statistical COUNTIFS function
* SUMIF Function
#Sum the values in a given range that match a specified criteria
=SUMFIF(E2:E115,”>3000")
“Pivot Tables”
*Formatting Data as a table
select any cell > Format as table
* Creating a pivot table
select any cell > Insert > Pivot Table
Also, watch the review in the video below
