A few weeks back I posted some nifty Excel functions that you might not have been aware of. I’m certainly no expert in Excel, but I have heard lots of scientists tell me “I wish Excel would… [fill in the blank]“, when in fact Excel already has that capability. So here’s a few more Excel tips to make your data organization a little bit easier.
- Track Changes. This feature in Microsoft Word is familiar to all academics- it’s the best way to give and get feedback from co-authors on manuscripts and grant proposals. Did you know you can track changes in Excel, too? On a Mac, go to the Tools menu, and select “Track Changes…”. You can then select “Highlight Changes”. This opens a new window where you can check “Track changes while editing”.
Note that this requires that you share your notebook. There are a few other options available in this menu as well. After you’ve made your changes and saved them, you can then select the “List changes on a new sheet” button. This produces a new tab with a history of the changes made. In this case, I changed my blank cells into “NA”: 
which results in a history page like this: 
Pretty neat! Especially if you have a lot of collaborators working on your project. If you are a Windows user, the process looks a bit different but is the same. Select the “Review” tab, and there’s a section of the ribbon called “Changes” where you can select “Track Changes”, enable sharing, and protect the workbook. For more from the Microsoft folks, check out their help file. - Prevent changes. Excel calls this “protecting”. You can protect particular sheets in a workbook, or the entire workbook. Do this by again going to the “Tools” menu and selecting “Protect…” and choose either sheet or workbook. You can even add a password. Why would you do this? Perhaps you want to prevent changes from being made to the “raw data” tab. Protect it! Do you have an undergrad lab technician entering values into your spreadsheet? Protect sheets you don’t want them to change.
If you are a Windows user. you have many more options for specifying protections, such as protecting formatting, selecting certain cells for protection, and preventing insertions and deletions. 
For more information, here’s Excel’s help file on the topic. - Create a “Chart” tab. Excel allows you to create a tab specifically for placing your graphs. This keeps you from muddying your lovely tabular data with inserted figures. To use this feature, first select the data you would like to use to create your figure. Then go to the “Insert” menu and select “Sheet” and “Chart Sheet”.
This creates a new tab with Excel’s default graph for the data you selected; you can make changes to the type of graph and its format in the same way as if you had inserted it into the spreadsheet. I haven’t figured out how to do this on a PC, but I know it’s possible- I’ve seen it! A little help, Windows users?
