How to find Apple's Numbers power spreadsheet features behind the simplicity

Posted:
in Mac Software
Numbers is still seen as a lite version of Excel but Apple's spreadsheet is exceptionally powerful -- it just keeps as much of that power hidden away as it can. AppleInsider adds up all the time-saving benefits that are just a click away.




We are all so used to how Excel works -- and how Lotus 1-2-3 did before it, how everything back to the original Visicalc functioned too -- that we can miss the fact that Numbers is different.

It's typical Apple design in that it appears so simple that you assume it's limited. Then the basics of using it are typically so obvious that you just get on with what you need to do. That's a rather remarkable fact, that a spreadsheet can be made obvious, but instead of appreciating Apple's design, we don't think about it at all.

Sheets and tables

In all other spreadsheet apps, you get a sheet to work on. You might add another one, two or even hundreds, but at any given time, what you see on screen is a single sheet. Your screen holds a single spreadsheet and it's filled with empty rows and columns.

In Numbers, you get tables instead. These are spreadsheet occurrences: you can have as many tables as you like and they each work as separate spreadsheets. At the top of the screen you could make a 10 x 10 grid and then next to it a 15 x 15 one for something else.

It makes little difference to you when you're using Numbers as a tool for yourself but when you need to show other people, it can be significant. Where Excel and the others will show you a chart by popping it over the grid of cells, Numbers just puts the chart next to a table.




Rather than a sea of empty cells, you make the Numbers tables be exactly the width and height you need for your data.

It's easy for you to see what you're working on and it's simple for other people to both grasp and be certain that they're seeing all they need.

Plus this means that Numbers requires fewer steps to make a presentable or appealing document. There is more you can do, though, and it's again something that you don't think of if you're used to other spreadsheets.

For instance, you'll know that you can change a chart by going back to the figures and altering those. In Numbers, though, you can click on a chart to get an option to Edit Data References. Click on that and then every line in the table gets a color label. Click on any part of the chart and the relevant line of figures is highlighted.

It's a fast way to see where a particular number is on the table when something looks wrong. When you've got all the figures right, though, you can then do more with the chart itself.




Click once to select it. Then double-click on a segment such as part of a pie chart. If that double click is on the label then you'll get options for altering the appearance of that.

However, if you double click away from the label, the segment itself highlights with grab handles. There are four handles, arranged equidistantly around the segment, but they all do the same thing. They let you drag the segment out from the chart. It doesn't matter which handle you grab and drag on, the segment moves.

It would be good to be able to make a segment bigger for emphasis but you can't. Except that if you click and hold in the middle of the chart, you can then rotate the entire piece and affect what's emphasized.

You can't change the segment size without altering the numbers behind it but you can present the exact same chart in ways that emphasize or reduce points for you.




Not that we're saying you would choose the chart that best backs up your position, but you can. You can do it by pulling out segments from a pie chart like this or just going through the 27 possible chart types.

Those 27 include several that are interactive: you get the chart plus a slider that shows the data over time.


Keyboard shortcuts

To increase the number of rows or columns, you can click on the icons at the edges of the spreadsheet. However, if you're currently typing into the bottom right cell, you can use the keyboard instead.

Just press Tab and you both create an extra column and move the cursor into the new bottom right cell.

Or press Return and you've now got an extra row with, again, the cursor moved to the last cell.

These same shortcuts work on Numbers for iPad, too. If you have an external keyboard for your iPad, hold the Command key down for a moment and you'll see a list of the most commonly used keystrokes.


Header columns

You can freeze a column or a row so that it stays where it is even as you scroll down or across through everything else. These are called header columns or rows and are handy for when you so much data that the sheet necessarily scrolls off the bottom of the screen.

Only, there's a limit of how many header columns or rows you can have. The maximum number is five and though that should be plenty, you can very easily create them without realising.

When click on a column, say, and then on the arrow that gets you a dropdown menu, it's remarkably easy to choose Add New Header Column Before because the menu changes.

If you click on a dropdown arrow on any column that isn't a header one, the option reads Add Column Before instead. Every Numbers spreadsheet has a header column, row A, so if you don't set one and haven't even heard of them, you get this difference in the menus.




This means the first time you become aware of header columns could be when you've accidentally created five. In that case you continue trying to add another column and can't see why it won't happen. There's no warning dialog when you do this, so you have to know.

Oddly, there is an option called Convert to Header Column but there isn't the opposite. The only way to undo having created a header column is to select all of the cells and paste them into a regular column. Then delete the header one.

Give people options

When it's just you working on the spreadsheet, that kind of fiddling between header and regular columns is a mild pain. When you're instead creating a spreadsheet for other people to fill in, you need to make all as simple as possible for them.

Even if it's their job to enter data into your sheet, you can still make the difference between them enjoying it or finding it a chore. You can make the difference them being accurate or not, too.

Click in a cell or in a column. Then click the Formatting button and choose the Cell tab. UnderData Format you can choose Pop-Up Menu.

By default, this offers you three options that you can retitle to anything you need. You can also add or remove options.

However, you can then also apply conditional highlighting. Give people a set of pop up options they can choose and then have the sheet automatically change the color or font of them.

This means you and they immediately see important details but it also means they can't mistype what they enter. So you know the sheet is as accurate as it can be.


Do use Numbers for text

That example of pop-up menus and conditional formatting is of text in a staff survey form. Text. Usually if you show us a spreadsheet that's just text, we shake our heads and start telling you that you should move to a database.

However, there are times when Numbers is the right choice for text.

For instance, we worked with a large table in Apple Notes -- that's a table in the regular, word processing sense instead of Numbers' version of a spreadsheet -- and it was a problem.

After perhaps 60 rows of data, the note became slow to open and prone to crashing.

We moved that same data over to a Numbers spreadsheet where it's currently holding just under 450 entries with not the slightest hiccup.

We should still move it to a database. Yet Numbers is so simple to use, it has such well-designed extra touches and it's so reliable that it's irresistible.






Keep up with AppleInsider by downloading the AppleInsider app for iOS, and follow us on YouTube, Twitter @appleinsider and Facebook for live, late-breaking coverage. You can also check out our official Instagram account for exclusive photos.

