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.
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.
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.
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.
A common question from Excel analysts and enthusiasts on my courses is to count the number of unique entries in a list.
This post looks at using a formula to calculate this distinct count.
Consider the list below of a list of delegates attending our courses. A normal count on this range will tell us how many attendances there were. That’s good, but we want to know how many unique attendees there were.
At some point you may need to sum the value from every nth row in a large list. Excel does not provide a function for this. Excel has a few different Sum functions, but not one to sum the value from every other, or every third, fourth, or fifth row in a list.
The spreadsheet below contains totals in every fifth row starting from row 3. We want to only add these sales totals.
Creating a scrollable chart is a great trick for enhancing the functionality of your Excel dashboards. You may have years of data to display in the chart, and yet space is limited on your spreadsheet.
By adding a scroll bar to the chart, users can interact with the chart and scroll to see the data they want displayed.
A Histogram is used in statistics to graphically represent the distribution of data. It looks like a column chart with each column representing an interval (bin), and the column height representing the frequency that it appears.
Essentially the graph groups numbers into intervals (bins) and displays how often they appear. The graph then beautifully illustrates how the sets of numbers are distributed.
If a workbook contains many sheets you can create a table of contents to make navigating to the sheets easier. This is a fantastic idea when producing a final version of a report in Excel for a customer.
Excel does not yet contain a feature that produces a table of contents, but you can create a macro to get the job done.