Overview
I have just covered excel basic functions and cleaning data for correct interpretation and derivation of insights. Learning about Statistical and Aggregate functions and finally about Conditional formatting and calculations.
What is Excel?
Simply put, I describe excel as a grid of boxes called cells, arranged in rows which are numbered 1, 2, 3... and columns which are lettered A, B, C.... Each cell can hold a number, a word, a date, or a formula.
Basics to Know
Formulas start with an equals sign (=). If you type =5+5 into a cell, Excel shows you 10. If you type =A1+A2, it adds together the numbers sitting in cells A1 and A2. This is the whole foundation of everything else. Formulas let a cell calculate something instead of just displaying a fixed number typed in.
The Messy Data I Was Working With
Looking at this data, some of the issues present include but not limited to:
- Spelling mistakes. "Markting" instead of "Marketing."
- Missing information.
- Inconsistent naming and cell formatting.
Step 1 of Data Cleaning: We figure out how bad the data set is.
To do this, we will use two formulas:
- =COUNTA()- this will tell us how many cells in the selected range have some value typed in them.
- =COUNTBLANK() - this formula will tell us how many blank cells there in a selected range.
Step 2: Fix Inconsistent Spelling and Capitalization and Spaces
For this, we will use two formulas.
- =TRIM() - This formula removes any invisible characters.
- =PROPER() - Tis formula fixes capitalization, so "human resources" becomes "Human Resources.
- =LOWER() - This formula sets all characters to lower case. It is very handy when generating emails.
- =LEN() -This formula will tell you how many characters are in a cell, including the invisible ones.
- Although the =Len() character count formula works best when used alongside other character counting formulas like, =LEFT(), =RIGHT(), =MID()
Step 3: Removing Duplicates
This is a strenuous activity if it were to be manually by scrolling one cell/column after the other.
Looking at the dirty data, we can see that there are double entries.
In excel we can remove duplicates easily by using =COUNTIF() formula.
Step 4: Formatting Columns Correctly
This can apply in cases where the column,ie the date or salary column is not correctly formatted, the values will be misconstrued and not accurately represent the data as they should.
Aggregate Functions
These add up or combine a whole range of numbers into one result.
=SUM() =adds everything in a range.
=AVERAGE() = finds the mean value.
=MIN()= smallest value in a range.
=MAX() = largest value in a range.
=COUNT() =counts how many cells contain numbers.
=COUNTA() = counts how many cells have anything typed in them.
Conditional Calculations
These only calculate based on a set condition.
=IF()= returns one result if a condition is true, another if false.
=SUMIF() and =SUMIFS() = adds numbers only where a condition (or several conditions) is met.
=COUNTIF() and =COUNTIFS() = counts entries that meet a condition.
Conclusion
This is week one in excel, learning the basics and building a foundation. The functions are quite versatile in their use once you understand how to use them to clean, organize and extract insights from a raw set of data.
Top comments (1)
Good work, Nesta. The article clearly explains Excel basics, data-cleaning techniques, and essential formulas using practical examples and screenshots. Keep it up!