Posts Tagged ‘Management’

Excel Tip: Custom Conditional Formatting June 12th, 2013

Vinod Kumar

I hope you are enjoying the Excel Tips series I planned for this week. I got feedback from people to show some of the old tips I show them with Excel 2007 or Excel 2010 version. So here is a simple blog post which will show how to use custom Conditional Formatting with Excel.

So for this blog post, we will use a simple table. We have 3 columns and some values to play with.

Conditional Formatting

Highlight cells from C3-D4 and select out out-of-box conditional formatting options. In the below figure we have selected, Home Tab –> Conditional Formatting –> Data Bars. Hover over the various options to check how it looks in our table.

In below example, I have shown how it looks using “Orange Data Bar”.

When you want to have a gradient of colors, select the “Color Scales” option and choose one of the options. Below I have shown how data will look in a “Green-Yellow-Red” color scales. This is one of the easiest color coding where Red means Low values and Green mean high values.

Now these are basic out-of-box features. I hope you have used them in the past already.

Custom Conditional Formatting

With out-of-box options available, what if we wanted to color code our rows based on our requirement. Let me describe my requirement, I want to highlight rows who’s Sales value is greater than 60.

To achieve this, Select “Conditional Formatting” –> “New Rule …”.

This will bring up the rules dialog box. Here select “Use a Formula to determine which cells to format”.

As per the requirement we want the “Sales column” value to be greater than 60. So in our “Formula” bar, enter value of “=$C3>60”. Next Select the Format button.

This brings up the Format dialog as above. I have selected the “Fill” tab to fill the cells with Green color. Feel free to use your own color coding. Now the final – “New Formatting Rule” dialog looks like below.

Clicking “OK”, the table now highlights the rows with Sales value greater than 60.

How cool is that? Formatting can be quite powerful and we can add interesting extensions to this. Maybe I will write the same next. Do let me know if you learnt something new today. Thanks for reading this far.

Continue reading...


Excel Tip: Options using INQUIRE June 11th, 2013

Vinod Kumar

Yesterday I wrote about Trace Precedents / Dependents and this blog post is an extension to what we have with Excel 2013. In this blog post we will see how to use “INQUIRE” tab and how it can be yet another powerful visualization to find our chain of dependencies inside Excel worksheets.

Enabling Inquire Tab

The first step is to enable the INQUIRE toolbar. This is not enabled by default. Open Excel 2013, in a blank document select “File” –> “Options”. We will be presented with the below screen.

Select “COM Add-Ins” and click “Go…”. We will get this mini dialog. Select the checkbox “Inquire” and click “OK”.


Once we have done the above step, you will find a new Tab added called as INQUIRE as you can see below.

In this blog post, we will show the following:

  1. Cell Relationship
  2. Worksheet Relationship
  3. Workbook Relationship and
  4. Workbook Analysis

We will use the same worksheet as used in Trace Precedents / Dependents.

Cell relationship diagram

This in a way is like our Precedent and Dependent diagram but is way more cool. This will show us the dependency like a fishbone diagram. With our cursor in “B7” cell, we are going to click on Inquire –> “Cell Relationship” button. We will be presented with the below screen. I generally like to keep the defaults and hit “OK”.

The output will look like below. We see this because we selected the “Initial number of expansion levels” as “Limited –> 1”.

We can expand it to the next level and we will be presented with a more detailed view. Here we are seeing B7 –> Dependent on B9, B70, D25, D15 and so on. How cool is this when compared to our Trace Precedents? We are yet not over. There is more.

Worksheet Relationship Diagram

Now this is new and an great extension. Here click on “Inquire” –> “Worksheet Relationship”. This will show us the dependencies of each of the Worksheets.

In our example above, it is showing the XLS sheet “Statement 2012-11.xlsm” has various sheets like Documentation, P&L Statement, CASH_FLOW etc. While it shows for example “Cost Cap” sheet is dependent on “IM Data”, “GL Data” etc which in this case are a different File and Sheet. Cool isnt it? We arent done yet !!!

Workbook Relationship Diagram

This is taking our dependency diagram to the next level. Especially for file like P&L data can come from a number of external sources. And this option shows how we are dependent on other external sources. Also it also shows if that file currently exists or not :). Cool right.

In our example, I selected “Inquire” –> “Workbook Relationship” to get a typical output like below.

In the above example we can see current XLS sheet is dependent on Northwind1.mdb (access DB), table.html (from web), and a number of other excel sheets. A “Red mark” indicates the object is not available now.

Workbook Analysis Report

This is a great summary of information that is available in our Workbook. It shows a number of information shown, I am listing some of them here.

  1. Visible Sheets
  2. Hidden Sheets
  3. Formulas
  4. Formulas With Errors
  5. Formulas With Logical Values
  6. Formulas With Numeric Values
  7. Formulas With Date/Time Values
  8. Formulas Without Cell References
  9. Formulas Referencing Blank Cells
  10. Formulas Referencing Hidden Cells
  11. Formulas Referencing External Workbooks
  12. Positive Formulas
  13. Negative Formulas
  14. Duplicate Formulas
  15. Cells With Dependents
  16. Cells With Comments
  17. Cells With Validation Criteria
  18. Cells With Conditional Formatting
  19. Invisible Cells
  20. Blank Cells
  21. Hidden Rows and Columns
  22. Named Items
  23. Named Items With Errors
  24. Warnings

And many more that I have not listed. The below diagram shows a snapshot of the same information for our Demo P&L Excel sheet.


Hope you found this blogpost interesting and you got to learn something new. This feature is part of Excel and worth a look any day. We will look at many more Office tips coming this week. Stay tuned on the series.

Continue reading...


Excel Tip: Trace Precedents / Dependents June 10th, 2013

Vinod Kumar

I am a big fan of Office products (Word, Excel, PowerPoint, Access and OneNote) and I always find it interesting to share my tips with customers almost every single day. I have found it tough to explain them over my blog till date except for one post (Excel Tip – Find invalid data). I am sure if you have played around with Excel 2013 there are loads of features to look out, I am not going to explain the common super cool features of PowerPivot, PowerView, GeoFlow etc. Those will be way in the future maybe :).

So stay tuned because this week will be “Excel 2013 Tips and Tricks” that I love to share with customers daily and now with you. Some of these features are already available in previous versions but they may look refreshing every single time. Incase you don’t find the option I am talking, it is an Excel 2013 feature.

Trace Precedents and Trace Dependents

In this blog post, we will look at the options available in “Formula’s” toolbar of Trace Precedents and Trace Dependents. We can will find these options:

Let us assume we have a P&L statement which is using tons of Excel references to different cells. In our below example, you can see that we have selected the cell “D13”.

This is a very big Excel sheet spanning multiple sheets and running close to 80-90 rows of cross references.

Trace Precedents

Now let assume you want to check which are the values that affect the value in cell “D13”. This is where “Trace Precedents” help. Click the same from “Formulas” toolbar.

You will now magically see an arrow (as above), this means the value in D13 is dependent on values coming from “B11” in our example. If there are other dependent values then we will get multiple arrows. Here is a typical example below:

Trace Dependents

Now the “Trace Dependents” on “Formulas” toolbar does exactly the opposite. This option shows which are the cells affected by values from this cell. In our example it is cell “D13”. Interesting part is, if we click the “Trace Dependent” again, it runs the dependency in recursion. It can give you an visual treat like below on values that can get affected because of “D13” (in recursion) in our example.

This is a powerful representation and can be quite useful too. To remove the arrows, use the “Remove Arrows” button from “Formulas” toolbar.


How cool is this? Did you find it useful? Have you used this feature in the past? Do let me know how you have used this feature in your daily life. More such fun coming your way this week :).

Continue reading...


SQL Server 2012: ColumnStore Part II June 5th, 2013

Vinod Kumar

I wrote about ColumnStore Indexes before and those were around the basic restrictions and things to consider while working with ColumnStore Index with SQL Server 2012. In this blog post let me take a moment to look at the boundaries, use with SSMS and other diagnostics information. Obviously some of the restrictions have been relaxed for next version of SQL Server 2014. That will be for a different blog post.

Datatype restrictions

In SQL Server 2012, columnstore index creation is supported only for the following data types:

  1. char, varchar, nchar and nvarchar (except varchar(max) and nvarchar(max))
  2. decimal (and numeric)
  3. int, bigint, smallint, and tinyint
  4. float, real
  5. bit
  6. money and smallmoney


There is now a new keyword COLUMNSTORE in the CREATE INDEX DDL which is required to create a columnstore index. More about it can be read from MSDN.

CREATE COLUMNSTORE INDEX <Name> ON <tableName>(<Cols>)

Index hint

We can force an index hint to use the columnstore index in a query if required:

… FROM mytable WITH (INDEX (mycsindex)) …

Query hint

We can either use a table hint to force the use of a different index or we can use a new query hint: IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX (MSDN). This new hint will prevent the use of any nonclustered columnstore indexes in the query. Below is an example of using the hint to prevent use of any nonclustered columnstore index in a query:

SELECT DISTINCT (SalesTerritoryKey)
FROM dbo.FactResellerSalesCopy


There are a number catalog views which will show different values for ColumnStore. I am listing a few here:


type : 6



data_compression: 3

data_compression_desc: COLUMNSTORE


key_ordinal: 0 (not a key column; columnstore index does not have a search key like a row-based index)
is_descending_key: 0 (default value; not a key column and index is not "sorted" like a row-based index)
is_included_column: 1 for every column mentioned in the create columnstore index ddl.


page_compression_success_count – Has a value of 0 for columnstore index.

Showplan Enhancement

Three additional properties have been added to the showplan data for columnstore indexes:

  1. Storage – row or columnstore
  2. EstimatedExecutionMode : row or batch
  3. ActualExecutionMode : row or batch

SSMS Enhancements for Columnstore

I am outlining some of the visible enhancements for columnstore with SQL Server 2012 Management Studio. Firstly, Columnstore indexes are represented by a different icon than traditional row based indexes.


The next UI changes is for creating a New Columnstore Index.


The third part is around adding columnstore columns as we create the index.


Final Words

These are some of the basic enhancements with SQL Server 2012 I thought was worth sharing. Do let me know if you have used columnstore index in your production environments and what are the scenarios you found using this type of index useful.

Continue reading...


Things you wish to have done when you look back on Life May 29th, 2013

Vinod Kumar

Don’t cry because it’s over, smile because it happened ― Dr. Seuss

Life is full of twists and aspirations that we always keep running against. How many of you ever looked back on your life and said – I should have done this or that. It is human instinct to always look back and wish things were not the way it has been for ages.

You only live once, but if you do it right, once is enough ― Mae West

In this blog post, let me take my top 21 wish list that I always love to share with folks. This is not complete list but just representative to what I thought comes to my mind. I am sure you will have a bigger list and do feel free to share them.

Life is what happens to you while you’re busy making other plans ― Allen Saunders

  1. Be more confident
  2. Live life more than you just existed
  3. Gain one good habit once a month
  4. Live the moment and enjoy it
  5. Love more than you hated
  6. Forgive people more than holding back
  7. Laugh more than the times we cry
  8. Break all bad habits
  9. Be kinder

    Everything you can imagine is real ― Pablo Picasso

  10. Trust yourself more and follow your intuitions
  11. Stop eating fast-food :)
  12. Get outside the comfort zone
  13. Boldly show how you were right to others
  14. Overcome your fears
  15. Mastering yourself
  16. Appreciate people for who they are
  17. Dealing with negative people
  18. Enjoy the silence
  19. Have no regrets
  20. Life is about doing and just not saying
  21. Enjoy today more than yesterday

Life is like riding a bicycle. To keep your balance, you must keep moving ― Albert Einstein

Life is beautiful and fun if we regret less and chase our dreams. So do feel free to share me your list too. Does this resonate something in your mind?

Continue reading...