Posts Tagged ‘Tips and Tricks’

Excel Tip: Copy Values from Formula cells August 7th, 2013

Vinod Kumar

I show a number of Excel Productivity to my customers every single day. One of the task includes getting some random number to show some charts or some formulae functions. I have used the RANDBETWEEN() function in the below example as an dataset. Assume we have a this range of numbers and now we want to copy ONLY the values from this range?

If you try to do anything with this Excel sheet, the random number is automagically generated. Not something we wanted :). Now the task is to copy ONLY the values into a different column.

First step is to Select the range of cells we want to copy. This can be any range in your workbook to start with. In our example we are using B2 to B6 as shown in figure below.

With the range selected. Move your mouse to the left side of the box. Do you see the “Cross Arrow” in the above figure? That is the trick.

Once you see the Cross Arrow, with your mouse in that over the box –> Right Click and Drag your mouse. You will see a range now where the values will land. In the below figure you see the D2:D6 for example. Now is the time to release the mouse.

Voila, now you will get an awesome popup inside Excel to play with. This gives us interesting options. Feel free to play with them as you wish. For our example, we wanted ONLY values, so I choose the 3rd option.

Now that lands the values, without the formulae into these cells. As you can see the values are copied, since we used the RANDBETWEEN() function – the values in our old cells are now regenerated. This is by design.

Our final output looks like above. You can use this technique in ANY range that contains range with formula. Do let me know if you used this trick anywhere in your Excel explorations.

Continue reading...


Leadership thoughts from Henry Ford July 31st, 2013

Vinod Kumar

We never know where we can get inspiration from. I read quite a bit on various topics and one of the most read topic will be around management and leadership. I have written a number of blog posts on Leadership which you can take a look at, some are: As a Leader, it is always your fault !!!, Know the Informal Leader, Top 10 Managing People Tips, Picture Post: Your View Vs Management View, I am not a Manager, I am a Leader, Characteristics of good leader and many more. I write a number of quotes from different people, but offlate I have been reading more on some of the great quotes from Henry Ford. It is quite surprising that we get inspiration from completely a different industry. Since I am from Mechanical Engineering background, I tend to have a natural inclination to people from this industry and Henry Ford is right on top of my list. Talk about assembly line automation, just-in-time for factory materials and various quality checks – he was a visionary of his time. In this blog, I am calling out some of the famous and interesting quotes I have read on leadership from this legend.

My Top 25 Leadership Quotes

  1. Employers only handle the money – it is the customer who pays the wages.
  2. Vision without execution is just hallucination.
  3. Anyone who stops learning is old, whether at twenty or eighty.  Anyone who keeps learning stays young.
  4. If you think you can do a thing or think you can’t do a thing, you’re right.
  5. Don’t find fault, find a remedy.
  6. Even a mistake may turn out to be the one thing necessary to a worthwhile achievement.
  7. The only real mistake is the one from which we learn nothing.
  8. If there is any one secret of success, it lies in the ability to get the other person’s point of view and see things from that person’s angle as well as from your own.
  9. When everything seems to be going against you, remember that the airplane takes off against the wind, not with it.
  10. If I had asked people what they wanted, they would have said – faster horses.
  11. You can’t build a reputation on what you are going to do.
  12. Failure is simply the opportunity to begin again, this time more intelligently.
  13. A business that makes nothing but money is a poor business.
  14. You don’t have to hold a position in order to be a leader.
  15. I cannot discover that anyone knows enough to say what is and what is definitely not possible.
  16. Thinking is the hardest work there is, which is probably the reason so few engage in it.
  17. Coming together is a beginning; keeping together is progress; working together is success.
  18. If everyone is moving forward together, then success takes care of itself.
  19. It is not the employer who pays the wages. Employers only handle the money. It is the customer who pays the wages.
  20. An idealist is a person who helps other people to be prosperous.
  21. Most people spend more time and energy going around problems than in trying to solve them.
  22. Obstacles are those frightful things you see when you take your eyes off your goals.
  23. It has been my observation that most people get ahead during the time that others waste.
  24. Nothing is particularly hard if you divide it into small jobs.
  25. You can’t learn in school what the world is going to do next year.

There is so much to learn from the legendary and it is always fun to learn from a person who has still been an inspiration for me. Do let me know which of these quotes inspired you the most?

