Home
Free Trial Sign In



  • Economic Impact
  • Research Techniques
  • Economic Impact Analysis
  • Cost-Benefit Analysis
  • Valuing Social Impact
  • Feasibility and Location Studies
  • Macro and Micro Forecasting
  • Data Analytics and Custom Models
  • Labour Market Analysis
  • Economic Development in EMs
  • State and Local Strategy
  • Scenario Analysis and Stress Testing
  •  
  • Economic Impact archive
  • By Topic
  • Browse Economic Impact by topic
  • By Industry
  • Browse Economic Impact by industry
  • Full archive
  • Publications in date order
  • Thought Leadership
  • Research Inputs
  • Global surveys
  • Executive interviews and case studies
  • Advisory panels and peer interchange
  • Social media research
  • Quantitative and economic analysis
  •  
  • Research Outputs
  • White papers, briefings, and think pieces
  • Charts, infographics, and dashboards
  • Analytical tools and models
  • Social and digital media
  • Microsites and presentations
  • Forward Thinking Blog
  • Click here for latest content
  •  
  • Thought Leadership Examples
  • By topic
  • Publications categorised by Topic
  • By Industry
  • Publications categorised by Industry
  • By Client
  • Publications categorised by Client
  • Full archive
  • Publications in date order

Techlabs

Helpful tips and guidance on

all the good stuff  that comes out of

Oxford Economics Technology Labs

  • Techlabs
  • Build Your Own Mondrian Dashboards

Build Your Own Mondrian Dashboards

Recently we published a couple of new Mondrian Dashboards which leverage Excel’s latest Power Query and Power Pivot technologies. The results are excellent when combined with Oxford Economics Excel Data Workstation.

image

In this post, we’re going to show you how do build these Mondrian Dashboards yourself.

Below is a graphic with 9 tried and tested steps which you can follow to build your own flexible interactive dashboards in Excel using Power Query and Power Pivot!

image

 

Nine Steps Recipe to Your Own Mondrian Dashboards

1

  • Decide upfront which indicators (variables + locations) you want to show on your dashboard as either charts or tables.
  • Create a new Excel worksheet and name the tab appropriately.

2

  • Open Excel Data Workstation (EDW), aka Excel Plugin, by clicking on the Oxford Economics ribbon menu.
  • Using the Data Series selection tool, pick your indicators and choose your period, frequency and measurement settings.

3

  • Using EDW’s Add Data Series command, download the selection into Excel.
  • Recommended, but optional, convert the worksheet to a data table (CTRL-T) and name it appropriately in Design | Properties | Table Name.

4

  • Using Excel’s Power Query ribbon (enabled via COM Add-Ins) create a data query on the worksheet via Power Query | Excel Data | From Table command.
  • This opens the Power Query editor application with a query of the same name as your data table.

5

  • In the Power Query editor, use the Transform | Any Column | Unpivot Columns ribbon command to convert the several horizontal year data columns into two columns.
  • They will be automatically named Attribute and Value.

6

  • Rename the unpivoted columns (Attribute, Value) as you desire (e.g. Year, Data).
  • Notice the type of the Value/Data column is automatically set to Decimal Number.
  • If you have YYYY QN Year formats, use Power Query’s Transform | Split Column command to create separate Year and Quarter columns.

7

  • Remove any columns you don’t require using Manage Columns | Remove Columns command.
  • Notice the individual query commands being applied are recorded in the right hand panel. They can be edited or removed allowing safe experimentation.

8

  • Click Close | Close & Load | Close & Load To…
  • In the pop up dialog box select Only Create Connection and check Add this data to the Data Model.
  • Click the Load button.

9

  • Open the Insert menu. Click Charts | Pivot Chart | Pivot Chart (or Pivot Table).
  • In the dialog box, choose Use an external data source, and click Choose Connection to select your newly-created (power) query.
  • Check New Worksheet and then click OK.
    Configure the pivot chart (or table).
  • Repeat Step 9 as many times as required

 

If you download any one of the new Mondrian Dashboards you can examine the VBA code we wrote to automate data updates using Oxford Economics Excel Data Workstation. Feel free to reuse and modify as much of that code as you like.

Have fun and let us know how you get on!

 

Arvindra Sehmi is Chief Information Officer and Director of IT at Oxford Economics.

Excel Dashboards, Excel Data Workstation, Excel Plugin

Search our archive

Search our portfolio of public information
Go to archive
  • Oxford Economics
    • About us
    • Contact us
    • Economists and analysts
    • Careers
  • Products and services
    • Subscription services
    • Economic Impact
    • Thought Leadership
    • Forecasts and scenarios
    • Impact of policy change
  • Help and software
    • Help using our services
    • Software and downloads
    • Latest technology developments
  • Follow Us
    • LinkedIn
    • Twitter
    • Facebook
© Oxford Economics 2021 all rights reserved