Resize embedded spreadsheet

Suivre ce lien pour la version en [Français]

 

OneNote desktop √

 

For simple table without Math, the Insert/Table feature available in any OneNote version (desktop, App, or 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

 

picture-1b
If it’s a « New spreadsheet », the note’ header will be used as the spreadsheet’ title, followed by « – Spreadsheet ».

 

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

picture-2b
Right Click and Rename

 

in order to get a custom spreadsheet’ title :

Picture 3b.png
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)
custom display.gif
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)

 

outcome-excel-table-without-filter
No 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 !

 

 

Big excel spreadsheet :

If you insert an existing spreadsheet containing various tabs, you may select the one you want to show  :

 

select-what-to-display2
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 :
Picture 4.png
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 :
Picture 5.png
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 :

Picture 6.png
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 :

Error message.png

Seems that OneNote needs time to acknowledge simple changes, and won’t let you close the notebook until he is done :

error-message-while-closing

On one occasion, I found a new page on my notebook, with the Excel file recovered

 

Fichier récupéré.png
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 » :

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…

 

Link : Tip found on Social.Technet.Microsoft

Further reading :

Microsoft Excel and OneNote

Why you shouldn’t edit simultaneously Excel sheet through OneNote : Synchronization conflicts [in German], but Edge translator’ extension is your friend.

 

Updated 19/02/2017, with Live embedded spreadsheet feature.

Publicités

Un commentaire

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s