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

What is Excel?

Excel is a spreadsheet program that enables users to capture, organize, calculate and visualize data. Excel is part of Microsoft office and is available for Windows and Mac. Operating systems that can't access excel like Linux will first require installation of a virtual Machine(A software based computer inside another physical computer) through a cloud-service provider like Microsoft Azure, Google Cloud Platform(GCP) or Amazon Web Services(AWS).

How Organizations Use Excel.

• Collection and storage of data.
• Data cleaning.
• Data analysis and visualization.
• Performance reporting.
• Accounting.
• Administrative and project management.

Excel User Interface and Components.

Interface.

When you open excel on your laptop, this is what you essentially see:

Components of Excel.

• Ribbon: A row of tabs that helps you locate and navigate commands. The tabs include Home, Insert, Page Layout, Formulas, Data, Review, View and Help.
  

• Quick Access Toolbar: Contains icons for save, undo and redo. Marked blue in the image below is the quick access toolbar.
  

• Formula bar: Where contents of a selected cell are displayed. The formul bar is circled red in the image below.
  

• Worksheet: A set of rows and columns.

• Column: A vertical line of cells labelled in Alphabetical Order(Column A, Column B,C...)

• Row: Horizontal line of cells labelled in numerical order( Row 1, Row 2,3...)

• Cell: Intersection of a row and column where raw data is captured. It is labelled as column_row(A1, B12)

  • Worksheet tabs: Tabs at the bottom (Original, Cleaned, Sheet2) that switch between spreadsheets within a workbook

• Workbook- A collection of multiple spreadsheets/worksheets that create an excel file.

• Status bar: Displays workbook status and quick calculation information. Refer on what it looks like using the image below.
  

Data Entry and Editing.

Entering data:

To enter data into a cell, click on any cell then add values. Columns store fields and rows store records. Click enter to move to the next row or the arrow keys to move to whichever cell you want.
See example below:

Data Types:

Some commonly used data types that can be found on the home tab under number group include:

• General: No specific format
• Text: Words(Gloria)
• Number:2
• Dates: 02/12/2026
• Currency: $280

Data types are also accessible when you select a cell/cells then right click and choose format cells on the dropdown list that appears.

Data formatting in excel:

All data formatting commands are found in the home tab.

• To Format text: Click on home tab then under font group select whatever format you desire. Some format options include Bold(B), Italics(I), Underline(U). You can also change Font type, Font color and Font size.

