๐ Excel Basics
Excel is one of the most important foundational tools for a Data Analyst. Before learning advanced formulas, PivotTables, Power Query, or dashboards, you need to understand how Excel works and how to structure data correctly.
1๏ธโฃ What is Excel?
Microsoft Excel is a spreadsheet application used to:
โข Store data
โข Organize information
โข Perform calculations
โข Clean data
โข Analyze data
โข Create reports
โข Build dashboards
โข Visualize trends
For a Data Analyst, Excel is much more than a place to enter numbers.
You can use it to answer questions such as:
Which product generated the highest revenue?
Which region is underperforming?
What is the average order value?
How has sales changed month over month?
2๏ธโฃ Understand Workbooks and Worksheets
๐ Workbook
An Excel file is called a workbook.
Example: Sales_Analysis.xlsx
A workbook can contain multiple worksheets.
๐ Worksheet
A worksheet is an individual sheet inside the workbook.
For example: Sales, Customers, Products, Summary, Dashboard
Common structure:
Raw_Data โ Cleaned_Data โ Analysis โ Dashboard
3๏ธโฃ Understand Rows and Columns
Rows: Run horizontally. Identified by numbers: 1, 2, 3, 4, 5
Columns: Run vertically. Identified by letters: A, B, C, D, E
Together, they create cells.
4๏ธโฃ Understand Cells
A cell is the intersection of a row and a column.
Examples: A1, B2, C5, D10
If you put Sales in cell C2, then C2 contains the value.
Formula example:
=B2+C2 adds the values in B2 and C2.5๏ธโฃ Understand Cell Ranges
A range is a group of cells.
A1:A10 means cells A1 through A10
A1:C10 means the entire area from A1 to C10
Ranges are extremely important because most Excel functions operate on ranges.
Example:
=SUM(B2:B100) adds all values from B2 through B100.6๏ธโฃ Learn the Correct Data Structure
This is one of the most important concepts for a Data Analyst.
One row = One record
One column = One attribute
Example:
Order ID | Customer | Product | Region | Sales
1001 | John | Laptop | North | 80000
1002 | Sarah | Mouse | South | 2000
1003 | Mike | Keyboard | West | 5000
This structure makes the data easy to: Filter, Sort, Analyze, Summarize, Create PivotTables, Import into Power BI, Load into databases
7๏ธโฃ Avoid Bad Data Structures
Beginners often format datasets like reports.
Bad: January/North 50000/South 60000 then February below it
Good: Month | Region | Sales with January North 50000, January South 60000, etc.
Now Excel can easily answer: sales by month, sales by region, best performing month.
8๏ธโฃ Learn Sorting
Sorting changes the order in which your data is displayed.
Numbers: Smallest โ Largest or Largest โ Smallest
Text: A โ Z or Z โ A
Dates: Oldest โ Newest or Newest โ Oldest
Example: 50,000 transactions โ Sort Sales โ Largest to Smallest to find biggest sales.
9๏ธโฃ Learn Filtering
Filtering allows you to temporarily display only the records you need.