Author Archives: Chris Mortimer

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

Excel Spreadsheet in Farming / Agriculture: Case Study

How can an Excel spreadsheet in farming / agriculture be helpful? In the below Excel spreadsheet development case study, Tiger Spreadsheet Solutions worked with a client in farming / agriculture to improve business efficiency and effectiveness. The project began in January 2014 and is ongoing.

Excel spreadsheet in farming / agriculture picture 1


Mary-Anne Ross, owner of the Milimani Estates Farm in Chisamba, Zambia, contacted Tiger Spreadsheet Solutions in January 2014 for help with the farm’s lambing operations. The farm rears lambs and sells them to national buyers who buy according to weight. The challenge facing Mary-Anne, therefore, was to improve the quality of livestock, as measured by the ‘dressed out’ weight, and the selling price. With a growing stock of animals, it was becoming difficult to track the critical information that determines the quality of the livestock and the profitability of the enterprise.

Excel spreadsheet in farming / agriculture picture 2

Mary-Anne picks up the story, ‘In a way, we were a victim of our own success. Sales were strong, which allowed the stock of animals to expand. But, growing numbers meant that our staff could no longer accurately remember information about the animals; for example, which lambs had been inoculated and wormed, and which had not. More importantly, as the flock grew, we could not keep track of the lambs’ fathers! This meant we could not identify the best rams and keep improving the weaning weight of the stock.’

A number of software solutions for livestock management in farming / agriculture are available for purchase. Why did Mary-Anne not invest in a piece of software? She comments, ‘We were aware of ‘off-the-shelf’ software, but I was not convinced that a piece of software would do what we needed. Moreover, a significant ‘one-off’ investment would be required, which was not ideal. I was looking for something more bespoke that would meet our needs very closely. I thought that an Excel spreadsheet in farming might be possible, which is why we got in touch with Tiger.’

Project Inception

Chris Mortimer, leading the project for Tiger, describes the inception phase of the project. ‘Mary-Anne was clear about the information she needed, and this was a great starting point. Our approach was to track back through the problem from the required outputs in order to identify the input data required. This allowed us to develop an input sheet for staff to use to record information about each animal. The sheet uses techniques such as data validation and cell protection to ensure user-friendliness, and we trained staff to make sure they were confident using it.’

Excel spreadsheet in farming / agriculture picture 4

After the inception phase, the project progressed on an iterative basis: a small development step followed by client feedback, and more development. The project took place almost entirely online via email dialogue and Skype conversations. This way of working created a constant open line of communication which ensured that the spreadsheet model is a close match with client needs.

Development Process

As the dataset grew, the Tiger development team worked through the Excel spreadsheet development process to create the analysis that the farm required. The first priority was to understand the performance of ewes and rams according to the weights of their progeny. By analysing each lamb’s mother and father, selling weight and other data, the spreadsheet model presented a ‘league table’ of rams and ewes that identified the top performers on the basis of important criteria. Other important analyses were added over time. A ‘dashboard’ summarises performance information in a visual, easy-to-understand format.

Project Impact

What has been the impact of the Excel spreadsheet model? Mary-Anne comments, ‘The record keeping is so useful and the analysis amazingly easy-to-read. It looks great! I cannot believe that I can access so much information, so easily. We are now able to make investment decisions based on the history of the animals, not just based on our suspicions and intuition; it has helped us to exploit the best rams and to replace the poor performers, for example. The development process has been straightforward and the team and Tiger very good to work with.’

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

Generate Random Data in Excel

Learn how to combine =RAND(), a probability table and VLOOKUP to generate random data in Excel. Practise! DOWNLOAD the PRACTICE FILE used in the video HERE.

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

Combining VLOOKUP and MATCH

Learn how to combine VLOOKUP and MATCH to retrieve data from a table and save time and effort. Practise! DOWNLOAD the PRACTICE FILE used in the video HERE.

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

Dynamic Dropdown Menus in Excel

Want to create a dropdown menu in Excel? You should! Dropdown menus save time and make spreadsheets look cool. Dynamic dropdown menus in Excel are particularly sophisticated and useful – they will impress your colleagues!

The first video shows you how to create a basic – or ‘static’ – dropdown menu.

Practise! Download the file used in the video here.

As the first video demonstrates, we create a simple dropdown menu in Excel by making a list of data and using the Data Validation dialogue box to reference the list. This allows users to select from the dropdown menu and to avoid unnecessary keyboard work. Not only that, it embellishes your spreadsheet with a user-friendly feature that is beyond many people’s Excel capabilities.

Now, what if we wanted to add to the list of data that our dropdown menu refers to? We could add to the list, then manually update the dropdown menu using the Data Validation dialogue box. This would work perfectly well but would be time-consuming; moreover – what if somebody else were using the spreadsheet? Ideally, the dropdown menu would automatically update to include new entries in the data list. This is where dynamic dropdown menus in Excel come in handy, as the second video in this series explains.

