PivotTables are one of the most useful tools in Excel. They allow you to easily summarise, examine and present a complex list of data.

This blog post explores 5 advanced PivotTable techniques:

• Grouping fields by month and year.
• Calculating data as a percentage of the total.
• Using Slicers.
• Applying Conditional Formatting to PivotTable data.
• Creating calculated fields.

The scope of a variable in Excel VBA determines where that variable may be used. You determine the scope of a variable when you declare it. There are three scoping levels: procedure level, module level, and public module level.

This article describes VBA variable scope, including examples.

How do you know when a user has entered a value into a formula cell, essentially overriding your formula?

Starting with Excel 2013, we can use conditional formatting with the new ISFORMULA function to highlight when this happens.

This post shows a simple technique that will vastly reduce the number of errors in your VBA code.

The simple technique is the Assertion statement. It is simple to use and implement and will provide dramatic results. However don't be fooled by the simplicity of Debug.Assert. Used correctly it is an incredibly powerful way of detecting errors in your code.

Today we look at Excel's built-in feature that flags inconsistent formulas, and see how that feature can call attention to potentially critical information lurking beneath the surface.

Excel's way of telling you that the formula underneath a cell is not like the others is to display a small green triangle in the upper left-hand corner of the cell.

Is there any way to make a shape stick to a point in a scatterplot, not an absolute location on the chart?

Of course. There is actually a pretty simple way to make the shapes stick to the points. This article shows you how.

By default, Excel (and most other spreadsheet systems too) return approximate matches when using the VLOOKUP function.

There is a benefit in using the approximate match, it is quicker (as binary search is used) but for small spreadsheets the difference is negligible.

The interesting thing is that there are risks associated with using the approximate match. Because it uses binary search it requires the search range to be sorted. If not, funky things can happen.

The COUNTIF function is one of the most useful functions in Excel. Its job is to provide conditional counting. This is primarily used for analysing data and producing reports and dashboards.

In this blog post we look into 5 more unorthodox but useful scenarios for the COUNTIF function to be used. The 5 COUNTIF examples we look at are:

• Prevent duplicates in a range.
• Uniquely rank items in a list.
• Count the unique entries in a list.
• Compare two lists.
• Identify names that occur 3 times or more.