Count How Many Times a Specific Character Appears – Excel Formula

In this blog post we will uncover an Excel formula to count how many times a specific character appears in a cell.

The formula used in this tutorial will work for any text character, and can also be used to count the occurrences of specific words in a cell.

In this example, we used the formula to count the occurrences of the asterisk in the cells of column A.
Count occurrences of a specific character
Continue reading

Create an Interactive Checklist in Excel

In this blog post we look at creating an interactive checklist in Excel. The checklist will automatically mark the items in a list when they are checked.

To do this we will first need to insert checkboxes onto the spreadsheet, we then need to be able to highlight an item when it is completed.

In this tutorial I am using the idea of a Christmas shopping list of names (shown below). Your checklist could however be for any list of tasks, inventory or products.
Interactive checklist in Excel Continue reading

Use Own Icon for Macro Button on Ribbon

In this blog post we look at how you can use your own icon or image for the button of your macro on the Ribbon. This could be an image that you have created, or one that you downloaded from a website.

By using our own icons we can create customised workbooks and not be constrained by the standard Microsoft icon set provided.

In this example I have a macro that exports all the worksheets of a workbook to PDF, and I want to assign it to the icon shown below. This icon was downloaded from flaticon.com, a fantastic resource for icons that you can use for your macros.
Custom icon for macro button Continue reading

Conditional Formatting with Mail Merge Fields in Word

You may be familiar with Conditional Formatting in Excel. Well, Conditional Formatting can also be applied to fields in Word.

Now, Word does not have a Conditional Formatting button like in Excel, but you can write an IF function in the field and format the true and false responses. This will create the perfect Conditional Formatting behaviour.

In this example, Mail Merge is being used to inform members of how many points they have earned this week. If the number of points is greater than 20, I want the number formatted in red, and if not formatted in black.

The name of the Mail Merge field we need to edit is number. Continue reading

Excel Formula to Extract Postcode from an Address

I was asked recently in class how to extract a postcode from an address in the UK. The person asking needed a formula because the spreadsheet updates often and they wanted an automated solution.

The problem with extracting UK postcodes is that they are highly irregular. They will be at the end of the full address and can come in a different number of characters e.g. E1 6AX, RM3 8HN and LE41 8JX.

They are not as structured as a US zip code may be and harder to extract. Because of this the formula is intense, but I am going to break it down and explain it in detail.

Extract postcode from UK address with Excel formula

Continue reading

4 Secret PowerPoint Tricks to Make you a Pro

This blog post uncovers 4 Secret PowerPoint Tricks that will help you to deliver effortless and high quality presentations. These 4 tricks are some of my personal favourites (PowerPoint has many useful tools).

These 4 tricks are easy to use, but will make Massive differences to the way you develop and deliver PowerPoint presentations.

Continue reading

Excel VBA: 6 Ways to Find the Last Row of a List

A very common task in Excel VBA is to find the last row of a list. This can be for many reasons such as to make a chart or PivotTable ranges dynamic, or to append fresh data to the bottom of a list.

This blog post uncovers 6 different ways you can find the last row. In these examples the number of the last row is assigned to a variable for use within your procedures.

Are you new to Excel VBA? Learn Excel VBA quickly with this online course.

Find the last row using Excel VBA

6 Ways to Find the Last Row of a List – Excel VBA

For these examples the following variables have been declared and the sht variable activated.

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ThisWorkbook.Worksheets("Sheet1")

Continue reading

Display a Symbol Conditionally using the IF Function

This blog post looks at using the IF function to display a symbol conditionally in a cell. In the image below a thumbs up or thumbs down symbol is shown dependent upon whether the sale of products have improved since last month.

This tutorial will show you how to display any symbol though, so you could insert a smiley face, hour glass, aeroplane and much more.

Display thumbs up symbol using IF function Continue reading

Excel Formula to Find the Least Frequent Value

This tutorial explains an Excel formula to find the least frequent value in a list. This formula will work whether the value is a number, or text. In this example we want to return the name that occurs the least.

The spreadsheet below shows a list of names with the answer in cell D2. Ross is the name that occurs the least in that list.

Excel formula for least frequent value

This formula returns the least frequent value from the list in A2:A16. The formula is explained below so keep reading.

{=INDEX(A2:A16,MATCH(MIN(COUNTIF(A2:A16,A2:A16)),COUNTIF(A2:A16,A2:A16),0))}

Continue reading

Count How Many Fridays Between Two Dates

You may need to count the occurrences of a specific weekday between two dates in Excel. For example, how many Fridays between now and a specific date.

There is no real standalone function in Excel to do this, but it can be done. This could be a useful formula to find how many payments, or how many meetings until an end date.

How Many Fridays Until a Specific Date – Excel Formula

The formula below calculates the number of Fridays between the date in cell A2 and the date in cell B2. The formula is explained below.

=B2-A2-NETWORKDAYS.INTL(A2,B2,16)+1

Count the number of fridays

Continue reading