ICAEW Excel tips: Pushing your limits

Hello all – time for your regular update on all things spreadsheet-related from ICAEW.

Last month, I wrote about the value of teaching, and how finding some time to help others learn more with Excel can pay dividends to your own ability. This month, I wanted to talk a bit about stretching yourself, and how trying to solve hard problems can help you become better at all things Excel.

Testing the limits of your ability

This is spurred out of a webinar which I ran this month, called “Tip of the Week Live – Advanced Formulas”.  If you’re an Excel Community member, you can watch the recording of that webinar here.

As the title implies, this webinar was a stretcher – not just for the attendees, but for me as well.  Focusing mainly on array formulas, the webinar had a lot of technical content to fit into the hour. 

Array formulas were for a long time something that I used and understood well enough to tinker with, but explaining them clearly in a limited time slot was beyond me. But the demand was there for a webinar dealing with the topic, and so I made myself stretch – preparing and practicing the content beforehand, and speaking more confidently on the day. This led to a successful presentation – but perhaps more importantly for me, a better understanding of the topic.

Getting better at Excel means occasionally to try something difficult. That might include tracking down something difficult to try and solve independently of it arising naturally – seeking out a challenge. Testing the limits of your ability and trying new things is a real catalyst for improvement.

As to where to find challenges – that depends on your tastes. Personally, I enjoy mathematics, so I like to try and make Excel files to play around with things like cellular automata, or the Collatz function, or prime numbers, or I go on to help forums and look for interesting problems. 

Tip of the Week highlight – More filling options

This month’s tip is all about how to make the most of the options provided in Excel for filling in data. It’s based mostly on Excel Tip of the Week #181.

You may well already be familiar with the Fill Handle – the ability to drag down the bottom right of a cell to replicate its content, or fill in a series:

David Lyford Smith

But did you know that there are more things that are possible? The Fill menu from the Home ribbon lets you do much more:

As well as using the options to fill in cells, you can use ‘Fill Series’ to fill cells with a certain pattern:

This would let you easily fill, say, 10,000 rows with increasing numbers without any formulas or copy/pasting at all.

Finally, there’s the very sexy ‘Fill Justify’ option, perfect for making your text fit the desired column width:

Principle of the Month

This month’s selection from the Twenty Principles for Good Spreadsheet Practice (www.icaew.com/excel20principles) is #8:

Design for longevity.

Short to describe, but it’s potentially difficult to achieve.  When designing a spreadsheet solution, try to consider the long-term reusability of that solution. Having a spreadsheet that is useless after one month is a big potential waste of time.

There are a few techniques you might employ to achieve this goal. For example, try to have ranges which are flexible to adding new inputs – so don’t lock down your data at being, say, 12 months; leave room for a 13th month to be added if it becomes necessary. Flexible ranges might be achieved with an OFFSET formula, or an Excel Table, or named ranges, or any number of other specific Excel tools – but avoiding anything that would make it excessively difficult to flex later on.

This also applies to assumptions and other constants – having these be entered in a way that adds flexibility will increase the lifespan of the spreadsheet. For example, having all cells that use an assumption value linked to a single input cell will allow you to quickly update that assumption; having the assumption value either in multiple locations or (worse still) hard-coded into formulas will significantly constrict the lifespan of the spreadsheet.                                                

See you next month,

~David

David Lyford-Smith is the IT faculty's technical manager and blogs for the ICAEW Excel Community.  The Excel Community is an Excel content hub that encompasses webinars, blogs, member Q&A, fortnightly bulletin updates, and more. Find out more at this link.