You may have set the cell formatting to “Text” and then typed the formula in it. When you set the cell formatting to “Text”, Excel treats the formula as text and shows it instead of evaluating it. This process is particularly troublesome when importing significant amounts of data.If you find yourself in this boat, try this trick: Start with a range of pre-entered data.But with hundreds of rows of data, this will take forever. The number formatting of all the cells will update. What it does is take all the values from each row and then re-enters them into the cells automatically.To speed things up, select the column and go to the Data ribbon and click Text to Columns. For this reason, this trick will not work for cells that are formulas.I’ve worked with this issue for a long time, and it’s actually caused me to avoid using Excel formulas in tables generated via Power Query all together.Having said that, there is now an easy way to fix this which renders that avoidance obsolete. We have a simple table called Animals as follows: And it gets landed in another table.
We’d like to give it custom formatting, so it looks more like a time from a stopwatch.
There’s a quirk with Microsoft Excel 2010 (and possibly other versions) where custom number formats don’t get applied to existing data.
This quick fix can save you from the tedium of re-entering thousands of rows of data.
This stubbornness usually happens when choosing a custom number format.
You can update it by double-clicking the cell, making no changes, and then press Enter, but this can be very tedious.