As a novice getting into data, I started off bundling data science, data analytics, and data engineering into one bucket called data science. I often heard terms such as analysis, Moving Annual Turnover, Year to Date, dashboards, and pivot tables in boardrooms during team meetings discussing annual and quarterly strategies. At that time, I would have never thought there would come a time when I would be the one preparing such reports from raw data, considering I was part of the regulatory affairs team. My work at that time involved making timely submissions for product registrations, updating information on drug registrations and retaining it on the registers, and being the focal person who updated the company on matters relating to regulatory affairs. Eventually, I shifted gradually into data and became interested in data analytics, the sweet science of converting raw data into valuable information that aids in driving informed decisions.
What is Data Analytics?
Data analytics, in the simplest terms possible, is the process of collecting, cleaning, and organizing raw data with the aim of drawing insights that aid in driving informed decisions. Data is ubiquitous and equally critical in making decisions. It is used in simple daily tasks such as predicting how long it will take you to arrive at the office, to more complex situations like clinical trials. Many commercial entities and banks use data to drive business strategies, while manufacturers use data to optimize processes, track inventory, and maintain safety standards. Data can be found in so many formats, ranging from videos (.mp4 and .mkv) to pictures (.jpg, jpeg, and .png), text (.txt, .pdf, and .docx), and audio (.mp3, .mp4, and .audio). However, in data analytics, data is usually found in:
- Structured data formats (csv, parquet)
- Semi-structured data formats (json, xml)
- Unstructured data formats (txt, docx, pdf, mp3, jpg)
Several tools exist in data analysis that help individuals clean up and organize raw data to draw meaningful insights. These tools include Python, Power BI, and Excel, with each of these tools being compatible with specific data formats.
Introduction to Excel
Excel is one of the ‘OG” data analysis tools that legendary and new businesses rely on for data entry, management, analysis, and financial modeling. It is the first tool that most data analysts use in their data analytics journey as they enhance their skills. Excel is a spreadsheet program that was developed by Microsoft in 1985. It basically organizes data in rows and columns, allowing you to clean up data, run mathematical functions, and design dashboards to show insights from the data.
Data Container Architecture Useful in Data Cleaning
Excel is available as an application (Office 365) compatible across multiple operating systems, such as Android, Windows, and iOs/macO. There are modern similar versions like Google Sheets. Excel data is usually stored with the .xlsx file extension. The main file is referred to as an Excel workbook. The workbook usually contains several spreadsheets, where data is stored in cells that fall generally in columns and rows. In addition, it contains a Ribbon that contains shortcuts to Excel Commands, Tabs, Groups, App Launcher. Sheets do not operate as isolated islands in the workbook but rather share a unified memory space that allows you to pull data from one sheet to another within the same workbook. Basically, it means you can have a formula in sheet 2 that references data in sheet 1.
Cells, Rows, and Columns
As explained earlier, each sheet contains cells with coordinates within columns named in letters (A to Z) and rows denoted in numbers. Cells allow one to type and store data within an Excel workbook. It is named based on what column and row it sits on; for instance, A1 is a cell found in column A and row 1. Cells can hold all types of data (dates, formulas, numbers, text, etc.). It’s always a good rule of thumb to use the Formula Bar to see what the data is, since a cell acts as a visual mask. You can enter formulas in Excel worksheets by typing the equal sign (=) prior to arithmetic formulas and established Excel formulas in the cell to achieve your intended answer. Simple operators include addition (+), subtraction (-), multiplication (*) and, division (/). You can also select multiple cells, columns, rows, or ranges within the worksheet by highlighting the targeted cells. Advanced functions include:
- =AND (returns answers/values based on two or more conditions
- =AVERAGE (=AVERAGEIF or =AVERAGEIF – calculates averages, including for ranges)
- =COUNT (=COUNTA - filled cells) =COUNTBLANK- blanks, =COUNTIF – specified cells)
- =MAX – returns highest values =Min -returns lowest value in a range
- =MODE – returns the most frequent or repeated number
- =MEDIAN – returns the middle value in the data
- =RIGHT – returns values from the right =LEFT -returns values from
- =TRIM – removes irregular spacing
- =UPPER – converts texts to uppercase =LOWER – converts texts to lowercase
- =VLOOKUP – allows for vertical searches in a table
- XLOOKUP – allows for searches in a table
- HLOOKUP – allows for horizontal searches in a table
Cell Format
One of the initial steps of data cleaning involves looking at the cell formats. You can confirm this by selecting a cell or entire column and checking under the Groups, Number that falls under the Home Tab. It can be classified under Number, General, Date, Currency, Percentage, etc. Alternatively, you can select a cell and right-click, selecting Format Cell, where you pick your desired category. It’s always important to ensure the data you have in the cells or column is formatted in the correct classification. It's best practice to ensure the entire column uses the same cell format and is standardized.
Relative and Absolute References
Referencing in Excel is available in two formats: Absolute and Relative. The former informs Excel to lock a reference, for instance $A$2, which commands Excel to lock on cell A2. I have used this when cleaning up large sets of data and running a COUNTIF formula to know the frequency of different entries within a data set’s column (= COUNTIF($A:$A, A2)). In this case, the formula locks onto column A. Conversely, relative references lack the dollar sign and give the function the freedom to adjust without any restrictions. (A2:A10)
Find and Replace
When cleaning, you can find and replace data by simply pressing Control and F. This feature allows you to find values within your data set, and if there is a need to standardize certain values, you can easily achieve it through the replace option. Under the Replace action, you can go a step further and use case sensitivity or match entire cell contents, which prevents you from making mistakes. For instance, if you want to find “Male” under a certain column and you have entries like “Female” that will appear in your search, and going an extra step of replacing “Male” with “M,” you will end up changing “Female” to “FeM”. However, checking the entire cell contents will avoid this problem.
Freezing and Filtering
Once you have named a particular column, you can filter the cells. The sort and filter function allows you to narrow your focus only on information that meets your criteria. You can access it under the Data tab. Once you filter, you can also sort your data. Sorting data allows you to sort it from largest to smallest for numbers and A to Z for cells under the text formatting, in ascending and descending order. Interestingly, you can sort out your data using two or more cells. When handling large data sets, you can freeze the parts of the worksheet. This function is under the View tab, where you can freeze the top row, column, or certain parts. In addition, you can customize the color of the focus cell by selecting the color of the border or the active cell (available in newer versions of Excel). I have found it really helpful when dealing with thousands of rows of data.
Conditional Formatting
Before you step into conditional formatting, it is good to know that you can turn your data or sheet into a table by simply clicking Control and T. A table will allow you to access options like AutoFill, Formatting, Filtering, and Sorting. You can remove duplicates by going to the Data Tab and selecting the Data Tools group. Here, you will have the option to remove duplicates under one column or multiple columns. Using this route is risky. Therefore, it is advisable to utilize Conditional Formatting, specifically cell highlighting, to show duplicate cells. Aside from showing duplicates, Conditional Formatting will allow you to change the appearance of cells based on dates, matching text, unique values, or numerical values. Once you are confident the data is clean, you can convert it into a PivotTable, which helps you analyze the data further and create a dashboard.
Hopefully, this introduction to Excel helps you view data cleaning as a vital process that determines what insights you might get from your data. It’s important to understand the different types of data formats and Excel basics for you to be able to clean up your data. Best practices in data analytics call for analysts to duplicate raw data and use the duplicate for their cleanup process. Initial steps of data cleaning involve interrogating the data first, checking through it, the columns, rows, blanks, and identifying patterns before making any changes. Furthermore, have an additional sheet where you can document the cleaning process and also your objective. Always keep this in mind as we move from cleaning to analysis, reporting, and visualization: “If it's not documented, it never happened.”
Top comments (1)
The "Male"/"Female" replacement example makes the risk of a careless bulk edit immediately clear, especially when "match entire cell contents" prevents
Femalefrom becomingFeM. Pairing that with duplicate highlighting before removal, a preserved raw-data copy, and a separate cleaning log gives beginners a much safer workflow than simply fixing cells in place. Once the same cleanup repeats across reporting cycles, I'd move those steps into Power Query or another reproducible pipeline-the real tradeoff is speed today versus auditability when the workbook becomes operational.