Working with data

01The problem

The managing director wants one number to start: Kolanut's total revenue since January 2025. The orders file has quantity, price and discount on each line, but no revenue column. You'll add one. Before that, the file has to come into Excel correctly.

02The concept

Two ways to open a CSV

Excel's guesses can go wrong: codes with leading zeros lose them (007 becomes 7), long numbers turn into 1.2E+15, and day-first dates can be read as month-first. Here is what happened when Kolanut's messy customer export was opened by double-clicking:

1. Phone lost its leading zero (08089165939 became 8089165939), and numbers in international format became 2.34915E+12. Those digits are gone for good once you save.
2. Credit Limit shows ₦2,050,000: the ₦ sign was read with the wrong text encoding, so the column can't be turned into numbers.

Importing through Data → From Text/CSV lets you catch these before they spread.

Values vs formatting. A cell's value is what's stored; its format is how it's shown. 0.19 formatted as a percentage shows 19%. Formatting never changes the value, so rounding a display to 0 decimals doesn't round the number used in calculations.

Calculated columns in a Table. Type a formula once in a Table column and Excel fills it down for every row, using structured references: [@quantity] means "the quantity in this row".

Kolanut revenue for one order line:

=[@quantity]*[@unit_price]*(1-[@discount_pct]/100)

03Example

Order line 10001: 14 packs × ₦18,600 × (1 − 0 ÷ 100) = ₦260,400.

A 5% discount line of 20 packs at ₦13,200: 20 × 13,200 × (1 − 5 ÷ 100) = 264,000 × 0.95 = ₦250,800.

04Walkthrough

1. In a new workbook, go to Data → From Text/CSV (keyboard: Alt, A, F, T) and choose orders.csv. In some versions it's under Data → Get Data → From File → From Text/CSV.

2. Excel shows a preview:

   

   1. File Origin: the text encoding. For files containing ₦ or other special characters choose 65001: Unicode (UTF-8).
   2. Delimiter: what separates columns. CSV means Comma.
   3. Data Type Detection: how Excel decides column types.
   4. The preview. Check that order_date shows dates and the numbers are numbers (right-aligned). The dates appear in your computer's date format; here, day first.
   5. Load puts the data on a new sheet as a Table. Transform Data opens Power Query to clean it first.

   Click Load.

3. Rename the sheet Orders and the Table Orders (Table Design → Table Name).

4. In the first empty column to the right, type the header revenue in row 1.

5. In row 2 of that column type the formula above and press Enter. Excel fills it down all 4,266 rows.

6. Select the column and format it: Home → Number → Comma Style, and reduce decimals to 0.

7. View → Freeze Panes → Freeze Top Row, so the headers stay visible as you scroll.

Excel may display the formula as =[@quantity]*[@[unit_price]]*(1-[@[discount_pct]]/100), with extra brackets around column names that contain an underscore. Both forms mean exactly the same thing.

To get the total, click in any empty cell and type:

=SUM(Orders[revenue])

Orders[revenue] means "the whole revenue column of the Orders table". It stays correct if rows are added.

05Practice

06Check your understanding