Data Import in Excel: Solving Common Import Errors and Validation

You import data from CSV, a database or the web. Numbers become text. Dates show serial numbers. Swedish characters (å, ä, ö) turn into strange symbols. All the data ends up in one column.

This is the guide that solves your import problems. You will learn to import data correctly from the start and to fix common errors after import. With the right method, an import takes 30 seconds instead of 30 minutes of manual fixing.

For general troubleshooting, see the Excel troubleshooting guide. For other problems, read common Excel problems.

Common data import problems

Problem 1: Numbers are interpreted as text

Symptoms: – Numbers are left-aligned (instead of right-aligned) – Green triangle in the top left corner – Formulas do not work (e.g. SUM gives 0)

Cause: The cell was formatted as text before import, or the data contained a leading apostrophe.

Problem 2: Dates become serial numbers

Symptoms: – Instead of “2026-01-30” you see “44 927” – Or: dates are interpreted incorrectly (US format: month/day/year)

Cause: The date format does not match what Excel expects, or the cell is formatted as a number.

Problem 3: Swedish characters become strange symbols

Symptoms: – “å” becomes “Ã¥” – “ö” becomes “Ã¶” – “ä” becomes “Ã¤”

Cause: Wrong character encoding on import (UTF-8 vs ISO-8859-1).

Problem 4: All data in one column

Symptoms: – You expect a table layout but everything ends up in column A – Data is separated by semicolons or tabs but Excel does not see it

Cause: Excel guesses the wrong delimiter on import.

Problem 5: Extra spaces

Symptoms: – The data looks right but formulas do not work – “Stockholm” does not match “Stockholm” (extra space)

Cause: Leading/trailing spaces from the source system.

The right method: Import from CSV/text

Wrong method (double-clicking the CSV): Excel guesses the format and often gets it wrong.

Right method (Data → From Text/CSV):

Step by step

Open Excel (blank)
Data → Get Data → From File → From Text/CSV
Select the file
A preview opens:
- File Origin: Select 65001: Unicode (UTF-8) for Swedish characters
- Delimiter: Check that the right delimiter is selected:
  - Comma (,) — Common CSV standard
  - Semicolon (;) — Swedish CSV standard
  - Tab — TSV files
  - Other — Enter your own character
- Data type detection: Select “Based on entire dataset”
Click “Transform Data” (opens Power Query)
In Power Query:
- Check each column
- Click the ABC icon in the column header to select the type:
  - Text — For personal identity numbers, postcodes (with a leading zero)
  - Whole Number — For integers
  - Decimal Number — For amounts
  - Date — For dates
- If a column is wrong: Right-click → Change Type
Click “Close & Load”

Result: The data is imported correctly with the right data types.

Advantages of the Power Query method

Repeatable: Right-click the query → Refresh to import again
Documented: All transformation steps are saved
Consistent: The same import every time
Fast: Once the steps are defined, an import takes a few seconds

Fixing import errors after import

If you have already imported data and discovered errors:

Fix: Numbers stored as text

Method 1: Text to Columns (fastest)

Select the column
Data → Text to Columns
Select “Delimited” → Next
Clear all delimiters → Next
Select “General” under column data format
Finish

Method 2: Find and Replace

Select the column
Ctrl+H
Find what: (leave blank)
Replace with: (leave blank)
Replace All

Excel is forced to re-evaluate the cells.

Method 3: The VALUE() function

In a helper column: =VALUE(A1)
Copy down
Copy the results
Paste Special → Values over the original

Fix: Dates as serial numbers

If it is displayed as a number (44927):

Select the cells
Right-click → Format Cells
Select “Date”
Select the desired date format
OK

If the date is text and must be converted:

=DATUMVÄRDE(A1)

Or if the format is YYYYMMDD:

=DATUM(VÄNSTER(A1;4);MEDEL(A1;5;2);HÖGER(A1;2))

Fix: Swedish characters (character encoding)

If already imported with the wrong encoding:

Unfortunately there is no easy fix. You must: 1. Delete the data 2. Import again with the correct encoding (UTF-8)

Prevention: Always use Data → From Text/CSV and select UTF-8 as the origin.

Fix: All data in one column

Use Text to Columns:

Select column A
Data → Text to Columns
Select “Delimited” → Next
Select the delimiters:
- Tab — If the data is separated by tabs
- Semicolon — If the data is separated by semicolons
- Comma — If the data is separated by commas
- Space — If the data is separated by spaces
- Other — Enter your own character
The preview shows how the data will be split
Next
Specify the data format for each column (usually “General”)
Finish

Fix: Extra spaces

Use the TRIM() function:

=BESKÄR(A1)

Removes: – Leading spaces – Trailing spaces – Multiple consecutive spaces (replaced with one)

Or for the whole column:

Select the column
Ctrl+H (Find and Replace)
Find what: One space + One space (two spaces)
Replace with: One space
Replace All
Repeat until Excel says “0 replacements”

