It’s week 3 in my Data Analytics class. We’ve finally finished installing our essentials, learnt how to successfully push projects to GitHub, and now we’re ready for Excel. We began with the basics of Excel, that is rows and columns, the ribbon and some of the functions of the tabs and commands. The next bit was data cleaning, and this is where it begins to get interesting, so let me take you with me step by step.
To make it simple, we will use a dataset to help explain how to clean data. Data is information, and the clearer and cleaner the data, the better the insights derived. The dataset, Dirty Café Sales, that we will use through this journey, has been obtained from Kraggle, by Ahmed Mohamed.
This is a copy of the original data set;
The first step is to save the file as an Excel Workbook file, and then create a copy of the raw data in the new sheet to work from;
Now we can begin to work on the cells themselves. The cells are the rectangular boxes you see on the workbook, intersections of rows and columns. It is represented/referenced by a column name and row number such as A2, F8.
We’ll autofit the column widths, freeze the top row pane, and edit the top row with headers so as to help us easily see what we’re working with.
This is what we’re now working with.
Let’s now work on the data itself. We begin by removing duplicates;
Next, we reset any inconsistent data types. We start by turning on the filters on the headers so that we can know what we’re working with in each column. There are columns with text values, other numerical values, others both. We will work methodically through the sheet from beginning to end.
We change all ‘ERROR’, ‘UNKNOWN’ or blank values to ‘Unknown’, and format the numerical cells to reflect the data they represent i.e currency;
We do this across the sheet.
Next, we learnt about conditional formatting. This is a kind of formatting that changes the appearance of cells.
At this point we got introduced to Text Functions, which are used to standardize text value, i.e if you want text in Proper case or UPPER case or all lower case, where you use the functions Proper(), UPPER() and lower() respectively. There are other like Trim() which you use to remove extra spaces, Len() which gives you the length of text among others. An interesting one is CONCAT() which you use to combine text in two cells, for example if you’re generating an email using first name and last name which are in different cells.
Our most recent class has been on sorting and filtering. You can sort data depending on the type i.e for text values, sort A-Z/Z-A, for numeric values, sort smallest-largest/largest-smallest, for dates, sort oldest-newest/newest-oldest.
You can also do a multi-level sort where you sort across different criteria. For example, below, I decided to sort according to the following selections;
Next comes the math part. I was surprised by how easy Excel made mathematical functions. We’ll begin with aggregate functions that process a range of rows and give a single summarized value. I’ll list them below along with their functions;
Power()- exponent, raises to a specified power
Product()- multiplication of values in a range
Sum()- Sum of values in range
Average()- average of values in range
Median()- value at the center of all values in range
Mode()- most repetitive of values in range
Min()- lowest value
Max()- highest value
SQRT()- square root
Then we have statistical functions;
Count()- counts only cells with numeric values
Counta()- count all non-blank cells
Countblank()- counts only blanks
We also have conditional aggregation that applies aggregate functions to only cells that meet specified criteria e.g Countif();
Finally, we learnt about the functions used to retrieve date values i.e;
Today()- brings you today’s date
Now()- today’s date + current time
Year()
Month()
Day()
Datedif()- gives the difference between two dates
Networkdays()- gives the difference between 2 dates excluding weekends
That’s all for today in Excel world folks. Till next time…
Top comments (0)