Of all the new features in Excel 2013, the biggest time saver for me is Flash Fill. It’s like Text to Columns on steroids:

The example shows a list of Surname/Name pairs, separated by comma.

To change them to Name/Surname (without a comma), just type an example of the output you want in the top adjacent cell (B1 here). Select the whole adjacent range as above, click the Home Tab, Editing Group (far right of the ribbon), and Fill.

The last entry in this drop-down is Flash Fill. Click that, and this is the result:

It doesn’t get any easier than that!

Bio: This article was written by Jasper Cook, Excel guru and trombonist of note. Call or email us if KRS and Jasper can assist you with your Excel: enquiries@krs.co.za

Most Excel users are aware that turning off calculation allows much faster saving of large workbooks (save often, avoid tears). Excel, by default, calculates before saving, but that becomes very slow with large workbooks. We can save time by using F9 to calculate manually once edits are complete. F9 can be used to debug or evaluate a formula.

In the example below, the user has typed =TRANSPOSE(A2:A13)&”,” and pressed F9 to evaluate the result, a comma delimited string:

We would usually press Esc immediately after, otherwise Excel will insert the evaluation into the formula as a literal, which we don’t usually want.

But wait, what if we actually want a string containing all those codes? Don’t press Esc! Just delete both curly braces, and insert “CONCATENATE(“ between the equal sign & the first double quotes, and add a closing parenthesis at formula end. Before pressing enter, delete...

What is Zurb Foundation?

ZURB Foundation is a free collection of tools for creating websites and web applications. It contains HTML and CSS-based design templates for typography, forms, buttons, navigation and other interface components, as well as optional JavaScript extensions. Zurb is also one of the leading frameworks for creating responsive web designs. Zurb facilitates responsive web design through the use of a fluid grid system, which adapts to the current screen resolution. This is all done through css classes defined in zurb. Another benefit of using Zurb foundation is that they have invested considerable time and effort in standardizing your website look across all browsers. You need very little know-how about the different browsers to accomplish this, it just works out of the box! For more information on Zurb Foundation go to http://foundation.zurb.com/index.html

The Grid -...

From time to time, I am asked to do things that make no sense to me (but I know we are all different). One such request was to concatenate the contents of three columns in Excel into a single column, but with fixed width for the results, as below (copied from a single column in Excel):

I don’t know why three columns, looking almost identical, were not good enough:

I suppose it was a question of style. However, it was a case of “You heard the (wo)man”. To right-pad one cell, use =$A1&REPT(“ “,PadColWidht-LEN($A1)) – PadColWidth is a Named Constant with a value of 20, in Name Manager:

So, this is the formula in full (for all three columns):

=$A1&REPT(” “,PadColWidth-LEN($A1))&$B1&REPT(” “,PadColWidth-LEN($B1))&TEXT($C1,”yyyy-mmm-dd”)

At the very least, it is a reasonable example of how to pad strings. A typical use case for this is for making last minute changes to a...

A user recently asked:

“I have scheduled fixed days throughout the year for a certain task. Some weeks, it isn’t needed at all, and some weeks contain more than one day for this task. It’s also on different days of the week. Instead of scanning a long list of dates each time, how can I devote only a couple of cells in a worksheet to show the next available free appointment?”

Well, here goes – here are the demo output cells in Excel:

Below is (part of) the named range “Appts2013” of available dates in worksheet appointments:

Formula in “Today is” cell is: =TODAY()

The formula in yellow cell with 04-Jul in is: =INDEX(Appts2013,MATCH(TRUE,Appts2013>$B$1,0))

it’s an array formula, so it won’t work unless it is entered properly – once the formula is typed in, with the cell selected,

• Press F2.
• Press the following simultaneously: SHIFT-CTRL-Enter
• That will bring the array formula to...

Red > Green > Refactor– the test-driven development “mantra” used by developers that focuses on writing only the code necessary to pass tests. The concept of this development style is to 1) reduce errors caused by incorrectly guessing that an implementation will behave as expected, and 2) reduce bugs caused by adding new functionality to existing code. In delivering better designed and cleaner and more extensible code I have decided to share my thoughts and findings on some of the challenges and benefits afforded by this development style.

What is TDD?

TDD is a software development process that relies on a short iterative cycle:

1. Red – write a failing test that implements a new feature.
2. Green – write just enough production code to make the test pass, fake it if necessary but remember you will need to refactor this later.
3. Refactor the production code to improve the design until it has...

Compiler writers are famously stuck for names for things. For this condition, Sun Systems compiler authors would have used: “_Ceaselessly.Challenged.With.Respect.To.Syntax.Appellations()”

English Royalty in old times would give a Commission of Array to leaders to muster and array citizens and to get them into condition for war. Maybe that inspired The First Compiler Writer to use Array. From military practice, s/he could just as easily have drawn Platoon, Regiment, Formation, Rank, or made a word up, contracting “Serried Ranks” into a Serry, perhaps, or gone civilian, with Group or Echelon.

I have always felt that array is about clothes, and would have chosen Phalanx, as in: Greek soldiers lined up in close formation, shield to shield.

Anyway, ARRAY stuck, and the creators of Excel stuck with it. The following, for those who hanker after high school maths rather than the “usual”...

The role of a Business Analyst in software development is a topic often ignored in Agile projects. While I fully support iterative development and embracing change in regard to creating specifications, I acknowledge that the need for business analysis skills is as great as it’s ever been.

I thought I’d share some of my thoughts on the changing role of the business analyst in an Agile environment. Let’s begin with some brief points around what Agile is…

Agile is….

The Agile Manifesto includes the following:

“Working software over comprehensive documentation.”
Agile allows us to question what value the various documentation needs bring to a project. Quite often, getting lost in administrative tasks can take our focus off what should really be our primary driver, which is delivering working software.

Agile is more focused on working software and the business value that it can...

Behaviour Driven Development (BDD) or Specification by Example is the process of writing acceptance criteria as tests, using the defined format of Scenarios.

Some teams take this further using a tool such as Cucumber to automate the tests, but the primary benefit of writing scenarios is to use specific examples when discussing the functionality and constraints of a feature before implementing it. This allows us to bring the specification into the tests we write, eliminating the ‘defect-injection-process’ of writing specs first then deducing tests and code from them separately. It also brings us closer to creating truly living documentation.

Recently our team’s focus has been on creating better acceptance criteria for stories. One of the problems faced is that it looks deceptively easy to write a scenario in the format; “Given (a context) When (an event occurs) Then (we should have a...