Excel First

Excel Solutions

Tips

now browsing by category

Excel Solutions

 

Read Data into a Dictionary of Arrays

There are many scenarios where we need to categorize and split raw data.
In this scenario, we will split raw data into new workbooks, by category, using a Dictionary of Arrays.

Normally, it’s best to combine data into a single location, as I did in this article.

The main challenge is to find an efficient way to read data into categories:

  • the table might not be sorted by category:
  • reading and writing data row by row is very slow.
Dictionary of Arrays
Dictionary of Arrays: Unsorted Data

Download the demo file to try the solution:

Which is the most efficient way to collect and write data?

Based on my experience, even reading data row by row from a list object is a slow operation, the fastest way to read data is to load it into an array.

A bi-dimensional array is the best option to paste data into a worksheet, fits perfectly into a range of cells that has the same dimensions: rows and columns.

Because we want to separate data by category, we have to create an array of data for each category.

We will store the arrays into a dictionary object in this exercise, using category as a dictionary key.

Read More

Combine data files with Power Query

Sample-automatic-File-Combiner-in-Power-Query

Data variety is a challenge when you need to combine it into a single table. If data is consistent over time, the usual tools within power Query will not fail, but this scenario rarely happens in the real life.

Most common issues when combining data:

  • there is a change in headers, refreshing the query fails to identify new headers;
  • data comes in a different format than expected: xlsx, csv.

Download the demo files to try the solution:

Make sure you unzip the files on a location from your computer, then update the path from Settings sheet (in the file named GetCSV and XLSX from folder.xlsx) to point to your Data folder new location.

Why Power Query fails to refresh when there is a change in source data?

When you use the default tool “Combine Files” when you Get Data from Folder with Power Query, the application will analyze the first data file from that folder, or you can select the file that will serve as reference.

Read More

Download 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

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:

Cannot Open Pivot Table Source file

Can’t Open Pivot Table Source file

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

The Excel Learning Journey that I propose for you, is structured in such a way as to offer you the most practical way to learn Excel, free.

Start Your Free Excel Learning Journey!

Enter your email address to subscribe to this blog: