ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

How to adjust to Excel 2007 from previous versions as well as step by step guides to many functions in Excel 2007

Updated on January 12, 2013


Welcome to my hub containing tips and tricks on using Excel 2007. This hub is the result of my journey from Excel 2003 and its menus to Excel 2007 and the advent of the ribbon. Each time I face a problem in Excel 2007 and solve it, I document it so I can remember the steps I took. I have collected these together into this set which I hope that you will find useful.

Excel 2007 Splash Screen, welcoming you to new experiences with Excel 2007.
Excel 2007 Splash Screen, welcoming you to new experiences with Excel 2007. | Source

Advantages of the Ribbon over the menu

Quite aside from any advances from 2003 to 2007, I believe that the ribbon is better than the menu for the following reasons

  • More options are immediately visible to you than before. Sure, you could customise toolbars to add common tasks, but this is now the default. This allows you to see and therefore potentially use far more options than you may have done before.
  • Once you “get” the logic of what is where on the tabs, buttons are much easier to find than the menus (this is personal choice of course and I know that there was a huge groundswell of negativity towards the ribbon).

How I adjusted to Excel 2007

I was not given a choice to update to Excel 2007. My company at the time upgraded everyone and I just had to learn how to use it. I must admit, I found the first weeks and even months very difficult and got very annoyed on more than one occasion.

The saviour for me was the Quick Access Toolbar. I put everything I use frequently on that toolbar and was able to use Excel and Word 2007 without constantly hunting through the menus.

TIP - to add buttons to the Quick Access toolbar, right click on the button and select Add to Quick Access Toolbar.

Once I was more familiar with the tabs, my use of the Quick Access toolbar dropped but it was invaluable in the beginning. You can see the Quick Access bar in the picture pointed out by the red arrow.

Locating the Quick Access Toolbar in Excel 2007.
Locating the Quick Access Toolbar in Excel 2007. | Source

Stopping Excel 2007 reporting errors on formulas that omit adjacent cells

One of my main gripes with Excel 2007 is that often I create a formula (for example to sum a row of data) and Excel will then dutifully mark the formula as having an error (as I have illustrated in the figure below). So all the formulas I have created are marked as having an error despite doing exactly what I want and containing no “real” errors.

Excel 2007 displaying errors on formulas that omit adjacent cells.
Excel 2007 displaying errors on formulas that omit adjacent cells. | Source

To turn this option off:

  • Click on the Excel button
  • Select Excel Options
  • Clickon Formulas
  • Deselect Formulas which omit cells in a region in the Error checking rules section
  • Click OK to return to Excel

Turning errors on formulas that omit adjacent cells off in Excel 2007.
Turning errors on formulas that omit adjacent cells off in Excel 2007. | Source

Now, when you create a formula and it has an actual error, you will get the little green triangle notification rather than receiving one for almost all formulas you create!

Step by Step Guides for Excel 2007

Throughout my journey with Excel 2007, I have come across many challenges. Some were solved by me adjusting to Excel 2007, others were solved by coming up with unique solutions to posed problems or queries.

Thermometer chart created in Excel 2007.
Thermometer chart created in Excel 2007. | Source

Creating charts and graphs in Excel 2007

The first was the change from the Chart Wizard in Excel 2003 to the new way of doing charts in Excel. Excel 2007 use styles and layout buttons once you have chosen your graph type. My first step by step guide is on creating graphs in Excel 2007 and can be found here:

One of my most enjoyable hubs was the thermometer chart or graph. I came across the thermometer chart by accident and decided that I would create the most beautiful chart that I could (please see the figure to the right and decide for yourself if that is not a beautiful thermometer chart). My hub on that topic can be found here:

Using Pivot Tables and Pivot Charts in Excel 2007.
Using Pivot Tables and Pivot Charts in Excel 2007. | Source

Creating Pivot tables and Pivot Charts in Excel 2007

Pivot tables are immensely useful in Excel 2007 and allow you to create easily customised tables from data. These tables can be very easily changed to show different aspects or analysis of your data simply and quickly. I created a step by step guide to creating and configuring pivot tables which can be accessed here:

Going hand in hand with the pivot table is the pivot chart, which I have also written a hub about that can be found here:

Using Filters in Excel 2007.
Using Filters in Excel 2007. | Source
Using Groups in Excel 2007.
Using Groups in Excel 2007. | Source

