Author Archives: Chris Mortimer

Excel Spreadsheet in Farming / Agriculture: Case Study

 
Introduction
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

Background

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

DOWNLOAD the PRACTICE FILE here.
 

  • 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

 

RATIONALE
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.

WHAT THIS ARTICLE COVERS
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.

DOWNLOAD the PRACTICE FILE here.

PART 1: CONVERT GRADES TO NUMBERS USING VLOOKUP

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?

PART 2: CREATE DROPDOWN MENU AND USE COUNTIF TO ANALYSE

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?

PART 3: CREATE SUMMARY TABLE AND CHART

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.

CONCLUSION: WHAT WE HAVE LEARNED AND WHY IT IS IMPORTANT
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

Background

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

Excel Visual Basic (VBA) for Beginners

Interested in speeding up Excel and automating time-consuming tasks? Learn the basics about Excel spreadsheet coding with these four videos.

Excel Visual Basic (VBA) for Beginners Part 1 of 4

Excel Visual Basic (VBA) for Beginners Part 2 of 4

Excel Visual Basic (VBA) for Beginners Part 3 of 4

Excel Visual Basic (VBA) for Beginners Part 4 of 4

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 Articles, Beginner Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles | Comments closed

Create A League Table in an Excel Spreadsheet

Need to create a league table in an Excel spreadsheet? The below videos take you through the key steps.

Download the practice file here.

Read the article here.

Create League Table in an Excel Spreadsheet Part 1 of 3:

The first video (above) explains how to create a Rank formula – the formula that Excel supplies to help rank pieces of data. TIP: When building a Rank formula for a league table, it is important to remember to fix references for your league table using the F4 key. This will insert $ signs around the league table reference, which means that you can copy your formula to other cells.

Create League Table in an Excel Spreadsheet Part 2 of 3:

The second video covers two of Excel’s most useful formulae – Match and Offset. Like fish and chips, these two work together beautifully. Applied effectively (it will probably take you a few attempts,) this technique will allow you to create a dynamic league table. Make sure you practise using the download file (link at top of article.)

Create League Table in an Excel Spreadsheet Part 3 of 3:

The final video deals with the topic of equal values. These are a common feature of league tables – a football league table is a good example. We explore how to model important details that distinguish between pieces of data. For example, goal difference in football. Failure to include these details could result in errors in your league table, so it is important that we understand how to deal with them.

League table Excel picture

Done? Now learn more about the formulae used in this video:

Excel Offset formula

Excel Match formula

Combining Match and Offset

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

What is Excel Spreadsheet Development?

Excel Spreadsheet Development is the practice of creating spreadsheets that save businesses time and money. In the video, Chris talks about why Excel Spreadsheet Development is important.

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

Excel Spreadsheet Development in the Legal Sector: Case Study

 

  • The project accelerated a complex admin task for Brethertons LLP’s Probate Department
  • The project rationalised a process, saved staff time and improved outputs for clients
  • The project took place in Summer 2012 and lasted four months

 

Intro Graphic

Background

Brethertons LLP approached Tiger Spreadsheet Solutions in Spring 2012 for help with spreadsheet development. The firm’s Probate Department uses a spreadsheet to assist with the processing and administration of estates; specifically, to understand how much tax should be paid to HMRC, and how much money – to the penny – each beneficiary should receive.

There was a feeling within the department that the Excel file could be improved. Tony Woodhouse, IT Manager, Brethertons LLP, explains:

 

“Although the spreadsheet was functional, its limitations were becoming evident as cases became more complex. The team were spending too much time ‘getting it to work’. Generally there was a feeling that we could get more out of Excel – we just needed an injection of spreadsheet expertise. So it was great to be introduced to Tiger.”

 

Project Launch

At an introductory meeting in April 2012, Tiger Solutions and the firm agreed to work together on a month-by-month basis to improve the spreadsheet. Chris Mortimer led the project for Tiger and was the principal developer of the improved spreadsheet. He picks up the story:

 

