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 CTRL+T (Insert -> Table) Experience

The From Table or Range Monkey replaces Excel’s standard Create Table dialog launched via CTRL + T 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
  • 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!

 

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

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
Named ranges and dynamic arrays created by Monkey Tools are all displayed in Excel's Name Manager feature

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.  A description of each of these options can be found below:

By default, Monkey Tools replaces the “Get Data” experience from these three entry points:

  • Data -> From Table
  • Data -> From Other Sources -> From Table or Range
  • Right click -> From Table/Range

By checking this box, each of these entry points will revert back to Microsoft’s default experience.

 

By default, Monkey Tools replaces the Insert->Table experience from these two entry points:

  • CTRL + T
  • Insert -> From Table

By checking this box, both of these entry points will revert back to Mircosoft’s default experience.

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

 

While we wish that Excel tables included a Connection Only query by default when created – and have designed Monkey Tools to do this – we appreciate that this may not be for everyone.  And while you can uncheck the option each time you create a new table or named range, that may result in extra clicks for you.

By checking this checkbox, the “Create Connection Only Query” will be unchecked by default when launching the From Table or Range Monkey form.

The Options Monkey holds three options relevant to the From Table or Range experience, allowing you to use Microsoft's default experiences, or customize how our form works.

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!

Share:

Facebook
Twitter
LinkedIn

Related Posts

Biblo Monkey

Article Overview If you’ve worked with Excel for any length of time, chances are pretty good that you have some favourite Power Queries, DAX Measures,

Global Options controlled by the Options Monkey include legacy PivotTable buttons, show the user interface in Free mode, Disable Enhanced Query Metadata Analysis and Error Logging

Options Monkey

Article Overview What is an application with Options? Monkey Tools is no difference, and we have a specific Options Monkey to help you control them

Export Model Data

Coming Soon! Sorry, but this article isn’t quite ready yet. We promise that we’re working on it! In the mean time, we did feature this

Export Model Components

Coming Soon! Sorry, but this article isn’t quite ready yet. We promise that we’re working on it! In the mean time – until we get

Import Monkey

Coming Soon! Sorry, but this article isn’t quite ready yet. We promise that we’re working on it! In the mean time – while you wait