Tech Tip: Microsoft Excel & Power BI

Taking Data Visualization and Collaboration to the Next Level: Excel and Power BI Integration

Data visualization tools are all about helping to make it easier for people to work with their data. An even more important aspect of data visualization is to be able to get real actionable insights into the data you’re working with. The more easily a tool can satisfy both aspects, the better it is for both data handlers and data consumers.

Microsoft Excel is king in this regard. For most users, no other tool comes close to Excel as far as data management is concerned.

If you’re using Microsoft Office 365, you have access to yet another gem: Power BI. This suite of business analytics tools allows you to analyze data, and obtain and share insights in ways that no other tool can.

In essence, this suite of tools is designed to provide users with a 360-degree view of data, with the most important metrics all in one place. Even more amazingly, Power BI updates information in real time and makes it available across all devices.

If you’re using this tool, you can explore the data behind your dashboard with just a single click. Everything is intuitive and guides you to find the answers you need with ease.

Pooling the strength of Excel and Power BI together

While most people think they have to choose whether to use Power BI tools or Excel, the two can be used together to create a powerful combination for any range of data visualization needs.

Notice that the whole idea of Office 365 is to enhance collaboration within the organization. A complete cycle of content creation and collaboration would typically look something like:

1. Get data,
2. Analyze the data,

• Visualize the data,

1. Publish the data, and;
2. Collaborate with other teams based on the data.

Normally, teams that use Excel tend to only involve Power BI when it comes to publishing their data. In essence, you can choose to work in the tool you love when using Excel and Power BI together. You can build your data or models, analyze and visualize them using either Excel or the Power BI tools – whichever works better for you – then publish out to Power BI where you can then build really beautiful reports to share with everyone in your enterprise.

Modern BI with Office 365

The modern BI available on Office 365 provides for efficient integration of Excel and Power BI Pro and Power BI Desktop. Different interactions between Power BI Desktop, Power BI Service, Excel Online and Excel Desktop exist that bring all these together in a common interface.

Each component in this interface has a function that complements that of the other.

Excel allows for data analysis in a familiar environment

Unlike most Power BI tools, the majority of the people within organizations are familiar with Excel functionalities. As such, Excel provides not only the flexibility and freedom to connect, shape, and model your data to fit your business needs, but also the familiar interface with which to visualize data for your organization’s teams.

Power BI Pro allows for publishing of reports

These are the BI tools available online that allow you to publish your reports securely to your organization while ensuring their accessibility from anywhere on any device.

Power BI Desktop is handy in building advanced models

This is the desktop-based interface of Power BI that lets you build advanced models, queries, and reports that help visualize your data in a way that can be consumed easily by anyone in your organization. Power BI Desktop is designed to enable visual data exploration and interactive reporting capabilities powered by a freeform canvas for drag-and-drop examination.

With this interface, you can build data models, create reports based on them, and share your work by publishing it out to the cloud-based BI services.

Office 365 brings everything together

This Microsoft service provides the platform that glues all these tools together into a single fabric and makes these interactions possible. On a broader scale, Office 365 allows solid team collaboration at the enterprise level; with real-time teamwork and compliance.

Here’s a sample demo showing a typical flow of data across this Excel- Power BI interface on BI Office 365.

The essence here is to see where each of the tools: Excel, Power BI Service and Power BI Desktop come in – with the SharePoint management tool as a necessary addition.

So, from the demo, different sales teams are working directly on various excel files, putting them together. Analysts then pull in all that data and add their input (behind the scenes/including adding data from other sources) before publishing it to Power BI where the data model will sit.

From there, a designer uses Power BI desktop to create final, typically actionable reports out of the model from the cloud.

Wrap Up

Well, as you can see, this is just a sample demo of what you can achieve by integrating Excel and Power BI tools together. Nonetheless, the key takeaway is the demonstration of just how using these tools in combination can serve to enhance collaboration within your organization.

Author: Joe Martin, Date: 2018-06-25