Grouping and filtering data in Excel 2007

Often, you will receive a spreadsheet that just has too much data. Grouping and filtering allow you to display only the data you wish to see. Filtering allows data to be sorted so that with the use of drop down boxes, you can decide which data is displayed. Grouping allows you to group similar data together. The groups can then be collapsed to hide details within the groups (subgroups can also be created). Grouping is very useful for data such as inventories as items can be grouped together logically. My hub introducing groups and filters is located here:

Subtotals in Excel 2007.
Subtotals in Excel 2007. | Source

Using Subtotals in Excel 2007

The Subtotal button inExcel 2007 allows you to group data as with Grouping above, but with the addition of subtotals. Where those subtotals sit in your data and which function you use (Sum, Average and Count) are completely configurable.You can also add additional subtotals should you wish.

Example of Conditional Formatting in Excel 2007.
Example of Conditional Formatting in Excel 2007. | Source

Conditional formatting in Excel 2007

Conditional formatting allows you to format data based on formulas or logical expressions such as true / false to visually display the results.

For example, imagine you are managing a team who are expected to phone 100 customers a day. You have your data and you want to see visually how your team are doing. If you use conditional formatting, you can format cells so that on days they do well it turns the cell green, not so well orange and poorly red. For more information on conditional formatting, click the following link

Check Boxes, Combo Boxes and Command Buttons in Excel 2007.
Check Boxes, Combo Boxes and Command Buttons in Excel 2007. | Source

Combo boxes, checkboxes and command button in Excel 2007

Excel 2007 allows users to interact with your spreadsheets in a number of different ways. Some of the more interesting ways are through visual aids. These include:

  • Checkboxes which allow users to select or deselect an item or a list of items.
  • Combo boxes which consist of a drop down box (or boxes) that allow users to select one item from a list
  • Command buttons invite users to select a button which performs a particular function.

How to access the Protect Workbook and Share Workbook buttons in Excel 2007.
How to access the Protect Workbook and Share Workbook buttons in Excel 2007. | Source

Protecting and sharing a document in Excel 2007

The ability to protect data from being edited is important if you want to protect certain data from being changed. You can fully protect it from any changes or allow people to make certain changes. Spreadsheets can also be shared to allow people to work collaboratively on a single spreadsheet. Configuring protection and sharing is discussed in this hub:

Example of the COUNTIF function in Excel 2007.
Example of the COUNTIF function in Excel 2007. | Source

 Using COUNTIF, COUNTIFS formulas and Remove Duplicates in Excel 2007

I was asked by a colleague if Excel can easily determine how many times something occurs in a range of Excel data. The answer is, yes using the COUNTIF and COUNTIFS formulas.

  • Remove Duplicates is also useful for simply creating a list of unique items in a list.
  • COUNTIF is used if you are counting data with one characteristic for example pens, pencils etc.
  • COUNTIFS is used if you are looking at items with more than one characteristic, for example blue, red and green pens.

A guide to using these formulas and also the Remove Duplicates button can be found here:


I hope that you have found an answer to a question you may have had in one of my hubs listed here, or that you have found them useful. I also hope you enjoyed reading them as much as I have enjoyed writing them. If you have any questions or comments or would like help in solving an Excel 2007 conundrum, please feel free to leave a comment or question below.


This website uses cookies

As a user in the EEA, your approval is needed on a few things. To provide a better website experience, uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at:

Show Details
HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
LoginThis is necessary to sign in to the HubPages Service.
Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
AkismetThis is used to detect comment spam. (Privacy Policy)
HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the or domains, for performance and efficiency reasons. (Privacy Policy)
Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
MavenThis supports the Maven widget and search functionality. (Privacy Policy)
Google AdSenseThis is an ad network. (Privacy Policy)
Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
Index ExchangeThis is an ad network. (Privacy Policy)
SovrnThis is an ad network. (Privacy Policy)
Facebook AdsThis is an ad network. (Privacy Policy)
Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
AppNexusThis is an ad network. (Privacy Policy)
OpenxThis is an ad network. (Privacy Policy)
Rubicon ProjectThis is an ad network. (Privacy Policy)
TripleLiftThis is an ad network. (Privacy Policy)
Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)
ClickscoThis is a data management platform studying reader behavior (Privacy Policy)