Whether you're a fresh-faced analyst new to programming, or a jaded veteran living on sunflower seeds and Mountain Dew, these tips will make programming with Excel and Access so much easier:

• Block comments.
• Quick copy blocks of code.
• Jumping between modules and procedures.
• Jumping directly to your functions/variables.
• Staying in right procedure.
• Dragging the yellow arrow.
• See the beginning and end of variable values.
• Turning off Auto Syntax Check.

If you have sluggish Excel models, it might not just be the file size that is slowing things down. Often it's the memory being used and when things get really bad, you might even get a "Not enough Memory" or "Not enough system resources to display completely" error message.

When you get this frustrating message, the only solution is to close Excel and restart it, so let's look at some ways to improve the performance of your Excel models.

For a data analyst, Excel Tables are a necessity! They are the most efficient way to organize your raw data and refer to data that contracts/expands on a regular basis.

Likewise, Excel tables can be extremely useful in combination with VBA. I personally use data tables as a way to store user settings without having to modify any VBA code.

In this article I wanted to bring all the common ways of referencing table data with VBA into one place.

If you have experienced the mishap of mistakenly closing a file while working on it or losing it due to a power failure or a gremlin in your computer, you'll know how frustrating it can be. Fortunately, changes were made in Microsoft Excel 2010 and 2013 which make the process of restoring workbooks simpler.

Here's a nifty little trick I use every day while working in Excel. It's a two part trick. Part one will be selecting all the blank cells in a range or rather reducing a selection or a range to blank cells within that range. Part two will be how to enter a value or a formula(!) into multiple cells in Excel simultaneously.

Many Excel experts believe that pivot tables are the single most powerful tool in Excel. According to Bill Jelen (a.k.a. Mr. Excel) "No other tool in Excel gives you the flexibility and analytical power of a pivot table". I agree. This article is a collection of important things you should know about pivot tables.

Many people have the idea that building a pivot table is complicated and time-consuming, but it's simply not true. Compared to the time it would take you to build an equivalent report manually, pivot tables are incredibly fast. If you have well-organized source data, you can create a pivot table less than a minute.

One of the first warnings an aspiring analyst hears is, "never take an average of an average". The reason for this warning may not be intuitive, but it's important to understand why it's generally a bad thing to average a bunch of averages.

In today's post, I'll attempt to explain the problems with using simple averages and how Weighted Averages can help you avoid inaccurate conclusions about your data.

Data forms the backbone of any analysis that you do using Excel. And there are thousands of things that can go wrong with the data – be it the structure, extra spaces, wrong placement, different formats, and so on...

In this blog post, I will show you 10 simple ways to clean data in Excel:

• Getting rid of extra spaces.
• Select and treat all blank cells.
• Converts numbers stored as text into numbers.
• Remove duplicates.
• Highlight errors.
• Change text to lower/upper/proper case.
• Use Text to Columns to parse data in Excel.
• Spell check.
• Deleting all formatting.
• Use Find and Replace to clean data in Excel.