Continue reading...


Excel Tip: Table’s Row-Column Shortcuts July 30th, 2013

Vinod Kumar

I work with tons of tables inside Excel and sometimes there are requirements to do some simple but repetitive activities on a table that I have always wanted to automate. These tasks include inserting a row, inserting a column, deleting a row, deleting a column, hiding a column or row etc. So this blog post will be all about these simple tasks. Our dataset looks like one below.

Inserting a Row and Column

This is one of the easiest shortcut to remember.

To insert a row at current location: ALT + I + R (like (I)nsert + (R)ow)

To insert a column at current location: ALT + I + C (like (I)nsert + (C)olumn)

Our excel will show an output like below.

Deleting a Row or Column

If adding was this easy. Now we might want to delete rows ad columns too right? There are two mode for me. Here is the lengthy route. If you press ALT+ E + D, you will get the following dialog.

Now, using the dialog box can be easy. We can still use some of the hotkeys on the dialog. Check the row and column. So the lengthy route to:

Delete row: Alt + E + D + R + Enter

Delete column: Alt + E + D + C + Enter

I have already calling this as lengthy approach. So there must be something simpler right. You guessed it correct. Use the below shortcuts.

Delete row: Shift + Spacebar + Ctrl + -

Delete column: Ctrl + Spacebar + -

That seems to be slightly lesser keystrokes right? Found it interesting? Let us move to the next shortcut.

Hiding Row or Column

What we want to achieve is to hide column D and Row 12.

So here are the shortcuts for the same.

Hide a Row: CRTL + 0

Hide a Column: CTRL + 9

The last part of this tips would be, to unhide the row or column – select the rows and right click to Unhide. Do you know of a shortcut to unhide columns or rows? Drop a line and let us learn from each other. Hope you enjoyed this tip today.

Continue reading...


SQL BI Knowledge Checklist July 23rd, 2013

Vinod Kumar

Being part of MTC (Microsoft Technology Center) is so much fun because we meet so many people and there is obviously opportunity to learn from others too. Recently, one colleague pinged me asking they were applying for some BI related roles internally and wanted to know if I had something on the lines of SQL DBA Checklist.

After searching my blogs, I didn’t find anything on this subject. So I thought, let me pen down some ideas when preparing for BI based interviews. This by any means is not exhaustive but let me categorize them into Basic, Intermediate and Advanced. So based on what role you apply, the depth on each topic might vary and based on organizational requirement there can be requirements on specific topics which you will need to know in depth.

Basic knowledge

Understand basics of solution area (i.e., foundation knowledge of data warehousing and Business Intelligence, overview of different technologies available in the stack and their features) and have some hands-on experience in using them. Know where to start looking and how to start experimenting if you don’t have knowledge in certain area.

  • Data Management
    1. Articulate what Databases and respective components are.
    2. Articulate how to use SQL Server Management Tools work.
    3. Articulate the OLTP systems.
    4. Consider basic implementation that works with both OLTP databases and data warehouse databases (i.e.: denormalization, indices, partitioning etc).
  • Data Warehousing
    1. Articulate the theories behind different data warehousing design approaches (Inmon’s and Kimball’s).
    2. Articulate the basic concepts and theories behind Multidimensional Modeling (Ralf Kimball’s approach)
    3. Articulate OLAP and differences between OLAP and OLTP systems.
    4. Differentiate and know the different Microsoft Appliances for DW (PDW, Fast Track and others)
    5. Basics of implementing an ETL (theoretical and implementation-wise).
    6. Articulate the concepts and considerations of implementing Master Data Management (theoretical and implementation-wise).
  • Business Intelligence
    1. Articulate the concepts and considerations of implementing a BI solution on top of SharePoint.
    2. Articulate the concepts and considerations of implementing a BI solution in the cloud.
    3. Articulate the concepts and considerations of implementing a Data Mining Solution.
    4. Implement Dashboards- Analytics and Data Mining – Report Generation and Distribution using appropriate Tools.

Intermediate Knowledge

