by Last week we explored a few techniques to convert Excel spreadsheets to HTML. But nowadays when everyone seems to be moving to the cloud, why not we? The new technologies of sharing Excel data online are a way simpler and provide a handful of new opportunities that you can benefit from.
With the emergence of Excel Online, you no longer need cumbrous HTML code to export your tables to the web. Just save your workbook online and access it literally from anywhere, share with other users and work on the same sheet all together. With Excel Online you can also easily embed your worksheet on a web site or blog and let your visitors interact with it to find just the information they are looking for.
Further on in this article, we are going to investigate all these and many other capabilities provided by Excel Online.
How to move Excel spreadsheets online
If you are new to the cloud in general, and Excel Online in particular, the easiest way to start is sharing your existing workbook using the familiar interface of Excel desktop.
All Excel Online spreadsheets are stored on OneDrive web service (formerly, SkyDrive). As you probably know, this online storage has been around for a while, but now it is integrated in Microsoft Excel as an interface option accessible in a click. In addition, your invitees, i.e. other user users that you are sharing your spreadsheets with, no longer need a Microsoft account to view and edit the Excel files you've shared.
If you don't have a OneDrive account yet, you can sign up now. This service is easy, free and definitely worth your attention because most Office 2013 and 2016 applications, not just Excel, support OneDrive. Once you are signed in, proceed with the following steps.
1. Sign into your Microsoft account
Make sure your are also signed into your Microsoft account from within Excel. On your Excel workbook, look at the top right-hand corner. If you see your name and a photo there, you can skip this step, otherwise click the Sign in link.
Excel will display a message asking to confirm that you really want to allow Office to connect to the Internet. Click Yes, and then enter your Windows Live credentials.
2. Save your Excel spreadsheet to cloud
Verify that you have the right workbook open, i.e. the one you want to share online, just to be on the safe side. In this example, I will be sharing a Holiday Gift List so that my family members and friends can view it and contribute : )
With the correct workbook open, navigate to the File tab, click Share in the left pane. The Invite People option will be selected by default and you click Save To Cloud in the right pane.
After that choose a cloud location to save your Excel file. OneDrive is the first option you will see on the right selected by default, and you simply choose the destination folder in the left pane.
Note: If you don't see the OneDrive option, then either you don't have a OneDrive account or you are not sighed in.
I've already created a special Gift Planner folder and it shows up in the Recent Folders list. You can choose any other folder by clicking the Browse button underneath the Recent Folders list or create a new one in the usual manner by right clicking anywhere within the right part of the Save As dialog window and selecting New > Folder from the context menu. With the proper folder selected, click the Save button.
3. Share the spreadsheet you saved online
Your Excel workbook is already online and you can view it on your OneDrive>. If you wish to share the online spreadsheet with other people, one more step is left for you to do - choose one of the following sharing options:
- Invite People (default). Just enter the email addresses of the contact(s) you want to share your Excel worksheet with. As you type, Excel's AutoComplete will compare your input with the names and addresses in your Address Book and display all matches. To add several contacts, separate the names with a semi-colon. Or, click the Search Address Book icon
to search for contacts in your Global Address list.
You can set viewing or editing permissions for the contacts by choosing the corresponding option from the drop down list on the right. If you are adding several invitees, the permissions will apply to all, but you will be able to change the permissions for each particular person later.
You can also include a personal message to the invitation. If you do not enter anything, Excel will add a generic invitation for you.
Lastly, you choose whether a user is required to sign-in into their Windows Live account before they can access your online spreadsheet. I don't see any particular reason why they should, but it's up to you.
When done, click the Share button. Each of the invited contacts will receive an email message containing a link to the file you shared. They simply click the link to open your Excel spreadsheet online, on OneDrive.
Upon clicking the Share button, Excel will display the list of contacts you have shared the file with. If you want to remove someone from the list or edit the permissions, right click the name and choose the corresponding option from the context menu.
- Sharing Link. If you want to share your online Excel sheet with many people, a faster way would be sending them a link to the file, e.g. using an Outlook distribution or mailing list. You choose the Get a Sharing Link option in the left pane and grab either a View Link or Edit Link or both in the right pane.
- Post to Social Networks. This option's name is self-explanatory and hardly requires any explanations, maybe just one remark. If you have chosen this sharing option but do not see the list of social networks in the right pane, click the Connect social networks link and you will be able to choose your Facebook, Twitter, Google, LinkedIn and other accounts.
- Email. If you prefer to send your Excel workbook as attachment (a usual Excel file, PDF or XPS) as well as internet fax, choose Email on the left and the appropriate option on the right.
Tip: If you want to limit the areas of your Excel workbook that other users can view, switch to the File > Info and click Browser View Options. You will be able to choose the sheets and named items you want to display on the Web.
That's all! Your Excel workbook is online and shared with other users of your choosing. And even if you are not collaborating with anybody, this is an easy way to access your Excel files virtually from anywhere, no matter whether you are at the office, work from home or travel.
How to create web-based spreadsheets in Excel Online
To create a new workbook, click a little arrow next to Create and choose Excel workbook from the drop-down list.
To rename your online workbook, click the default file name and type a new one.
To upload your existing workbook to Excel Online, click on the Upload button on the OneDrive toolbar and browse for the file on your computer.
How to edit workbooks in Excel Online
Once you have the workbook open on Excel Online, you can work with it using Excel Web App almost in the same way you use Excel desktop: enter data, sort and filter, calculate with formulas and present your data visually with charts.
There is only one significant difference between web-based Excel spreadsheets and desktop. Excel Online does not have the Save button because it saves your workbooks automatically. If you've changed your mind about something, press Ctrl+Z and Ctrl+Y to undo or redo, respectively. You can use the Undo / Redo buttons on the Home Tab > Undo group for the same purpose.
If you are trying to edit some data but nothing happens, most likely you are in read-only view. To switch to editing mode, click Edit Workbook > Edit in Excel Web App and make quick changes directly in your web browser.
For more advanced data analysis features such as pivot tables, sparklines or connecting to an external data source, click Edit in Excel to switch to the desktop version.
When you save the spreadsheet in your Excel, it will be saved where you originally created it, i.e. in your OneDrive.
Tip: If you want to make quick changes in several workbooks, the fastest way is to open the list of files on your OneDrive, find the workbook you want, right click it and select the needed action from the context menu.
How to share Excel Online spreadsheets with other users
To share your web based Excel spreadsheet, click Share > Share with People and then choose either:
- Invite People and type the email addresses of people you want to share the workbook with, or
- Get a link to paste it to an email message, web-page or social media sites.
You can also choose whether you want to give viewing or editing permissions to your contacts.
When several people are editing the worksheet at the same time, Excel Online shows their presence and the updates straight away, provided that everyone is editing in Excel Online, not in Excel desktop. When you click a little arrow next to the person's name in the top right-hand corner of your spreadsheet, you can even see which exactly cell is being edited at the moment.
How to lock certain cells for editing in a shared worksheet
If you are sharing your online sheets with a number of people, you may want to limit the editing rights for your team members only to certain cells, rows or columns in your Excel document on OneDrive. To do this, you need to choose the range(s) that you allow to edit in your desktop Excel and then protect the worksheet.
- Select the range of cells that your users can edit, go to the Review tab and click "Allow Users to Edit Ranges" in the Changes group.
- In the Allow Users to Edit Ranges dialog, click the New... button, verify that the range is correct and click Protect Sheet. If you want to allow your users to edit several ranges, click the New... button again.
- Enter the password twice and upload the protected sheet to OneDrive.
If you are using the desktop version of Excel, these instructions may come in handy: How to lock or unlock specific areas in worksheet.
Embed online spreadsheet onto a website or blog
If you want to publish your Excel workbook on a web-site or blog, perform these 3 quick steps in the Excel web app:
- With the workbook open in Excel Online, click Share > Embed, and then click the Generate button.
- In the next step, you decide how exactly you want your spreadsheet to appear on the web. The following customization options are available to you:
- What to show section. It lets you embed the entire workbook or its part such as a range of cells, pivot table etc.
- Appearance. In this section, you can customize the appearance of your workbook (show and hide grid lines and column headers, include a download link).
- Interaction. Allow or disallow users to interact with your spreadsheet - sort, filter and type into cells. If you allow typing, the changes that other people make in the cells on the web will not be saved in the original workbook. If you want to a certain cell to be selected when the web page opens, select the "Always start with this cell selected" check box, and then click the cell you want in the preview that is displayed the right part of the window.
- Dimensions. Type a width and height for the spreadsheet viewer, in pixels. To see how the viewer will look with the sizes you've defined, click the "View actual size" link at the top of the preview. Just keep in mind that you can specify a minimum of 200 x 100 pixels and a maximum of 640 x 655 pixels. If you want to use other dimensions outside these limits, you will be able to modify the code later using any HTML editor or directly on your web-site or blog.
- All that is left for you to do is click the Copy link underneath the Embed code section and paste the HTML code (or JavaScript markup) onto your blog or a web-site.
Note: The embed code is an iframe, so make sure your web-site supports iframes and blog editor allows iframes in posts.
Embedded Excel Web App
What you see below is an interactive Excel spreadsheet that illustrates the technique in action. This "Days Until Next Birthday" app calculates how many days are left until your next birthday, anniversary or other event and shades the gaps in different tints of green, yellow and red. In the Excel Web App, just enter your events in the first column and try changing the corresponding dates to experiment with the results.
If you are curious to know the formula, please check out this article - How to conditionally format dates in Excel.
Excel Web App mashups
If you want to get more interactions between your web-based Excel spreadsheets and other web apps or services, you can use the JavaScript API available on OneDrive to create interactive data mashups.
Below you can see the Destination Explorer mashup created by our Excel Web App team as an example of what web developers can do for your website or blog. This mashup uses the APIs of Excel Services JavaScript and Bing Maps and its purpose is to help the web-site visitors choose a destination where they want to travel. You can choose a location and the mashup will show you the local weather or the number of tourists that visit the area. The screenshot below shows our location :)
As you see, working in Excel Online is simple. Now that you know the basics, you can explore the other features and manage your online spreadsheets with ease and confidence!
67 comments
Hi
could you please tell me if any customized work book will appear the same as in the stand alone excel version? or it will be over-written by the web? Im just wondering
Hi Caesar,
From my experience, it appears exactly the same as in the desktop version, except if it contains macros, shapes or other features that are not supported in Excel Online.
Hi, I m having trouble leaving certain cells in unlocked in worksheets on excel one drive. When I follow the instructions above, the entire worksheet is locked in the online version and no cells are editable, even though they are in the desktop version of excel. Any ideas on where I might be going wrong?
Before locking your worksheet,first unprotect the cells you want to be editable from the Format Cell... dialogue box by unchecking the unlock button for cells at the Protection tab.
I need to save the client webpage changes
how to code it
beucase said interacion
let people type into cells (their changes will not be saved)
I need to make the changes effective
Hello Alfredo,
Please describe the result you are trying to achieve in more detail. Do you want clients to see all their changes next time they log into the site or you want all the changes to be sent to you for checking or anything else?
Hi Svetlana,
The problem still remains because even after I remove the download link, visitors can still download the spreadsheet by clicking on "view full size workbook" in the same bottom bar.
Thanks
I was just wondering if you've found a solution to this problem yet.
Thanks
I am having this same issue, would love to know how to stop this?
Hi Svetlana,
Thanks for this post.
I would like to set up a webpage spreadsheet where people can input data and the spreadsheet calculates the results. I have successfully set this up. The problem I am having is want web visitors to only be able to see their inputs/results without seeing my entire spreadsheet which is proprietary. While I can limit the range that appears,I have noted that in the bottom bar where it says "Excel Web Ap", Microsoft allows visitors to access my entire spreadsheet and download if they desire. I do not want this. Any way to prevent this or any other resources you would recommend that could solve my problem?
Hello Bif,
You can disable this icon by including &wdDownloadButton=False in your workbook address (src=parameter).
You can find more information about different parameters that you can use when publishing workbooks on your website in this article by Microsoft:
Hi Svetlana,
Your article is excellent.
I stumbled across it while searching for a way to convert an excel price list into a secure (ie: password) web based ordering tool.
Only invited customers will be given the password. Customers will sort & filter to find products in the price list, enter qty to order, see order total, then somehow submit that order.
I was really looking for a method to simply convert the spreadsheet to a simple online order store, but this seems to be a possible alternative.
Importantly, I don't want a customer to be able to extract the entire price list (copy/paste, save as).
Your thoughts would be very much appreciated.
Cheers,
Duncan
Simply lock your worksheet while unlocking the cells ranges that you want your customers to sort and filter.
Doing this will make it impossible for anyone to copy your whole file.
Hello Duncan,
I've consulted our VBA specialists and they say that Excel may not be the best platform for your task. For example, we don't know how to disable Copy / Paste in Excel Online. In the desktop version of Excel, you can block icons using VBA, which users could also turn off. So, probably a better way would be looking for some out-of-the-box modules for your web-site. As far as I know, there are a great number of such modules for different platforms.
Hi Ma'am,
I'm encountering some issues with Excel Online that wasn't present when it was still Excel Web App. I have some files that use external data connections to gather data from 1) an sql server, 2) an OData feed and 3) a data import "From Web". I uploaded them to OneDrive so I could embed them on web pages. "Refresh All Connections" used to work fine then I noticed recently that they're throwing an error. Some have "...external data connection or BI features not supported." Others have "...connection failed to refresh" and "XML maps not supported." I haven't done any changes to them or to their connections but I can't seem to make them work again. The only difference I can see is that they're opening with Excel Online now whereas previously it was Excel Web App.
I hope you can enlighten me with this.
Thanks!
Hello Rina,
I am very sorry but I am not able to help you with this. Please contact Microsoft support service directly and describe the issues you are experiencing. I believe they will be able to assist you better because they know the nuts and bolts of their own programs.
Thanks for the reply! I'll do more research on it. However, maybe you can just help me clarify one more thing. Would you know if the feature of refreshing external data connections still supported on Excel Online? Asking this because upon creating and uploading new documents with external data connections, they already fail upon clicking the refresh on Excel Online. I've seen articles (http://bit.ly/1wMZzZ0) stating significant differences between Services, Web App and Online but now, it seems that everything redirects to Online.
Thanks again.
Hello Rina,
The basic options are supported in Excel Online, but the normal, fully-functional handling of external data connections is possible only in Excel desktop only. Here is what Microsoft states in this regard: "You can use Excel Online to view data connections, but you can't create external data connections using Excel Online. You'll need the Excel 2013 desktop app to work with external data". For more details, please see this article on Microsoft's website:
https://office.microsoft.com/en-us/sharepoint-help/working-with-external-data-in-excel-services-HA102830785.aspx
Alright. Thank you so much!
Hey,
I would like to ask is it possible to link two or more excel files so they would work together.
Simple example vlookup from getting data from database from another file.
Thank you
Vlado
Hi Wlado,
Yes, it is possible. Just make sure the Excel file from which you want to get the updated information is opened.
Hi,
Is there a way to stop an Excel document from being edited in Excel Online and only in the Excel Client.
The use case is to stop a user editing a copy because the file contains unsupported features.
I understand that you can protect a workbook and manage file permissions but I want to allow a user to edit the file only through the Excel Client and just view with Excel Online.
Thanks
David
Hi David,
I am sorry, I don't know the way to do that. You can contact Microsoft support service for assistance or post your question to the Microsoft Community.
Is it mandatory the user should have Microsoft account to Login. Can they use a different account
thank you
Hello Jatin,
If you want to share your online workbook with other users, they do not necessarily need to have Microsoft accounts. If you want to embed your online spreadsheets onto a website or blog or edit them using Excel Online, then you do need a Microsoft account.
hi,
I have shared an excel workbook and multiple people are using the sheet to add and remove data. Is there a way if one user is working on the sheet the second user should get notification that someone is working on the worksheet with the name
thank you
Of course, this is possible. Please see "How to share Excel Online spreadsheets with other users" in this article for more detail. You can also check out the following article by Microsoft ("Work with others in Excel Online" section):
https://office.microsoft.com/en-us/office-online-help/work-together-on-office-documents-in-onedrive-HA102625039.aspx
I have 10 people sharing excel sheet, when it opens with one of us it is ready only for others that's fine. If same time anyone will open then he is not getting pop up name who is working on that. Can I hav suggestion for this?
Hi Dhan,
Do you share your spreadsheet in Excel Online or a usual spreadsheet in desktop Excel? If the latter, the following article by Microsoft may be helpful:
https://office.microsoft.com/en-us/excel-help/use-a-shared-workbook-to-collaborate-HP010096833.aspx
Svetlana,
The embedding functionality is great, but is it possible to do this without sharing the file with the whole world? I want users of my site to be able to access the content and no one else. Is there a way to both lock down the file to limit access, and ensure unauthorized users can't view the content if they somehow got their hands on the embedded src url?
Thanks,
Mike
Hello Mike,
When sharing your spreadsheet you can add emails of authorized users and choose either view or edit rights for them. You can also select the option "Require users to sign in before accessing the document". In this case, only your authorized users logged into their Windows Live accounts will be able to access the document and no one else. These options are demonstrated in the screenshot under Step 3. Share the spreadsheet you saved online.
However, this approach is hardly acceptable if you want to let all visitors of your web-site to view your spreadsheet. In this case, you can publish (embed) the spreadsheet in some restricted area of your web-site that only registered users can access.
Hi, I have done this where i have embedded an online calculator within a restricted sign in section of my website. However, the issue i now have is that although the download option has been turned off, there are still 2 other buttons on the bottom right side of the iframe which allow users to "view full-size workbook online" or "view information about this workbook". I'm wanting to know if there is a way to block those options to stop my spread sheet from being stolen? I've tried to reduce the size of the iframe to hide the scroll bars but it doesn't seem to be possible and even though the HTML says scrolling="no" there is still scrolling?? Can anyone help?
Hi, same problem here.
Could you find a solution?
Thx!
This is a really big issue that I'm having too. I'd like to allow people to interact with the contents of the spreadsheet without being able to take all of the content. Ah!!
Hi Svetlana,
Is it possible to automatically create a copy of the an Excel Sheet (this is the template) everyday (with a file name that indicates date)in Excel online?
Regards
Prem
Hi Prem,
Sorry, I am not aware of any way to do this automatically, most likely this is not possible.
Hi Desire,
to my project having some doubt bcz how to create an online excel automatically.....i need ur help...
Hi Deepa,
I am not sure I fully understand what you mean by "create an online excel automatically". There are 2 ways to create online worksheets:
1. Move your existing worksheets online, as explained here.
2. Create a workbook online on your OneDrive, as described in this section.
Hi,
What is maximum capacity(in Mbs) can an excel file be loaded and accessed in OnDrive?
Desire
Hi Desire,
To my best knowledge, you can upload a file up to 2 GB onto OneDrive. But Excel Web App allows editing workbooks up to 2 MB only. Please see this page for more info.
Thanks Svetlana,
the last one, Can I share some of spreadsheets to users?
Regards,
Desire
Hi Desire,
Of course, you can share your spreadsheets with other users by clicking Share > Share with People in Excel Online. Please see the following section for the detailed guidance with screenshots - How to share Excel Online spreadsheets with other users.
Hi,
I am very excited by this excel online solution,one more question.
How to prevent my website( containing embedded excel) to be hacked?
Regards,
Desire
Hi Desire,
The embedded code has practically no impact on the security of your web-site. The Excel Web App is loaded on the client's side, your web-site just indicates which spreadsheet should be taken and how it shall be loaded.
Thanks Svetlana,
Can you also help on this:
Is there a way of linking 3 files in cloud?
1. one is INPUT file which will be always replaced (overwritten) daily or weekly.
2. the second one is the master file (the ENGINE File containing formulas)and highly protected( only Admin can have access to the file)
3. The 3rd is the OUTPUT file linked to Engine file, which will be downloaded regularly.
Kindly let me know if it is possible and how to do about it.
Regards,
Desire
Desire,
I have tried to answer these questions, to the best of my knowledge, in comment 13 above.
Hi Svetlana, Is it possible to limit edit access for a team member to be able to edit only cetain columns in an excel document on OneDrive?
Thanks,
Natalie
Hello Natalie,
This is a very good question! Of course, you can allow your team members to edit certain columns and lock others. I've added the instructions to the article, please check out the section How to lock certain cells for editing.
Svetlana,
I'm looking for a website developer to set up a new website for my business that takes an Excel file I created and imbeds it as an interactive spreadsheet for the public to use. The user will have 10 cells in the spreadsheet that can be changed to a different number, then hit a "sort" button that shows an updated ranking based on the user's inputs. The developers I've spoken with do not know how to imbed interactive spreadsheets, yet I see in various Google searches, that led me to your blog, that it can be done. Any recommendations.?
Thanks,
Ben Hirschenfang
Property Review Program
Hi Ben,
Usually, there is nothing difficult with embedding interactive spreadsheets to a web-site. Please check out the following parts:
- How to move Excel spreadsheets online.
- How to embed the online spreadsheet onto a website. All you need to do is click the Copy link underneath the Embed code section and paste the HTML code (or JavaScript markup) onto your blog or a web-site.
Most importantly, you will need to decide which cells the user can edit in your interactive worksheet and which should be locked for editing. You can find the details on how to do this in this article - Lock cells in a worksheet.