Excel Features

Dynamic Charts

No comments

Formulas discussed: Charts, Offset, Ranges
The workbook used throughout this post is available here.

Charts can be a wonderful way of presenting data to an audience – just look at the popularity of infographics. Sometimes, particularly with line graphs, you might want to change the start or end point of the graph, either so you can zoom in on a subset of the data, or to remove some dodgy-looking bits. An example of this would be where you have a model that includes an upfront construction period, with certain things not being calculated until construction completion, and the completion time might not be fixed. In this circumstance, you don’t really want to display all those blank or zero cells (see workbook example 1).
screenshot - chart 1

Brendan WalpoleDynamic Charts
read more

Dealing with Circular References


Formulas discussed: Algebra, VBA (Copy, Paste, Select, Range, If..Then..End If, For..Next
The workbook used throughout this post is available here.

The problem

When building a model, it is not unusual to, either accidentally or deliberately, build a circular reference. A circular reference is where the result of a formula depends on itself. This could be as simple as B5 = B4+B5, or a complicated loop where cell A references B, which references C, which references D, which references B and E, while E references A, as in the diagram below:

Brendan WalpoleDealing with Circular References
read more

Excel’s best feature hidden in plain sight – Using Styles

No comments

Styles are a reasonably new feature, being significantly upgraded in Excel 2007 for Windows (earlier verisons had much reduced functionality), but not making it to the Mac version until Excel 2011, inexplicably being left out of Excel 2008 for Mac, along with all macro/VBA functionality. I love styles so much that 10 years ago wrote a .xla file that I used to keep formatting consistent throughout a model (although far inferior to what MS ultimately incorporated, as it was very slow, having to look at each cell individually). And when I found out that they’d been included in Excel 2011, I went and upgraded the whole Office suite on the spot (which if the company you work for is a participant in Microsoft’s Home Use Program, is well worth it).

What are Styles

Styles are a set of preset, editable formats that you can apply throughout your workbook. Excel contains a good number of built-in styles straight out of the box, such as Normal, Output, Heading1, Total, Input etc. These are all capable of being customised to your liking, or you can create new ones.

Brendan WalpoleExcel’s best feature hidden in plain sight – Using Styles
read more