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.

Protecting Your Dashboards and Reports

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.

Securing access to the entire workbook

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:

• Set read-only access to a file until a password is given.
• Require a password to open an Excel file.
• Remove workbook-level protection.

The next few sections discuss these options in detail.

Permitting read-only access unless a password is given

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:

1. With your file open, click the File button.
2. To open the Save As dialog box, click Save As and then double-click the Computer icon.

3. 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.

4. Type an appropriate password in the Password to Modify input box, shown in Figure 14-2, and click OK.
5. Excel asks you to reenter your password, so reenter your chosen password.

6. 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.

Requiring a password to open an Excel file

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:

1. With the file open, click the File button.
2. To open the Save As dialog box, click Save As and then double-click the Computer icon.

3. 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.

4. 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.

5. Save your file to a new name.

   At this point, your file is password-protected from unauthorized viewing.

Removing workbook-level protection

Removing workbook-level protection is as easy as clearing the passwords from the General Options dialog box. Here’s how you do it:

1. With your file open, click the File button.
2. To open the Save As dialog box, click Save As.

3. 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.

4. Clear the Password to Open input box as well as the Password to Modify input box and then click OK.

5. 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.

Limiting access to specific worksheet ranges

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.

Unlocking editable ranges

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:

1. Select the cells you need to unlock.
2. Right-click and select Format Cells.
3. On the Protection tab, as shown in Figure 14-6, deselect the Locked check box.
4. Click OK to apply the change.

Applying worksheet protection

After you’ve selectively unlocked the necessary cells, you can begin to apply worksheet protection. Just follow these steps:

1. To open the Protect Sheet dialog box, click the Protect Sheet icon on the Review tab of the Ribbon; see Figure 14-7.

2. 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.

3. 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.

4. If you provided a password, reenter the password.
5. Click OK to apply the worksheet protection.

Protecting sheet elements and actions

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:

• Select Locked Cells: Allows or prevents the selection of locked cells.
• Select Unlocked Cells: Allows or prevents the selection of unlocked cells.
• Format Cells: Allows or prevents the formatting of cells.
• Format Columns: Allows or prevents the use of column formatting commands, including changing column width or hiding columns.
• Format Rows: Allows or prevents the use of row formatting commands, including changing row height or hiding rows.
• Insert Columns: Allows or prevents the inserting of columns.
• Insert Rows: Allows or prevents the inserting of rows.
• Insert Hyperlinks: Allows or prevents the inserting of hyperlinks.
• Delete Columns: Allows or prevents the deleting of columns. Note that if Delete Columns is protected and Insert Columns is not protected, you can technically insert columns you then can’t delete.
• Delete Rows: Allows or prevents the deleting of rows. Note that if Delete Rows is protected and Insert Rows is not protected, you can technically insert rows you then can’t delete.
• Sort: Allows or prevents the use of Sort commands. Note that this doesn’t apply to locked ranges. Users can’t sort ranges that contain locked cells on a protected worksheet, regardless of this setting.
• Use AutoFilter: Allows or prevents the use of Excel’s AutoFilter functionality. Users can’t create or remove AutoFiltered ranges on a protected worksheet, regardless of this setting.
• Use PivotTable Reports: Allows or prevents the modifying, refreshing, or formatting of pivot tables found on the protected sheet.
• Edit Objects: Allows or prevents the formatting and altering of shapes, charts, text boxes, controls, or other graphics objects.
• Edit Scenarios: Allows or prevents the viewing of scenarios.

Removing worksheet protection

Just follow these steps to remove any worksheet protection you may have applied to your worksheets:

1. Click the Unprotect Sheet icon on the Review tab.
2. If you specified a password while protecting the worksheet, Excel asks you for that password; see Figure 14-9. Type the password and click OK to immediately remove protection.

Protecting the workbook structure

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:

1. To open the Protect Structure and Windows dialog box, shown in Figure 14-10, click the Protect Workbook icon on the Review tab of the Ribbon.

