Chapter 12
Plunging into Excel Online

Excel Online is a version of Excel that is part of the Office 365 offering. This version of Excel runs as a web application in your web browser. Working with Excel as a web application has a number of benefits, including the simple fact that you don’t need to have the full version of Excel installed on the computer you are using to edit your document. All you need is a web browser and access to your SharePoint site, OneDrive, or another cloud-based storage location (such as Dropbox). You might be on a cruise or using a computer in a café or working from a shared computer at your organization. You can still work with your spreadsheets without having to have Excel installed on the computer you are using.

In this chapter, you explore some of the basic, as well as the more advanced, features of Excel Online and discover how it differs from the traditional version of Excel.

Comparing Excel Online and Excel

Microsoft Excel is one of the most popular data analysis tools on the planet. Using Excel, you can enter numbers into a spreadsheet and use functions to manipulate them and perform analysis. In addition to analyzing numbers, Excel is often used for tracking and managing other data, such as customers, for example. In many organizations Excel has turned into a database-type application where all types of information is stored. Excel is what is known as a thick client in that it runs from your local computer. In Windows 10, you click Start All Apps. Then you browse to your Microsoft Office applications and you click Excel to fire up the program. Excel then runs on your computer. A web-based application, on the other hand, runs on a computer in a data center that you access over the Internet. If you use Outlook. com or Gmail for email or browse a web page, then you are using a web application. The way you access a web application is by using a web browser, which is a program installed on your computer.

You will find that Excel Online is still Microsoft Excel but it does have some differences. For one thing, Excel runs on your computer, and Excel Online runs out in the cloud and you access it by using your web browser.

When you create a document on your local computer with Excel, that document traditionally has been stored locally on your computer. When you click the Save button to save a document, you are prompted for the location in which to save the file. You may save the file to a cloud-based storage location (such as SharePoint, OneDrive, or Dropbox) or to your Desktop or any other folder on your local computer. In any case, your creation is a physical file located in cloud-based storage or on your local computer. When working with Excel Online, however, you do not have a local physical file. When you create a document and save it, your document lives on a computer in one of Microsoft’s data centers. Because you don’t know exactly which computer in which Microsoft data center, you can just say that the document lives out in the cloud. In the case of Office 365, your document lives within a SharePoint Online document library app.

TIP

You don’t have to be a paid subscriber to Office 365 to work with Excel Online. You can use it for personal use for free with a free Microsoft Office 365 account or Outlook.com account.

Covering the Basics

Working with Excel Online is easy after you find your way around the interface. If you have used the traditional Excel application, then you can recognize that it is extremely similar and you won’t have any trouble at all using Excel Online. If you‘ve never used Excel, then you are in for a treat with Excel Online.

Using the Excel Online interface

The Excel Online interface is different from the traditional Excel application in that the web app runs within your web browser. The Ribbon at the top of the Excel Online interface screen contains tabs, such as Home and Insert.

The Home tab contains common functionality in groupings, such as Clipboard, Font, Alignment, Number, Tables, Cells, and Editing, as shown in Figure 12-1.

FIGURE 12-1: The Home tab on the Excel Online Ribbon.

Table 12-1 describes the sections of the Home tab on Excel Online Ribbon.

TABLE 12-1 Features of the Home Tab

The Insert tab allows you to insert objects into your Excel Online document, such as functions, tables, add-ins, charts, links, and comments.

A function performs some calculation, such as summing the values of cells. There are many different functions to choose from in Excel Online. Many would say that the functions are what make Excel so valuable. Adding functions is explored in this chapter.

A table allows you to manipulate data with functionality, such as sorting the data in ascending or descending order or filtering the data based on specific criteria.

An Office add-in is like an app that extends the Excel environment. There is an online store that lists all of the Excel Online add-ins. You access the store by clicking the Office Add-Ins button in the Ribbon of the Insert tab.

A chart is a visualization of data. You can insert many different types of charts into your spreadsheet. Some of the most common types include bar charts, column charts, line charts, and pie charts.

A hyperlink allows you to create clickable text that, when clicked, opens up a new website. For example, you can create a hyperlink with text that says “Learn More” — clicking it takes the viewer of the Excel Online document to a news article.

The comment field allows you to insert a comment into the spreadsheet. A comment is metadata because it isn’t part of the actual data in the spreadsheet. The comment is data about the data in the spreadsheet, which is why it’s called metadata. Comments are useful so that you can leave notes about the spreadsheet without actually modifying the spreadsheet data.

The Insert tab is shown in Figure 12-2.

The Data tab includes data specific functionality, such as connections to data, calculations about data, and sorting of data. After the Data tab is the Review and View tabs. These tabs are designed to provide you various views into your spreadsheet. On the Review tab, you can view and edit comments. On the View tab you can switch between the Editing Mode and the Reading View. The Reading View gives you a preview of how the spreadsheet will look when printed. If you’re just reviewing a spreadsheet and not editing it, the Reading View can be much less distracting, which will let you focus on the contents instead of the editing details. The Reading tab is also where you can turn on and off grid lines and headings.

