DEV Community

egesa-Ian
egesa-Ian

Posted on

Getting Started with Excel: From Basics to Data Cleaning

#tutorial #datascience

Getting Started with Excel: From Basics to Data Cleaning

Data plays a key role in everyday decisions in nearly every industry. But before you can get useful insights from raw data, you need to organize and clean it first.

If you are starting out in data analytics, software development, or finance, Microsoft Excel is still one of the easiest and most useful tools for getting your data ready.

This beginner guide will walk you through the basics of Excel, show you how to enter data correctly, and teach you simple ways to clean up messy datasets.

1. Navigating the Excel Environment

Before you start working with data, let’s look at the main parts of the Excel interface:

  • Ribbon: The top menu toolbar containing tabs (Home, Data, Formulas, etc.) and commands.
  • Quick Access Toolbar: Located at the top-left corner, giving fast access to frequent actions like Save, Undo, and Redo.
  • Rows & Columns: Rows run horizontally (numbered 1, 2, 3...), while columns run vertically (lettered A, B, C...).
  • Cells: The intersection of a row and a column (e.g., A1, B12).
  • Formula Bar: Displays the true underlying value, text, or formula of the selected cell.
  • Worksheets: Individual tabbed pages within a single Excel workbook.

Tip: Keep your workbook tidy by using separate sheets for raw data, cleaned data, and your analysis. For example:

  • Sheet 1: Raw Data
  • Sheet 2: Cleaned Data
  • Sheet 3: Analysis & Visuals

2. Best Practices for Data Entry

Good analysis begins with clean data. You can set rules for what people can enter in a cell by using Data Validation.

Setting Up Data Validation

  1. Select the cell or column range.
  2. Go to the Data tab, then Data Tools, and choose Data Validation.
  3. Set your rules (e.g., restrict. For example, you can limit numbers in a Salary column so they must be between 30,000 and 120,000, preventing typos or out-of-range inputs at the source.

3. Practical Data Cleaning Techniques

Most real-world datasets are messy. You might find duplicate entries, inconsistent capitalization, extra spaces, or mixed-up data types. Here’s how you can fix these problems, step by step:

A. Handling Missing Values

To locate missing values across large datasets:

  1. Highlight your header row and click Filter under the Data tab.
  2. Click the dropdown arrow on a column and scroll to check for (Blanks).

Ways to fix missing values:

  • Fill in the accurate missing data if available.
  • Delete incomplete rows if they lack critical information.
  • Leave as (Blanks) or label explicitly as Unknown if required by your pipeline.

B. Removing Duplicate Records

Duplicate records can mess up your counts and averages.

  1. Select your data range.
  2. Go to the Data tab and select Remove DuplicatesPick the column that has unique values, like Employee ID or User ID, so you don’t remove any valid records by mistake.ed.

C. Standardizing Inconsistent Text Case

If names or words are not capitalized the same way (like ndegwA, NDEGWA, Ndegwa), it can cause problems when grouping or searching. You can use Excel’s text functions to fix this:

  • Proper Case: Capitalizes the first letter of each word.

=PROPER(A2)

  • Uppercase:

=UPPER(A2)

  • Lowercase:

=LOWER(A2)

d-stripping-unwanted-white-spaceD. Stripping Unwanted White Space

Extra spaces can show up when you type data by hand or copy it from websites. The TRIM function removes spaces at the beginning, end, or in the middle of text:

=TRIM(A2)

Standardizing Data Types

Make sure each column only has the type of data it’s supposed to:

  • Text: Names, categories, IDs.
  • Number: Counts, ages, durations.
  • Currency: Financial figures.
  • Date: Standardized date formats (e.g., YYYY-MM-DD).

To change a daTo change a data type, select the column, go to the Home tab, open the Number dropdown, and pick the right format.ev.to/new#4-sorting-amp-filtering-data4. Sorting & Filtering Data

Once your dataAfter you clean your data, you can use sorting and filtering to organize it and look at certain parts more closely.to/new#sorting-dataSorting Data

  1. Click inside your dataset.
  2. Go to the Data tab and click Sort.
  3. Pick the column you want to sort and choose the order, like A to Z or Smallest to Largest.

Filtering Data

  1. Select your header row, then go to the Data tab and click Filter.
  2. Click the dropdown arrow on any column header to filter by certain values or set text or number conditions.

Conclusion

Cleaning your data is a key first step before you can do any real analysis. If you get comfortable with Excel’s main features like text functions, data validation, and removing duplicates, your data will be much more reliable.

Top comments (0)