Dynamic Report with Values and Percentage on Column Chart in Excel

Hello, in this video I'm going to show you how to create dynamic reports in Excel with

percentage and values displayed on cluster column chart. Let's get started. Based on what we can see

I've got this dynamic report in Excel and of course there is a list data validation drop-down. So when I

choose let's say critical order priority you can see the report changes and amazingly the cluster

column charts do contain the percentage and the values. So let's see how we can replicate these

from scratch. The first thing I want to do is to copy this order priority label so Ctrl C and

Ctrl V in cell I1 and of course in cell J1. We want to create a list data validation so J1

selected click on the data tab on the data tools click on data validation and we want to allow a

list. Now in the source I'm going to come to the top of column C and click enter and this creates

automatic unique values for me and of course this is a new functionality in list data validation

in Excel. So I can press Alt down arrow key so I can see the order priority to pick from. So the

next thing I'm going to do is to create a sorted unique account manager. So I'm going to come to

cell I4 type in equal sign sub function and the unique and of course I want to get all the unique

values. So close close the bracket click enter and of course this created a sorted unique account

managers name. So I'm just going to call this one account or let me just copy from here so B1

and Ctrl V to paste. Now I'm going to create total sales here. Now for the total sales

I want to use the sum effects function so sumifs and for the sum range I want to aggregate the total column in column G put in a comma and then

for the criteria range one I want to come to the account manager many side put in a comma

for the criteria one I want all this unique account managers name and of course you can

see this spill operator which is part of the dynamic array so put in a comma. Now for the

criteria range two because I want to create a dynamic calculation whereby when we pick from

the drop down everything changes dynamically. So I'm going to come to the order priority in column C

and put in a comma and for the criteria two I want to choose the selected order priority in cell J1

so close the brackets Ctrl Enter and Ctrl Shift down arrow key and I'm going to press Ctrl Shift 4

and of course there we go so we can see this is working fine. Now let's get rid of the decimals

so I'm going to come to the old tab and get rid of the decimals okay lovely. Now I'm going to come

to cell J1 and let's see I want to choose low order priority you can see everything works pretty

fine. Now the next thing I want to do is I want to actually calculate the percentage for each of

the total so I'm going to come here and type in a cosine I'm going to get this value so J4

divided by the sum of the entire values and it's going to give me a spill operator. Now watch what's

going to happen I'm going to press the bracket click enter it's going to work fine but the moment

I copy down the formula I'm going to get a ref error. Now to fix this problem I'm going to come

back to the top cell double click and apply F4 key to make it absolute Ctrl Enter and copy down

the formula and this is working pretty fine. Now what I'm going to do is I want to format this

and represent this percentage with text so I'm going to double click and use the text function

now the text function takes the value all this as the input value put in a comma

Now since we want percentage inside double quotes I'm going to type in 0.0 and then I'm

going to put in the percentage symbol close the double quotes close the text control enter

double click to copy down the formula so we can see the percentage represented as text

we can investigate to and use the is number or is test okay and when I grab the K4 close

the bracket I'm going to get true because now this is now represented as text that's lovely

get rid of this. Now the next thing I want to do is to represent these total sales in text so

equal sign I'm going to use the text function again and select the J4 put in a comma for the

text format inside double quote I'm going to apply the formatting so pound sign comma pound sign pound

sign and then close the double quotes and of course I can put in the GB pound symbol here and

then I'm going to close the bracket control enter there we go so we can see that we've been able to

represent the total sales as text so double click to copy that formula now what I'm going to do is

very simple next now I'm going to use the concat function to concatenate this percentage and the

numbers in text so I'm going to copy this uh control a to select control c to copy escape

I'm going to come here and after the equals I'm going to pump in the concat function

now there's going to be the text one and I'm going to put in a comma now for the text tool I'm going

to use what's called a line feed so to use a line feed in excel I'm going to use the child function

this is line feed put in a comma close the brackets put in a comma and control v and get

rid of this equal to operator and I'm going to close the bracket for the concat function control

enter and then copy that formula so I can get rid of this I don't need this again okay so what I'm

going to do next is to go ahead and create my clustered column chart so I'm going to select

this block of values and come to the insert tab and then under the chart group I'm going to choose

the insert column or the bar chart I will choose the 2d column chart and there we go let's make it

to be a little bit bigger get rid of the grid lines I don't need the grid lines so turn off

the grid lines and of course I want to get rid of this while I just get it out and what I'm going

to do next is to make this to be a little bit bigger so I can right click and choose format

data series and then for the gap width I'm going to reduce to let's say 100 and this is fine and I

can come to the fill bucket and I can change the color so vary colors by point so this is fine and

I can come to the effects so click on the effects and I can choose 3d format so for the top bevel

I'm going to use this bevel round bevel and that's lovely so the final path is to go ahead and display

the percentage and values on the clustered column chart so this is what I'm going to do I'm going to

click on the chart in the chart area and then I'm going to click on this plus sign and choose

data labels option here and I want to choose more options and this automatically adds the

default data labels based on the total sales so I'm going to come here to the labels option

and choose the best option again and I'm going to turn off the original value and then I want

to select value from cells so click on it and of course in the select data label range I'm going

click on cell k4 and press ctrl shift down arrow key and then click on ok so this is the moment

of truth click ok and that's amazing so you can see the percentage and the values on the cluster

column chart and of course you can press ctrl b to make it to be bold I can close this format data

task pane and I can move this down a little bit and then when I come to the cell j1 let's say I

want to choose critical but a priority watch what happens so critical there we go so you can see

the report dynamically change and of course the chart also responded to the changes so you can

see the percentage and of course the values let's say I want to choose medium order priority

there we go so everything works dynamically so this is basically how we can create a dynamic

report in excel that display percentage and values on the cluster column chart

I trust you enjoyed this video if you do like comment and share with your friends

thank you and bye for now cheers