Data analysis is the process of examining, cleaning, transforming, and interpreting data to find useful information required for decision-making. In simple terms, we can say that it is the process of taking raw data, organising it, and finally turning it into useful information for better insight.
It is important to prepare raw data since if you try to interpret messy data, you will also get misleading results. Raw data usually is inconsistent, inaccurate, and not well structured. Common issues with raw data might include, but are not limited to, duplicate records, incorrect dates, incorrect data types, missing values, and incorrect spellings, among others.
Excel is especially useful for beginners since it provides an easy and accessible way to learn fundamental concepts for analysis before progressing to more advanced tools that require one to have programming knowledge, such as SQL, R and Python.
I will be demonstrating the different concepts learnt during the first week of Excel by using them to clean a raw dataset, prepare the information for analysis, identify patterns after the data has been cleaned and present the visualisation and insights I will derive from interpreting that data.
Excel Fundamentals for Data Analytics
Before working with any raw dataset, it is important to understand the features of Excel that make it useful for data analysis. These fundamentals provide the foundation for data entry, organising, cleaning and presenting the data.
I started by understanding how data is organised in cells, rows, columns and ranges. These may seem like very basic concepts, but it is very important to understand how data is organised in a worksheet, especially when dealing with large datasets. I also learnt the different types of data such as dates, numbers, currency and text and how to properly format them. This was useful because some data looked correct at first glance before formatting, but it was actually wrong, such as dates entered as text data. Sorting and filtering was also very important when dealing with different data types. I was able to custom sort multiple columns and filter records based on the respective values that they contained, which made it easier to examine.
Data validation is another concept I found very useful, since it can be used to apply specific rules to cells or ranges such as allowing users to select only values from a predefined list. This prevents incorrect data entry when maintaining a dataset over time.
Quality Issues Noted in the Dataset
I learnt all this using a dirty HR dataset that contained multiple columns such as Employee ID, First Name, Last Name, Department, Salary, Hire Date, Bonus, Gender, Age, Marital Status and Performance Score, among others.
The dataset was dirty by all standards, as it contained various inconsistencies, including inconsistent employee IDs, duplicates, incorrect formats, missing values, inconsistent capitalisation, invalid values, inconsistent categories, wrong date formats, spelling errors and multiple pseudo-blank categories. This allowed me to practise almost every data cleaning technique I learnt during the week.
This data is very important to an organisation but cannot be used to generate useful insights. Salary comparisons between departments, performance by department, and reliably reporting the hiring dates or even marital status would be a headache using this data. Thereby, it is important that such data be cleaned so as to assist in decision-making by the management/stakeholders.
Preparing/Cleaning the Data
The next step after identifying the quality issues was to prepare and clean the data before attempting any analysis with the data.
Inconsistent Employee IDs
A few employee IDs were inconsistent with the rest of the data, so I had to standardise that column data.
Here, I used the RIGHT() function to extract the rightmost characters since all the IDs had five characters at the end, hence =RIGHT(A2, 5). This worked, and it removed the EMP- at the beginning of the employee IDs.
Incorrect Formats
In the salary column there were multiple incorrect formats where some values were entered as text data and others had a wrong currency sign. I first converted those values entered as text to the numbers format and then formatted the whole column from the format in the ribbon and changed it from a number format to a currency format, and then I went ahead and chose the correct currency symbol, which made the column become standardised.
Inconsistent Capitalisation
In the name columns, there were multiple instances where the names were inconsistent in capitalisation; some were written in all uppercase while others were written entirely in lowercase.
I used the text function PROPER() and autofilled the whole columns so as to change any inconsistencies down the name columns =PROPER(A2), =PROPER(B2)
Invalid Values
There were also cases of invalid values, especially in some columns such as the age and hire date columns. There were employees listed as being four years old, while others were listed as having been hired in the year 1900, which cannot be the case; therefore, I had to replace the invalid values with blanks because they appeared to be cases of incorrect data entry. This is why data validation is actually important in such scenarios since it prevents invalid future entries in the dataset.
Inconsistent Categories
There were a lot of inconsistent categories in this dataset. Most of these occurred within the same columns where you would find for example, in the department column, instances of HR, H.R, Human Resources, IT, I.T. and Info Tech. In the office location column, you would find Nairobi, Nairobii, Tokio and Tokyo. These were meant to refer to the same thing but were entered differently. I therefore used find and replace to standardise the entries so that each category had a consistent representation. This made it easier whenever you wanted to filter employees by department, office location and other categorical variables.
Duplicate Records
The dataset also contained duplicate records where some employees appeared more than once. I used Excel's Remove Duplicates feature under the Data tab to identify and remove these duplicates after I checking to ensure they were actually duplicates and not just employees with matching details.
Analysis, Visualisation & Insights
After cleaning the dataset, I could now begin analysing it with more confidence that obvious inconsistencies would not affect the results. I used formulas and pivot tables to compare the salaries in different departments such as finance and HR using SUMIF. I went further and compared the salaries in these departments by gender using SUMIFS. For example, I used the following formulas:
=SUMIF(E2:E870,"HR",F2:F870)
=SUMIF(E2:E870,"Finance",F2:F870)
=SUMIFS(G2:G870,E2:E870,"HR",J2:J870,"F")
=SUMIFS(G2:G870,E2:E870,"HR",J2:J870,"M")
=SUMIFS(G2:G870,E2:E870,"Finance",J2:J870,"F")
=SUMIFS(G2:G870,E2:E870,"Finance",J2:J870,"M")
The SUMIF formulas allowed me to calculate the total salaries for each department, while SUMIFS allowed me to apply multiple criteria at the same time, in this case, department and gender.
I also created pivot tables to analyse the same information, making the comparison easier to interpret visually.
The analysis revealed that the HR department had a higher total salary expenditure than the finance department. It also showed that the female employees accounted for higher total salaries than the male employees in both departments. However, the HR department also had more employees overall than the finance department, which suggests that the difference in salaries between the two departments may partly be influenced by the difference in the number of employees rather than individual salaries.
This analysis proved that Excel is a vital tool in analysing data since it provides a basis for further analysis. It also highlighted the importance of clean data since the analysis does not begin with creating charts or tables but rather with the preparation and cleaning of the dataset. This exercise gave me a practical understanding of how Excel can be used to transform raw data into meaningful information. Also, analysis is not just about formulas and visualisations but about asking the right questions and interpreting the results carefully.
Top comments (0)