2. 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:

   • Viewing worksheets you’ve hidden
   • Moving, deleting, hiding, or changing the names of worksheets
   • Inserting new worksheets or chart sheets
   • Moving or copying worksheets to another workbook
   • Displaying the source data for a cell in a pivot table Values area or displaying pivot table Filter pages on separate worksheets
   • Creating a scenario summary report
   • Using an Analysis ToolPak utility that requires results to be placed on a new worksheet
   • Recording new macros

   Choosing the Windows option prevents users from changing, moving, or sizing the workbook windows while the workbook is open.

3. If you provided a password, reenter the password.
4. Click OK to apply the worksheet protection.

Linking Your Excel Dashboards to PowerPoint

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.

This technique of linking Excel charts to PowerPoint is ideal if you aren’t proficient at building charts in PowerPoint. Build the chart in Excel and then create a link for the chart in PowerPoint.

Creating a link between Excel and PowerPoint

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:

1. Open the Chapter 14Samples.xlsx file.
2. Click the chart to select it and press Ctrl+C on the keyboard to copy the chart.
3. Open a new PowerPoint presentation and place the cursor at the location that you want to display the linked table.

4. 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.

5. Select the Paste Link radio button and choose Microsoft Excel Chart Object from the list of document types.

6. 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.

Manually updating links to capture updates

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:

1. 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.

2. 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.

3. 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.

Automatically updating links

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:

1. In PowerPoint, click the File button to get to the Backstage View.

2. 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.

3. 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.

Distributing Your Dashboards via a PDF

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:

• Distributing your reports and dashboards as PDF files allows you to share your final product without sharing all the formulas and back-end plumbing that come with the workbook.
• Dashboards appear in PDF files with full fidelity, which means that they appear consistently on any computer and screen resolution.
• PDF files can be used to produce high-quality prints.
• Anyone using the free Adobe Reader can post comments and sticky notes on the distributed PDF files.
• Unlike Excel’s security, the security in a PDF is generally better, allowing for multiple levels of security, including public-key encryption and certificates.

To convert your workbook to a PDF, follow these simple steps:

1. Click the File button and then choose the Export command.

2. 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.

3. Click the Options button, as demonstrated in Figure 14-20.

4. 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.

5. Click OK to confirm your selections.
6. Click Publish.

Distributing Your Dashboards to OneDrive

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

• View and edit your workbooks from any browser, even if the computer you’re using doesn’t have Excel installed.
• Provide a platform where two or more people can collaborate on and edit the same Excel file at the same time.
• Share only specific sheets from your workbook by hiding sheets you don’t want the public to see. When a sheet in a published workbook is hidden, the browser doesn’t even recognize its existence, so there is no way for the sheet to be unhidden or hacked into.
• Offer up web-based interactive reports and dashboards that can be sorted and filtered.

To publish a workbook to OneDrive, follow these steps:

1. 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.

2. Sign in to your OneDrive account.

   After you sign in, the Save As dialog box shown in Figure 14-23 appears.

3. 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.

4. 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.

5. 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:

• Copy the web link from the browser address bar and email the link to your cohorts.
• Click the File button in the web version of your file, choose Share, as shown in Figure 14-25, and then click the Share with People command to send an email to anyone you specify.
• Use the Embed command on the same Share pane to generate HTML code to embed your workbook in a web page or blog.

Limitations When Publishing to the Web

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:

• Data Validation doesn’t work on the web. This feature is simply ignored when you publish your workbook to the web.
• No form of VBA, including macros, will run in the Excel Web App. Your VBA procedures simply will not transfer with the workbook.
• Worksheet protection will not work on the web. Instead, you need to plan for and use the Browser View options demonstrated earlier, in Figure 14-23 and 14-24.
• Links to external workbooks will no longer work after publishing to the web.
• Array formulas work on the web, but you can’t create array formulas while editing a workbook online. You need to create array formulas before publishing to the web.
• You can use any pivot tables with full fidelity on the web, but you cannot create any new pivot tables while your workbook is on the web. You need to create pivot tables in the Excel client on your PC before publishing to the web.
• You can create simple charts in the Excel Web App, but not all customization and formatting options are available on the web. Although you have a limited set of chart formatting options on the web, any chart created before publishing will retain all of its original look and feel.