Creating Multiple Dependent Drop Down Lists

Drop down lists can be created in Excel to assist with data entry and to prevent potential typos and misspellings. The Data Validation tool in Excel can be used to easily create lists.

However if you have many entries, you may need to break the list into multiple dependent drop down lists. By creating a cascading set of lists, the entry in one is dependent upon the selection made in another.

Continue reading

Ranking Fields of a PivotTable in Excel 2010

In Excel 2010 a feature was introduced to easily rank the fields of your PivotTables.

Yes, you can sort the fields of a PivotTable to view items in order from largest to smallest, or smallest to largest depending on what you are trying to achieve. But you may wish to keep your list of products, customers, salespersons or whatever the field is your are ranking in alphabetical order.

Take the table below for instance. This PivotTable displays the number of sales and conversion rate for the members of a sales team.

Sales team pivottable

We would like to add a rank field to the PivotTable for both fields.

Continue reading

Create Sports League Tables and Tournaments in Excel Online Course

This week I am pleased to announce the release of a new online course on How to Create Sports League Tables and Tournaments in Excel.

I have blogged and created videos in the past demonstrating spreadsheets that calculate team rankings and generate cup draws etc. I get many, many messages about these posts so have been planning for a while to put this course together.

The course is now open for enrolments. In the course you will learn:

  • How to automatically calculate league table rankings for players or teams as results are entered.
  • How to automatically create a random cup draw for knockout tournaments.
  • Different formulas to calculate various sports statistics and to determine match results.
  • How to calculate different cricket statistics such as net run rate, bowling strike rate, number of balls bowled and more.

You can join this course now using the link below for just $19.

Continue reading

Create a Picture Lookup in Excel

Many Excel users are familiar with lookup functions such as VLOOKUP, INDEX and MATCH to look up information in a list. But how about performing a picture lookup to return a picture dependent upon the contents of a cell.

This requires a little extra thought as a standard VLOOKUP is not capable of returning a picture from a list.

In this blog post we will explore how to create a picture lookup. We will look at how to return the picture of a flag dependent upon the country name that is selected from a list.

Continue reading

Add Your Own Function to the Status Bar in Excel

When a range of cells are selected, Excel automatically displays formula results on the status bar. This can be an incredibly useful feature of Excel, especially when combined with filtering data.

Excel can display the sum, average, max, min, count of cells containing values and count of cells that are not blank in the status bar.

Excel displaying formulas in status bar

This blog post looks at how you can get Excel to display your own function on the status bar. For example, you may need to know the median or mode value, or count the number of blanks in the selected range.

In this post we will get Excel to display the number of blank cells in the selected range on the status bar.

Continue reading

World Cup 2014 Flags Quiz

With the FIFA World Cup 2014 in full swing. Today I have another World Cup related post to share with you.

Are you good at recognising the flags of different countries? Well here is your chance to impress. This spreadsheet contains the flags of all 32 countries participating in the FIFA World Cup 2014. Your task is to name them.

Download the World Cup Flags Quiz spreadsheet.

World Cup flags quiz spreadsheet

Continue reading

FIFA World Cup History – Excel Dashboard

On the eve of the FIFA World Cup 2014 I have created a world cup history dashboard full of fascinating statistics from the previous 19 world cups.

I find creating spreadsheets involving your passions a great way of developing and testing your Excel skills

This dashboard incorporates a few different Excel dashboard techniques which are explained in this post.

The spreadsheet is completely unprotected so all the formulas and code can be explored.

Continue reading

Animate a Chart in Excel

Animate a chart in Excel to create a cool effect for your charts. You will see the chart build itself in front of your very eyes.

The chart can be created in the usual way and then VBA is used to create the animation effect.

In this example a combo box is used to provide a way for the user to select the chart they want to see. The chart then gradually appears one data point at a time.

Continue reading

Create a Hangman Game in Excel

I created a hangman game using Excel VBA for fun, and thought I would post it here for all budding Excel VBA learners to look at.

I find creating games, quizzes and sports spreadsheets an enjoyable way of keeping my Excel skills fresh, and to develop them further.

The game asks you to guess a letter to complete the phrase. A hint is provided for a little help, and the phrase will be related to music, sports, people, film or geography.

The spreadsheet is unprotected so you can check out the code and play around it with it further.

The VBA used to create this hangman game includes;

  • Dynamic arrays.
  • Creating a userform.
  • Module scoped variables.
  • Lots of text work including functions such as Left and StrConv.
  • Lots of For Loops, IF statements and a Select Case statement.

Continue reading

Separate Text into Different Cells

A common requirement in Excel is the need to separate text into different cells. The reasons for this are numerous, but typically it is because the way the data was imported or received is not sufficient for your analytical needs.

This article looks at four techniques for separating text. Use the links below to jump to a specific technique.

Continue reading