Monkey Tools

From Table or Range Monkey

Article Overview

One of the things we have been asking the Excel team to do for a long time is give us the ability to provide a table name during the creation process. This is particularly important when using the Get Data From Table/Range feature, as this feature creates a generic table (or dynamic array) name and references it via Power Query. When you then update the name, your Power Query blows up and needs to be fixed.

To solve this issue, Monkey Tools replaces the standard “Create Table” dialog with a custom “From Table or Range Monkey” dialog which gives you not only the ability to name your table, but a few other options as well.

Working with the From Table or Range Monkey

Feature 1: A revised Create Table Experience

The From Table or Range Monkey replaces Excel’s standard Create Table dialog launched via CTRL + T, CTRL + L or Insert -> Table. Our revised dialog allow you to:

Create Tables, Named Ranges or name Dynamic Arrays
Define the name of the item during the creation process
(Optionally) Generate a Connection Only query with the Table or Named Range

By default, if you select a single cell in the data range prior to launching this experience, Monkey Tools will identify if it is a tabular range or a dynamic array spill area, and provide the correct range of data to name. Of course, if we do happen to get it wrong, you can always click in the address area and select a different range as well!

Monkey Tools also provides a smarter algorithm for detecting if your data has headers than the Excel default, making it less likely you’ll ever need to check/uncheck that box when creating a Table.

The From Table or Range Monkey form in action, allowing you to define the Table's name upon creation — CTRL+T allows more options when creating tables with Monkey Tools

Using Monkey Tools From Table or Range functionality to create a Named Range — CTRL+T allows the creation of Named Ranges in Monkey Tools

Using Monkey Tools From Table or Range functionality to name the spill range of a Dynamic Array formula — Monkey Tools From Table or Range experience allows you to name Dynamic Array spill ranges

Feature 2: A revised Get Data -> From Table or Range Experience

Using Get Data -> From Table or Range in Monkey Tools allows you to define the name of the table BEFORE launching into Power Query — Get Data -> From Table or Range using Monkey Tools

Using Get Data -> From Table or Range in Monkey Tools allows you to configure a named range instead of a table — Define Workbook or Worksheet scoped names

In addition to the CTRL + T experience, we also provide this form when creating a new query using Power Query’s From Table or Range connector. The only differences you’ll find between the two methods are:

You will not see the ability to create a Connection Only query , because…
After choosing your Table/Named range setup, you’ll be launched directly into the query editor to customize your query

The impact of this form is massive: You are able to define the name of the Table, Named Range or Dynamic Array spill area BEFORE being launched into Power Query! Instead of having to explicitly set up these names prior to using the Get Data button, you can now use the functionality as it was intended, without the concern of breaking your newly created query by fixing the table name afterwards.

Managing Your Named Items

At the end of the day, Monkey Tools just gives you an interface in order to make it easier to create named items, saving you from potential issues in future. Having said this, each name can be managed via standard Excel functionality.

To rename:

A Table: Select a cell inside the table, go to the Table Design contextual tab and change the name in the Table Name box on the far left of the tab.
A Named Range/Array: Go to Excel’s Name Manager found via the Formulas tab -> Name Manager

Warning! Changes to names (for Tables, Named Ranges or Arrays) are not tracked by Power Query. If you change a name it WILL break your Power Query and you will need to edit the Query to repoint it to the new name. The only exception to this is if you are a Monkey Tools pro license holder, renaming a table via the Table Design contextual tab will not break the query as our Monkeys watch for this.

From Table or Range Monkey Options

While the From Table or Range Monkey is enabled by default, we do recognize that some users may not want this. And we certainly don’t want that to be something that makes you consider removing Monkey Tools from your computer. For that reason, we have provided some configurable options to customize the user experience as you would prefer to see it. To access these options, go to the Options Monkey -> Global Options -> From Table or Range Monkey. A description of each of these options can be found below:

Use Default Options

By default, Monkey Tools replaces the “Create Table” experience launched from each of the entry points below:

From Table/Range Experience
- Data -> Get Data -> From Other Sources -> From Table or Range
- Data -> From Table or Range button
- Cell Context (right-click) menu -> Get Data From Table/Range
Insert tab
- Insert tab -> Table button
Keyboard Shortcuts
- CTRL + T
- CTRL + L

By checking any of these boxes each of these entry points will revert back to Microsoft’s default experience, allowing you to mix and match your experience as you see fit. (To revert to Microsoft’s default experience across the board, make sure you check all four boxes.)

NOTE: Monkey Tools does not replace the default experience from the Home -> Format as Table dialog.

Include Query by Default When Creating Tables

While we wish that Excel tables included a Connection Only query by default when created – the reality is that this setting does slow down table creation and we appreciate that this may not be for everyone. For this reason we leave this option un-checked by default and allow you to select it each time.

By checking this checkbox, the “Create Connection Only Query” will be checked by default when launching the From Table or Range Monkey form, saving you a click each time if you prefer that behaviour.

Custom Prefaces

By default, Monkey Tools will offer to create new items using the following prefaces:

Tables: Table
Named Ranges: rng
Dynamic Arrays: ary

In each case, Monkey Tools will provide the name and select it in its entirety to allow you to replace it easily. As many users like to provide custom prefaces for their Excel objects, we have added the ability to do so for Tables, Named Ranges and Dynamic Arrays.

By providing any text in these text boxes, the Monkey will use your custom preface instead, selecting any text after your custom preface name so that you can overwrite just that portion.

Note that clearing these fields will revert to the our Monkey’s default choices.

See the From Table or Range Monkey in Action!

Would you like to see the feature in action? Have a look at the video which Ken introduces the feature and gives a demo as to how it works!

Monkey Tools

From Table or Range Monkey

Article Overview

Working with the From Table or Range Monkey

Feature 1: A revised Create Table Experience

Feature 2: A revised Get Data -> From Table or Range Experience

Managing Your Named Items

From Table or Range Monkey Options

See the From Table or Range Monkey in Action!

Share:

Related Posts

Excel Feature Improvements

Connection Monkey

Options Monkey

Export Model Data

Export Model Components