• To Align cells> Check alignment group.Some alignment options include aligning to either the right, left, center top or bottom, merging cells(combining cells) and wrappint text(moving words to a new line when they reach a margin instead of overflowing.
  Normally, when the data type is text, the cell contents are aligned to the left. Numerical data is usually aligned to the right.

• To Format numbers: Check the Number group which contains actions like number types and choose your data type from the dropdown list.

• Borders and fill colors: Select your cell range then go to font group and click on the diagram that looks like a window pane. See borders in the image below marked in blue.
  

Saving a Workbook:

Different ways to save your workbook:

1. Click on file then click on save as. Select the directory/folder you want it save in.(eg.Desktop) then write your file name. Make sure the file type is excel workbook/ .xlsx.
2. You can also click on the save icon at the quicktool bar at the top left part of the screen.
3. To save time, use the save shortcut by clicking ctrl and S.

Data Sorting and Filtering.

Sorting:

Sorting means arranging data in a specific order. You can sort text in alphabetical order(A-Z) or (Z-A), numerical values from largest to smallest and vice versa and date from oldest to newest and vice versa.
Sorting helps to make data easier to read, search and manage.
To sort:

• Select column or range of cells you want to sort.
• Go to home tab then sort and filter under the editing group.
• Choose what type of sorting you desire. You can also custom sort. Then click ok to run that command.

Filtering:

Filtering hides cells that don't meet a certain criteria. It only displays the cells that do. The filer button is at the same place where the sort is. Check the sorting diagram above.
To filter:

• Click anywhere on your data set.
• Click on sort&filter on the hometab in the editing group, then click filter.
• A dropdown arrow will appear on the column you want the filter.
• Click on the arrow and choose your filter option. Click ok.

There are three types of filter options:

• Text Filter: Contains, Begins With, Ends With.....
• Number Filter: Greater Than, Less Than......
• Date Filter: Before, After, Between.....

Data Validation:

Data validation restricts the type of data that can be keyed into a cell in a particular field/column.

• Select range of cells you want to validate.

• Go to data tab then click on data validation
  

• On the dialog box, select the criteria you want to use.
  

An example is when you want your column to only accept K, L and M, you can click on list then add the 3 values. No other value that is not the three will be accepted in that particular column. See demonstration below.

Any attempt to key in any value that is not in the criteria will bring an error message as shown in the illustration above.

Data Cleaning.

Data cleaning is the process of finding and fixing errors, missing values and removing duplicates in a dataset to make it more accurate and ready for analysis.

Before cleaning your data, you can perform this actions to make navigation easier:

1. Autofit: When values in cells are cut off showing ###errors, you can first select all data on your workspace using the short cut C*TRL+A* then autofit by clicking on the home tab, then on the cells group click format, then click autofit width length as shown below.

2.Freezing Panes:
Freezing panes helps to lock specific rows or columns so that they remain visible as you scroll. This makes it easier to work on the data especially when dealing with a large data set.
Click on the view tab the freeze panes the choose what you intend to freeze, then click ok.

Removing Duplicates:

To remove duplicates,

• Select your complete dataset by clicking the ctrl+A shortcut.

• Go to the Data tab and select remove duplicates. See illustration below.
  

• In the dialog box, check the columns to consider. Often it is wiser to check all. Then click ok.
  

Number Formatting:

• Number formatting changes how numerical values appear on a spreadsheet without changing the actual data stored in the cell.
• To format number,
• You can select cells then right click to return a dropdown box with a format cells option.

1. Select cells then under the home tab go to number group and click on number format bar. These data types are used to show prices(currency), percentages(percentages/%)and dates.

Conditional Formatting:

Conditional Formatting is used to highlight cells that follow a rule that you have set, making it easier to search for a particular value and also spot trends.
To conditional format:

• Select cells.
• Go to home bar, under styles group click on conditional formatting.
• Choose the rule you wish to apply. Click ok
• Enter the value you want to highlight then click ok.

Text Functions

Text functions can be used to clean data by addressing issues such as extra spaces, inconsistent text formats, unwanted symbols, or multiple text strings combined in a single cell.
Below are the different text functions:

• UPPER-Changes all text in selected cell to upper case- Example: If cell k5 contents is "Grace", when you use the upper function =UPPER(K5) and run it GRACE will be displayed.
• LOWER-Changes all text in selected cell to lower case- Example: If cell k5 contents is "Grace", when you use the lower function =LOWER(K5) and run it grace will be displayed.
• PROPER-Capitalizes the first letter of each word in a cell.If cell k5 contents is "grace kamau" when you use the proper function =PROPER(K5) then "Grcae Kamau" will be displayed.
• TRIM-Removes extra spaces within text. If you =TRIM( " Grace Kamau " then "Grace Kamau" will be displayed.
• LEFT-Extracts leftmost characters. If you =LEFT("Grace,2) then "Gr" will be displayed. Similar to the RIGHT function where if you =RIGHT("Grace",2) it will display "ce".
• MID-Extracts characters from the middle. if you =MID("Grace", 1) then "a" is displayed.
• LEN-Calculates length of text. If you =LEN("Grace") then "5" is displayed.
• FIND-Finds position of a substring. If you =FIND("a","Grace") the "3" is displayed.
• CONCAT-Combine two texts from different cells into one. If Grace(k5) and Kamau(L5) are in different cells you can =CONCAT(K2," ",L2) then "Grace Kamau" will be displayed. the " " on the function is for when you want a single space between the two texts.
• SUBSTITUTE-Replaces a text within a string. If you =SUBSTITUTE("Grace kamau"," kamau"," Mutua") it will display "Grace Mutua".

Other functions include

• Aggregate functions: SUM, PRODUCT, POWER, MODE, MEDIAN, MAX, MIN, SQRT etc. Statistical functions: COUNT, COUNTA, COUNTBLANK etc. Conditional: COUNTIF,SUMIF....etc. Date and Time: TODAY,NOW,DAY,MONTH,YEAR,DATEDIF,NETWORKDAYS etc.

Formulas.

A formula is an expression in a cell that performs calculations. It must always start with the = sign.
It can include

• Cell references(M20)
• Operators(*,+)
• Numbers(45)
• Functions(SUM) An example: =78+22 which will return 100

Conclusion

This article has explained the basics of Mircosoft excel including components and the different ways to you can use it to clean data(Sorting and filtering, Data validation, removing duplicates, number formatting). Moreover, you can practice on ai generated unclean date and explore other articles to deepen your skills.