Join David Rivers for an in-depth discussion in this video Creating and opening workbooks, part of Excel 2008 for Mac Essential Training.
Well, the previous chapter was all about getting you comfortable in your Surroundings, familiar with the User Interface. You now know you can modify your Surroundings and your User Interface to totally suit your needs. So, with the formalities officially out of the way, it's time to start using Excel. This chapter is going to be all about Workbooks. So we are going to look at ways to create brand new Workbooks, and get you a nice head start using the Project Gallery. We will look at ways to open existing Workbook, even import Workbooks that are of older formats in Excel, or different formats all together.
We will look at some saving techniques using Undo and Redo, and then we will explore working with pages, including page numbering in this chapter. Right now though, in this lesson, we are going to look at creating and opening Workbooks. Now, the very first time we launched Excel, we saw that Project Gallery show up. We are going to go back to that for sure because there are some great shortcuts in there. We bypassed the Project Gallery the first time by clicking Cancel, and this is what we ended up with, a blank Workbook. Now, you can create a new blank Workbook anytime, just by going up to the File menu, and choosing New Workbook.
Notice that command N on the keyboard will do the exact same thing, but it is blank, and you have to start from scratch; you fill in everything, including the formatting. If you want to head start, you are in a rush, whatever the reason, you don't want to have to do all of the work, why not access the Project Gallery. Notice that on the keyboard the shortcut is Shift+Command P, as in Project, and we are going to access it by clicking File, Project Gallery. Now, with the Project Gallery opened, you can see that I have got a number of categories down the left hand side, and one of those categories is selected, Blank Documents.
Over here in the right hand side, where I have got some thumbnails, one of them is already selected, which is Excel Workbook. Now, clicking Open right now would create a New Workbook, a blank one, just like the one that's already open. So that's not what I want to do. Another option for Excel is to create a list using the List Wizard. These are the only two options for Excel in the Blank Documents' category. Notice the others have different logos in the top. Here are some entourage options, like a Calendar Event and Mail Message.
Here is a PowerPoint Presentation to create a blank PowerPoint Presentation. I wouldn't do that in Excel, just like I wouldn't create any of these three Word Documents, whether it's in Notebook Layout or Publishing Layout. So the Project Gallery we know is shared by the other applications in the Microsoft Office suite, but we are only concerned with Excel options right now, and that's why this blank Excel Workbook is selected. But there are more. Yes, if we go over to the Categories here, notice that we have got Categories listed alphabetically.
I am pretty sure if I go to Brochures, I am not going to see any Excel options, all of these would be created in something else like Microsoft Word. But if I go down to something like Home Essentials, well, I wouldn't just click on Home Essentials because its got a little triangle next to it indicating there are subcategories. So if I click on that, look at that, there's Finance Tools, and I have got a College Cash Flow, for example, Investment Calculators and so on. All of these use Microsoft Excel. If I go down to Travel Tools, Itineraries and Road Trip Logs, these are Excel files.
I can collapse this branch by clicking the same triangle, and those with Home Essentials selected, all I see is Project Gallery information over here. But notice I have also got Ledger Sheets if I click on that one. Wow, I can get to Accounts, for example, to create all kinds of Excel Workbooks that are preformatted and set up for me; like Savings Passbooks and Checking Ledgers and Business Checkbooks. I have got a series of Budgets; Event Budget, Home Budget, Travel Budget, Income and so on, Invoices to choose from, Lists, Portfolios, and even Reports.
So if I wanted to create any of these from scratch, if I have got the big head start with the formatting and set up of the sheet, I can go to any one of these, like Expense Report, for example, and click Open. When I do that, not only does it create a brand new Workbook, and you will notice up here it says Workbook2 now, that's because it hasn't been saved, and given a name at this point. It's actually a blank file, but a lot of the work has been done. There is some formatting in here. Notice, alternating rows are shaded for me. I have got some headings like Date, Payee, Category, all the way over to Posted here.
This column uses check box, its already formatted for me. Notice the Category column down below, in the Category field is where I can go to select from a list of categories that show up when I click here. So if I was filling out my Expense Report for Entertainment, I will just select it over here and it gets input for me, rather than me typing it. I am not going to select anything, I will just leave Bonus in there as the default. Also, I can set up the title and so on. Up here at the top when I move my mouse pointer, it changes into this little character representing, I am in the Header area, it says Enter report name, so I can do that by double clicking.
I am going to type in My Expense Report. Now, I am going to double click down below to lock that in. There is another area down below for entering report comments, I could double click there. If I don't want any, I hit Delete, gone. Or I could type in something here like my trip to, let's say it was Cuba, 2008, and then I just double click down here again to lock that in. So there we go. Once I save this and give it a name, the name is going to show up over here by default, next to File.
All of this has been set up for me so it's a lot of work I didn't have to do because I went through the Project Gallery. Even this column for Total Paid is automatically going to calculate totals for me. So a lot of the work is done by using the Project Gallery. Of course, I can manipulate this, remove columns I am not going to use, add new columns that I think are missing, but the majority of the work is done for me. So that's just one of the ways to create a new Workbook here in Excel. Now, if we want to open existing files, well, we need to navigate to the location of those files.
So we are going to go to our Open button, same as going up to File, and choosing Open from here. Command+O on your keyboard is the shortcut, if you like using the keyboard. But since the button is here on my Standard toolbar, I am going to click Open. I am going to navigate to the Lesson2 folder of the exercise file. So if you have got the exercise files, meaning you are a premium subscriber or you have bought the actual CD, you can access these files along with me. If you don't have them, no problem, just open up any old file, and I am going to go over here to Expenses, ECP_Expenses1, and I want to make sure it's the xlsx format.
So we have got a new format here in Excel 2008, the extension is not just xls, like it used be, we have got this extra x on the end, indicating we have got some XML technology built into this. So ECP_Expenses1.xlsx, that's selected, I click Open, and it's just going to open it up for me. So there we are, we have got one sheet down below. This is an expense report that was already been filled out. You can see its got the Name, who is it To, the Purpose, Department, Data Submitted, and a whole bunch of options in here, already filled in with the tool down below.
So opening an existing file, no problem. If we are going to make changes, of course we want to save those, and there is a whole lesson devoted to saving. So, just a couple of ways to get started here in Excel. You can create New Workbooks by going Command+N on your keyboard, or creating a new blank Workbook from File, choosing New Workbook. But, if you want a great head start, go up to the Project Gallery under the File menu, and you will always have access to those head starts that we just looked at to get you going. Now in the next lesson, along the lines of opening a workbook like we just did, you may want to bring in some older formats, so older Excel files, for example, or even formats that are not Excel formats.
In the next lesson we are going to look at importing workbooks.
- Customizing the user interface Using workbooks Adding and removing sheets Restricting input with validation rules Formatting workbooks Using formulas and functions Working with charts Adding, removing, and editing text Aligning and layering objects Creating PivotTable reports Sharing spreadsheets Creating custom templates
Skill Level Beginner
Q: How does one generate an average using cells in columns that are not consecutive i.e. a7,c7,e7...?
A: To get an average of non-contiguous cells, you can either select them individually, or type them in manually. Here are the steps involved:
1. Click in the cell where you want the average to appear
2. Start the function by typing: =average(
3. Now, either type the cells addresses (ie A7,C7,E7) or select each cell by clicking them while holding the Command key.
4. Close off the function with closing round bracket: ) and press Return key
You should see the answer in the cell where you entered the function. Checking the formula bar, your finished "formula" will look something like this: