Transferring Ribbon Customizations

by Allen Wyatt
(last updated February 24, 2018)


Ian created a macro and then customized the ribbon with a button that executes that macro. (Works great!) He transferred the workbook into Dropbox so that a colleague could use it. When she opened the workbook, the button Ian had added to the ribbon was missing. He wonders why this occurs and how he can get around it.

The answer to this question depends, in large part, on how you made the customizations to your version of Excel.

Working with the Quick Access Toolbar

If your customizations involved simply adding the macro to the Quick Access Toolbar, those changes are not stored in a particular workbook like macros are; they are stored by Excel in what is referred to as a QAT file. How you work with a QAT file depends on the version of Excel you are using. You can, if desired, simply use Windows to search for any file ending in a QAT extension. If both you and the colleague are using Excel 2007, you can simply copy the QAT from your system to the colleague's system, making sure it overwrites whatever QAT file may exist on that system.

If you are using Excel 2010 or later and your colleague is using the same version of Excel as you, then you should follow these steps:

  1. Click the File tab on the ribbon.
  2. Click Options. Excel displays the Excel Options dialog box.
  3. At the left side of the dialog box click Quick Access Toolbar. (See Figure 1.)
  4. Figure 1. The Quick Access Toolbar options of the Excel Options dialog box.

  5. Click the Import/Export drop-down list at the bottom-right corner of the dialog box. Excel displays two options.
  6. Choose Export All Customizations. Excel displays the File Save dialog box.
  7. Using the controls in the dialog box, select a location where you want the backup file saved.
  8. Click Save. Excel saves the customization file where you specified in step 6.
  9. Click on Cancel to dismiss the Excel Options dialog box.

The file created in step 7 is your export file. You can copy it to Dropbox, and the colleague can use the same steps listed above, with the caveat that he or she should import in step 5, making sure that your export file is what gets imported.

Of course, you may want to consider modifying how you actually made the changes to the Quick Access Toolbar. The latest versions of Excel allow you to specify that a Quick Access Toolbar change be saved with a particular workbook. So, for instance, you could save the change with the same workbook that contains the macro—the same workbook you are saving to Dropbox and wanting your colleague to use. To do that, follow these general steps:

  1. Make sure that the macro for your colleague is stored in a new workbook.
  2. Save the workbook as a macro-enabled workbook, as you normally would.
  3. Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 and later versions display the File tab of the ribbon and then click Options.)
  4. At the left side of the dialog box click Quick Access Toolbar.
  5. At the upper-right corner of the dialog box, use the Customize Quick Access Toolbar drop-down list to choose the workbook that contains your macro. (This should be the workbook you want to share with your colleague.)
  6. Use the controls in the dialog box to add the macro to the QAT.
  7. Close the Excel Options dialog box.
  8. Save the workbook and share it with your colleague.

The QAT customization is now only active when the workbook that contains that customization is active. (The key is the selection you make in step 5.

Working with the Ribbon

If your changes were not actually made to the Quick Access Toolbar but were, instead, made to the ribbon itself, then the above won't really help. Instead, you'll need to follow a more detailed series of steps that involve the transfer and installation of what is called an OfficeUI file. You can find a lot of in-depth info on transferring this file here:

Creating an Add-In

The problem with any of the solutions presented so far is that they have the likelihood of overwriting any customizations that Ian's colleague had already set up for his or her system. In other words, Ian's customizations wouldn't be added to those customizations; they would replace those customizations. This has the potential of making Ian's colleague quite grouchy.

The best solution may be for Ian to create an add-in that makes the macro available and, in doing so, makes changes to the user's interface. Ian would need to do two things:

  • Create an "auto open" macro that would modify the user's interface when the workbook is opened. This macro would need to be in the same workbook as the macro Ian created for the colleague.
  • Convert the workbook into an add-in.

This may seem a bit of overkill, but it isn't that difficult. (Honestly, the macro to modify the user interface might be the hardest, but that level of difficulty depends on how extensive the changes are that you want the macro to make.) Doing the conversion into an add-in isn't that difficult at all; you can find details here:

Once the add-in is done, then the colleague can install and activate the add-in, regardless of the version of Excel he or she is using.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7790) applies to Microsoft Excel 2007, 2010, 2013, and 2016.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...


