The VLOOKUP function is the most popular lookup and reference function in Excel. It is also one of the trickiest and the dreaded #N/A error message can be a common sight.
This article will look at the 6 most common reasons why your VLOOKUP is not working:
- You need an exact match.
- Lock the table reference.
- A column has been inserted.
- The table has got bigger.
VLOOKUPcannot look to its left.- Your table contains duplicates.
Full article: 6 reasons why your VLOOKUP is not working
This is an experiment in writing VBA code without using Hungarian notation.
I hated every minute of it. I hate reading it right now. I’ve struggled to pinpoint why it displeases me so, but I have a theory. It's hard to tell the difference between keywords and variables.
Full article: The great Hungarian debate
A little while ago, I made a blog post on why I think we no longer need Hungarian Notation.
This morning, the esteemed Excel expert, Dick Kusleika of Daily Dose of Excel, wrote a response post to my rant against Hungarian Notation in The great Hungarian debate. I started writing my response in the comments. But I'm terrible at brevity. So I'm going to post my response here.
Full article: A VBA coding manifesto
If you select more than one sheet in a workbook, and start typing or formatting, that data or formatting will be entered in all the selected sheets, not just the active sheet. That's a great feature – if you want to type on all the sheets, or add formatting, or whatever. But it's not so great if you don't notice that the sheets are grouped, and accidentally work on all the sheets.
It's easy to forget that you grouped a few sheets – you interrupt your work for a quick phone call, or grab another cup of coffee, and poof! That memory is gone, and the worksheet damage can begin.
A solution to this problem is to use a small VBA macro that warns you when worksheets are grouped.
Full article: Warning for grouped sheets
How do you know your workbook is accurate? This question is important because it's our responsibility to ensure that data flows properly through the workbook, with numbers that tie out, and with reports that are internally consistent.
One way to help ensure workbook accuracy is by creating a worksheet within the workbook dedicated to this task, and one that will help you perform your review more quickly. In my workbooks, I call this error check worksheet ErrorCk.
Full article: Not available
This article provides a high level overview of Modular Spreadsheet Development principles.
Modular Spreadsheet Development makes it possible to build spreadsheets exponentially faster while reducing the risk of errors and making spreadsheets much easier to understand.
This concept is not completely new, but I'm writing this article because the spreadsheet modelling world would be a much better place if it was more commonly adopted.
Full article: Modular spreadsheet development – A thought revolution
On the face of things, precision seems to always be preferable. For instance, 12.324 is more precise that 12.3. There are times, however, when precision is Not desirable, and may even not make much sense.
Formatting in Excel will make your numbers appear to be rounded. The trouble with relying on formatting is that Excel retains the precision in the background, and any calculations will be based on that precision.
Sometimes precision is the enemy. In such cases, use ROUND!
Full article: Round like a circle
Excel keeps track of the last cell you used during your current session, and uses it to determine the used range of a sheet. The last cell is the one you get to when you press Ctrl+End.
This article describes some VBA functions to get the:
- First used cell.
- Last used cell.
- Actual used range.
Full article: Get the actual used range in a spreadsheet
One of the repeating problems I've noticed recently are formulas that are not written efficiently. Efficiency means writing formulas that are quick to write, easy to update, and intuitive.
This article describes some techniques for writing efficient formulas, including:
- Absolute vs Relative references.
- Named ranges.
- Excel tables.
- Column number and row number arguments.
Full article: Writing Excel formulas efficiently
