Excel for analysts

01The problem

Kolanut Distribution's sales team lives in Excel. Every report the managing director reads started as a spreadsheet. Analysts who move around Excel slowly (scrolling, clicking through menus, retyping) spend their day on mechanics instead of answers.

This course takes you from opening a raw file to a finished analysis. First, the ground rules for working in Excel like an analyst.

02The concept

Why Excel? It's on almost every office computer, everyone can open your file, and it covers the full cycle: import, clean, calculate, summarise, chart. Larger data goes into databases and Power BI, but Excel stays the everyday tool.

Workbooks, worksheets, cells and ranges

Four words you'll use in every lesson:

Columns are lettered A, B, C … Z, then AA, AB and so on, up to XFD (16,384 columns). Rows are numbered 1 to 1,048,576. That limit, about a million rows, is one reason very large data moves to a database.

What can go in a cell

The alignment is a free check: if a column of numbers has some values sitting on the left, those are text, and SUM will skip them. You'll use this check in lessons 2 and 7.

Ranges versus Tables

A plain range is just cells. A Table (Insert → Table, or Ctrl + T) is a range that Excel knows is a dataset:

This course turns every dataset into a Table first. It's the habit that saves the most mistakes later.

The Excel window

This is Excel with Kolanut's orders loaded as a Table, exactly as you'll set it up in this course:

1. File name. The workbook you're in (Kolanut-sales). Click it to rename the file or see where it's saved.
2. Ribbon tabs. Home, Insert, Formulas, Data (import, sort, filter, remove duplicates), View… Extra tabs such as Table Design appear only when you click inside a Table.
3. The ribbon. The commands for the selected tab, in labelled groups.
4. Name Box. Shows the selected cell (H2). Type an address like A4000 and press Enter to jump there.
5. Formula bar. Shows what's really in the selected cell. Here H2 holds a formula, not a typed number: the revenue calculation you'll write in the next lesson.
6. Table header row with filter buttons (the small arrows). The data is a Table, so every column can be sorted and filtered.
7. Sheet tabs. One per worksheet: Orders, Customers, Products. Click to switch, or use Ctrl + Page Up / Page Down.
8. Status bar. Shows the mode (Ready), and when you select numbers it shows their Sum, Average and Count. Filtered tables report "X of Y records found" here.

What version? This course uses Microsoft 365 or Excel 2021 or later, which include XLOOKUP, FILTER and UNIQUE. Google Sheets works for almost everything too; where menus differ, we say so.

Shortcuts worth learning today (Windows; on a Mac use ⌘ for Ctrl)

03Example

Download Kolanut's three files. You'll use them through the whole course:

• orders.csv: one row per product on an order (4,266 rows).
• customers.csv: one row per customer, with channel, region, city and sales rep.
• products.csv: one row per product, with category and current list price.

04Walkthrough

1. Open customers.csv in Excel.
2. Click cell A1 and press Ctrl + ↓. You land on the last customer. The row number minus one (the header) is the number of customers.
3. Press Ctrl + → from A1 to find the last column.
4. Click anywhere in the data and press Ctrl + T, confirm My table has headers. The data becomes a Table: banded rows, filter buttons, and it will grow automatically when you add rows. Most of this course works with Tables.
5. With the Table selected, look at Table Design → Table Name and rename it Customers. Named tables make formulas readable later.

05Practice

06More practice

Optional drills. They don't count towards the certificate, but they're the fastest way to make this lesson stick. Several use a different dataset from the lesson on purpose: if you can do the same thing on unfamiliar data, you've really learned it.

07Check your understanding