Author Archives: Chris Mortimer

Spreadsheet Skunkworks! Excel VBA Chess Challenge

Can we create a chess computer in an Excel spreadsheet using Excel VBA?

Welcome to the Spreadsheet Skunkworks! In this video series, Chris tests the boundaries of what is possible in Excel. You will not find the usual step-by-step tuition here; the focus is on experimentation and creativity. Expect plenty of coding challenges, and some nice surprises about what is possible in Excel…

Skunk Snap

We will seek to use Excel’s functions and Excel VBA to create the functions of a chess computer. We do not know to what extent this is possible but, ultimately, we would like to challenge somebody to a game on using our spreadsheet-based system. Along the way, we aim to learn lots about Excel VBA and its capabilities.

Some key questions:
– How can we represent a chess board and chess pieces in Excel?
– Can we make Excel understand where the pieces can move?
– Can we ‘teach’ Excel some simple chess principles (capture the other pieces, move your pieces to good squares), and get Excel to recommend a move to make?

What do you think about the Spreadsheet Skunkworks series? Do you have any ideas for the Chris? Don’t forget to leave a comment on YouTube.

New videos in the series will be added to this post.

Click here to go directly to the Spreadsheet Skunkworks playlist on the YouTube channel.

Video 1 – Use Excel VBA to create a chessboard effect in an Excel spreadsheet

Click here to download the completed file used in the video.

In the first video, the challenge is to create a visual ‘chessboard’ effect in Excel. Chris first looks at how to do this ‘manually’ and presents some time-saving tips. Then, we explore how to use Excel VBA to speed up what could be a time-consuming task. Chris first records some code to format a cell and employs a ‘loop within a loop’ to colour the 64 squares of the chessboard. Some VBA techniques on show include ‘do – until’ loops and conditional statements. Enjoy!

Video 2 – Use a ‘remote referencing’ approach to create a chessboard effect, and allow user control of square formats

Click here to download the completed file used in the video.

In the second video in the series, Chris improves code produced in the first. We explore how to implement a ‘remote referencing’ approach to creating the chess board which achieves efficiency improvements. We also build in a mechanism to allow the user to control the appearance of the squares, and look at some cool cell fill options. VBA techniques in the video include for / next loops, loop within a loop, integer and boolean variables, and copy / paste (special) coding. Finally, Chris introduces his new puppy, Kuda!

Video 3 – Use VBA to select a range of cells

Click here to download the completed file used in the video.

In the third video in the series, Chris deals with the topic of selecting a range of cells using VBA. This requires some intricate coding but, once the technique is mastered, its applications are powerful. Chris refers to the technique as the ‘range, range, range!’ technique because three components are required – Range(Range 1, Range 2); range 1 refers to the top left-corner of the range to be selected, and range 2 to the bottom-right – with all cells between the two ranges being selected. See how Chris applies this technique in the video to quickly change the formatting of the number / letter references for the chess board. Chris also discusses how to best represent the chess pieces in Excel – using cells, or shapes?

Video 4 – For Next Loop, Offset and Variables to create and position pieces

Click here to download the completed file used in the video.

We currently have a chess board, but no pieces! In the fourth video in the series, we explore how to create the ‘pieces’, and position them accurately. It would be possible to do this ‘manually’ using shapes and dragging them around the board; but, it would be better to automate this process with a ‘reset’ button, to set the pieces up for a new game.

What coding techniques are required to get this job done? Chess comprises 32 pieces in total, so we’ll use a loop, combined with a table in the spreadsheet, to repeat the ‘positioning’ process while changing important details for each piece. Some Tiger favourites are on show here, including the offset command and integer variables. We’ll also create a ‘backend’ sheet to hold information that the user does not need to see.

Video 5 – The Power of Loops

Click here to download the completed file used in the video.

You could say that the last video was heavy in terms of coding, and light in terms of what we achieved in a practical sense. But, this is typical of most coding tasks: intensive programming is required at the beginning to set up the various mechanisms but, once the foundation is in place, we can quickly ‘scale up’ the code to get jobs done quickly. This is why Chris describes coding tasks as ‘front-loaded’.

In the fifth video, we firm up the foundation and then ‘scale up’ the code by populating a table in the backend, and adjusting the loop control to iterate through the code multiple times. This allows us to generate a row of pawns, positioned precisely on the board. What facilitates this awesome click-of-a-button functionality? We are witnessing the power of loops…

Chris also talks about the important ‘subset’ of skills in Excel VBA that create disproportionate value for the programmer. There is so much to learn in VBA, and in Excel more generally. Chris argues in the video that a small number of techniques, combined together, is all that is needed to create awesome Excel applications. What are some of the main components of this ‘subset’? If you follow Tiger’s videos, you will know that loops, variables and offset feature prominently.

Video 6 – Organising Code and Harnessing a Loop

Click here to download the completed file used in the video.

The next video starts with the ‘dry’ topic of code organisation in the VBA editor. Code is usually organised into ‘modules’ in the VBA editor; well-organised consists of routines (‘macros’) that are grouped by theme, and located in named modules. Moreover, as the name of a routine should describe its function, the name of a module should give us a clue about what the routines inside it do. As more routines are created, it is important to periodically step and back and tidy things up. Yes, things might be understandable to you, but would a colleague or a customer be able to make sense of things? What about you in six months’ time?! A professional coder allocates time to keeping things neat-and-tidy, and understands that, in the long run, this approach is more time-efficient anyway.