In the second video, we explore how to create ‘dynamic’ dropdown menus in Excel – menus that update as you add to your list of data. We utilise the COUNTA and OFFSET formulae to create a dynamic reference to a list that we can update with new data. Formula building is tricky because, in the Data Validation dialogue box, we cannot see the prompts that Excel’s formula editor provides. Therefore, dynamic dropdown menus in Excel require some practice; but they will save you a lot of time and impress your clients, colleagues or classmates.

How did you get on? Contact us.

Having fun? Great! Check out more interesting Excel tutorials on our YouTube channel.

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

Excel for Teachers: Create A Student Progress Tracker

Intro Graphic

Not in a reading mood? Watch a VIDEO of this article HERE on YouTube, or simply scroll down to view videos


  • This article and video series show how to create a student progress tracker in Excel
  • The tracker converts grades to numbers, compares actual vs. target grades, allows fast data input and generates visual data summaries
  • The techniques we are cover are VLOOKUP, dropdown menus, COUNTIF, and charts


We are writing this article on GCSE results day, 2014, which reminds us that the focus on performance measurement in education is relentless. For the modern teacher, basic data analysis skills are crucial. Yet, our experience of Excel Development Projects in education tells us that most teachers lack confidence in Excel.

Our philosophy is built on the conviction that a small amount of Excel learning, focused on the important techniques, can have a disproportionate effect on a person’s analytical skill. There is a fundamental misunderstanding of Excel – that it is the preserve of IT specialists and analysts; in fact, the important techniques are straightforward to learn and, with some guidance, most are perfectly capable of unleashing Excel’s awesome computational power – as the following example illustrates.

In this article and video series, we teach three techniques (VLOOKUP, dropdown menus, COUNTIF) that are considered ‘advanced’, but are relatively simple to learn. We explain how to combine the techniques together to create a student progress tracker.

First, the article unpicks a thorny problem: how to convert grades (A, B, C, 2a, 2b, 2c) into numbers in order to compare target vs. actual grades using VLOOKUP. This is a common problem for teachers and, as a company, we have been asked to do this at least a dozen times in the past 2-3 years.

Second, the article explores how to create dropdown menus that will accelerate the process of data input and minimise annoying keyboard work.

Third, the article sheds light on how to create data summaries, in tabulated and visual form, that communicate the bigger picture – and that might impress senior management.

Ensure that you download and use the practice file so that you can practise and develop the skill yourself.



The first video (above) deals with the issue of comparing target and actual grades, and understanding the difference between them. Many education systems use letter-based rather than numerical grades – in the UK, for example, grades such as 2a, 2b, 2c are common. This approach offers a useful level of precision for teachers, but can be a nightmare when trying to quickly establish the difference between two grades for each individual in a group of students.

Excel can help by quickly converting the grades into a numerical value. Once the grades are in numeric form, it is easy for us to understand the difference between target and actual grades, and to track progress.

The formula we use for this is VLOOKUP. As explained in the above video, the formula can convert letters to numbers by referencing a table. It is first necessary, therefore, to create a table that contains all of the grades that you are using and an equivalent numerical value. A simple, two-column table with grades in the first column and numbers in the second will suffice. VLOOKUP can then ‘look up’ the grade in the table and return the appropriate number.

The video shows you how to build the formula step-by-step. The practice file (DOWNLOAD) contains a fully worked example for your reference. Don’t expect to get it right first time but, with practice, the skill will come.

If you are struggling with the VLOOKUP concept, take a look at our dedicated VLOOKUP video that features a much simpler example.

But, the video goes further. We implement two VLOOKUP formulae in the same cell, linked by a subtraction sign (-), one relating to the actual grade, and one to the target. This allows us to understand the difference – in numbers – between the actual and target grades, and to generate a simple, effective performance measure for each student. Nice!

Now, how can we speed up the process of data input in the student progress tracker, avoid annoying keyboard work, and generate simple summary analysis?


The second video shows how to create a dropdown menu for speedy data input. Dropdown menus allow us to bypass the keyboard when inputting text, saving time and freeing up headspace. The process for creating them is relatively simple, and is broken down in detail in the video above.

A dropdown menu is actually a form of data validation, because it predetermines – or ‘validates’ what appears in a particular cell. So, dropdown menus are generated through the Data Validation dialogue box under the Data tab in Excel 2013. Make sure that you try to make your own!

Now we have implemented two mechanisms – one to measure progress on the basis of target and actual grades (VLOOKUP), and the other to facilitate quick data entry (dropdown menus). We have already created a useful tool; but we are missing a vital element: summary information.

Effective summaries convey the key messages in the dataset. In our case, we are seeking to understand the overall performance of the group. If a senior manager were looking at multiple groups, she would want to understand at-a-glance if the group is a cause for concern or not; she would want to understand how much progress is being made, without having to dig into the data. With this in mind, what tools are available in Excel to help us create an effective summary?

