Join Lorna Daly for an in-depth discussion in this video What's changed in Excel 2007?, part of Excel 2007 Essential Training.
When you open Excel 2007 for the first time, the changes are right there in front of you. For those of you that have been used using Excel in the past, You'll see that the interface is completely different. No longer are you hunting and pecking through menus to find the commands you want, we now have a ribbon at the top that organizes the commands and information in logical areas. For you brand new users to Excel, you're going to find this a very easy interface to work with. We'll explore this information in detail in further movies. Another nice thing about the new Excel environment, is the ability for you to gather more information. I'm just going to type in a1000000 in my little are here, and hit the Enter key. And this takes me down to the one millionth row in the Excel spreadsheet.
If we peek down a little bit further, you'll see that there's even more than one million rows of data that you can handle. The other nice thing is that it also will take you to 16,000 columns. So if you multiply those together, you've got one huge spreadsheet for you to be able to put data in. Don't worry, you're never going to have that much data to analyze at one time. Another nice thing about this new 2007 Excel spreadsheet, is the ability to quickly format your cells using style gallaries. I'm just going to take a look over here, after I remove my filtering, to this styles area, and I'm going to select Format as a Table.
I quickly select the area of my table that I'm interested in creating a new look and feel for, click on the Format, and I'm given a huge array of different looks and feels that I can choose from. Let's say I want to get very creative with my look and feel, and I like the red style. I simply hover over it, click once, ensure that my information is the area that I want to work with, click OK, and all of a sudden the new look and feel is applied to my table. Pretty easy, isn't it? If I'm not too keen on the color, I can simply hover over each of these to see all the different looks and feels I can work with. Again, we'll get into more detail on how to do this in subsequent movies. Another nice thing about this new version of Excel is the ability to create professional looking charts.
And I'm just going to give you a little taste of that kind of information by clicking over onto a new report format and displaying that for you there. This is very, very easy to do. And we'll be exploring how to do this in subsequent movies as well. The ability to create pivot tables was more of a challenge than anything, in previous versions of Excel. The way that you organize your data and capture your data, is not necessarily the way that you want to present and analyze your data. The use of pivot tables in Excel's 2007, is an easy way to do just that. What you can do is you can take your information that's displayed, as in the tabular format that we see here, and easily create a pivot table which summarizes and displays the information in a very readable format.
This is something that's very easy to do in 2007, and we'll be exploring that in a movie later on. If we go back to our source data screen, another nice thing about the 2007 version of Excel, is the different ways that you can take a look at your information on the screen. In previous versions of Excel, and in other spreadsheet applications, it took a long time to see what the information that you're seeing here looked like in a printable format. Down at the bottom of the screen, you have a new view, which is called Page Layout, and if I click on that, it changes my screen to present to me how this information is going to be displayed on a page. I can also go into the areas, and change the information for my headers and footers very, very easily on the screen.
So instead of having to go and hunt for my layouts or my page headers and footers and titles, I can change this information right here. So it really does give a novice user, for those of you that her brand new, the ability to see exactly what you're going to be presenting, and change it right then and there. So it's a very, very easy to use format in the new application. If I move back to our regular workflow page, I can also identify trends in my spreadsheet here by using what's called Conditional Formatting. And this is something also that's new in Excel 2007.
I simply highlight the area that I would like to use the conditional formatting in, click on the Conditional Formatting screen, and choose from the list there of what I'd like to show. Let's say I'd want to identify the top 10 items in this particular list, and I don't want to have to sort alphabetically, I want to present them in this area. But I want to be able to highlight them for anyone looking at my spreadsheet, or perhaps printing it out. I simply select the Top/Bottom Rules list, I click Top 10 Items. It identifies again, how I want to present them.
DO I want to highlight them in red? Or would I like to highlight them in green, with dark green text? I get to see right here behind me, what it's going to look like when I make my selection. I think the dark green doesn't highlight it quite as much as I want; I think I want to put it in red text. Click OK, and it goes through the selected area, and identifies the top 10 items, or the highest ranking items in this particular area. So that's conditional formatting and see how easy it is to take your information and highlight it, and then go in to present it with two other people who may be using the same spreadsheet. Those are just a few things that have changed in Excel 2007.
In our next movie, we're going to take a look at the most obvious, or the biggest change in Excel 2007, and that's the user interface, and the ribbon.
Skill Level Beginner
Q: When trying to apply the techniques from the “Relative and absolute referencing” video to a worksheet other than the exercise file included with the title, the formulas did not work for the entire worksheet. The formulas would only work when going through the worksheet row by row. What could be causing this to happen?
A: When trying to apply formulas to a whole workshee, here is a tip to try:
If you want to always refer to the same cell then use an absolute reference. For example, always pulling the value from cell A3 would be referenced as $A$3. This will never change no matter where you copy it to in the spreadsheet.
If you want to reuse the same formula, but with values in different cells, use the relative reference, A3. This way formula =A3*B3 will become =A4*B4 as you copy it down a column.
Q: In the chapter 7 video "Sorting and Grouping" at approximately 4:05, the author says to go to cell 5 on the worksheet and click on Subtotal to subtotal the grouping. My screen will not allow me to click on the Subtotal option at the top of the page. Is this an issue with my version of Excel?
A: It seems that there is an error in the instructions in this video. The video should have instructed users to do the subtotaling first, then create the table.
Q: Where can I learn more about Excel formulas?
A: Discover more on this topic by visiting Excel formulas on lynda.com.