Now, the exciting stuff! In this video, Chris creates the starting position for a chess game, which consists of 32 pieces positioned on the correct squares. This task sounds onerous but, by harnessing the loop created in the previous video, we can get it done ‘at the click of a button’. The key mechanism is the interaction between the visual basic editor and a table on the ‘Engine’ sheet in the Excel file. It is easy to type information into the table; looping through a set of instructions 32 times, the VBA editor takes this information and, via a set of variables, uses it to position objects on the spreadsheet. The video shows how, with the basic ‘for-next loop’ mechanism established, things can be ‘scaled up’ very quickly to get a mind-boggling amount of work done.

As a bonus, Chris shows how to use text formulae such as =RIGHT, =LEFT and =LEN to quickly manipulate text, and avoid excessive typing.

Now, we’re almost ready for a game of chess!

Video 7 – Let’s Get Organised!

Click here to download the completed file used in the video.

It’s been a while since the last installment of the series and it’s time to ‘tidy up’ the file. It is important to keep things ‘neat-and-tidy’ in the VBA Editor, and in the spreadsheet file generally. In the VBA Editor, this means periodically deleting unused code and (re-) organising code into named modules; in the spreadsheet, it means ensuring the correct elements are positioned on the correct sheets, among other things. Code is difficult enough without the distraction of a messy work environment; this ‘tidying up’ creates more mental bandwith for the task at hand.

In the video, Chris moves some cells to the supporting sheet, and tweaks / tests the code to get it working with the new setup. Then, we get into the VBA editor, clear out some old modules and rename the remaining ones, and add some helpful annotations. Finally, buttons are added to trigger routines such as the macro to set up the pieces, and to delete them (without deleting the buttons we had just created!) The result is a tidy VBA editor and clean user interface. Onwards!

What do you make of the spreadsheet skunkworks series? Any questions about Excel VBA? We would love to hear from you. Contact us here.

Posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Excel Userforms for Beginners

Use Excel VBA to Create an Excel Userform and Manage a Database!

Click here to download the STARTER example file used in the videos.
Click here to download the COMPLETED example file used in the videos.

Video 1 – Introduction

In the first ‘in-car’ video, Chris explains how Excel userforms can help with a typical Excel task – managing a database. This can be time-consuming and frustrating and, correctly implemented, Excel userforms can help! We briefly discuss the importance of two functions – adding new entries to a database, and editing existing ones, consider how an Excel userform might help, and highlight the pivotal role of VBA code. Why not download the completed Excel file to ‘get a feel’ for the topic? Then, download the starter file so you are ready to work along with Chris
Why are Excel userforms important and what are some applications?

Video 2 – Create a Userform, Add a ‘Control’

This video looks at creating a Excel userform in the VBA editor and adding a simple feature or ‘control’. We add a command button and assign some code to the button. In the future, the user will click on this button to transfer data from the userform to the database. We make the command button flash up a message box so that we can understand how the Excel userform, command button, VBA and message box are interacting. This is a simple example, but all userforms follow a similar basic process to get things done.

Video 3 – Build up a Userform, Add Labels, TextBoxes, ComboBoxes

In this video, we build up a userform to so that it can quickly collect data from the user. We look at three controls, and focus on two ways of gathering data – TextBoxes and ComboBoxes. Textboxes allow the user to make a ‘free text’ entry, whereas ComboBoxes require the user to choose from a menu. ComboBoxes must reference a list in the spreadsheet, and we look at how to get the VBA editor and spreadsheet to interact to achieve this. We also explore how to implement option buttons – another interactive and user-friendly Excel userform feature.

Video 4 – Maximise User-Friendliness

A userform that is not working well can be more of a hindrance than a help! In the fourth video in the series, we look at maximising user-friendliness to make the user experience as smooth as possible. We explore how to configure the TabIndex property in the VBA editor to allow the user to navigate the userform using the Tab key on the keyboard. We also look at how to make default values appear in the controls to speed things up for the user, in addition to some other important details that help create a ‘professional’ feel.

Video 5 – Link the userform to the spreadsheet using Excel VBA

This task involves multiple ‘objects’ – the workbook, the worksheet and the userform, among others. VBA is essential to make these objects interact effectively – it is the glue that binds the various elements together. In this video, we consider how to approach what can appear a complicated coding task. We identify a sensible and simple first step that transfers data from the userform to the spreadsheet, and implement it using VBA. Dealing with the option buttons, however, requires a more sophisticated coding concept, can you guess what?

Video 6 – Position data precisely in the database using Excel VBA

So far, we have used VBA to move data from the Excel userform to the database. However, we need a way to tell Excel exactly where to put the data in the database. If adding a new entry, we would want the data to appear at the bottom of the database, for example. In this video, we explore how to set up a mechanism to measure the size of the database and to convey this information to the VBA editor. The mechanism is ‘dynamic’ in the sense that it responds to changes in the size of the database. It’s a great example of a common theme in VBA – getting spreadsheet formulae and VBA commands working together to achieve powerful and precise control.

Video 7 – Control and Helpful User Interaction

This video is a break from the complex coding! We take a detour through two topics that are often ignored but which are critical if you want to take your spreadsheet to ‘professional’ level. We deal first with the topic of control. We can achieve good levels of control by using validation to check user inputs. In the video, we look at setting up a ‘custom’ validation control to check if a proposed name is already in the spreadsheet, or not. The second topic is user interaction. It is not always clear what has happened when a VBA routine has run and it is a good idea to let the user know. How can we make this happen in the Excel userform task?

Video 8 – Use an Excel Userform to Edit Existing Data

The ability to edit (rather than add a new) entry is an important function that presents a new challenge to the programmer. In this video, we consider the task and identify the main steps. We look at how to create a new, simple userform to allow the user to highlight the entry to be edited. We use an offset formula in the spreadsheet to define a ‘dynamic’ range that changes size according to the number of entries in the spreadsheet. Then, we create a mechanism to establish the row that the ‘target’ entry to be edited is on.

Video 9 – Load Data from a Database into an Excel Userform

In this video, we explore how to extract data from the database and to load it into an Excel userform. Note that our approach is NOT to create two separate userforms – one for data input, and one for editing, which would represent a duplication of effort; rather, we use the original userform and attempt to establish two ‘modes’ – one for new entry input, and the other for editing existing entries. We look at how to create a sophisticated position control mechanism to retrieve data from the database accurately, and to display it in the Excel userform. This sounds like a big job, but ‘recycling’ code that we know already works can help!

Video 10 – Creating a Sense of Mode – ‘Add New’ and ‘Edit Existing’

In the final video, we explore how to create the sense of ‘mode’ that we refer to above. This is an intricate task that involves ‘saving’ some information for use in subsequent VBA routines. We look at how to combine a worksheet, the Excel userform and the VBA editor to make this happen. The result is a powerful, interactive and robust Excel userform that allows the user to quickly add data to and edit a large database.

Note: Chris did do some ‘tidying up’ of the code after shooting, to improve the quality of the completed file. This involved improving the in-code annotations and the structure of the code. No new coding ideas were introduced, however, and the mechanisms all work in the same way. Don’t feel that you should copy every detail of Chris’ approach, however; you now have the tools to design and implement your own powerful Excel userform-based applications. Good luck!

For regular spreadsheet hints and tips and more on the #ExcelRevolution:

How did you get on with the video series? Were you able to apply the technique in your work? Contact us here.

Posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Use Excel VBA to Create Spin Buttons – Really Precisely!

Use Excel VBA to Create Spin Buttons!

Click here to download the STARTER example file used in the videos.
Click here to download the COMPLETED example file used in the videos.

Spin buttons are another great feature of Excel. Spin buttons speed up the process of data input by allowing us to change the value of a cell with a single button click. Cool! Correctly implemented, they can be a great time-saving feature. But, implementation can be problematic … If you have multiple rows in your spreadsheet, it can be time-consuming to creating lots of buttons. Moreover, getting them to line up neatly can be onerous. With some simple VBA coding, however, you can speed up this task and get it done with maximum precision. Welcome to the Use Excel VBA to Create Spin Buttons video series!

Video 1

In the first video, Chris demonstrates how to create a button, and how to link the button to a cell. It is important to ‘snap’ the button to the gridlines (use the Alt key on a PC) and to ensure that row heights are consistent – this will help create a neat and tidy look.

Video 2

In the second video, we look at how to use Excel VBA to create additional spin buttons. This may sound intimidating, but a basic routine like this is actually fairly easy to create. We use the macro recorder to record and examine some VBA code, then we make some tweaks to get it working for us. Finally, we add an instruction to the Excel VBA to ensure the buttons are positioned with maximum precision.

Video 3

In the third video, we explore how to ‘scale up’ the code. Some VBA to create a single button is great, but what if we want to create multiple buttons? This involves a powerful coding concept known as a ‘loop’. A loop works through a set of instructions a certain number of times, and is exactly what we need to create and position multiple buttons. Check it out!

Video 4

A spin button only works if it is linked to a cell – the cell that changes when the spin button is clicked. Clearly, however, we do not want to click through all of the spin buttons to manually link each to a cell. This would not be in the spirit of the video series! Can we achieve this using VBA instead? Find out in the last installment of the Use Excel VBA to Create Spin Buttons video series!

Was this video series helpful for you? What have you used spin buttons for? Do you have any feedback on the video series? We’d love to hear from you. Get in touch with Tiger using the platforms below!

For regular spreadsheet hints and tips and more on the #ExcelRevolution:

Posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Use Excel VBA to Create Buttons For Super Fast Data Input!

How can we use Excel VBA to create buttons for super-fast data input?

Click here to download the example file used in the videos.

Any Excel user spends a lot of time doing data input. So why don’t we talk about it more? More efficient data input could save us a lot of time … and frustration!

What are the basic data input options? The first and most commonly used option is to simply type data in. This is what you did when you first used Excel, and you still do it now … a lot! It can mean annoying mouse / keyboard work, and time and effort to fix spelling mistakes, and other errors. Are they any better options?

