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
VLOOKUPfor an exact match.
VLOOKUPfor a range lookup.
- The fantastic
Full article: 4 alternatives to nested IF formulas
When it comes to VBA, it's almost too easy to make a mistake. These mistakes can cost you greatly, both in time and in frustration.
In this post, I'd like to help you avoid these typical mistakes and make you a better VBA programmer:
- Using the
- Not using
Application.ScreenUpdating = False.
- Referencing the worksheet name with a string.
- Not fully qualifying your range references.
- Making your
- Going down the nested
Full article: 7 common VBA mistakes to avoid
Learn why using an Excel Table as the source of a Pivot Table can save time and prevent errors.
The reasons why you should use Tables for the source data range of your Pivot Tables are:
- Adding new data & preventing embarrassment.
- Eliminate maintenance on multiple Pivot Tables.
- Prevent errors when creating Pivot Tables.
- Avoid whole column references.
- Prevent the Filter controls error with connected Slicers.
Q. Is there a list of standard Excel design rules we should be following as we create new Excel worksheets?
A. By following a common set of spreadsheet design rules, companies can produce more consistent workbooks that may be easier to review, edit, and use by others in their organizations:
- Table of contents.
- Print macro buttons.
- Avoid embedded assumptions.
- Well-organized worksheet assumptions.
- Assumptions in yellow cells.
- Name assumption cells.
- Error-checking formulas.
- Organize your template by worksheets.
- Simplify complex calculations.
- Consistent look and feel.
- Add File Properties.
- Cross-footing and error-checking formulas.
- Worksheet protection.
Full article: Microsoft Excel: Rules for designing Excel workbooks
The Wells Fargo inadvertent disclosure episode provides a high-profile reminder that attorneys who are responsible for reviewing and producing client documents must thoroughly understand those documents.
As reported by the New York Times, the inadvertently produced material "included copious spreadsheets with customers' names and Social Security numbers, paired with financial details like the size of their investment portfolios and the fees the bank charged them."
Here is a list of skills that I think every reviewer must have to competently review spreadsheets in the eDiscovery context:
- Reviewing the entire workbook, not just the current tab.
- Understanding Filters.
- Identifying hidden rows, columns, and worksheets.
- Identifying & deciphering formulas.
- Understand the Freeze Panes display feature.
- Finding and expanding truncated text.
- Identify the boundaries of a worksheet.
- Finding each worksheet's print formatting.
A worksheet in Excel is made up of cells. These cells can be referenced by specifying the row value and the column value.
The power of Excel lies in the fact that you can use these cell reference in other cells when creating formulas.
There are three kinds of cell references in Excel:
- Relative cell references.
- Absolute cell references.
- Mixed cell references.
Understanding these different type of cell references will help you work with formulas and save time (especially when copy pasting formulas).
Microsoft quietly replaced the comfortable Text Import Wizard from Excel and replaced it with the "Get & Transform" tools.
The "Get & Transform" tools offer a lot of options and are very powerful. Unfortunately, they are quite complicated to use. Here is what you should now.
Topics in this article include:
- Restore the "Text Import Wizard".
- How to use the "Text Import Wizard".
- Import text and csv files with the "Get & Transform" tools.
This tutorial explains what an Excel name is and shows how to define a name for a cell, range, constant or formula. You will also learn how to edit, filter and delete defined names in Excel.
- Excel name - the basics.
- How to name a range in Excel.
- How to create a named constant.
- How to make a named formula.
- How to name columns and rows (create names from selection).
- How to create a dynamic named range in Excel.
- Excel name rules.
- Scope of Excel names.
- Excel Name Manager.
- Top 5 benefits of using names in Excel.
- Excel named range - tips and tricks.
Full article: Excel named range - How to define and use names in Excel
Looking around, a few things are apparent:
- Everyone is still using Excel.
- Spreadsheets still contain errors.
- Graduates still don't have the requisite spreadsheet skills upon entering the workforce.
- Companies still aren't regularly enforcing good model governance.
This article (see page 7) looks at how we can address the issues and make spreadsheets more user-friendly and faster to use, less prone to error and more capable of completing the tasks required of them.
Full article: Excel and modeling governance: What can we do better?