Resize embedded spreadsheet

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

If you choose “New spreadsheet”, your note’ header will be used as the spreadsheet’ title, followed by “- Spreadsheet”.

 

Right-Click on the Excel’ icon, and you may modify the title :

Right Click and Rename

 

in order to get your custom spreadsheet’ title :

Type your own 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 above sequence explained in video

 

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)

 

No more empty lines nor columns !

 

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  :

Right Click on the Excel icon, choose “Select What to Display” and select either Sheet1 or Sheet2 (or both !)

 

Alternative :

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 :
After having selected all blank columns, right-click on any column header, and select “Hide” in the dropdown menu

 

  • Select all blank rows : click on the first blank row, press Shift + End + Down arrow
  • Hide the selected rows :
From now on, only the useful area will be exhibited

 

Troubleshooting :

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 :

But the table is not editable anymore !

 

Reliability :

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

 

Recovered file, stored on a new note.

 

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)

Microsoft Excel and OneNote

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.

2 comments

    • Yes, it happened to me quite a few times also, when I tested this features intensively to write about it.
      Have you tried on another notebook or section, to check if it’s related to a specific notebook/section (and its sync status) ?

Leave a Reply

Your email address will not be published. Required fields are marked *