The first version of Office Online, released in summer 2007, was called Office Web Apps. Office Online is a browser-based limited version of the Office suite of applications. Office Online provides the basic functionality of Office, and you can access it on any device, with any operating system, as long as you have an Internet connection and use a compliant browser.
The Office Online application is hosted on Microsoft servers. The advantage of using it is that you need nothing but a browser; you don’t have to install an application, and users do not have to worry about updating the application or applying hotfixes. Similar to Google Spreadsheets and Google Docs, Office Online is ideal for students and those not wanting to pay a fee for the desktop version of Office.
Office Online is compatible with most major browsers:
- Internet Explorer (7 or later)
- Firefox
- Chrome
- Safari
TIP
We recommend using the latest available version of your preferred browser. Office Online makes use of many of the latest features implemented in browsers, so it’s important that you use the latest browser version available and keep it up-to-date to make the most of your Office Online experience.
This chapter refers to two modes of Office:
- Office Online/Excel Online—In this case, the Office application runs on a server as a hosted service and is used with a browser.
- Office Desktop/Excel Desktop—In this case, the Office application is installed and runs on a workstation.
With a subscription to Office 365, you can access a version of Office Online that you can install on a private network. It functions the same as Office Online run from Microsoft servers (that is, it runs within a web browser), but the service is hosted on servers within a company’s firewall, thus avoiding security issues that can arise when using public services.
To create an Excel workbook on OneDrive (https://onedrive.live.com), you select Create | Excel workbook, as shown in the next figure. A free Microsoft account is required to log in. If you have a hotmail.com,
outlook.com, or Xbox Live account, you can use those credentials to log in to OneDrive. When you create a new OneDrive account, you can use any valid email address.
Create a new Excel workbook in Excel Online.
The workbook appears in Office Online (in your browser), and it looks much the same as it does in Office Desktop, with the ribbon displaying the familiar tabs FILE, HOME, INSERT, DATA, and VIEW. However, you also see Excel Online displayed at the top of the window, as shown in the next figure.
A blank workbook in Excel Online.
A drop-down control to the right of the Excel Online title provides quick access to the Office Online applications. When you click it, a gallery of Office Online applications appears, as shown in the next figure. Options include Outlook.com (previously known as Hotmail), People (that is, contacts), Calendar, OneDrive, Word Online, Excel Online, PowerPoint Online, and OneNote Online.
Office Online quick switch menu.
Excel Online displays Tables the same way as Desktop Excel. However, not all the functionality available in Excel Desktop is available in Excel Online. In particular, the contextual TABLES ribbon tab is not available. Another difference is that although you can use the INSERT tab to create a Table in Excel Online, you cannot change a Table’s name in Excel Online.
Keyboard shortcuts are different, too. This is primarily due to the fact that the browser in which Excel Online is running already exposes a number of keyboard shortcuts. For example, CTRL+T, which creates a Table in the (US English) desktop version of Excel, typically opens a new tab in a browser.
|
TIP For a list of keyboard shortcuts that work in Excel Online, visit http://office.microsoft.com/en-us/office-online-help/keyboard-shortcuts-in-excel-online-HA010378329.aspx. |
What Is Available in Excel Online
If a workbook has any unsupported objects—such as shapes, comments, or ink—you cannot edit it in Excel Online. The next figure shows the dialog box you get when you attempt to open such a workbook. If you click Edit in the browser, Excel Online creates a copy of the workbook, with all the unsupported objects removed. If you later open this newly copied version in desktop Excel, those features are still removed permanently.
Excel Online error message when you attempt to edit a workbook that contains unsupported features.
If you click Cancel, Excel Online displays the workbook, but you cannot edit it. It displays a yellow bar directly under the ribbon to remind you that you can’t make changes to the workbook, as shown in the next figure.
Unsupported features banner in Excel Online.
Another issue with Excel Online is that Tables that are connected to external data (for example, SQL Server) cannot be refreshed. Instead, Excel Online shows the last data pulled with a refresh and then saved using Excel Desktop.
Slicers and Timelines are available in Excel Online and work just as they do in Excel Desktop. However, you cannot create or modify Slicers and Timelines in Excel Online.
Excel Online also offers the distinct advantage of allowing you to view and interact with a workbook that contains a Data Model (for example, Power Pivot). If the file resides on OneDrive, Excel Online does allow you to interact with the Data Model components (Slicers and Timelines). To allow interaction with the Data Model, the file has to reside on a SharePoint server with BI capabilities or a SharePoint Server 2013 with Excel Services. If you want to refresh any external data connections, the file has to reside on SharePoint Server 2013. These are some examples of data sources you can use when a workbook resides on either of these servers:
- Windows Azure SQL Database Tables
- SharePoint lists that are available as OData feeds
- Windows Azure Marketplace data
- External data through Business Connectivity Services
- SQL Server data in Power BI Admin Center (subscription required)
CAUTION
Excel Online imposes file size limitations for viewing files. If you’re using an Office 365 subscription that includes SharePoint Online, the file size limit is 10 MB. If your Office 365 subscription includes Power BI, the limit is extended to 250 MB. If you’re opening files from Outlook.com, the limit is 5 MB.
There continues to be some confusion about what is and what is not available in Excel Online. Below is a list of some functionalities that are currently available in Excel Online:
- Real-time co-authoring
- Worksheet protection
- Data validation
- Cell drag-and-drop
- Workbook renaming
- Ability to embed a spreadsheet on a web page
- Column AutoFitting
- Printing to HTML
- PivotTable field lists
- Query Tables
- Sharing by email/link
- Sheet hiding/unhiding
- Sorting
Table formulas and structured references render normally in Excel Online. However, there are a few fundamental differences between creating them in Excel Online and in Excel Desktop. This can lead to some confusion, but no matter where they’re created, these calculations work as expected in all versions of Excel. The following are differences between Excel Online structured formulas and those created in Excel Desktop:
- Excel Online does not provide IntelliSense for Table references. IntelliSense works with Excel Online formulas in general, but it does not display any Table structured references.
- In Excel Online, you cannot insert structured references by clicking on parts of the Table. In Desktop Excel, when you select Table parts while editing a formula, Excel enters the respective Table structured reference—for example, =[@Column1]—but Excel Online does not do this. Instead, it inserts A1-style addressing. You can enter structured references manually, however, and it will work all the same.
- Although you can’t enter array formulas into a cell in Excel Online, Excel Online does calculate preexisting array formulas correctly.
Filtering is available in Excel Online but is a more manual process than in Excel Desktop. In Excel Desktop, the AutoFilter displays a list of unique values. In Excel Online, the AutoFilter drop-down list does not display a list of unique values. Sorting and filtering options include Sort Ascending, Sort Descending, Clear Filter from ‘Worksheet’ (if a filter is applied), a Filter command that opens a dialog box of unique values, and Number Filters, which provides the following options:
- Equals
- Does Not Equal
- Greater Than
- Less Than
- Between
- Top 10
- Above Average
- Below Average
- Custom Filter
Filter drop-down in Excel Online.
Number Filter submenu in Excel Online.
To set a custom filter on a column, you click the AutoFilter drop-down button. Depending on what type of data that column contains, you either see a Text Filters button, a Date Filters button, or a Number Filters button. At the bottom of this menu, choose Custom Filter to open the Custom Filter dialog box. As shown in the next figure, this dialog box in Excel Online provides only one condition (instead of than the two in Excel Desktop).
Custom Filter dialog box in Excel Online.
Custom Filter options in Excel Online.
Excel Online allows you to easily create customized surveys that can be distributed to anyone who has a browser and Internet connectivity. Survey data captured with a survey is saved to OneDrive, where the results are published using a Table in an Excel file. Multiple users can take the same survey at one time.
To create a survey within an existing workbook, select INSERT | Survey | New Survey or, in OneDrive, select Create | Excel survey. When the survey is created, additional options are displayed in the Survey menu, as shown in the next figure.
Excel Online Survey menu.
The Edit Survey dialog box, like the one shown in the next figure, appears. In it you can enter a title and description for the survey.
Edit Survey dialog box.
After you enter a title and description, you can click Add New Question to open the EDIT QUESTION dialog box, shown in the next figure, for defining a new question. Enter the question text, a subtitle (shown in smaller font below the question), the response type, whether it is a required question and must be answered before submission, and the specific answer requirements. There is effectively no limit to the number of questions in a survey.
Survey EDIT QUESTION dialog box.
These are the possible Response Type options:
- Text—A single-line text box.
- Paragraph text—A larger text box that allows multiple lines of text.
- Number—A text box that allows entry of numbers only. Choose a desired number format. For fixed decimal, percentage, and currency types, choose the number of decimal places.
- Date—A text box that allows entry of valid dates only. Excel Online accepts dates in the format determined by the survey taker’s locale settings. Dates entered are stored using Excel’s internal universal format so that, when the Table containing the survey results is viewed, all the dates are displayed using the viewer’s locale settings.
- Time—A text box that allows entry of valid times, without or with seconds.
- Yes/No—A combo box with Yes and No choices.
- Choice—A combo box with a custom list of choices. When you enter choices, press ENTER to separate the items.
When you’re done entering the questions and defining the responses, Excel Online creates a Table on a new worksheet titled “Survey1”. The number appended to the Table name increases with each survey added to the workbook. The survey results are appended to the Table, one row per survey completed.
A Table based on a survey.
To help users filling out a survey, Excel shows examples of specific formats in text boxes where formats have been specified. For example, a text box that accepts only dates shows the text “Example: 10/21/2015”. The text disappears as soon as the user types a value in the box. The next figure shows two text boxes, one for a date and one for a time, with Excel’s examples.
Default view of date and time entry types.
If a survey taker doesn’t enter a valid value and moves on to another question, the skipped question and response field are set in a light red background, as shown in the next figure. Regardless of whether the question is marked as required, the survey taker will not be able to submit the survey if the answer is not in a valid format. For an answer that is not required, clearing the answer field allows the survey to be submitted.
Entered survey data that doesn’t match the required format.
When the survey taker attempts to submit a survey with incorrect or missing values, Excel displays any errors preventing the submission on the survey form, as shown in the next figure.
Error message informing the user that the data entered must be a date or time.
Once you complete a survey definition, you share it by clicking the Share Survey button and then clicking the Create link button shown in the next figure.
Click Create link to generate a sharable link to the survey.
Excel generates a unique link for the survey and displays it for you, as shown in the next figure. If you click the Shorten link button, Excel gives you a shorter link as opposed to a hyperlink to the actual file. Both links work identically. Anyone who uses a supported browser and follows the link can take the survey. Survey takers do not need to have a OneDrive account.
Click Shorten link to get a shorter link provided by OneDrive.
When users take your survey, at the bottom is a Submit button, as shown in the next figure. After the user clicks this button, Excel provides a message saying that the response was received. Excel then puts the answers to the questions into the Table in the Excel file created or used to create the survey.
Survey takers click the Submit button at the bottom of the survey.
After a user submits survey responses, Excel tells the user the responses have been received.
Because the survey results are stored in an Excel Table, the results can easily be analyzed in place or with a PivotTable or PivotChart.
Updates
A significant advantage with Excel Online is that users never have to worry about having the latest update installed. Microsoft keeps the Office Online servers constantly updated with the latest versions, security patches, etc.
|
TIP The best way to stay informed about what has been updated in Excel Online, or to generally follow its progress, is to follow the Office blog at http://blogs.office.com. |