Microsoft Excel can be quirky at times. Opening a CSV by double-clicking is the fastest way to ruin your data.
Opening CSV files in Excel is deceptively tricky. Many candidates open CSVs by double-clicking the file — a reflexive move that seems reasonable. The trap? All the data gets squeezed into a single row, making it unusable for analysis. The actual job is to import CSV files properly, preserving the structure so you can work efficiently within your limited test time.
If you attempt to fix a badly opened CSV by manually splitting columns or copying data, you waste precious minutes you do not have. The cleanest way to open a CSV in Excel is by importing it — not opening it directly.
The trap of double-clicking CSVs
When you double-click a CSV on Mac or Windows, Excel often ignores delimiters and imports the entire line as one cell. The data looks like a giant string of text in cell A1. This breaks all your formulas and pivot table attempts downstream.
This problem is consistent across Excel versions and operating systems. The pattern is consistent: Excel defaults are not your friend with CSVs.
Here is what I tell PMs: Spend 30 seconds doing it right, or spend 10 minutes fixing it later. In an interview or test scenario with a 2-hour limit, that 10 minutes can be the difference between passing and failing.
How to properly import CSVs into Excel
Follow these steps to avoid the formatting mess:
- Open a blank Excel workbook.
- Go to File → Import.
- Choose the CSV option, then select the CSV file you downloaded.
- When prompted, select Delimited data and click Next.
- Click Finish. You do not need to tweak any other settings. Excel will format the sheet properly after this step.
- Choose Existing Sheet to place the data and click OK.
- Your data will now be correctly split into columns.
This method works on both Mac and Windows across different Excel versions. It is a universal pattern for cleanly importing CSVs without data corruption.
What to do after import: normalizing your date column
Once your CSV is imported, you will notice the first column contains dates — but some rows may have blank cells in this column. This is common in datasets where dates are merged or only shown for the first entry of a group.
This is a subtle trap that many candidates miss. Blank date cells break sorting, filtering, and pivot tables. You must fill down the dates so every row has a valid date.
How to do this quickly:
- Select the first filled date cell.
- Copy it (Ctrl+C or Cmd+C).
- Select the blank cells below that should have the same date.
- Paste (Ctrl+V or Cmd+V).
Repeat this for all date groups. This step ensures your data is normalized and ready for pivot tables and calculations.
Organizing your workbook: renaming the master sheet
You will create multiple sheets during your analysis — pivot tables, filtered views, charts. To keep your file organized, rename the original imported sheet to "Master" or something similarly descriptive.
This saves time searching for the source data as you build your analysis and helps avoid mistakes referencing the wrong sheet.
Why this matters for analytical interviews
In product management interviews or tests, you often get CSV files with raw data. Your ability to quickly and correctly import, clean, and prepare this data is critical.
I have watched thousands of candidates struggle because they tried to open CSVs by double-clicking and then spent valuable time fixing formatting errors. This is what week one looks like for most new PMs — and it is avoidable.
The actual job is to prepare your data so you can focus on analysis and insight generation, not data cleaning.
From the field: Excel is the PM's best friend
From a Pragmatic Leaders AMA:
"A product manager cannot survive without the ability to crunch data or be friendly with data. Microsoft Excel is the best friend for most PMs. Some use advanced tools, but Excel is usually good enough — as long as you know how to import and prepare data properly." — Talvinder Singh
Learning these basics is the foundation for all higher-level analytical work.
Test yourself: Import and prepare a CSV
You have downloaded a CSV file from a marketplace analytics platform on your Mac. You double-click it and see all data in one row. You have 2 hours to analyze the data and produce a report with pivot tables and charts.
The call: What is the correct first step to prepare your data for analysis in Excel?
Your reasoning:
Where to go next
- If you want to learn how to summarize data interactively: Pivot Tables in Excel
- If you want to practice analyzing marketplace data: Marketplace Analytics Case Studies
- If you want to build your SQL fundamentals complementary to Excel: SQL for Product Managers
- If you want to understand how to structure data-driven product decisions: Data-Driven Product Management