I use Regular Expressions (or RegEx) for data validation in Excel 2010. It is extremely fast, very flexible and powerful in the sense that its logic will only ALLOW the exact data you want to be processed - everything else will be validated as invalid.
Using Regular Expressions negate the use of multiple If, Then, Else or Case statements. This article will cover validating different types of data using a simple Regular Expression Subroutine. I will also provide an IsValid Regular Expression Function and some common Regular Expression patterns for you to try out.
Full article: How to use Regular Expression for data validation in Excel 2010
When cells are not visible on a worksheet, you can watch those cells and their formulas in the Watch Window. The Watch Window makes it convenient to inspect, audit, or confirm formula calculations and results in large worksheets. By using the Watch Window, you don't need to repeatedly scroll or go to different parts of your worksheet.
Full article: How to easily navigate large worksheets with the Watch Window
If a workbook contains many sheets you can create a table of contents to make navigating to the sheets easier. This is a fantastic idea when producing a final version of a report in Excel for a customer.
Excel does not yet contain a feature that produces a table of contents, but you can create a macro to get the job done.
Full article: Automatically create a Table of Contents in Excel
Back to basics today, and a look at some Leading Best Practices for organizing your data and laying out your Excel workbooks:
- Data on one worksheet; information on another worksheet.
- No blank rows.
- Deconstruct your data.
- Format your headings.
- Sort your data.
- Columns are for fields.
Full article: Layout best practices
Scrub your data in VBA using Regular Expressions (RegExp).
A Regular Expression is a sequence of characters that create a pattern. The sequence could be something complicated like <([A-Z][A-Z0-9]*)\b[^>]*<(.*?) to something simple like \d.
Regular Expressions are very useful in VBA for working with many different scenarios of strings and introducing automation for transforming your data before loading to target databases for OLAP such as Essbase, Power Pivot or SSAS.
Full article: Scrub your data expressively
If you have ever tried to use a VLOOKUP function with two or more criteria columns, you've quickly discovered that it just wasn't built for that purpose. Fortunately, there is another function that may work as an alternative to VLOOKUP depending on what you want to return:
SUMIFS. Perform multi-column lookups when the return value is numeric.VLOOKUPandCONCATENATE. Perform multi-column lookups when the return value is a text string.
Full article: VLOOKUP on two or more criteria columns
Cells can contain two types of hyperlinks. There's the embedded kind that you create using Insert – Hyperlink and the formula kind that you create using the HYPERLINK function.
Excel does not stop you from entering a HYPERLINK formula in a cell with an embedded hyperlink. If you do, you can end up with what seems like two hyperlinks in one cell.
I say "seems like" because Excel only recognizes one. And to be even more precise, it recognizes pieces of both hyperlinks to make one.
Full article: The duality of hyperlinks
A pivot table is a great way to see a summary of sales to your customers. You can see how much they bought, and how much they spent, on each product.
But, by default, pivot tables don't show missing data. By making changes to the pivot table, you can easily spot the missing products for each customer:
- Option 1: Change the pivot table layout.
- Option 2: Show all items.
- Option 3: Show only the customers with no purchases.
Full article: Pivot Table shows customers with no purchases
When performing data analysis, we assume our values cluster around some central data point (a median). But sometimes, a few of the values fall too far from the central point – skewing the analysis. These values are called outliers.
In this example we apply the Tukey method for highlighting outliers in a data set.
Full article: Highlighting outliers in your data with the Tukey method
