'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix Excel #DIV/0! Errors When Calculating CSV Percentages on Mac
Question details
The user needs to resolve a #DIV/0! error in Excel on Mac that occurs when calculating percentages or averages from data imported from a CSV file.
- Product
- Microsoft Excel
- Device & OS
- macOS
- Scenario
- Calculating averages or percentages from data imported from a CSV file using Power Query.
- Observed behavior
- Excel returns a #DIV/0! error even though the denominator values appear to be non-zero, usually because the imported CSV numbers are being treated as text due to locale mismatches.
Ensure your CSV file is completely saved and closed in other applications, and verify your Mac's system region and language settings match the origin of your data.
Transform CSV Data Types in Power Query Before Loading
Use Power Query's Transform feature to ensure numeric values are properly recognized as numbers instead of text, resolving regional format mismatches.
When Excel imports CSV data, differences in regional settings (such as using commas instead of periods for decimals) can cause numbers to be imported as text. Mathematical functions will ignore these text cells, causing a #DIV/0! error.
Navigate to the Data tab and select 'Get Data (Power Query)' to open your CSV file.
Instead of immediately loading the data, click the 'Transform Data' button to open the Power Query Editor.
Look at your percentage or numeric columns. If they are aligned to the left or display a text data type icon (ABC) in the column header, Excel is treating them as text.
Click the 'ABC' data type icon in the column header and select 'Decimal Number' or 'Percentage' to convert the text entries into valid numbers.
Click 'Close & Load' on the Home tab to return the corrected data to your worksheet. Your formulas should now calculate without the #DIV/0! error.
Match CSV Locale Settings During Import
Specify the origin locale of the CSV file during the import process so Excel correctly interprets regional formatting.
Use WPS Office for Hassle-Free CSV Data Imports on Mac
If Excel's regional settings continue to cause formula errors on your Mac, try WPS Office. It provides an intuitive CSV import wizard that makes managing data formats seamless, ensuring your formulas work perfectly.
Frequently Asked Questions
Why does Excel show #DIV/0! when my denominator is clearly not zero?
This happens when Excel treats the cell values as text rather than numbers. Mathematical functions like AVERAGE ignore text cells completely, which effectively results in a denominator of zero during the calculation, triggering the error.
How do regional settings affect CSV imports on a Mac?
Different regions use different characters for decimal points and thousand separators (e.g., European regions often use a comma instead of a period). If the CSV's format doesn't match your Mac's system settings, Excel fails to recognize the values as numbers and defaults them to text strings.
Can I fix the #DIV/0! error without using Power Query?
Yes. You can use the 'Text to Columns' feature under the Data tab to force Excel to convert text-formatted numbers back into real numbers. Additionally, you can wrap your formulas with the IFERROR function (e.g., =IFERROR(A1/B1, 0)) to hide the error display.
