How to align the yaxis scale for all charts in the sheet?

%3CLINGO-SUB%20id%3D%22lingo-sub-1428266%22%20slang%3D%22en-US%22%3EHow%20to%20align%20the%20yaxis%20scale%20for%20all%20charts%20in%20the%20sheet%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1428266%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%3C%2FP%3E%3CP%3EPlease%20see%20my%20sample%20workbook.%3C%2FP%3E%3CP%3EI%20have%20a%20few%20pivot%20charts%20in%20a%20worksheet.%3C%2FP%3E%3CP%3ECurrently%2C%20it%20is%20hard%20to%20compare%20the%20charts%20as%20their%20Y-AXIS%20scale%20is%20different.%3C%2FP%3E%3CP%3EI%20would%20like%20to%20align%20the%20scale%20of%20all%20charts%20in%20the%20worksheet%20based%20on%20the%20maximum%20scale%20in%20all%20charts.%3C%2FP%3E%3CP%3EFor%20example%2C%20when%20all%20departments%20are%20selected%2C%20the%20maximum%20scale%20is%20600%20(chart%201)%2C%20hence%20the%20scale%20for%20chart%202%20and%203%20to%20be%20set%20as%20600%20too.%3C%2FP%3E%3CP%3EFor%20example%2C%20if%20dept%20AZ%20is%20selected%20in%20slicer%2C%20the%20maximum%20scale%20of%20the%203%20charts%20is%203.5%20(chart%201)%2C%20hence%20the%20scale%20for%20chart%202%20and%203%20to%20be%20set%20as%203.5%20too.%3C%2FP%3E%3CP%3EAppreciate%20the%20advice.%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1428266%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1429173%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20align%20the%20yaxis%20scale%20for%20all%20charts%20in%20the%20sheet%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1429173%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F684091%22%20target%3D%22_blank%22%3E%40cswshaun%3C%2FA%3E%26nbsp%3BPlease%20see%20the%20attached%20sample%20solution.%3C%2FP%3E%3CP%3EIn%20your%20Data%20Tab%2C%20I%20will%20add%204%20more%20data%20rows%20that%20caps%20the%20value%20for%20Dept%20As%20and%20Dept%20Bs%20(Admin%2C%20Manual)%20to%20max%20of%20available%20values%20above%20(I%20round%20it%20up%20but%20you%20can%20do%20whatever%20you%20want%20as%20long%20as%20those%20values%20are%20the%20same%20and%20are%20the%20highest%20value%20in%20Value%20column.%3C%2FP%3E%3CP%3ENote%20that%20in%20column%20A%2C%20for%20the%204%20new%20data%20rows%2C%20you%20must%20type%20in%20a%20space.%20This%20help%20to%20scale%20all%20charts'%20upper%20bound%20to%20the%20same%20value%20but%20not%20show%20the%20label%20for%20those%20series%20on%20the%20horizontal%20axis.%20If%20you%20leave%20column%20A%20for%20those%204%20new%20data%20rows%20as%20empty%20instead%20of%20a%20space%2C%20their%20labels%20will%20be%20shown%20on%20horizontal%20axis%20as%20(blank).%3C%2FP%3E%3CP%3EThen%20you%20go%20to%20each%20of%20your%20charts%2C%20select%20only%202%20series%20point%20for%20those%20new%20data%20rows%20on%20each%20chart%20(they%20are%20the%20last%202%20highest%20vertical%20line%20at%20the%20right-most%20of%20your%20chart)%20-%20carefully%20to%20select%20only%20the%20series%20points%20alone%2C%20not%20the%20whole%20series.%20Then%20you%20right%20click%20on%20each%20vertical%20line%2C%20select%20Format%20Data%20Point%2FFill%20(the%20icon%20of%20Paint%20Can)%2FSelect%20No%20Fill%2C%20No%20Border%20to%20make%20them%20invisible.%20Finally%20select%20each%20data%20labels%20for%20each%20new%20data%20row%20on%20top%20of%20those%20vertical%20lines%2C%20delete%20them.%3C%2FP%3E%3CP%3EI%20also%20remove%20the%20Grand%20Total%20(off%20for%20both%20Rows%20and%20Columns)%20in%20each%20of%20your%20pivot%20tables%20because%20you%20will%20not%20want%20to%20include%20Grand%20Total%20in%20the%20charts%20which%20is%20too%20large%20as%20compared%20to%20individual%20values%20and%20can%20skew%20your%20charts%20significantly.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1432349%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20align%20the%20yaxis%20scale%20for%20all%20charts%20in%20the%20sheet%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1432349%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20hynguyen%26nbsp%3B%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20workaround.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20wondering%20if%20this%20will%20work%20for%20charts%20refreshed%20by%20slicers%3F%20Please%20see%20attached%20image.%20I%20selected%20a%20few%20depts%20from%20the%20slicer%20but%20the%20scale%20of%20the%203%20charts%20are%20not%20aligned.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22example.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F195849i1CC50F621BDEA5FF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22example.png%22%20alt%3D%22example.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1432385%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20align%20the%20yaxis%20scale%20for%20all%20charts%20in%20the%20sheet%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1432385%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F684091%22%20target%3D%22_blank%22%3E%40cswshaun%3C%2FA%3E%26nbsp%3BIf%20you%20also%20select%20the%20first%20%22empty%20space%22%20dept%2C%20it%20would%20work%20because%20it%20helps%20scale%20all%20charts%20to%20the%20same%20upper%20bound.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22PivotCharts2.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F195855i7836528F8F5545F8%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22PivotCharts2.png%22%20alt%3D%22PivotCharts2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi all

Please see my sample workbook.

I have a few pivot charts in a worksheet.

Currently, it is hard to compare the charts as their Y-AXIS scale is different.

I would like to align the scale of all charts in the worksheet based on the maximum scale in all charts.

For example, when all departments are selected, the maximum scale is 600 (chart 1), hence the scale for chart 2 and 3 to be set as 600 too.

For example, if dept AZ is selected in slicer, the maximum scale of the 3 charts is 3.5 (chart 1), hence the scale for chart 2 and 3 to be set as 3.5 too.

Appreciate the advice.

Thanks!

3 Replies

@cswshaun Please see the attached sample solution.

In your Data Tab, I will add 4 more data rows that caps the value for Dept As and Dept Bs (Admin, Manual) to max of available values above (I round it up but you can do whatever you want as long as those values are the same and are the highest value in Value column.

Note that in column A, for the 4 new data rows, you must type in a space. This help to scale all charts' upper bound to the same value but not show the label for those series on the horizontal axis. If you leave column A for those 4 new data rows as empty instead of a space, their labels will be shown on horizontal axis as (blank).

Then you go to each of your charts, select only 2 series point for those new data rows on each chart (they are the last 2 highest vertical line at the right-most of your chart) - carefully to select only the series points alone, not the whole series. Then you right click on each vertical line, select Format Data Point/Fill (the icon of Paint Can)/Select No Fill, No Border to make them invisible. Finally select each data labels for each new data row on top of those vertical lines, delete them.

I also remove the Grand Total (off for both Rows and Columns) in each of your pivot tables because you will not want to include Grand Total in the charts which is too large as compared to individual values and can skew your charts significantly.

Hi hynguyen ,

 

Thanks for the workaround.

 

Just wondering if this will work for charts refreshed by slicers? Please see attached image. I selected a few depts from the slicer but the scale of the 3 charts are not aligned.

example.png

Thank you!

@cswshaun If you also select the first "empty space" dept, it would work because it helps scale all charts to the same upper bound.

PivotCharts2.png