You import data from CSV, a database or the web. Numbers become text. Dates show serial numbers. Swedish characters (\u00e5, \u00e4, \u00f6) turn into strange symbols. All the data ends up in one column.<\/p>\n
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.<\/p>\n
For general troubleshooting, see the Excel troubleshooting guide<\/a>. For other problems, read common Excel problems<\/a>.<\/p>\n Symptoms:<\/strong> – Numbers are left-aligned (instead of right-aligned) – Green triangle in the top left corner – Formulas do not work (e.g. SUM gives 0)<\/p>\n Cause:<\/strong> The cell was formatted as text before import, or the data contained a leading apostrophe.<\/p>\n Symptoms:<\/strong> – Instead of \u201c2026-01-30\u201d you see \u201c44 927\u201d – Or: dates are interpreted incorrectly (US format: month\/day\/year)<\/p>\n Cause:<\/strong> The date format does not match what Excel expects, or the cell is formatted as a number.<\/p>\n Symptoms:<\/strong> – \u201c\u00e5\u201d becomes \u201c\u00c3\u00a5\u201d – \u201c\u00f6\u201d becomes \u201c\u00c3\u00b6\u201d – \u201c\u00e4\u201d becomes \u201c\u00c3\u00a4\u201d<\/p>\n Cause:<\/strong> Wrong character encoding on import (UTF-8 vs ISO-8859-1).<\/p>\n Symptoms:<\/strong> – 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<\/p>\n Cause:<\/strong> Excel guesses the wrong delimiter on import.<\/p>\n Symptoms:<\/strong> – The data looks right but formulas do not work – \u201cStockholm\u201d does not match \u201cStockholm\u201d (extra space)<\/p>\n Cause:<\/strong> Leading\/trailing spaces from the source system.<\/p>\n Wrong method<\/strong> (double-clicking the CSV): Excel guesses the format and often gets it wrong.<\/p>\n Right method<\/strong> (Data \u2192 From Text\/CSV):<\/p>\n Open Excel (blank)<\/strong><\/p>\n<\/li>\n Select the file<\/strong><\/p>\n<\/li>\n A preview opens:<\/strong><\/p>\n Click \u201cTransform Data\u201d<\/strong> (opens Power Query)<\/p>\n<\/li>\n In Power Query:<\/strong><\/p>\n Click \u201cClose & Load\u201d<\/strong><\/p>\n<\/li>\n<\/ol>\n Result:<\/strong> The data is imported correctly with the right data types.<\/p>\n If you have already imported data and discovered errors:<\/p>\n Method 1: Text to Columns (fastest)<\/strong><\/p>\n Method 2: Find and Replace<\/strong><\/p>\n Excel is forced to re-evaluate the cells.<\/p>\n Method 3: The VALUE() function<\/strong><\/p>\n If it is displayed as a number (44927):<\/strong><\/p>\n If the date is text and must be converted:<\/strong><\/p>\n Or if the format is YYYYMMDD:<\/p>\n If already imported with the wrong encoding:<\/strong><\/p>\n Unfortunately there is no easy fix. You must: 1. Delete the data 2. Import again with the correct encoding (UTF-8)<\/p>\n Prevention:<\/strong> Always use Use Text to Columns:<\/strong><\/p>\n Use the TRIM() function:<\/strong><\/p>\n Removes: – Leading spaces – Trailing spaces – Multiple consecutive spaces (replaced with one)<\/p>\n Or for the whole column:<\/strong><\/p>\n In Power Query:<\/strong> – Filter rows – Select columns – Change data types – Close & Load<\/p>\n Refreshing:<\/strong> Right-click the query \u2192 Refresh. The data is retrieved from the database again.<\/p>\n Use cases:<\/strong> – Exchange rates from central banks – Sports results – Statistics from public authorities<\/p>\n Tip:<\/strong> Use Power Query to transform the JSON structure.<\/p>\n After import \u2014 validate that the data is correct.<\/p>\n Quick method:<\/strong> 1. Select a column 2. Look at the format box (Home tab) 3. It should show the right data type: – Whole numbers \u2192 Number format – Decimal numbers \u2192 Number format with decimals – Dates \u2192 Date format – Text \u2192 Text format<\/p>\n Find extreme values:<\/strong><\/p>\n Does it look reasonable? Or are there extreme values (e.g. 999999 as \u201cmissing data\u201d)?<\/p>\n Find outliers:<\/strong><\/p>\n Use conditional formatting: 1. Select a column 2. Home \u2192 Conditional Formatting \u2192 Top\/Bottom Rules 3. Select \u201cAbove Average\u201d or \u201cBelow Average\u201d<\/p>\n Find duplicates:<\/strong><\/p>\n Remove duplicates:<\/strong><\/p>\n Warning:<\/strong> Permanent action. Make a copy first.<\/p>\n Find empty cells:<\/strong><\/p>\n All empty cells are selected.<\/p>\n Replace empty cells:<\/strong><\/p>\n Are the values unique when they should be?<\/strong><\/p>\n Examples: Personal identity numbers, article numbers, customer numbers.<\/p>\n Compare with the total number of rows. They should be equal if all values are unique.<\/p>\n Or use a pivot table: 1. Select a column 2. Ctrl+T (create table) 3. Insert \u2192 Pivot Table 4. Drag the column to \u201cRows\u201d and \u201cValues\u201d 5. Filter values > 1 (finds duplicates)<\/p>\n Not \u201cOpen\u201d or double-clicking. Use Power Query saves all transformations. Next time: right-click \u2192 Refresh.<\/p>\n Do not let Excel guess. Select the data type in Power Query for each column.<\/p>\n Run quick checks: – Min\/Max for numeric columns – Count empty cells – Check the first and last rows<\/p>\n Add a comment in the Excel file: – Source (file, database, URL) – Import date – Any manual steps<\/p>\n Keep the original source (CSV, etc.) for at least one month.<\/p>\n For recurring imports from the same source, VBA can automate everything.<\/p>\n Example: Import a CSV every Monday<\/strong><\/p>\n Read more:<\/strong> Excel automation with VBA<\/a><\/p>\n Contact Excel Department if:<\/strong><\/p>\n We offer: – Power Query solutions for complex data transformation – VBA automation for scheduled imports – Data validation and quality control – Integration with external systems<\/p>\n Call 010-264 20 20<\/a> or email info@exceldepartment.se<\/a>.<\/p>\n Before import:<\/strong> 1. [ ] Use Data \u2192 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<\/p>\n After import:<\/strong> 7. [ ] Check that numbers are right-aligned 8. [ ] Check the date format 9. [ ] Check Swedish characters (\u00e5, \u00e4, \u00f6) 10. [ ] Run Min\/Max on numeric columns 11. [ ] Search for empty cells 12. [ ] Check for duplicates if relevant<\/p>\n For the future:<\/strong> 13. [ ] Save the Power Query query 14. [ ] Document the import process 15. [ ] Keep a backup of the source data<\/p>\n Excel Department<\/strong> Slottsbacken 8, Stockholm Phone: +46-10-264-20-20<\/a> \/ 010-264 20 20<\/a> Email: info@exceldepartment.se<\/a><\/p>\n Need help with this?<\/strong> Our Excel consultants<\/a> will help you \u2013 the first consultation is free of charge.<\/p>\n Book a free call<\/a> or call 010-264 20 20<\/a>.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":" You import data from CSV, a database or the web. […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-2010","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/posts\/2010","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/comments?post=2010"}],"version-history":[{"count":0,"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/posts\/2010\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/media?parent=2010"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/categories?post=2010"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/tags?post=2010"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Common data import problems<\/h2>\n
Problem 1: Numbers are interpreted as text<\/h3>\n
Problem 2: Dates become serial numbers<\/h3>\n
Problem 3: Swedish characters become strange symbols<\/h3>\n
Problem 4: All data in one column<\/h3>\n
Problem 5: Extra spaces<\/h3>\n
The right method: Import from CSV\/text<\/h2>\n<\/p>\n
Step by step<\/h3>\n
\n
Data<\/code> \u2192 Get Data<\/code> \u2192 From File<\/code> \u2192 From Text\/CSV<\/code><\/p>\n<\/li>\n\n
65001: Unicode (UTF-8)<\/code> for Swedish characters<\/li>\n\n
,<\/code>) \u2014 Common CSV standard<\/li>\n;<\/code>) \u2014 Swedish CSV standard<\/li>\n\n
\n
Advantages of the Power Query method<\/h3>\n
\n
Fixing import errors after import<\/h2>\n<\/p>\n
Fix: Numbers stored as text<\/h3>\n
\n
Data<\/code> \u2192 Text to Columns<\/code><\/li>\n\n
\n
=VALUE(A1)<\/code><\/li>\nFix: Dates as serial numbers<\/h3>\n
\n
=DATUMV\u00c4RDE(A1)<\/code><\/pre>\n=DATUM(V\u00c4NSTER(A1;4);MEDEL(A1;5;2);H\u00d6GER(A1;2))<\/code><\/pre>\nFix: Swedish characters (character encoding)<\/h3>\n
Data \u2192 From Text\/CSV<\/code> and select UTF-8<\/code> as the origin.<\/p>\nFix: All data in one column<\/h3>\n
\n
Data<\/code> \u2192 Text to Columns<\/code><\/li>\n\n
Fix: Extra spaces<\/h3>\n
=BESK\u00c4R(A1)<\/code><\/pre>\n\n
Import from databases (SQL, Access)<\/h2>\n
Connect to a database<\/h3>\n
\n
Data<\/code> \u2192 Get Data<\/code> \u2192 From Database<\/code><\/li>\n\n
Import from the web<\/h2>\n
From a web page<\/h3>\n
\n
Data<\/code> \u2192 Get Data<\/code> \u2192 From Web<\/code><\/li>\nFrom an API (JSON)<\/h3>\n
\n
Data<\/code> \u2192 Get Data<\/code> \u2192 From Web<\/code><\/li>\nData validation after import<\/h2>\n
Check data types<\/h3>\n
Check ranges (numeric data)<\/h3>\n
Min: =MIN(A:A)\r\nMax: =MAX(A:A)<\/code><\/pre>\nCheck for duplicates<\/h3>\n
\n
\n
Check for missing values<\/h3>\n
\n
\n
0<\/code> or Missing<\/code> or something else<\/li>\nCheck unique values<\/h3>\n
=ANTAL.UNIKA(A:A)<\/code><\/pre>\nBest practices for data import<\/h2>\n
1. Always use Power Query for recurring imports<\/h3>\n
Data \u2192 From Text\/CSV<\/code>.<\/p>\n2. Save the import steps<\/h3>\n
3. Specify data types explicitly<\/h3>\n
4. Validate after every import<\/h3>\n
5. Document the import process<\/h3>\n
6. Keep a backup of the source file<\/h3>\n
Automate data import with VBA<\/h2>\n
Sub ImporteraCSV()\r\n Dim filv\u00e4g As String\r\n filv\u00e4g = "C:\\Data\\f\u00f6rs\u00e4ljning.csv"\r\n\r\n ' Ta bort gammal data\r\n Sheets("Import").Cells.Clear\r\n\r\n ' Importera ny data\r\n With Sheets("Import").QueryTables.Add( _\r\n Connection:="TEXT;" & filv\u00e4g, _\r\n Destination:=Sheets("Import").Range("A1"))\r\n .TextFileParseType = xlDelimited\r\n .TextFileSemicolonDelimiter = True\r\n .Refresh BackgroundQuery:=False\r\n End With\r\nEnd Sub<\/code><\/pre>\nWhen you need professional help<\/h2>\n
\n
Summary: Your import checklist<\/h2>\n
Related content<\/h2>\n
\n
\n
\n