
Microsoft Excel's worksheet password protection options have been known to trip people up from time to time. Mainly because there are just so many option combinations to choose from! Hopefully by the end of this article you will be comfortable with all the various restrictions you can place on other users who may be working in or viewing your spreadsheets.
Full article: The various ways to password protect Excel worksheets

The VBA Immediate Window is an awesome tool that allows you to get immediate answers about your Excel files, and quickly execute code. It is built into the Visual Basic Editor, and has many different uses that can be very helpful when writing macros, debugging code, and displaying the results of your code.
This post will explain 5 different uses for the Immediate Window:
- Get info about the Active Workbook.
- Execute a line of VBA code.
- Run a macro.
- View
Debug.Print
info. - Get or set a variable's value.
Full article: 5 ways to use the VBA Immediate window in Excel

This article describes a technique for adding images to the labels on a chart.
Also read the article's comments, for additional suggestions.
Full article: Add pictures to a chart axis

The Worksheet Object has a UsedRange
property. Normally, we should be able to use this property so that we can quickly identify the entire UsedRange
on the Worksheet without having to jump through a lot of hoops.
However, a false positive occurs when we test for data and Excel tells us that there is data when in fact there is not. This article develops a VBA function for finding the true used range of a Worksheet.
Full article: Excel Used ghost

In its simplest form Excel's GETPIVOTDATA
function enables you to extract values from a PivotTable report, but if you're like me when you first came across the GETPIVOTDATA
function you were less than pleased with the results. Understandably so, because in it's default form it's quite inflexible.
However, the benefit in using GETPIVOTDATA
, as opposed to a regular cell reference, is huge in terms of reducing your ongoing workload in maintaining your reports. The trick with leveraging GETPIVOTDATA
power is to replace the hard keyed arguments with nested formulas so the GETPIVOTDATA
formula becomes dynamic.
Full article: Excel GETPIVOTDATA function

Chart events can make it much easier for people to use your programs, and add interactivity to your charts.
In this article, you'll find out how useful chart events can be. They can extend the user interface of the programs you write, making it easy for users to identify points for your program to work on.
With a little ingenuity, you can create powerful applications. Once you've experimented with chart events, you'll think of many ways to enhance your projects.
Full article: Chart events in Microsoft Excel

One of the greatest things about Excel is that with its endless array of tools and commands it almost always offers multiple ways of achieving a certain goal.
Some Excel purists (particularly those that are familiar with Array formulas), will almost always opt in favor of formulas, whereas a pragmatic will always choose the fastest way (whatever it may be). The following example shows pragmatic and pure approaches to solving the same problem.
Full article: Pragmatism vs. purity in Excel

During one of my Excel training sessions, I was surprised to find out that the people were aware and quite proficient in using VLOOKUP
, but most of them had no idea about INDEX
/MATCH
.
Here in this blog post, I will try and compare these 2 formulas as objectively as I can in terms of popularity, ease of use, flexibility, and speed.
INDEX/MATCH
is powerful combo that has more flexibility and speed formula than the VLOOKUP
formula. If you haven't already adopted INDEX
/MATCH
as the way of life, it is time you do it.
Full article: VLOOKUP vs. INDEX/MATCH – The debate ends here!

An event occurs when certain actions are performed by the user or VBA. For example when you open a workbook the Workbook_Open
event is triggered. When you (or your VBA) saves a workbook, the Workbook_BeforeSave
event is triggered.
These events are built into Excel VBA and are executed automatically by Excel. You can write your own code which is also executed when these events occur. This code is known as an Event Handler.
In this article I'm going to look at events for worksheets and workbooks, but there are also events specific to the Excel application itself, user forms, and charts.
Full article: Workbook & worksheet events in Excel VBA