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

Introduction

Because it combines a well-known spreadsheet interface with functions for data cleansing, validation, organizing, filtering and calculation , Microsoft Excel is one of the easiest tools to learn data analytics. Many of the habits of a competent data analyst can be learned straight in Excel by a biginner before switching to more sophisticated tools like Power BI, SQL or Python. The main concept is straightforward: the accuracy of an analysis depends on the quality of the data used.

Objectives

1. Navigate the Excel interface confidently i.e the use key components (ribbon, cells, sheets, tabs,rows and columns,).
2. Understand formatting and data types i.e difference between text, numbers, dates, and currency, and appropriate formatting for correct data representation.
3. Enter and organize raw data effectively.
4. Identify and correct common data quality issues
5. Apply basic formulas and functions.

Understanding Excel and its Environment

The excel environment is formed if form of excel workbook, worksheets, rows and columns and lastly a cell. An Excel workbook is an excel file containig multiple worksheets in this case the Kenya Hospital Health record represents a workbook. A Worksheet consists of columns and rows, in the below attachement staging and Cleaned and consequently their interection will form a cell. In excel each individual cell has a unique address, example A1 in the below attachement.This reference is very important because we use formulas to retrieve and perform calculations.

Maintaining a straightforward rectangular dataset structure one variable per column, one observation per row, and a single header row at the top is crucial for data analytics, for example: patient, first name, last name, ID etc as highlighted below.

Excel Ribbon

This is the toolbar at sitted at the top of Microsoft Excel.It has the commands and tools that a data analysts needs to create,format, edit and analyse data.

The main parts incluse;

• File-This is used to open, save, print and finally close excel files.

• Home-Has common tools like copy, paste,formatting i.e font and number formattind and alignment.

• Insert-Applied when adding tables, pictures,tables,pivottables and objects.

• Page Layout-This sets page margins,size,printing options and orientation.

• Formulas-Has tools to create and manage functions and formulas.

• Data-Sorting, removing duplicates, filtering and data cleaning.

• Review-Provides opportunities for spelling checks, worksheet protection and comments.

• View-Controls worksheet appearance i.e zooming and additional view options.

Entering, Formatting, and Organizing Data

Excel can save text, numbers, dates, and logical values. Excel processes different types of data differently, thus it is important to choose the correct type. A real Excel date can be sorted chronologically and utilized in calculations, however a date entered in plain text may not perform as expected. Quantity should also be numerical if it is to be utilized in arithmetic.Formatting should convey meaning. Dates should be formatted consistently as dates, prices as numbers, and percentages as percentages.

Entering Data

This is adding information in excel. This data may include text, formulas,numbers and dates.

Data Formatting

Changing the look and appearance of data to easy interpretation and understanding, examples include;

• Making column head bold

• Add borders

• Adjust row or column width

• Changing font size

Organizing Data

Arranging data for easy management, retrieving and analysis. The most common command used is Sort and Filter
Data → Sort → Largest to Smallest
Data → Filter → Gender → Female

Identifying and Removing Duplicates

Duplicate records might increase totals. Excel's Data > Remove Duplicates option allows the analyst to select the columns that constitute uniqueness.Before deleting duplicates, determine why they exist. Two similar-looking rows might represent legal independent transactions, therefore duplicate elimination should adhere to a business standard.

Filtering and Sorting Data in Excel.

This are crucial excel skills that helps a data analyst to orgainize and find information in large datasets.

Sorting Data

This is the process of arranging data in a specific order.

Consider the above data set, we can sort:

• Patient First Name: A-Z OR Z-A

• Age:Largest to Smallest or Smallest to Largest.

Filtering Data

This allows display of specific records which meets a specific conditons while temporarily hidding other records.

In the above dataset we can filter

• Patients older than 50

• Female patients only.

Data Cleaning

Finding and fixing erroneous, inconsistent, incomplete, duplicated, or improperly structured data is known as data cleaning. It is rare for actual organizational data that is ready for analysis hence data cleainig is very important.

Extra spaces, incorrect capitalization, blank values, text-based integers, inconsistent status values, and duplicate records are always present in any dataset. In this case extra spaces causes data matching problems when creating pivot tables or data visualization. We deploy Excel TRIMfunctions which removes extra spaces.Additonally CLEANis applied to eliminate non-printing data characters.
Case consistency is paramount when cleaning data with names of persons, cities etc. We use PROPER,UPPERand LOWERfor text standardization.

When data is imported from external databases or systems in form of numbers might be stored in text form. These values may look numeric but when formulas are used for calculations, they they behave differently. We deploy excel Text to colums of VALUEto convert numeric text to a number. The below is a summary of the basic data cleaning workflow and as we advance and learn more about excel, we will come across better and complex cleanig procedures.

1. Import raw data and save it in your project folder.
2. Inspect the data types and columns
3. Convert the data range into table
4. Sort and filter to identify unsual records.
5. Standardize data by removing unnecessary spaces.
6. Check the missing values.
7. Check numeric fields and convert to numeric data types.
8. Remove duplicates.
9. Conduct data validation.

Conclusion.

Excel abilities from Week 1 offer a useful basis for data analytics. A beginner data analyst may deal with structured data with confidence if they understand worksheets, cells, tables, formatting, formulas, sorting, and filtering.The key takeaway is straightforward: excellent analysis begins with good data. Excel allows for the practical application of this before advancing to more sophisticated analytics systems.