COUNTIF is another little-known formula that creates powerful analyses. Put simply, it counts the number of cells in a given range that meet a certain criteria. In the video, we use the formula to count the number of students who are achieving A-C grades. Further, we use a simple SUM formula to aggregate the progress scores of each student into a single measure of group progress. Cool!

Now, how can we add a visual analysis to increase the communicative power of the spreadsheet?


The third and final video deals with the topic of creating a visual summary in the form of a chart, alongside a compact summary table. We use simple formulae to link worksheets together and ‘pull through’ the summary information to a separate summary worksheet.

This mode of analysis – presenting tabulated and visual information side-by-side – is usually an effective approach, not least because it pleases both the aesthetes and the numerically-inclined! Using the various techniques, we have a created an easy-to-use tool that communicates the key points in a easy-to-understand manner. We have used Excel to create a student progress tracker that works at an individual and group level.

Education is a data-intensive environment and teachers’ analytical skills are stretched on a daily basis.

It is our belief that, with the right guidance and mindset, you can approach data analysis tasks with confidence. It is is case of knowing the right techniques, and knowing how they fit together. Unfortunately, conventional Excel training tends to teach techniques in isolation and out-of-context.

At Tiger, we believe that progress in Excel is not ‘linear’ – x hours study does not result in x units benefit. Rather, there is a group of techniques that – if you master – can dramatically accelerate your Excel learning, unlocking exponential benefits as you improve. Further, we offer a different approach to learning Excel based on practical, in-context examples and experiential learning (learning through making mistakes) that demystifies ‘advanced’ techniques and, hopefully, goes some way to democratising the field of data analysis.

This is the fundamental idea behind the #ExcelRevolution – that focused learning of a handful of key Excel techniques can transform your analytical ability. This has knock-on effects on a company- or organisation-wide level, and even on the economy as a whole.

Good luck, and don’t hesitate to get in touch.

Done? Now learn more about how to create a dynamic league table in Excel:

Rank Things and Create a League Table in Excel.

Or, watch more videos on our YouTube channel.

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

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

Excel Spreadsheet Development in the Third Sector: Case Study


  • The project helped a charity serve its customer base more efficiently
  • The project saved members of staff time and energy, and improved Excel skill
  • The project took place in Summer 2013 and lasted three months


Fiori Musical Logo


Fiori Musicali approached Tiger Spreadsheet Solutions in Summer 2013 for help with managing data in Excel spreadsheets. Fiori Musicali, a registered charity, are one of the principal providers of high quality professional classical concerts outside of the London area. The charity’s issues focussed on management of an important dataset containing details of those who have attended concerts.

Bernard Rapson, Treasurer of Fiori Musicali and the member of staff charged with dealing with data issues, picks up the story:


“Although we are a relatively small organisation, our issues were complex. Managing a database of 1000s of customers was becoming onerous. We had lots of data on previous concert attendees, but could not easily manipulate this data to target specific geographical areas. There was a growing sense of frustration. We investigated off-the-shelf software, but we were not convinced that it would suit our needs and investment would be costly. Tiger offered us a possible solution.”


Project Inception

Bernard’s enquiry via the Tiger Spreadsheet Solutions website started a dialogue about the charity’s needs. Chris Mortimer, leading the engagement on behalf of Tiger, explored the problem situation with Bernard. The two agreed to work together on a day-by-day basis which offered both parties flexibility. Chris travelled to the charity’s headquarters for the first in-person development session. Chris says:


“This is the kind of work that we relish. Exploring a business problem ‘live’ with the client, understanding root causes, and identifying improvement opportunities is a great challenge. Our conversations with clients suggest that that these exploratory sessions really help. Our service extends beyond merely fixing spreadsheets; we apply our experience with Excel to help clients view data and their business differently.”


Development Process

Issues were identified and prioritised, shaping a project plan which was executed over the subsequent weeks. The plan included a second in-person development session at the Tiger offices in Nottingham. This session focused on a particular issue: the generation of targeted customer lists for marketing purposes. This dataset presented the typical complications, including duplicated names and inconsistent capitalisation; Chris worked with Bernard to implement formulae and visual basic coding to resolve these issues. Bernard comments:


“As a fairly experienced Excel user, it was beneficial to be able to sit alongside the developer and understand the changes made. I found Chris to be a good trainer. I was able to pick up several new techniques, and the experience broadened my appreciation of how Excel spreadsheets can help businesses.”


Project Results

The project continued in a cyclical manner: exploration, development, testing. This approach meant that communication between user and developer was consistent and effective, leading to a solution closely aligned to the needs of the charity. The improved spreadsheets have accelerated the process of separating out target audiences, and has helped Fiori Musicali to serve their customer base more effectively. Bernard remarks:


“I would recommend an Excel spreadsheet development project with Tiger. The project has helped is deal with a number of nagging data issues, avoid investment in costly software, and we are now able to handle box office data more efficiently. Tiger are also great fun to work with.”


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