I want to be clear that when I say apps for Office in Excel, what I am talking about are the apps that you insert into your worksheet from the Excel ribbon.
Microsoft have stated that they are moving away from the term Apps for Office though and will be using Office add-ins instead.
I’m not sure this makes things any less confusing though, as to me an add-in is something you write in VBA and then install into Excel in a completely different way.
What is an App?
Essentially it is a web page that you can embed into your worksheet. It can be as simple as a few lines of HTML or as complicated as your imagination.
Requirements to Install and Run Apps for Office
Depending on where you look, you might find slightly different requirements listed to be able to install and use apps for Office. But here is my summary of requirements to use apps on your Windows desktop computer:
- Excel 2013 (or later) or Excel Online. No mention is made of Office 365 but I presume that it will work with it.
- Internet Explorer 9 or later. This doesn’t have to be your default browser, but it must be installed as Excel uses IE to render the app.
- Internet Explorer 9 with at least MS12-037: Cumulative Security Update for Internet Explorer: June 12, 2012 installed
- Internet Explorer 10 or later (highly recommended)
- Firefox 12
- Safari 5
- Chrome 18
Content Apps v Task Pane Apps
In Excel you can have two types of apps, Content App and Task Pane Apps.
A Content App is inserted into the body of the worksheet (inline) and appears as an object embedded into the sheet, similar to how a chart is embedded.
A Task Pane App appears alongside the worksheet in a separate Task Pane. This type of app can be used to provide additional functionality, for example you could have an app that translates selected text from the sheet.
How do I create an app?
An app consists of, at minimum, just one file. This is an XML file that describes the app for Excel, and points to a website or HTML file that contains all the code that makes your app work.
If you want, and to make any real use of apps you will, create your own HTML file and put in this all the code you need for the app.
If you are already screaming at the thought of XML and HTML, the good news is it is just text and the programming skills required to get started are pretty much zero. If you can edit a text file, you can create an app.
Anatomy of an App
Before we dive into creating an app, let’s first look at how the things work. As I said the bare minimum you need just one file, an XML file, which is called the app manifest.
This manifest is a series of data telling Excel things about the app like who wrote it, what type of app it is, what it can do and lets you specify things like a name for the app and a description, and where the HTML file is located. A simple one looks like this
NOTE : the color coding and numbering along the left hand side are all done automatically by Notepad++. It really helps when working with this type of file and beats the pants off Notepad
For what we are going to do the only bits you need to change are
- <Id> : a unique identifier for the app. Must be a series of HEX numbers i.e. 0 to 9 and a to f
- <ProviderName> : Your name or your business name
- <DefaultLocale> - change this if you are not using EN-US (English-United States)
- <DisplayName> : The name of the app shown in the list of apps when looking for apps to insert in Excel
- <Description> : The tool tip that pops up when you hover your mouse over the app in the list of available apps.
- <SourceLocation> : where the app’s HTML file is located
- <RequestedWidth> : initial width of the app. This is optional
- <RequestedHeight> : initial height of the app. This is optional
You can see that for the <SourceLocation> I have specified our blog address https://www.myonlinetraininghub.com/blog so all this app does is load our blog into the worksheet. Easy!
Creating Your Own HTML File
If you want to do more than just load a website then you will need to create your own HTML files, or get someone to do this for you. At its most basic the HTML looks like this :
Let’s say we want to play a video from Vimeo in our worksheet. We need to get the embed code for the video from Vimeo and put this into a HTML file which is loaded by the app. Here’s the HTML file with the video embed code added :
NOTE : The embed code from Vimeo is the line starting with <iframe … and then continues off screen. I haven’t shown it all simply because it won’t fit.
All I’ve had to do was add a single line of code (provided by Vimeo) and I now have an app that can embed and play a video in my worksheet. Cool.
Setting up apps for installation
In order for the app to be available for you to insert into Excel you need to put the XML and HTML files in certain places. The XML file must be stored in an App Catalog, and the HTML file can be in that same App Catalog or on a web server.
Creating an App Catalog
The purpose of the app catalog is to store all the app manifests (the XML files) in a central location that can be accessed by everyone. Your colleagues can then access and install your apps.
Apps can be published to the Office Store, and Outlook apps can be published to a Microsoft Exchange server, I will cover neither of these scenarios here.
Our Excel app manifest (the XML file) can be stored (published) to either a Microsoft Sharepoint catalog, or to a shared folder on a computer. I am not going to go over setting up Sharepoint, and will instead look at setting up a shared folder as an app catalog.
The shared folder can be on your own Windows computer but if you want to distribute apps to your work colleagues, it would make most sense to be on a server that everyone in your company can access.
Setting Up a Shared Folder
The first thing you need to do is create a folder on the computer hard drive (or use an existing one) and then share it so that everyone has Read access.
Nominating the Trusted App Catalog
In Excel go to :
File -> Options -> Trust Center -> Trust Center Settings -> Trusted App Catalogs
Add the shared folder into the Catalog Url field using the format \\computer_name\share_name then click the Add catalog button.
The location should now appear in the list of Trusted Catalog Addresses. Check the box beside the shared folder name (under the Show in Menu heading) then click OK, and OK again to get back to Excel.
In the example below my computer is named pgt-pc and the shared folder is named Appcatalog so the Url I entered was \\pgt-pc\Appcatalog
Store XML App Manifests in Catalog
Make sure you move all your XML manifest files to the shared folder you are using as your catalog. Then, go into Excel and click on the INSERT tab, then click on My Apps
Click on SHARED FOLDER and you should see the apps you have created, you may need to click Refresh.
Either double click one to insert it, or click on an app and then click on the Insert button at the bottom right of the window. If you haven’t specified an initial width and height for the app, you will probably need to resize it.
HTML and any other files the app needs are best stored on a web server. Your web server could be an intranet, a publicly available web server or a Content Delivery Network like Amazon S3.
I’ve created several example apps, some very simple, some more complicated. Remember that what you are doing is embedding a webpage into a worksheet. Pretty much anything that you can do on a webpage you can do in the app.
The videos below for each example are show in 720p High Definition so you can go full screen if you wish, or just watch them embedded in the page. There is no sound with any of these videos.
Privately Hosted Video
This video is loaded from our content delivery network (CDN) Amazon Cloudfront.
Embedded Video from Vimeo
This video is loaded using the embed code provided by Vimeo.
Our blog loaded into your workbook.
Data such as stock and commodity prices, foreign exchange rates, and stock charts. Some of this data is continuously updated in your workbook.
Workbook Embedded in a Workbook!
Just for fun I created an app so that I could embed a workbook that we have stored on OneDrive into another workbook. Read our blog on how to create an interactive Excel workbook on OneDrive.
A bar chart created using D3 again. This chart shows the frequency of use of the letters of the alphabet.
After inserting an app you can resize it and Excel will remember this next time you open the workbook.
Check out these apps yourself
In order to install my apps yourself you will need access to the XML manifest files. As you won't be able to access the shared folder on my computer, the best way for you to do this is to download the manifest files and set up your own shared app catalog, as described above.
You can then insert the app(s) into your workbook and load the HTML files from our servers. Or if you like you can also download the HTML files, pick them apart to learn how they work, and put them on your own servers.
Interacting With the Workbook
The possibilities are huge.
All D3.js code written by Mike Bostock
Charts and market data from Investing.com and TradingView
Sharing is Caring
If you liked this or know someone who could use it please click the buttons below to share it with your friends and colleagues.