Vertical Sparklines Bar Graph linked to cells - can you change Max value, using a Cell Reference ?

[StrawberryDreams]

I found an interesting way to create a Vertical bar like graph using sparklines instead of trying to physically drag and overlay a Table bar graph chart to line up with column cells.

I am wondering if there is a way to add a cell reference to the Maximum Value in the Vertical Sparklines Axes setting? I tried to enter an = A2 and it will only accept integers...

I can make a guess what the max value will be , but it would be more dynamic if it could adjust as the values change. Is this possible ?
Secondly I noticed in the Change sparklines color that the weight line option is greyed out, Is this because I'm on a MAC and it's only available for Windows ? Would love if the bars could fill the entire width of the column ( visually no gaps between columns ).

the xl2bb did not copy over the sparklines properly. I will post a screenshot of what it looks like too.

Basic data calculator test 9.9b.xlsm
	A	B	C	D	E
1	MAX Value	200	50	125	350
2	350
3
4
5
6
7
8
9
10		500 is the set Maximum Value I placed in sparklines Vertical Axes
Sheet1

Cell Formulas
Range	Formula
A2	A2	=MAX(B1:E8)

 

[awoohaw]

View attachment 111039

Well, another of my personal rules regarding visualization and reporting is that they are ALWAYS separate from my data. With that I never have to move things around.

 

[awoohaw]

Oh this is nice ! is this using sparklines ? Is it possible to add an outline to the coloured bars too ?

no, they are charts placed into cells. Make a chart, use ALT-drag/snap to corners.

 

[awoohaw]

look at this:

 

[StrawberryDreams]

no, they are charts placed into cells. Make a chart, use ALT-drag to corners.

Ok thanks, Yes a bit tedious as you say, but maybe not much different then dragging and sizing an entire range of data in a chart and lining up to the cell references as I did before.

Ya I've found since I'm still learning that merged cells can cause data issues sometimes or incompatibility. When you are trying to design a nice looking spreadsheet that fits all the cells and column and balance it with other visual aids it can be a bit of a challenge... Not like my older Filemaker Days where you create every cell in its own box that you can move around . I guess this is what the VBA combo box gets into... I haven't tried to learn that yet. Still trying to develop my data to make sure it works . Then after will see if I need to change it to VBA or user form style ( if possible ).

Cheers

 

[awoohaw]

Here is the mini chart sized to fit the range of numbers below:

 

[StrawberryDreams]

For example. I have a dropdown data validation in my layout that has theee or four words in it. I want just above that dropdown to have 4 cells with numerical values in each cell. In order to have the drop down fit the text I need to merge all four cells. If I drag the drop down to allow the text to display, then the one cell above with a number is huge and the other cells to the right are off the layout lineup and much smaller.

 

[StrawberryDreams]

Here is the mini chart sized to fit the range of numbers below:

View attachment 111044

Can you show me a zoomed in look of your min chart with the grid lines on to see what the two chart box corners look like ? When I drag the plot box to the edge of the chart box it can still go past. Guess I should zoom to 200% and try again.

 

[awoohaw]

Can you show me a zoomed in look of your min chart with the grid lines on to see what the two chart box corners look like ? When I drag the plot box to the edge of the chart box it can still go past. Guess I should zoom to 200% and try again.

select plot area
select format plot area

drag corners of plot area to corners of chart area:

(notice plot area still selected):

Select Chart Area to ALT - Drag to cells you want the chart in.

And finally, I just saw this: In Chart Format is "snap to grid", click that and the chart will resize as that cell is resized/moved.
But you have to certain the chart is in the cells, and it will resize proportionally, so all columns the chart covers must be the same width.

 

[awoohaw]

For example. I have a dropdown data validation in my layout that has theee or four words in it. I want just above that dropdown to have 4 cells with numerical values in each cell. In order to have the drop down fit the text I need to merge all four cells. If I drag the drop down to allow the text to display, then the one cell above with a number is huge and the other cells to the right are off the layout lineup and much smaller.

All i can say to that is try to figure a different way. Once I have a dashboard set, I try to never move things around.

There are plenthy of visualizations videos on youtube. MyOnlineTrainingHub, Leila Gharani, ExcelIsFun, and Mr. Excel all have them.

 

[StrawberryDreams]

All i can say to that is try to figure a different way. Once I have a dashboard set, I try to never move things around.

There are plenthy of visualizations videos on youtube. MyOnlineTrainingHub, Leila Gharani, ExcelIsFun, and Mr. Excel all have them.

Cheers thanks for all your help , advice and links !