Publication and Deployment – Sharing with Report Users

The dashboard we created in the previous chapter must now be shared with users. Creating an Excel dashboard is just the beginning. Sharing it securely and effectively with others is an important part of the process. This chapter will help you navigate through securing your data, making it easy for others to collaborate, and choosing the right ways to share it online. Our aim is to help you keep your dashboard flexible for updates while ensuring the data remains accurate.

We will cover the following main topics:

• Protecting your workbook
• Collaboration
• Publishing online via OneDrive/SharePoint
• Exporting your data model to Power BI

Protecting your workbook

Keeping your data safe is key when sharing your workbook. Excel has a bunch of tools to help you do this. Excel lets you protect different parts of your workbook, including individual cells, sheets, or the entire workbook. This way, you can control who sees or edits what. The Protect Sheet and Protect Workbook features are simple yet effective ways to keep your workbook safe. They provide you with the option to add passwords to further secure the data and formulas in your workbook.

Figure 10.1 – The Protect Sheet and Protect Workbook features

The Protect Workbook feature in Excel prevents others from making big changes such as adding or deleting sheets. This is handy when sharing with many people who may not know their way around your workbook.

Protecting certain cells or sheets with the Protect Sheet feature in your Excel dashboard is a good idea when you have sensitive information or formulas. This keeps important data...

Collaboration

Working together in Excel can make your projects better. It’s easy for everyone to chip in with their ideas when they can work on the dashboard at the same time. When you use platforms such as OneDrive or SharePoint with Excel, many people can work on the same workbook all at once. This helps get things done faster and makes sure the data is accurate. The Share feature in Excel lets you invite others to view or edit your workbook. This is great for big projects with many people involved.

Figure 10.15 – Accessing the Share option

You can set different permission levels, such as Can view or Can edit. This way, you control who can change your workbook.

Figure 10.16 – Setting permission levels

Before allowing others to collaborate with you on your workbook or dashboard, let’s look at a few considerations:

• Determine access levels: Decide who needs edit access versus who only needs...

Publishing a view-only online version via OneDrive/SharePoint

When we invite people to collaborate on shared workbooks in Excel, the changes they make in the workbook can impact the original workbook depending on the permissions we set for them. Creating an interactive view-only dashboard in Excel can enhance collaboration and ensure that the people we share our dashboard with can access and view the same dashboard without impacting each other’s changes.

To do this, we can generate a custom URL linked to our dashboard and share this link with others. Sharing it this way means people can see your workbook anytime, anywhere.

Skill three – using OneDrive/SharePoint as a means to share your final work

There are a few things we need to consider first. We will go through them step by step:

1. Saving on OneDrive/SharePoint: The process begins by ensuring that our dashboard is saved on OneDrive or SharePoint. We then need to adjust the settings and configurations...

Exporting your data model to Power BI

Exporting a data model from Excel to Power BI, including all measures and Power Query transformations, is a seamless process that involves importing the Excel workbook into Power BI Desktop. The two programs work together smoothly, so you can move your data over without a hitch.

Power BI can handle big, complex datasets. This is great when you have a lot of information to work with.

Let’s go through the steps:

1. We begin by opening Power BI Desktop. This is free and you will not require an account to use this. However, a licensed version gives you more advantages when you bring in the data model from Excel.

Figure 10.33 – Power BI Desktop

2. After opening Power BI Desktop, you can now go to File | Import and choose the Power Query, Power Pivot, Power View option:

Figure 10.34 – Accessing the Import option

Ensure that your Excel file containing...

Summary

Beyond creating your dashboard in Excel, you have now learned about various ways you can share, collaborate on, and publish your final work. Now you know how to keep your workbook safe with the Protect Sheet and workbook features and share it online. Using OneDrive, SharePoint, and Power BI, you can reach a wider audience and make your data work harder. The skills you’ve learned in this chapter will help you share your Excel dashboard effectively, keeping it flexible for updates while making sure the data stays accurate.

As we wrap up this book, reflect on the skills you’ve gained and consider how they’ll aid you in creating an effective data model in Excel and building an interactive dashboard that will provide your users with key insights for decision-making.