Step 1: Check Excel Version

Power Query is available in:

• Excel 2016 and newer (Windows)
• Excel for Mac 2016 and newer
• Microsoft 365 (all versions)

If using older Excel (2013 or earlier), skip to Method 2.

Step 2: Open Excel

1. Launch Excel (blank workbook or existing file)

Step 3: Access Power Query

1. Click Data tab in the ribbon
2. Look for “From JSON” button (Excel 2021+) or “From File” → “From JSON”
3. If not visible, click “From Other Sources” and look for JSON option

Note: If you don’t see JSON options, your Excel version may not support it. Use Method 2 instead.

Step 4: Select JSON File

1. File browser opens
2. Navigate to your JSON file location
3. Select the JSON file
4. Click Open

Step 5: Configure JSON Import

Power Query Preview opens showing JSON structure:

1. Review the preview pane showing your data
2. Select which data section you want to import (if multiple arrays in JSON)
3. Check that column headers are recognized

Step 6: Load Data into Excel

1. Click “Load” to import into current sheet
2. Or click “Load To” dropdown to choose specific location
3. Data imports as table into Excel

JSON converts to proper Excel table format with column headers and data rows.

Pros: Built into Excel, converts complex JSON, preserves data types, creates live connection

Cons: Only Excel 2016+, may not handle deeply nested JSON perfectly

Step 1: Open JSON in Text Editor

1. Right-click JSON file
2. Select “Open With” → Notepad (Windows) or TextEdit (Mac)
3. JSON content displays as text

Step 2: Manually Extract Column Headers

Look at the JSON structure and identify field names. Example:

"name": "John",
"email": "john@example.com",
"amount": 1500

Column headers would be: name, email, amount

Step 3: Create Excel Headers

1. Open Excel (blank workbook)
2. In cell A1, type the first header (e.g., “name”)
3. In B1, type second header (e.g., “email”)
4. Continue across for all columns

Step 4: Enter Data Manually

1. In cell A2, enter first data value from JSON
2. Continue filling in data row by row
3. For large JSON files, this becomes impractical

Only use this method for very small JSON files (5-10 records maximum)

Step 5: Save as Excel File

1. File → Save As
2. Select “Excel Workbook (.xlsx)”
3. Save the file

Pros: No installation needed, works for simple data

Cons: Manual process, very time-consuming, error-prone, only for small files

Step 1: Open JSON in Text Editor

1. Right-click JSON file → “Open With” → Notepad

Step 2: Identify Data Structure

Look for the array of objects. Example:

"customers": [
  {"name": "John", "email": "john@example.com"},
  {"name": "Sarah", "email": "sarah@example.com"}
]

In this case, you need: name, email columns

Step 3: Manually Create CSV Format

1. In Notepad, create first line with headers separated by commas:
2. name,email
3. On next line, enter first record separated by commas:
4. John,john@example.com
5. Continue for each record in JSON

Step 4: Save as CSV

1. File → Save As
2. Change extension to .csv (e.g., “data.csv”)
3. Save the file

Step 5: Open CSV in Excel

1. Open Excel
2. File → Open
3. Select your CSV file
4. Excel imports with proper column separation

Pros: Works with any Excel version, creates usable spreadsheet

Cons: Requires manual data extraction, impractical for large files

Recommended Online Converters

• CloudConvert: Supports JSON to XLSX and CSV conversion
• JSON to CSV Converter: Dedicated JSON converter (search “json to csv”)
• Convertio: Fast conversion of various formats
• Online Convert: Free JSON conversion without registration

Step-by-Step Conversion

1. Open converter website in browser
2. Click “Upload” or paste JSON content directly into text area
3. Select output format: “CSV” or “XLSX”
4. Click “Convert” button
5. Wait for processing (usually 5-30 seconds)
6. Click “Download” to save converted file
7. Open downloaded CSV or XLSX in Excel

Security Note

• Only use for non-sensitive data: Files upload to external servers
• Privacy concern: Avoid with confidential information
• Best for: Public data, test files, non-confidential datasets
• Not for: Financial data, personal information, proprietary business data

Pros: Very fast, no software installation, simple interface

Cons: Privacy concerns, internet required, file size limits

Step 1: Copy JSON Data

1. Open JSON file in Notepad
2. Look for lines that don’t contain brackets or braces (actual data)
3. Select and copy those lines

