Conditional Chart

You must have heard and used a concept called Conditional Format in your excel dashboards.... Ever heard of a conditional chart?

If not, let me tell you something about it....

An old friend of mine called me today and asked for some help. He was trying to create a chart which picks up the data dependent upon a condition. In other words, the data the chart should display was dependent upon the result of a formula or a user input.

My immediate response was we can use the VBA code to create a dynamic chart. But here comes the major problem... he was looking something in excel alone without the use of a macro.

I had another solution for him... it was using the named ranges. Yes, the old friend (name range) of ours, which helps us reduce the size of excel formulas can also help in creating a dynamic chart.

In the next few steps I will explain the problem first followed by the non VBA solution for it.

Problem

The requirement is of a single chart, which shows Result A series as the data if Result A is chosen from a drop down in cell D2. Similarly if Result B is choosen in cell D2, then the chart should show Result B series.

Solution

There are ways in VBA to create this kind of chart but if you need a non VBA way, then follow the steps given below:

Step 1:

Create two named ranges, one each for X Axis labels given in column A and one for data to be shown on the chart Y Axis.

To create the X Axis label, go to the Defined Names group on the formula tab and click on Define Name, give the name as XRange and select the range from the sheet.

To create the name range for Y Axis data series, go to the Defined Names group on the formula tab and click on Define Name, give the name as YRange and use the following formula in the refers to field

=If(Sheet1!$D$2="Result A",Sheet1!$B$5:$B$9,Sheet1!$C$5:$C$9)

This formula is first checking the contents of cell D2 and dependent upon it giving the range to YRange

Now add a chart from Insert tab, Charts group by selecting Column & the first option in the 2D column

Once the chart is displayed, click on the Select Data and click on the Edit button for the Legend Entries (Series)

In the Edit Series Dialog Box, give a caption for the series name and provide the YRange name range as the data to be displayed. You need to use File Name ! Name range to specify the name range

Now click on OK, and click on Edit for Axis Labels

Provide the XRange as the axis label

And the chart is ready. You can choose Result A or Result B from cell D2 and the data in the chart will change depending upon your selection.

Result A

Result B

No matter if India lost another one.... here is another excel tip

Cell Function


This function examines a cell and displays information about the contents, position and formatting.

=CELL("TypeOfInfoRequired",CellToTest)
The TypeOfInfoRequired is a text entry which must be surrounded with quotes " ".


The following example uses the =CELL() function as part of a formula which extracts various information

Count functions.... contd....

Continuing where I had left last week on the count functions... let me introduce to you one more count function

Lets have a look at the CountIf function. CountIf alows you to count based on a given criteria. For example you want to count the number of employees with sale above $1000 in the below data

The formula which you can apply will be
=COUNTIF($C$2:$C$9,">1000")

In this formula ">1000", is the criteria, which comprises or the operator greater than. All the relational operators like >, <, <=, >= & <> can be used. These is no need to write equal to, if you want the exact match of 1000 (use "1000" in that case).

Various Count Functions

And yet again... its been a while since I wrote something last time..... Can't seem to figure out where my time goes... This time no promise of being a regular on this blog... Though will try harder to update it regularly....

Tip that I have for today is the difference between various Count functions.

We all know count function is used to count the number of entries but very few know that the Count function only counts the numeric entries and skips the text and the blanks.

To count the text entries along with the numeric entries, in other words to count all the non blank cells, the function is CountA and to count the blank cells the function is CountBlank.

Here is a screenshot detailing the difference.... enjoy the Excel 2010 look as well.

Formula on request

I have been requested a formula, which checks the contents of column A, if it is a number and greater than 0, then number is to be multiplied by 100. If it is a number but is less than 0, the number is to be multiplied by 50. However if it is not a number but some text, text should appear as the result.

Here is it, Have written only single formula, which gives the desired result. Have a look!!!!!

How to add jpeg picture in your comments?

How to add jpeg picture in your comments?

Interesting question??

The answer is, yes, it is possible. Not directly though but you can definitely add a picture to your comments. It can not only make it look different but can also be useful in terms of space utilization.

Want to learn how? Lets have a look at the steps:

1. Add the comment to the cell by right clicking on the cell & by selecting Insert Comment.

2. Select the comment from the red pointed area (the border of the comment) & right click on the selected area. Select Format Comment from the menu.

3. The below dialog box will appear. This is the major point in the steps. Make sure you get a similar dialog box. The best way to find out is, it will have more than one tab. If it shows only one tab, try the step 2 again.

4. Select Color and Lines & then from Fill Color drop down select Fill Effects.

5. From the Fill Effects dialog box select the picture you want to add to the comment by using "Select Picture" button. Once selected, click OK and then OK on the Format Comment dialog box as well.

6.  The end result is a different looking comment :)

You can always try with Katrina Kaif's or Sunny Leone's picture :-P

5 of my most used commands

Thought of listing something which I frequently use. Here is a list of 5 of my most used commands in Excel:

Sort your data using Data - Sort & Filter

Apply Data Validation using Data - Data Tools

Apply Conditional Formatting using Home - Styles

Change the format of text using Home - Font

Create a Pivot table using Insert - Tables