“Our initial approach is always to understand what users are trying to get out of the spreadsheet. So our first move was to arrange a meeting with the whole Probate team. This allowed issues to be aired and explored, and a list of priorities to be drawn up. It was also a great opportunity to meet the staff and hopefully to gain their commitment to the project.”

 

Chris created a project plan by combining the needs of the staff with Tiger’s 6 spreadsheet development principles: structure, formulae, robustness, presentation, automation and user engagement.

An audit of the spreadsheet with these principles in mind led to ‘quickwins’, or quickly realisable improvements, at the beginning of the project. One was to implement a consistent formatting approach across the whole file: input cells coloured in light blue, headings in white and calculations in grey. This gave the file a new, sophisticated feel and helped staff to navigate smoothly around the various sheets.

Development Process

The project progressed with Brethertons LLP and Tiger working on a week-by-week basis: a day’s development work, acquisition of user feedback and a short summary report. This iterative way of working aligned development closely with user needs and allowed the project to stay on track. Nadiya Virani-Bland, Head of Department, Private Client provided feedback on new developments on behalf of her team:

 

“I found the spreadsheet development process to be flexible and effective. It was great to be able to talk directly with Chris about our requirements, and to see them implemented in the spreadsheet at short notice. The improved version is easy to operate and impresses clients with its professional appearance; it allows us to focus less on getting Excel to work and more on delivering the best possible service to our clients.”

 

The spreadsheet evolved to incorporate sophisticated functionality, including button-triggered macro routines that automate time-consuming tasks. Several of these routines interact with legal form software to populate complicated HMRC forms at the click of a button.

Project Outcomes

The application was rolled out to users in late 2012. So far, feedback has been positive, with several team members commenting that the spreadsheet saves time, energy and improves the presentation of the documents shared with clients. Tony Woodhouse, IT Manager, Brethertons LLP, comments:

 

“The project has improved the efficiency of one of our most used spreadsheets . The process was people-focused and Tiger engaged with our team from day one. As a result, the spreadsheet delivers the functionality that our staff require and has helped improve the service that our Probate department offers to clients. Working with Tiger has helped us to use spreadsheets to push the firm forward in a cost-effective fashion.”

 

Excel Spreadsheet Development Projects

The project with Brethertons LLP is an example of how effective Excel development can help small-to-medium-sized enterprises improve the efficiency of business processes and deliver better service to clients. For more information about Excel development, 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

Cool Excel Formatting: Font Size and Colour

Learn how to adjust font size and colour in Excel to create a professional look and make important information stand out. Practise! Download the file used in the video here.

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

Rank Things and Create A League Table in Excel

Intro Graphic

Not in a reading mood? Watch a VIDEO of this article HERE

 

  • This article will show you how to rank things and create a league table in Excel
  • The example we look at relates to ranking students on the basis of their grades
  • The formulae we cover are =Rank(), =Offset(), =Match(), in addition to simple arithmetic formulae

 

Download the ‘Ranking things and Creating League Tables in Excel’ exercise file by clicking here.

Ranking things and creating league tables in Excel

This topic covers something that we are asked to do on a regular basis: rank things in Excel (usually sports teams or students) according to grades or points accrued. The formulae required are =rank(), =match() and =offset(). The technique takes time to master, but will save you time, energy, and will enable you to create effective data summaries.

What is wrong with simply ‘sorting’ data?

Of course, it is possible to use Excel’s ‘sort’ function to perform this kind of task; the drawback, however, is that multiple mouse clicks and scrolls are required. This is onerous and, with the required knowledge, there is a better way. How can we create a table that works without any effort on the part of the user – one that dynamically updates? Let’s take a look.

Technique 1: Implement a Basic =rank() formula

We recommend that you practise the following techniques yourself.

Download the ‘Ranking things and Creating League Tables in Excel’ exercise file by clicking here.

The first step is to implement a simple =rank() formula. This will look at a student grade and tell us where this grade lies in a list of grades. In other words, it will ‘rank’ the student in relation to other students.

Simple Rank Graphic

Excel will ask us for three elements to complete the =rank() formula: the number that we want to rank, the range of numbers that we want to rank the number in, and whether we want to rank in descending or ascending order.

