Contact UsInterested 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.
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.
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.
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.)
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.
Done? Now learn more about the formulae used in this video:
Or, watch more videos on our YouTube channel.
Can you help us improve these resources? We would love to hear from you. Contact Us.
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.
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.
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.
Not in a reading mood? Watch a VIDEO of this article HERE
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.
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.
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.
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
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: 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.
Finding and Counting Something – or Some Things – in Excel
You probably know how frustrating it is to be sat in front of a spreadsheet scrolling up and down a list trying to locate a piece of data. It is perhaps more frustrating to know that Excel has the formulae to help you do this kind of thing at the click-of-a-button.
This article will help you speed up this sort of time-consuming task by implementing counting formulae.
Example – Finding A Buyer’s Name in a Prospect List
We were recently asked to do a piece of Excel development work on this theme. The client had two lists of names: one of people who had purchased tickets for an event; the other a list of ‘prospects’, or people who had been contacted about the event. The client wanted to understand if a particular person had purchased a ticket, ie. if he or she appeared in both the prospect and buyer lists. There was also a need to understand how many people had purchased tickets and establish a ‘conversion’ rate (names changed in example).
Technique 1: Use Find and Select to Locate a Name
We recommend that you practise the following techniques yourself.
Download the ‘Finding Things in Lists of Data’ exercise file by clicking here.
The simplest and also most time-consuming way to locate something in a list is to use Excel’s ‘Find and Select’ facility. It is situated under the ‘Home’ tab on the far-right in Excel 2010.
Simply click ‘Find and Select’ and enter the value or word that you are looking for. In our case, we enter the name at the top of the ‘Confirmed Buyers’ list – ‘Beth Baker’. Then click ‘Find Next’ one or more times. Excel will show you each occurrence of the word or value. Cool!
We can see than ‘Beth Baker’ appears in both the prospect and buyer lists. We have ‘converted’ this person from prospect to buyer.
Technique 2: Use the =Countif() Formula to Count Occurrences of Name in a List
We can use Excel’s =Countif() Formula to tell us if a particular word or value appears in a row or column that we specify. So we can set up a formula to ask if a name in the buyer list also appears in the prospect list.
You can practise implementing the =Countif() formulae in the ‘Finding Things in Lists of Data’ exercise file. You can download the file by clicking here.
If we type “=countif(” into a cell (without the quotation marks), prompts will appear to help us complete the formula. Yes – Excel is trying to be helpful!
But what do these prompts mean? Remember, we want to find out if a name in the buyer list appears in the prospect list. In other words, we want to find out if a certain word or value appears in a certain row or column. In Excel language, we have to specify a ‘range’ and a ‘criteria’. This is what Excel is asking for.
The formula will look something like this: =COUNTIF(D17:D266,B17)
It should return a value of 1. It is telling us that ‘Beth Baker’ appears once in the prospect list. Neat!
Now, what if we were to implement =countif() formulae for all names in the buyer list?
Technique 3: Use Multiple =Countif() Formulae to Count Occurrences of Names in a List
Let’s recap. We have tried the ‘Find and Select’ facility which offers a simple but time-consuming solution.
We have implemented a single countif formula to tell us if a particular name in the buyer list appears in the prospect list; this provides instant feedback. So, let’s try to implement a countif formula for each name in the buyer list to give us the complete picture.
You can practise this approach in the ‘Finding Things in Lists of Data’ exercise file. You can download the file by clicking here.
Implement a =countif() formula next to one of the names to find if that name appears in the other list. For example, implement a =countif() formula to tell you if Beth Baker appears in the prospect list (above). Alternatively, create a formula to show whether names in the prospect list also appear in the buyer list – this is what we have done in the example in column G. Check it out!
Copy and paste formulae down the list – simply double-click in the bottom-right corner of the cell. Now, let’s try to add up all those ‘1’s to find our prospect-buyer conversion rate…
Technique 4: Add Summary Figures
Now we are in a position to calculate summary figures; in this case – prospect-buyer conversion rate. To calculate this, we need to know:
1. The total number of prospects
2. The number of prospects converted to buyers
You can practise this approach in the ‘Finding Things in Lists of Data’ exercise file. You can download the file by clicking here.
First, use a =counta() formula to count the number of prospects. This formula counts cells that are not blank (ie. that contain something) in a specified range; we need to remember to subtract unwanted entries (ie. the text in the rows at the top). So the formula will look something like: =COUNTA(D:D)-4
Next, use a simple addition formula to total up names that appear in both lists, or converted prospects. Remember, our =countif() formulae return a value of 1 where names appear in both lists – ie. for prospects who are converted to buyers. Use =sum() to add up those ‘1’s, and remember to select the correct range.
We can now add a simple division formula to divide converted prospects by total number of prospects. Implement % formatting (Home Tab, middle) to create a percentage. This is our conversion rate. Easy!
Summary: What we have learned and Why it is useful
This is an example of an analytical task that Excel can help us with. You have used intermediate formulae including =countif() and =counta() to execute the task and find the prospect-buyer conversion rate. Your boss is happy, your colleagues impressed!
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. =countif() 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.
1. How data relates to action
How do you decide what is right for your organisation?
You call on your knowledge; the sum of your existing experience and training. You may seek the advice of your peers, employees or colleagues. Perhaps you also guided by intuition. These factors form your understanding and ultimately determine your action.
But what is missing from this process?
Every action has consequences, and these consequences can be quantified and recorded as data.
Unless it also incorporates this data, your understanding will always be fundamentally incomplete.
That all sounds fairly obvious. But many organisations still fail to use data effectively. Why?
The difficulty lies in the transformation of raw data into useful knowledge.
Intuition, advice and experience are human creations; they are expressed in language we can all understand; we can select or discard them based on relevance or quality to develop our understanding.
Data is different.
In its raw state, it cannot be readily incorporated into our understanding. Unlike intuition, advice or experience, raw data is inherently foreign to us.
Achieving a fuller understanding entails the transformation of this raw data into useful knowledge.
Ok, so how does this transformation take place?
It is both impractical and ineffective to process data manually. Anyone who has spent time poring over lists of figures will testify that the task is exhausting and time-consuming.
The insights derived from such endeavours are also fundamentally limited. Manual processing restricts the size of datasets and the potential for human error is unavoidable.
Software provides a far more efficient and reliable means to transform data into understanding, but off-the-shelf products are expensive and inherently generic in their functioning. They rarely offer insights specifically tailored to a particular organisation.
An ideal solution would be to take software already employed by your organisation and design a system in-house which caters precisely for your needs.
It turns out this is entirely possible.
It is called Excel development, and it is the key to unlocking and exploiting the deep insights contained in your organisation’s data.
It is also the engine driving the #ExcelRevolution.
2. Ignorant bliss
We are all aware that the Microsoft Office Suite has advanced capabilities that we use rarely if at all.
Excel is no different: most people make relatively limited use of its functions.
However, an important distinction exists between Excel and its co-applications: whereas the primary purpose of Word and PowerPoint is to present information, Excel is fundamentally an analytical tool.
An understandable and common mistake is to view all three Office applications as performing the same presentational function for different content: Word for written documents, PowerPoint for presentations and Excel for numerical data.
Employing this mindset – viewing Excel as kind of ‘Word but for numbers’ – actually encourages us to overlook its most powerful analytical functions.
This situation has serious implications. Our usage of Word or PowerPoint may be limited, but we are nevertheless aware of what is possible and are therefore able to develop our skills if necessary.
When it comes to Excel however, limitations in our skills are matched by limitations in our awareness: we are fundamentally unable to unlock the true power of Excel because we are not aware that this power exists.
At a personal level, this means we are probably handling data less effectively than we could be.
At an organisational level, we may also be wasting considerable sums of money on ill-suited off-the-shelf software when we could be developing more effective data analysis solutions with Excel.
At the level of the whole economy, a wide range of private and public organisations are missing out as they lose the competitive advantage to larger companies with more effective data analysis.
But are we really on the brink of an #ExcelRevolution?
3. The consequences of ignorant bliss
At this stage it is worth considering the impact of Excel’s functions – including those we weren’t even aware of.
In organisational terms, all Excel functions are not equal. Although a mastery of the basics can be very beneficial to individuals, substantial benefits at an organisational level really start to accrue when advanced Excel functions like vlookup and drop-down menus combine to enable the efficient and intuitive conversion of raw data to practical information.
We have used three graphics to communicate this idea.
The Excel Curve (p6) shows most people’s Excel proficiency is quite low.
The organisational benefits barchart (p7) shows that advanced Excel functions deliver dramatically higher benefit to the organisations than basic ones.
An intriguing picture (p8) emerges when we compare the two graphs: often Excel capabilities in an organisation drop off just at the point where organisation-level benefits really begin to rise.
It wouldn’t take much to move that Excel curve to the right. The rewards lie only just out of reach. And the really frustrating thing? Many people don’t even realise these rewards exist.
The Excel Curve (above) shows most people’s Excel proficiency is quite low.
The organisational benefits barchart (above) shows that advanced Excel functions deliver dramatically higher benefit to the organisations than basic ones.
An intriguing picture (above) emerges when we compare the two graphs: often Excel capabilities in an organisation drop off just at the point where organisation-level benefits really begin to rise.
At this point we can draw some important conclusions.
For many organisations the core problem is not a lack of technical ability, but a lack of awareness.
Once the potential of Excel is recognised, developing an organisation’s capabilities is therefore a lot easier than might be anticipated. This means that modest, incremental improvements in Excel capability can trigger exponential organisational benefits.
In the short term, these include saving money by replacing off-the-shelf software and improving data management with tailored in-house Excel development.
In the long term, handling data analysis in-house with Excel has a much more profound benefit: when empowered to perform their own data analysis, employees are exposed to a whole range of new ideas regarding their organisation; new ways of thinking are opened up which remained hidden while data was analysed ‘in the box’.
We’ve seen the potential for organisations to transform as their members explore the possibilities of Excel development to extract deep insight from their data.
4. The #ExcelRevolution
At this stage you may wonder why this isn’t already happening. If Excel development is so significant, why hasn’t this #ExcelRevolution already begun?
Lets take a look a some of the players we’re dealing with here.
First up: data specialists working for major corporations. These guys certainly know their way round Excel. They do great work and they are well paid for what is perceived to be a highly specialised skill set. So it is not really in their interest to propagate the notion that Excel development is actually a practical tool for non-specialists at all levels.
Next, software companies. As we have seen, the generic data analysis offered by their off-the-shelf products is achievable with applications developed in Excel. In fact, building applications in-house via Excel development projects actually improves data analysis by creating tailored tools and has the added fundamental advantage of empowering organisations to look outside the box and develop more creative strategies and solutions. Understandably, this is a reality that software companies aren’t particularly keen to promote.
So what about Microsoft itself? Surely it is missing a trick by not marketing Excel’s analytical functions?
Actually, Microsoft IS trying to get the word out – a glance at their website confirms that they offer materials specifically targeting analysis with Excel. But when was the last time you swung by the Microsoft Office website? Why indeed would you? If it really wanted to engage with organisations across the economy, Microsoft would have to invest a great deal to reach them. The fact is that for Microsoft, the major corporations have to be the key focus. It is here that the long-term software service contracts are signed which constitute the company’s main revenue stream.
So let’s look over the facts.
An established order has held sway over a set of resources which could be disseminated to the benefit of all kinds of organisations. When these resources are utilised the advantages are felt in both the long and short term at all levels of an organisation.
These organisations already have the ability to use these resources, they simply aren’t aware that they exist. And there are some influential players working to keep that knowledge out of the hands of the majority.
But given a nudge in the right direction, organisations can and do employ effective Excel development to unlock the insights hidden in their data, creating a sense of momentum that can transform individuals and organisations, and could ultimately contribute to a re-balancing of the economy away from big corporations and toward smaller organisations.
The #ExcelRevolution starts here.
Listen to a dialogue between a typical Excel user and Chris from Tiger Spreadsheet Solutions. This is an introduction to our #ExcelRevolution manifesto. Read more here.
Learn how to combine MATCH and OFFSET to retrieve data from a table and save time and effort. Practise! Download the file used in the video here.
Learn how to instantly extract data from a table to save time and stress with the VLOOKUP formula. Practise! Download the file used in the video here.
Excel Spreadsheet Development in the Third Sector: Case Study
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:
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:
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:
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:
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.