28. Excel Online

You might think that Excel Online is simply a way to view and edit your workbooks in a browser. Although this is one use for it, there are far more useful things you can do with Excel Online. Here are some of the benefits:

If you are stuck using the computer in the hotel business center and they do not have Excel, you can successfully open the workbook in a browser, make edits, and save it back to your OneDrive so someone at work can use the updated version.

Have two people—or an entire team—editing the same Excel worksheet from different computers, at the same time.

Share a range of your workbook over the Web, with interactivity, yet protect all your formulas and intellectual property. Others can’t hack in and unhide your hidden worksheets, because as far as the browser knows, the hidden worksheets aren’t even there.

Create a quickie web calculator. People reading your blog or web page can enter some input cells, and then your Excel formulas and charts update.

Build a survey in Excel and publish the survey page. All of the results are posted into your workbook on OneDrive.

The list of things that Excel Online cannot do is getting smaller every four months. If you use 80% of Excel’s features, it is possible to do everything you need to do in a browser instead of Excel. There are still a few exceptions: You cannot enter a new array formula in Excel Online. But Excel Online will calculate an array formula entered on a PC and then opened in Excel Online.

Either sign in to Live.com or to your Office 365 site, find the file on OneDrive, and click it. Initially, the file is rendered in the browser (see Figure 28.1). At this point, you can view the data but not change anything. To edit the workbook, choose Edit, In Excel Online.

When you choose to edit in the browser, a subset of the Excel ribbon appears. You have tabs for File, Home, Insert, Data, Review, and View. Working in the browser is remarkably similar to working in Excel. You can navigate the cells just as you do in Excel. The fill handle works. Formulas work, and even referring to cells in a formula with either the mouse or the arrow keys works.

Comment in cell D1

Hyperlink in B2

Strikethrough in D2

Image in G1

Sparkline in A3

Conditional formatting rules in F4

Pivot table in A6

Slicer in H6

GETPIVOTDATA formulas in G15

Data bars in G15:G16

Icon sets in I15:I16

Chart in F17

Figure 28.3 shows the workbook in Excel Online. It does remarkably well. Several features are working that were not working in the 2013 edition of this book.

Data validation now works.

If you select a cell in the pivot table, a field list appears where you move fields around the pivot table.

You can insert a chart in Excel Online, move the legend, and add a data table.

Comments now appear, although the indicator appears as a speech bubble instead of a red triangle (see Figure 28.3).

AutoComplete works.

You can enter GetPivotData functions on the Web.

Excel Online still has a list of limitations. In many cases, though, Excel Online renders a feature, but it doesn’t let you create that feature on the Web. Thus, it is best to create your workbook in Excel and then upload to the OneDrive. The following list describes some limitations of the Web app:

You cannot create new pivot tables on the Web, but pivot tables that you create in the Excel 2016 client render on the Web. You can add or remove fields from existing pivot tables.

You do not have the full range of chart editing on the Web. You can create simple charts. Charts created in the client render on the Web.

Generate GetPivotData is not functional on the Web, but =GetPivotData functions that you create in the Excel client work on the Web.

You cannot enter array formulas on the Web, but array formulas that you enter in the Excel client work.

Some features do not work on the Web:

Links to external workbooks do not work on the Web.

VBA macros do not run in Excel Online.

Worksheet protection does not work on the Web.

Hiding columns is now possible in Excel Online, but the process is different. Drag the column width to zero to hide a column.

One missing command is File, Save. Every edit you make in the browser is automatically saved every two minutes to your OneDrive. This way, if the Internet connection goes down, your changes are always saved.

If you save your workbook to OneDrive and then share an editing link, you can have multiple people editing the workbook at the same time (see Figure 28.4).

To get a sharing link from the Excel client, use File, Share, Get a Sharing Link. Choose whether you want to create a read-only link or an editing link. The URL will be impossibly long, so use a URL shortener, such as Bit.ly.

Suppose that you are editing the workbook in Excel Online. When someone else starts editing, a notifier in the top right lets you know someone else has arrived. Each person is indicated by a different color. Joe might be red; Mary might be blue. Three cell pointers are visible: your cell pointer, and then a red one for Joe and a blue one for Mary. This helps to prevent two people from editing the same cell at the same time.

As another person edits a cell, the new text appears on your screen within seconds.