The final formula will appear as in the graphic on the right. Remember to use the F4 key to fix the references for the second component of the formula, the range of values. When you are happy with the formula, you can click the square in the bottom-right corner of the cell, hold and drag down to copy and paste the formula down the column.

Our example refers to 10 students, but you could easily implement this formula for 100 or 1000. Cool! Now, can we use the ranking information to create a league table?

Technique 2: Use =match() and =offset() formulae to create league table

When we combine formulae together, we begin to unleash Excel’s awesome computational power. An excellent example is combining =match() and =offset() with =rank() to create a league table.

Match Graphic

Offset Graphic

Learn this technique in the practice file. Download the ‘Ranking things and Creating League Tables in Excel’ exercise file by clicking here.

The =match() formula tells is where a value appears in a list of values. Remember the =rank() formula that we have already implemented? By pointing our =match() formula to the column that contains the =rank() formula we can understand how far down the column each ranking (1,2,3) appears.

The =offset() formula looks at an anchor cell and moves away, or ‘offsets’ from that cell a certain number of cells. It then displays the value of the cell it arrives at. For each ranking (1,2,3), we can use offset to display the student at that rank by referring to the match formula we have already created.

It takes some attention to detail to build these formulae effectively. We have to make sure that the correct cell references are ‘fixed’ using the F4 key, for example. However, working at this level of precision is a skill that comes with practice, and will not come without practice. We highly recommend downloading the practice file; it is cool when it works, and – be warned – it can be addictive!

Technique 3: Deal with Equal Values

Let’s recap. We have created the =rank() formula to tell us where a student’s grade lies in relation to the other grades. Using this information, we have used =match() and =offset() to retrieve student information in order of rank. We have created a dynamic league table. Neat!

Learn this technique in the practice file. Download the ‘Ranking things and Creating League Tables in Excel’ exercise file by clicking here.

Equal Values Graphic

In order to make the table robust, we have to deal with equal values. What do we mean by ‘equal values’? In the league table, try making a student grade equal to the grade of another student. An error is created because our model cannot yet handle equal values.

To remedy this, we can create an increment to add to the grade for each student. Our increment gives the highest value to student 1 and the lowest to student 10. If you are working with sports teams, for example, you could allocate the highest increment to the team with the best goal difference. Where points are equal, the team with the highest increment would appear higher.

We make this work by including a new ‘total’ column to add the increment to the student grade. We then point our rank() formula to the ‘total’ column rather than the original grade column.

Check out the practice file to see how this works. Now we are ready to finish our league table…

Technique 4: Completing the League Table

Final League Table Graphic

We are now in a position to create our final league table that can deal with equal values.

We have created an =rank() formula that refers to the ‘total’ column which is the sum of the grade and the increment. As in technique 2, we can implement =match() and =offset() formulae to retrieve student data and create the league table.

Complete the league table in the practice file. Download the ‘Ranking things and Creating League Tables in Excel’ exercise file by clicking here.

Let’s test our league table. Try making one or more of the student grades equal – what happens? The league table should cope with equal values and rank according to the increment. Note that the table dynamically updates – this is the main advantage over using Excel’s sort function. Excessive mouse clicks and, in particular, scrolling increase cognitive load and make user experience unpleasant; using these formulae we have made Excel do the work for us.

We would also want to see the student grade next to the student name in the final table. As an extension, see if you can implement a vlookup formula to retrieve and display each student’s grade.

Summary Picture

Summary: What we have learned and Why it is useful

Being able to rank things and create this kind of league table is a useful skill that will enable you to create helpful data analysis. The exercise is an excellent example of how combining formulae (=rank(), =match(), =offset()) can create powerful analytical tools. We can make Excel do the work!

But why is this kind of Excel technique so important to learn?

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. =rank() is one of them, and the others are covered on this website.

Mastering these will allow you to approach data-intensive tasks with confidence.

This is the fundamental idea behind the #ExcelRevolution – that focused learning on certain 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.

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 | Comments closed