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

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.

How to add or remove a background?

Want to show some picture as a watermark to your worksheet?

You can easily add or remove the background with just one click.

To add a background Go to - Page Layout tab of the ribbon

On the Page Layout tab - look for Page Setup group and then click Background. Simply browse to the picture file you want as the background and click Insert after selecting the required file.



The selected picture will be applied. To remove simply click the Delete Background button on the same Page Setup group.