Video 1 – Introduction

A dropdown menu is a good idea. With a single mouse click we can view possible inputs and select one. It is certainly an improvement on keyboard inputs, but still requires some fairly precise movements to manipulate the dropdown menu, which creates cognitive load, or ‘stress’! In a work situation it could be a distraction from your job.

At this point, let’s consider a real-life example. A classroom teacher would want to record student attendance, and might wish to use Excel or another piece of software to do this. This is where the benefits of super-fast data input come into sharp focus. The classroom teacher has a million things to think about: starting the lesson, student queries, have the students done their homework? In this situation, multiple mouse clicks and keyboard work is frustrating; indeed, it might mean that the teacher gives up on the Excel file altogether!

Super Fast Snap

We can use Excel VBA to create buttons for super-fast data input. This means that, rather than clicking around or using the keyboard, the teacher can make a single button click to input the required data. Moreover, mistakes can be quickly rectified by clicking the same button. With this level of usability, super-fast data input can make this kind of Excel-based tool a real asset to the classroom teacher.

How does it work? Well, multiple buttons are required. Anybody who has tried to create and manipulate multiple buttons in Excel knows that this can be onerous! There is nothing worse than copy / pasting and manually lining up shapes. With some basic coding ideas, however, we can dramatically speed up – and de-stress – the process. Watch the video series, work along in the download file, and learn the practical skills. Welcome to Excel VBA Beginners – Create Buttons for Super-Fast Data input!

Video 2

Video 3

Video 4

Video 5

Video 6

Video 7

Video 8

How did you get on with the video series? Were you able to apply the technique in your work? Contact us here.

Ever thought about how to cleanse a dataset using computer coding in Excel?

Read more about the #ExcelRevolution here. Or, follow us on Facebook and Twitter.

Posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

How to Collate Sports Fixtures Results into a League Table in Excel


How can you collate sports fixtures or results into a league table in Excel?

Click here to download the STARTER file.
Click here to download the COMPLETE file.

Sport enthusiasts might wish to create a league table from a set of results in Excel, but it can be difficult to know how to start. Without good pivot table or VBA programming skills, it is a complex task that cannot be completed in one fell swoop. It necessitates some clear, logical thinking about the required steps, and the creation of a well-structured Excel file.


In the introductory video, Chris talks about the task, why it can be difficult to create a league table in Excel, and the best mindset to adopt.

The video series invites us to reflect on what a well-structured Excel file looks like. We propose a structure comprising three elements – a backend, calculations and frontend – and apply it in the video series. This is a good general structure to apply to your next Excel-based task!

Along the way, we apply Excel formulae that are essential in spreadsheet modelling including if, sumif, offset, match and vlookup.

As a starting point, Chris takes the results data from the 2015-16 Premier League season and works through the steps towards a league table. In the final video in the series, Chris tests the model created against the actual Premier League table. Will it be accurate?

Excel Sports Results

Video 2 – convert scores to a result using if

Video 3 – collate wins and losses using countif 1

Video 4 – collate wins and losses using countif 2

Video 5 – use sumif to find goal difference

Video 6 – use match and offset to create the league table

How did you get on with the video series? Were you able to collate the sports results into a league table in Excel? Contact us here.

Ever thought about getting started with computer coding in Excel?

Read more about the #ExcelRevolution here. Or, follow us on Facebook and Twitter.

Posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Excel Custom Navigation: Excel VBA for Beginners


What is an Excel custom navigation system? Switching between sheets, unless using keyboard shortcuts, means moving the mouse pointer all the way to the bottom of the screen. With multiple sheets and workbooks, this can be onerous – is there a better way?

Click here to download the STARTER file.
Click here to download the COMPLETE file.


Watch Chris describe what the Excel custom navigation system is, why it helps, and how to create it.

The Excel custom navigation system is a neat alternative. It positions navigation buttons at the top of the worksheet, not at the bottom, facilitating smooth navigation. It also creates a distinctive visual effect, similar to a ‘cockpit’ or car dashboard, which gives the spreadsheet a professional look.

But, from the learning perspective, there is something more important. Building a custom navigation systems presents an opportunity to learn new coding techniques. Clearly, we do no want to have to create and position all the buttons manually! So, what coding techniques will allow us to do it quickly, precisely and minimise stress?

Excel Custom Navi

The Excel custom navigation system video series takes you through the main steps. We explore how to create multiple buttons, how to position them, how to trigger the right code, and how to copy them across sheets. Some of the code is quite intricate – you can see Chris get into a coding tangle, and work his way out!

Video 2 – Create a shape and duplicate it using VBA

Video 3 – Use VBA to position shapes

Video 4 – Use VBA to control text that appears in shapes

Video 5 – Use VBA to navigate to a sheet dynamically

Video 6 – Use VBA to copy / paste shapes across sheets

Why not try creating your own Excel custom navigation system? Let us know how you get on!

Read more about the #ExcelRevolution here. Or, follow us on Facebook and Twitter.

Posted in Beginner Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Excel Game – Do You Know the Offside Rule?!


