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.


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.


Count the number of fridays

Continue reading

Excel Conditional Formatting with Charts – Two Examples

You may already be familiar with the Conditional Formatting tool in Excel. The amazing tool that improves how we view and visualise our data.

Well unfortunately Excel does not yet have a Conditional Formatting feature that we can apply to charts. I have great news though. There is a way that we can do it, and it is not difficult.

This blog post will look at two examples of Conditional Formatting with charts so that you get a feel for how to do it. You can then apply the same technique to whatever example you need.

Highlight a Column Based on User Selection

In the first example shown in the image below. We want to be able to highlight the column in each chart that corresponds to the product type selected by the user in cell B3.

Excel charts with conditionally highlighted columns

Continue reading

Count Cells by Colour – Excel VBA Function

If you have ever tried to count cells by colour in Excel, you may have noticed that Excel does not contain a function to accomplish this.

We can find out how many cells by filtering the list by colour, and maybe use the AGGREGATE function to return the count. This is an option. But having to filter the list each time may not be good enough.

Because functions such as COUNTIF cannot count by cell colour, we will need to create our own custom function (also known as User Defined Functions or UDF’s) to get the job done. Continue reading

Save Time by Using Project Templates in MS Project

If you find yourself creating projects on a regular basis, you should consider creating your own project template, or customising the default MS Project template. It is easy to do and will save you loads of time in the long run.

This tutorial will begin by showing you what a template consists of, and how to save one. This approach is best if you require multiple templates. For example, specific templates for specific clients or specific templates for specific types of job.

This tutorial will then show you how you can customise the default MS Project template. This will be the best option if you just need one template for all your projects.
Continue reading

Create a Rolling Chart for Last 6 Months

When creating reports in Excel, a common requirement is to report on a rolling basis. For example, this could mean the last 12 months, the last 6 weeks or the last 7 days.

Whatever the timeframe being reported, this can mean a lot of time editing chart sources and formulas to show the right data.

This blog post looks at creating a dynamic rolling chart to show the last 6 months of data, so when new data is added to the table, the chart automatically updates to report the last 6 rows (months).

Rolling chart showing last 6 months sales

Continue reading

Excel Formula to Display the Sheet Name in a Cell

This blog post looks at using an Excel formula to display the sheet name in a cell. By finding the sheet name using an Excel formula, it ensures that if the sheet name is changed, the formula returns the new sheet name.

For the formula we will be using the CELL, MID and FIND functions. Let’s begin by looking at the CELL function.

The CELL function is a fantastic, and relatively unknown, function in Excel. Its purpose is to return information about a cell such as its column, cell colour, whether it is in a protected state or the filename. Continue reading

Euro 2016 Planner – Excel Template

Euro 2016 is here and I have a Euro 2016 planner to share. The spreadsheet has a separate sheet for the group stage and the knockout stage. Enter the results into the boxes on the sheets and everything is calculated automatically.

The group league tables are ranked as the results are entered. As soon as a groups fixtures have been completed, the knockout schedule is created. The quarters, semi-final and final fixtures are then calculated as these results are entered.

The spreadsheet is unlocked so you are free to check out the formulas and how it all works. Download the Euro 2016 planner spreadsheet.

Euro 2016 Planner Template
Continue reading