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

Microsoft Excel is a spreadsheet software that allows users to organize, analyze, calculate and visualize data. Despite the rise of Python, R and SQL, Excel has remained a foundational data analysis tool across industries because of it's low learning curve, powerful features like pivot tables, formulas, functions and quick charts and because no coding experience is needed to navigate or see results.

In this article, we will explore navigating the grid, essential formulas, data filtering and cleaning messy data using Excel's built-in features.

Understanding The Excel Interface

The excel interface consists of these core elements.

• The Ribbon & Tabs
  • The toolbar across the top that contains the primary commands organized into tabs (Home, Insert, Draw, Page Layout etc.)
• The Quick Access Toolbar
  • The icons on the top left corner to Save, Undo and Redo.
• Formula Bar
  • The area above the grid where the content or the formula of the selected cell appears.
• Name Box
  • Displays the address of the selected Cell (e.g. B3)
• The Grid
  • This is the visual network of intersecting horizontal lines and vertical lines that creates the workspace. The Grid is made up of
    • Rows - Horizontal lines of cells labelled vertically with numbers (e.g. Row 1)
    • Columns - Vertical line of cells labelled horizontally with letters (e.g. Column A)
    • Cells - A single box where a row and a column intersect (e.g. B4)
    • Range - A group of selected cells (e.g., A1:C3).
• Worksheet Tabs
  • The tabs at the bottom that show the name of the spreadsheet and also allow you to navigate between the sheets in a workbook.

Essential Navigation & Keyboard Shortcuts

Keyboard Navigation:

• Arrow keys – Move one cell at a time
• Ctrl + Arrow key – Jump to the end of the data region
• Tab – Move one cell right
• Enter – Move one cell down
• Ctrl + S – Save the file

Mouse Navigation:

• Click to select a single cell
• Click & Drag to select multiple cells
• Scroll to move vertically or horizontally across the sheet

Efficiency Combinations (Keyboard & Mouse):

1. Ctrl + Right Arrow to jump to the end of row
2. Ctrl + Down Arrow to jump to the bottom
3. Ctrl + Shift + Arrow Keys to select ranges efficiently

The Data Environment

Data Types.

Excel supports 4 primary data types. i.e.,

• Text - Any non-numeric or character based information e.g. names, addresses, descriptions).
• Numbers - Any values that can participate in mathematical calculations.
• Dates and Time - Values used to track time-based events and calculations
• Boolean - Logical values, either (TRUE or FALSE) usually generated from logical formulas.

Entering Data

We can enter data anywhere in the grid by:

• Clicking on a cell
• Typing in our value (e.g. Hello)
• Press Enter to move down or Tab to move right
• Double click on the cell or selecting it and pressing F2 to edit existing content. ### Formatting Basics. Formatting changes how the data looks without changing the values stored in the cells, making the data visually clear and more professional.

Text Formatting.

• Selecting the Cell
• Navigate to Home > **Font Group
• Clicking Bold (Ctrl+B), Italic (Ctrl + I), Underline (Ctrl + U)
• Change Font, Font Size, and Font Colour.

Number Formatting

• Select cells with numeric data.
• Navigate to Home > Number section
• Choose the appropriate format:
  • Number (with decimals)
  • Currency (KES, $, etc.)
  • Percentage
  • Date formats

Alternatively, we can access the Format Cells Dialogue by (Ctrl+1)

Data Cleaning and Sorting.

Data Cleaning is the process of formatting, or removing incorrect, duplicate or incomplete data within a dataset. Sometimes, especially when working with large datasets, data can get misplaced, mistyped or mislabeled. Cleaning ensures that the dataset is accurate and consistent.

Data cleaning prevents making decisions based on flawed data, catches and fixes errors early and provides clean inputs for formulas and charts.

For Practice, I used a spreadsheet containing HR data, applying a combination of built-in excel tools and formulas to clean it.

Best Practice: Always preserve your original raw data. Make all required cleaning changes on a duplicate worksheet or separate file.

How to Duplicate Your Data

1. Click anywhere in the spreadsheet and press Ctrl + A to select all cells.

2. Press Ctrl + C to copy.

3. Click the + button at the bottom of the window to create a new sheet.

4. Click cell A1 in the new sheet and press Ctrl + V to paste.

5. Double-click the sheet tab and rename it to Cleaned_HR_Data.

This gives us a canvas to work in.

The Data Cleaning Workflow

We did the cleaning in distinct stages.

• Analyzing the Data:

  Here we checked the data for visible errors and inconsistencies. In this dataset, we found mixed data types in numeric fields (text mixed with numbers), missing/blank values, duplicate entries, currency formatting errors, typos and misspellings etc.

• Removing duplicates:

  The second step was to delete identical rows or entries that were recorded more than once. I achieved this by:

• Selecting the entire spreadsheet using Ctrl+A

• navigating to the Data Tab and Clicking Remove Duplicates.

• Confirming the target columns and clicking OK

Fixing structural errors:

This step involved correcting typos, standardizing inconsistent capitalization (e.g., changing "Nairobi", "nairobi", and "NAIROBI" into one uniform format), merging mislabeled categories and trimming extra spaces in the text columns. Certain categories like employer ID also had inconsistent formatting.

Standardizing Codes:
Employer ID inconsistent formatting was remedied by selecting the column, then applying an auto filter using Shift+Ctrl+L and checking the Drop down to see the range of values I had. Then I selected the Column, and Ctrl+H to find and replace and replaced all EMP- with a Blank to fix the formatting.

• Trimming Spaces and fixing Capitalization: TRIM() removes extra spaces between words. Name Entries were fixed by using =PROPER(TRIM(TEXT)) on the first and last name columns for both text length and proper case and then replacing the names in both columns using Paste (Values)

Handling missing values:

Deciding whether to filter out empty cells, fill them with zeroes, or input an average value.
Empty/missing values in the remaining text and numerical Columns were replaced by unknown converting them into unknown values using the Ctrl+H find and replace feature.

Standardizing formats:

Ensuring all dates followed a consistent format (e.g., YYYY-MM-DD) and numbers are converted from plain text into actual numeric formats.

• Salary & Bonuses: Highlighted the numeric columns and pressed Ctrl + 1 to set the format to Currency (Ksh).

• Dates: Selected the date column and set the formatting to DD/MM/YYYY.

To verify that we had successfully cleaned and validated our data, we applied auto filters Ctrl + Shift + L to the entire Spreadsheet and sorted each column to confirm that our categories were uniform, and our numbers were formatted correctly and whether we had any lingering errors. We then had a 'clean' dataset that was ready for analysis.