Into The Data Verse — Data Analysis with Excel Online (Part 2).

In the previous part of this article, I talked about what Excel Online is actually all about, its uses, and also how to get started using this awesome web application. In this part of the article, I’ll be focusing more on how Excel Online can be used for analysis purposes (Just basic).

Before we get down to business, head on to this link => https://1drv.ms/x/s!AhkI_v-IPe65dgt2zMrcsmur4_8 to get the file we’ll be using for our analysis. After visiting the page, click on the download button at the top right corner of your screen to get the file after which you can then upload into your OneDrive account (check the previous part of this article to create one if you don’t have an idea of how to go about it).

Let’s get started!

After successfully uploading your file, and launching on the web, it should look similar to what is in the picture below.

We’ll begin our analysis be creating a pivot table. To create a pivot table, go to the Insert tab, then click on the Pivot table option. However, before activating the Pivot table, you can choose to convert the dataset format which is in a range into a table. You can do this with the keyboard shortcut; Ctrl + l. But then, it is advisable to convert it into a table.

After creating the pivot table, select the SubRegion field, this automatically puts in the Row Label of the Pivot Table because the data in this field contains text. Do the same thing to the Month field, but then, manually drag it to the column section so we, then select the Sales Amount field, finally drag the Region Label into the filter section. You’ll notice a filter is created at the top of the pivot table. Your data should be similar to what is in the picture below after following the steps above.

You’ll notice that when you activated the PivotTable, there was no PivotTables tools tab unlike the one on your desktop. This is one of the differences between Excel Online and Excel (desktop version).

Click on the values section in the PivotTable fields section, then go to Value field settings, you get to see a list of calculation options you can use on your data. For example, if you select the max option, the maximum sales value for each region in every month is being displayed. This also applies to other calculation options in the list.

Go to the PivotTable field to unselect all the options in the field, then, select the Market, Month, and Sales Amount fields. Drag the Month section to the Rows, the Market section to the column, and then finally make sure the Sales Amount field is selected.

The data above displays the total sales of products in different markets. Now, lets us determine in which of these months in each region the company made above 20% of the total sales.

Select the whole data excluding the grand totals which are at both the rows and columns. Copy, then paste special to retain the number formatting into a new sheet. Go to, the Home tab, conditional formatting, go to the Top/Bottom rules under the conditional formatting section, then select Top 10%, change the default 10 to 20. This selects the sales amount which is above 20% for each month and for each region respectively.

Conclusion and Observations about Excel Online

While writing this article, I noticed some few things which actually had their impact on this article in their own way. For instance, I noticed Excel Online doesn’t have the PivotTable tools bar which I could use to manipulate the data in the PivotTable like hiding grand totals etc. I also noticed I couldn’t use slicers. I feel a little bad about this because I planned on using it to create a simple dashboard in this article but I couldn’t. I hope they get to add it soon. I also noticed when you try to upload a file which is more than 5mb, you get a warning at first, then after you keep using it, it stops working.

Whatever I have written above are just a few things I noticed while creating this article. However, I actually loved the application because it’s flexible to use and one cool thing you can do with it, is to directly open whatever file you are working on the browser on your desktop and then continue working from there.

Thanks for reading!