In this year’s first 'Excel Tips and Tricks live – your questions answered' webinar, John Yeldham and Ian Pay tackled Excel questions and queries covering best practice, the use of functions and functionality, and a range of techniques for data handling and analysis in Excel. Bani returns to answer some of the unanswered questions from the webinar.
The question
I typically turn off the "auto save" to avoid saving an incorrect version. Is that necessary or is there auto version control?
Answer
Instead of turning-off the AutoSave functionality in Excel to avoid saving unintended changes, you can use the ‘Show Changes’ functionality in Excel to keep track of cell edits. This is also useful when a spreadsheet has been shared with others and when keeping track of multiple versions and changes can be hard.
To enable this functionality, you need to save your document in either SharePoint or OneDrive. In the Review tab click on ‘Show Changes’ to see a pane open up on the right-hand side with a list of all the edits made to cells in the spreadsheet.
In Excel Online, you can access version history from the Review tab. Click on ‘Version History’ to access earlier versions of the Excel file and scroll through changes that have been made to each earlier version. From here you can then save or restore a copy.
Another useful tip to note is that Excel Online will allow you to switch to read-only mode via a toggle in the top right corner. This can also help to ensure unnecessary or accidental changes are avoided.
The question
My company has increased the use of Teams to store and share Excel files. Is there any way to link to an Excel file shared through Teams? I used to link the file via share drive, but now I need go to Teams, download the file to my folder, and then link to that source file.Answer
You can link files stored in Teams to your spreadsheets in OneDrive by using the sync functionality.To do this make sure you’re signed into OneDrive with the same work account you use for Teams.
Open Teams and find the channel where you have stored the Excel workbook you want to link to. In the File tab click on the ‘Open in SharePoint’ option.
The question
When importing data from several pages of a PDF, Excel wants to create a tab for each page - how do I import several pdf pages to one Excel tab?Answer
There is a workaround for this which you can use to get data from a PDF into one Excel tab.In the Data tab click on ‘Get Data’ and then ‘From File’ and ‘From PDF’ to get started. Select the PDF and then all the pages you want to load into Excel.
The question
Could you please share examples where the "Automate" functions would be useful?Answer
Within the ‘Automate’ tab in Excel, you will find the Office Scripts functionality. Office Scripts is a feature that allows users to record and document actions in Excel so you can automate repetitive tasks in Excel. For example, you may run a monthly process where you download data from another system and then use Excel to format and clean the data. This may involve removing empty rows and formatting the data into a table. You can use Office Scripts to record the steps in this process. The next time you download the data, you can then run the script to perform the same tasks.Office Scripts is sometimes compared to VBA, but it may be suggested that it offers more compatibility with a wider range of applications in the Microsoft suite. As mentioned in this article exploring the difference between VBA and Office Scripts, Office Scripts and VBA are significantly different technologies and work in different environments with different scopes.
Office scripts can also be used with Power Automate which can be used to create workflows to automate tasks in Excel and other Microsoft’s suite of applications. You can read more about Power Automate in our introduction on this topic.
While this feature was initially available in Excel Online, it can now be found in the ‘Automate’ tab in the desktop version.
It’s important to note that Office Scripts is only available in enterprise and educational licenses and in some cases an administrator may need to enable this feature before you can use this.
We have covered more on how to use office scripts in Tip #470.
- Your Questions Answered #11 – Tips and Tricks Live extended
- Your Questions Answered #10 – Tips and Tricks Live extended
- Your Questions Answered #9 – What is the purpose of # when using dynamic arrays?
- Your Questions Answered #8 – Does Power BI leave the underlying data source unchanged?
- Your Questions Answered #7 - How do I combine tables from multiple files into a single table?
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.