Today we are going to discuss about tricks in Excel for better visualization and for better interactivity. Lot to learn, let's dive in.
Cards in Excel Dashboard using shapes:
You would have seen card visuals in POWER BI, we are going to replicate the same in Excel dashboard with some interactivity.
Consider column A:C is dashboard part,
Our source data resides in O3:P15,
Calculation part resides in R4:S5.
You can keep this source and calculations anywhere you want.
I placed it in same sheet for better understanding.
In Dashboard, drop down is provided in C3 cell to be able to select months.
Formula used in R4 cell,
=INDEX($P$4:$P$15,MATCH($C$3,$O$4:$O$15,0))
This will help us to pick the sales based on month selection.
Formula used in R5 cell,
=$C$3&" Month Sales"&CHAR(10)&TEXT(R4,"$ #,#.00")
This will help us to get dynamic label for our card visual,
CHAR(10) helps us to get line feed or Alt + Enter. So, we can see the dynamic label and sales values in separate lines in card.
In dashboard, insert rounded rectangle shape and format based on your preference. Once, you are done, select the shape and in formula bar type '=' and refer the cell R5.
Now, we can have the card visual in excel with dynamic label.
Dynamic Chart Title:
As we did in Shapes, we can add dynamic labels to text box and chart Titles.
Here, we are going to discuss about chart title.
Our source data resides in column O:S,
Calculation resides in column U:V.
Formula used in V4 cell,
=INDEX($P$4:$S$8,ROWS($V$4:V4),MATCH($C$3,$P$3:$S$3,0))
Formula used in E1 cell,
=$C$3&" Month Sales"
You can hide this by setting the cell font color to white.
Insert the chart in dashboard based on the range U3:V8,
Do the necessary formatting. Once done, select the chart title and in formula bar type '=' and refer E1 cell.
Replace Columns in Chart with Shapes:
We can replace columns, bars and even markers with shapes.
Insert FlowChart:Delay shape and format based on your requirement, Copy that shape and select the columns in chart and paste, that's it done.
But, we need to do bit more customization and data preparation to make it look professional.
Data Preparation:
Formula for Top part,
=MIN($X$4:$X$8)
This for Shape, so that it will look same for all columns, otherwise shape will get stretched based on column size.
Formula for Bottom part,
=X4-Y4
Now, select Product, Top and Bottom data by pressing CTRL key and insert default chart using shortcut key ALT + F1.
Change chart type into stacked column chart.
Make sure part for the shape is stacked at top.
Insert FlowChart:Delay shape, copy that shape and select the columns stacked at top in chart and paste.
Chart Customization:
Change the colors for both top and bottom part.
Select the bottom part, click on the '+' icon in right side top corner of the chart and insert data labels and place it inside base.
Select the data labels and press CTRL + 1 to open format pane,
In data label format options, check the box for Value from Cells and select products in data.
Uncheck the box for Values, now you can see the product names in base of bottom part.
Do the same for Top part but place data labels at inside end and select the values from Sales column.
Commentaires