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.
NOTE
In February 2014, Microsoft changed SkyDrive to OneDrive. This is Microsoft’s cloud-based storage service, which ties together the features of Office Online and Outlook.com and gives consumers free availability to these 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 Functionality
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
Formulas
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
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.
Surveys Using Excel
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.
11 Tables on Mac, iPad, and Office Mobile
At this writing, Microsoft supports versions of Office for Mac, iPad, and smart phones (iPhone, Windows Phone, and Android).
Excel 2011 was the first Mac version to offer Tables. Excel 2008 for Mac (like Excel 2003 for Windows) used the term Lists for what it now calls Tables. Though there are some fundamental differences between the Mac and Windows versions, most of the functionality is the same. In 2013 Microsoft released a version of Office, Office Mobile, for iOS (Apple's operating system for mobile platforms) and for Windows Phone and Android. In March 2014 Microsoft released Excel for iPad, along with Word and PowerPoint.
The various Office versions have many unique functional differences compared to each other and the Windows desktop version. This chapter discusses the differences in Table functionality provided by each of these versions. In the rest of this chapter, we refer to Excel 2011 for Mac as the Mac version, Excel for iPad as the iPad version, and Excel 2013 for Windows as the Windows version.
Tables in Excel for Mac
This section discusses the major differences between Tables in the Mac version and Tables in the Windows version, as well as user interface differences.
Tables Tab
As shown in the next figure, in the Mac version, the Tables ribbon tab is always present and is not contextual, as it is in the Windows version. There are some slight differences in the controls on the Tables tab in the Mac version. Because it is not contextual and is always present, there is a New button on the far left; this button is on the INSERT tab in the Windows version. Also, there is no Filter Button control, as there now is in the Windows version. Most Table operations—such as inserting rows and columns, deleting rows and columns, removing du
plicates, using a Table as source data for a PivotTable, converting to a range, and renaming a Table—are available in both the Mac and Windows versions. One minor difference is that the Mac version gives you a command button rather than a text box for renaming a Table.
Tables ribbon tab in the Mac version.
The Mac version does not have anything equivalent to the Windows version's TABLE TOOLS tab group or DATA tab but, because the Mac version still has a menu bar, some of the commands on these two tabs are available in the Data menu, as shown in the next two figures.
Table Tools submenu of the Data menu.
Get External Data submenu of the Data menu.
Filtering
There are minor differences between filtering a Table in the Mac version and in the Windows version. When you click the AutoFilter button for a column, the Result floating dialog box appears, as shown in the next figure. The Result dialog box stays open while you enter and change the filtering criteria, and the Mac version instantly applies the criteria to the Table. (On the other hand, in the Windows version, you must click OK to close the AutoFilter pop-up and perform the action.) The Result dialog box stays open until you close it by clicking the close control in the upper-left corner or by clicking any cell on the worksheet.
Result dialog box for a Table in the Mac version.
NOTE
Slicers are not available in the Mac version.
Tables in Excel for Office Mobile
Office Mobile is often confused with Excel Online or Excel for iPad. But Office Mobile is an app that is available only for smart phones. The app is free to download and install, and you can use it to view files without paying a service fee. However, to edit files in Office Mobile, you must have one of these Office 365 subscriptions:
Excel Tables Page 16