Skip navigation

Numbers vs. Excel

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

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)
  • Kappy Level 10 Level 10 (221,095 points)
    Currently Being Moderated
    Jul 1, 2013 1:18 PM (in response to Patrick Forbes)

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

  • Wayne Contello Level 6 Level 6 (12,665 points)
    Currently Being Moderated
    Jul 1, 2013 1:37 PM (in response to Patrick Forbes)

    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

  • Kappy Level 10 Level 10 (221,095 points)
    Currently Being Moderated
    Jul 1, 2013 1:51 PM (in response to Patrick Forbes)

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

  • Wayne Contello Level 6 Level 6 (12,665 points)
    Currently Being Moderated
    Jul 1, 2013 2:03 PM (in response to Patrick Forbes)

    Patrick,

     

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

  • Yellowbox Level 4 Level 4 (3,920 points)
    Currently Being Moderated
    Jul 1, 2013 8:36 PM (in response to Patrick Forbes)

    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'

  • Yellowbox Level 4 Level 4 (3,920 points)
    Currently Being Moderated
    Jul 6, 2013 11:48 PM (in response to Patrick Forbes)

    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.

  • kharisma Level 1 Level 1 (105 points)
    Currently Being Moderated
    Jul 8, 2013 10:08 PM (in response to Patrick Forbes)

    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.

1 2 Previous Next

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.