• Practice
  • About 17 min
  • 5 practice questions

Charts, printing and fixing error values: troubleshooting tasks

Choose and build the right chart, set up a sheet to print cleanly, and diagnose and fix #DIV/0!, #NAME?, #VALUE!, #REF!, ##### and circular references.

If something looks wrong, please tell us.

The last practical skills are showing results and fixing problems. Test questions often show a broken formula or a poorly chosen chart and ask what went wrong. The cause is almost always one of a few patterns, the same in Calc and Excel.

Error values: cause and fix

ShowsCauseTypical fix
#DIV/0!A divisor is 0 or an empty cell=IF(C2=0,"-",B2/C2), or fill in the missing value
#NAME?Misspelt function (SUMM), text without quotes (Pass instead of "Pass"), or a range missing its colonCorrect the spelling; put text in double quotes
#VALUE!Text where a number is needed, e.g. "12 kg" in an additionKeep units in the heading and only numbers in cells
#REF!A cell the formula used was deletedUndo, or rewrite the formula with valid cells
#N/AA lookup could not find its valueCheck the value being looked up
#####Column too narrow (or, in Excel, a negative date or time)Widen the column

A circular reference is a formula that depends on its own cell, such as =SUM(C1:C5) typed in C5. Excel warns about it; Calc shows Err:522. Move the total outside the range it adds: put =SUM(C1:C4) in C5.

Worked example 1: diagnosing three cells

  1. A2 = 300, B2 = 0, C2 = A2/B2 shows #DIV/0!. Replace with =IF(B2=0,"N/A",A2/B2): it shows N/A now, and 75 once B2 is changed to 4 (300 ÷ 4 = 75).
  2. D2 holds =IF(A2>=40,Pass,Fail). Pass and Fail without quotes look like names, so it shows #NAME?. Fix: =IF(A2>=40,"Pass","Fail").
  3. E2 holds 12, F2 holds the text 15 kg, and G2 = E2+F2 shows #VALUE!. Type 15 in F2 and put 'kg' in the column heading; G2 then shows 27.

Choosing and building a chart

  • Comparing branches, subjects or products: column chart; long names or many items: bar chart.
  • A change over days, months or years: line chart (time on the horizontal axis).
  • Shares of one total, a few parts, all positive: pie chart. A pie with twelve thin slices, or with values that are not parts of one total, misleads.
  • Select the labels and the numbers before inserting, so the categories appear on the axis and in the legend.
  • Excel only: F11 makes a chart of the selected data on a new chart sheet; Alt+F1 places it on the current sheet. In Calc, use Insert > Chart, which opens the Chart Wizard.
  • Charts are linked to their data: correct a number and the chart changes.

Good practice: give every chart a title that says what it shows, label axes with units (₹, kg), and be careful with a value axis that does not start at zero - it can make a small difference look huge.

Worked example 2: preparing a wide sheet for printing

  1. Open the preview (Excel: File > Print; Calc: File > Print Preview). A twelve-month table spills onto a second page.
  2. Switch to landscape and scale to fit all columns on one page (Excel: Fit All Columns on One Page; Calc: Format > Page Style > Sheet > Scale).
  3. Set rows 1-2 to repeat at the top of every page (Excel: Page Layout > Print Titles; Calc: Format > Print Ranges > Edit).
  4. Add a footer with the page number, and set the print area if the notes at the side should not print.
  5. To print only some cells once, select them and choose to print the selection in the print dialog.

#DIV/0! divide by 0 or blank · #NAME? unknown name or missing quotes · #VALUE! wrong data type · #REF! deleted cell · ##### narrow column. Excel: F11 = chart sheet.

Key takeaways: read the error value as a diagnosis, keep only numbers in number cells, guard divisions with IF, and keep totals out of the range they add. Pick the chart from the question being asked, and preview, orient, scale and repeat headings before printing.

Check your understanding

  1. 1. D2 holds =IF(A2>=40,Pass,Fail). What does it show?

    1. Pass or Fail, depending on A2
    2. #NAME?
    3. #VALUE!
    4. #REF!
    Show answer

    Answer: B. Text results must be in double quotes. Without them, Pass and Fail are read as names that do not exist, so the cell shows #NAME?. The fix is =IF(A2>=40,"Pass","Fail").

  2. 2. C2 holds =B2*2. You delete the whole column B. What does the formula now show?

    1. #NAME?
    2. 0
    3. #REF!
    4. #DIV/0!
    Show answer

    Answer: C. The cell the formula referred to no longer exists, so the reference is broken and the result is #REF!.

  3. 3. In Microsoft Excel, which single key creates a chart of the selected data on a new chart sheet?

    1. F11
    2. F5
    3. F4
    4. F2
    Show answer

    Answer: A. F11 creates a chart sheet in Excel (Alt+F1 puts a chart on the current sheet). F4 cycles references, F2 edits a cell and F5 opens Go To.

  4. 4. A2 holds 300 and B2 holds 0. What does =IF(B2=0,"N/A",A2/B2) show?

    1. #DIV/0!
    2. 0
    3. 300
    4. N/A
    Show answer

    Answer: D. The test B2=0 is true, so IF returns the value-if-true, N/A, and the division is never done.

  5. 5. E2 holds 12 and F2 holds the text 15 kg. What does =E2+F2 show?

    1. #VALUE!
    2. 27
    3. 12
    4. #NAME?
    Show answer

    Answer: A. 15 kg is text, which cannot be added to a number, so the result is #VALUE!. With 15 alone in F2 the sum would be 27.

Privacy · Terms · Refunds · Delivery