Turning Off Hyperlink Activation

Does it bother you when you enter a URL and it becomes "active" as soon as you press Enter? Here's how you can turn off ...

Discover More

Spell Checking Forms

Word may be used to create protected forms that limit where the user may input data. Normally spell checking is disabled ...

Discover More

Printing Summary Information

Word automatically maintains a number of properties for each document you create. As part of those properties you can ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Turning Off Insert Options

When you insert rows, columns, or cells in a worksheet, does the resulting Insert Options icon bother you? Here's how to ...

Discover More

Tab Key Jumps a Screen at a Time

Have you ever pressed the Tab key, expecting to move to the next cell in your worksheet, only to have Excel completely ...

Discover More

Setting the AutoRecover Directory

Excel, by default, periodically writes information to AutoRecover files that can help protect your data in case Excel is ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is six more than 8?

2018-03-10 11:10:29

John Mann

@Michael (Micky) Avidan

I would agree that using Freeze Panes is a possible solutioin to my second problem - but not always.

I have worksheets where Freeze Panes would work perfectly to keep a macro button visible, but I also have other worksheet where it wouldn't be a very desirable solution. On those sheets, the cell(s) above and to the left of the freeze boundary contains important headings or logos. While I suppose it would be possible to use the logo as a button, it would not allow me the ability to put instruction text on the button. On the worksheet I on which I have placed a button to trigger a macro, I have inluded text "Click to clear data" (the macro clears data from a range of cells), and I have also included a note about the keyboard short cut which will also run the macro.

Going back the the original topic of this thread, I have extensively modified my QAT in an attemtp to partially make up for the absence of proper tool bars. The lack of proper, context sensitive toolbars is on of the reasons I sometimes us either Lotus Smart Suite (I have the last version) or Open Office, for spreadsheet or workproccing.

2018-03-04 16:21:32

Michael (Micky) Avidan

@John Mann,
Your second "problem" is not a problem at all.
Try to add 3-4 empty rows at the top of the sheet and place the commndbutton in this empty area.
Now, all you need is to Pane Freeze those 3-4 rows (and some columns) in order to have the Button available all the time.
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” Excel MVP – Excel (2009-2018)

2018-03-03 15:58:29

John Mann

The problem with putting a button on a worksheet is at least two-fold.
First, it's only on one sheet, so if your workbook has several worksheets, you need to duplicate the button on each of them
Second, when you scroll around the worksheet, the button will move around also, and will soon disappear off the screen. Adding a keyboard short cut - if remembered - will get around that problem.
I do use a button to run a macro on one of my worksheets, but it has only a few rows and columns so scrolling off screen is not an issue.

2018-02-28 07:14:48


Wouldn't it be easier to just apply the macro to a button with the worksheet, still has the same ease of access (for the most part) and doesn't require any QAT adjustments.
If the other user uses Excel a lot they likely have their own custom QAT and have memorised the shortcut keys to access it so manipulating that might not be the best idea.

2018-02-26 20:03:35


Never mind. I should have read farther into the article before posting!

2018-02-26 20:01:36


Copying a QAT sounds like it might be problematical. Wouldn't overwriting my QAT with someone else's QAT erase my existing customizations?

2018-02-25 10:01:09



The method you suggest (and prefer) is actually mentioned in the above tip. (See the paragraph beginning "Of course, you may want to consider modifying how you actually made the changes to the Quick Access Toolbar.")

I had to address other ways because it was not entirely clear from Ian's situation if his customizations were solely to the QAT or if they were to the ribbon itself, as well.


2018-02-24 08:36:46

Willy Vanhaelen

In my opinion, the simplest and safest method is to add a button to the QAT and specify to save it with the current workbook instead of "For all documents (default)". This way the buton will also additionaly show in that workbook's QAT on all other systems without changing anything else on their ribbon customasations.

"Working with the Ribbon" is also a good alernative but it is a little more complicated.

I don't see how an Add-In could solve this. As far as I know, add-ins have no special abilities of specifying what is displayed in the QAT.

(see Figure 1 below)

Figure 1. 

This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.