DEV Community: Felix Nyabwonda

Getting Started with Excel for Data Analytics: From Basics to Data Cleaning

Felix Nyabwonda — Mon, 31 Aug 2026 00:55:30 +0000

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.”

Quick Overview of Relationship Schemas and Joins

Felix Nyabwonda — Sun, 28 Jun 2026 23:57:26 +0000

Introduction

BI analysts are tasked with answering business questions for their clients. Before answering questions concerning revenue, profits, or growth prospects, an analyst needs to understand where the data is stored and how to bring them together. And here is where relationship schemas and joins come in. Data is usually not stored in one place. It is spread across multiple sheets originating from different systems, such as CRM records, POS systems, marketing campaigns, or off-system sales.

Relationship databases are made of two core concepts: relationship schemas and joins. Relationship Schemas is the framework on what data a given systems store and how the different sections of the data connect. Meanwhile, Joins is the means by which an analyst can query the connections established by relationship schemas. Ultimately, the latter creates the full picture of the data and answers business questions.

What is a Relationship Schema?

A relationship schema is a description of how data is structured. It describes the structure of each table and how the different tables relate to one another. A schema is more of the skeleton or structure for where data is stored and how the different information relates or connects. In addition, it is a map that tells you that a certain table exists and what columns the different tables hold and how these tables connect.

We have different types of tables as expounded below:

Flat Table – simple table with everything included without relationships being highlighted.
Fact Table – is the heart of a data warehouse with little descriptive texts and majoring in measurable business events.
Dimension Tables – provides context around the facts and richer in descriptions.
Bridge Table – also known as a Junction Table. It sits between two tables and holds one row per pairing in many-to-many relationships

When we narrow down to the tables in the relational database, each row represents one specific instance of the entity whereby each entity is maintained a table. Moreover, each column represents an attribute of the entity (specific table). Basically, the importance of a relationship schema is to store each piece of the data once and reference it whenever its required. For these relationships to work in a data design, you need two keys: primary and secondary.

The primary key is a must for every table because without it you have no way to uniquely refer to a specific row. Contrarily, foreign keys act as a link or pointer for more details. It references the primary key of another table and acts as a relationship between two tables. Furthermore, we have other key types: composite, surrogate and natural keys.

Two of the most common schemas include the Star and Snowflake schema. The former is the most commonly employed schema by analysts. It contains a fact table sitting a center surrounded by dimension tables. The dimension tables are smaller than the fact table and usually just provide context to the events captured in the fact table. The fact table contains foreign keys that point to each dimension. Meanwhile, the Snowflake schema is sort of an upgraded version of the Star where the dimension tables give links to other tables that allow one to create or write more joins. Other schemas you might come across include Galaxy schema, Data Vault, and the One Big Table.

There are three types of relationships:

One-to-One – one record in one table corresponds to exactly one record in another table.
One-to-Many – one record in one table corresponds to many records in another table.
Many-to Many – many records in one table corresponds to many records in another table, thereby requiring a bridge table.

What are Joins?

Once you have understood what a relation schema is and the importance of having structure in data housing the next step is leaning how to query the same data. A Join is an SQL operation that combines rows from tables based on columns with relationships. It usually follows matching of a foreign keys to a primary key. As we had explained earlier, data is usually generated from different tables or worksheets from multiple systems in a business. In that case, Joins brings the different relevant pieces together when you want to draw information.

Joins include the Inner Join, Left Outer Join, and the Right Outer Join. The Inner Join returns only rows where there is a match in both tables while the Left Outer Join returns all rows from the left table. In addition, the latter also returns any matching rows from the right table keeping in mind that no matches from the right column are filled with NULL. Conversely, the Right Join will return all the matching rows from the right table and left table, and unmatched columns on the left become NULL. Other Joins include:
• Full Outer Join returns all the rows from the tables and where here no matches NULL is filled on both sides.
• Cross Join literally combines everything since it produces a cartesian product of two tables resulting in exponential growth.
• Self Join is a table joined to itself which is useful in recursive or hierarchical data in a single table

How Excel is Used in Real-World Data Analysis

Felix Nyabwonda — Sat, 06 Jun 2026 21:58:22 +0000

Overview of Excel

Excel is a spreadsheet offered by Microsoft made up of columns and rows, where you can store information between the columns and the rows. You can store all sort of information on excel including but not limited to values, names, dates, quantities, currencies, etc. You can also write excel formulas or functions in the cell to affect multiple cells. Excel allows you to take raw data and draw clean reports by product, regions, and month using simple tools like pivot tables that do not require formulas.

Uses of Data Analysis

Excel finds many uses in most corporate meetings, enabling data analysts and Business Intelligence practitioners advices key decision makers on what moves to make based on the available raw and cleaned data.

For instance in making key business decision, Excel offers one of the best non-complex ways to arrive a key decision. Excel can help you analyze a new market quickly, build a weighted scoring, and run a break-even analysis.

You can also use Excel to see trend and analyze market performance. I have used it before to analyze Import-Export data in pharmaceuticals to segment the market. The info aids in curating reliable paths for companies trying to enter the market since I can rely on the historical data and how the different ATC (Anatomical Therapeutic Areas) are performing in a given area.

Excel Features or Formulas
Excel formulas are meant to make your work easier especially when dealing with hundred thousand rows of raw data and trying to make sense out of it. These functions come in in cleaning

Excel Formulas
Data cleaning is the most critical stage that precedes data processing and analyzing. It ensures that data is
accurate, complete and ready for analysis and reporting. Data cleaning is usually achieved by various Excel formulas and shortcuts to remove errors, duplicates, and any inconsistencies in the data that might affect analysis. Here are some of the formulas or shortcuts I routinely use in data cleaning:

- to capitalize the some of the names in the data you can use (=PROPER).
- to lower case is good for use inconsistencies in the capitalization of names (=LOWER) 3 - to substitute a certain term that is repetitive while you want to make all the same (control F to search the item and then Replace _the items with the nae you would it to _reflect). 4 - Dates you can get them through the =TRIM function if it comes with times or the (=DATEVALUE)
- You can convert these formulas to values simply copying the entire column or affected areas and pasting them as values (press these buttons simultaneously control shift v)

Pivot Tables
One of the most important tools of Excel is Pivot tales, which allows users analyze large sets of data within a short time frame. A live pivot simulation allows you to draw different insights from the same data. I use pivot tables so often, especially when discussing with stakeholders to reveal hidden patterns in raw data and see trends.

Creating a pivot table takes less than 5 easy steps:

Click on any cell inside your data and press Control _simultaneously with _T. The short cut allows you to turn the data into a table.
Go under the Excel ribbon Insert click the drop down on the PivotTable and select table range.
Clicking on the pivotable template on the left allows you to make adjustments to what data from your table to will used. You will have the Rows, Values, Filter, and Column sections.
Pivot Tables also have other powerful tools such as calculated fields and items, slicers, running totals, and percentage different, which help in analyzing data further.
You can also create charts from the pivot table to showcase trends in the data you are analyzing.

Impact of How Excel in Viewing Data

Most people use Excel to draw insights from raw data and make insightful decisions. For instance Business Intelligence officers use it to analyze market trends while accountants use it to analyze financial statements. Excel is a tool that assist everyone analyze trends, uncover hidden patterns, and make meaningful analysis that goes beyond the superficial perceived trends.