Excel Online - File types and other basics

<< Click to Display Table of Contents >>

Navigation:  Office365 and PowerApps >

Excel Online - File types and other basics

I will cover the basics of Excel file types and how Excel files appear in the cloud. To whit there are 4 standard file types that you are probably going to be dealing with and to test my theories I am working with a shared Dropbox folder.

*.xls file : This old favourite is a binary file and it isn't supported in Excel Online. It will still work in Excel on the desktop and you can embed vba macros in it.

*.xslx : this is the format that Microsoft wants you to use now. It takes up less space though this is achieved using Zip compression. If you want to use ExcelOnline, this is the format that you need to use. If you add a vba macro to the file, it will not work in ExcelOnline. There are some features such as Protection and XML Maps that are not supported in ExcelOnline.

*.xslm : These are .xslx files that have VBA macros in them. You can open them in ExcelOnline but you have to save them in a copy of the file. How this is useful is beyond me but I guess it might help in certain circumstances.

So to start, lets share a Folder that is in my dropbox as shown in Figure 1. All I do in this case is right click on the Dropbox folder and choose Share. Watch for the Can Edit option, this includes deletion of files but in this instance we want users to edit the spreadsheet so Can Edit it is.

excelshare1_zoom35

If you paste the link into a browser, you will see all the files in a folder as follows. The speed at which this folder updates with changes is very impressive.

excelshare2_zoom45

If I click on the file, SampleOrder.xls, I see a readonly view of the first worksheet in the spreadsheet. I can then Edit that in ExcelOnline or in Excel on the desktop as shown in figure 3 under the Open Button (top right).

excelshare3_zoom37

 

Lets wind up the pictures with a view of ExcelOnline in edit mode on the OrderSample.XLSX file. If you look at the picture, you will see that I have added Excel Data filtering to the spreadsheet.

excelshare4_zoom35

This brings me to the final point in this article, the things that you can do to make a online spreadsheet more like a software solution than a standard spreadsheet.

Deploy Filter Data in the spreadsheet (See Data tab on the ribbon)

Add Conditional Formatting to cells on the worksheet (see Home ribbon)

Given that you will very keen to have people enter accurate data, consider the use of colour to dissuade people from using cells that they shouldn't

Now you need to persuade people to do the data entry and for this I would pack your introduction email with some nice diagrams.

 

Postscript 1:
If you want to add VBA or workbook protection *.XLSM, just resign yourself to sharing solution that uses Excel on the desktop.

 

Postscript 2:
ExcelOnline will work on Ipads and Android and Windows phones but you will need to make the sheets small and simple to be successful.

 

Further Reading
 
Online Excel Data Doesn't have to be Dumb