When it comes to spreadsheets Excel is king. When Apple released iWorks people bemoaned Numbers' lack of all sorts of things. Numbers has had a lot of work done to it and I feel the idea that Numbers is worse than Excel is not a fair one. The two approach things completely differently but Numbers has far more power than it's given credit.
Part of the reason that people think that Numbers isn't powerful is because Excel has VB and Numbers doesn't have any scripting language. This is not the case as Numbers does use AppleScript which is a very powerful tool. For many cases you won't need it but if you go to Applications/Utilities or from Launchpad go to Other then launch Script Editor. In the File menu click on Open Dictionary and you'll be given a list of supported applications on your machine. Find Numbers and look through what it has to offer.
But Numbers is different to Excel. This statement is obvious but not one taken into account when Numbers is compared to Excel. There's a lot in Numbers that is done very simply. It's this simplicity that throws a lot of people. So I'll tell you about some features I use all the time and some I've just found out about.
If you're using Excel or LibreOffice/OpenOffice or Google Docs or any other spreadsheet on any platform you'll be greeted to a single sheet that is massive. In Excel there are 1,048,576 rows and 16,384 columns. That's about 17,179,869,184 cells. That's a lot more cells than most people will ever need and that's only on one worksheet. Multiply that by however many worksheets you have in your spreadsheet and you'll see a lot of wasted space. This also adds to the size of the spreadsheets.
Numbers approaches this differently. It starts with a table of 10 rows and 5 columns. You can expand this to your needs very easily though. But you'll only have what you need. This is a much better way of doing things in my not so humble opinion.
Here's a little video to show you what you can do with tables in Numbers:
This can be done in Excel and Numbers and is relatively easy in both but Numbers trounces Excel here. Excel uses a document import feature to import an XML file direct from a website. This works really well but it can be a complex process for some to utilise. Apple being Apple decided to make this very simple. Here's how to convert a currency easily:
Date and Time
If there's one thing that Numbers kills at it's working with date and time, at least with time. When it comes to time Numbers has a feature no other spreadsheet has. Numbers has a cell type of Duration. When set it will force any calculation to be a duration. This is a big thing for me because I use it to track a number of things where time is crucial. For example my timesheet at work.
Most spreadsheets will give you a decimal format but this can be a bit of a hassle for reading.
However, if you want to Apple's duration for a price it's not going to work as you can't change a duration to say currency. But Apple made this simple as well. Simply by using this formula:
This will give you a decimal value. In the video example the duration ended up being 2 hours 15 minutes. That then becomes a decimal number of 2.25 with this formula.
Working with date and time in Numbers is well in advance of everyone else and it surprises me that Excel, which everyone says is far more powerful than Numbers, doesn't have this functionality.
This one blows my mind that it's not in Excel. Once again everyone says that Excel is so powerful but it requires working with VB to achieve. Just watch Numbers in action will you:
This of course is the same if I use PopUp Menus, ratings, Steppers, or Sliders. These are simple functions that can allow you to do powerful things but they don't exist in Excel and the like.
I'm not going into everything else Numbers can do. Integration with Pages and Keynote is unbelievable. There's now even the ability to easily track stocks.
I'm not saying Numbers is perfect. It still can't access data in other spreadsheet documents like Excel can. This could be helpful to reduce sizes of spreadsheets with common data across a number of spreadsheets. But we can live without that at the moment.
Another people think is missing is Pivot Tables but it's there just not called Pivot Tables. Here's a cool vid that shows how it's used:
While it's not perfect it is a seriously great app. I'm kind of sad that Windows and Linux users won't get to use this natively but you can use the iCloud version. It really is a worthy contender for serious business use. Its true power is being able to do seriously complex things very easily. That should never be looked down upon at all. That's a hard thing to do.