Excel Tips and Tricks

I spent much of last week talking to scientists about Excel.  One of the questions I asked is What drives you crazy about Excel?   This definitely falls in the opening-a-can-of-worms category, but people were surprisingly clear and helpful in their comments.

Some of the complaints surfaced in multiple conversations, and I had to wonder whether the smart folks over at Microsoft Research had heard these complaints before.  I also figured that MSR they had, they might have already addressed them in Excel’s functionality.  So I did a little bit of poking about the Excel menus, and I was pleasantly surprised to find that Excel actually does a lot of the things people would like it to… here’s a few neat tricks I discovered:

1. Automatically saving as .xls:  Microsoft introduced the .xlsx format in 2007; it is based on XML (eXtensible Markup Language, read more on Wikipedia). The .xlsx format came up quite a bit while talking with scientists, especially if they  work with collaborators who have problems opening or using this format.  I wish I could set it to automatically save as .xls instead of .xlsx.   Good news! This is possible and very easy- just change your Excel preferences.
   1. On a Mac: In Excel, go to “Preferences…” in the “Excel” dropdown menu. In the “Sharing and Privacy” row, select “Compatibility”.  The second section of this menu has a dropdown menu where you can designate what format you would like your Excel files to be saved as by default.
   2. On a PC: in Excel, click on the “Office Button”.  Select the “Excel Options” button at the bottom of the menu that appears.  Select “Save” from the bar on the left. Under “Save workbooks” near the top, you can choose the default format from the dropdown menu.
2. Prompt you for workbook metadata: If you are terrible at documenting your data (like many of us are), you should think about turning on a feature in Excel that prompts you for workbook-level metadata.
   1. On a Mac: In Excel, go to “Preferences…” in the “Excel” dropdown menu. In the “Authoring” row, select “General”.  There is a box you can check for “Prompt for Workbook Properties”.  Check that box. When you save your workbook, Excel will pull up this:
   2. On a PC: I’m still looking… but I’m sure it’s there somewhere.
3. Regional settings: This is not technically an Excel setting, but it can influence how Excel handles your data, spell-checking, number formats, currency formats, etc.
   1. On a Mac: go to “System Preferences”. In the “Personal” row select “Language and Text”. You can change your language settings, how the dates, times, and numbers should be displayed, and your region. (I was surprised to learn that my laptop thought I was still in Edmonton, Alberta!)
   2. On a PC: Go to the Start menu, select the Control Panel, and open the “Regional and Language Options” Menu.

It’s not altogether surprising that scientists aren’t always aware of Excel’s potential.  Along with our less-than-ideal education about data management (see previous posts here and here), we are often left to trudge through Excel alone, learning tips and tricks along the way from classmates and colleagues.  More tips and tricks to come. Stay tuned!