Webbo3 Data Analytics · Cohort 2 · Excel Track · Week 1, Live 2

The Excel Interface, Data Types and Importing a CSV

Today we open Excel together. By the end you can name everything on the screen, tell text from numbers from dates at a glance, and bring TechPlug into Excel without damaging it.

Last class was about thinking. This class is about the tool. You cannot clean what you cannot see, and you cannot see clearly until the screen stops looking like a wall of buttons.

1. The Excel Window

Definition

The Excel window is everything you see when a workbook is open: the commands along the top, the grid of cells in the middle, and the information along the bottom. Every part has one job.

Real-Life Scenario

The dashboard of a car. The first time you sit in the driver's seat, it is a confusing panel of dials. After a week of driving, you glance at the fuel gauge without thinking. The Excel window is the same: learn what each part is once, and you will never have to think about it again.

The Parts of the Window

2. Rows, Columns, Cells, Ranges and Sheets

Definition

A column runs down and has a letter. A row runs across and has a number. Where they cross is a cell, named by its column then its row: C5 is column C, row 5. A block of cells is a range, written as its top-left cell, a colon, and its bottom-right cell: A1:L1071. A sheet is one grid, and a workbook is the file that holds one or more sheets.

Real-Life Scenario

A cinema. Rows are numbered, seats in each row are lettered, and your ticket says "Row F, Seat 12". Excel just swaps the order: letter first, then number. "Book seats F12 to F15" is a range. Each screen in the cinema is a sheet; the whole cinema is the workbook.

The Building Blocks

Where the Data Really Ends

The header sits in row 1, so the first sale is in row 2. That means the last of 1,070 sales is in row 1,071, not row 1,070. It sounds trivial; it is the cause of more wrong totals than anything else in this track. A total over G2:G1070 quietly leaves out the last sale.

Moving Around Quickly

Select column A with Ctrl + Space and look at the status bar: it says Count: 1071. That is 1,070 sales plus the header. Whenever you open a new file, this is the fastest way to see how many records it has.

Now scroll down. Row 1 stays at the top, so you always know which column is which, even at row 250.

3. Data Types in Excel

Definition

Every cell holds one data type, and the type decides what Excel can do with the value. There are three you will meet in every dataset:

• Text: letters, or digits Excel treats as a label. It can be searched and grouped, never added.
• Number: an amount. It can be added, averaged and compared.
• Date: a day on the calendar. Underneath, it is a number, which is why dates can be sorted and subtracted.

Two more appear later: TRUE / FALSE (the answer to a yes-or-no question, from Week 2) and errors such as #N/A (from Week 4).

Real-Life Scenario

A school register has a column of names and a column of test scores. You can work out the class average from the scores, but not from the names. If somebody wrote one score as "seventy-two" in words, the average would quietly leave that student out. Excel works the same way, and it does not warn you.

How to Tell Them Apart at a Glance

This is Excel's default alignment, and it is your quickest test. A number sitting on the left of its cell is almost always a number stored as text.

Type 1 and Type 2: Text and Numbers, and What Breaks

Here are the first five quantities from TechPlug. Their total should be 17. In the second copy, the third value (7) has been typed with an apostrophe in front, which is how a number becomes text. It looks the same. Watch the total.

The total dropped from 17 to 10, exactly the 7 that is stored as text. SUM did not show an error. It skipped the text and gave a wrong answer that looks completely normal. That is the danger: Excel does not refuse dirty data, it just calculates with it.

Type 3: Dates Are Numbers in Disguise

Excel stores a date as a day number, counting 1 January 1900 as day 1. The cell shows you a date because it has a date format; change the format to General and the number underneath appears.

3 March 2024 is day 45,354. That is why you can sort dates, subtract one from another to get the days between them, and group them by month in a PivotTable in Week 4. A date stored as text can do none of those things.

4. Importing a CSV File

Definition

A CSV (comma-separated values) file is a plain text file that holds a table: one record per line, with commas between the fields. It is the most common way data moves between systems, because almost every program can write one and read one.

Real-Life Scenario

