Protected View on Reports Exported to MS Excel

Are you struggling with editing documents exported from Sage Evolution to MS Excel because they are in ‘protected mode’? By default, documents that fail validation in MS Office 2010 will open in ‘protected mode’. This means one cannot edit and work on these documents while they’re in a Protected View display.

This can be extremely frustrating when exporting reports or grids from Sage Evolution to MS Excel documents.

Sage Evolution, Protected View, MS Excel

So, how does one get rid of the Protected View on reports or grids exported from Sage Evolution?

Procedure to remove Protected View on Reports Exported to MS Excel:

1. Close Sage Evolution on the local PC

2. Open MS Excel

3. Click File | Options | Trust Center

4. Click Trust Center Settings | File Block Settings

The option ‘Open selected file types in Protected View’ will be selected by default

How to Edit or Remove Protected View on Sage Evolution Reports Exported to MS Excel Post 2

5. Select the ‘Open selected file types in protected view and allow editing’ option

How to Edit or Remove Protected View on Sage Evolution Reports Exported to MS Excel Post 3

6. Remain on the File Block Settings and uncheck all options as displayed in the below screenshot

How to Edit or Remove Protected View on Sage Evolution Reports Exported to MS Excel Post4

7. Click OK

8. Close and reopen MS Excel

I hope the above helps users who might be a bit annoyed by the Protected View!

Should you have any queries please comment below.

Violet Van Niekerk

How To Recover Unsaved Excel Files

If you’re an ardent Excel user, you will understand the panic induced when you’ve inadvertently forgotten to save the changes to your workbook or, as is highly possible, there’s been a power failure and you’ve not saved your Excel spreadsheet. There is hope, simply set up the AutoRecover option in Excel and the next time this happens, you’ll have a back up. Learn how to recover unsaved excel files below.

How to set up the AutoRecover option in Excel:

  1. Select File > Options > Save
  2. Tick ‘Save AutoRecover information every … ’ and set it to an acceptable time limit
  3. As an added precautionary measure, also tick ‘Keep the last autosaved version if I close without saving’

Image 1, Recover Unsaved Excel Files, Excel, Excel Icon, Microsoft Excel

The AutoRecover feature only works on Excel workbooks that have been saved at least one time.

How to recover a file that was never saved or saved at least once:

  1. On the File tab, click Info
  2. Click Manage Versions > Recover Unsaved Workbooks
    Image 2, Recover Unsaved Excel Files, Excel, Excel Icon, Microsoft Excel
  3. Select the file from the Open dialog box

Image 3, Recover Unsaved Excel Files, Excel, Excel Icon, Microsoft Excel

This is an alternative method to the same file path depicted in the image directly above:

Recover Unsaved Excel Files, Excel, Excel Icon, Microsoft Excel

It has also been observed that this recovery option is not completely foolproof and for that reason, a great habit to develop is to periodically press Ctrl-S as you go.

Good luck and I hope you don’t ever need to use this function. 🙂

Credit to http://www.accountingweb.com, http://www.excel-easy.com and https://www.ablebits.com for the helpful information this blog is based on.

Speed Up Your Excel Workbook

Are your Excel Spreadsheets so big and slow that you could literally go and make a cup of coffee while the workbook updates?  Good news, there is a simple and quick solution to Speed Up Your Excel.

I recently had a request from a client to assist them with improving the speed of their Excel Workbooks.  While working on their data it was taking so long for their workbooks to update that it was seriously affecting their ability to deliver their financial reports.  Running the initial report took nearly 6 hours (they would run it overnight).  Each time new data was entered, it would take a couple of minutes for Excel to recalculate, equaling hours per workbook!  It doesn’t take an expert to see that this is not good for business.

It didn’t take me long to find the problem, there were far too many worksheets in a formula heavy design.  The formula used were old look-ups (e.g: Index & Match combination resolves quicker and uses less system overheads with more flexibility than the traditional VLookup formula) and too many nested IF statement variables.  They weren’t using List referencing, Named Ranges and there were hardly any Pivot Tables (a great way to quickly summarise and analyse data, again, using less resources).

Newer Business Intelligence report writing methodology using newer software functionality greatly assists with the ever increasing volume of business data sources that need analysing and help to Speed Up Your Excel

That being said, you don’t have to rewrite all your reports just because they’re ‘old school’.  Sometimes we just have to make do with what we’ve got.  Luckily for you there is another easy way that one can use to speed up your Excel Workbook even if you really have to make use of a nested formula-heavy design.

Speed Up Your Excel by setting your Excel Workbook formula calculation to manual. 

What that means is that Excel won’t update your formula until you tell it to.  This leaves you free to open your Spreadsheet, edit your information, and, when you’re ready, calculate your formula.

By changing this setting I brought the report run time at my client down from over 6 hours to just over 15 minutes.  They are now able to run the report, make edits, and, update their information multiple times in a day, something they previously couldn’t do.

Here’s how to set your Excel Workbook Formula Calculations to Manual:

Step 1:
Go to the Formulas Tab in your Excel Workbook Ribbon, then select ‘Calculations Options’

Speed Up your Excel Workbook 1

Step 2:
Set to ‘Manual’

Speed Up your Excel Workbook 2

NB: Don’t forget, now that Excel calculates your formula manually, every time you’re finished updating your spreadsheet or want to update your workbook, you will need to come back here and tell Excel to ‘Calculate Now’

Speed Up your Excel Workbook 3

Hopefully this tip helps to Speed Up Your Excel and make your life using Excel a bit easier 🙂

Brian