Introduction to Microsoft Excel
Microsoft excel is used for storing, organizing, cleaning, analyzing and presenting data. It provides a structured environment where users can enter data into rows and columns, perform calculations using formulas and functions, create charts, and apply formatting to make information easier to understand.
Understanding the Interface
Quick Access Toolbar
Save icon- Save icon or you can Ctrl + S
Undo / Redo arrows-step backward or forward through your recent actions.
File name- shows the name of the current file in excel.
-
Search bar-lets you search for any Excel command by typing what you want to do.
Tabs
At the top holds most comomon tools used in excel: File, Home, Insert, Draw, Page Layout, Formulas, Data, Review, View, Help.
Home Tab Groups
Clipboard part we have the Paste,cut and Format Painter.
Fonts part we have the font name needed to use and its font size,below it we have bold,italic,underline ,borders,Theme colors which helps change colors of the headings,we also can increase or reduce the size of the heading with the "A"
Alignment part we have Vertical alignment ** (top/middle/bottom),Horizontal alignment** (left/center/right),Wrap Text — makes long text fit into one cell and Merge & Center — combines multiple cells as one, often used with title headings.
Styles part we have Preset cell styles: Normal, Bad (red), Good (green), Neutral (yellow), Calculation, Check Cell,
Conditional Formatting — changes the apperance of cell and you can uuse color filter and Format as Table — turns a range into a structured, filterable table.
Cells we have the Insert, Delete, Format which add/remove rows, column or sheets and adjust row height/column width or lock cells.
Editing part we have AutoSum (Σ) — quick shortcut to insert SUM, AVERAGE,MIN, MAX, COUNT and more options, Fill is an extend of values or formula across a range, Sort & Filter is a tool behind sorting data and removing blanks and duplicates, Find & Select — search the sheet, or jump to special cells.
Other Ribbon Tabs.
Besides the Home tab, the Ribbon has several other tabs, each grouping tools around a specific type of task:
Insert — used to add things into the sheet beyond raw data: Tables, PivotTables, Charts (bar, line, pie, etc.), Pictures and Icons, Shapes, Text Boxes, Headers & Footers, and Hyperlinks.
Draw — lets you draw or write freehand directly on the sheet using a mouse, stylus, or touchscreen, useful for annotating or sketching ideas quickly.
Page Layout — controls how the sheet looks when printed or exported: Margins, Orientation (Portrait/Landscape), Page Size, Print Area, and Themes that change the overall color/font scheme of the workbook.
Formulas — the home of the Function Library (organized by category: Financial, Logical, Text, Date & Time, Lookup & Reference, Math & Trig), along with Name Manager for naming ranges, and Formula Auditing tools like Trace Precedents/Dependents to see how cells are connected.
Data — where you sort and filter, import data from external sources (like text files, other workbooks, or the web), use Data Validation, apply Text to Columns to split a single column into several, and access the What-If Analysis and Outline/Group tools for summarizing data.
Review— used for checking and collaborating on a workbook: Spelling check, adding Comments or Notes, Protecting a sheet or workbook from edits, and Tracking Changes made by different people.
View — controls how the workbook is displayed: switching between Normal, Page Break Preview, and Page Layout views, showing/hiding Gridlines and the Formula Bar, Freeze Panes (covered earlier), and Zoom.
Help — provides quick access to Microsoft's support resources, feedback options, and training content directly from within Excel.
DATA CLEANING
Removing duplicates
Removing duplicates is applied across all collumns in a one technique way and is fast and easy and first thing to do is copy the first sheet and create another called clean data and have original data stay in a diffrent sheet.
-Six duplicates removed from the data
Sorting and Filtering
sorting and filtering is the fastest way to check data quality issues and know whether they are blanks in the data and then go clean the data after this you Autofit colunm witdth.
In the view bar you freeze the top row so that it can be intact.
Cleaned data
While cleaning data we also use Find and replace to find the unwanted data that was entered and replace with the corect data use ctrl + H.
Data Validation
Data Validation controls what a user is allowed to enter into a cell.
EXample, if you have a column called Gender, you can make a dropdown containing:
Male
Female
The screenshots bellow shows how data validation works it gives us adrop down on the data we supppose to enter and shows an error when one tries to enter invalid data.
Conditional Formating
Conditional Formatting changes the appearance of cells automatically based on their values.
The screenshot bellow shows we telll excel to make the cell red or certain or color of your choice.
First we select the collumn you wannt to format and highlight cell rule and choose the color you'd like to use.
Next you filter by color
Finnaly you have the filtered data by colour selected together in red.
Text Functions for Cleaning Data
Text functions help standardize text, remove extra spaces, extract parts of strings, or find and replace substrings, it is very useful when a dataset has messy or inconsistent entries.
Examples of the fucnctions:
UPPER() — converts text to uppercase. =UPPER("john") → JOHN
LOWER()— converts text to lowercase (lower case → lower c). =LOWER("JOHN") → john.
PROPER()— capitalizes the first letter of each word. =PROPER("john doe") → John Doe.
TRIM() — removes extra spaces from text, e.g. =TRIM(A2).
LEFT() — extracts the leftmost characters — abstract "left" is better read as "from the left." =LEFT("John", 2) → Jo.
RIGHT() — extracts the rightmost characters, e.g. =RIGHT(A2, 3) → last 3 characters.
MID() — extracts characters from the middle, e.g. =MID(A2, 2, 3) → 3 characters starting at position 2.
LEN() — returns the length of the text, e.g. =LEN("John") → 4.
*FIND() *— finds the position of a substring inside text (case-sensitive), e.g. =FIND("w", A2) returns the position of the letter "w".
SUBSTITUTE() — replaces text within a string, e.g. =SUBSTITUTE("John Doe","Doe","Smith") → John Smith.
CONCAT() — joins two or more cells together into one string, e.g. =CONCATENATE(A2, B2) or =CONCAT(A2,B2), useful for merging a first name and last name into a full name.
Screenshot below shows implemented functions
Basic Calculations, Formulas and Arithmetic
Heelps with calculation of cells or cell and a certain figure.
Addition- (+) = A1+B1
Subtraction - (-) = A1-B2
Multiplication - (*) = A1*B1
Division - (/) =A11/B1
Exponent - (^) = A1^2
Common Functions
SUM()— adds a range of numbers, e.g. =SUM(B1:B5)
AVERAGE() — calculates the mean of values.
MIN() — finds the smallest number.
MAX() — finds the largest number.
MEDIAN() — finds the middle value.
MODE()— finds the most frequently occurring value.
PRODUCT() — multiplies numbers together.
POWER() — raises a number to an exponent, e.g. =POWER(10,3) → 10^3 = 1000.
Screenshot of how i worked some of the functions;
Counting and Conditional Functions
These are especially useful once you're cleaning or summarizing large datasets:
COUNTA()— counts all non-blank cells.
COUNTBLANK() — counts all blank cells.
COUNT() — counts numeric values only.
COUNTIF() — counts cells that match one condition, e.g. =COUNTIF(Q2:Q870,"Remote").
COUNTIFS()— counts cells that match more than one condition, e.g.
=COUNTIFS(Q2:Q870,"runner",P2:P870,"Madrid").SUMIF()— sums values that meet one condition.
SUMIFS()— sums values that meet more than one condition.
Date Functions
TODAY() — returns today's date.
YEAR() — extracts the year from a date.
MONTH() — extracts the month from a date.
NOW()- returns the exact time
Datedif()-returns the diffrent number of days,months or years whatever you want from the previous date to the recent date. =datedif(g2,h2,"d").
Networkdays- returns the working days.
Cell References
Instead of typing actual numbers into formulas, you can use cell references like A1 or B2, so that if the value in the referenced cell changes, the formula result updates automatically.
Relative reference — changes when the formula is copied to another cell, e.g. A1.
Absolute reference — stays fixed no matter where the formula is copied, e.g. $A$1.
For example, in a formula like =A2B1, if B1 holds a fixed rate that should never change as the formula is copied down, it should be written as =A2$B$1 instead, so B1 stays locked in place while A2 still adjusts for each row.
These errors are common while writing formulas across a large dataset, and knowing what each one means makes troubleshooting much quicker than guessing.
Common Formula Errors
#DIV/0!- Division by zero.
#REF!- Invalid reference.
#VALUE!- Wrong data type.
#NAME? - Misspelled function.
Conclusion
Excel is a lot more than a grid for typing numbers into — it's a genuine data-cleaning and analysis tool. From navigating the interface, to cleaning data with duplicates removal, sorting, filtering and validation to using text functions, arithmetic, and conditional functions like COUNTIFS and SUMIFS, Excel gives you everything needed to turn a messy, raw dataset into something clean and structured. Understanding cell references and common error codes also makes troubleshooting formulas much faster. Overall, this covers the core Excel toolkit needed to confidently work with real-world data, from basic entry all the way through to conditional summarizing and cleanup.
Top comments (0)