Frustrated with Excel

System
System Posts: 100,534 🤖 Admin 🤖
edited 12:08PM in AAT member discussion
I regularly export Quickbooks reports to excel to work with the data, however there's a stupid little quirk with it that I'd love to know how to fix.

Once the report is in excel, for some reason if I type a formula into a cell within the original report it shows the whole formula instead of the result. If I type the same formula a few cells along from the original report it's fine, and I can then drag that cell to wherever I need it without it reverting back to showing the whole formula.

Does anyone know why this happens and if there's a fix for it?

I've checked on 'options' and the 'formulas' box is not ticked. I've also tried copying & pasting the whole report in a new workbook before working on it, but with no luck :(

ta

Comments

  • System
    System Posts: 100,534 🤖 Admin 🤖
    Re:Frustrated with Excel

    My son has been working on an excel spreadsheet (from Quickbooks) and hasn't mentioned any problems. (Computer Whizzkid). I will check with him and let you know if he came across this.
  • System
    System Posts: 100,534 🤖 Admin 🤖
    Re:Frustrated with Excel

    Hi Jodie,

    This has happened to me before and seemed to be a result of the way that QB format's the cell as 'text' and not 'general'.

    If you reformat the cell to general it still won't correct it - but hitting F2 seems to update it.

    Sometimes...........




  • System
    System Posts: 100,534 🤖 Admin 🤖
    Re:Frustrated with Excel

    :D thanks so much :D

    That works - I'll just have to remember to change the whole spreadsheet to 'general' before working with it. It messes up the dates but they're always the american dates so need reformatting anyway so that I don't get confused!

    Why didn't I ask this 2 years ago? :lol:
  • System
    System Posts: 100,534 🤖 Admin 🤖
    Re:Frustrated with Excel

    As a general pointer to change a spreadsheet from showing formulas to values, and back again you need to press Ctrl & the grave key "`" (to the left of the number 1).

    Scott.
  • System
    System Posts: 100,534 🤖 Admin 🤖
    Re:Frustrated with Excel

    thanks scotty, i'm sure that'll come in use at some point, but it doesn't work for the QBs quirk i'm afraid!

    it's funny - i remember my dad getting a computer about 20 years ago and telling me to NEVER EVER push that key, so i never have! I felt like a bit of a rebel doing it :lol:

Categories

Privacy Policy