Customizing Microsoft Excel
Excel has many different customization options; but first, let’s explore the changes from previous versions to Excel 2007. The change to the 2007 version dramatically changed with the new menu system. In previous versions, there were menus across the top with a few toolbars; customizing Excel was as simple as going to the Tools Menu and selecting either Options or Customize. In 2007, all of the icons change as you select different menus. My initial use of Excel 2007 left me doing a lot of starring at the screen and scratching my head as to where the commands went. I think the new version is easier to use for a new user, but for someone who was used to the old way, it was a painful transition. Thankfully, I found how to customize the ribbon and that made things easier. The ribbon for anyone who is not familiar looks like a tool bar and it is located in the top left hand corner of the screen by the Microsoft Office Logo button. The ribbon is used for placing commands (or buttons) in a place that does not change as your click through different menus. Kind of like a short cut bar. Let me start by reviewing the Excel Options menu tab by tab. I am going to do a brief overview on these, touching only on what I think is useful. Click on the Microsoft Office button and click Excel Options at the bottom.
When the menu pops open, it opens to the Popular Tab which allows you to change some high level options. The first options to check or uncheck is whether or not you want a mini-formatting toolbar to pop up when you right click on data in Excel. (See Example 1) The Show Developer Tab in the Ribbon is one to check if you do anything with macros, VBA, or use form controls. The next thing that I find interesting here is the Edit Custom Lists button. I haven’t used this, but it could be useful if fill data using specific sequences (such as January, February, etc). Days of the week and months are already in there, but you can add a custom list of your own if it makes your life easier. In this menu, you can change the default font and font size, how many sheets each new workbook opens with, and what view default view you want new spreadsheets to open up in.
Now click on the Formulas tab. The top section defaults whether or not you want the formulas in Excel to calculate automatically or manually. If for some reason your formulas are not calculating, this is a good spot to check to make sure formula calculation is set to auto. Also, look under the formula menu and click on the calculation options drop down. The bottom section has default options for error checking. I normally leave this tab alone.
The AutoCorrect Options button allows you to define how Excel will auto correct. The lower section lets you tweak how spell check will work in Excel. Unless there is something really specific that you want to change, I would leave this tab alone.
This tab is worth taking a look at. Unless you do a lot of work in previous versions of Excel or need to save your files in a format others than .xlsx, I would leave the format the files are saved in alone. The big thing that I use on this tab is changing the settings of Auto Recover defaults. Unless you are working on very large files, I would make sure the auto save is set to around 10 minutes. If you are working with a large file, it may be better to set it to a half hour or so to avoid your system temporarily locking up trying to save all of the data. You can also set the default of where you want Excel to save your files.
Most of the options on this tab I would leave as they are. You can change how you want the “Enter” key to act in Excel (Left, Right, Up, Down). I prefer the “Enter” key to move down, but that is just what I am used to. The only other thing else that I found that is worth noting on here is that you can change the number of recent documents that are shown inside of the Microsoft Office Logo menu.
Customize Tab (Ribbon)
Use the drop down box under “Choose Commands From:” and select All Commands. You can choose whatever grouping that you want, but for this article it is easier to explain without having to keep jumping menus. I like to have the commands that I use often on the ribbon so I do not have to go hunting for them. With the new menu system in Excel, it is inefficient to have to constantly click the menu that you want for things that you do over and over again.
20 Common Commands to Put on Your Ribbon
1. New Document
2. Open File
4. Save As
5. Email Attachment
6. Set Print Area
7. Quick Print
8. Print Preview
9. Insert Column
10. Insert Row
11. Delete Column
12. Delete Row
14. Record a Macro
15. Unhide Columns
16. Hide Columns
17. Fill Color
18. Text Color
19. Insert Pivot Table
20. Freeze Panes
Also, below the commands box on the lower left hand side is the toggle box to toggle where you want the ribbon to be displayed.
This menu manages Add-In functions for Excel. I never touch anything in this menu. I would suggest downloading the plug-in that enables you to save documents as .pdf.
Trust Center Tab
The Trust Center Settings button brings up another window and there are two places that you may want to change depending on how you use Excel. I would look at the Macro tab and adjust the macro trust settings. I normally only work with spreadsheets that I create, so I allow all macros. If I was working with other people’s files, I would make sure that the trust level is set to block macros from untrusted sources. The other tab that you may want to change is the External Content tab. Again, I work with mostly my own spreadsheets so I tend to allow external content, but if I used other people’s spreadsheets I would set it to ask me whether to allow the content.
Everything on this tab is self-explanatory. It is a good idea to check for updates every now and then to make sure that you have the latest patches. If you are having any issues with Microsoft Office, you can run a diagnostics program to try and repair any problems.
In closing, Excel offers many different ways to customize it. Adding commands to your ribbon is one of the best ways to make you more efficient. Take the time to explore all of the customization options Excel offers and see if there are any options that will increase your efficiency.