Introduction
Microsoft Excel is an application that was developed by Microsoft as a tool for organizing information in rows and columns, performing calculations and presenting data. Overtime, excel has evolved into a powerful tool for data analysis.
Data specialist adopted excel and begun using it for various purposes including data entry, cleaning, organizing, storing, calculating, and validating data among others.
In this article we will be learning the basics of excel and data cleaning in the process of data analysis. We will use example to see how we can convert raw data into meaningful data that can be used to generate powerful insights.
Overview
1.Understanding the Excel Interface
2.Data Types
3.Functions
4.Data Analysis
i) Understanding the Excel Interface.
The first time you open an excel worksheet you will be met by a book like image with a toolbar panel at the top referred to as a ribbon, beneath it on the left a bar referred to as a name box, and next to it is what is referred to as a formular bar, at the center and what covers the main area is a worksheet frame with rows and columns, with each having a column and row header. The rows are numbered from 1 to 1,048,576 while the Columns are named from A to XFD.
An example of a worksheet would be like the one bellow.
The different parts of a worksheet are precisely named in the image below:
For a better understanding we shall define the parts labelled in our image above.
Control Menu/Box- Opens a menu with commands for controlling the Excel window, such as minimizing, maximizing, or closing it.
Quick Access Toolbar-Provides quick access to frequently used commands, such as Save, Undo, and Redo.
Ribbon-The main area at the top of Excel that contains the different commands and tools used to work with a workbook.
Ribbon Category/Tab- Organizes Excel commands into different tabs, such as Home, Insert, Page Layout, Formulas, Data, and Review.
Title Bar- it displays the name of the current workbook along with the name of the Excel program.
Help Button- Opens Excel's help and support features, allowing users to find information or guidance when needed.
Worksheet Frame- This is the main part of the Excel window where the worksheet grid and its contents are displayed.
Select All Button- Selects the entire worksheet at once, including all rows and columns.
Name Box- Shows the location or name of the currently selected cell, such as A1. It can also be used to move quickly to a specific cell.
Active Cell-The cell that is currently selected and ready for data entry or editing.
Dialog Box Launcher- A small button that opens a dialog box containing additional settings and options for a particular group of commands.
Insert Function Button (fx)- Helps users find and insert functions into formulas without having to type the entire function manually.
Column Headings- The letters at the top of the worksheet that identify each column, such as A, B, C, and D.
Row Headings-The numbers along the left side of the worksheet that identify each row.
Formula Bar- Displays the contents of the selected cell and allows users to enter or edit data, text, and formulas.
Category Groups- Groups related commands together on the Ribbon, making it easier to find and use the tools needed for a particular task.
Worksheet Area- The main grid made up of rows and columns where users enter, organize, and view their data
workbook- is an excel file that contains one or more worksheets.
Split Boxes- Divide the worksheet window into separate sections so that different parts of the worksheet can be viewed or scrolled at the same time.
Scroll Bars- Allow users to move around the worksheet vertically or horizontally to view areas that are not currently visible.
Sheet Tabs- Allow users to switch between different worksheets within the same Excel workbook.
Insert Worksheet Button-Adds a new worksheet to the current workbook, making it possible to organize information on separate sheets.
Status Bar-Located at the bottom of the Excel window and displays useful information about the current worksheet, selected cells, and other activities.
View Buttons-Allow users to change how the worksheet is displayed on the screen.
Normal View-The standard Excel view used for entering, editing, and working with data in a worksheet.
Page Layout View- Shows the worksheet in a format similar to how it will look when printed, including page boundaries and margins.
Page Break Preview- Shows where Excel will divide the worksheet into separate pages when the document is printed.
Zoom Slider- Allows users to increase or decrease the size of the worksheet displayed on the screen, making it easier to view the data.
Data Types
Now having understood the excel interface we will look at the different data types catered for in the excel working space.
They include:
- Numeric-data type that contains only numbers.
- Text-data types that contain text and strings
- Date-contains date which includes, days, months, and years.
- Time-contains the time
- Currency-contains the different currency like KES and $
- Boolean-refers to the data that generates TRUE and FALSE values.
Functions
Functions refer to predefined formulas in excel that perform specific calculations.
In our article today we are going to look at text, numeric, date and COUNT Functions used in excel.
a.)Text function
In excel you will encounter text function like
Upper function, Upper()-is used to change a given text into uppercase.
Lower function, Lower()-is used to convert a given text into lowercase.
Proper function, Proper()- is used to convert a given text into proper case E.g. Mary Ann
Find function, Find()-its a function that is used to find a certain text within another text.
Left function, by Left()-is used to extract a specified number of text to the left of a text string.
Right function, right()-is used to extract a specified number of text to the right of a txt string.
Length function, Len()-is used to count the number of characters in a cell or text including spaces.
Mid function, mid()-extracts a specific number of characters from the middle of a string.
CONCAT function, CONCAT()-it is used to combine text from two or more cells into one
b.) Numeric Functions and Operators
Operators refer to symbols used in excel to perform calculations.
These operators include:
- Arithmetic Operators which involve: i) Addition (+) ii) Subtraction (-) iii) multiplication (*) iv) Division (/) v) Exponent (^)
c.) Mathematical functions
i) Power()
ii) sqrt()
d.) Aggregate Functions
In addition we have aggregate functions which can be used to work on a group or range of numbers and give one output.
PRODUCT()
MIN()
MAX()
MODE()
MEDIAN()
SUM()
AVERAGE()
e.) Date Function
These are functions used to create, extract, calculate, or manipulate dates and times in excel
They include:
TODAY()
NOW()
DAY()
MONTH()
YEAR()
DATEDIF()
NETWORK()
f.)Counting Functions
These are functions that are used to count cells or values in a range.
They include
COUNT()
COUNTA()
COUNTBLANK()
e.)Conditional Aggregation Functions
We also have another set of functions that are used to calculate or summarize data only when certain conditions are met.
This functions can be used with both numerical and text data types. They include:
SUMIF()
SUMIFS()
COUNTIF()
COUNTIFS()
AVERAGEIF()
Data analysis
The process of analyzing data in excel begins with having data, then cleaning the data and ensuring each entry is in the correct order, and finally analyzing it to get answers to the questions at hand. We shall define some more labels that will be paramount in our journey to analysis our data.
Data cleaning: This is the process of finding and correcting or removing inconsistencies in a dataset so that the data is accurate and ready for analysis.
Data validation: refers to a feature that is in excel that controls the type of data that a user can enter into a cell, and if entered at a later time when we have our data it prevents entry of current data that is not coherent with the type of data specified.
Conditional Formatting : this refers also to an excel feature that automatically changes the outlook of a cell when they meet a specified condition.
Sorting: these refers to arranging data in a particular order.
sorting is unique for numeric, text, and date data types and custom filter where you use your own condition.
In numeric Sorting numbers are sorted from largest to smallest or smallest to largest
In text sorting sorting occurs either from A to Z or Z to A
While in date sorting its done from oldest to newest or newest to oldest.
Above that we have multilevel sorting where we sort data using more than one criterion, one after another.
Filtering: is the process of displaying only the data that meets specific conditions while temporarily hiding the other data.
Just like sorting filtering depends on the data type, where we have text filters having criterions like equals, does not equal, Begins with, Ends with, Contains, Does not contain, meanwhile number filters have criterions like Equals, Does not Equal, greater than, less than, less than or Equal to, Between, Top 10 and
date filters containing criterions like equals, before, after, Between, Today, Tomorrow, Yesterday, This week, Last week etc.
custom filters on the other hand is used where you use your own conditions.
Now we shall look at an example that puts to practice every information that has been discussed in the article.
Lets say we have a data called Nurses salary. Our data contains different column entries including Nurses id, first name, last name, age, country, year of employment, salary and the type of shift each does.
The image below shows a sample of the data we are working with.
It is important to note that before begin anything with your data you must have two worksheets, one which contains the original data, and the other named cleaned which you will be cleaning and carrying out your analysis
i) cleaning the data
The first thing is to remove duplicates- Select your data range or press Ctrl + A to select the whole table. Go to the Data tab on the top ribbon. Click Remove Duplicates.
1.cleaning numeric data example
Point to note in our data set is that the age column seems to have age appearing on the left side of the column instead of the right. All text appear on the left and numeric data to the right. Our data seems to have a problem with data type so we will go to Home-Number tab-select numeric
after correction
In the salary column you will also note blanks, where there is no entry, for numeric we leave it like that.
2.Cleaning date example
the first entry contains a different format of the date so we are required to change it into one format
3. An Example of cleaning Text Data
Firstly, check if there are any blanks in text columns and convert them to unknown Using ctrl+H that enables you to find the blanks and replace them.
then click on replace all
The text data my contain text not written in proper format like names.
for example look at our screenshot below.
we are required to right click in the columns header-insert column, and use the function Proper to change the names into proper format.
create yet another final column that you will copy the entries from the column first name p to, to avoid having reference errors when the column first name is tampered with.
delete the other columns and remain with the last created column named first name proper.
Conclusion
Excel provides a simple and practical starting point for data analytics because it allows users to organize, examine, clean, and prepare data within a single application. Understanding basic concepts such as rows, columns, cells, cell references, formulas, and functions creates a strong foundation for working with datasets. Through the practical dataset used in this article, it has been demonstrated that Excel can be used to identify and correct common data-quality problems such as missing values, duplicate records, inconsistent text entries, and incorrect data formats. Features such as sorting, filtering, tables, and functions make the cleaning process more efficient and help ensure that the resulting dataset is accurate and consistent. Overall, data cleaning is an important step in the data analytics process because the quality of analysis depends largely on the quality of the data being analyzed. By mastering these basic Excel skills, beginners can confidently move from raw data to a clean, structured dataset that is ready for further analysis and visualization.
Top comments (0)