Here is a quick excel number formatting tip. If you ever want to format numbers in such a way that it shows decimal values only if the number is less than 1 you can use conditional custom cell formatting (do not confuse with conditional formatting).
Here is an example:
In such cases you can use conditions in custom cell formatting.
First select the numbers you want to format, hit CTRL+1 (or right mouse click > format cells)- In the “Number” tab, select category as “custom”
- Now, write the formatting condition for custom formatting the cell. In our case the condition looks like
[<1]_($#,##0.00_);_($#,##0_). See to the right. what it means is, if the cell value is less than 1 then format the cell in $#,##0.00 format otherwise format as $#,##0. Excel cell formatting is a tricky business and if you want to master it there is no better source than Peltier's article on Custom Number Formats.
More excel tips on formatting:
Formatting numbers in excel - few tips
Custom Cell formatting in Excel - Quick tips
One Response to “Easily Convert JSON to Excel – Step by Step Tutorial”
Great guide! You mentioned that "Power Query in Excel offers a quick, easy and straightforward way to convert JSON to Excel." This is very true for simple structures. For those dealing with deeply nested JSON that Power Query struggles with, I've found a few tips helpful: 1) Flatten the JSON structure before importing if possible, 2) Use Python for more complex transformations as you suggested.