Must know how to apply a topic in new ways to address complex, large-scale, and unusual customer situations with in-depth understanding of developing, implementing and deploying end-to-end BI Solution.

  • Data Management
    1. Write SQL Queries and use built-in functions and create sub-queries.
    2. Create and execute stored procedures as well as user-defined functions.
    3. Implement error handling.
    4. Program with T-SQL.
    5. Improve query performance.
    6. Install, configure, deploy and patch manage SQL Server and its tools.
    7. Upgrade DB Servers.
    8. Migrate databases to new environments and new editions of the SQL Server product.
    9. Implement partial database containment.
    10. Implement user-authentication techniques.
    11. Create user-defined server roles and use them. Implement security.
  • Data Warehousing
    1. Implement solutions using functionalities of SSAS, SSIS, SSDB, MDS and DQS.
    2. Choose the right configuration approach in each tool.
    3. Know to secure the Data Warehouse and security basics.
    4. Know to Debug, troubleshoot and deploy each product individually.
  • Business Intelligence
    1. Implement solutions using Microsoft BI Tools features and functionalities like SSRS, PowerPivot, PowerView, PowerQuery, PowerMaps, PerformancePoint, Azure Reporting, Windows Azure SQL Server, BI Features in SharePoint Online and O365 etc.
    2. Choose the right configuration approach in each tool.
    3. Must know to create and manage organizational performance by using four perspectives for the balanced scorecard: Financial perspective, includes metrics such as revenue, cost, and profit.
    4. Build KPI Details reports to use in conjunction with scorecards in a dashboard. Use KPI Details reports to show additional information about the KPIs and other items that are in a scorecard.
    5. Use a scorecard as data source for trend analysis chart.
    6. Operate how an Analysis Service server instance in SharePoint integrated mode loads, queries, and unloads data, including data processing if the workbook is configured for PowerPivot data refresh.
    7. Identify when to use Reporting Services, Report Builder, or Power View; design/implement context transfer when interlinking all types of reports (SSRS, Report builder, PowerView, Excel, PowerPivot); implement BI tools for reporting in SharePoint (Excel Services versus Performance Point versus Reporting Services); know how to make a subscription strategy or Data Alerts for organizational needs.

Expert Knowledge

Be familiar with integration and interoperability scenarios, custom code, scripts, application solution development, data model design, and end-to-end solution troubleshooting and debugging.

  • Data Warehousing
    1. Design an optimized data model based on industry requirements.
    2. Track data issue from the report up to the data source to identify the problem.
    3. Choose the best SSIS components to provide best performance possible.
    4. Create SSAS stored procedures and extensions.
    5. Create SSIS custom components and scripts.
    6. Implement a sliding window on the DB engine level.
  • Business Intelligence
    1. Extend SQL Server Reporting Services via code.
    2. Implement workarounds to overcome PerformancePoint limitations.
    3. Choose the right SSAS Model (Tabular or Multidimensional).
    4. Advanced MDX skills
    5. Advanced DAX skills.

So here is the brain dump of activities that come to my mind. The idea here is to do a self check if we know the end-to-end story when it comes to Microsoft BI offerings. Do let me know your thoughts.

Continue reading...


Excel Tip: Compare two lists (Method 3) July 19th, 2013

Vinod Kumar

This is in continuation to our series where we want to compare two lists and find the difference. In previous posts we discussed the use of MATCH() and then VLOOKUP() functions. In this post, I will use the other popular method of PIVOT TABLES. Just to reiterate, we have two lists “List A” and “List B” and we want to know how many values in List B is not available in “List A”.

Method 1: Using MATCH() function.

Method 2: Using VLOOKUP() function.

Method 3: Using PIVOT TABLE.

For PIVOT Table, I am going to prepare the above two lists into one mega list. I have added a column to denote Year and merged both these lists like below.

Now that our data is prepared, our next step is to initialize the PivotTable. Goto INSERT Tabe –> PivotTable.

The Create PivotTable dialog comes up. Under Select a table or Range, select our complete table. Under PivotTable Placement-> Select a cell in our same worksheet. I have shown a typical example below.

To aggregate Drag the corresponding columns to get the result I have shown.

Columns –> Year

Rows –> Company

Values –> Revenues

Voila, our task is complete. Now in one shot we have got, the red empty boxes are the new values in 2012 that are not available in 2011. While the blue box below represents, values in 2011 that are missing in 2012 year.

Thanks for reading this far. I hope this was useful and you learnt something new. Do let me know if you want me to write some other topic.

Continue reading...