Comments

  • Reply 1 of 18
    love Numbers. I've used Excel since it first came out on the Mac back in the '80s, through a couple decades on Windows for Intel and Alpha, and have learned to loathe the ribbon and user-hostile interface it has. However, I do have one gripe, and one hidden power feature not touched on above, regarding Numbers.

    First, the gripe. Numbers has never had pivot tables. In the iWork 9 version of Numbers, it had Table Categories, and that was a brilliant way of viewing the same detail. Sadly, that was stripped out during the iCloud-enabling rewrite and no longer exists. I'd love for Table Categories to come back!

    Not mentioned above: when laying out your sheet full of tables and charts and pictures and text boxes, you can easily arrange things that don't print out well. Turn on your rulers and drag guide lines to match your print space so that you can be sure to place elements where they'll not be ignominiously chopped up in little bits when transferred to paper or PDF. Just calculate your print space as your paper size minus your margins (top + bottom, or left + right) and divide by your print scale factor. I've not found an automated way to do that -- perhaps Apple could add such a feature?
    cgWerkswatto_cobrapscooter63
  • Reply 2 of 18
    Oh, one more gripe. (Sorry.)

    32,768? Really Apple? These machines have gobs of virtual memory. I deal with spreadsheets that frequently have more than 2^15 rows. This is a 64 bit machine -- would 2^32 rows really be too much to ask for?  ;)
    razorpitspace2001philboogie
  • Reply 3 of 18
    alphaman said:
    First, the gripe. Numbers has never had pivot tables. In the iWork 9 version of Numbers, it had Table Categories, and that was a brilliant way of viewing the same detail. Sadly, that was stripped out during the iCloud-enabling rewrite and no longer exists. I'd love for Table Categories to come back!
    This. Numbers would be so much better if there was something to quickly organise large collections of data (e.g. pivot table, but maybe made simpler and more powerful than its Excel counterpart). Regardless, I use it regularly (as well as Pages) to open and view Office documents I get at work.
    watto_cobraphilboogie
  • Reply 4 of 18
    razorpitrazorpit Posts: 904member
    alphaman said:
    Oh, one more gripe. (Sorry.)

    32,768? Really Apple? These machines have gobs of virtual memory. I deal with spreadsheets that frequently have more than 2^15 rows. This is a 64 bit machine -- would 2^32 rows really be too much to ask for?  ;)
    I agree with everything you said. I use Numbers (and Pages) for just about everything. I'm upset at the things they removed from both programs in the redesign, but admittedly both are better programs overall than their predecessors when you factor in things like iOS and web browser functionality. Using the web versions of the software is so much more fluid than the Office 365 alternative.

    I was surprised by the 32,768 limit. I forget what I was trying to go through, I think it was a report from Box.com, but Numbers really choked on it. It was the one time Numbers let me down.
  • Reply 5 of 18
    I use excel at work but Numbers at home. Numbers is way nicer IMO, just pleasant to use. Using free-form tables within sheets rather than one giant table that is the sheet (excel) is so much better. This allows you to stack multiple tables each with different column widths and have them all look great and precisely as they should. 

    The web client via iCloud.com is nice. Free iOS clients is great. I won’t be paying for microsoft’s ransomeware. 
    watto_cobra
  • Reply 6 of 18
    Numbers is great for simple things but in no way even close to the power of excel when it come to pivot tables and filtering data. Not even close!
    boboliciousphilboogie
  • Reply 7 of 18
    Numbers is great for simple things but in no way even close to the power of excel when it come to pivot tables and filtering data. Not even close!

    I revert to Numbers 09 to use what may resemble pivot tables and sort/summarize data using 'table categories', and agree it would be my most requested feature to be restored, or even better developed...

    Is this feature deprecation a trade off for iCloud, which I never knowingly use?

    www.youtube.com/watch?v=jkJzyTdBaHo

    www.youtube.com/watch?v=Y906lhpFLT4

    edited August 24
  • Reply 8 of 18
    fastasleepfastasleep Posts: 1,968member
    It should be mentioned that Apple actually has some decent online documentation for Numbers:

    Portal
    https://support.apple.com/numbers ;

    Mac in-depth docs (from above):
    https://help.apple.com/numbers/mac/5.1/
    watto_cobrapscooter63space2001jony0
  • Reply 9 of 18
    MplsPMplsP Posts: 783member
    I like numbers for a lot of things, but I often find the interface to be less efficient than Excel (at least Excel 2011) The old Excel toolbar had everything you used 90% of the time on one toolbar; Numbers often makes you switch or dig to find it.

    Also, Numbers still has still has no facility for rotating text sideways in a cell. 

    Having the ability to have multiple separate tables on one sheet is very handy, though.
    watto_cobra
  • Reply 10 of 18
    wizard69wizard69 Posts: 12,641member
    As an app Numbers is great from the user interface standpoint!   But that doesn't make up for the fact that it is basically useless due to gross compatibility issues.

    Try using numbers with spreadsheets that tilt to the technical side and you will get very frustrated importing files originally developed in Excel.    In fact I'm close to zero usability.    If I remember correctly a simple sheet I tried to import implemented ISNUM that Numbers fails to implement.   That broke the whole spread sheet.   That was a simple sheet, it is not uncommon for Numbers to generate tens even hundreds of errors importing a decently complex Excel file.  

    It is so bad that I basically gave up on it.
  • Reply 11 of 18
    paxmanpaxman Posts: 4,588member
    Every time Numbers is the subject the same conclusion can be drawn - For most people Numbers is great. More than capable and very user friendly. For power users Excel is the beast of choice. I don't think Apple has any desire to replace Excel with Numbers. The only problem for me as an long time Excel user was that the interface was different. For that reason most spreadsheet user probably prefer Excel - because that is what they know. I changed over years ago and much prefer Numbers. I use it on a daily basis for my business, but I never have to share with anyone. I do have Excel and when someone sends me an Excel doc once in a blue moon, I use it.

    watto_cobra
  • Reply 12 of 18
    fastasleepfastasleep Posts: 1,968member
    paxman said:
    Every time Numbers is the subject the same conclusion can be drawn - For most people Numbers is great. More than capable and very user friendly. For power users Excel is the beast of choice. I don't think Apple has any desire to replace Excel with Numbers. The only problem for me as an long time Excel user was that the interface was different. For that reason most spreadsheet user probably prefer Excel - because that is what they know. I changed over years ago and much prefer Numbers. I use it on a daily basis for my business, but I never have to share with anyone. I do have Excel and when someone sends me an Excel doc once in a blue moon, I use it.

    You know Numbers can open and save Excel files, right?
    watto_cobra
  • Reply 13 of 18
    aegeanaegean Posts: 105member
    I have to tell that whatever Numbers does, it does it better than Excel. Yes, its still way behind Excel in terms of advanced features but I truly enjoy using Numbers. I hope Apple will further improve its functionality, slowly but surely.
    edited August 26
  • Reply 14 of 18
    It should be mentioned that Apple actually has some decent online documentation for Numbers:

    Portal
    https://support.apple.com/numbers ;

    Mac in-depth docs (from above):
    https://help.apple.com/numbers/mac/5.1/

    Thanks for those links!
    fastasleep
  • Reply 15 of 18
    I am a pretty heavy user of Excel at work, but I like Numbers for personal spreadsheet use at home.  Numbers has lots of features that would satisfy most basic users and it is far and away better than Excel in the graphics and layout category.  Input for certain data types is also better (pre-formulated + or - a day, month, or year when time units are used for example) and it is probably also easier for someone to learn to use who has never used a spreadsheet before. What maybe off-putting for some posters here is the title of this article.  If someone thinks any of the features listed in this article are "power spreadsheet features" (with the exception of conditional formatting), then they have no idea what a power user does with a spreadsheet, especially in the enterprise.  Power features would be those that actually aid in data crunching or otherwise aid the user in actually accomplishing the work inside a spreadsheet.  Nearly everything mentioned in this article is just formatting which might make it easier to make the spreadsheet look nice, but has no impact on functionality.

    Pivot tables have already been mentioned, but Pivot charts have not.  Excel will automatically issue a table when you use this feature so that you aren't first making a table and then trying to represent the data graphically afterwards.  Another feature of Excel that Numbers seams to lack entirely is working with spreadsheets on any sort of server location.  Numbers will download a local copy (just like Excel), but this local copy is broken from the server and changes are only saved locally.  The exception to this is how it interacts with iCloud, but most Enterprise users are not using spreadsheets saved to iCloud (even if their IT department hasn't blocked access to iCloud in the first place).  External spreadsheet references or even links to other types of external documents or data also doesn't work in Numbers outside of web addresses. There is no way to define and search for duplicates, just simple find/replace. 

    paxman said:
    Every time Numbers is the subject the same conclusion can be drawn - For most people Numbers is great. More than capable and very user friendly. For power users Excel is the beast of choice. I don't think Apple has any desire to replace Excel with Numbers. The only problem for me as an long time Excel user was that the interface was different. For that reason most spreadsheet user probably prefer Excel - because that is what they know. I changed over years ago and much prefer Numbers. I use it on a daily basis for my business, but I never have to share with anyone. I do have Excel and when someone sends me an Excel doc once in a blue moon, I use it.


    The examples above are just a few power features that come to mind without opening Excel and purposely looking for advanced features.  Like paxman mentions, Numbers is nice to use and has most tools that homeowner's or maybe even self-employed people (think creating invoices, basic "database" functions, uncomplicated tabulation, and other simple tasks) would need.  These use-cases are far from power user levels though and that's ok as Numbers' target market likely is not enterprise clients, but rather, the "Prosumer" that Apple targets with most of their offerings.
    edited August 27
  • Reply 16 of 18
    philboogiephilboogie Posts: 7,430member
    MplsP said:
    Also, Numbers still has still has no facility for rotating text sideways in a cell.
    There is a workaround, but not a solution, to that: https://discussions.apple.com/docs/DOC-7201
  • Reply 17 of 18
    BTW, you can convert header columns and rows back into regular columns from the Table > Headers & Footer section of the Format panel without needing to copy-past them.  If you decrease the number, then the right most column (or bottom most row) revert back to normal.
  • Reply 18 of 18
    NeedMoreCoffeeNeedMoreCoffee Posts: 1unconfirmed, member
    You might all be happy to know that Numbers version 5.2, released 24 Sept 2018, has now added "Smart Categories" / pivot tables. I've been a hardcore pro Excel user for all my working life - but have to say, Apple's eventual implementation is, as usual, more elegant!
Sign In or Register to comment.