Customized Excel Applications, adapted to your needs.
L A T E S T P O S T S
Pattern Matching in Power Query
-
Download Excel App to Fill Out PDF Forms
LEN function: Basic and Advanced Examples
-
SUBSTITUTE function: Basic and Advanced Examples
-
Working with Excel Functions and Formulas
-
Can’t Open Pivot Table Source file
-
Working With a Defined Name
-
Selecting Excel Table Components
-
Order of Precedence
-
Excel Table Components
Excel CRM Tool for Personal Use
If you’re looking for a Client Relationship Management tool,
you can try the Excel CRM. The lite version has 3 modules active: Contacts, Organizations and Calendar, these are the most popular.
Download Excel CRM: Excel CRM lite.xslm
The full version has a lot more modules, as you will see on the opening page. Each module has it’s corresponding User Form:
Contacts User Form:
Linear Interpolation In Excel
Unfortunately,
Excel does not provide a function for linear interpolations. If your data table has a low granularity (you have only units, not sub – units), and you need precise results, you have to create your own linear interpolation formula. You will find in this article an excel formula, and a User Defined Function (UDF) for Linear Interpolation in Excel. Read More
Excel Table Tennis ScoreBoard
Here is a ScoreBoard View:
This one is dedicated to all Table Tennis fans π , hope they will find this Excel Table Tennis ScoreBoard useful.
I’ve built this application for our table tennis team, High-Tech, you will find us in the list of teams and players π
The Excel Table Tennis ScoreBoard will work in Excel 2007, 2010 and excel 2013, on 32-bit and 64-bit versions, not tested in Excel 2003. (forget about this version, there are many new excellent features in the newer versions) Read More
Use Excel to populate PDF Form Fields
Did you ever need to fill out multiple PDF forms faster and without mistakes?Β I’m sure that, if you’ve done this manually, on regular basis, you agree that it is tedious and time consuming.
Update:
If you have Adobe Acrobat Pro installed OR a trial version (even if it expires and you donβt want to purchase a license), then you can use an Excel App to Fill Out PDF Forms automatically, directly from Excel. Read the article and download the tool from here: excel-app-to-fill-out-pdf-forms
If you prefer the manual method, then continue reading this page for an alternative method.
Pattern Matching in Power Query

In Power Query there is no tool yet for matching regular expressions (patterns).
Only in Power BI we can run scripts in R and Python, hopefully these languages will be added to Excel Power Query.
Until then, to solve this problem, with a little imagination, we can design our own pattern matching process.
Here is a file you can download and test:
Read MoreDownload Excel App to Fill Out PDF Forms
Excel App to Fill Out PDF Forms
In an older article: Use Excel to populate PDF Form Fields, I presented a manual method to transport data between Excel and PDF forms, that can be used without a paid version of Adobe Acrobat.
If you have Adobe Acrobat Pro installed OR a trial version (even if it expires and you don’t want to purchase a license), then you can use an Excel App to Fill Out PDF Forms automatically, directly from Excel. Read More
LEN function: Basic and Advanced Examples

LEN function type: Text
Description: Returns the number of characters in a text string
LEN is a basic function widely used, many formulas built for text processing will need this function.
Take a look at the advanced applications examples:
Applications:
- Get the text with the lowest length from a column (first match only)
- Count how many cells within a range of cells have a text length higher than normal
SUBSTITUTE function: Basic and Advanced Examples
SUBSTITUTE function type: Text
Description: Substitutes the old text with new text in a text string
Beyond its basic use, which is fairly simple, the SUBSTITUTE function can be very useful combined with other functions, to get your desired results.
Take a look at the advanced applications examples:
Applications:
- Substitute last occurrence only
- Extract partial string after last delimiter
- Extract item from delimited text, based on item position
Working with Excel Functions and Formulas
In this article on Excel Functions and Formulas :
1. Excel Intellisense: Formula AutoComplete
2. Working with Insert Excel Functions Wizard
3. Debug Complex Formulas: The Magic F9 and Ctrl+Z
4. Display items using display triggers
Can’t Open Pivot Table Source file
Can’t Open Pivot Table Source…
Like a good Excel fan, I switched to Excel 2016 to try the new version.
While working on a Dashboard for a client, with a lot of Pivot Tables and charts in it, at some point, after moving some pivot tables and charts into other sheets, within the same workbook, I received this unexpected error message – Can’t Open Pivot Table Source:

Can’t Open Pivot Table Source file
Working With a Defined Name
What is a Defined Name?
In Excel, you have the option to assign a Name to a cell, or to a Range of cells. This name is reffered to as a Defined Name.
A Defined Name will give more flexibility than before, you can refer to it in your formulas, or chart series.
Also, the name can be located anywhere in your workbook, you don’t need to use cell or range references.
Selecting Excel Table Components
Selecting Excel Table Components is slightly different from selecting normal ranges in a worksheet, because we have 3 Excel Table Components which are special ranges: Read More
Order of Precedence
The Order of Precedence used by Excel to calculate formulas is extremely important to know when building complex formulas.
Of course, Excel will respect all mathematical rules you learned in school, there are only a few more Operators in Excel: the Range Operators, single Space Operator, Union Operator, the Concatenation Operator and the Exponentiation Operator.
Excel Table Components
What is a Defined Table?
An Excel Defined Table is a special object in Excel which adds a lot of new options and functionalities when working with data ranges. With Defined Tables, you can manipulate data more easily than before, adding power and flexibility to your applications.
To create a Defined Table, select the data range, or simply select any cell within the data range, and click the Table button from the Insert tab from Ribbon. The keyboard shortcut key is Ctrl+T to create a Defined Table.