Excel Fundamentals for Data Analysts

Why Excel Is Still Essential for Data Analysts

Despite the rise of Python, SQL, and specialised data tools, Microsoft Excel remains one of the most widely used tools in data analysis. It is installed on virtually every business computer, requires no setup, and allows you to explore, clean, and visualise data quickly. Many organisations store and share data in spreadsheet format, and most business stakeholders are comfortable receiving reports in Excel or Google Sheets.

For a data analyst, Excel proficiency is a baseline expectation. This module covers the Excel skills that are actually used in day-to-day analytical work.

The Excel Interface for Analysts

Before diving into formulas, understand the Excel interface from an analyst's perspective:

Workbook and Worksheets: An Excel file is a workbook. Each tab is a worksheet. Organise your work across sheets: raw data on one sheet, analysis on another, and output or dashboard on a third. Never modify your raw data sheet.

Rows, Columns, and Cells: Data is stored in cells identified by column letter and row number (A1, B5, C12). Rows typically represent records (one row per customer, order, or event). Columns represent attributes (name, date, amount, category).

The Formula Bar: Shows the formula or value in the selected cell. Always look here to understand what a cell actually contains.

Named Ranges: You can assign names to cell ranges (e.g., naming B2:B1000 as "Sales") and reference them by name in formulas. This makes formulas more readable.

Essential Navigation and Shortcuts

Speed matters when working with large datasets. Master these keyboard shortcuts:

Formatting Data as a Table

One of the most important habits to develop is formatting your data as an Excel Table (Insert > Table). This provides:

• Automatic column header filtering
• Structured references in formulas ([@ColumnName] instead of cell references)
• Automatic expansion when you add rows
• Easier referencing for PivotTables

To create a table:

1. Click anywhere in your data range
2. Press Ctrl + T
3. Confirm the range and whether your data has headers
4. Give the table a meaningful name in the Table Design tab

Cell References: Relative, Absolute, and Mixed

Understanding cell references is fundamental to writing formulas that work correctly when copied across rows and columns.

Relative reference (A1): Adjusts automatically when the formula is copied. If you write =A1+B1 in C1 and copy it down to C2, it becomes =A2+B2. Use for most calculations.

Absolute reference ($A$1): Does not adjust when copied. Use when referencing a fixed value like a tax rate or a lookup table. Press F4 to toggle the dollar signs.

Mixed reference ($A1 or A$1): Locks either the column or the row but not both. Useful for building multiplication tables or cross-reference matrices.

Example:

=B2 * $E$1   -- B2 is relative (moves with the row), $E$1 is fixed (always refers to the tax rate)

Data Types in Excel

Excel handles several data types, and getting them right matters for analysis:

Numbers: Stored as numeric values. Right-aligned by default. Ensure financial data is formatted as numbers, not text.

Dates: Excel stores dates as serial numbers (the number of days since January 1, 1900). This allows date arithmetic. Ensure date columns are formatted as dates, not text strings.

Text: Left-aligned by default. Numbers stored as text cannot be summed. Use VALUE() to convert text to numbers.

Logical values: TRUE and FALSE. Used in logical formulas.

Errors: #DIV/0!, #VALUE!, #REF!, #N/A. Each has a specific meaning. Learn to recognise and handle them.

Freeze Panes and Data Navigation

When working with large datasets:

Freeze Panes (View > Freeze Panes): Keeps your header row visible as you scroll down. Select the row below your headers, then freeze. Essential for datasets with more than 30-40 rows.

Filter (Data > Filter or Ctrl + Shift + L): Adds dropdown filters to each column header, allowing you to quickly view subsets of data.

Sort: Sort data by one or multiple columns. For multi-level sorting (sort by region, then by date within each region), use Data > Sort.

Basic Data Quality Checks

Before any analysis, check your data quality:

1. Check for blanks: Use Ctrl + G > Special > Blanks to highlight empty cells
2. Check for duplicates: Data > Remove Duplicates or use COUNTIF to find repeated values
3. Check data types: Look for numbers formatted as text (left-aligned numbers)
4. Check date formats: Inconsistent date formats are a common source of errors
5. Check ranges: Verify minimum and maximum values make sense for each column

Key Takeaways

• Excel remains essential for data analysts because it is universally available, requires no setup, and is familiar to business stakeholders.
• Format your data as an Excel Table (Ctrl + T) from the start to enable automatic filtering, structured references, and automatic expansion.
• Understand the difference between relative ($A1 or A1), absolute ($A$1), and mixed references to write formulas that work correctly when copied.
• Excel stores dates as serial numbers, enabling date arithmetic -- ensure date columns are properly formatted as dates, not text.
• Always perform basic data quality checks before analysis: look for blanks, duplicates, type mismatches, and values outside expected ranges.

Practice Exercise

Open a new Excel workbook and create a dataset of at least 20 rows representing sales data with columns: Date, Product, Region, Units Sold, Unit Price. Format it as an Excel Table. Then:

1. Add a calculated column "Total Revenue" using a formula that multiplies Units Sold by Unit Price
2. Freeze the header row
3. Apply filters and sort by Region, then by Date within each region
4. Use Ctrl + End to identify the last row of your data