Forum Discussion
Format Data Labels - Value from Cells
I have a spreadsheet (below) that I wish to show in two different ways:
- The actual numbers in each of the cells Domestic, Overseas, EU, and Non-EU as shown.
- The percentage values as shown in the %age domestic, %age overseas, etc..
Using the Format Data Labels and selecting Value From Cells, I can do this for any 2 of the 4 columns.
However, when I try to select Value From Cells from the third and/or fourth column, nothing appears in the bar chart - it's completely blank (apart from the background colour.)
I have uploaded the failing sheet, which can be downloaded by https://c3a-cyprus.org/test-work.xlsx.
I'd appreciate any thoughts
TIA
Nigel
Hi Nigel
No idea where this comes from but found it strange:
- Removed Data Label on all series
- Added them again
- Formatted each Data Label > Values From Cells > Selected the full range (rows 5-22):
I case this would matter… running 365 v2601- b19628.20150 / Windows
(bordered cell B18 where 2021 is stored as Text - something you'll probably want to fix…)
3 Replies
Intrigued by the [CELLRANGE] on your chart series I searched the Net & found 3 posts:
Suggestion to make your life easier & to display the correct Years above your data & chart title => Format your data as a Structured Table
Few benefits amongst others:
- Using structured references make formula more comprehensive
- Formulas, cells formatting, conditional formats, data validation… auto. apply to new rows you add to the Table
- If the Table Total Row is activated and contains formulas using structured ref., new rows are picked up auto.
Years on Data & Chart
- In attached sample B3 & B4 take the MIN & MAX of the [Year] column to dynamically adjust the Data & Chart titles (row 4 should be hidden)
- To ignore <blank/empty> Year(s) on the Chart: Right-click on the Vertical axis > Format Axis > Axis Options > Axis Type = Date axis
Hope this makes sense & helps
Lz.Hi Nigel
No idea where this comes from but found it strange:
- Removed Data Label on all series
- Added them again
- Formatted each Data Label > Values From Cells > Selected the full range (rows 5-22):
I case this would matter… running 365 v2601- b19628.20150 / Windows
(bordered cell B18 where 2021 is stored as Text - something you'll probably want to fix…)