 |
01670 732139 |
 |
steve@itseasy.co.uk |
New in Excel
Dynamic Arrays
The introduction of dynamic array functionality seems to have caught a few people out, me included. Subscribers of Office 365 may have noticed the @ sign suddenly appearing within formulae. The old versions of Excel files were automatically updated to accept dynamic arrays. This then caused problems, because if you tried to edit your formulae by removing the @ sign the Spill Error occurred.
Spill Error
The spill error occurs when there is no room for the array to display the results of a formula. If you remove the @ sign from your formula, which tells Excel not to use the array feature, the array functionality kicks in and you will see a spill error.
The example above has the Spill error because the array function I am using cannot fill down as the word test is in the way. Remove the word Test and the formula fills down and works as it should.
Spill Problem
Understanding spill is one thing but trying to sort out your own data with thousands of functions can become a bit tricky. I found it best to delete formulae where possible and retype them to let the array feature work and where that is not possible, make sure the @ sign remains and everyone that is using the file understands what it is there for. I have come across people that still use the @ sign instead of Sum, this is because they were exposed to Lotus 123 before Excel. Having formulae with two @ signs would be confusing, thankfully, if you type the @ sign now, Excel converts it to the equals sign.
Spill Example
Resurces
Excel courses are at https://www.itseasy.co.uk/courses/excel-training/
Excel for New Managers can be found here. This book covers all the levels of Excel from beginners to advanced.
Testimonials
“Steve’s background in IT training clearly provided him with great organisational and managerial skills which he put to good use at Second Chance Enterprises. His ability to see the long term strategy as well as manage the day to day management is one of his biggest strengths.”
Chriss Graham
Project Coordinator at Daisy Group
“I studied with Steve when we completed our CiPD CTP together. During the course I found Steve to be very personable and also very professional. Steve was first on my list of suppliers. Steve was very competitively priced and very accommodating.”
Simon Snowdon
European Training Manager at HP Enterprise Services
“Steve delivered a number of Microsoft Office Training Sessions to a range of employees across Newcastle College. The service provided by Steve is second to none. I have no hesitations in recommending him to other employers.”
Christine Jackman
Learning Manager(North) - Winner of Training Programme of the Year NECCA
Craig Forster
Estimator, John F Hunt Regeneration Ltd
