Archive for June, 2013

Excel Tip: Custom Conditional Formatting on User Input June 13th, 2013

Vinod Kumar

This blog post is an extension to our previous post on Excel Tip: Custom Conditional Formatting. I highly recommend you to read that post because we will be building on the same concept and take it to next level.

In this blog post the problem statement is simple:

  1. Have user input for Sales or Spend.
  2. If “Sales” is given, we want to highlight “Sales” values greater than 70.
  3. If “Spend” value is given, we want to highlight all “Spends” values lesser than 30.

Let us see how to achieve this step-by-step.

Initial DataSet

This is the same dataset I used before for Excel Tip: Custom Conditional Formatting. Nothing magical about the dataset :).

Creating Filter Dropdown

We will try to create an Input element, in our example below I am creating a Label of Filter and adding a cell C1. Let us next add the dropdown.

With Cursor on C1, Select Data Tab –> “Data Validation”.

This brings the Data Validation Screen. In this screen, Select Allow –> List. Under Source, we will select our sources of values in C3 and D3. This will be for values Sales and Spend.

Now click “OK” on Data Validation screen. Now you will be presented with a Dropdown on C1 cell and selecting that gives us two values as below.

Starting Conditional Formatting

Now the process for conditional formatting is similar to the post from Excel Tip: Custom Conditional Formatting. Let us select the section C4-D9 region and select Home –> Conditional Formatting and “New Rule…”.

In our Formatting rule screen, let us add a simple rule to start with. Here we have entered a Formula of =IF($C$1=”Sales”, $C4>70). Also don’t forget to change the Format color before hitting OK.

This formula shows conditional format of Sales > 70 when our Dropdown value shows Sales. A sample output is shown below.

Interestingly, we have not yet written our rule for “Spend”<30. So Goto Conditional Formatting –> Manage Rule and Edit our Rule again. Here we have used the Syntax of IF(Condition True, then, Else(If (condition) true, then)).

The actual condition looks like =IF($C$1="Sales", $C4>70, IF($C$1="Spend",$D4<30)).

Click “OK” and get back to our C1 Dropdown. Select Spend and now you will see the formatting change.

We can extend this concept to a number of formatting options. I have found this really powerful use of Conditional Formatting and works like charm in almost most of the earlier versions of Excel.

Hope you are enjoying this series of Excel Tips. Keep locked in, we have many more coming. If you liked it, please drop a comment.

Continue reading...


 

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

INQUIRE Tab

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.

Conclusion

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.

Conclusion

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


 

Installing SQL Server 2012 Analysis Services–Tabular mode June 6th, 2013

Vinod Kumar

I wrote last week about DAX (Introduction to DAX in Excel) and we did mention there are many more blog posts on its way on that topic in future. I had a few requests from people on how to install SQL Server 2012 Analysis Services Tabular Mode. This is a picture blog post in a way showing the Step-By-Step process.

Q: The reason I wrote this is because a customer asked me with SQL Server 2012 Analysis Services already installed in their environment, how can they convert it into a tabular mode.

A: The matter of fact remains, we cannot convert the already installed multi-dimensional SSAS instance into tabular mode. We need to install a separate instance for this.

Installation Steps – Tabular mode

So if you have the SQL Server 2012 installation binaries ready, start the “setup.exe”. And go through the wizard as follows:

Select “New SQL Server stand-alone installation or add features to an existing installation” option.

Let the Setup Support Rules run and Click “OK”.

Ignore the Warning for “Windows Firewall” for now and press “Next”.

Select “Perform a new installation of SQL Server 2012”. Here is assumption is we already have an SSAS multi-dimension installation. If this is a fresh installation of SSAS, you can choose the second option.

If you have a valid Product Key, enter the same here or feel free to use an Evaluation version. Also go through the License Term and move next to “Setup Role”.

Under Setup Role, select “SQL Server Feature Installation”.

Select “Analysis Services” under the Feature Selection.

Press “Next” after checking the status in “Installation Rules”.

Under Instance Configuration, Give a “Named instance” – here we are calling it as POWERPIVOT. Our assumption is our default instance is the multi-dimensional SSAS instance. Press “Next”.

Check the Disk Space requirements and Server Configuration details and Click “Next”.

Under “Analysis Services Configuration” screen, select the “Tabular Mode”. This is the most important step. Also make sure to add the current user from the below button. Press “Next” to move ahead in installation.

Check the “Installation Configuration Rules” pass and you will be in “Ready to Install” screen as above. Please take a note of the “Configuration file path” and feel free to check the ConfigurationFile.ini if you want to use it in future for unattended installation. At this step, we are all set and ready to install. Click the “Install” button.

You will be presented with the final screen as above post install. This also has the Summary log file for installation. If any of your installation errors out, the details are present in this txt file mentioned.

This completes our installation of SQL Server 2012 Analysis Services Tabular Mode. In future blog posts we will write queries to this instance and show the functionality possible with this mode of installation.

Continue reading...