Search code examples
python-3.xxlsxwriter

Hiding #N/A or empty cells from an excel chart produced by xlsxwriter

Essentially I am generating a number of charts with xlsxwriter and trying to ensure gaps appear in the chart where I have #N/A's. Unfortunately it is not possible unless the show #N/A as empty cell setting is selected (I dont believe it is a supported feature as it is relatively new for excel).

enter image description here

I am currently using the following code to produce the above:


from xlsxwriter.workbook import Workbook

workbook  = Workbook('chart.xlsx')
worksheet = workbook.add_worksheet()

bold = workbook.add_format({'bold': True})

headings = ['Number', 'Value']

line_chart = workbook.add_chart({'type': 'line'})

line_chart.add_series({
    'categories': '={1,2,3,4,5,6}',
    'values':     '={2,#N/A,2,3,4,#N/A}'
})

worksheet.insert_chart('F2', line_chart)

workbook.close()

I understand that if I changed it to the following, the problem would be fixed, however my aim is to use the above approach. If its not possible thats fine.


from xlsxwriter.workbook import Workbook

workbook  = Workbook('chart.xlsx')
worksheet = workbook.add_worksheet()

bold = workbook.add_format({'bold': True})

headings = ['Number', 'Value']
data = [
    [1, 2, 3, 4, 5, 6],
    [2,'', 2, 3, 4,'=NA()'],
]

worksheet.write_row('A1', headings, bold)
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])

line_chart = workbook.add_chart({'type': 'line'})

line_chart.add_series({
    'categories': '=Sheet1!$A$2:$A$7',
    'values':     '=Sheet1!$B$2:$B$7',
})

worksheet.insert_chart('F2', line_chart)

workbook.close()
Solution

  • Unfortunately it is not possible unless the show #N/A as empty cell setting is selected (I dont believe it is a supported feature as it is relatively new for excel).

    That is correct. It looks like that feature was added recently in Excel 16 (the XML element is <c16r3:dataDisplayOptions16>). So it isn't supported in XlsxWriter.

    Note, it is possible to set the first 3 of those options using chart.show_blanks_as():

    chart.show_blanks_as('span')

    The available options are:

    • gap: Blank data is shown as a gap. The default.
    • zero: Blank data is displayed as zero.
    • span: Blank data is connected with a line.

    Update: 2023-09-18 - Added support for enabling the Excel "Show #N/A as an empty cell" chart option via the chart.show_na_as_empty_cell() method in XlsxWriter v3.1.4.