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

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.

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).

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.

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.

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

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!

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.

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:

http://robbiecwilson.hubpages.com/hub/Creating-charts-and-graphs-in-Excel-2007

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:

http://robbiecwilson.hubpages.com/hub/Creating-a-thermometer-graph-or-chart-template-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:

http://robbiecwilson.hubpages.com/hub/Creating-Pivot-Tables-in-Excel-2007

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:

http://robbiecwilson.hubpages.com/hub/Creating-Configuring-and-Using-Pivot-Charts-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:

http://robbiecwilson.hubpages.com/hub/Filtering-and-Grouping-data-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.

http://robbiecwilson.hubpages.com/hub/Using-the-Subtotal-button-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

http://robbiecwilson.hubpages.com/hub/Conditional-Formatting-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.

http://robbiecwilson.hubpages.com/hub/Configuring-and-Using-Combo-boxes-in-Excel-2007

http://robbiecwilson.hubpages.com/hub/Configuring-and-Using-Check-boxes-in-Excel-2007

http://robbiecwilson.hubpages.com/hub/Create-a-Command-Button-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:

http://robbiecwilson.hubpages.com/hub/Sharing-and-protecting-documents-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:

http://robbiecwilson.hubpages.com/hub/Using-the-COUNTIF-and-COUNTIFS-formulas-and-the-Removing-Duplicates-button-in-Excel-2007

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.