Posts Tagged ‘Excel’

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...


 

SSMS Tips–Object Explorer Details February 5th, 2014

Vinod Kumar

Today’s blog is inspired from the use of common tools that we use day-in-day-out. For me when playing with SQL Server, it will be SQL Server Management Tool (SSMS.exe). This blog has a number of posts around SSMS Tips like – SSMS: T-SQL Debugger Shortcuts, SQL Server: SSMS Tips II, SQL Server 2012 : SSMS Tips, SQL Server 2012: Snippets and IntelliSense and few more. So do look at them too.

In this blog let me just talk about a simple addendum screen that we hardly use but has tons of valuable information hidden inside SSMS – it is Object Explorer Details. For easier understanding I am just going to use a number of pictures to illustrate what I have actually done :).

Once you are in SSMS, click on F7 shortcut or use the View –> Object Explorer Details keyboard navigation as shown in picture below.

This bring the Object Explorer Details in the usual location where we normally have the Query Window. You can navigate to any node like the Object Explorer or click on a node on Object Explorer and it will populate the related details in the Details pane as shown in the next image. Below we are at the AdventureWorksDW database and have selected the Tables node. This enumerates and lists all the tables in the given database with few interesting properties.

Some additional capability in the Details pane is the ability to search for an object using the Search box on the top and once we are at the object, we can go ahead and click the double arrow (as in figure) to synchronize the Object Explorer View. It is quite an handy feature to use.

The Columns in the Object Explorer Details are customizable and quite interesting. From the header if you right click we will be presented with a list of additional columns that we can include in our view. The list changes based on the node that you are currently on. So “right-click” every header and play to your hearts desire.

In the above options, I selected Data Space Used (KB) and Row Count for my example. We can also remove any unnecessary columns which we don’t want to see too. Now with this data it becomes interesting. In the below figure I have done two things, a) sorted the “Row Count” Column and then b) selected about 10 rows from the OED pane. Now just CTRL+C and take these values to Excel :).

I have pasted the same inside Excel and it looks like below.

Inside Excel we have the freedom to do a number of things. One of the features that I have loved using the suggested charts, formatting of Excel 2013. When the table is selected, a small icon appears on the bottom right side – click on it and I have selected the Formatting –> Data Labels.

Other shortcut to play around is ALT+F1 to get a chart quickly populated. Below is a sample output that I have shown.

I am sure you have played around with Object Explorer Details before. If you haven’t played around till date then I am sure you will do now. Do let me know if you find this interesting and we will followup with many of these tips in the future.

Continue reading...


 

Excel Tip: Undo Shortcuts October 8th, 2013

Vinod Kumar

One of the most used productivity software is Excel. It has always been that way if you are a Data person. Whenever I talk with customers who use reporting or are part of finance operations, their first choice for data manipulation defaults to Excel. There is familiarity of use, predictable and known UI, ease of formula use and many more. So in this blog, let me try to see how much we know of our tool that we use day-in-day-out a.k.a Excel. I have been writing about various Excel tips over my blog and a lot of times people ask some interesting trivia’s that I get to learn from. This post here is inspired by one such question that did come my way and I am happy to share with you.

If I were to ask – “What is the most commonly used shortcut when you are working with Office?”. What is your answer to this question? There are a number of shortcuts, but the survival of all the computer professionals will go for a toss if today’s keyboards missed “CTRL+C” and “CTRL+V”. This is one such shortcut without which our kid’s homework will be complete, our college assignments would become incomplete, and the code that we write would be incomplete and so on. Hands-down this is one of the most commonly used shortcut we would have used in almost any Office product suite – let alone Excel. Lesser known are thousands more shortcuts like these that we can use. In exploration to one such shortcut is our quiz used in later part of this post.

Undo Functionality

So if that was not the quiz, what is the quiz or tip all about anyways? The question now to you is, what are the ways you Undo the operations that you have done inside Excel. There are multiple answers:

Option 1: CTRL+Z

The most widely used shortcut to undo the series of operations is the CTRL+Z key. So we have given you one of the common answers.

Option 2: Using Quick Access Toolbar

This is one of those hidden or must say the second most used method of performing the Undo operation. Very powerful because the dropdown in that arrow shows a history and we can undo to a point in time of our document. This is more than powerful than you think.

Quiz Question

The quiz question is simple. Can you get me a 3rd Option to do the Undo operation inside Excel? This needs to be repeatable just like our CTRL+Z and I must be able to use this shortcut / operation / click and achieve the same result?

Continue reading...


 

Excel Functions: ROMAN, ARABIC and FORMULATEXT August 16th, 2013

Vinod Kumar

If you a power user of Excel, then there is no way you could have lived without tons of awesome functions available with Excel. In this blog post, let me talk about three such awesome functions which are simple yet powerful. These are not the traditional function’s but are used for special purposes. The functions are as below:

ROMAN Function

How many have wanted in creating your kids homework to convert numbers into Roman numerals? Here is the magic function ROMAN. Below are the options available with ROMAN function. Do play around with them to know the differences.

In the below example, I have used the “0 – Classic” option and the output can be seen below.

ARABIC Function

This is an interesting function. This is the reverse of what we did before. Assume the situation where-in you want to convert from Roman numerals back to normal numeric? Now that is the function to take a look. It is ARABIC, pass it the Roman numeral and you will get the integer value.

Formulatext Function

Now this is not part of the ROMAN and ARABIC function. But when playing around with Excel, sometimes I need to show the formula that is behind the cell. Many a times I use F2, copy the formula and paste it with an apostrophe (‘). There is a even better way to do this – it is via the function FORMULATEXT. A typical use of FORMULATEXT is shown below.

I plan to write many more Excel formula in future. Do let me know if you want me to cover any of them in particular. Will line them up !!! Thanks for reading this far.

Continue reading...