Excel is synonymous with spreadsheets, and for good reason. Decades after its introduction on the original Macintosh, Excel continues to be the most powerful spreadsheet app. It's not just a desktop app anymore, either. Today, you can use Excel on Windows, Mac, iOS, Android, and even the web.
That latter version is perhaps the most interesting. Microsoft Excel Online is a free, online version of Excel that includes the functions, chart tools, and more that you'd expect from a spreadsheet. Similar to Google Sheets, Microsoft Excel Online lets you collaborate with others in real-time, gather answers in an online survey, and crunch numbers with Excel functions. Most importantly, though, Excel Online now lets you connect your spreadsheet with other web apps to gather data and start automated workflows—and since it syncs with traditional Microsoft Excel, you can use the Online version to add automations to any spreadsheet.
If you've used Excel for years, switching to a web app may sound scary. Don't worry. In this guide, we'll walk you through everything you need to get your spreadsheets in the cloud, so you can gather data and collaborate—and still use Excel on your desktop.
- Microsoft Excel Online Basics
- Gather Data with an Excel Survey
- Share and Collaborate in Microsoft Excel Online
- Move Older Excel Files to the Cloud
- Extend Microsoft Excel with Add-ins and Integrations
What is Microsoft Excel Online?
Microsoft Excel Online—along with the rest of Microsoft's Office Online apps—are a new take on the traditional Office suite you've used for years. Typically, you'd buy a copy of Office for a few hundred dollars and install it on your computer. That's how Office has been sold for years, starting with the first version of Excel for Macintosh in 1986. Microsoft Office is so ubiquitous, its bundled apps including Word, Excel, and PowerPoint are synonymous with documents, spreadsheets, and presentations—they're the standard business apps across most industries.
Microsoft Excel Online is the free version of Excel that works in your browser. It's a companion app to Excel, designed to let you view and edit your Excel spreadsheets online. And yet, it's still a full spreadsheet app that supports nearly every Excel function and includes charting and survey tools to help you gather and visualize data. It's better than Google Sheets for working with existing Excel files or sharing spreadsheets with other Excel users, and at the low cost of free, is the cheapest way to use Excel.
You can use all of Excel Online's features for free, as long as you don't have more than 5GB of files in OneDrive, Microsoft's online file storage. Or, if you still want a copy of Office installed on your computer as well, the best option today is subscribing to Office 365, which gives you the most recent versions of Office for your computer along with file sharing in OneDrive. You can store all of your files in OneDrive, then edit any of the Office files online with Office Online. And—with the Business versions of Office 365, for now—you can use Microsoft Excel Online integrations to connect your spreadsheets to your favorite apps.
Here are the Office 365 options you can choose from:
- Office Online - Free: With a Microsoft account, you can use Office Online and save up to 5GB of files in OneDrive for free.
- Office 365 Personal - $6.99/month: Get Office apps on your computer, and 1TB of OneDrive storage.
- Office 365 Home - $9.99/month: Get Office apps for 5 people in your family, along with 1TB of OneDrive for each user.
- Office 365 Business - $8.25/month: Get Office on your work computer, with 1TB OneDrive for Business storage with professional integrations.
- Office 365 Business Premium - $12.50/month: Get Office on your work computer and 1TB OneDrive for Business storage, along with email and video conferencing.
No matter which account you have, you can use the all of Office Online—including Excel Online. So let's dive in.
Excel Online Basics
There are two easy ways to open Microsoft Excel Online: Go to products.office.com/office-online and click the Excel link to create a new spreadsheet, or open OneDrive and click on a spreadsheet you've saved online (we'll look at how to move your spreadsheets to OneDrive later on).
If you open Excel Online from Office.com, you'll see a variety of templates to start a new spreadsheet, along with a list of recently opened sheets on the left side. Click a template or the blank spreadsheet option to start making a new spreadsheet.
Alternately, if you open a spreadsheet from OneDrive or the Recent list, Excel will open it in an online viewer first. Click the Edit Workbook button then select Edit in Browser to open the spreadsheet in Excel Online. If you have Excel installed on your computer, you can select Edit in Excel to open the spreadsheet in traditional Excel and save the changes back to the web when you're done if you'd like, instead.
Microsoft Excel Online looks much like you'd expect. Your data is organized in rows and columns, with tools and features included in an Office ribbon toolbar. On the bottom right, the standard Excel status bar is included, with quick sums and averages of the cells you've selected.
Just click in a cell and type to start adding data to your spreadsheet. Or, paste in data from your computer or other spreadsheets, with the standard options to paste the formulas, values, or formatting.
To change the formatting, just click the toolbar buttons at top. You can tweak fonts, colors, text alignment, and number formats, or choose to wrap text and merge cells. Right-click to insert rows—or click the Insert button at the top for more detailed options. There's also Sort and Find tools to keep your data organized.
You should be able to find the tools you need quickly just by looking through the toolbars. Or, just click the search box at the top and start typing—Excel Online includes a handy tool to help you find the tool you need with search. That's also where you'll find Excel's documentation to help you learn how to use each tool.
Functions
There is one spot where Excel Online is full featured: Functions.
Excel 2016 includes 471 functions, including 11 new ones just added to this version (along with 51 functions added in 2013, and 57 added in 2010)—and they're all included in the Excel Web App.
There are only 411 functions listed in the Excel Web App's Functions menu, but it actually supports all but 3 built-in Excel functions: SQL.REQUEST, JIS, and EUROCONVERT. Everything else is supported; even if the function isn't shown in the auto-complete dropdown, it'll still work once you enter it correctly.
To add a function, just type = followed by your function name. Excel will auto-complete the function name—press the Tab key to accept the suggested function—and then give you tips on what data you should include in the function. Or, you can look through the included functions by clicking the fx button right under the toolbar.
You can click cells to add them to your function, just as in other versions of Excel. And, once you've added a function to a cell, just select it and drag down to add the function to all of the other cells you've selected.
For a quicker way to add functions, just click the down arrow beside the AutoSum button to sum, average, count, or find the max/min numbers in a range. If you only need to glance at the data, just select a range of numbers and the bottom right toolbar will show you the sum, average, and count automatically.
Charts
Once you've added data to your spreadsheet and have crunched your numbers with functions, it's time to bring it to life with charts. Excel Online includes a wide range of charts—and most of the tools you need to customize their appearance.
Just select the data you want to visualize, then click the Insert menu and select one of the chart styles you'd like. Excel will automatically generate the chart—and if you don't like it, just click any of the other chart types in the new Chart tab.
Then, you can customize your chart's title, legends, and labels. Just select the menu option for the item you want, and you can both add text and customize where it's displayed.
Unfortunately, there are no theme or color options for charts in Excel Online. If you want something different from the default colors, start your spreadsheet using one of the built-in Excel templates, and your charts will pick up the colors from that template. Alternatively, open your spreadsheet in Excel on your computer to change the design—then save it, and the changes will show up in Excel Online, too.
Tables
Or, perhaps you just need an easier way to sort through data. For that, Excel's Tables are the tool you need.
Select the data you want to sort through, and click the Format as Table button in the Home toolbar. You can then sort each column, filter for specific items (say, all items that include the text "tool"), or use number filters to find items that equal or are greater than/less than, above or below average, or the top 10 items in that column. Or, you can add custom filters to find exactly the data you want.
Gather Data with an Excel Survey
With your spreadsheets online, Excel can work in the background even when your computer is turned off. One of the handiest ways to use that is with Excel Online's built-in Surveys tool. It's one Excel feature you won't find in the other versions of Excel.
Just open a spreadsheet in Excel Online, click the Survey button in the Home tab, then select New Survey.
That opens an editor pop-over, where you can add a title and description to your form or survey, along with your survey questions. Excel Surveys includes text, paragraph text, number, date, time, yes/no, and multiple choice questions. Just add your question and an optional subtitle, along with choices if you selected that question type. Then, choose whether the question is required, and optionally provide a default answer.
Then, click the Share Survey button to copy a link to your survey. You can share that link privately in emails, or share it publicly to get a wider range of answers. Excel will automatically save your survey responses to a new sheet in your spreadsheet, with table formatting so you can easily sort through and filter your survey responses.
Share and Collaborate in Microsoft Excel Online
Need to crunch numbers as a team? Just click the big Share button in the top right corner of Excel Online to get started. You can share the spreadsheet via email, sending a message along with a private link to your colleagues. Or, you can get a view or edit link to share directly. Anyone with the link can view or edit your spreadsheet, depending on the link you chose.
If you have an Office 365 Business account, Excel offers more granular sharing options. You can restrict your file to only people in your company, or make it view-only. You can also add a link expiration date so it's not shared forever, or copy a QR code that your collaborators can scan to work with you.
Once your colleagues have opened your spreadsheet, everyone can work on it together inside Excel Online—though you can't open the file in traditional Excel apps at the same time. You'll see a list of the names of collaborators in the top right along with the cell they're currently editing. As changes are made, you'll see cells currently being edited shaded in that users' color, and cells selected by another user highlighted with their color.
Add Comments to Your Spreadsheet
To work together a bit better, click the Review tab to open Excel's comment tools. You can add a comment to a cell, show or hide the comments sidebar, or edit and delete any comments already on the spreadsheet. You can't add a second comment to a cell that already has a comment, and can't reply to comments, so you might need to work a bit to find a system that works well for your team. It's a good way to let others know where you left off work—or to identify cells that need changed—at least.
Share a Copy of Your Spreadsheet
Want to share your spreadsheet with others offline so they can edit it in a traditional copy of Excel? You could either copy the spreadsheet from your OneDrive folder, or you can download a new copy from the web.
Just click the File menu in Excel Online, select Save As, then choose Download a Copy to download a .xlsx formatted copy of your spreadsheet. Alternatively, you can download an OpenDocument formatted .ods spreadsheet to use in alternative spreadsheet tools like OpenOffice and LibreOffice.
Moving Your Existing Spreadsheets to Excel Online
If you're already an Excel user, you'll likely want to move your existing spreadsheets to the web so you can view and edit them in Excel online. There are two ways to do that. You could open OneDrive.com, and drag-and-drop your Excel spreadsheets to save them online.
The best option, though, is to install the OneDrive apps, and sync your spreadsheets to the cloud. You can then edit them either with Excel on your computer or online with Excel Online, and have the changes saved to both places automatically.
To do that, just download the OneDrive app and install it, then log in with your Microsoft account. If you have multiple accounts—say a OneDrive for Business account for work and a free account for personal files—you can sync both of them at the same time. Then, copy your Excel files to the new OneDrive folder to sync them with Excel Online.
You can then open OneDrive online and see all of your synced files, organized into folders just like on your computer. And if you're ready to edit one, just click it to open it in Excel Online.
Any changes you make to the spreadsheet online will be saved back to your computer automatically. And if you make any new spreadsheets online, they'll also be saved to your computer in standard .xlsx format that you can open in any modern version of Excel. That's one of the best features of Excel Online: it saves your data in real files, so your spreadsheets aren't trapped in the cloud.
Opening Older Spreadsheets in Excel Online
One thing to note: Excel Online can only open .xlsx and .xlsm files, and with the latter it won't be able to run any macros included in the workbook. If you upload an older .xls file, or a .csv spreadsheet to OneDrive and then try to open it online, Excel will just download the file to your computer instead of opening it.
As such, you'll first need to convert any spreadsheets you want to use online to the newer .xlsx format. Just open them in Excel, open the Save as… dialog from the File tab or menu, then select Excel Workbook (.xlsx). You can then add those files to OneDrive to view and edit them online.
Excel Online also can't open files larger than 10MB. That should be fine for most spreadsheets.
There is a workaround, though, if you have larger sheets. Just signup for Power BI, Microsoft's data analyzation tool, which offers a free 1GB account to get started. Then, you can add your larger spreadsheets (up to 250MB each) to Power BI, and open them in Excel Online.
Extend Microsoft Excel with Add-ins and Integrations
Microsoft Excel Online may have as many functions as traditional Excel, but it doesn't include anywhere near as many features. You can fix that to a degree with Office Add-ins.
From the Office Add-Ins Store, you can find a wide variety of free and paid tools for Excel. Incredibly, the same add-ins designed for Excel 2016 run in Excel Online, so you can use many of the same powerful tools that would otherwise require desktop Excel.
To add an add-in to your Microsoft Excel Online spreadsheet, click the Insert menu in Excel Online and select Office Add-ins to browse the store right inside your spreadsheets. Once you've found an add-in you want to use, just click the Add button to install it—and occasionally you'll then need to click a Start button to enable the add-in.
You'll then see your add-ins from the sidebar along with any comments you've added to your documents. When you re-open this spreadsheet, those same add-ins should open in the sidebar again. If you don't see them, just open the Add-ins Store again, add the same add-in to your spreadsheet, and the sidebar will appear with any add-ins you've enabled.
There are a number of great add-ins, many of which connect to web apps and let your spreadsheets create invoices, manage projects, and more. Some of the most handy are those that add extra features to Excel, or replace built-in features you'd find in Excel 2016. Here are some of the best to try:
- Advanced Find and Replace to search deeply through your spreadsheets
- Duplicate Remover to remove duplicate data
- Split Text to Columns to make your data easier to use in a spreadsheet
- Trim Spaces to remove extra blank spaces from your spreadsheets
- Compare Text to find differences between text
- Merge Cells to merge rows, columns, or entire ranges
- Solver to solve optimization problems for statistics
- Bing Maps to create maps from your spreadsheet data
- Blockspring to add extra data analysis functions to Excel
There is one thing you can't run in Excel Online: macros. Macros let you automate Excel, either by letting Excel record what you do in a spreadsheet or by writing simple code. For that, you'll need a traditional copy of Excel installed on your computer. Then, you can write your own Excel macros to make powerful spreadsheets, and sync them with OneDrive to still be able to view and edit their info online.
Connect Excel to 700+ Apps with Zapier Integrations
Another great way to do more with Excel Online is with Excel integrations using app automation tool Zapier. Zapier can watch your spreadsheet for new or updated rows, and then start workflows with over 600 other apps based on that data from your spreadsheet. Or, it can search through your spreadsheet, add a new row, or even update existing rows in your spreadsheets.
Say you want to keep track of how many tasks your team finishes in Trello. You'd just make a Zap with the Trello integration, and have Zapier watch for cards that are archived in Trello.
Then, you'd add an Excel action step to the Zap, and have Zapier add a new row to your spreadsheet. Connect your Office 365 account to Zapier, then select the correct spreadsheet and worksheet where you'd like to store your data.
Now all you have to do is click the + icon beside each of the column names from Excel, and select the correct data from Trello to add to the spreadsheet. Once everything's customized the way you want, test and turn the Zap on, and Zapier will automatically log all archived tasks to your spreadsheet.
There are dozens of ways you can use integrations like these to save data to your Excel spreadsheet automatically and get work done right from your spreadsheet. Here are some of the most popular integrations—or check out Zapier's Excel integrations for more ideas and ways to connect your favorite apps.
Microsoft Excel Online may be designed as a sidekick to traditional Excel, but it's a powerful tool it its own right. You can create spreadsheets, crunch numbers, collaborate and more right from your browser for free. And with add-ons and integrations, you can add any extra features you want to your spreadsheet.
It's the perfect way to use Excel anywhere—and may be the best way to get started with Excel if you don't already have a copy installed.
Learn More About Spreadsheets with Zapier
Spreadsheets are incredibly powerful tools, no matter which app you create them in. They can manage your finances and mailing lists—or they can turn your website's analytics data and let you build your own custom apps.
In our new Ultimate Guide To Google Sheets book, you'll find a number of tutorials on how to get more out of spreadsheets. And don't worry: the same tutorials will work in Excel Online with only minor differences.
Here are some of the best tutorials to help you get started:
- If you're just getting started with spreadsheets, check out The Beginner's Guide to Spreadsheets
- Want to manage contacts in a spreadsheet? Here's how to build a spreadsheet CRM
- Spreadsheets can help you write faster, too. Here's how to outline, organize notes, create HTML, and more in spreadsheets
- Then, create a business dashboard in a spreadsheet, and to make it work in Excel, just use the Keluro Web Analytics add-on to get Google Analytics data in your spreadsheet
Or, you can download a copy to learn more about spreadsheets on your own anytime.
Excel for Macintosh ca. 1986 photo by Microsoft Sweeden via Flickr
source https://zapier.com/blog/excel-online-guide/
No comments:
Post a Comment