How can you get better at Excel? Creating an Excel game is a great way to explore Excel’s capabilities away from the pressures of work or study. We hope that our Excel game inspires you to try to create your own! The game works for PC (but not Mac); it will not work in Google docs or Open Office. If you have Excel, Simply save the file to your desktop, open it, and get started!

Click here to download the file.

Excel Game Shot 1


Our Excel game is called Euro 2016 Linesman. A linesman’s role in football or ‘soccer’ is to indicate if a player is offside. Simply put, a player is ‘offside’ if there are fewer than two players between him or her and the goal, when the ball is played.

In our game, you have to make a number of offside decisions from a ‘bird’s eye’ view of the pitch. Decisions are assessed in terms of accuracy and time. If your decision-making is good enough, the assessor will promote you to a higher league. The aim is to reach the Euro 2016 tournament at the end of the season, but you only have 11 months – and you can be demoted as well as promoted! There is at least one surprise along the way…

Excel Game Shot 2

Why not download it and try it out? Simply save the file to your desktop, open it, and get started! Will your friends be able to beat your scores?

Click here to download the file.


The Excel game file is not protected, and you can explore it to understand how it works. Simply right-click on the sheet tab at the bottom to unhide the hidden sheet.

The Excel game combines together formulae and Excel VBA code to create the required functionality. The shapes are randomly positioned within certain parameters using random numbers. Using VBA, a table on the background sheet is populated with the positions of each of the ‘players’; formulae then compare the positions, and establish if players are offside or not.

Excel Game Shot 3

The file uses formulae and VBA to note the time that decision-making starts, and ends. This gives a time for each decision. Right and wrong decisions are tracked from one routine to the next using module-level variables. User interaction takes place through dialogue boxes – simple message boxes, and ‘yes / no’ message boxes. All of these elements combine together to create the gameplay.


Exploring an Excel game is a great way to improve your Excel skills away from the pressures of work or study. This kind of creative application pushes our imagination and skill level, which can only benefit our day-to-day work. The Excel game is also a great example of a complete Excel application: more important than understanding the formulae and VBA code is to understand how the whole thing works. When you learn to combine different elements (formulae, code, user interface, backend) together, you can create powerful Excel applications.

We hope you enjoyed the Excel game! Do you have a specific question about how it works? Why not leave a comment on the video on the YouTube channel, and share the file with your colleagues.

Ever thought about getting started with computer coding in Excel?

Read more about the #ExcelRevolution here. Or, follow us on Facebook and Twitter.

Posted in Advanced Articles, Beginner Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Rich Picturing: Six Tips for Getting Started

This article tells you what rich picturing is, why it is important, and how to do it. It provides six tips for getting started, and two example rich pictures based on cases in the business press. If you’re not in a reading mood, you can simply watch the video below!

What is Rich Picturing, and Why Is It Important?

Analysts and managers in organisations tend to talk or write about situations. You probably write many emails and attend many meetings every day! It is the accepted way of doing things.

The problem is that there are limitations to the spoken and written word. Neither helps us to view situations holistically, or express dynamic relationships well. It can limit our understanding. If a situation is not well understood, it is difficult to take purposeful action, which leads to stagnation, or failure. There are plenty of recent examples of failed strategic initiatives to support this idea.

In the 21st century, markets can be transformed, redefined, or made irrelevant very quickly. Our business environment has changed; but, we generally use the conventional medium to understand it. We think we need alternative ways to process this new, complex environment, and to appreciate the opportunities and threats it presents. We question whether the conventional approach is always fit-for-purpose! What are the alternatives?

Rich picturing is a richer and more challenging medium that presents new possibilities for analysts and managers. It refers to the practice of representing a problem situation visually. You could say that it is drawing out your situation on paper. It can be done individually, or in groups.

Rich Picturing Still 1

Usage for Analysts, Managers and Consultants

For an analyst, rich picturing offers new possibilities. In your day-to-day work, you would typically receive a briefing from your manager, some data, and would be expected to build a model to ‘solve’ a problem. Rarely, however, are you asked to explain ‘why’ a model is important; but, perhaps you have doubted the utility of some of the models that you have been asked to build. Rich picturing allows you to look at the situation in its entirety, and to choose the best place to focus your modelling efforts. It’s a great way maximise the impact of your modelling skills.

For a manager, rich picturing can be an awesome strategic tool. Rich picturing allows you to view the different areas of a problem situation at the same time. Using the structure outlined below, you can capture the firm-, industry-, and environment-level factors in a single view. This engenders a more holistic understanding of the situation, and a stronger appreciation of the linkages between the various elements. It provides you with an excellent starting point for strategic thinking.

For a consultant, rich picturing can facilitate a first client meeting. Whether you are consulting internally or externally, there is a need to ‘talk through’ the situation and to air all of the issues. Your client will no doubt have an idea what the root causes of their problems are, but it is your job to provide constructive challenge, and offer alternative hypotheses. In an individual, group or one-to-one setting, rich picturing can be a useful addition to your consulting toolkit.

For everybody, rich picturing offers a creative way to think about business problems. Research suggests that pictures are more memorable than words, and people usually think it is fun putting one together. So, how do you get started with it?

Rich Picturing Still 2

Some Tips to Help You Get Started!

Now, if you are thinking ‘I won’t be able to do that, because I can’t draw!’ you are not the only one. But, as we will find out, your artistic ability or inclination does not really matter!

The purpose of these resources is to share some basic guidelines that anybody can apply to help them get started with rich picturing.

So, let’s look at some helpful tips.

The number one rule of rich picturing is ‘It’s not about the picture!’ We are not suggesting that you should show your picture to your client or manager; but we are suggesting that creating a rich picture will help you view a problematic situation differently, and generate new insights. You have to adopt an entirely different mindset to create a rich picture, and this is where the value actually lies. The presentational quality is less important.

Number two is to use a structure to help you get started, and to identify the levels relevant to your problem situation. Use three levels and divide the paper into three to represent these. We recommend using the levels of firm, industry, and broader environment. Other possible levels are the individual, the team, the department or division, or a group of competitors. There are many possibilities; the point is that the picture helps you think about the different levels and captures the relationships between them.

Rich Picturing Structure Graphic

Number three is to create an issues list. It is important to diagnose your organisational situation – to identify all of the things that are going wrong! So, when you identify something that is problematic, contentious or uncertain, write it down. Try to be articulate: writing ‘marketing’, for example, is not really helpful; but, writing ‘difficult to identify target market’ is more precise and therefore useful. The list of issues is the main output of the rich picturing process. You can position it on the right of the picture, as illustrated in the graphic above.

You may not want to share your rich picture for whatever reason, but the issues list identifies all of the insights that you gained, in text form. It should guide your next steps: deciding where to take action in the problem situation, and the best modelling approach.

Number four is to decide on the focus. The focus should be your client or customer. Of course, your client or customer could be yourself, if you are using the picture for your own purposes. Draw your client or customer in first, usually it is best to position him or her just inside the organisation part of the picture.

Number five is to focus on relationships. It is important to make the picture as rich as possible – you should not be ‘filtering out’ many of the details yourself. However, it is equally important to link the various elements together. As an analyst or a manager, it is helpful to understand how long-term trends shape the dynamics of the industry, and the firm’s position in relation to its competitors. You must focus on the interactions between the parts of the picture to do these things. Use dotted lines to indicate a link, and an arrow to convey a clear causal relationship.

Rich Picturing Still 4

The final guideline is to create an icon set. As you do more rich picturing, you will find yourself using the same symbols or icons repeatedly. Get used to drawing common features such as people and other competitors, and develop quick ways to draw them. If you can quickly draw out the important elements, it will improve the efficiency of the process. So go on, create your own icon set!

There are six basic guidelines to help you get started. We hope you realise that it is nothing to be scared of, and we hope this motivates you to try it out for yourself! Now, time to practise…

Use the below cases and rich picture examples to help you get started. Access the article, read it and, with the six tips in mind, create your own rich picture for the organisational situation. Then, compare to the examples provided.

It is important to bear in mind that there is no ‘model’ rich picture for any situation – everybody’s interpretation is different. However, comparison with the examples may help you check how well you have applied the principles outlined above. It is important to also reflect on how rich picturing made you think about the situation. What insight, if any, have you gained that you would not have gained from a bullet point analysis?

Exercise One – Hugo Boss
Link to article
PDF of example rich picture

Exercise Two – Morrisons Supermarket
Link to article
PDF of example rich picture


We hope you’ve found these videos helpful, and thar you have tried rich picturing for yourself. How did you find it? Let us know! Contact us here.

Do you want to see more of this type of content? Content focused on ‘soft’ business analysis techniques, as opposed to spreadsheet tutorials.

Ever thought about getting started with computer coding in Excel?

Read more about the #ExcelRevolution here. Or, follow us on Facebook and Twitter.

Posted in Excel Guidance Articles, Excel Guidance Videos & Articles, Uncategorized | Comments closed

Excel VBA for Beginners – Use Excel VBA to Cleanse a Dataset


This Excel VBA for Beginners series is aimed at Excel users just starting out with code, or those who have never coded, and who are curious about it. Too often, Excel users find themselves engaged in tricky manual tasks that take up time and create stress. Data cleansing is one such task. If you have ever gone through a list of data and looked for mistakes (in the spelling of people’s names, for example) then you have done a data cleansing task. It probably was not much fun! With a little coding knowledge, it is possible to dramatically speed up this kind of task, reduce the stress, and kick start your coding journey. Welcome to Excel VBA for Beginners!

Excel VBA for Beginners Cleanse Data Picture 1

Excel VBA for Beginners practice file – NO CODE

Excel VBA for Beginners practice file – WITH CODE

The video series breaks this kind of data cleansing task down into three steps. In the first video, we tell Excel where our data is, and which cells to look at. It is possible to take advantage of Excel VBA’s collections to maximise efficiency at this stage. A collection refers to a group of objects that Excel knows belong together. For example, Excel understands all of the open workbooks as a ‘collection’, and all of the worksheets within a workbook. Using collections, we can say to Excel programatically ‘do something to everything in this group of things’.

