After months of quietly defending the Mac against relentless condemnation for the way Excel 2004 mangles formulae and references in spreadsheets created on Windows systems, I discovered today a three-click solution for a whole swath of problems: the “Analysis ToolPak” Add-in for Excel.
The problems manifest as #NAME or #N/A appearing all across your delicate sheets and workbooks when you alter a cell containing a date used in a formula. I can find no reasonable explanation for this, but the plugin apparently makes Excel calculate things the same as it does in Windows. Why it doesn’t just do this already, I can only speculate angrily.
Installing the plugin to fix things is simple:
- In Excel, select “Add-insâ€¦” from the “Tools” menu.
- Check the box next to “Analysis ToolPak”
- Click “OK”
The plugin installs automatically and takes effect immediately. Any open spreadsheets will be re-calculated, but I would recommend closing and re-opening any affected spreadsheets for the sake of consistency.
Here’s looking forward to Office 2008, the elimination of all compatibility issues between the platforms, and the subsequent series of blog posts about dealing with the remaining compatibility issues between the platforms.