OneNote and Excel

Microsoft OneNote and Excel Tables

Yes, they know each other, but they are not really friends. 

While using OneNote to track my blog information I ran into a simple problem. The table that I was using grew just a bit too large. I wanted to track simple things: page name, permalink, whether the format was updated (titles and font sizes were messed up on older posts), and the topic of the post. Just a few columns multiplied by a couple hundred lines... 

Of course, Excel would handle the job with ease, so I went to the Insert tab in the ribbon, clicked on spreadsheet, then New Excel Spreadsheet and BOOM! a very nice link and table is inserted into my OneNote page. 

The only issue - it doesn't work very well.

Excel in MS Word

If you insert a spreadsheet into Word 2013 you get a very nice Excel table - with a great deal of Excel functionality. On the Insert menu click Insert Object, Create New, then select Microsoft Excel Worksheet.  Once you have finished your data entry, click outside of the Excel object and the table drops into your document. Save, re-open, click to edit... it all appears to work. 

Excel in OneNote (Click for larger view)

The OneNote Experience 

OneNote has a mouse-over in the top left cover of the table to allow you to edit the spreadsheet. Click the edit button, Excel opens. Add data to the table, save and close, and the table refreshes in OneNote - simple and easy. Did you notice that the table is now an image and is not editable? Did you notice that the hyperlinks are not functional?  

Click the edit button again to make updates, Excel opens... make your updates save and close the file. Then something else happens. 

Filename: Link to OneNoteSample8.xlxs

Rather than opening up the same file, or opening an embedded version of the file like MS Word, it opens Excel, and inserts a copy of the table. You can edit Excel as needed, then save and close and OneNote will update the table. But, now you have two Excel files on your PC. The first file will not reflect updates made in the second file, nor will the second reflect changes made in the third. 

One Way Street

Open the Excel spreadsheet first, make edits, then save and close. Go back to OneNote and... nothing. The changes do not carry back from Excel to OneNote. Let's say you we keeping scores for a bowling league and had an Excel spreadsheet posted in a shared location. Users could make edits, but you could never really pull that information into OneNote. Of course, you could Open Existing Spreadsheet, but now you have multiple OneNote pages with similar information. This really is not an issue in MS Word, because the table is embedded in the document - so you can only edit in one place. 

Filename: OneNoteSample9 (ugh). 

The Solution 

Usually I try to offer a solution, or a simple work-around. This problem doesn't seem to have a good method. 

Excel provides method to query a source file and refresh the data as needed - you can keep a working copy on your desk and the source can remain in the cloud at a shared location. It does take a couple of clicks to update, but the process makes sense. OneNote seems to get about 60% of the way to that solution. 

The Recommendation

An integrated toolset would be great, but leveraging Excel from inside of OneNote really doesn't work. Build your complex tables in Excel and enjoy all of the functionality that it brings. Then you will need to resort to the old school method of Linking to the file (Select some text, on the Insert tab click Link, then navigate to the file in Explorer and click OK). 

If the source file moves, the link breaks, but at least there is only one Excel file, only one source of truth, and the hyperlinks work!

~Xolo