Formatting window/menu missing in Excel 2013

Posted on 2015-02-02
Last Modified: 2016-02-10
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?
Question by:JDido
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
LVL 23

Expert Comment

by:Michael Fowler
ID: 40585358
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

Author Comment

ID: 40585500
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?
LVL 23

Expert Comment

by:Michael Fowler
ID: 40585506
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).
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 40585698
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.


Expert Comment

by:Katie Pierce
ID: 40586688
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.

Author Comment

ID: 40587569
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?)


Expert Comment

by:Katie Pierce
ID: 40587585
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?
LVL 23

Expert Comment

by:Michael Fowler
ID: 40587593
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.
LVL 23

Accepted Solution

Michael Fowler earned 500 total points
ID: 40587626
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

Author Closing Comment

ID: 40587656

This fixed it!

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question