7

I have Excel 2016 and I cannot use the statistics charts because that, along with two other options are missing in the "Insert" tab.

This is what a normal Excel chart options area looks like:

Excel 2016

However, mine looks like this:

enter image description here

How do I get the other options so I can add a histogram?

4
  • It might be that some options are hidden because of limited space. Try dragging the vertical line (to the right of Pivot Chart) to the right, and maybe the missing options will appear. Commented Jan 6, 2018 at 16:44
  • The line cannot be dragged. Maximizing the window doesn't help either. Commented Jan 6, 2018 at 16:46
  • Can you find Histograms under Recommended Charts>All Charts? This will tell you if you're missing the charts entirely, or just a ribbon shortcut to them.
    – dav
    Commented Jan 8, 2018 at 13:05
  • For some reason, you're missing all of the new chart types. Treemap, Sunburst, Histogram, Pareto, Boxplot, Waterfall. What specific version of Excel? Under the red Office logo in File > Account, it says Microsoft Office [Something Something] 2016: what's in brackets? Also, below that it says version something and build something: what are these numbers? And what version of Windows? Commented Jan 13, 2018 at 3:24

8 Answers 8

3

You need to get rid of compatibility view. Save as the file and reopen the new file. Should work now.

1
  • If you simply Save or even Save As the file, Office will automatically save it as Office ’97 compatible. Please include more detailed instructions on how to save the file as the correct type and reopen it to escape compatibility mode.
    – binki
    Commented May 15, 2019 at 15:13
2

In Excel go to File / Options / Add-Ins

Select Analysis ToolPak (it should be in the "Inacticve Application Add-ins")

Click Go at the bottom.

Check the box at Analysis ToolPak and click OK

Close and Re-open Excel again and you should have the histogram option.

This is working for me in Excel 365.

1
  • The Analysis ToolPak does not contain the new chart types. It does have a histogram, but it's an implementation of the existing column chart. Commented Jan 24, 2023 at 16:34
2

Make sure you haven't opened a sheet in compatibility view. If you have, go to the file menu and click convert. This will give you back these charts on this sheet

1

Select "File", "Save As" and save the file as Excel Workbook (.xlsx) or some other recent versioning. If the workbook is saved as an older version of Excel, i.e. Excel 97-2003 (.xls), the Analysis Toolpak add-in will not work.

1

I solved the same problem just now: The file was saved as .xls and not .xlsx, so excel 2016 features did not apply for that file and automatically these features are disabled. BTW, in my case it was noted as "Compitability mode" at the top of the screen. So: 1. Save the file as .xlsx 2. Close it. 3. Open the .xlsx file

And voila statistical charts are there again.

1
  • 2
    Welcome to Super User, and kudos for solving your problem. However, EByrd posted this solution some months ago. The intention is that each answer should provide a solution that hasn't already been contributed.
    – fixer1234
    Commented Dec 13, 2018 at 10:34
0

Please check if you add more commands to the Insert tab, try to remove them.

Then try to reset Ribbon option in Excel, please go to this path:

C:\Users\User Name\AppData\Roaming\Microsoft\Excel

Please rename the file Excel16.xlb to Excel16.xlb.old then reopen Excel and check the result.

2
  • Excel15.xlb is probably irrelevant, since Excel 2016 is Excel16. Commented Jan 15, 2018 at 3:08
  • OH,YES~My mistake.
    – Lee
    Commented Jan 15, 2018 at 6:02
0

What happened with mine was that two sheet tabs were actually selected i.e. I somehow pressed CTRL and clicked on another Sheet, so Sheet1 and Sheet2 were selected. I continued working, but the selected sheets would not go away. I realised this because when I tried removing one sheet, both sheets were deleted.

Clicking on either Sheets wouldn't make both of them "unselected". So, I clicked on the third Sheet (or insert Sheet3 if you don't have a third one). Then the Insert Ribbon is no more greyed.

-1
  • had the exact same problem, I changed the compatibility view and it fixed everything. If you go to the file menu and click convert this will give you back the charts you are missing.

*

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .