ICAEW.com works better with JavaScript enabled.
Exclusive

Your Questions Answered #10 – Tips and Tricks Live extended

Author: Bani Lamba

Published: 25 Mar 2024

Exclusive content
Access to our exclusive resources is for specific groups of students, subscribers and members.

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.

Excel screenshot
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.
Excel screenshot
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.
Excel screenshot
Excel screenshot
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.
Excel screenshot
This will open up SharePoint in your browser. Click on the ‘Sync’ button to make sure the files are up to date.
Excel screenshot
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.
Excel screenshot

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.
Excel screenshot
Excel screenshot
Select ‘Load’ and then you will notice that Excel will create queries and connections to this source of data.
Excel screenshot
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’.
Excel screenshot
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’.
Excel screenshot
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.
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.

Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250