Join David Rivers for an in-depth discussion in this video Adding and removing sheets, part of Excel 2008 for Mac Essential Training.
I don't know about you, but I have been using spreadsheet applications for a very, very long time. Do you remember this one? Lotus 123, and I was using that back in the DOS days on a PC and then along came Windows and there were some early versions of Microsoft Excel and Quadro Pro. You might remember that one. Even on the Mac there were early versions of spreadsheet applications and all of them have one thing in common. When you opened up a file, you had one huge spreadsheet. There were hundreds of columns, thousands of rows and all of your data went on to one big sheet.
And that made it very difficult when you had different areas where you had data stored in different pockets and some of that data was used in other areas and then navigating through this huge sheet was difficult, cumbersome and things really improved when you finally had the ability to have multiple sheets in a workbook like we have now in Microsoft Excel. So this chapter is going to be all about working with sheets and sheets really help you to stay organized. Keep things nice and neat and clean and easy to work with.
You can see I have already open up a file here from the lesson three folder of the exercise files. If you have got them and you want to follow along, open up Revenues1.xlsx and you will have what I have. Now, this particular workbook is made up of multiple sheets. You can see them down below represented by these tabs. I have got an Overall sheet which I am currently viewing. Next is ILT short for Instructor Led Training. If I click on that tab I view that sheet and then I have got Tutorials. If I click on that tab I see the Tutorial sheet.
You can see how every thing is nice and neat, located in one area and if I go back to the Overall sheet I have got columns for Instructor Led Training, Tutorials, I have also got one for Webinars that is currently not located down at the bottom, but information in this Overall sheet is going to come from those other sheets. So let's say that my responsibility is Instructor Led Training and I might just work on this one sheet. I don't have to worry about the other tabs down below. It's easy for me. I don't have to locate it. It's right there, one easy tab to click on and I have got everything I need in front of me.
Now other people might need to work on Tutorials or maybe the Webinars for example. All of that info will come back to this Overall sheet and that's the beauty behind having multiple sheets. So this chapter like I said is all about working with sheets. In this lesson we are going to show you how to add sheets because it would appear that according to this Overall sheet, we are missing one for Webinars. Now, there are different ways for us to add sheets to an actual workbook. One way is just to come down here and click the little + sign where it says Insert Sheet.
Now doing that accepts any defaults. Let's try. We will click the + sign. It adds a new sheet. It's a blank sheet. There is nothing on it. No formatting, no data, no head start and it's added to the end of the last sheet here after Tutorials. Easy as pie. Now, if I go back to the Overall sheet and I know that numbers are going to be coming from these other sheets and I look at ILT and I look at Tutorials, they look very similar in nature. One has an extra column, but wouldn't it be better if I could actually take this sheet and copy it.
Well I could. I could copy all of the contents here and paste them over here on this sheet, but this sheet doesn't have the same attributes as the others. For one you can see all of the gridlines, so the formatting and so on would have to be applied. So there will be some manual labor involved, but inserting the sheet was pretty easy. Another way to insert new sheet is just to go up to Insert menu and from here you will see we do have a Sheet category with a submenu where we can insert a Blank Sheet that's just what we did by clicking the + sign. This is the equivalent.
But you can also insert different kinds of sheets as well. Later on in this title we will be working with charts inserting a Chart Sheet actually starts a brand new chart for you. So you get a nice head start. Same thing if you are working with List. Later on when we explore List, we will insert a List Sheet which gets you started with a brand new blank list. So for now we are just looking at Blank Sheets. Notice that we have Others down below as well. Clicking Others going to display the Project Gallery and in here you can see we have got the ability to insert those three sheets we just saw a blank, there is chart, there is list.
There is a couple of others as well. Macro Sheets and Dialog Sheets. Now I am going to Cancel this and show you probably the best way to insert a new sheet and that's from the Elements Gallery. Up here the very first tab in the Elements Gallery is Sheets and when I click on that notice the categories Accounts, Budgets, Invoices, Lists, Portfolios, Reports and Blank Sheets. All of these are categories that allow me to select from these thumbnails down below, and as I harbor over them I find out a little bit information over here in the left hand side about that sheet.
This is for Account Balances, Business Checkbooks so a lot of the formatting and some of the actual labeling and so on is done before you get a nice head start. If I go up to Budgets I will see different options like Bill Trackers, Event Budgets. Now if that's great if you want to insert a brand new sheet that's different from the rest. Now, I am going to close up the Elements Gallery here by clicking Sheets again and really what I want to do is not insert a blank sheet or any of those other sheets. I want one that looks like one of these.
So let's clear the scenario here. The Webinars actually tracks revenues from Live Webinars as well as Generic Webinars and then there is also an Archive. So I am thinking it's probably going to look a lot like the Tutorial sheet because we want three columns and then our Total. So wouldn't it be nice if we could just take a duplicate of this one and then tweak it a little bit to be our Webinar Sheet. Now that's exactly what we are going to do in the next lesson. So before we do that we need to get rid of the new sheet we just added.
Clicking on the Sheet tab here displays the blank sheet. Now, when you insert a sheet that's not something you can undo. Notice the undo button is not available. Wouldn't that be easy? Just click undo and the sheet is gone. But you can't. You actually have to delete a sheet. If I go up to the Edit menu over here, you will notice that I have got some options like Delete Sheet there. There it is right there. I also have Move or Copy Sheet. That's what we are going to be looking at in the next lesson. Another option is to right-click or if you got a single button mouse hold down your Ctrl key and click on the Sheet tab down here at the bottom that displays a menu which includes Delete.
So we are going to do that. We will need to confirm that we meant to delete that because anything that's in here will be permanently deleted. This is blank, so I am okay with clicking OK. It's gone, we are ready to move on to the next lesson now where we are going to actually duplicate one of these sheets and then a make a few adjustments.
- 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: