Posts Tagged ‘Office’

Creating Excel Interactive View July 24th, 2014

Vinod Kumar

I have been wanting to write on this topic for ages but seem to have missed out for one reason or the other. How many times in your life seen a web page with a bunch of tables and it is so boring to read them? The numbers or tables sometimes might have sorting capability but lacks a striking visualization to say the least. So I am going to borrow a table from a Wikipedia page about Indian Population. There are a number of tables and the table of interest to be in Literacy rate. So the rough table looks like:

State/UT Code India/State/UT Literate Persons (%) Males (%) Females (%)
01 Jammu and Kashmir 86.61 87.26 85.23+-
02 Himachal Pradesh 83.78 90.83 76.60
03 Punjab 76.6 81.48 71.34
04 Chandigarh 86.43 90.54 81.38

Well, this is as boring as it can ever get even when pasted as-is on this blog. Now here is the trick we are going to do called as Excel Interactive View. As the name suggests, we are going to use the power of Excel to make this mundane table into some fancy charts for analysis. This includes a couple of scripts that needs to be added as part of the HTML Table and we are done. It is really as simple as that. So let me add the complete table with the script added. Just click on the button provided above to see the magic:

State/UT Code India/State/UT Literate Persons (%) Males (%) Females (%)
01 Jammu and Kashmir 86.61 87.26 85.23+-
02 Himachal Pradesh 83.78 90.83 76.60
03 Punjab 76.6 81.48 71.34
04 Chandigarh 86.43 90.54 81.38
05 Uttarakhand 79.63 88.33 70.70
06 Haryana 76.64 85.38 66.77
07 Delhi 86.34 91.03 80.93
08 Rajasthan 67.06 80.51 52.66
09 Uttar Pradesh 69.72 79.24 59.26
10 Bihar 63.82 73.39 53.33
11 Sikkim 82.20 87.29 76.43
12 Arunachal Pradesh 66.95 73.69 59.57
13 Nagaland 80.11 83.29 76.69
14 Manipur 79.85 86.49 73.17
15 Mizoram 91.58 93.72 89.40
16 Tripura 87.75 92.18 83.15
17 Meghalaya 75.48 77.17 73.78
18 Assam 73.18 78.81 67.27
19 West Bengal 77.08 82.67 71.16
20 Jharkhand 67.63 78.45 56.21
21 Odisha 72.9 82.40 64.36
22 Chhattisgarh 71.04 81.45 60.59
23 Madhya Pradesh 70.63 80.53 60.02
24 Gujarat 79.31 87.23 70.73
25 Daman and Diu 87.07 91.48 79.59
26 Dadra and Nagar Haveli 77.65 86.46 65.93
27 Maharashtra 83.2 89.82 75.48
28 Andhra Pradesh 67.66 75.56 59.74
29 Karnataka 75.60 82.85 68.13
30 Goa 87.40 92.81 81.84
31 Lakshadweep 92.28 96.11 88.25
32 Kerala 93.91 96.02 91.98
33 Tamil Nadu 80.33 86.81 73.86
34 Puducherry 86.55 92.12 81.22
35 Andaman and Nicobar Islands 86.27 90.11 81.84

So how cool is this Excel visualisation? I am sure you will want to build or use this capability in your webpages or internal sites in your organizations too. I hope you learnt something really interesting.

If you want to learn more about using this feature in your dataset and web pages, well read the documentation from Excel Interactive View.

PS: the data comes from Wikipedia and I have just used a snapshot to show the same. So please dont read too much into the data etc, look at the Excel view capabilities.

Continue reading...


Excel Tip: Month Name Sorting in Excel July 14th, 2014

Vinod Kumar

In my previous article Excel Tip: Month Name Sorting with PowerPivot / PowerView, I wrote about sorting of month name inside PowerPivot. I got a number of ping about the same functionality inside standard Excel sheets. I thought this would be the easiest and known to many – but to my surprise, not all know about the capability of Excel. So in this post, let me take you through the same process for Excel tables.

So let me prep you with the data first. I have two columns Month Name and Sales. When I try to sort by the Month Name, you can see how the sorting happens for A-Z and for Z-A. It is nowhere near to what one would expect to sort as month.

