1 2 Previous Next 21 Replies Latest reply: Jul 10, 2013 11:12 PM by Barry
Patrick Forbes Level 1 Level 1 (15 points)

As a new User of Numbers and very well versed in Excel, I'm having a hard time interacting with the two.

Just a few issues:

Can't set a single column to a Monetary value,

Can't 'Freeze Panes',

Correct date display changes half way down to a weird value after opening a .csv.

 

What gives here?


iMac, OS X Mountain Lion (10.8.3)
  • 1. Re: Numbers vs. Excel
    Kappy Level 10 Level 10 (226,775 points)

    Numbers is not 100% compatible with Excel. I suggest you use Excel. Buy Office 2011 for OS X.

  • 2. Re: Numbers vs. Excel
    Patrick Forbes Level 1 Level 1 (15 points)

    That is not an answer. That is an excuse for not having a simple Excel feature built into Numbers. Am sad, now. I have Office on my PPC PB and that response makes me wish to regret my purchase of this Intel box. Is that your intention? I'm seeing Pages issues as well. Makes me even sadder.

  • 3. Re: Numbers vs. Excel
    Wayne Contello Level 6 Level 6 (13,615 points)

    Patrick,

     

    The CSV problem is caused because Numbers attempts to infer the "type" of the information in each cell.  So if you look at the actual data often it is something that confuses Numbers as to the type.  This can be avoided ( at the expense of time) by formatting the table as text, then pasting the contents of the CSV into the preformatted table.

     

    You should also know that this forum is answered by fellow users who have no ability whatsoever to affect future features.  Kappy's answer is, infact, and answer because neither he nor I nor anyone else in this forum is responsible for feature that do, or do not , work in Numbers.

     

    My guess is you aren't going to want to open a CSV file, then replace all the comma delimeters with tabes, then copy, then paste into Numbers.  If you are  (that's what I do) great, otherwise you are stuck with having to use Excel (or an office clone like LibreOffice).

     

    For large datasets I would recommend avoiding Numbers anyway.  Try LibreOffice.... you may like it AND it's free.

     

    Best regards

    Wayne

  • 4. Re: Numbers vs. Excel
    Kappy Level 10 Level 10 (226,775 points)

    I'm trying to help you out. You're just being rude.

  • 5. Re: Numbers vs. Excel
    Patrick Forbes Level 1 Level 1 (15 points)

    I get that. However, the format change in the Date column ocurrs about half way down. That's what's really odd. Excel doesn't seem to ever have that problem.

    My Bank sends me my account statement in cvs which Moneywell likes quite well. But when I need to print the cvs for filing with its receipts, that's where it becomes a mess. Excel doesn't make it into a mess.

    I'm posting this issue, as well as my Pages problem, here as I suspect (hope?) Apple folks lurk here and just might one day key into this defect and (hopefully) create a fix.

    In the interim, I use my PPC PB for the conversion and printing of my statements while waiting for Apple to get it right.

     

    Are you seeing this, Cupertino?

  • 6. Re: Numbers vs. Excel
    Patrick Forbes Level 1 Level 1 (15 points)

    Sorry, Kappy, my response to your information was NOT meant to be rude. Your recommendation to purchase Office to resolve my issue just flew in the face of my effort to make more use of my Intel iMac that I'm having problems with. I have a PPC PB with Office and it is phenomenal. I expected this Intel box to be better. I doesn't seem to be. 

  • 7. Re: Numbers vs. Excel
    Wayne Contello Level 6 Level 6 (13,615 points)

    Patrick,

     

    If you would like to make a suggestion regarding Numbers features/bugs use the menu item in Numbers "Numbers > Provide Numbers Feedback"

  • 8. Re: Numbers vs. Excel
    Patrick Forbes Level 1 Level 1 (15 points)

    Thanks, I hope that might work, if they're listening.

  • 9. Re: Numbers vs. Excel
    Yellowbox Level 5 Level 5 (4,580 points)

    Hi Patrick,

     

    I just opened a csv from my bank and tried what you tried.

     

    Can't set a single column to a Monetary value

     

    Do the positive values have a + sign in front of them? That makes them Text. Find and replace to remove the +

     

    Can't 'Freeze Panes'

     

    True. That is a feature of Numbers. However, you can do:

     

    Menu > Table > Freeze Header Rows or Freeze Header Columns

     

    I guess your CSV has no Header Rows or Columns (it is a plain text file with commas to separate columns. Numbers *is* smart enough to translate commas as separators!). You will need to add a Header Row:

     

    Menu > Table > Header Rows > 1

     

    Correct date display changes half way down to a weird value after opening a .csv

     

    Try opening with TextEdit to see if there is anything strange.

     

    I guess banks produce statements that look nice in a web browser.

     

    Regards,

    Ian.

     

    Message was edited by: Yellowbox. Changed 'on a computer screen' to 'in a web browser'

  • 10. Re: Numbers vs. Excel
    Patrick Forbes Level 1 Level 1 (15 points)

    Thanks. Regarding the date problem, the first attached pix is what Numbers did to my csv. The second is what Text shows for the same period. Can't understand why it does that. Every time, too. Excel doesn't, ever.

    Screen Shot 2013-07-06 at 3.09.55 PM.pngScreen Shot 2013-07-06 at 3.10.56 PM.png

     

    The Header freeze thing just selected a column. I need to play with that feature a bit more to figure out how to make it work properly….

  • 11. Re: Numbers vs. Excel
    Yellowbox Level 5 Level 5 (4,580 points)

    Hi Patrick,

     

    1. Dates.

     

    As far as I can see, the dates make sense. The cell format has changed for some reason.

     

    6/17/13 I intrerpret as June 17 2013.

     

    0006-12-13 I interpret as June 12 2013.

     

    Select Column A by clicking once on the label "A"

     

    Format that Column in Inspector > Cells > Date and Time > choose the Date format you want for that whole Column. Set the Time display to None:

     

    Screen Shot 2013-07-07 at 4.13.53 PM.png

     

     

    2. Freeze Header Rows.

     

    Numbers will not split panes, but it will freeze Header Rows (or Header Columns) but first you need a Header Row!

     

    I suggest that you first insert a blank Row above your data. That will give you a spare Row to play with, and you can delete it if you find you didn't need it.

     

    Your CSV in Numbers might look something like this:

     

    Screen Shot 2013-07-07 at 4.23.23 PM.png

     

    Click on the grey label ("1") to the left of Row 1. Hover over the "1" until you see a downward arrow, then click on the arrow to see a menu:

     

    Screen Shot 2013-07-07 at 4.27.19 PM.png

     

    To get this:

     

    Screen Shot 2013-07-07 at 4.29.16 PM.png

     

    Now you can go to Menu > Table > Header Rows > 1

     

    and type in your Column Labels:

     

    Screen Shot 2013-07-07 at 4.31.27 PM.png

     

    OK, I was wrong. You don't need that spare Row (now Row 2). You can delete it.

     

    Now Menu > Table > Freeze Header Rows

     

    and you can slide the table under Row 1. Row 1 is frozen and stays put as you slide the table.

     

    The Numbers User Guide explains this. The Guide is searchable. Download the Guide from the Help Menu in Numbers.

     

    Regards,

    Ian.

     

    Message was edited by: Yellowbox. Oops, my fault in reading USA/Canada dates. I hope I now have them right.

  • 12. Re: Numbers vs. Excel
    Patrick Forbes Level 1 Level 1 (15 points)

    Re: the Date thing. This is how Excel handles that same portion with no kinks:Picture 1.png

     

    Haven't had time to check the others out.

  • 13. Re: Numbers vs. Excel
    kharisma Level 1 Level 1 (105 points)

    Patrick, what is your localisation setting on your computer?

     

    My computer handles those dates the same way yours has; it is interpreting the dates as DD/MM/YY, but those dates in the csv are in US order - MM/DD/YY.

     

    If you are in a country that uses MM/DD/YY, I don't know why one app would suddenly have problems when others don't.

     

    In the meantime, one possible solution is this:

     

    Make a copy of the original csv.

    Open the copy in Textedit.

    Find-Replace all slashes to commas.

     

    This has the effect of creating three columns for the date.

     

    Open the file in Numbers.

    Create an extra column at the front of the table, and use this formula: =DATE(2000+C2,A2,B2)

     

    Copy the new date column, and choose Edit > Paste Values.

    Delete the work columns.

  • 14. Re: Numbers vs. Excel
    Patrick Forbes Level 1 Level 1 (15 points)

    Local is Canada: Day Month Year

    The rest of your suggestion is WAY too much work. Excel doesn't have that problem. Numbers shouldn't.

    The whole purpose of this is to print a simple and paper consertive Bank Statment to which the months receipts get attached and then gets filed. That's all.

1 2 Previous Next