Import from databases (SQL, Access)

Connect to a database

Data → Get Data → From Database
Select the type:
- SQL Server
- Microsoft Access database
- From ODBC
Enter the server name and database name
Specify authentication (Windows or database login)
Select a table or write a SQL query
Click OK

In Power Query: – Filter rows – Select columns – Change data types – Close & Load

Refreshing: Right-click the query → Refresh. The data is retrieved from the database again.

Import from the web

From a web page

Data → Get Data → From Web
Enter the URL
Excel finds tables on the page
Select a table
Transform in Power Query if necessary
Close & Load

Use cases: – Exchange rates from central banks – Sports results – Statistics from public authorities

From an API (JSON)

Data → Get Data → From Web
Enter the API URL
If the API requires authentication: Select the authentication type
JSON is converted to a table automatically
Expand columns with nested data
Close & Load

Tip: Use Power Query to transform the JSON structure.

Data validation after import

After import — validate that the data is correct.

Check data types

Quick method: 1. Select a column 2. Look at the format box (Home tab) 3. It should show the right data type: – Whole numbers → Number format – Decimal numbers → Number format with decimals – Dates → Date format – Text → Text format

Check ranges (numeric data)

Find extreme values:

Min: =MIN(A:A)
Max: =MAX(A:A)

Does it look reasonable? Or are there extreme values (e.g. 999999 as “missing data”)?

Find outliers:

Use conditional formatting: 1. Select a column 2. Home → Conditional Formatting → Top/Bottom Rules 3. Select “Above Average” or “Below Average”

Check for duplicates

Find duplicates:

Select the column
Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values
Select the formatting

Remove duplicates:

Select the data table
Data → Remove Duplicates
Select the columns to check
OK

Warning: Permanent action. Make a copy first.

Check for missing values

Find empty cells:

Ctrl+G (Go To)
Special
Select “Blanks”
OK

All empty cells are selected.

Replace empty cells:

Select the column
Ctrl+H
Find what: (leave blank)
Replace with: 0 or Missing or something else
Replace All

Check unique values

Are the values unique when they should be?

Examples: Personal identity numbers, article numbers, customer numbers.

=ANTAL.UNIKA(A:A)

Compare with the total number of rows. They should be equal if all values are unique.

Or use a pivot table: 1. Select a column 2. Ctrl+T (create table) 3. Insert → Pivot Table 4. Drag the column to “Rows” and “Values” 5. Filter values > 1 (finds duplicates)

Best practices for data import

1. Always use Power Query for recurring imports

Not “Open” or double-clicking. Use Data → From Text/CSV.

2. Save the import steps

Power Query saves all transformations. Next time: right-click → Refresh.

3. Specify data types explicitly

Do not let Excel guess. Select the data type in Power Query for each column.

4. Validate after every import

Run quick checks: – Min/Max for numeric columns – Count empty cells – Check the first and last rows

5. Document the import process

Add a comment in the Excel file: – Source (file, database, URL) – Import date – Any manual steps

6. Keep a backup of the source file

Keep the original source (CSV, etc.) for at least one month.

Automate data import with VBA

For recurring imports from the same source, VBA can automate everything.

Example: Import a CSV every Monday

Sub ImporteraCSV()
    Dim filväg As String
    filväg = "C:\Data\försäljning.csv"

    ' Ta bort gammal data
    Sheets("Import").Cells.Clear

    ' Importera ny data
    With Sheets("Import").QueryTables.Add( _
        Connection:="TEXT;" & filväg, _
        Destination:=Sheets("Import").Range("A1"))
        .TextFileParseType = xlDelimited
        .TextFileSemicolonDelimiter = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Read more: Excel automation with VBA

When you need professional help

Contact Excel Department if:

Complex data sources (multiple systems, APIs)
Large data volumes (100,000+ rows)
Recurring imports that must be automated
Data validation with advanced rules
Integration with business systems (ERP, CRM)

We offer: – Power Query solutions for complex data transformation – VBA automation for scheduled imports – Data validation and quality control – Integration with external systems

Call 010-264 20 20 or email info@exceldepartment.se.

Summary: Your import checklist

Before import: 1. [ ] Use Data → From Text/CSV (do not double-click) 2. [ ] Select UTF-8 as the character encoding 3. [ ] Check the delimiter in the preview 4. [ ] Open in Power Query 5. [ ] Specify the data type for each column explicitly 6. [ ] Close & Load

After import: 7. [ ] Check that numbers are right-aligned 8. [ ] Check the date format 9. [ ] Check Swedish characters (å, ä, ö) 10. [ ] Run Min/Max on numeric columns 11. [ ] Search for empty cells 12. [ ] Check for duplicates if relevant

For the future: 13. [ ] Save the Power Query query 14. [ ] Document the import process 15. [ ] Keep a backup of the source data