Version : √ OneNote desktop
For simple table without formulae, the Insert/Table feature available in OneNote (all versions : desktop, App and Online) is very convenient.
But, for more complex calculation, or table formatting, having the full power of Excel at hand directly within OneNote is useful.
To embed an excel spreadsheet in OneNote is straightforward : Insert/Excel spreadsheet
Right-Click on the Excel’ icon, and you may modify the title :
in order to get your custom spreadsheet’ title :
The outcome is better, but the spreadsheet still shows many blank columns & rows…
Also, the gridlines cannot be removed. Even hiding them in Excel is useless : they are always displayed in OneNote…
I struggled to find a solution since neither :
- Selecting the Print Area (Page Layout/Print area) within Excel,
- Resizing the table’ container (within OneNote),
allowed for cropping the intended area !
After a long search on the internet, I finally found a way to hide the unnecessary lines & columns :
- in excel, select the portion of the table you want to show
- Insert/Table will format this portion “as a table”
- Then, come back on OneNote, and close the spreadsheet
- Right click anywhere to get the “Custom Insert” window
- Select the table (instead of the whole sheet)
The outcome finally match my expectation !
(You may remove the filter on the first line, which are added automatically when you convert the highlighted area into a “Table”, to get a cleaner result)
Until now, I was reluctant to use excel in combination with OneNote, because I didn’t know it was possible to resize the spreadsheet. Now that I have discovered a way, it opens new perspectives !
Large excel spreadsheet :
If you insert an existing spreadsheet containing various tabs, you may select the one you want to show :
If you have trouble with the Table selection (I did, sometimes), there is a turnaround : it consist in hiding the blank columns and rows :
- Select all blank columns : click on the first blank column, press Shift + End + Right arrow
- Hide the selected columns :
- Select all blank rows : click on the first blank row, press Shift + End + Down arrow
- Hide the selected rows :
Beware : It may be possible to remove the Excel icon (and the associated filename), clicking on the Excel icon and pressing Del.
For a few seconds, the “Edit” button remains, which let you think that the table is still editable :
It seems that embedded spreadsheet is a tricky feature, especially with synchronised (and possibly shared) notebook.
Sometimes, I get the following messages :
Seems that OneNote needs time to acknowledge simple changes, and won’t let you close the notebook until he is done :
On one occasion, I found a new page on my notebook, with the Excel file recovered
Live embedded spreadsheet :
The spreadsheet you have inserted in OneNote is not linked to the original file.
Whatever option you choose “Attach File” (= insert an excel file’ icon) or “Insert Spreadsheet” (= insert a view of the spreadsheet), it consist in a copy of your original file : any change you may perform in your original file may not reflect in its copy in OneNote.
Even if you click on “Refresh”, it will not update the spreadsheet embedded in you note.
You may nevertheless open the original file by clicking on “Open original” :
A solution, proposed by Marjolein Hoekstra (see her tweet), is to embed a Sway (which embed your excel file previously saved on OneDrive) : works fine although you won’t be able to restrict the area to be shown…
Further reading :
Social.Technet.Microsoft (where I found this tip)
Synchronization conflicts : Why you shouldn’t edit simultaneously Excel sheet through OneNote. In German, but Edge translator’ extension is your friend !
Updated 19/02/2017, with mention to Live embedded spreadsheet feature.