This is exactly what we do. Using a For Each construct, we define a range of interest and establish a basic loop. It would be possible to define this loop in static terms by using notation such as range(“B3:B200”); if we took this approach, however, what would happen if data were added to the list? It would be discluded from the process. It is much better, therefore, to use a dynamic reference that could handle additions to the data. In the video, we create an Excel formula to measure the size of the dataset, and then reference its cell in the code. In doing so, we create a thing of beauty: worksheet formulae and VBA code working together in harmony. The result: awesome dynamic functionality.

The purpose of the exercise is to identify mistakes (for example, spelling mistakes in people’s names) in a dataset. In the next video in this Excel VBA for Beginners series, this is what we do. What worksheet formula might help us to do this? We have a list of data and a list of names, spelt correctly. The countif formula counts how many times the value in a cell appears in a specified range. So, if the name is spelt correctly, the countif formula would return a value of 1 – indicating that the value in the list of data appears in the name list, and that the name is spelt correctly. A mistake, on the other hand, would return a value of 0. Once again, we can reference the cell that contains the countif formula in the VBA routine. In this way, we establish a mechanism for flagging up errors.

By harnessing the power of the loop, we instruct Excel to repeat the process for each piece of data. Cool!

In the final two videos, we tell Excel what to do if a mistake is found. We explore two possible approaches: the first exits the code when a mistake is found, tells us where the mistake is, and allows us to fix it; the second loops through the whole data set, tells us how many mistakes were found, and highlights the mistakes in a distinctive colour. This kind of helpful user interaction will help you to maximise the impact of your coding skills.


We hope you’ve found this article helpful, and you have now kick-started your coding career! How did you find it? Contact us here.

Ever thought about creating a dynamic league table in Excel?

Read more about the #ExcelRevolution here. Or, follow us on Facebook and Twitter.

Posted in Beginner Articles, Beginner Videos, Excel Guidance Articles, Excel Guidance Videos, Excel Guidance Videos & Articles, Intermediate Articles, Intermediate Videos, Uncategorized | Comments closed

Excel Offset Function – Give It Some Love!


The Excel Offset Function is an awesome Excel formula with unique capabilities. Yet, the Excel Offset Function receives little attention from Excel users, and is overshadowed by its more glamorous cousins – the LOOKUP functions. Why is this? The aim of this series is to explore this issue, to shed light on the capabilities of the function, and to inspire you to use it in your work. So go on, give it some love!

Excel Offset Function Picture 1

First, why does the Excel Offset function receive little recognition? We think that there is a problem with the function’s name. The role of other functions, in comparison, can be easily understood from their names. Take VLOOKUP, for example, whose role is to ‘look up’ data from a table. Maybe this helps explain why VLOOKUP is so popular with Excel users.

Offset, on the other hand, sounds rather obscure. Very few people realise intuitively that the name of the function is derived from its ability to control position; in other words, to establish an anchor point and to ‘offset’ or ‘move away’ from that point by a certain number of rows / columns, then return a value from a cell, or to perform an operation on a range of cells. The first function is really useful; the second is unique to offset and, when combined with other functions, creates awesome dynamic functionality.


The series comes with a download file, and we recommend that you work along with the video. Click to download the Excel spreadsheet file below:

Excel Offset Function practice file

In the first video, we explore a basic task: retrieving data from a table using the offset function. Now, usually we would deploy a LOOKUP formula to do this. However, the structure of the table dictates that a LOOKUP formula would not be easy to set up. Offset, however, can handle this non-conventional format. It is an illustration of why it is useful to have a range of possible approaches at your disposal.


In the second example video, we exploit the unique capabilities of the Excel Offset function. We have already seen that the formula consists of three main components: (1) a reference, or anchor point, (2) the number of rows to move away from the reference, and (3) the number of columns to move away from the reference. With these components, the formula returns the value of the cell that it arrives at.

We can optionally add a 4th and 5th component to the formula. These are often ignored, but are actually the key to creating awesome dynamic functions. The 4th and 5th components determine the ‘height’ (in rows) and ‘width’ (in columns) of the range to be returned. Now, on its own, this functionality would not be particularly useful. However, when combined with other formulae – in this case the SUM formula – powerful functions result. Take a look at the video, and make sure you work along in the download file (link above).


In the final example video, we combine offset with an Excel chart to create a chart that changes according to a user input. This means that the chart is ‘dynamic’ – a great way of saving space in your spreadsheets. As we have seen, the Excel Offset function allows us to reference a range of cells. In this case, we link the offset formula to a match formula which is, in turn, linked to the user input. This chain of dependencies creates awesome ‘click-of-a-button’ functionality. Check it out!


We hope you’ve found these videos helpful, and feel enthused about the Excel Offset function. Make sure you try it out, We think that you will not regret it! Let us know how you get on, and what applications you have found. Get in touch here.

Now, learn how to use visual basic code in Excel.

Read more about the #ExcelRevolution here. Or, follow us on Facebook and Twitter.

Posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Excel Visual Basic (VBA) for Post-Beginners

If you know the basics of Excel Visual Basic (VBA), and have watched our Excel Visual Basic (VBA) for Beginners series, it is time to take the next step. In this series, We take an everyday task – creating reports from a database – and look at how to complete it using Excel VBA. The series consists of 6 videos, including the introduction.

