Dynamically formatted Charts in Excel $
$
Can we create a dynamic formatting in Excel Chart? Segment with highest revenue •
Excel provided default charts get updated automatically with change in data, but the limitation with these charts is that the formatting doesn’t get updated automatically.
•
Here is a chart showing revenues for 7 different business segments. Segment F highlighted in Orange has the highest revenues
•
In the given example, if we change the data of Column A from 1,000 to 5,000 the formatting does not get updated automatically. Column F is still highlighted in Orange (as the highest Segment with highest revenue segment) which is not correct. revenue (not highlighted automatically)
Email: Support@skillfinlearning.com
Mobile: 9650010925
Steps to create a dynamic formatting in chart
$$ Start with the default chart
Email: Support@skillfinlearning.com
Format the basic chart as per the requirement
Adjust chart data for dynamic formatting
Mobile: 9650010925
Add dynamic Conditional formatting to the chart
Create a default column chart Chart data – Showing revenue for different business segments (A to G)
Select the data> Click on Insert > Select column chart in excel. Select the column chart
Business segment A B C D E F G
Revenues (USD mn) 1,000 1,500 2,000 1,200 2,500 4,200 1,250
d
This is the chart you get in Excel Email: Support@skillfinlearning.com
Mobile: 9650010925
Format the default column chart (1/2)
Format the column chart • Remove the y-axis • Remove grid lines • Name the chart to “Revenues” • Remove chart borders • Add data labels
d
The chart after all formatting looks like this Email: Support@skillfinlearning.com
Mobile: 9650010925
Format the default column chart (2/2)
• To make it visually more appealing, click on the bar and then right click and select Format data series. • Under series options reduce gap to 70%.
d
Revenues 4 200
2 500 2 000 1 500
We get this chart which appears better compared to the one we had before (see previous page).
A
Email: Support@skillfinlearning.com
Mobile: 9650010925
1 250
1 200
1 000
B
C
D
E
F
G
Adjust the data for dynamic formatting
• Add a column to the data set. • Then add a conditional formula, such that if the number in previous column is highest in the range then give that number or leave the cell blank. (See screen shot) • This adjustment in basic data will help identify segment with highest revenues.
Email: Support@skillfinlearning.com
da
Mobile: 9650010925
Add conditional formatting to the chart (1/3)
•
Right click on the chart and click on Select data.
da
• Then Click on Add.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Add conditional formatting to the chart (2/3)
• Then in series name select the name of the cell you are referring to. For instance “Max” here. da • In series value select the relevant data. “Max revenues” in this case.
We get this chart.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Add conditional formatting to the chart (3/3)
• Now click on the orange column and then right click and select Format data series. da • Then Reduce the Gap width to 70% and increase Series overlap to 100%
We get this chart. The Dynamic formatted chart is ready in Excel Email: Support@skillfinlearning.com
Mobile: 9650010925
What happens with change in data
• If you change the revenues for segment A to $5000 you get the chart shown here.
da
• The color of the column changes with change in data.
Are you ready to create your own dynamic chart?
Email: Support@skillfinlearning.com
Mobile: 9650010925
THANK YOU
Email: Support@skillfinlearning.com
Mobile: 9650010925
Visit us: www.skillfinlearning.com Chat with us: +91 96500 10925 Email us: support@skillfinlearning.com