Your Questions Answered #10 – Tips and Tricks Live extended

• ‘Excel Tips and Tricks Live – Your Questions Answered (January 2024)’ webinar recording now available.

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.

We have covered how to use the ‘Show Changes’ functionality in Excel in more detail in Tip #475.

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.

In the desktop version of Excel, you can access version history from the ‘Info View’. If you click on ‘Version History’ here, you can view and restore earlier versions of your spreadsheet. More on the ‘Info View’ has been covered in Tip #469.

It’s important to note that if you sign in with a personal Microsoft account, you can retrieve the last 25 versions. If you sign in with a work or school account, the number of versions you can save will depend on your library configuration. Furthermore, if you're using OneDrive, your administrator may have turned off document versioning so it’s important to check in with your IT team.

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.

This will open up SharePoint in your browser. Click on the ‘Sync’ button to make sure the files are up to date.

Once the sync process is finished, go to your File Explorer, you will find a new folder named after your organisation in the left navigation panel. The Excel workbook from Teams should be stored there. You can now link this in your other Excel file stored in your OneDrive.

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.

Select ‘Load’ and then you will notice that Excel will create queries and connections to this source of data.

Once the queries and connections have been created, a pane will open up on the right-hand side of the document. Hover over the page or table of data, click on the ellipsis, and select ‘Load to’.

An import data menu will appear here. Click on the ‘Table’ option and input the cell you want the data to appear in and click ‘OK’.

Repeat the last two steps for other pages and make sure you select a cell reference in the same tab. This will import multiple pages of data into tables into the same sheet in your Excel workbook.

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.