Microsoft continues to promote the importance of Excel and Power BI (“Better Together”). Still, once you’re in Power BI, there are very limited options foputting standard Excel on top of your existing Power BI Models.

Watch  to hear from reporting specialist, Paul Martin as he takes us through how to use the companion Excel solution; EiB ReportStudio to create robust, dynamic and easy to build Excel reports directly from your Power BI Data Model.

Using standard Microsoft products, there are several options for putting standard Excel on top of your existing Power BI Models:

  • Export from Power BI to Excel – Quick & effective, but the content is static, so this method is only suitable for once-off exercises
  • Analyze In Excel (from Power BI) – Creates a blank PivotTable in Excel, ready for you or your users to build report content
  • Insert a PivotTable in Excel using a Power BI Dataset as a source

All the above are perfectly adequate options, but what if you need a bit more from your Excel Management Information (MI) reporting?

For example, here are some of the additional things you might want from your Excel Reports:

  1. Deliver fully formatted Excel reports but with dynamic drill-down and customised drill-through to detail. Interact with those reports with standard Excel formulas, just like any standard spreadsheet.
  • PivotTables may be powerful, but most final board report packs are still standard Excel reports. Why? Because Excel users want precise Excel formatting and calculations added as part of the final packaging and consumption.
  1. Replicate existing MI reports in Excel but with a fully interactive experience from Power BI data.
  • One of the most difficult challenges to overcome in persuading customers to use Power BI (or any other BI solution), is how do they still deliver their existing Excel report packs that they’ve used for the last five years! Sure you can argue, time for a change, time to improve etc., but this is so often a real objection, which can now be addressed rather than fought or debated!
  1. Explore Power BI data in your models and reconcile data without being a BI product or Excel expert.
    • One of the most common misrepresentations in business intelligence is the notion that all users will be able to easily slice and dice their data in a self-service fashion,  from easy-to-use BI products.
    • Giving a user access to a PivotTable with 200+ dimensions and saying “You can analyse anything you want”, is worse than giving them nothing at all.
  1. Use native Excel data input to drive dynamic, scalable, exception reporting.
    • It is staggering that after 20+ years of Dashboard development in some great Dashboarding products (including Power BI), the user cannot easily ask dynamic questions of their data, such as:
      “Give me the top 40 products, where revenue is above $20,000 for the year (and yes, of course, I want to change my mind on top 40, 10, 20 above $ 10,000, between $20,000 and $30,000 etc.)” Well now that’s possible from within Excel!
  1. Deliver Excel reports as applications rather than a directory of spreadsheets on your Network folder or OneDrive.
    • Smartphones have taught us the importance of apps rather than tools/components. Why not apply this to Excel reporting solutions?
  1. Distribute automated offline Excel report packs to non-executive board members or field-based users.
    • Despite our obvious desire to have everything online, in many organisations, significant effort is taken (every period end) to produce static Excel content as MI packs and to email these packs to users who require the content for board/management meetings
    • Another usage of this functionality is to provide a copy of a “point in time” report pack, so historical comparisons/audits can be stored and referenced accurately. What if we pay a salesperson 5% commission on monthly data each Month, but when there is a historical query on June 22, the data for June 22 as at February 24, is different to the data for June 22 as at June 22 ?
  1. Finally, there is the technology consideration when using Excel reporting apps for Power BI (or any other data source)
    • How do I know where all the company’s MI spreadsheets reside?
    • How can I know I’ve backed up all the Excel reports in my organisation?
    • How can I provide secure storage/access to such Excel reports based on user permissions (in addition to the usual data permissions)?

Paul Martin, Managing Director of Excel in Business shared all the above using a companion Excel solution; EiB ReportStudio for Power BI, which was developed based on delivering over 600+ BI solutions spanning a 25-year+ career in analytics.

Get a 12 month trial
Follow Paul Martin on LinkedIn
Follow Danielle Stein Fairhurst on LinkedIn

Register for a Virtual Meetup

★Download Accompanying file
Files from Paul Martin at Excel in Business