Formatting window/menu missing in Excel 2013

The window / menu / toolbar (not sure exactly what to call it) that pops up on the right of screen in Excel 2013 when you want to change settings for a chart is missing. Currently I can't change the axis min / max values, change data series chart types or anything at all that requires that side menu/window/toolbar.

Likely related to this, I had a whole lot of strange things going on recently. For example right click menu disappeared altogether. I managed to get this back by reenabling via VBA. I'm wondering if some addin I've used has crashed leaving several features off/disabled. However, I couldn't find any examples of how to force enable the formatting window / menu / toolbar but that is possibly simply as I don't know what to properly call it!

My hunch is that the right vba code will allow me to reanable the formatting window.

Any ideas?
Who is Participating?
Michael FowlerConnect With a Mentor Solutions ConsultantCommented:
The following script will display all of the task panes which appear to the right

Sub test()
    Dim i As Long
    Dim cmd As CommandBar
    i = 2
    Range("A1").Value = "Name"
    Range("B1").Value = "Index"
    Range("C1").Value = "Enabled"
    For Each cmd In Application.CommandBars
        If cmd.Position = msoBarRight Then
            Range("A" & i).Value = cmd.Name
            Range("B" & i).Value = cmd.Index
            Range("C" & i).Value = cmd.Enabled
            i = i + 1
        End If
End Sub

Open in new window

On my my machine the following listing appears

Name	Index	Enabled
PivotTable Field List	10	FALSE
Task Pane	127	FALSE
XML Source	131	FALSE
Research	132	FALSE
XML Document	133	FALSE
Signatures	134	FALSE
Document Actions	135	FALSE
Clip Art	136	FALSE
Selection	137	FALSE
Format Object	138	TRUE
Document Management	139	FALSE
Document Updates	140	FALSE
Mail Merge Panes	141	FALSE
Fax Service	142	FALSE
Meeting Workspace	143	FALSE
Attachment Options	144	FALSE
Accessibility Checker	145	FALSE
Proofing	146	FALSE
Dictionaries	147	TRUE
Thesaurus	148	TRUE

Open in new window

If any of these are incorrectly disabled then use

Sub EnableCmdBar(idx As Long)
   Application.CommandBars(idx).Enabled = True
End Sub

Open in new window

where idx is the index of the offending commandbar
Michael FowlerSolutions ConsultantCommented:
Try this to restore all toolbars

   Sub ShowAllToolbars()
       'loop variable
       Dim i As Integer

       ' Loop through the total number of toolbars.
       For i = 1 To Application.Toolbars.Count

           ' Show each toolbar.
           Application.Toolbars(i).Visible = True

       ' End of loop.
       Next i

   End Sub

Open in new window
JDidoAuthor Commented:
No, unfortunately that didn't do it.

The right click menu came back after I messed with application.commandbars.
Is there a commandbars that refers to the formatting menu/window/toolbar?
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Michael FowlerSolutions ConsultantCommented:
OK ensure that the menu is selected. Go to Tools > Customize, then click the Toolbar tab. Make sure the option for 'Chart' is selected.

Don't forget that the 'Chart' menu item only appears in chart mode (by starting a chart or activating any chart command).
JDidoAuthor Commented:
This is Excel 2013 so I don't think there is a Tools menu.

I took a screenshot of what I'm talking about on another PC.

The menu/toolbar is this one that normally appears on the right of screen whenever you right click and go format... for example FORMAT PICTURE... or FORMAT AXIS... or any number of other options. For some reason this will not appear on this particular PC.

Different PC, same workbook = no problem.
I have repaired and restarted several times to no avail.

Katie PierceCommented:
Under File> Options>Customize the Ribbon, if you select the drop down for Customize the Ribbon on the right, are all the check boxes checked below it? Specifically, you should see Design and Format checked under the subheading Chart Tools.
JDidoAuthor Commented:
Hi Katie,

Yes they are all checked.
I tried turning them off and closing Excel down then starting again and reenabling them: This didn't solve the problem either.

Is there a way this could have been disabled by VBA? (And can be reenabled?)

Katie PierceCommented:
Unfortunately I have limited knowledge of VBA, so I'm not much help there.

Last ditch effort, though: When the menu is active on the right, you can hover over the edge and get a sizing arrow that will allow you to make the formatting menu narrower and narrower.  Is there any chance that when you try to click format that it does come up, but is very narrow?  Maybe if you hover the cursor there you can see the arrow and expand the menu?
Michael FowlerSolutions ConsultantCommented:
Sorry about that.

Have you tried opening the task pane using some of the other methods


CTRL + 1
On a chart, select an element. On the Ribbon, select the Chart Tools Format tab, then click Format Selection.
On a chart, select an element. Right-click, then select Format <element> where <element> is the axis, series, legend, title, or area that was selected.
JDidoAuthor Commented:

This fixed it!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.