Excel SWITCH Function – Nested IF Function Alternative

The SWITCH function was introduced in Excel 2016 (sorry not available in previous versions) and is a great alternative to nested IF functions.

It is a useful substitute and a real timesaver compared to complex nested IF formulas. It is more compact and easier to read.

Watch the Video

Continue reading

Excel VBA: Select Case Statement with Examples

The Select Case statement can offer a neater and more concise alternative to nested If Then Else statements in Excel VBA.

So when you are performing complex logical tests, the Select Case statement can break it down into more logical chunks of code. This then makes it easier to write, read and understand what the code does, which everyone will appreciate.

In this tutorial we will look at a few examples to show the different ways that the Select Case statement can be written. Continue reading

Select/Deselect all Checkboxes with a Single Checkbox

Add a single checkbox to select, or deselect, all of the checkboxes on a worksheet with one click.

If you have a large list of checkboxes, selecting all of the boxes, or removing the checks from all the boxes can be very time consuming.

This blog post will provide the code to select/deselect all the checkboxes on a worksheet using a single checkbox, and explain how it works.

Watch the Video

Continue reading

How to Create a Reverse FIND Formula

In this blog post we create a reverse FIND formula to extract text after the last occurrence of a character.

Many of you reading this may already be familiar with the FIND function. You would probably have used it with LEFT or MID to locate a delimiter character, and return text before, or after that character.

In this tutorial we want to extract text after the last occurrence of a character, so want to create a reverse find effect.

Watch the Video

Continue reading

Automatically Refresh PivotTable – Excel VBA

Excel PivotTables do not refresh automatically when the data source is updated.

Now, you can refresh all of the PivotTables in your workbook with just 2 or 3 clicks. But this is not something you need to do when you work with formulas, charts or Conditional Formatting. So you might forget, or just find it irritating to do it regularly.

This tutorial walks you through the VBA code to refresh the PivotTable automatically.

New to VBA and want to learn quickly? Sign up to our online Excel VBA course.

Watch the Video

Continue reading

The GETPIVOTDATA Function in Excel

The GETPIVOTDATA function is used to query and extract data from a PivotTable. It is essential a PivotTable lookup formula.

This function can be extremely useful. When your PivotTable updates and may grow or reduce in size, or the field items may change order, GETPIVOTDATA will continue to extract the correct data.

In this blog post we will show why the GETPIVOTDATA function is useful with an example, but then show an example when we do not want it and how to turn the feature off. Continue reading

4 Alternatives to Nested IF Formulas

Nested IF formulas are extremely useful for complex decision making on a spreadsheet, but they can also be long, messy and convoluted.

This blog post explores 4 alternatives which are easier, faster and cleaner than the classic nested IF.

Watch the Video

Continue reading

Excel Char Function with Examples

The CHAR function of Excel returns a character specified by a code, from the character set of your computer.

In this blog post we look at why you might want to use the CHAR function, and some examples of it in action in Excel.

The CHAR function is typically used to insert, or to find, a character which you ordinarily cannot type from your keyboard.

To use it you will need to find out what the code is for the character you want to use.

You can search online for a full list of these character sets so that you know each code (or at least the one you need at the given time). These character sets differ between the Windows and Mac computers, and are also dependent upon the language used. Continue reading

Excel VBA Progress Bar on Userform

In this blog post we look at how to create a custom progress bar on a userform using Excel VBA, so that we can see the progress of our macros as they are running.

The progress bar will look like the one below. The fonts and colours used can be customised to your own liking.

Create a progress bar in Excel VBA

There is a video at the bottom of this tutorial if you prefer to learn that way. Otherwise lets crack on and create this progress bar.

Download the completed file from this tutorial. Continue reading