Excel First

Excel Solutions

  • Download Excel App to Fill Out PDF Forms

    If you have Adobe Acrobat Pro installed, then you can create an Excel App to Fill Out PDF Forms automatically, directly from Excel.

    Learn More
  • LEN function: Basic and Advanced Examples

    LEN function: basic text function, widely used. Advanced examples: Count Items from a range based on text length. Return the smallest text in range.

    Learn More
  • SUBSTITUTE function: Basic and Advanced Examples

    SUBSTITUTE function can be very useful combined with other functions, to get your desired results. Applications examples: Substitute last occurrence only, Extract partial string after last delimiter, Extract item from delimited text, based on item position.

    Learn More
  • Working with Excel Functions and Formulas

    Excel Functions are built into Excel and their functionality and arguments cannot be modified by users. An Excel Formula is designed by the user and it can contain multiple Excel Functions, Defined Names, Constants, Range references.

    Learn More
  • Can’t Open Pivot Table Source file

    Pivot Table fails to refresh, because the Data Source reference is not relative anymore, it contains the file name too. If you receive the message: Can’t open Pivot Table Source file, the workbook will not work anymore as expected.

    Learn More
  • Working With 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.

    Learn More
  • Selecting Excel Table Components

    Selecting Excel Table Components is slightly different from selecting normal ranges in a worksheet, because we have 3 major Excel Table Components, which are special ranges: the Headers Row, the Data Body Range, the Totals Row.

    Learn 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.

    Learn More
  • Excel Table Components

    An Excel Defined Table is a special object in Excel which adds a lot of new options and functionalities when working with data ranges. Whith 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 shortcut key is Ctrl+T to create a Defined Table.

    Learn More
  • Excel Reference Operators

    There are 3 Reference Operators in Excel: “:” (colon) is the Range Operator, “,” (comma) is the Union Operator, ” ” (space) is the Intersection Operator.

    Learn More
  • Excel Cell Modes

    When you try to edit a conditional formatting formula, and you click the left or right arrows to move across formula’s text, Excel inserts unwanted cell references. Same thing happends when you try to edit a defined name formula, or a chart series…

    Learn More
  • Excel CRM Tool for Personal Use

    If you’re looking for a Client Relationship Management tool, and you don’t want to spend thousands on a CRM solution, then try this free Excel CRM.

    Learn More
  • Dynamic Hyperlinks in Excel

    Adding a hyperlink to a specific worksheet or cell is an easy thing to do. But what if you need the hyperlink to be dynamic, no matter if you insert or delete sheets, change the order of sheets, and you want the hyperlink to “know” which is the next or previous sheet, without manually adjusting all hyperlinks?

    Learn More
  • Use Excel to Create Mass Letters

    This article describes a solution for creating mass letters from Excel, using a Word Document as the Letter template and a Map between Excel Defined Names and Word Document Bookmarks.

    Learn More
  • Add New Controls to Excel UserForm

    In this article, you will find code for adding new controls to excel userfom, for adding items to comboboxes and to add values from a range of cells to a combobox list.

    Learn More
  • Linear Interpolation In Excel

    Excel does not provide a function for linear interpolations. If your data table has a high granularity, 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 for Linear Interpolation.

    Learn More
  • Excel Table Tennis ScoreBoard

    This Excel Table Tennis ScoreBoard is dedicated to all Table Tennis fans 🙂 , hope they will find this Excel Table Tennis ScoreBoard useful.

    Learn More
  • Autoexpand Excel Tables on Protected Sheets

    An important feature of tables, the autoexpanding functionality, is lost when the worksheet is protected. When you type anything under the last row, the table will not expand to include the new row, the decision to protect the sheet is not easy when you need both protection and autoexpanding features enabled.

    Learn More
  • Hide Excel Sheets if Macros are not Enabled

    If your workbook contains macros designed to assist users in typing data, or you have UDF – User Defined Functions for specific calculations, then if users are not enabling macros when opening the file, your codes are useless…

    Learn More
  • Excel User Form Assistant

    Excel User Forms are a great way to collect data in a specific structure. Unfortunately, there is a major limitation that you have to take into consideration when choosing between a worksheet form or a userform: there is no built-in data validation for text boxes, or comboboxes… (you can only limit the number of characters, which might not be what you wanted)

    Learn More

