{"id":1820,"date":"2026-11-12T07:51:21","date_gmt":"2026-11-12T07:51:21","guid":{"rendered":"https:\/\/www.coolutils.com\/blog\/?p=1820"},"modified":"2026-11-12T07:51:21","modified_gmt":"2026-11-12T07:51:21","slug":"how-to-open-json-file-in-excel","status":"publish","type":"post","link":"https:\/\/www.coolutils.com\/blog\/how-to-open-json-file-in-excel\/","title":{"rendered":"How to Open a JSON File in Excel"},"content":{"rendered":"<div id=\"bsf_rt_marker\"><\/div><p>In Microsoft 365 Excel for Windows, open a blank workbook and choose <strong>Data &gt; Get Data &gt; From File &gt; From JSON<\/strong>. Select your local <code>.json<\/code> file. Power Query opens a preview where you can check the rows and columns before loading the data into a worksheet. Save the workbook as <code>.xlsx<\/code> when the table is ready.<\/p>\n<p>This walkthrough uses a three-record sample so you can see what Excel should produce at each stage. You can open the original JSON in a text editor to inspect its contents. Use Power Query when you want to work with those records in cells and keep the source file for reference.<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_83 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\r\n<div class=\"ez-toc-title-container\">\r\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\r\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\r\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.coolutils.com\/blog\/how-to-open-json-file-in-excel\/#Download_the_sample_JSON_file\" >Download the sample JSON file<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.coolutils.com\/blog\/how-to-open-json-file-in-excel\/#Import_the_file_with_Power_Query\" >Import the file with Power Query<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.coolutils.com\/blog\/how-to-open-json-file-in-excel\/#Check_the_table_before_you_load_it\" >Check the table before you load it<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.coolutils.com\/blog\/how-to-open-json-file-in-excel\/#Load_the_worksheet_and_save_an_XLSX_copy\" >Load the worksheet and save an XLSX copy<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/www.coolutils.com\/blog\/how-to-open-json-file-in-excel\/#Fix_common_JSON_import_problems\" >Fix common JSON import problems<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/www.coolutils.com\/blog\/how-to-open-json-file-in-excel\/#Check_a_new_export_before_replacing_the_old_one\" >Check a new export before replacing the old one<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/www.coolutils.com\/blog\/how-to-open-json-file-in-excel\/#Convert_JSON_to_XLSX_in_a_browser\" >Convert JSON to XLSX in a browser<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/www.coolutils.com\/blog\/how-to-open-json-file-in-excel\/#Frequently_asked_questions\" >Frequently asked questions<\/a><\/li><\/ul><\/nav><\/div>\r\n<h2><span class=\"ez-toc-section\" id=\"Download_the_sample_JSON_file\"><\/span>Download the sample JSON file<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Download <a href=\"https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/json-excel-sample.json\">json-excel-sample.json<\/a>, or copy the following into a plain-text editor and save it as a UTF-8 file with the <code>.json<\/code> extension. Save it somewhere you can find from Excel, such as a folder for this example. If you&#8217;re using a file from another application, use its JSON export as the starting point. Changing the extension of a CSV or a text log leaves the contents as they were.<\/p>\n<pre><code class=\"language-json\">[\n  {\"record_id\":\"R-001\",\"customer\":\"North Studio\",\"quantity\":2,\"active\":true},\n  {\"record_id\":\"R-002\",\"customer\":\"West Workshop\",\"quantity\":4,\"active\":false},\n  {\"record_id\":\"R-003\",\"customer\":\"East Office\",\"quantity\":1,\"active\":true}\n]<\/code><\/pre>\n<p>The square brackets enclose an array. Each pair of braces encloses one object with four fields. For this example, each object is one record and becomes one worksheet row. The field names become column headers. The quoted IDs and customer names are text, the quantities are numbers, and the unquoted <code>true<\/code> and <code>false<\/code> values are Booleans.<\/p>\n<table>\n<thead>\n<tr>\n<th>record_id<\/th>\n<th>customer<\/th>\n<th>quantity<\/th>\n<th>active<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>R-001<\/td>\n<td>North Studio<\/td>\n<td>2<\/td>\n<td>true<\/td>\n<\/tr>\n<tr>\n<td>R-002<\/td>\n<td>West Workshop<\/td>\n<td>4<\/td>\n<td>false<\/td>\n<\/tr>\n<tr>\n<td>R-003<\/td>\n<td>East Office<\/td>\n<td>1<\/td>\n<td>true<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Keep this table handy during the import. The three distinct IDs tell you which records came through. The quantities add up to <code>7<\/code>, and <code>R-002<\/code> is the only inactive record. A row count alone would miss a quantity attached to the wrong customer, so check the values against their IDs as well.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Import_the_file_with_Power_Query\"><\/span>Import the file with Power Query<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<ol>\n<li>Open a blank workbook in Microsoft 365 Excel for Windows. On the <strong>Data<\/strong> tab, select <strong>Get Data &gt; From File &gt; From JSON<\/strong>.<\/li>\n<li>In the file dialog, select <code>json-excel-sample.json<\/code> and choose <strong>Open<\/strong>. If you keep several exports in the same folder, check the filename before continuing.<\/li>\n<\/ol>\n<figure><img decoding=\"async\" class=\"wp-image-1823 size-full\" src=\"https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/json-excel-open-en.png\" alt=\"Excel Data menu or file dialog for opening the sample JSON\" width=\"1440\" height=\"900\" loading=\"lazy\" srcset=\"https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/json-excel-open-en.png 1440w, https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/json-excel-open-en-300x188.png 300w, https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/json-excel-open-en-1024x640.png 1024w, https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/json-excel-open-en-768x480.png 768w, https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/json-excel-open-en-1200x750.png 1200w\" sizes=\"auto, (max-width: 709px) 85vw, (max-width: 909px) 67vw, (max-width: 1362px) 62vw, 840px\" \/><\/figure>\n<ol start=\"3\">\n<li>When Power Query Editor opens, look at the preview and the <strong>Applied Steps<\/strong> pane. Power Query can detect the table and apply steps to flatten JSON automatically. If the preview already has the four expected columns, continue to the checks below.<\/li>\n<li>If you see a single column of records, inspect an entry first to confirm it holds the four sample fields. Use the expand control on that column to bring the fields into the table. If the preview starts with a list, convert that list to a table before expanding its records. Keep the record level that gives you one row per ID.<\/li>\n<\/ol>\n<figure><img decoding=\"async\" class=\"wp-image-1824 size-full\" src=\"https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/json-power-query-en.png\" alt=\"Power Query preview with the sample JSON records and four fields\" width=\"1440\" height=\"900\" loading=\"lazy\" srcset=\"https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/json-power-query-en.png 1440w, https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/json-power-query-en-300x188.png 300w, https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/json-power-query-en-1024x640.png 1024w, https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/json-power-query-en-768x480.png 768w, https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/json-power-query-en-1200x750.png 1200w\" sizes=\"auto, (max-width: 709px) 85vw, (max-width: 909px) 67vw, (max-width: 1362px) 62vw, 840px\" \/><\/figure>\n<p>The preview for another file can look different. For example, an export may have a top-level object that contains a list of records under a field such as <code>results<\/code>. Inspect that collection before choosing what to expand. A list of records and a list inside one record represent different levels. Expanding the latter can give you several rows for a single ID, which may be useful for a detail report but changes the meaning of a worksheet intended to contain one row per record. Microsoft explains automatic detection and the handling of nested data in its <a href=\"https:\/\/learn.microsoft.com\/en-us\/power-query\/connectors\/json\">Power Query JSON connector guide<\/a>.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Check_the_table_before_you_load_it\"><\/span>Check the table before you load it<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Start with the headers. You should be able to identify <code>record_id<\/code>, <code>customer<\/code>, <code>quantity<\/code> and <code>active<\/code>. If an expansion adds a prefix to a header, you can rename that column to match the sample. Check the contents before renaming so that similarly named fields from different levels keep their correct meanings.<\/p>\n<p>Next, check the column types in Power Query. Keep <code>record_id<\/code> and <code>customer<\/code> as <strong>Text<\/strong>. Set <code>quantity<\/code> to <strong>Whole Number<\/strong> and use <strong>True\/False<\/strong> for <code>active<\/code>. Examine any type step already applied to the table. If a type change causes an error, select the preceding step and look at the value as it arrived from the source. That is often enough to tell whether the field was empty, written as text, or in a different format from the other records.<\/p>\n<p>Read across each sample row. <code>R-001<\/code> should show North Studio, a quantity of <code>2<\/code> and an active flag of <code>true<\/code>. <code>R-002<\/code> should show West Workshop, <code>4<\/code> and <code>false<\/code>. <code>R-003<\/code> should show East Office, <code>1<\/code> and <code>true<\/code>. If a record appears twice, step back through the expansions to find where the extra row came from. If a record is missing, look for a filter or an expansion that dropped it before changing the source file.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Load_the_worksheet_and_save_an_XLSX_copy\"><\/span>Load the worksheet and save an XLSX copy<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Once the preview matches the sample, select <strong>Home &gt; Close &amp; Load<\/strong>. In the blank workbook, Excel can load the query result into a new worksheet. If you use <strong>Close &amp; Load To<\/strong>, choose <strong>Table<\/strong> and a <strong>New worksheet<\/strong> so the sample has a sheet of its own.<\/p>\n<p>Check the table in the worksheet. There should be four columns and three data rows. Excel may display the Boolean values as <code>TRUE<\/code> and <code>FALSE<\/code>. Check that the quantity column contains numbers you can add and that its total is <code>7<\/code>. If you want to use a formula in the sample layout shown above, enter <code>=SUM(C2:C4)<\/code> in an empty cell outside the imported table. Use the actual range if the table starts somewhere else.<\/p>\n<figure><img decoding=\"async\" class=\"wp-image-1825 size-full\" src=\"https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/json-excel-result-en.png\" alt=\"Excel worksheet with three JSON records and their active flags\" width=\"1440\" height=\"900\" loading=\"lazy\" srcset=\"https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/json-excel-result-en.png 1440w, https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/json-excel-result-en-300x188.png 300w, https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/json-excel-result-en-1024x640.png 1024w, https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/json-excel-result-en-768x480.png 768w, https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/json-excel-result-en-1200x750.png 1200w\" sizes=\"auto, (max-width: 709px) 85vw, (max-width: 909px) 67vw, (max-width: 1362px) 62vw, 840px\" \/><\/figure>\n<p>Choose <strong>File &gt; Save As<\/strong> and save the workbook as <code>json-excel-sample.xlsx<\/code> using the <strong>Excel Workbook (.xlsx)<\/strong> format. Reopen that workbook and check the same IDs, quantities and flags. Keep <code>json-excel-sample.json<\/code> as your original. The workbook saves the worksheet and its imported data, and the query can still refer to the local source path. If you later move the JSON and want to refresh the query, update the source location in Excel.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Fix_common_JSON_import_problems\"><\/span>Fix common JSON import problems<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<table>\n<thead>\n<tr>\n<th>What you see<\/th>\n<th>What to check<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>Excel reports a parsing error<\/td>\n<td>Open the source in a text editor. Check for missing brackets or quotes, extra commas, or a file that contains an error message in place of the export. Request a fresh export if the file appears incomplete.<\/td>\n<\/tr>\n<tr>\n<td>Each line is a separate JSON object<\/td>\n<td>You may have JSON Lines. A file of independent records on separate lines needs the JSON Lines procedure in Microsoft&#8217;s connector guide.<\/td>\n<\/tr>\n<tr>\n<td>A column still shows List or Record<\/td>\n<td>Inspect a value to see which fields or items it contains. Expand only the collection needed for your worksheet and compare the row count again.<\/td>\n<\/tr>\n<tr>\n<td>The From JSON command is missing<\/td>\n<td>Confirm that you are using the desktop Windows edition covered here. Check Microsoft&#8217;s connector instructions for your edition before looking for the same route on another platform.<\/td>\n<\/tr>\n<tr>\n<td>The imported record count differs from the source<\/td>\n<td>Compare the IDs and review Applied Steps from top to bottom. Look for the first filter or expansion after which the count changes.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>For an export you did not create, try the small sample first. If the sample imports and the real file fails, compare the structure of the two files. The real export might wrap its records inside another object, use JSON Lines, or contain an incomplete final record. A file that opens in a text editor can still contain data the connector cannot parse. The exact error message helps you decide whether to repair the export or choose a different import route.<\/p>\n<p>If the real file loads but some fields are blank, inspect several records in the source. A field may be optional, empty, or stored at a different level. Keep those distinctions in mind before filling missing values with zero or deleting a column. For a shared workbook, compare a few records against the source after reopening it, especially if the table will be used for totals or reporting.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Check_a_new_export_before_replacing_the_old_one\"><\/span>Check a new export before replacing the old one<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>If you receive the same report again with updated values, save the new JSON alongside the original. Try the import in a separate workbook or point a copy of the query at the new file. Compare the headers, row count and a few IDs before using the result in your existing report. An export can gain or lose a field when the source application changes. If the preview shows an error in an expansion or type step, select the step just before it to see which fields are available now. Keep the original workbook until the new table gives you the columns and records your report expects.<\/p>\n<p>If you share the result, decide whether the recipient needs the saved rows or a workbook they can refresh. For a copy that contains just the saved values, copy the table to a new workbook and use <strong>Paste Values<\/strong>. Check the resulting headers and row count before sending that workbook. For a workbook the recipient needs to refresh, include the JSON source and explain where to put it. The original query points to the file you selected on your own computer. The recipient may need to set the source path to the location where they saved their copy.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Convert_JSON_to_XLSX_in_a_browser\"><\/span>Convert JSON to XLSX in a browser<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>If you need a workbook from a local file and prefer a browser conversion, you can <a href=\"https:\/\/www.coolutils.com\/online\/JSON-to-XLSX\">convert JSON to XLSX online<\/a>. Upload the JSON file, select XLSX and download the workbook. Check its rows and columns against your source, especially if the original contains nested values.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Frequently_asked_questions\"><\/span>Frequently asked questions<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><strong>Do I need to write code to open the sample JSON in Excel?<\/strong> The sample uses the built-in JSON connector and the Power Query interface. You can import, inspect and load it using the menus in this guide.<\/p>\n<p><strong>Why do I still see List or Record in some cells?<\/strong> Those cells contain structured values. A record contains named fields, and a list contains items. Open a value to see what is inside and decide which fields or rows your worksheet needs before expanding it.<\/p>\n<p><strong>Does saving an XLSX workbook replace my JSON file?<\/strong> Excel saves the workbook as a separate file. Keep the original JSON so you can check the source records or refresh the query later.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Open a JSON file in Excel with Power Query. Follow a worked example, check the rows and columns, and save the result as an XLSX workbook.<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[189],"tags":[],"class_list":["post-1820","post","type-post","status-publish","format-standard","hentry","category-office-converters"],"_links":{"self":[{"href":"https:\/\/www.coolutils.com\/blog\/wp-json\/wp\/v2\/posts\/1820","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.coolutils.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.coolutils.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.coolutils.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.coolutils.com\/blog\/wp-json\/wp\/v2\/comments?post=1820"}],"version-history":[{"count":1,"href":"https:\/\/www.coolutils.com\/blog\/wp-json\/wp\/v2\/posts\/1820\/revisions"}],"predecessor-version":[{"id":1831,"href":"https:\/\/www.coolutils.com\/blog\/wp-json\/wp\/v2\/posts\/1820\/revisions\/1831"}],"wp:attachment":[{"href":"https:\/\/www.coolutils.com\/blog\/wp-json\/wp\/v2\/media?parent=1820"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.coolutils.com\/blog\/wp-json\/wp\/v2\/categories?post=1820"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.coolutils.com\/blog\/wp-json\/wp\/v2\/tags?post=1820"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}