Cooked Dynamic Charts ( 3 Flavours)

Dynamic Charts (3 - Flavors) - Coming Soon... »

Jul 09

Cooked Dynamic Chart (3 Flavors)

Hi there,

We have learnt Cooked Dynamic Chart using Table. I promised for few more flavors but the priority posts made me little delay. However, I have taken some time now to complete this exercise before getting delay.

Here we go:

Three more flavours that I want to share with you are

Dynamic Chart Using Functions (Named Ranges)

Dynamic Chart Using Filter Option and
Dynamic Chart Using Camera Tool

First, let us see how to prepare Simple 2D Column Chart before we go to Dynamic Chart using Formulas/Named Ranges.

Raw Data

We have a set of data and want to show the performance of the data through a chart.

Follow the below steps to prepare a normal 2D-column chart:

...

Select the data (CTRL + A)
Goto Insert TAB (2nd one from left)
Click on Column chart from "Charts" Group and select first 2D chart (3rd Group from left)
2D Column chart is ready

To adjust the column chart per our requirements and to create attention to the management:

Client names are long so need to shorten the names
Legend is not required (removed due to space constraint)
Apply name to the chart
Reduce the gridline color and format number in y - axis

To complete the above requirements:

Select the Horizontal axis and

Right click with mouse and select "Select Data"
Click on Edit in Horizontal Axis and select the shorten names in the previous column as shown in the figure.
Click on OK and then OK (two windows two OK's)

To remove the legend click on it and use delete button from the keyboard. That's it.
Click on the chart (anywhere)

Click on "Layout TAB" in the "Chart Tools"
Goto "Chart Labels" in the "Labels" Group.
Select Centered Overlay Title
Right click on the "Title" and Select "Edit Text " to update the title
Once updated move the title accordingly adjusting to the chart (I have moved towards right - end)

Select the gridlines and right click using mouse

Click on Format Gridlines
Use Line Color and Choose Solid line (2nd Option)
Change Transparency from zero (0) to your requirement (I have changed to 84% here)

Adjust the y-axis numbers with $ symbol and decimal values using Format axis -->Number -->Currency -->None
In the same window goto Decimal Values --> change to 0 (zero), default is 2.

That's it. Close the window.

Note: We can adjust the color and font using Home Button or the immediate window which pops up while editing the title.

Till now, we have seen how to prepare a simple column chart and how to adjust its properties.

Here is the final 2D chart look.

Dynamic Chart Using Functions - Flavor 2: (Flavor 1 is here)

Create Named Range:

Create a Named Range for Clients
Similarly create named range for three columns, Rev_Gold, Rev_Diamond, Rev_Silver.

The named range created now will be static and is fixed to the range. Now, our aim is to create a Dynamic range using formula called OFFSET.

Use OFFSET and COUNTA to achieve this result. Here we go:

Goto Name Manager in Defined Names of Formulas TAB

Choose one named range we have created just now
Say Clients
Click on Clients and choose Edit option on the top
In the Edit window, Goto "Referto:" option
Change the reference from static to dynamic like below

=OFFSET(Sheet1!$C$4,0,0,COUNTA(Sheet1!$C:$C))

Let me crack this formula before I proceed.

OFFSET function requires 3 compulsory fields and 2 optional fields. i.e.,

Compulsory Optional

Reference Height

Rows Width

Columns -

The reason for COUNTA here is to count the number of non-blank cells dynamically, like a running total.

OFFSET: Takes the value from D4 which is fixed reference and moves 0 (zero) rows down and 0 (zero) columns right and count the non-blank cells dynamically, if blank cells are updated with a value then COUNTA function will include that value to a chart range.

Similarly, create a dynamic named range for columns:

Rev_Gold: =OFFSET(Sheet1!$E$4,0,0,COUNTA(Sheet1!$E:$E)-1)

Rev_Diamond: =OFFSET(Sheet1!$F$4,0,0,COUNTA(Sheet1!$F:$F)-1)

Rev_Silver: =OFFSET(Sheet1!$G$4,0,0,COUNTA(Sheet1!$G:$G)-1)

Here we use -1 (minus one) to unselect the headers.

The above procedure will help the data dynamic but to make chart dynamic or chart to take effect of above changes

Right click on the chart
Click on Select Data
If the default field selected is Rev_Gold then click on Edit
Change the Series Values: from range to dynamic (use Named range)

='Dynamic Charts.xlsx'!Rev_Gold, Similarly

='Dynamic Charts.xlsx'!Rev_Diamond

='Dynamic Charts.xlsx'!Rev_Silver

='Dynamic Charts.xlsx'!Clients

Now, create new clients and new values it will automatically updates to the chart. MAGIC MAGIC...

Cool right! :)

Now, we will proceed to flavor 3:

Dynamic Chart Using Filter Option: Flavor 3

Firstly, write column names one by one in rows (say Row1, Row2 Row3)

Rev_Gold
Rev_Diamond
Rev_Silver

Now, apply filter and align the data to the center. ( I will tell you why you need to align the data to the center)

As usual prepare a 2D-column chart for Rev_Gold, likewise Rev_Diamond and Rev_Silver.

Then follow the tips given above to impress the management.

Now, move corresponding chart to each cell and adjust the cell dimensions to each chart. (Use CTRL for auto adjust the chart)

Now, Rev_Gold chart is sitting comfortably in cell which contains Rev_Gold simillarly the other two.

Now, change the color of the bars for each chart from Format series --> Fill Option accordingly.

That's it. Cool 2 right :)

Dynamic Chart using Camera Tool - Flavor 4

Prepare a 2D-column chart for all three columns
Required to fit into the cell.
Create filter and create name range for the three headers.
Here I have named it as "Revenue".
Once the charts are placed, activate one cell of the chart say for eg: my first chart is occupied in cell reference G19.
Use camera tool and click on the camera tool. (Camera Tool is available in Excel Options-->Customize -->Choose Commands from --> Commands not in the ribbon -->Camera --> ADD -->OK

Screen Shot will be embedded, use the cursor and place it accordingly to anywhere you feel it fits.
Now, When you select the chart from filter option this screen should be updated accordingly
Here I will tell you how to change the screen from Rev_Gold to Rev_Diamond or Rev_Silver manually.

To find the address of the chart screen -->Goto Function bar and check the address, It is =G19 because I have placed first Rev_Gold chart in the cell G19
To increment from one chart to the next we need to use =INDIRECT("$G$" 18+ $H$10). This formula results 0 (zero) in the cells.

$H$10 refers to =match($G$7,Revenue,0) where $G$7 refers to the validation list that is created for all 3 headers.

Create a name range using this formula say "DCUsingCameraTool"

Now, activate the chart screen and replace the address from =G19 to =DCUsingCameraTool

That's it magic 3 works now.

Cool 3 right!

Check some important links:

Do You Use Dynamic Chart???

Do you have different flavors apart from this. Do you face any challenges while preparing Dynamic Charts at your work.

Your questions, comments, challenges, achievements can be shared through this space using comments section. Let us find the answers to all the challenging queries. If you feel it personal then share it with me through my official e-mail ID: nourgent@yahoo.com.

Thank You

You must be logged in to see the comments. Log in now!

If you have no account yet, you can register now! (It only takes a few seconds)