Thursday, March 12, 2015

Hidden features of Excel?

A colleague recently shared an article from LifeHack: 20 Tricks That Can Make Anyone An Excel Expert. The article is meant to turn you into a power user of Excel, able to uncover the features of the spreadsheet and make you more efficient.

However, I saw it as an example of software usability. Software should not make it difficult to access its features. Here are the "power features" of Excel, from the article:
  1. Click the square between column "A" and row "1" to select everything.
  2. Select multiple files at once to open them all.
  3. Use Ctrl-Tab to switch between spreadsheet tabs.
  4. Customize the toolbar using the FileOptionsQuick Access Toolbar menu.
  5. Create diagonal lines in cells using the HomeFontBorders menu.
  6. Drag and select X rows (or columns) to insert X new rows (or columns) at once.
  7. Drag highlighted columns or rows to new locations.
  8. Show or hide blank cells using the DataFilter menu.
  9. Use wildcards when searching with Ctrl-F. Use ? to represent one character and * to represent more than one character.
  10. Generate unique data using the DataAdvanced menu.
  11. Validate your input using the DataData ValidationSetting menu.
  12. Jump to the edge of the sheet using Ctrl + arrow keys.
  13. Transpose a table when copying and pasting, using the HomePasteTranspose menu.
  14. “The best and easiest way to hide data thoroughly is to use HomeFontOpen Format CellsNumber TabCustom→Type ;;; → Click Ok.” (The article really does describe this as "easy.")
  15. Combine text from multiple cells using &.
  16. Change text to uppercase and lowercase using the UPPER(), LOWER(), and PROPER() functions.
  17. Insert literal text into a cell using the single quote mark.
  18. Create your own shorthand using the FileOptionsProofingAutoCorrect Options menu.
  19. Right click in the bottom right to get more stats on cells you've highlighted.
  20. Double-click a spreadsheet tab to rename it.

I knew most of these features already, having used OpenOffice, LibreOffice, and Microsoft Office for years. But I also admit that most of these features are opaque, hidden from the user.

Some of these "power" features are discoverable. I believe users will eventually uncover several of these features on their own. For reference, the "Click the square between column A and row 1 to select everything" functionality is the same on every modern spreadsheet I've used, as shown in this comparison between Microsoft Office and LibreOffice:


The method to select multiple files and switch between tabs is used in other application programs, including the web browser (Ctrl-Tab) and the file manager (select multiple files). Navigating to the edges of a spreadsheet using Ctrl + arrow keys is somewhat discoverable, although this may happen by accident until the user figures out how to reproduce the desired behavior. Other features are a matter of learning the functions available in the program, such as changing text to uppercase and lowercase using the UPPER(), LOWER(), and PROPER() functions.

However, many of the "power" features are hidden deep within menus or available only when other seemingly-unrelated actions are engaged. For example, users must first highlight a group of cells, then access the HomePasteTranspose menu in order to "flip" or "transpose" the cell order. But perhaps the most egregious example of functionality that is difficult-to-reach is this: “The best and easiest way to hide data thoroughly is to use HomeFontOpen Format CellsNumber TabCustom→Type ;;; → Click Ok.” (The article really does describe this as "easy.")

Software must strike a balance between what is easy to use, what is discoverable, and "power" features for "advanced" users. Will most "general" or "average" spreadsheet users need to validate their input? Or create custom "shorthand" when entering in complicated, repeated text? Probably not. But perhaps many "general" or "average" users will want to "flip" a table using the "transpose" feature.

I'm not particularly critical of this spreadsheet example, as I know many "general" users use spreadsheet programs to do simple calculations or to create tables to organize basic data (such as an inventory of books on a bookshelf). But let this and any other "power features" article serve as reminders that software should be accessible by all, both "average" and "power" users.

1 comment:

  1. This is a neat cheat-sheet! My comments:

    #1: Ctrl-A (sometimes twice) also selects everything.

    #12: I've been using the sequence [End] (release) [arrow-key] to move to the end of data. Now I can just hold Ctrl down instead.

    #14: Doesn't the ribbon also have a Home -> Number to save a step? Power users have been formatting cells with Ctrl-1, much faster. Usability aside (or not?) hiding data this way is bad manners, no? If one creates a spreadsheet for others to use, doesn't that create its own usability issues?

    #15: You can combine cell references and string literals this way.

    ReplyDelete

Note: Only a member of this blog may post a comment.