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

10 Excel Paste Special Tricks to Make you a Pro

To be an Excel pro you must be a master of Paste Special.

Copy and paste is one of the most common tasks a computer user performs daily. Paste Special in Excel allows you to specify what elements of the copied item get pasted. It also allows you to perform operations on values and other cool stuff.

This blog post explores 10 Excel Paste Special tricks that an Excel pro needs to know. Continue reading

Use a Doughnut Chart to Measure Progress to a Goal

A Doughnut chart is a very popular way to measure progress towards a goal or target. They are simple to understand, appealing to the eye and familiar to users.

You see Doughnut charts being used to show progress in the news, in business reports and even when your computer is loading or refreshing.

Well the good news is that they are also extremely easy to create.

Watch the Video

Continue reading

5 Reasons Why your Excel Formula is Not Calculating

When your Excel formulas are not calculating, or not updating, it can be very frustrating. Your formulas are the driving force for your spreadsheet.

There are 5 reasons for your Excel formula not calculating are many. In this tutorial we explain these scenarios.

Watch the Video

Continue reading

Create a Geographic Heat Map in Excel – FREE Add-in

In this blog post we create a geographic heat map in Excel using a cool free Excel add-in. This add-in is extremely easy to use, meaning you can have an awesome looking geographic heat map on your Excel report in minutes.

The image below shows the data being used for this example. I have a list of countries and a number of online training subscribers.

This data has been plotted on an image of a world map using a red to green colour scheme.

Create a geographic heat map in Excel
Continue reading