Chapter 14
In This Chapter
Controlling access to your dashboards and reports
Displaying your Excel dashboards in PowerPoint
Saving your dashboards and reports to a PDF file
Publishing your dashboards to the web
Let’s face it: You’re not making these dashboards and reports for your health. At some point, you’ll want to share your handiwork with others. The focus of this chapter is on preparing your dashboards for life outside your PC. Here, I help you explore the various methods of protecting your work from accidental and intentional meddling and show how you can distribute your dashboards via PowerPoint, PDF, and the web.
You’ve put in a ton of hours getting your dashboard and reports to work the way you want them to. The last thing you need is to have a clumsy client or an overzealous power user botching up your Excel file.
Before distributing any Excel-based work, you should always consider protecting your file using the protection capabilities native to Excel. Although none of Excel’s protection methods are hacker-proof, they do serve to prevent accidental corruption and to protect sensitive information from unauthorized users.
Perhaps the best way to protect your Excel file is to use Excel’s protection options for file sharing. These options enable you to apply security at the workbook level, requiring a password to view or make changes to the file. This method is by far the easiest to apply and manage because there’s no need to protect each worksheet one at a time. You can apply blanket protection to guard against unauthorized access and edits. Take a moment to review the file-sharing options, listed here:
The next few sections discuss these options in detail.
You can set your workbook to read-only mode until the user types the password. This way, you can keep your file safe from unauthorized changes yet still allow authorized users to edit the file.
Here are the steps to force read-only mode:
In the Save As dialog box, click the Tools button and select General Options, as shown in Figure 14-1.
The General Options dialog box appears.
Save the file to a new name.
At this point, the file is password-protected from unauthorized changes. If you were to open it, you’d see something similar to Figure 14-3. Failing to type the correct password causes the file to go into read-only mode.
Note that Excel passwords are case-sensitive, so make sure Caps Lock on the keyboard is turned off when entering your password.
You may have instances in which your Excel dashboards are so sensitive that only certain users are authorized to see them. In these cases, you can require users to enter a password to open the workbook. Here are the steps to set up a password for the file:
In the Save As dialog box, click the Tools button and select General Options. (Refer to Figure 14-1.)
The General Options dialog box opens.
Type an appropriate password in the Password to Open text box, as shown in Figure 14-4, and click OK.
Excel asks you to reenter your password.
Save your file to a new name.
At this point, your file is password-protected from unauthorized viewing.
Removing workbook-level protection is as easy as clearing the passwords from the General Options dialog box. Here’s how you do it:
In the Save As dialog box, click the Tools button and select General Options. (Refer to Figure 14-1.)
The General Options dialog box opens.
Save your file.
When you select the Read-Only Recommended check box in the General Options dialog box (refer to Figure 14-4), you get a cute but useless message recommending read-only access upon opening the file. This message is only a recommendation and doesn’t prevent anyone from opening the file as read/write.
You may find that you need to lock specific worksheet ranges, preventing users from taking certain actions. For example, you may not want users to break your data model by inserting or deleting columns and rows. You can prevent this by locking those columns and rows.
By default, all cells in a worksheet are set to be locked when you apply worksheet-level protection. The cells on that worksheet can’t be altered in any way. That being said, you may find you need certain cells or ranges to be editable even in a locked state, like the example shown in Figure 14-5.
Before you protect your worksheet, you can unlock the cell or range of cells that you want users to be able to edit. (The next section shows you how to protect your entire worksheet.) Here’s how to do it:
After you’ve selectively unlocked the necessary cells, you can begin to apply worksheet protection. Just follow these steps:
Type a password in the text box shown in Figure 14-8 and then click OK.
This is the password that removes worksheet protection. Note that because you can apply and remove worksheet protection without a password, specifying one is optional.
In the list box shown in Figure 14-8, select which elements users can change after you protect the worksheet.
When a check box is cleared for a particular action, Excel prevents users from taking that action.
Take a moment to familiarize yourself with some of the other actions you can limit when protecting a worksheet. (Refer to Figure 14-8.) They are described in this list:
Just follow these steps to remove any worksheet protection you may have applied to your worksheets:
If you look under the Review tab on the Ribbon, you see the Protect Workbook icon next to the Protect Sheet icon. Protecting the workbook enables you to prevent users from taking any action that affects the structure of the workbook, such as adding or deleting worksheets, hiding or unhiding worksheets, and naming or moving worksheets. Just follow these steps to protect a workbook:
Choose which elements you want to protect: workbook structure, windows, or both. When a check box is cleared for a particular action, Excel prevents users from taking that action.
Selecting the Structure option prevents users from doing the following:
Choosing the Windows option prevents users from changing, moving, or sizing the workbook windows while the workbook is open.
You may find that your organization heavily favors PowerPoint presentations for periodic updates. Several methods exist for linking your Excel dashboards to a PowerPoint presentation. For current purposes, I focus on the method that is most conducive to presenting frequently updated dashboards and reports in PowerPoint — creating a dynamic link. A dynamic link allows your PowerPoint presentation to automatically pick up changes that you make to data in your Excel worksheet.
When you create a link to a range in Excel, PowerPoint stores the location information to the source field and then displays a representation of the linked data. The net effect is that when the data in the source file changes, PowerPoint updates its representation of the data to reflect the changes.
You can find the Chapter 14Samples.xlsx file example for this chapter on this book’s companion website.
To test this concept of linking to an Excel range, follow these steps:
On the Home tab in PowerPoint, choose Paste ⇒ Paste Special, as shown in Figure 14-11.
The Paste Special dialog box appears, illustrated in Figure 14-12.
Click OK to apply the link.
The chart on your PowerPoint presentation now links back to your Excel worksheet. See Figure 14-13 for an example.
If you’re copying multiple charts, select the range of cells that contains the charts and press Ctrl+C to copy. This way, you’re copying everything in that range of cells — charts and all.
The nifty thing about dynamic links is that they can be updated, enabling you to capture any new data in your Excel worksheets without re-creating the links. To see how this works, follow these steps:
Go back to your Excel file (from the example in the previous section) and change the values for Samsung and Nokia, as shown in Figure 14-14.
Note the chart has changed.
Return to PowerPoint, right-click the chart link in your presentation, and choose Update Link from the menu that appears, as demonstrated in Figure 14-15.
You see that your linked chart automatically captures the changes.
Save and close both your Excel file and your PowerPoint presentation and then open only the newly created PowerPoint presentation.
Now you see the message shown in Figure 14-16. Clicking the Update Links button updates all links in the PowerPoint presentation. Each time you open any PowerPoint presentation with links, it asks you whether you want to update the links.
Note the scary language warning you about potential security concerns. (See Figure 14-16.) This is just a reminder that opening untrusted documents can open the door to malicious viruses. Use your best judgment, and only open documents from trusted sources.
Having PowerPoint ask you whether you want to update the links each and every time you open your presentation quickly gets annoying. You can avoid this message by telling PowerPoint to automatically update your dynamic links upon opening the presentation file. Here’s how:
In the Info Pane, go to the lower-right corner of the screen and select Edit Links to Files, as shown in Figure 14-17.
The Links dialog box opens, as shown in Figure 14-18.
Click each of your links and select the Automatic radio button at the bottom of the dialog box.
When your links are set to update automatically, PowerPoint automatically synchronizes with your Excel worksheet file and ensures that all your updates are displayed.
To select multiple links in the Links dialog box, press the Ctrl key on the keyboard while you select your links.
Starting with Excel 2010, Microsoft has made it possible to convert Excel worksheets to a PDF (portable document format). A PDF is the standard document-sharing format developed by Adobe.
Although it may not seem intuitive to distribute dashboards with PDF files, some distinct advantages make PDF an attractive distribution tool:
To convert your workbook to a PDF, follow these simple steps:
In the Export pane, select Create PDF/XPS Document and then click the Create PDF/XPS button, as shown in Figure 14-19.
The Publish as PDF or XPS dialog box opens.
In the Options dialog box, illustrated in Figure 14-21, you can specify what you want to publish.
You have the option of publishing the entire workbook, specific pages, or a range that you’ve selected.
OneDrive is Microsoft’s answer to Google Docs. You can think of it as a Microsoft Office platform in the cloud, allowing you to save, view, and edit your Office documents on the web.
When you publish your Excel dashboards or reports to OneDrive, you can
To publish a workbook to OneDrive, follow these steps:
Click the File button on the Ribbon, click the Save As command, and choose OneDrive, as demonstrated in Figure 14-22.
The OneDrive pane allows you to sign in to your OneDrive account.
If you don’t have a OneDrive account, you can sign up for one using the Sign Up link.
Sign in to your OneDrive account.
After you sign in, the Save As dialog box shown in Figure 14-23 appears.
Click Browser View Options to select which components of your workbook will be viewable to the public.
The Browser View Options dialog box allows you to control what the public is able to see and manipulate in your workbook.
Click the Show tab, illustrated in Figure 14-24.
Here, you can select and deselect sheets and other Excel objects. Removing the check next to any sheet or object prevents it from being viewable from the browser. Again, this is a fantastic way to share your dashboard interfaces without exposing the back-end calculations and data models.
After you confirm your Browser View options, save the file to your Documents folder.
At this point, you can sign in to OneDrive and navigate to your documents to see your newly published file.
There are several ways to share your newly published workbook:
It’s important to understand that workbooks that run on the web are running in an Excel Web App that is quite different from the Excel client application you have on your PC. The Excel Web App has limitations on the features it can render in the web browser. Some limitations exist because of security issues, whereas others exist simply because Microsoft hasn’t had time to evolve the Excel Web App to include the broad set of features that come with standard Excel.
In any case, the Excel Web App has some limitations:
18.218.55.223