Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

i am using Excel 2007 for displaying results of various queries i get from TOAD/Oracle. suddendly it dawned on me that the Auto Calculations on the bottom status bar are not working, although everything that should be ticked is ticked and configured.

when writing arbitrary numbers in ANOTHER new sheet (of the same document) and selecting the corresponding cells, the sum is displayed and is correct

I spent a frustrating hour searching everywhere why is that happening, enabling/disabling add-ons and options and decided to ask a question here.

I came to the conclusion that something is definitely wrong with the FORMAT of the data in the cells, or the way TOAD exports them.

share|improve this question
up vote 2 down vote accepted

It's possible the data has been imported as text, rather than numerical values. Most Excel functions will treat text that looks like a number as a number, therefore skipping the problem, but it does appear to trip up the Auto Calculations.

To see if this is the problem, select some of the cells and apply a number format (CtrlShift1 is an easy shortcut for 0.00). If the cells remain unchanged, then they are text, rather than numbers.

To fix the problem, you might be able to change the TOAD/Oracle export settings or you can convert the text to numbers using the VALUE() function or paste the cells into a text editor and then back into Excel.

share|improve this answer
    
thanks for prompt reply. i couldn't find how to change TOAD Excel export settings easily, so what i finally did is to use delimited text/clipboard export - which again proved to be a bit sticky and messy - since i have both numeric and text columns where i want to keep leading zeroes and such. but your tip about copying to text editor and back into Excel did the trick. therefore i preformat all the cells in Excel to have TEXT formatting (i don't want it to guess format for me), do the export/paste Excel and then do the notepad trick just for the numeric columns. – hello_earth Jul 12 '12 at 10:09
    
I had the same issue, and to check if values were read as text or numeric, I entered in another cell a formula =2*<cell to be tested>. The formula worked well, so I assumed data was regarded as numeric. But it was not the case. Trying to increase or decrease the number of decimals worked as a test (for text data it does not have any effect). – sancho.s Jan 31 at 17:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .