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

2015 Payroll Medical Aid Increase

One of the areas of Tax and Sage Pastel Payroll administration which Sage Pastel Payroll clients tend to neglect is that of Medical Aid Contribution inconsistencies.

As many of us already know, Medical Aid Contributions for most open Medical Aid Schemes increase in January of each year.

As this affects the income of the employees and expenses for the employer, there can be dire effects if this is not updated on the Sage Pastel Payroll administration system.

Some of the negative effects of not updating your Sage Pastel Payroll system include:

  • Incorrect PAYE, UIF and SDL
    • EMP201 Correctives
    • Penalties and Interest
    • SARS Payment Allocation issues (You know what we’re talking about)
  • Non Balancing Sage Pastel Payroll Journal to Expenses.
  • Reporting Reconciliation issues
  • Under taxation of employees
  • Incorrect IRP5’s
  • Corrective Retrospective Transactions and entries for Year to Date contribution adjustments.

For those who like trend analysis and comparisons, here is the average medical aid contribution increases for 2015:

Medical Aid SchemeIncrease Percentage
Genesis Medical Scheme6.4%
Momentum Health7.2%
Spectramed Medical Scheme7.53%
Compcare Wellness Medical Scheme7.7%
Bestmed Medical Scheme8.7%
Discovery Health Medical Scheme8.9%
Fedhealth Medical Scheme8.9%
Sizwe Medical Fund8.9%
Keyhealth8.9%
Hosmed Medical Aid Scheme 9.1%
Medshield Medical Scheme9.4%
Liberty Medical Scheme9.5%
Medihelp10.1%
Bonitas Medical Fund10.6%

 

Let this be a friendly yet urgent reminder to update your relevant medical aid contributions in your Sage Pastel Partner Payroll account.

Remember, if you need assistance, please contact Francois for immediate support.

francois@e2e.co.za