Into The Data Verse — Data Analysis with Excel Online (Part 1).
Introduction
What exactly is Excel Online? Excel Online (formerly Excel Web App) is basically Excel that runs on the browser. It allows you to do cool stuff like collaborating remotely with a co-worker and also connect your spreadsheet to other apps online to perform further analytics on the data in the spreadsheet. An example is connecting your spreadsheet to Microsoft Azure Machine Learning studio to perform predictive analysis on the data. It is part of the Office 365 Online family — A cloud services platform that offers familiar Microsoft products like Word, Excel, PowerPoint, and OneDrive (along with other productivity services) either online or on-premises via a subscription plan.
This article is mainly aimed at giving you an insight into what you can do with this amazing web app and also how you can get starting using it.
Setting Up The Workspace
In order to use this web app, you’ll need to have a OneDrive account. OneDrive is an online storage app from Microsoft. It is very similar to Google Drive. The reason why you’ll need the OneDrive account is to be able to easily launch the file or workbook online. Follow the steps below to set up your OneDrive account and also upload a file.
1. Visit this link https://onedrive.live.com/.
2. Sign-up with either an Outlook, Hotmail, or any other email account. If you have an account already, then just skip this and sign-in instead.
3. After you’re done with the authentication process, you’ll see a page similar to the one in the picture below.
4. Click on the Upload button to upload a file.
5. After it is done uploading, you should see the Excel file somewhere on the page just like the one in the picture below which I named “data-hack”
6. Click on it to launch the file. After which you should see a page that looks similar to what is in the image below.
This page displays the content of the Excel file in a spreadsheet on the web. Isn’t that awesome? if you look closely, you’ll discover it is very similar to the desktop version except for some little differences like the macro recorder, which doesn’t exist here because Excel VBA macros can’t be run on the web and others which we’ll look at later.
Another way you can import a file online is by visiting https://office.live.com/start/Excel.aspx and then importing your file directly from your file system online.
Now that we’re done with the boring part, let us get our hands dirty and doing some data analysis online.
Firstly, Let us start by converting our dataset into a table. You can create a table by using the shortcut CTRL + L or using the menu bar ( Insert > Table). After you’re done with this, you should get something similar to what is in the image below.
Let’s begin by doing some simple data manipulation on the datasets present in the table.
1. We’ll do that by creating two extra columns. We then name the first one Months and the second Revenue.
2. The month column should be after the Date column and the Revenue column should be after the Sales Column.
3. Enter this formula in the month cloumn =Text(A2, “mmmm”) and in the Revenue Column enter =G2*GH. After this my nw table loooks like what is in the image below.
Let us apply some conditional formatting to the data so we can just have a little insight about the data in the table. Firstly, we’ll start with the temperature.
1. Select the Temperature Column.
2. Click on the Home tab, then go to the Condition Formating, you should see a drop-down menu, click on Colour Scales and then choose the Red-White color scale or any other scale you choose to.
3. Select the Rainfall Column
4. Undet the Conditional Formatting drop-down menu, click on Data Bars, then select Blue Data Bar under the Solid Fill section.
After following the steps above the table should look similar to this:
I think from the little introduction to the wonderful web app, one can deduce that it is easy to use even one has just been introduced to Excel can easily pick it and learn. For further insight as to what files the app can open or any other another thing, you are skeptical about, kindly visit the links below.
In the next part of this article, I’ll continue with analyzing the data and also do some data visualization.
Thanks for reading!
