Director SumProduct
Share this content
Tags:

Excel updates: More actions with your diagrams

Excel MVP Liam Bastick continues his summary of the latest Excel developments, focusing this time on diagrams in Visio and Office scripts. 

24th Jun 2020
Director SumProduct
Share this content
Code
istock_monsitj_aw

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:

Data table

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’:

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:

Visio

Editing the diagram, such as adding text or images, applying design themes or making other customisation modifications, requires a licence.

Excel

Clicking the Share button allows you to share this file with other team members or any Office 365 users:

Share

From the far most right-hand corner ellipse, you have options to add comments, print or download the diagram:

Excel

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.

Excel

The new feature is incorporated in the Automate tab on the Ribbon for Excel on the web:

Excel

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.

Excel

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’.

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.

Excel

Click on the Insert tab and choose the Column chart in this case.

Excel

Next, we will have our first simple column chart for business unit one (1) like:

Tech Lowdown

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.

My Script

In the Code Editor, we can see the code in more detail:

Code

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.

Excel

The result dashboard would be like:

Excel

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​

Tags:

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.