Excel updates: More actions with your diagrams
byExcel MVP Liam Bastick continues his summary of the latest Excel developments, focusing this time on diagrams in Visio and Office scripts.
In my previous article, we introduced Microsoft's Visio Data Visualizer. The add-in creates flowcharts and organisational charts in Excel. We're going to start this tutorial by moving on from flowcharts to diagrams.
In an Excel sheet with more than one flowchart, to identify the data table linked to the chart, click the ellipses in the chart box and choose ‘Source table’. This will cause the linked data table to be highlighted:
Also, if you are signed in, your Visio diagrams are created as online files and saved in OneDrive or SharePoint by default, so you may view, print and share them with other Office 365 users. To open the diagram in Visio for the web, from the ellipses, choose ‘Open in web’:
You will be navigated to Visio on the web in your browser, where you can view your flowchart. Options for more actions with the diagram may be found in the top right-hand corner:
Editing the diagram, such as adding text or images, applying design themes or making other customisation modifications, requires a licence.
Clicking the Share button allows you to share this file with other team members or any Office 365 users:
From the far most right-hand corner ellipse, you have options to add comments, print or download the diagram:
In short, this add-in makes it much straightforward for Excel users to create hierarchy type process maps and flowcharts. From the samples discussed above (but not limited to them), this add-in can be utilised to draw other creative charts, such as decision trees, activity diagrams or conditional probabilities modelling.
Office Scripts goes local
We have mentioned Office Scripts previously, and explained it is a new feature for Excel on the web. However, we thought we would revisit this as it is now available for users in what Microsoft calls “country/region specific data centres”, also known as “Go Local data centres”. This means that although a Preview rolled out in January, certain territories (eg Australia, Canada, New Zealand) could not access it – well now you can!
Users with any of the following licenses:
- Office 365 Business
- Office 365 Business Premium
- Office 365 Enterprise E3
- Office 365 Enterprise E5
(subject to the name changes – see elsewhere in this newsletter!) may now access Office Scripts in Excel on the web.
This is provided your administrator has opted in. To opt into Office Scripts in the Microsoft 365 Admin Center, if you haven’t already:
- sign in to Office 365 with your work or school account as a global administrator
- in the Microsoft 365 admin center, choose Settings -> Settings (you may need to select ‘Show all’ first)
- select Office Scripts
- check the box for ‘Let users automate their tasks in Office on the web’ to activate Office Scripts.
The new feature is incorporated in the Automate tab on the Ribbon for Excel on the web:
This feature allows users to automate some repetitive day-to-day tasks, but in locations where VBA can’t. We can record our Excel actions with the Action Recorder and create scripts based on actions. We can also create and edit scripts with the Code Editor. Office Scripts makes the record and replication of Excel actions on different workbooks and worksheets possible, and it may also help you to reduce your whole workflow to a single button press.
The language supporting Office Scripts is JavaScript / TypeScript. Over the past few years Microsoft has been heavily investing in JavaScript as it moves to unify Office across all devices (PC, Mac, Browser, and Mobile), including automating Excel. With JavaScript, you may theoretically write a single script that would work on any device. Also, JavaScript is extremely fast at pulling data from outside sources, and is especially useful for seamless data integration from different sources. For example, the Google Suite (G Suite) has been using JavaScript as the scripting language, so it is possible to integrate the data stored in Google with API.
Let’s have a look at one simple example to understand how Office Scripts works in Excel on the web. Suppose we have six Sales tables with different business units.
We want to generate charts for each individual business unit Sales table. We can do this by going to the Automate tab and click ‘Record Actions’.
Then, we can start recording our actions in Excel and create our first chart based on the sales data for business unit one (1). In this example, we choose the range A1:B6.
Click on the Insert tab and choose the Column chart in this case.
Next, we will have our first simple column chart for business unit one (1) like:
Yes, we can now make the chart prettier by formatting all elements, but that’s another example for another day. Here, we simply wish to show you how Office Scripts works. Therefore, at this point, we will stop recording the code and save the recording with the name ‘My Script’ for the scripts we created.
In the Code Editor, we can see the code in more detail:
In the code above, variables for workbook and active worksheet have been defined. The chart has been added by defining the type of the chart and the range of data source. We may move the chart to its “proper” position by assigning values to different parameters (e.g. left, top, width and height).
If we want to create the same chart for the other five (5) business units, we can simply copy and rewrite the code to create more charts based on the existing code and apply changes to the data sources and position of the chart. You could write the code as follows, for example:
async function main(context: Excel.RequestContext) {
// Insert chart on sheet 'Data'
let workbook = context.workbook;
let worksheets = workbook.worksheets;
let selectedSheet = worksheets.getActiveWorksheet();
// Insert chart on BU1
let chart_1 = selectedSheet.charts.add("ColumnClustered", selectedSheet.getRange("A1:B6"));
// Resize and move chart Chart 1
chart_1.left = 320;
chart_1.top = 0;
chart_1.width = 170;
chart_1.height = 110;
// Insert chart on BU2
let chart_2 = selectedSheet.charts.add("ColumnClustered", selectedSheet.getRange("A8:B13"));
// Resize and move chart Chart 2
chart_2.left = 320;
chart_2.top = 120;
chart_2.width = 170;
chart_2.height = 110;
// Insert chart on BU3
let chart_3 = selectedSheet.charts.add("ColumnClustered", selectedSheet.getRange("A15:B20"));
// Resize and move chart Chart 3
chart_3.left = 320;
chart_3.top = 240;
chart_3.width = 170;
chart_3.height = 110;
// Insert chart on BU4
let chart_4 = selectedSheet.charts.add("ColumnClustered", selectedSheet.getRange("D1:E6"));
// Resize and move chart Chart 4
chart_4.left = 500;
chart_4.top = 0;
chart_4.width = 170;
chart_4.height = 110;
// Insert chart on BU5
let chart_5 = selectedSheet.charts.add("ColumnClustered", selectedSheet.getRange("D8:E13"));
// Resize and move chart Chart 5
chart_5.left = 500;
chart_5.top = 120;
chart_5.width = 170;
chart_5.height = 110;
// Insert chart on BU6
let chart_6 = selectedSheet.charts.add("ColumnClustered", selectedSheet.getRange("D15:E20"));
// Resize and move chart Chart 6
chart_6.left = 500;
chart_6.top = 240;
chart_6.width = 170;
chart_6.height = 110;
}
In this routine, we repeat the steps in creating a column chart for each business unit and make adjustments to the parameters of that chart’s position. Then, we click the ‘Run’ button in the Code Editor.
The result dashboard would be like:
We can update the Sales data on the left side of the dashboard and open the Code Editor to run the scripts to update the charts on the right side automatically.
In the final part of this series looking at the recent developments in Excel and Power BI we'll explore a new feature that allows users to track money and spending
You might also be interested in
Recognised by Microsoft as one of 104 Most Valuable Professionals (MVPs) in Excel worldwide by Microsoft, Liam has over 30 years’ experience in financial model development/auditing, valuations, M&A, strategy, training and consultancy. He has headed Ernst & Young’s modelling team in Melbourne and was an Assistant Director in their...