Customized Excel Applications, adapted to your needs.

Excel Cell Modes

How can the Cell Modes affect your work?

When you try to edit a Conditional Formatting formula, and you click the left or right arrows to move across formula’s text, Excel inserts unwanted cell references. Same thing happens when you try to create or edit a Defined Name formula, or a Chart series…

If you are not aware of the active Cell Mode, then working with formulas, defined names formulas or conditional formatting formulas, editing chart series can become very annoying.

Read More

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:

Excel CRM Contacts Form

Contacts Form

Read More

Dynamic Hyperlinks in Excel

Dynamic Hyperlinks in Excel to navigate between Worksheets

Adding a hyperlink to a specific worksheet or cell is an easy thing to do. But what if you need the hyperlink to be dynamic, no matter if you insert or delete sheets, change the order of sheets?
Or, if you start from a template sheet, and keep adding sheets, you need to have dynamic hyperlinks in the template sheet, that “knows” which is the next or previous sheet, without manually adjusting all hyperlinks.

Few things you should know:

You have multiple options to create a hyperlink (without VBA):

  1. you can add a hyperlink to a cell, by right clicking that cell and selecting Hyperlink from right click menu;
  2. add a hyperlink to a shape, by right clicking that cell and selecting Hyperlink from right click menu; (very similar to adding a hyperlink to a cell)
  3. add a hyperlink in a cell using the Excel Hyperlink Function.

Read More

Use Excel to Create Mass Letters

Summary:

This article describes how to create mass letters from Excel, using a Microsoft Word Document as the letter template and a Map between Excel Defined Names and Word Document Bookmarks.

Thoughts…

Starting from the example described in this article, there are numerous things that can be done: if we setup the same bookmarks in Multiple Word Documents, and if we use a multi-select dialog box to open and fill all those selected documents, we can create A SET of different documents for each recipient! Of course, all letters can be attached to an email and sent to that recipient email.

Most of the times,

It should be obvious you first need to create an Excel Table containing the name, physical address, email address, and other pertinent information that is to be inserted into the letter/email of all your intended recipients.

To create a letter for all recipients from your table, a Mail Merge will be all you need. But there are many scenarios where you need flexibility:

  •  you may want to create a letter for only 1 recipient, without going through all Mail Merge steps;
  • or you need to select a range of rows and create letters for all selected rows,
  • or, you may want to filter the table for different criteria, and create letters just for visible rows,
  • Create Letter for each row in table.

The following will cover all these scenarios, the letters will be saved as PDF files. Read More

Add New Controls to Excel UserForm

Add New Controls to Excel UserForm

In this article, you will find code for adding new controls to an Excel User Fom, for adding items to comboboxes and to create an array of values from a range of cells.

Read More

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

Autoexpand Excel Tables on Protected Sheets

Autoexpand Excel Tables on Protected Sheets

Scenario:

An important feature of Excel Defined Tables, the autoexpanding functionality, is lost when the worksheet is protected. On a protected worksheet, when you type anything under the last row, the table will not automatically expand to include the new row, as you might expect. The decision to protect the sheet is not easy when you need both protection and autoexpansion capabilities for an Excel Table.

Most likely, Microsoft engineers are well aware of this limitation, but until they come up with a solution to this problem, you can try my solution. The solution involves a little Visual Basic programming, but fortunately for you, I have taken care of the programming part.

Using the Worksheet_SelectionChange event,

we can detect what cell is selected by a user, and then we can decide if we need to unprotect (or not) the worksheet. Read More

Hide Excel Sheets if Macros are not Enabled

Scenario:

If your workbook contains macros designed to assist you or your users in typing data, or you have UDF’s – User Defined Functions for specific calculations, then if the users do not enable macros after opening the file, your codes are useless…

Read More

Excel User Form Assistant

Force users to type in Text Boxes only what you expect…

Excel User Forms are a great way to collect data in a specific structure. Unfortunately, there is a major limitation that you have to take into consideration when choosing between a worksheet form or a User Form:  if utilizing a User Form for data entry, there is no built-in data validation for Text Boxes, or Combo Boxes… (you can only limit the number of characters, which might not be what you want)

The solution is quite simple:

To see how the solution works, download the test workbook: Real Time user form assistant.xlsm

Read More