Step 2: Paste into Excel

1. Open Excel
2. Click cell A1
3. Paste the data (Ctrl+V)

Step 3: Use Text to Columns

1. Select all pasted data
2. Data tab → “Text to Columns”
3. Choose “Delimited”
4. Select delimiter (comma or colon, depending on JSON format)
5. Click Finish

Pros: Uses built-in Excel feature, works offline

Cons: Very limited, only works for simple JSON structure, manual and error-prone

Issue 1: Power Query “From JSON” Not Available

Problem: Data tab doesn’t show “From JSON” option.

Cause: Older Excel version (pre-2016) or incorrect menu location.

Solution:

• Check Excel version (File → Account → About)
• If older than 2016, use Method 4 (online converter) instead
• Look under Data tab → “From Other Sources” for JSON option

Issue 2: JSON Too Complex or Deeply Nested

Problem: Power Query imports JSON but data looks incorrect or incomplete.

Cause: JSON has complex nested structure that doesn’t convert to flat Excel table.

Solution:

• Use online converter instead of Power Query
• Request simpler JSON from data source if possible
• Manually extract only the data you need from JSON

Issue 3: JSON File Opens as Text in Excel

Problem: Double-clicking JSON file opens it as plain text in Excel, not imported as data.

Cause: Excel treating JSON as generic text file.

Solution:

1. Close the text view
2. Use Method 1 (Power Query From JSON) to import properly
3. Do not double-click JSON files directly in Excel

Issue 4: Special Characters Display Incorrectly

Problem: Accented characters (é, ñ, ü) show garbled after conversion.

Cause: Character encoding issue during conversion.

Solution:

• Ensure JSON file saved as UTF-8 format
• Use Power Query which handles encoding better
• If using online converter, look for UTF-8 encoding option

Issue 5: Numbers Show as Text or Scientific Notation

Problem: Numeric values display as text or in format “1.23E+04”.

Cause: Column formatting didn’t convert properly.

Solution:

1. Select column with numbers
2. Right-click → Format Cells
3. Select “Number” category
4. Set decimal places and click OK

Issue 6: Dates Not Recognized

Problem: Date values display as text instead of date format.

Cause: Date format in JSON not recognized by Excel.

Solution:

1. Select date column
2. Data → Text to Columns
3. Step 3: Select date format matching your data
4. Click Finish

Issue 7: JSON File Too Large to Convert

Problem: Conversion fails or times out for large JSON files.

Cause: File exceeds online converter limits or Power Query capacity.

Solution:

• Request data in CSV format instead
• Ask for JSON split into smaller files
• Use programming tools (Python, Node.js) for very large files

Issue 8: Can’t Find “From JSON” Despite Excel Version Supporting It

Problem: Excel 2016+ installed but Data tab doesn’t show JSON option.

Cause: Power Query add-in disabled or not installed.

Solution:

1. File → Options → Add-ins
2. In “Manage” dropdown, select “COM Add-ins” → “Go”
3. Check if Power Query listed and enabled
4. If not visible, reinstall Excel or update Power Query

Q: Can Excel open JSON files directly?

A: No. Excel doesn’t support JSON format natively. You must use Power Query (Excel 2016+), online converter, or manual methods.

Q: What if I have Excel 2013 or older?

A: Power Query not available. Use Method 4 (online converter) or manually convert JSON to CSV format and open in Excel.

Q: Can I save Excel files as JSON format?

A: Excel doesn’t have native JSON export. You can use online converters to convert XLSX to JSON, but this is less common.

Q: What if JSON has nested arrays?

A: Power Query can handle nested JSON to some extent. If structure too complex, request simplified JSON or flatten manually.

Q: Is data lost when converting JSON to Excel?

A: Data is preserved, but nested structure is flattened to rows and columns. Complex hierarchies may lose some organizational context.

Q: Can I edit JSON data in Excel and convert back?

A: You can edit data in Excel, then save as CSV. Converting back to JSON is possible with online tools or programming.

Q: What if online converter asks for password or subscription?

A: Many converters are free. Avoid ones requiring payment unless absolutely necessary. Look for “free online json to csv converter”.

Q: Can very large JSON files convert to Excel?

A: Excel has 1,048,576 row limit. Very large JSON files may exceed this. Request smaller portions or use database tools instead.