That bring us to an interesting option. It has been there all along, select the “Sort by Color” –> “Custom Sort…”.

This comes up with a small dialog which I am sure most of you would have used. There right at the order dropdown, is a hidden gem called as “Custom List…”. Select this to see the magic.

This bring a standard set of list or feel free to define your own New List for sorting and build the table.

Click on “OK” and see. This is awesome because now the list is sorted automatically back in your table. Now if we sort A-Z and Z-A to see the difference. So how easy and cool is it? Have you ever used this option before, let me know.

Continue reading...


Excel Tip: Month Name Sorting with PowerPivot / PowerView June 25th, 2014

Vinod Kumar

I do a lot of presentation on Excel related topics to customers as part of my BI conversations at MTC, Bangalore. Recently one of the developer when looking at the PowerView demo said, he had an unique problem. He said that the PowerView was not ordering the month names in the correct order of Jan, Feb, Mar and so on but was sorting based on Alphabets. I repeatedly asked him if they had enabled the proper sort order. He was little clueless and wanted to see it in action. Here is the 30 seconds demo that I showed how this can be achieved.

In our PowerPivot dataset we have the following. It is a list of MonthNames and sales marked against them.

When I use this dataset and create a PowerView report, I select MonthName and Sales. This comes up with an report like this irrespective of the sort I do. This is the problem that the customer was mentioning.

The solution is simple. Get back to PowerPivot Tab, under Home –> Sort by Column –> Select “Sort by Column”.

We will be presented by the following dialog. Here I am mentioning, whenever the “Month Name” column is selected, use the “Month” integer column to sort behind the scenes. That is it !!

One the above step is complete, our PowerView will show a dialog of change in Data Model, select OK.

And the change can be seen now. The months seems to be in correct order.

Have you encountered this requirement in your report generation anytime? Do let me know.

Continue reading...


Use existing connections in PowerPivot and Visual Studio April 1st, 2014

Vinod Kumar

I have been fortunate enough to work with a lot of data which involve using of PowerPivot or customers using Tabular model using BISM. Though there are striking similarities in building both the tasks, the mistakes made in both these models are almost the same. Let me talk you through this for a moment.

Whenever we work with PowerPivot or BISM model and we are importing data from a data source, the initial tendency is to use the “From Database” or “From Other Sources” tab and we start building the model. This is a fair step and in doing a data mashup, we bring data from multiple sources.

Many a times we might connect to the same data source using the above step. And to our irony we will encounter the following in our existing connections. In the example below, I have made 3 different query to the same data source and it is now pointing to the same source – 3 times. Isn’t it inefficient? I have seen customers having close to 20-25 connections to the same source but they are bringing some 20-25 different tables. I have observed the performance during “refresh” gets severely hampered by this approach.

So how to get around this? The steps are simple. Whenever you want to connect to the same source – click on the “Existing Connections” button.

Select the source that you want to reuse as a connection. And now select “Open”.

We will be presented with the same wizard as we normally do with “New Connection” Wizard.

The steps are similar when using the same in Visual Studio too when developing BISM models.

Continue reading...


Lync: Join Online meetings quickly March 4th, 2014

Vinod Kumar

Joining a corporate culture has its own challenges and learnings. As we join a team which is geo-dispersed we are often faced with the challenge of collaborating with various at odd hours and getting into an online meeting setup via Lync can be frustrating and time consuming for many.

I have seen a lot of times folks join a Lync call using the button we have on Outlook reminder. No doubt it is the simplest and the easiest step to join a meeting. But for some strange reason we get dropped off that Lync call or close our window we are scouting to Open Outlook –> Click Calendar Tab –> Click the Meeting Invite –> Click on the Lync Call details. Though this works 100% of the time, it is far too many clicks before we landed into our meeting.

With Lync 2013, I found an interesting behavior. As shown below there is a Calendar tab that you can take a quick look at. This lists all the meetings scheduled for today in a snapshot. Cool ain’t it?

If that was not enough, the even cooler part comes next. All meetings which are normal time blocked for various activities will be marked in black. Interestingly, Lync call embedded meetings will be shown in Blue color. Cool right? Now just double click on the blue meeting invite and it will join you to Lync call immediately. No more scrambling around for invite inside Outlook etc.

Continue reading...