« OFFSET function trick(s) - REVEALED | Dynamic Charts (3 - Flavors) - Coming Soon... » |
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.
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
- 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.

- Create a Named Range for Clients
- Similarly create named range for three columns, Rev_Gold, Rev_Diamond, Rev_Silver.
- 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
Do you have different flavors apart from this. Do you face any challenges while preparing Dynamic Charts at your work.
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)
Recent comments