Lesson 28: The Ribbons since Excel 2007
For a lot of my visitors (long time users of Excel) the first reaction to Excel 2007 is "Where are all these commands that I use daily. I hate these ribbons.". We feel that all of a sudden Microsoft has moved the steering wheel of our car in front of the passenger seat.
Such is not the case. Read the three sections below and you will see that you can now put the steering wheel wherever you want and that you can rearrange the dashboard of your car to suit your personal needs very easily. You do it one time and you are back on the road. Better yet you can add all kinds of commands (more than 1,000) to make them readily accessible whenever you want them.
But because you don't want to have an overloaded dashboard (Excel Quick Access Toolbar) the fourth section is to help you find in the ribbons these commands that you love but that don't use often enough for them to be in your personalize tool bar.
Once you are through this first shock and you understand that this new set up is a major improvement.
I had great difficulties letting my 1967 Volkswagen go until I discovered everything I could do with my 1987 4WD Subaru. So instead of keeping my dear beetle on my front yard for old days sake I brought it gently to the scrap yard. My wife was more then happy and the night was beautiful.
The Office Button
When you click on the office button a window shows you the recent files that you have opened. There are a few basic commands (that you can add or remove from your OWN Quick Access Toolbar and the very important "Excel Options" button.
When you click on the "Excel Options" button you will find an extensive menu to change the calculation execution, some display features and all the options that you could change in prior versions of Excel.
The Excel Ribbons
The first choice that you have concerning the ribbons is to see or not to see them. You might want to hide the ribbon because without the ribbon you see more of the grid. To do so go to the small arrow on the right of the Quick Access Toolbar and select "Minimize the Ribbon".
From then on you see the ribbons only when you click on the different menu items. It is your choice.
The Excel Quick Access Toolbar
What you need more often is right before you eyes. No more clicking on menu items and sub-menu items, it is right there where YOU have set them. This is the Excel Quick Access Toolbar.
There are more than 1,000 commands that you can install on the Excel Quick Access Toolbar. To do so go to the small arrow on the right of the Quick Access Toolbar (see image above) and select "More Commands". The following window appears:
In the text box (top left) you can choose to see "Popular Command" or "All Commands". Then in the left list you choose a command and you click on the "Add" button. You can also insert separator by selecting this item in the left list and Click on the "Add button". Once you commands have been selected you can move to the right list and reorganize your Excel Quick Access Toolbar with the arrows on the right. Select an item and bring it up or down. You can also select en item in the right list and click on the "Remove" button in the center. Once you have built YOUR Excel Quick Access Toolbar just click on OK. Use the reset button to remove everything and bring back the simple Excel Quick Access Toolbar offered at the beginning.
Below is MY Excel Quick Access Toolbar. Notice that my Excel Quick Access Toolbar is shown below the ribbon a choice that you can make when selecting the commands for the Excel Quick Access Toolbar as shown in the image above.
As I work a lot with the "datasheet approach" the first three commands are the autofilter and both sorting command. There is also the "Clear Contents" command to empty one or many cells without destroying the formulas that refer to it. Next are the indispensable Undo/Redo commands and the Spelling command. Next a set of three commands to enter automatically the day's date, the very important "Data Validation" command to create drop-down-lists in Excel and the command to insert text boxes that I use frequently in questionnaires, headers for invoice and as buttons to trigger the macros in my workbooks. The print button and then the "Protect/Unprotect Sheet" command, the "Paste Special" command and the paint brush on which you can double click and paste a format to many other cells all over the sheet. You go back to it to deactivate the formatting procedure. Next is the extraordinary "Camera" that allows me to assemble small pictures of many dynamic tables of different shapes on a single sheet to create comprehensive reports. Next is the automatic chart creator. As I work a lot with external data next is the "Import External Data" command and the "Refresh Data" command. Finally the button to access the Visual Basic Editor (macros) and the macro recorder.
The Shortcut Menus
Those of you who have developed the "Right Click" reflex will see that the shortcut menus have been improved. Select a cell, right click, select a row right click, select anything and right click...there are discoveries to be made.
Right clicking on a cell offers you these two windows. In the bottom one you now find the very useful "Paste Special" functionality and the filter and sort menus. It is unfortunate that a "Clear" function doesn't appear in the "Filter" and "Sort" menus.
The new top window takes care of everything related to the cell. A great idea.
The "Right Click" reflex is a great reflex to develop in any of Microsoft's products but within an Excel sheets it is even more important.
All the Excel Commands
In the table below are the commands that I use regularly but that I don't want to add to my already crowded Excel Quick Access Toolbar. Here they are and the ribbon in which they hide.
Other Useful Things in Excel 2007
Look at the bottom right corner of Excel and discover the Zoom, This new functionality allows you to zoom in or out to work in Excel. No more need to go to the menu bar.
As for the new ribbons the "It was better before" will fade away rapidly. Use this change of habits to discover some of the powerful functionalities that have been there for a while and that you have never discovered yet. Try them and also try the new functionalities and be amazed. There is nothing like dropping an old habit . It is often the path to renewal.
PS: In my 1967 VW Beetle there was no real heating system. Living in the north I was using a candle in the ashtray to help defrost my windshield. My wife and I miss this romantic set-up. Nostalgia is very funny thing.
There are five sections in this website
Section 1: About Databases: 4 Lessons
Database 101 Excel and External Data Analyzing Data with Excel Reporting with Excel Other Functionalities