The Ribbons in Excel 2007

Excel reporting and databases                              Excel Examples

 

Excel tips on functions and formulas

 

Excel Reports Sitemap

 

Excel Tutorial

 

 

 

 

Excel database 101

 

Excel external data

 

Excel data analysis

 

Excel Reporting

 

Tips and help ion Excel

 

 

 

Excel Functions and Formulas

 

Excel Macros and VBA

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.

The Ribbons The Commands
Home Format Cell, Conditional Formatting, AutoSum, Find and Replace, Filter, Sort....
Insert Pivot Tables, Charts, Text Boxes, WordArt, Header/Footers.....
Page Layout Page Setup for Printing, Gridlines, Heading...
Formulas Help on Functions, Named Field Management, Auditing, Calculation Options...
Data External Data, Filtering, Sorting....
Review Spelling, Protect/Unprotect Sheet and Workbooks, Comments
View New Window, Arrange Windows, Freeze Panes
Developer (only available if you have installed VBA) All Visual Basic functionalities, Macro Recorder....

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.

In Conclusion

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
In this section we review the basic notions on databases and evaluate Excel as a database. You will then see how Excel can be used downstream from most centralized databases and data management programs. Finally we will discuss the "upgrade" from Excel to Access..
Section 2: Excel and External Data: 7 Lessons
There are many ways to get data into your Excel Workbooks and there are many ways to easily extract data from all the databases in your organization and the Internet. This section is centered on querying data
Section
3:
Analyzing Data with Excel: 8 Lessons
Once you have the data in the right formats within your Excel workbook it is time to use the powerful analytical tools of Excel including the database functionalities (sort, filter, subtotals), the integrated calculator, the comparative analysis tools and the mysterious and not so complicated pivot tables. 
Section 4: Reporting with Excel: 7 Lessons
The report is a communication tool and you can not accept to compromise on its presentation. Excel is the best reporting tool allowing you to easily develop popular dashboards and to share these reports with multiple colleagues.
Section 5: Other Functionalities in Excel: 3 Lessons
In this fifth section you will review the useful CTRL and ALT keystrokes, the conditional formatting, the drop-down lists and other interesting functionalities.

 

 

   

 

Excel fonctions et formules

Database 101   Excel and External Data   Analyzing Data with Excel   Reporting with Excel   Other Functionalities
Email   
Tutorials