Advanced Excel Tricks to Become an Excel Expert – Online Course

Do you want to learn the advanced Excel tricks that only the experts know?

Stand out from the crowd and be the expert in your company by learning the Excel tricks people do not teach you.

In this online course you will learn some amazing tricks to make the most of the power of Excel.

You can enroll now and receive a huge 85% discount.

Enroll Now

Advanced Excel tricks online course Continue reading

Excel VBA Evaluate Function – How to use this Secret VBA Function

The Evaluate function is a hidden secret that few Excel VBA users know. In this blog post we will explore what it does, and why you will be happy to know it.

The Excel VBA Evaluate Function converts a Microsoft Excel name to an object or a value.

The syntax for the Evaluate function looks like this;

Evaluate(Name)

The name can be a formula or the name of an object that you want to evaluate. This name must not exceed 255 characters.

What does this mean? And why is this useful?

Well let’s have a look at some typical uses of the Evaluate Function. Continue reading

6 Ways to Speed up your Excel Macros

Creating macros in Excel is a fantastic way to automate Excel processes and save loads of time. However as your macros get bigger, you may need to increase the speed that they execute.

This blog post looks at 6 ways that you can speed up your macros. You really need to know this stuff to create efficient macros in Excel.

If you are a user of Excel VBA already, then you may know all, or most of these. Please feel free to comment with the techniques that you use.

Watch the Video


Continue reading

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