Excel Properties brings the ability to create Topic Properties whose values are fed from a Microsoft Excel Spreadsheet. The function enables values to be retrieved from Excel as well as allowing values to be changed in the Map and then refreshed back to the source spreadsheet on demand.
Values that are provided from calculated cells in Excel are made read-only in the Map to prevent accidental overwriting and disconnected cells/values are checked and notified as you use the function. Currently only numerical values are supported such as Currency, Percentage, Number etc. Text and Dates will be added at a later date. You can utilize values from multiple source spreadsheets in one Map and Topics can be updated/refreshed on a Topic by Topic basis or by a complete Map refresh depending on the users requirements.
Excel Properties can be edited in the Map and can be removed when redundant. Commands can be accessed from the MAP ribbon menu or directly on any Topic containing an Excel Property through a small Control Strip. This provides quick access to any Topics that contain these special Properties.
Adding an Excel Property
When you wish to use an Excel Property you need to know the location of the Spreadsheet and also the Cell Reference that contains the value you wish to use. Once you know this, you can left click the top section of the Excel Properties command to access the Excel Property Details form.
Type in the name for the Property. Next, select the Property Type from the drop-down menu. As mentioned above, Current Types are Currency, Number, Integer and Percentage. If you select Currency you will also be able to select the Currency Symbol to use. Next, select the Spreadsheet.
If you have previously used the Spreadsheet it will be listed in the drop-down of the last 10 used Spreadsheets. If not, you can click the Browse button to locate the file. Once the Spreadsheet is successfully selected, the Select Workbook drop-down menu will populate so you can select the Worksheet that contains the Cell you wish to reference.
Once you have the Worksheet selected enter the Column and Row values and click the Insert button. The Property will be added to the selected Topic and immediately retrieve the value from the Spreadsheet.
Excel Property Control Strips
Once you have inserted an Excel Property onto a Topic you will notice the Excel Property Controlstrip. This gives you access to the range of commands and actions you can use with these Properties. To access them simply left-click the Controlstrip to reveal the context menu.
From this menu you can Edit the current Property, Retrieve or Update the Properties of the selected Topic with the Spreadsheet or Retrieve or Update all of the Excel Properties in the whole Map.
Editing Excel Properties
Should you wish to Edit an Excel Property or the Property becomes disconnected from its source Spreadsheet for any reason you can use the Edit/Remove Excel Property command. This can be accessed from the context menu of the Controlstrip or from the Excel Properties command on the ribbon menu drop-down.
Once clicked, the Update Excel Property Details form will display. You can now select which Property you wish to edit/remove and the details will automatically propagate in the form.
You can now make any changes or click Remove to safely remove the Property from the Topic.
Refreshing Excel Properties
You can refresh/update Excel Properties on either a Topic basis or across the whole Map. The Retrieve and Update commands are available from either the main Excel Properties command located on the ribbon menu:
or the context menu of the Excel Properties Controlstrip located on the Topic
Both methods give access to the same commands. Simply select the relevant action and your Topics/Spreadsheet should update accordingly. If you have many Excel Properties in your Map you may see a small progress indicator as the process runs to let you know something is happening.
Calculated Excel Cells
If you reference a value from a Cell that is derived from a formula in Excel then the Property will be Read-Only. This is so the Excel Cell formula is not inadvertently overwritten by sending the value back to the Spreadsheet. Only Cells that contain static values can be updated back to the source Spreadsheet from the Map.
The Property Clipboard provides the ability to copy Topic Properties such as Custom Properties, Autocalcs, Formulas, Spreadsheets and Excel Ranges. The function can be used to copy all Topic Properties in one action, Paste Properties to multiple Topic selections and copy individual Property Types through the extended drop-down menu commands. You can also choose whether to include any Property Values or simply copy the Topic Property itself.
Copying All Topic Properties
The default action of the Property Clipboard is to copy any and all Topic Properties from the selected Topic. To place the Properties onto the Property Clipboard simply select a Topic and left-click the top section of the Property Clipboard command.
Once the Properties have been copied the Property Clipboard command button will become “lit”. This indicates that there are Properties on the Clipboard waiting to be pasted onto a new Topic(s). The icon for the button changes and the command text also changes from “Property Clipboard” to “Paste Properties”. Select the Topic(s) you wish to Paste the Properties onto and then left-click the top section of the command a second time to Paste the Properties.
If the Paste is successful you will then see the Properties on the selected Topic(s).
Copying Specific Types of Property
If you wish to copy a specific type of Topic Property then you can use the individual commands that enable you to copy/paste any of the following Properties:
- Custom Properties
- Formulas & Autocalcs
- Embedded Spreadsheet
- Excel Properties (only available with MAP)
You can access these commands by left-clicking the bottom section of the Property Clipboard command.
The selected Topic must contain Properties of the type you wish to copy for the command to be enabled.
Pasting to Multiple Topics
Once you have copied Properties to the Clipboard you can choose to Paste then to a single Topic or a selection of Topic(s). If you wish to paste a selection, simply select multiple Topics using the CTRL key or left-click and hold the mouse button down while dragging to use the “Rubber Band” tool. Once selected left-click the Paste Properties command to complete the action.
Including or Excluding Topic Property Values
There is an option that enables you to specify whether the action should include the Property and its value or just the Property. If you wish to change this you can toggle this setting On/Off from the extended menu accessed from the bottom section of the command.
Clear Property Clipboard
If you select a Topic, click to copy the Properties and then decide you do not wish to use the currently selected Properties, you can clear the Property Clipboard by using the Clear Property Clipboard command located on the extended menu. This will reset the Clipboard ready for a new copy action. Additional Commands You can also initiate the default copy action, (all Properties), from the Topic right-click context menu.
Once the Clipboard contains Properties you can also use this menu to Paste the properties onto a Topic or Topic selection and also clear the Clipboard if required.
Filter by Property
If you use Custom Properties in your Maps you may find occasions when you need to Filter your Map to highlight Topics with a specific Property Value. The Filter by Property capability will enable you to do this.
When activated from either the MAP ribbon menu or the MAP Task Pane you will see the Filter by Property dialog slide into view.
Ribbon Menu Command
Task Pane Command
Filter by Property Dialog
One the dialog is displayed you can select the Property you wish to Filter by extending the drop-down menu. This will display a list of ALL Properties used in the current Map.
Enter the Value you wish to Filter on. You can choose to ignore the case of any text values if you need to.
When you are done click the Filter command and your Map will re-draw to display the Filter results.
Map before filtering (on “Germany”)
Map after filtering
Property Manager aims to combine many of the possible actions that can be applied to Topic Properties an easy to use single interface.
With Property Manager you can:
- Rename Properties (Replaces Property Renamer)
- Edit Property Values (Globally) (Replaces Edit Property Value)
- Change Property Type
- See Property Usage
- Delete Properties
Most of these options can be run on a selection of Topics or across all instances of the selected Property.
The Property Manager Interface
When Property Manager is initialised it scans the current Map and stores an instance of all the different Properties used in the Map. These are then displayed on the right hand side of the Property Manager dialog in list format. The list shows the Property Name and its Type.
This list can be sorted by column if desired to help you locate Properties where you have many in a single Map. As you select a Property from the list you will see the various options open to you change on the left hand side of the interface.
You can apply changes to the currently selected Topics or to all matching Topics across the entire Map by toggling the Apply to selection only check box located in the lower left hand corner of the dialog.
Once you have made your changes, click the Update Property button to update the Map with your changes.
Changing a Property Name
Property Manager can be used to change the name of Properties by selection or across the Map which saves huge amounts of time if you decide to change something once you have committed yourself and started to build large Maps which contain many Topics with Properties.
When updating Properties where the Name has been changed you will be asked whether or not you also wish to update any Formulas in the selection/Map that utilise the changed Property.
If you select Yes then Property Manager will run through all the Formulas in the Map and replace the old reference with the new one, keeping your Formulas up to date and valid. Selecting No to this prompt will leave existing Formulas as they are and result in a Formula syntax Error.
Once complete, you will be informed how many Properties were changed in the selection/Map.
The Map will then change to reflect your updated Properties. The examples below show us changing Sales to Turnover.
Changing a Property Value
You can also change the value of a selected Property. Note that this will make all instances of the Property have the same value so please be aware of this if your selected Properties contain unique values.
The process is the same. Simply select the Property from the list, Click the Edit Value button to activate the relevant value field and enter the new value.
Note the field for the value will change to suit the type of Property selected. So a Text value will offer a text box control whereas a Date value will offer a Date and Time Picker control etc.
Clicking Update Property will then scan the selection/Map and make the desired changes.
Changing a Property Type
Changing a Property Type can be useful if you have created several Properties and then decide that the Type initially chosen is not perhaps the best. This will mainly be relevant for numerical Types such as changing a Number to a Currency value or perhaps changing a Currency Type to a different currency symbol etc.
Simply select the Property to change and select the new type from the Type drop-down list. Once selected, the dialog will update and give you access to the specific values for the selected type that may be changed.
When you have finished making your amendments click the Update Property button to update the selection/Map.
Deleting a Property
There may be times when Properties you have set up in a Map become redundant and you wish to remove them. Removing them from the Properties Table saves space and keeps readers of the Map focused on the data that matters and not obscured by out of date or redundant Properties.
Note: When Properties are removed from the Map any references to them in Formulas will remain intact and therefore break any such Formulas.
Sort by Property
Another requirement users of Custom Properties may find they need is the ability to Sort a collection of Sub-Topics by a specific Property. The Sort by Property capability will enable you to do this.
You must have selected a Topic that contains more than one Sub-Topic that contains a Custom Property.
When activated from either the MAP ribbon menu or the MAP Task Pane you will see the Sort by Property dialog slide into view.
Ribbon Menu Command
Task Pane Command
Sort by Property Dialog
One the dialog is displayed you can select the Property you wish to Sort by extending the drop-down menu. This will display a list of ALL Properties contained in the current Sub-Topic collection.
Enter the Value you wish to Sort.
When you are done click the Sort command and your Map will re-draw to display the Sorted Sub-Topics.
Map before sorting the “Sales” Property
Map after sorting
Currently, Topics are sorted by Ascending values. The option to select between Ascending or Descending results will be added in a later update to MAP.