Here is the best part: You can protect your intellectual property. You can choose to publish Sheet1 in the browser and not show other worksheets. The formulas on Sheet1 reach back to use information on Sheet2, but no one is able to hack in and unhide Sheet2. They aren’t able to see your formulas.

To adapt the earlier Loan Calculator workbook to create a web page, you can do these tasks:

Add a new worksheet. I called the worksheet Hidden just to help me remember which worksheet won’t be seen.

Cut anything that does not need to be seen from the first worksheet and paste it to the Hidden worksheet.

Consider whether any input cells can be changed to a slicer. Slicers are excellent for selecting values in a web page. The Interest Rate slicer in Figure 28.5 is tied to a simple six-row data set and pivot table on the Hidden worksheet. The Interest Rate cell is now a formula that pulls the first value from the pivot table. When someone chooses from the slicer, the interest rate tied to that type of loan is fed into the interest rate field. The Term slicer is from a second pivot table tied to the Interest Rate slicer. It ensures that only valid terms are offered for each loan type.

Take a few steps to make your worksheet not look like Excel. On the View tab, uncheck Formula Bar, Gridlines, and Headings.

Figure 28.5 shows the workbook in the Excel client. This is the first worksheet. Most of the data is on the Hidden worksheet.

To allow certain fields to be entered on the web page, you have to define a named range for each cell. Click on the Amount Financed entry cell. Click in the Name box to the left of the formula bar. Type a name without spaces, such as AmtFinanced.

You need to control what is shown in the browser. Open the File menu and choose Info from the left navigation area. Click Browser View Options in the center pane.

In the Show tab, open the drop-down. Change Entire Workbook to Sheets. You can then uncheck the Hidden worksheet.

In the Parameters tab, click the Add button. Excel shows you a list of all single-cell names in the workbook. Choose the AmtFinanced name and the slicer.

Save the workbook to your OneDrive account. You should test the workbook before sharing it. Make sure that the parameters work and that everything looks correct. When you are signed in to OneDrive and open your own workbook, it might automatically open in Edit mode. Go to the View tab and choose Reading View.

Figure 28.6 shows the workbook in the browser. If you click on a slicer, the interest rate changes, all the formulas on the hidden worksheet update, the calculated cells in the browser update, and the chart updates.

To edit one of the input cells, the user types new values in the Parameters pane and clicks Apply at the bottom of the pane. Again, the cells update, all the formulas calculate, the results display, and the chart changes.

Because you are reading this book, I bet you know a lot about Microsoft Excel. You can probably knock out amazing formulas that do all sorts of calculations. Now, with just the knowledge you’ve gained in this chapter, you can create amazing interactive web pages.

While you are viewing the workbook, use Share, Share with People.

The Share dialog offers three categories:

You can send an email inviting others to use the workbook.

You can post to Twitter or LinkedIn.

You can get a link that you can distribute.

To start, go to OneDrive and create a new Excel workbook. Open the Survey icon and choose New Survey.

A survey contains a title, a description, and then several question fields. Each question field has a question, a subtitle, and a data type. As you enter the information in the fields on the right, you can see a preview of the question on the left (see Figure 28.7).

The question data types include Text, Paragraph Text, Number, Date, Time, Yes/No, and Choice. The paragraph text choice provides a taller text box to enable you to type more information.

If you choose a data type of Choice, you can type values into the Choices box. The question appears with a drop-down menu.

When you are finished, click the Share Survey link at the bottom of the screen. Excel Online generates a survey link that is a mile long.

Frankly, no one will ever be able to type this entire link. Go to any URL shortener, such as Bit.ly or TinyURL.com, and convert the long URL to a short URL.

Distribute the link to others. Anyone who follows the link will see a web page like the one shown in Figure 28.8.

Here is the amazing thing: When someone fills in the survey web page, his answers are written to the next row in your OneDrive workbook! Go back to your OneDrive, open the workbook, and all the answers are there (see Figure 28.9). You can sort and filter right in Excel Online or download to Excel to perform further analysis.

To save a worksheet as a PDF file, select File, Export, Create PDF/XPS. You have the option to save the file in a high-resolution format suitable for printing or a low-resolution format that is suitable for viewing onscreen.

If you frequently work with PDF files, you might have noticed that some PDFs contain data that can be selected, copied, and pasted to Excel. Other PDFs contain strange formatting that causes the paste back to Excel to render horribly. You would think that a PDF file created by the Excel team would have the capability to paste back into Excel, but this is not the case. Try opening the PDF in Word. Copy the data from Word to Excel.