Getting Started With Excel For Data Analytics: From Basic Data to Cleaning

Introduction

This week at Luxdevhq, our data analyst group, we dove into Excel, learning how to navigate it and use its tools as real analysts. Join me as I explain what we covered this week. But before we go any further, we must first understand what Excel is.

What is Excel

Excel is a spreadsheet software program designed by Microsoft that allows you to collect, organize, analyze, calculate, and visualize data efficiently. It allows users to create charts,and manage large datasets, making it one of the most widely used tools in data analysis and business reporting.

1 Excel Fundamentals

1.1 Workbooks, Worksheets and Cells

Excel file is called a workbook, and a workbook is made of one or more worksheets(often called sheets), visibly seen as tabs at the bottom of the screen. Each worksheet is a grid of cells arranged into columns labelled with letters A, B, C... and rows labelled with numbers:1,2,3...
Every cell has a unique address formed by its column and row, such as A2 or G12, and that address is what formulas use to refer to data elsewhere in the sheet.

1.2 Data Types

• Numbers: plain quantities use in calculations e.g: 2, 56, 134

• Texts: words or codes that Excel won't try to calculate e.g Nairobi

• Dates: stored internally as serial numbers;1/12/2000

• Formulas: expression begins with equals sign(=) that calculates result from other cells. e.g (=A2+C2)

  The Ribbon and Navigation

  The ribbon is the toolbar across the top that contains all commands organized into tabs (Home, Insert, Page Layout, Formulas, Data, Review) being the most commonly used.
  Home tab covers formatting(fonts,colors,number formats),the formula tab gives access to the function library, and the Data tab is where most data-cleaning tools are found, including Removing Duplicates, Text to Columns, Data Validation, and Sort& Filter.

1.4 Basic Formulas and Functions

A formula is an expression that calculates the value of a cell. Formulas begin with an equals (=) sign and can include:

• Constants (numbers like 100)

• Cell references(like A1, B2)

• Operations like (+, -, *, /)

• Built-in functions (like SUM, AVERAGE)

FUNCTIONS
The following are some of the functions we went through this week:

• =SUM() - adds a range of numbers

• =AVERAGE() -calculates the mean of a range

• =COUNT()/=COUNT() -counts numeric cells, or non-blank cells respectively.

• =COUNTIFS() and =SUMIFS() -conditional counting and summing

2 Data Cleaning Exercise

This section we will walk through a realistic sample dataset: fifteen order records from a small online store selling electronics and furniture. This dataset was built in away that it contains different problems every analyst runs into early in a project; inconsistent capitalization, stray whitespace, mixed data formats, a duplicate record, missing values, and a data entry error

2.1 The Raw Data

Below is the "Raw Data" worksheet exactly as it may arrive from an export or a manual entry log. A few problems can be noted immediately, a few become obvious once formulas are applied to check for them:

2.2 Standardizing Text with TRIM, PROPER, and LOWER

These text functions mostly do most of the work when cleaning inconsistencies text entries:

• =TRIM() -removes leading, trailing, and repeated internal spaces (e.g, turns " Peter Otieno" into "Mary Achieng")

=PROPER() -converts text to Title Case, so "john mwangi" and "MARY ACHIENG" to "John Mwangi" and "Mary Achieng"

• =LOWER() -converts texts to lowercase, useful for standardizing email addresses so "[e-mail] and [e-mail] are recognized as equal. ### 2.3 Handling Dates and Catching Format Errors Data in Excel can sometimes look correct but actually can be stored incorrectly, which can cause problems when calculating and sorting data. e.g a date like 13/06/2023 might be understood differently depends on Excel's regional settings. The formula checks whether Excel can understand the entry as a real date. If it's a real date, it changes it format DD-MMM-YYYY such as 13-05-2023 ### 2.4 Flagging Duplicates, Blanks, and Errors This step is about finding problems in a data automatically instead of checking rows manually. Three checks are used:

• Duplicate detection: Finds records that appear more than once and marks them as "Duplicate"

• Blank Detection: Finds cells where information is missing and marks them as "Yes".

• Price validation: Checks whether the price is negative. If it is, it shows "Negative Price"; otherwise, it shows OK.
  Conditional formatting is used to highlight these problems in red problems making them easy to spot:

  2.5 Producing the Final, Clean Dataset

  With every issue identified, the final step combines Excel's inbuilt data; Remove duplicates with the corrections suggested by the flag columns: the duplicate order (1007) was removed, the negative price on order was corrected to its intended positive value, missing quantities and regions were filled after checking the original records, and every data was rewritten in single consistent format. The result is a clean analysis ready table:
  

Key Takeaways

Data cleaning is very important in data analysis. Excel functions like TRIM, PROPER, LOWER, COUNTSIFS help fix common data problems.

• Use conditional formatting to highlight problems so that they are easy to see.

• Always check dates. A date may look correct but could be stored as a text, which can cause errors.

• Use formulas for analysis instead of manually typing.