The series comes with a download file, and we recommend that you work along with the video. Click to download the Excel spreadsheet files below:

File WITHOUT Excel Visual Basic
File WITH Excel Visual Basic

Video 1: Introduction

Video 2: Record Code to Duplicate Sheets

Video 3: Loops to Duplicate Sheets

Video 4: Loops and If to Delete Sheets

Video 5: Offset to Position Data

Video 6: Create Reports Using VBA – Full Demo

Have you completed the video series? How did you find it? If you develop confidence in these areas, it won’t just save you time, it will transform the way you work with Excel spreadsheets.

Now, learn about another advanced Excel technique.

Can you help us improve these resources? We would love to hear from you. Contact Us.

Posted in Advanced Videos, Excel Guidance Articles, Excel Guidance Videos, Excel Guidance Videos & Articles, Intermediate Videos | Comments closed

Excel Spreadsheet For Cricket: Case Study


How can an Excel spreadsheet for cricket help people to get the analysis they need? The below case study describes an Excel development project that modelled the scoring system of an amateur cricket league, created instant analysis, and saved the time and energy of the league organisers. Read on to find out how an Excel spreadsheet for cricket can provide insight, and save valuable time and energy.

Click here to watch a video explaining some of the Excel techniques used to create the Excel spreadsheet for cricket.

Excel spreadsheet for cricket Picture 1


Ian Smith, Secretary of the Nottinghamshire Premier League, got in touch with Tiger Spreadsheet Solutions in Spring 2013. Ian needed a spreadsheet to track results in Nottinghamshire’s highest standard of amateur cricket, and to generate a league table to publish on the league website. Due to the intricacies of the scoring system in cricket, this was not as easy as it might sound. Ian picks up the story.

‘To be honest, I was not sure if an Excel spreadsheet for cricket would work well. The scoring system relies on a host of complicated calculations such as run rates and bonus points. Also, we wanted to enter results for a whole season, and to be able to view a league table instantly. I did not think that an Excel spreadsheet for cricket could handle this kind of complexity and volume; but, what we had left us prone to human error and was not fit-for-purpose. We needed something better. It is fair to say that we were stumped!’

The previous solution, based on an Excel spreadsheet, came with a long set of instructions and relied on the league organisers to make some calculations themselves. Organisers would also have to manually sort and manipulate data to create the league table in the correct format. This led to inaccuracies in the league table, angry emails from team captains, and frustrated organisers.

Excel spreadsheet for cricket Picture 2

Project Inception

How did the project move forward from here? Chris Mortimer of Tiger Spreadsheet Solutions, who did most of the development on the project, explains how Tiger became involved.

‘As a keen amateur cricketer, I was excited about an Excel spreadsheet for cricket. We knew that what Ian needed was within the capabilities of Excel. We also knew that the previous solution, which relied on long, complicated formulae that are not easily auditable, was not ideal. We wanted to create something simple but powerful, that required minimal effort on the part of the user. Moreover, our aim was to make the league table ‘dynamic’, so that it would update automatically, without any user clicks. This would ease the pressure on league organisers.’

Development Process

In the first stage of the project, Ian shared information about how points are scored in the league. Chris was then able to produce an initial prototype that Ian was able to experiment with, and provide feedback on. On the technical aspect of the modelling, Chris comments:

Excel spreadsheet for cricket Picture 3

‘We needed sophisticated analysis to create the kind of league table we were looking for. To do this, there were a few possible approaches; for example, using pivot tables or visual basic for applications (VBA) – Excel’s programming language. However, the ideal solution would not require any clicks on the part of the user to trigger code, or to update pivot tables. So, we opted for a solution based entirely on Excel formulae, and employed multiple hidden sheets to perform calculations for each team. The second priority was to create a pleasant user experience – would the spreadsheet be easy-to-use? We met this priority by creating a clearly structured input sheet for the league organisers to input results. Inputs here are immediately reflected in the other sheet that users can see – the league table itself (pictured above.)’

Click here to watch a video explaining some of the Excel techniques used to create the Excel spreadsheet for cricket.

Project Impact

As the project progressed, the league organisers were trained in how to use the spreadsheet, and small amounts of data were inputted to test it out. Chris and Ian worked together to iron out bugs as and when they occurred. The spreadsheet was ‘rolled out’ at the beginning of the 2013 season, and generated its first published league table in April 2013. Since then, the league organisers have used the spreadsheet to quickly generate the analysis they need. On the impact of the project, Ian comments:

‘I did not think that an Excel spreadsheet for cricket would have such an impact. Because of the spreadsheet, a task that was mentally demanding and time-consuming is now routine. It has made the job of the organisers less stressful and allowed us to focus on running the league as efficiently as possible. If you are working with a problematic Excel spreadsheet in cricket, I would recommend getting in touch with Chris and the team at Tiger.’

Would you like to talk to somebody about Excel spreadsheet development projects? Contact us.

Read more about the #ExcelRevolution here. Or, follow us on Facebook and Twitter.

Posted in Advanced Articles, Excel Case Studies, Excel Guidance Articles, Intermediate Articles, Uncategorized | Comments closed