Using Excel Sub-Totals for Reporting

<< Click to Display Table of Contents >>

Navigation:  Office365 and PowerApps >

Using Excel Sub-Totals for Reporting

Excel online is different canvas to Excel on the desktop. Your audience is likely to be less familiar with Excel, they are very likely to have a small screen and they probably couldn't "be bothered" to scroll off the first page of your report. This is the fourth piece that I have put together on Linked in on reporting using Excel Online.

 

Lets set the scene, you have to create a report of sales and budget information that is hundereds of lines long. The first few lines of that will look like Figure 1. Give that to the manager of a distant region and ask if things are going well and you will draw a muted response.

201612_gr1

Figure 1

If you were instead to give the manager a consolidated report as in Figure 2, you are more likely to get a good response.

201612_gr2

Figure 2

 

To jump straight to the tech behind this article, we are going to use the Sub-Total function in Excel desktop to consolidate the data into summary totals. This can be found on the Data tab in the Ribbon as shown below in figure 3

201612_gr3_zoom33

Figure 3 - Click to enlarge

But as this article is about publishing in the cloud, lets look at the same report in the Excel online viewer. To illustrate this, in figure 4, I right click on the Excel file and click View on Dropbox.com

201612_gr4

Figure 4

This brings up the folder viewer in Dropbox as in figure 5. Now I click on the Exported.xls file and check it out in the viewer.

201612_gr5

Figure 5

 

Figure 6 below is what the Exported excel file looks like in the Excel viewer. It is really easy to read and a manager could decide if there was an important issue with the totals on any device. This view would be suited to most modern phones and certainly on ipads and small portable tablets.

201612_gr6

Figure 6

But lets say the manager wants to drill into the data. Figure 7 shows what the manager sees in Excel Online editor. You will note that the expander plus signs are visible on the left hand side.

 

201612_gr7

Figure 7

Clicking on the + expander (Fig 8) and the manager can now see the detail data for one of the sub topics (Iced Tea). Pretty impressive !

201612_gr8

Conclusion: This is a really good way to engage a person with your information in the cloud. The initial view of the information is concise and fits on a small page. If that information engages the reader, they can explore the data further using the tools available in the Excel Online editing tools. These tools are available for free in Dropbox, OneDrive, on Ipads, SharePoint and Box.

Thanks for reading and if you are a programmer, check out my newsletter at vb123.com.au where I will soon publish information on how to program this report using Excel Automation.