Opening a JSON File in Excel and Filtering Data

Purpose

This SOP outlines the steps to open a .json file in Microsoft Excel and filter multiple rows and lines of data.

Requirements

• Microsoft Excel (2016 or newer recommended)
• A .json file
• Basic knowledge of Excel functionality

Steps

Step 1: Open the JSON File in Excel

1. Launch Excel. Open Microsoft Excel on your computer.

2. Open the JSON File.

   • Click File > Open > Browse.
   • Locate your .json file in the file browser.
   • In the file type dropdown (bottom-right), select All Files (.) to view JSON files.
   • Select the .json file and click Open.

3. Import JSON Data.

   • Excel will recognize the file as non-standard text and open the Power Query Editor.
   • In the Power Query Editor, Excel will display the JSON data in a tree structure.

4. Transform the JSON Data.

   • Click To Table in the top-left corner. (Excel will attempt to flatten the JSON structure into a table.)
   • Adjust the delimiter and other settings if necessary, then click OK.

Step 2: Load the Data into Excel

1. After transforming the data, click Close & Load in the top-left corner of the Power Query Editor.
2. Excel will load the JSON data into a new worksheet as a table.

Step 3: Format the Data for Filtering

1. If the data is not in table format:

   • Select the range of data.
   • Press Ctrl + T (or Cmd + T on Mac) to convert it into a table.
   • Ensure My Table Has Headers is checked, then click OK.

2. Once in table format, Excel automatically enables filtering options for each column.

Step 4: Filter Multiple Rows and Lines

1. Enable Filters:

   • Click the filter dropdown arrow next to the column headers.

2. Filter by Multiple Rows:

   • Select or unselect the desired values using the checkboxes.
   • Use the search bar in the dropdown menu to quickly find values.
   • For advanced filters, click Text Filters or Number Filters for options like Contains, Starts With, Greater Than, etc.

3. Filter by Multiple Lines:

   • If a column contains multi-line data:
     • Use Text Filters > Contains to find specific text across lines.
   • Alternatively, use Excel's built-in Find and Replace (Ctrl + F) for broader searches.

Notes

• Nested JSON Files: If your JSON file contains deeply nested objects, you may need to expand the data further in the Power Query Editor by clicking the small expand icons (⯈).
• Complex Data: If the data is too complex to handle in Excel, consider using additional tools like Python with pandas or Power BI for more advanced manipulation.

Troubleshooting

1. Data Missing or Incorrect:
   • Recheck the steps in the Power Query Editor to ensure all fields are expanded and converted properly.
2. Excel Version Issues:
   • If you're using an older version of Excel that lacks JSON support, convert the JSON to a CSV using an online tool or script before importing.