A CSV is like a shopping list sent by text message: "rice, 2 bags, 45000". There is no formatting, no bold, no colour, just the facts in order. Whoever receives it decides how to lay it out. Excel is the person receiving the text message.

Transaction_ID,Date,Product_ID,Product_Name,Category,Quantity,Unit_Price,Staff_ID,Staff_Name,Branch,Customer_Type,Payment_Method
TP-0892,2024-03-03,PRD-015,Thermocool AC 1.5HP,Appliances,3,380000,EMP-004,Aisha Musa,Kano,Retail,POS
TP-0785,2024-03-18,PRD-004,Infinix Hot 40,SMARTPHONES,4,75000,EMP-007,fatima garba,Abuja,RETAIL,Credit
TP-0210,2024-03-13,PRD-018,Anker Power Bank 20K,Accessories,7,28000,EMP-007,fatima garba,Abuja,RETAIL,Credit
TP-0343,2024-01-10,PRD-009,"Samsung·43""·TV",Televisions,1,250000,EMP-004,··aisha·musa,Kano,Wholesale,Credit
TP-0238,2024-01-07,PRD-020,Logitech·Wireless·Mouse,Accessories,2,18000,EMP-003,··Tunde·Bello,Ibadan,Retail,Cash
TP-0474,2024-03-03,PRD-009,"Samsung·43""·TV",TELEVISIONS,2,250000,EMP-006,···EMEKA·NWOSU,ENUGU,Corporate,Transfer

There is no formatting in the file at all: no column widths, no bold header, no Naira format. Everything you will see in Excel is Excel's interpretation of these lines. That is why the way you import a file matters.

Two Ways In

Step by Step: Data > From Text/CSV

Excel puts the data on a new sheet named TechPlug_Sales_Data, as a green striped Table, and opens a Queries & Connections pane on the right that remembers where the data came from.

Turn the Table Into a Normal Range

An Excel Table is a powerful feature, but it changes how formulas are written: instead of F2*G2 you would see [@Quantity]*[@Unit_Price]. Every formula in this track is written with plain cell addresses, so that what you see in a lesson is what you see on your screen. So we convert it:

Excel warns that this removes the link to the CSV file. That is what we want: the data is now an ordinary sheet you can work on, and the downloaded CSV stays untouched.

From Now On: the TechPlug Sales Workbook

What you just imported is exactly the Sales Data tab of the TechPlug Sales Workbook. The workbook also holds the Cleaning Process, Product Catalogue and Staff Directory tabs you will need from Week 2, so every class from here works in the workbook. It is already an Excel file, so it simply opens: File > Open, or double-click it.

Save It as a Workbook, Not a CSV

Now the downloaded workbook stays untouched as your original, and every change you make lands in your own copy.

Make It Readable

Formatting changes how a value looks, never what it is. 380,000 is still 380000 underneath, and the formula bar will show you so.

5. What Is This Data About?

Before any formula, an analyst reads a new dataset the way a detective reads a room. Five questions describe almost any business table:

Put it together and you can describe the whole sheet in one sentence: "Each row is one TechPlug sale: who sold it, to what kind of customer, what was sold, where, when, for how much and how it was paid, for 1,070 sales from January to March 2024." If you cannot write that sentence about a dataset, you are not ready to analyse it.

Practical Exercises

Exercise 1: Very Easy

Without looking at the table above, name the ten parts of the Excel window.

Exercise 2: Simple Application

Import TechPlug_Sales_Data.csv with Data > From Text/CSV, convert it to a range, and save it as an .xlsx workbook. Then open the TechPlug Sales Workbook and save your own working copy of it.

Exercise 3: Combination

Format the Date column as yyyy-mm-dd and the money columns with Comma Style, then freeze the top row.

Exercise 4: A Small Real-World Problem

For every column, write T (text), N (number) or D (date) on a piece of paper by looking only at the alignment. Then click one cell in each column and check yourself in the formula bar.

Checkpoint

Next, Week 1, Virtual 2: you open the Sales Data tab on your own, label every column, count the rows, and list every data quality problem you can find, before writing a single formula.