Count Unique Values in a PivotTable

When using a PivotTable to summarise a large list of data, you may need to count unique occurrences in a particular field.

For example, you may have a list of data like the one below and want to count the number of different customers in each country, or the number of different products that were sold in each country.

List of data to count uniques

If you added the Customer Name field to the Values area of a PivotTable and summarised it with a Count you would get the result below.

Counting values in a PivotTable

However this is counting all of the transactions in each country. We need to count the unique customers making those transactions in each country.

Continue reading

Format Specific Words in a Cell using VBA

In this blog post we look at how to create a macro that will look in a list and format all the instances of specific words that appear in a cell.

This macro is case sensitive and will also only apply the formatting if it is the whole word. For example, if you are formatting all instances of the word ‘red’, this macro will not format it if used in words like ‘reddish’ and ‘sacred’.

Continue reading

Advanced Techniques for Summing Values

Using the SUM function to total a column of values is an essential skill in Excel. However, it is not always that straightforward. The column of values may contain errors, or the values you want to sum may be on many different sheets.

This article looks at 4 advanced techniques for summing values including;

  1. Summing a range that contains errors
  2. Creating a cumulative total
  3. A consolidated sum from multiple sheets
  4. Summing Every Nth Value in a Range

Continue reading

Formatting Mail Merge Fields from Excel

When performing a mail merge from Excel to Word, your mail merge fields tend to lose their Excel formatting. This is especially common with date, time and currency fields.

This tutorial will look at how you can correctly format date, time and currency mail merge fields from Excel. Once the formatting is applied to the mail merge document it will be remembered for future use.

Continue reading

Create a Battery Chart in Excel

Create a battery chart in Excel just like you see on your smartphone, tablet device or even PC. It is a great visual way of viewing the percentage remaining of a value.

In Excel a battery chart could be used to view the amount remaining of a budget, the work remaining on a task, or number of people still to attend training.

There are many reasons why you may want to visualise an amount dropping until it is empty, or complete. A battery chart provides a method that people can relate to easily.

The image below shows a battery chart being used to show the amount outstanding on a training budget.

Battery chart in Excel

Continue reading

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

Create a Looping PowerPoint Introduction

This blog post looks at how you can create a looping PowerPoint introduction. This is when a few select slides are set to loop continuously before the main presentation begins.

It provides something for the audience to watch whilst they are gathering in the room. It can be a useful technique for creating a buzz, and setting the expectations for what is to follow.

To do this we will need to create two custom shows. One for the looping introduction, and another containing the main body of the presentation. We will then need to link the two shows so that the presenter can easily exit the loop and begin the presentation when needed. Continue reading