FIGURE 12-2: The Insert tab on Excel Online Ribbon.

In addition to the Ribbon tabs, the interface also includes a File menu. The File menu allows you to perform functionality, such as saving the document with the same or a different name, opening the document in the traditional Excel application located on your computer, downloading a snapshot of the document or a copy of the document to the local computer. The File menu is shown in Figure 12-3.

FIGURE 12-3: The File menu on Excel Online interface.

Working with workbooks

Creating a new Excel Online document in a SharePoint document library app is easy. On the header of the document library app, click the New button and then select Excel Workbook, as shown in Figure 12-4.

FIGURE 12-4: Creating a new Excel document in SharePoint.

When you create a new document, SharePoint is smart enough to create the document and place you in Excel Online in edit mode. If you already have Microsoft Excel installed locally on your computer, then you can switch to the Excel client by clicking the Open in Excel button in the Ribbon. This will open up the document in the full-featured application. If you don’t have Excel installed locally, you can continue to work on the spreadsheet with your web browser and Excel Online.

After you have finished developing your spreadsheet, you can save it, which will automatically save it to the document library app in which you created it. You can then click on the document to view it and then edit it further by using either Excel Online or the local Excel application running on your computer.

Editing Mode and Reading View

There are times when you do not need to edit a document. For example, you might want to just view the latest spreadsheet report or show a colleague a set of data. Excel Online contains two different modes. When you are editing the document you are using Editing Mode. When you want to read the document, you can simply click it in SharePoint to open it and view it. This is called Reading View, which looks very similar to a document that is printed on paper. A document in Reading View is shown in Figure 12-5.

FIGURE 12-5: An Excel document in Reading View in Excel Online.

Using Advanced Features

In addition to the basic features that you will use in Excel Online, there are also some advanced features. In particular, you can work with formulas and functions, manipulate data, and even coauthor spreadsheets in real time in the cloud.

Adding functions

One of the primary reasons for the popularity of Excel as a data analysis tool is the seemingly endless supply of functions. A function is a bit of logic that performs some calculation or manipulates data in a certain way. For example, you may want a cell to display the addition of two other cells. You can use a simple plus (+) sign to accomplish this addition. Going farther, however, you might want a cell to display the current time. You can use a function, such as Now(), which would display the current time. The power of this function lies in the fact that Excel updates the time each time it recalculates the spreadsheet.

To enter a function in a cell, enter the equal (=) sign followed by the function. For example, to enter the Now() function, you type =Now(), as shown in Figure 12-6.

FIGURE 12-6: Using the Now() function in Excel Online.

TIP

To insert a function you can also use the Insert tab and then click the Function button to insert a function. This will provide you a list of functions in case you don’t know off hand which function you want to use.

After you finish entering the function and press Enter, you will see the current time rather than the =Now() function in the cell. This simple yet powerful functionality is what lets users create very valuable and complex spreadsheets with minimal training.

TIP

If you are following along, notice that as you begin to type the function, Excel Online automatically starts to show you all the functions and narrows in on the list of possible functions as you continue typing. This feature is useful when you cannot remember the exact name of the function but remember it starts with a specific letter.

An excellent list of the available Excel Online functions listed alphabetically or by category is available on the Microsoft Office website. To find the functions, open your browser and navigate to http://support.office.com. Then search for “excel functions” in the search tool. There are many different functions and spending some time looking through them can save you a lot of time in the future when you are crunching data.

Manipulating data

The ability to manipulate data is a staple of Excel and continues in Excel Online. You can manipulate data by using functions or by creating your own formulas. Functions exist for manipulating numeric data and also text data. You can dynamically link the contents of a cell to other cells. For example, you might have a column for sales and a column for costs and then a third column that denotes profit by subtracting the costs column from the sales column. Using functions and mathematical equations, with nothing more than your web browser, you can quickly whip data into shape by using Excel Online.

Coauthoring workbooks in the cloud

One of the exciting features found in Excel Online is the ability to coauthor spreadsheets with others in real time and at the same time. For example, imagine that you are in Seattle and your colleague is in Manila. You can edit the same document in real time by using the browser. When your colleague enters text or numeric data, you see it appear on your screen. Coauthoring allows for a much more productive experience because you are both editing the same document, which maintains a single version of the truth.

With a spreadsheet open in Excel Online, you can see the other users who are currently editing the document in the lower-right hand corner of the screen. For example, if two people are editing, you will see text that says “2 People Editing.” If you click on this text, you will see the two users who are currently editing the spreadsheet.

When one of the users makes change to the document, everyone who is currently viewing the document will see the changes take place in their view as well. This turns out to be an extremely useful feature because the new changes do not have to be emailed to other people in order for them to see the most recent version of the spreadsheet. The spreadsheet only exists in one place, so there is only one version of the truth for this spreadsheet.