By Richard Rost 14 years ago
This is the full text listing of one of our handbooks. There is a lot more to this handbook. The full-color screen shots have been removed for this page. This text is simply provided so that the search engines will index the course contents. This is so any customer searching for a topic can find what class it's covered in. If you are interested in more about information about our courses, click here for our complete course listing. For details on how to purchase a handbook, visit our handbooks page.
Microsoft Excel 102
Course Handbook Supplement
By Richard Rost
PO Box 1308, Amherst NY 14226 USA
First Printing 6/30/2005
Copyright 2005 by Amicron Computing
All Rights Reserved
Welcome to the 599CD Microsoft Excel 102 Handbook. This course follows Excel 101.
This handbook is designed to be a supplement to the full 599CD video course for Microsoft Excel 102. We recommend you use this handbook to follow along with the class videos. This handbook is not meant as a stand-alone study guide.
We do recommend that you watch the course videos one time through, paying attention to the lessons covered. Follow along with the course videos using this guide. Take notes on the pages where needed. Then, watch the videos a second time, practicing the examples yourself on your computer.
Table of Contents
Table of Contents 2
1. Introduction 3
2. Using AutoFill, part one 4
3. Using AutoFill, part two 17
4. Sorting Data 45
5. Charting, part one 59
6. Charting, part two 69
7. Charting, part three 89
8. Review 100
Welcome to Microsoft Excel 102, brought to you by 599CD.com. I am your instructor, Richard Rost.
Objectives for today’s class:
· Creating a Calendar
Pre-Requisites: Microsoft Excel 101. We will assume you have taken Excel 101 before this class.
In this class we will be using Microsoft Excel XP and Windows XP for our live-action videos. However, the version of Excel and Windows you have does not matter. Most, if not all of these examples should be applicable regardless of what version of Excel you’re using.
2. Using AutoFill, part one
We looked at AutoFill a little bit in Excel 101. We barely scratched the surface, however, on all that AutoFill can do. Let’s begin by starting Microsoft Excel.
Here we are at a blank new workbook. I’m going to close the Task Pane.
In Excel 101, I showed you this trick: in cell A1 type in the abbreviation for January.
Now, click and drag the AutoFill Handle to the right. Remember, the AutoFill Handle is that little dot on the bottom-right corner of the selected cell. When you hover your mouse over it, your pointer changes from a white plus to a black plus.
As you’re dragging your mouse, notice the little yellow tooltip that pops up showing you where in the sequence of months you’ll be (in the picture above, April).
When you release the mouse button, you will have the entire sequence of months.
Try the same thing with the days of the week. In cell A2, type in “Mon” and AutoFill it across.
Excel also knows the full names for the days of the week…
And the months…
You can also use CAPITAL letters. Excel will try its best to match the format of whatever you type in.
Try typing in a date like “1/1/90” and AutoFilling that across.
Notice how Excel increments the day by one and gives you a series of consecutive dates. Let’s try it with a number. Type in “1” into cell A7. AutoFill it across.
I get a bunch of ones. Not what I was expecting. If you want a series of consecutive numbers, like “1, 2, 3, etc.” then you have to type in at least two of the numbers in the sequence you want. Here’s how: start by typing “1” in cell A8 and then “2” in cell B8.
Now, highlight them both together.
Now grab the AutoFill handle…
And pull it across…
Now you have your series of consecutive numbers. You can also do non-consecutive series. As long as Excel can figure out your pattern, it can create the series for you. Try “3” and “5”…
Notice Excel completes the series.
Try this one… “1/1/90” and “2/1/90”
We get a list of the firsts for each following month. Excel figured out the pattern.
Keeping this in mind, we can use Excel to create a basic calendar. Let’s come down and click on the Sheet2 tab.
Let’s start off by placing the days of the week in the top row. I’ll type in “SUN” into cell A1 and then Autofill it across.
This gives me a nice header row.
Notice how I cannot see the entire row on my screen. I’d like to be able to zoom out a little so that I can see everything. Find the Zoom drop-down box on the menu bar. It says “100%” in it.
You can zoom in or out to make the spreadsheet larger or smaller. Note this does not effect the actual size of the sheet when you print it… this is just for your viewing benefit. Try zooming in to 200%.
Notice the sheet appears much larger. This is just as if you moved your eye closer to a printed page.
Try zooming out to 75%.
Notice how everything got smaller.
Try this trick. Zoom out to 50%, which makes the sheet really small. Now, select a block of cells… say… A1 through H15.
Now, from the zoom drop-down menu, choose “Selection.” This will zoom in so that the selected region fits perfectly on the screen.
Alright, now let’s put the days in. Our calendar is for January of 2003. The first of January falls on a Wednesday, so I’m going to put “1/1/03” in cell D2.
Let’s click on that cell and Autofill to the right.
If you want to include the last couple of days of 2002, you can click on D2 and then Autofill to the left (yes, you can go backwards).
Notice what happens when I let the mouse go. I get “#####” in the cells. This just means that the data is too large to fit in the space provided.
In Excel 101 I showed you how to resize a single column by clicking between the column headers and dragging to resize them.
Remember your trick… you can double-click on that spot to make the column exactly as wide as it needs to be to fit the data in it.
Now I have the problem that all of my columns are of different sizes. I would like them all to be exactly the same size. Here’s how we can resize them all. Move your mouse over the header for column A. Notice your mouse pointer changes to a down-arrow.
Now, click-and-drag your mouse to the right to highlight all of the columns from A to G.
Now, if I were to resize any of the columns in the range, it will resize all of them to the exact same width.
I’ll resize them smaller…
That’s a bit too small. Let’s try again…
And that’s how we can resize all of our columns to the exact same width. Note that this same technique works for rows as well. You can also double-click to resize multiple columns, however if you do, each column will be sized differently to fit its own data…
That’s not what we want in this case, so I’ll resize them manually again. You can also optionally right-click on the column header and select Column Width.
You can then type in a size manually.
I’ll just hit Cancel.
3. Using AutoFill, part two
Now, I would like to change the format of the dates that I have displayed. I’d like to show a 2-digit year instead of a 4-digit year. Right-click on one of the cells and select Format Cells.
The “Format Cells” dialog box appears. Excel recognizes this as a Date under the Category list. Let’s select the format we want from the Type list.
Click on the OK button from the bottom when done.
Notice the format has been applied to the cell we had selected.
Now, how do you get that format applied to the rest of the dates? Well, you could just Autofill that cell to the right again. That would copy the format to those cells.
Let me show you something different. Let’s Undo that last change.
Now I’m going to show you how to use the Format Painter. Locate the Format Painter button on your toolbar.
The Format Painter is a great tool for copying and pasting the Format of a cell, without the data. First, click on the cell that has the format you wish to copy. In this case, click on cell D2.
Now click on the Format Painter button. Notice the “dancing ants” around cell D2. This indicates the cell has been selected for the requested operation.
Now, notice how your mouse pointer has changed to a plus with a brush.
Now click and drag to “paint the format” over the cells you want to change… in this case E2 thru G2. Notice when you release the mouse, the formats have changed.
You can do the same thing to the rest of the dates. Click on cell D2. Click on the Format Painter. Paint over A2 through C2.
Notice the format has been copied.
Here’s another example of what you can do with the Format Painter. Select cell A1, which has “SUN” in it. Let’s change the font to “Book Antiqua” and the font size to “12”. Let’s bold it, and align it to the center of the cell. Let’s change the background color to light green, and the foreground color to dark blue. It should now look like this:
Now, I just made a bunch of changes to that one cell. I should have changed all of the cells in the top row. I don’t want to have to do all of that again. Here comes the Format Painter to the rescue… Click on cell A1, then click on the Format Painter, then paint the format over the rest of the row.
TIP: If you double-click on the Format Painter button, it will stick on until you turn it off. This is handy if you would like to change the format of several different cells that aren’t necessarily together. Try it… you can select individual cells to change the format of. Click again on the Format Painter button to turn it off when finished.
Personally, I don’t like “Book Antiqua” so I’m going to switch back to “Arial”.
I’ll also shrink the column widths just a little bit so everything fits on the screen.
OK, let’s type in the following week. The next Sunday is “1/5/03”. Let’s type that in. Oh no… look what happened. Excel reverted back to the old format. That’s because we never changed the format of cell A3.
So, I’ll click on cell A2, click on the Format Painter, and paint the format to cell A3.
Now I can Autofill across the entire row…
Now, watch this: highlight cells A2 and A3 together.
Now, Autofill down the column…
Notice you have a list of the next several Sundays in a row.
Undo that. Here’s something better. Highlight both of the weeks we have already…
Autofill the whole thing down several rows…
Look at that. Cool trick, huh? I’ve got a couple extra rows in there. I’ll delete them.
Now, we want to put a header row at the top of this calendar so we know what month it is. We’re going to insert a blank row in front of row 1. Here’s how… move your mouse over the header for row 1. Notice how it turns into a right-arrow.
Click once to highlight the entire row.
Now, right-click on the same spot and select Insert from the menu.
This will insert a blank row in front of row 1.
Now, click on cell A1.
Now I’m going to type in “January 2003”.
Watch what happens when I press ENTER.
Excel played a nasty trick on me. It changed the format of the text I just typed in. It changed it into a date instead of just text. Go ahead and Undo that. Here’s a trick… if you want Excel to treat whatever you’re typing in as TEXT only, then put a single-quote in front of it like this:
Notice that Excel will now treat that cell as TEXT and won’t try to convert it or reformat it at all. This will come in handy later.
For example, you’ve probably experienced this problem before… try typing a ZIP Code with a leading zero in it, such as “01121”.
Press TAB or ENTER and notice that Excel tries converting that number into a date. It might also try to convert it to a number and you lose your leading zero… getting just “1121” in that cell.
I’ll go over the Sheet3 for some scratch space… Take, for example, a Social Security Number. If you type it in without the dashes, you get:
Press ENTER and Excel conveniently loses the leading zero for you.
If you want Excel not reformat your data, make sure to begin with a leading single-quote.
OK, back to our Sheet2 tab… let’s make the header, the “January 2003,” a little bit bigger, and perhaps bold it.
Now, I’d like to center it across the top of the calendar. I want it centered evenly across seven columns. If I just simply click on the center button, Excel attempts to center the text inside its own cell. That’s not what I want…
Let’s put it back to Align Left or just Undo that action.
To center something across multiple columns, first highlight all of the columns you want it to be centered inside of.
Rick’s Tip: I didn’t mention this in the video, but make sure your text is in the left-most of the columns! I guess I figured it would be obvious.
Now, find and click on the Merge and Center button. Your text will be centered across those columns, and notice that these columns have all now been merged into one big cell.
And, of course, now you can apply different formatting to this cell… you can change the foreground color, the background color, fonts, etc.
Rick’s Tip: Earlier versions of Excel did not merge the cells into one big cell. They just had a feature called “Center Across Columns” which did the same thing, but left the cells separate.
Now, let’s take a look at the Print Preview for this sheet, to see what it would look like if we were to print it right now…
This is what we get… a zoomed-out version of our sheet. You can see the full sheet of paper as it would look like when printed. Notice that your mouse pointer has turned into a magnifying glass. Click anywhere on the page to zoom in on it – click somewhere over the printed text of the calendar.
Notice how Excel zooms in to that section.
Notice there are currently no borders (or gridlines) around any of the calendar dates. Click on the Close button on the toolbar.
Now, find the Borders button on the toolbar.
The default selection for this button (if you click on the button itself) is a thin underline. So if I were to click on a cell and click on the border button, I would get a thin underline…
I’ll click off of that cell so you can see what it looks like better…
Notice the cell under column F that has the thin underline. Go ahead and undo that.
Click on the drop-down arrow next to the border button to open up all of the options.
You can see all of the different kinds of border options that are available. The upper-left selection is for no border (to turn all borders off).
There are a bunch of different types of borders, and to be honest, the best way for you to learn them is to just take a few minutes and experiment with them.
Let’s try this… go back to your spreadsheet and highlight just your dates:
Now, come back to your border selection box and choose the All Borders option.
Notice how you now have borders around all of the cells that contain your dates. Click off the selection somewhere to see it more clearly.
Now let’s put a thick solid border around the whole thing. Let’s highlight all of the dates, but this time include the header row too (A2:G7).
Now, select Thick Outside Border from the border drop-down box.
Now, notice how there is a thick single border around the outside of that box.
I’ll also put a thick box border around the January 2003 cell as well. Just click on the cell and do the same thing (sure, you can put a border around just one cell). I’m also going to change that font color from blue to white… just because it shows up better on the printed version. Let’s get another Print Preview…
That looks pretty good. Click Close on the toolbar. Let’s highlight all of our dates and click on the Center button to center all of the dates inside their cells.
And now do another Print Preview and you’ll see the difference.
Now, I’d like to be able to leave enough space inside each of these date cells so that after I’ve printed the calendar out, I could write stuff in under each date. Let’s resize the rows so we can fit stuff in. Begin by clicking on the header for one of the rows.
Now move the mouse over the border separator and click-n-drag to resize the row – just like we learned how to do with columns.
Now that resizes row 3 nicely.
Again, as with columns, you can resize multiple rows by selecting them all…
…and then resizing any one of them. This will resize them all to the same height.
Notice that Excel aligns the data on the bottom of each of the cells. We want to align the data vertically to the top of the cells. So, once again, highlight all of the cells you want to change.
Now, right-click somewhere in your selection and choose Format Cells.
This time, click on the Alignment tab.
We’ll discuss all of the different options on this tab later. For now, find the Vertical drop-down box, and select Top from the list of options.
Now, click OK.
Now, notice we’ve changed the vertical alignment of the cells from bottom to top. The data is now lined up with the top of the cell.
Now, scroll down. Notice that I didn’t highlight the bottom two rows of my calendar. No problem. I can just click on one of the cells from the top of the calendar, and use the Format Painter that we learned about previously to paint this new format over the bottom two rows.
And now all is right with our calendar.
Optionally, you might want to hide the years in our dates. Just select the cells, right-click on them, select Format Cells, go back to the Number tab, and then select the date format you want.
That would give you the date format you want.
Then you could format paint it over the rest of the cells. Me, personally, I like having the dates. So, I’ll undo that change.
4. Sorting Data
In this lesson we’re going to use Excel to sort data. Let’s begin with a blank, new workbook. I would like the following fields (columns) for this sheet:
· First Name
· Last Name
· DOB (Date of Birth)
· SSN (Social Security Number)
· Commission (rate)
· 2002 Sales
· 2003 Sales
Go ahead and set up the column headers, and start entering some sample records. Notice how when I type in my Social Security Number for this sample employee, the number begins with a zero.
Like we saw in the previous example, Excel drops off that zero.
We can fix the problem by using the leading single-quote (like we learned earlier). Notice how when we enter the number with the single-quote, we get a little green marker over the upper-left corner of the cell.
If you click on this cell, you’ll see a little warning message pops up.
Drop down the little box that appears and you’ll see some “help” options. In this particular case, we have a number that’s being stored as text – so that’s the option that shows up. There are some other options, such as to convert this text to a number. We don’t want this. So, let’s click on Ignore Error.
This will force Excel to stop bugging us about this error. It’s not really an error, but Excel is trying to be helpful by pointing this potential problem out for us.
Note that you could also type in the SSN with the dashes, like this:
Joe’s commission rate is 5%, which I’ll type in as 0.05.
And, let’s type in Joe’s sales… digits only.
Just to make some simple formatting changes, I’m going to zoom out a little bit, change my header row so it’s bold and green, and change “commission” to “com rate” just so it fits better.
Let’s make the DOB header text and data line up to the left side of the column. Highlight the entire column and click on the Align Left button.
Let’s make Commission Rate, and the two Sales columns line up to the right side of the columns.
Let’s highlight all of column E and click on the Percent Style button. This will format all of the numbers in that column to look like percentages. Remember, percentages are fractions of 1.00. So, 50% would be 0.50.
Let’s do something similar for columns F and G. Highlight them and click on the Currency Style button.
You might only want to see whole dollar amounts in these columns. So with them still highlighted, click on the Decrease Decimal button once. Notice that the number of decimal places after the point has gone from two down to one.
Click on that button again. Notice the number of decimal places has gone from one down to zero.
Notice how I could click on the Increase Decimal button to increase the number of places after the decimal point. Click on it three times, for example, and you’ll have three decimal places.
Let’s put it back to zero decimal places and type in another sales rep. For this sales rep, I’ll type in a commission rate of 5.5%.
Notice when I hit TAB to move to the next field, Excel changes this to 6%. What’s happened?
Notice how if you click on that field, you’ll still see the exact, original value in the formula bar above the spreadsheet.
This is essentially a decimal point problem. Right now, the column is only set to display zero decimal places. So, highlight the column and click on the Increase Decimal button.
Take a moment now to type in a couple more sales reps. The data isn’t important, just make sure it’s something you can work with. We need data to sort.
I would also like to put some sales totals at the bottom of my sheet. Remember how to do this?
Tip: as a matter of personal preference, I like to leave a blank row between the bottom of the spreadsheet and the totals row. I didn’t mention this in the last in the last class because I didn’t want to confuse anyone.
Now, I could use AutoFill to copy that SUM function over to column G (or I could even just copy and paste it), but here’s something new. Click on that cell in column G where you want to sum to go, and click on the AutoSum button on the toolbar.
The AutoSum button looks like a Greek letter Sigma (the wacky-looking “E”). It’s used for quickly getting a sum function of the column you’re working with.
Please be careful… sometimes the AutoSum feature guesses the wrong range! Especially if you have a long column with breaks in it. I didn’t cover AutoSum in Excel 101 because I want you to have an appreciation for how SUM functions really work. This is just a shortcut.
How about a little label for our “Sales Totals”?
Now, let’s get to the whole point of this lesson… sorting the data. The boss has come up to me and said, “I want this list sorted by the sales rep’s first name.” Generally, whenever we want to sort something in Excel, we highlight the data, and then click on the Sort buttons. The sort buttons are on the toolbar.
You’ll see the Sort Ascending and Sort Descending buttons. They are used to sort data from A-Z and Z-A (alphanumerically, and reverse alphanumerically), respectively. And for those of you who have taken my Microsoft Access 101 course, you know that “ascending” has nothing to do with the back of a donkey.
So all we have to do is highlight our data and click on the appropriate sort button. Let’s highlight the data in the range A2:A6 to get all of the first names.
Now, I’ll click on the Sort Ascending button (A-Z). Excel pops up this warning message.
Excel XP users get this warning. Earlier versions of Excel did not include this warning! Excel is warning you that it found data next to your selection that you didn’t include in your sort. Essentially, Excel wants to know if you want to sort all of the rest of the data (last name, date of birth, etc) with the first names.
Normally, you would click on Expand the selection, but I want to show you what happens if you don’t. Click on Continue with the current selection and click Sort.
Look what happened. Excel sorted only the data we had selected, so essentially your first names were sorted without taking the rest of the data with them – the first and last names are now effectively scrambled.
I like to show this to you because, again, if you’re using Excel 2000 or older, you won’t get that warning message. So make sure you select all of the data you want sorted!.
Click Undo to put the data back the way it belongs.
Now, in order to properly sort your table, highlight ALL of the data, from A1 out to G6, and then click on the Sort Ascending button.
Now, notice that your records are all sorted by First Name and all of the related fields were carried with the first name… so Amy is still “Amy Peters.”
OK… next problem… now the boss says, “I want this information sorted based on 2003 sales.” Now what do you do? Here’s the trick… highlight the same range of cells again, but this time start from the other side. Start highlighting from G2 and go back to A6.
Notice that the first cell you start to highlight is the “white” or “clear” one. This is called the Key Cell and that’s the cell that controls which column is sorted. Now click on the Sort Ascending button.
You may also want to sort the sales figures in descending order by clicking on the Sort Descending button.
Now, what if you want to sort by the Last Name field? It’s not on either end. We need some way of picking a different column to sort. We need to be able to move the Key Cell inside of the range. You can do that using the TAB key. Start by highlighting all of your data from A1 to G6.
Now press the TAB key on your keyboard. This will move the Key Cell one column to the right.
You can continue pressing TAB to move to the right through the selected cells. You can use SHIFT-TAB to move back to the left. This controls which column will be sorted when you click on the Sort buttons.
Now I can TAB to the Last Name field and hit the Sort Ascending button. Now, the data is sorted by last name.
Notice that I have two Smiths, and Joe Smith is on top of Charles Smith. If you need to be able to sort by multiple columns, for example Last Name and then First Name, you need to use a different approach. Highlight your cells and click on Data > Sort… from the menubar.
The Sort dialog box appears. You can now sort by up to three columns. I’ve set this example up to sort by Last Name. Then, if the last names are the same, sort by First Name. Then, if those are even tied, sort by DOB.
Our list does have a header row (the row that has the headings in it). Click on OK and notice that our data is properly sorted. Charles Smith is now listed on top of Joe Smith.
Save this workbook as Sales Reps so we have it for a future lesson.
5. Charting, part one
In this lesson, we’re going to learn how to make charts using Excel. The boss wants us to make a chart showing the sales reps and their 2002 sales figures.
One problem we have is that the sales rep name (let’s use just first names) and the sales data are not next to each other. In order to select the correct data for our chart, we need to know how to highlight non-contiguous cells (in other words, cells that aren’t next to each other). First, select cells A1:A6.
Now, hold the CTRL (Control) key down on your keyboard, and select F1:F6. This is how you select cells that are not next to each other.
Now that we have the data selected that we want to chart, click on the Chart Wizard button.
When I click on the Chart Wizard, the Office Assistant, Clippy, pops up. I’m going to tell him to go away.
The Chart Wizard starts up. We are on step 1 of 4. Pick the Column chart from the list of chart types, and select the first chart shown (upper-left choice). Click Next.
Step 2 shows a sample of what your chart is going to look like. You’re asked to verify the data range. Don’t worry about what all of this means right now. We’ll cover this in more detail in future lessons. If the chart sample looks good to you, and it does, just click on Next again.
Step 3 shows you some chart options. You can change things like the Chart Title. You can add axes labels, and change other options. We’ll go over more of these options in future lessons. For now, again, just click Next. I want to get our first chart built without a lot of hassle… I want to show you how quick and easy it is to build charts.
Step 4 asks where you would like your chart to go. You can bring this chart as an object inside of your sheet (which makes it look like a picture inside of your worksheet). Personally I don’t like this option. It makes the chart look real cramped (unless it’s a small chart). The other option is to simply make this chart a sheet of its own. Click on the first option, “As new sheet.” Click Finish.
Now we have a new sheet in our workbook called “Chart1” and our chart is displayed.
To change the color of your chart’s bars, click once on one of the bars. Notice the dots that appear over them.
You have now selected the entire data series… or the group of bars. Now, right-click on any one of those bars and select Format Data Series.
The Format window appears. We’ll discuss most of these options in future lessons. For right now, all I care about is the color. Let’s select a lighter shade of blue and click OK.
Notice how the bars have changed colors.
Likewise, if you don’t like the background color, you can right-click on it, and select Format Plot Area.
Let’s say I just want a simple white background. Click on the white block and click OK.
Now my background color is white.
If you want to change pretty much anything on the chart, you can right-click on it and choose the Format option. Take our names, for example. You can right click on them and select Format Axis.
Now, click on the Font tab. You can change the font, make it bold, italics, and even change the size and color from this menu. Click OK when done.
6. Charting, part two
In this lesson, we’re going to make a 3-D chart. This time, the boss wants to compare 2002 with 2003 sales, and she wants them on the same chart. Come back to our sheet with the data in it. Highlight A1:A6, then hold down the CTRL key and select F1:G6. This will highlight all of our data.
Click on the Chart Wizard to begin the process of building our chart.
Let’s stick with Column charts, but select the bottom option: a 3-D Column chart.
If you’d like a quick preview of what this chart is going to look like, click on the Press an Hold to View Sample button. Hold it down. A preview will appear. Click Next to move to the next step.
In step 2, you can see the chart in more detail. The 2002 sales are in blue. The 2003 sales are behind it in red. Click Next.
Let’s put in a Chart Title. Type in “2002 & 2003 Sales.” For our Category (X) Axis, we could type in something like “Sales Rep” and it would appear as follows below the category…
However, I think that the axis is really self-explanatory, so I’ll delete that. The other axes are really self-explanatory to the reader as well. Let’s leave these ones blank. Let’s take a look at the Axes tab.
Here, you can turn the axes labels on or off by clicking on the check boxes. Here, I’ve turned off the X Axis labels.
Let’s turn them back on. Click on the Gridlines tab. These are the black lines in the background behind the chart. Right now, only the Major gridlines (big fat ones every $10,000) show up. Minor gridlines would just show up more frequently. You can turn on gridlines for the other axes, like the X Axis…
Let’s take a look at the Legend tab. The Legend is that box floating out to the right of the chart (see the previous picture). We don’t really need it here, do we? Our axes are self-explanatory. Let’s turn it off. Uncheck the “Show Legend” box.
If you did want to leave it on, you could then select where you wanted it to go… bottom, corner, top, right, or left.
I like it off in this case.
Next, click on the Data Labels tab. Data labels are little value labels that appear on the top of each of your columns. Now, I think they look really cool on 2D charts (like the one we made in the last lesson), but on 3D charts, they make the chart look real cluttered. You can put the series name, category name, or the value on top of the column…
I’ll turn them back off. Keep this feature in mind when we get to our pie charts later. Finally, the Data Table tab allows us to put a table under the chart that shows the actual values. This may be handy in some instances, but for this chart, I’ll leave it off.
Click Next. Select “As new sheet,” and instead of just Chart2, let’s call this our “3D Chart.” Click Finish when done.
There’s our 3D Chart.
Some of you may have the Chart Toolbar available. We’ll cover the features of this toolbar in detail in a future class. For now, just move it off to the side or close it.
All of the tricks we learned with our 2D column chart work here with the 3D chart too. You can right click on one of the bars and select Format Data Series to change colors and such. Remember now that you have two different data series to work with.
Notice, once I have a data series selected, if I click on a bar a second time, it highlights just that specific bar, or Data Point.
Now, I can right-click on it and select Format Data Point.
I can change the color or other settings and you can see it only changes that one data point.
You can also format the Walls by right-clicking on them and selecting Format Walls.
Now, you’re not limited to just solid colors. Click on the button that says Fill Effects.
Let’s click on the Gradient tab. Select a one-color gradient. Pick a color (I’ll pick light blue).
You can see some variants on the bottom of the window… such as having the light color at the top, bottom, middle, etc. Click on one of them to change it. You can also make the shading style go vertical, diagonal, and so on. Feel free to experiment with these settings as much as you like. You can make the gradient lighter or darker (sharper).
Click on OK when you’re happy with it. Notice the sample gradient. Click on OK again.
Now notice our nice background wall.
Let’s try something else. Right-click on one of your columns and select Format Data Series again.
Click on Fill Effects.
Let’s do a 2-color gradient from light green to dark blue with a vertical shading style…
Click on OK twice…
If you don’t like the gradients, you can also click on the Texture tab (back inside the Format Data Series, Fill Effects options). You can pick from sandstone, granite, marble, and more…
And now we have granite columns in our chart.
You can also select a pattern from the Patterns tab. Pick a pattern that you like, set the foreground and background colors, and click OK.
This will give you nice diagonal lines on your columns. This is a great feature, by the way, if you only have a black and white printer.
I’m going to change mine back to a solid color for class.
One of the neatest features of 3-D charts is the ability to rotate them around. Find the Plot Area for your chart – this is a box that surrounds your chart (see the previous picture). If you don’t see it immediately. Try clicking somewhere in this region and the box should appear. Right-click in this area, and select 3-D View from the list of format options.
The 3-D View box appears. You can see a wireframe image of a chart, and some buttons to control elevation, rotation, and perspective.
Elevation controls how high above (or below) the chart you appear to be. Click on each of the elevation buttons to move up or down a little at a time.
Hit the Apply button and you will see the changes take place on your chart in the background.
You can Rotate the chart…
The Perspective is how far away you appear to be from the chart…
When you’re done, hit OK to see your chart.
7. Charting, part three
In this lesson we’re going to learn how to create a pie chart. This time the boss wants to see the sales rep and the 2002 sales, but she wants them in a pie chart. So, begin by selecting the data. Select the first names, hold the CTRL key down, and select the 2002 sales data.
Click on the Chart Wizard.
Let’s select Pie from the chart type. From the chart sub-type selections, I’ll pick the top-middle one.
Again, if you want to see what it will look like when you’re done, click and hold the Press and Hold to View Sample button.
Click Next. Step 2 will simply validate that we have the correct data and our chart looks good. Click Next.
In step 3, our chart title looks fine. We’ll leave the legend on for this one. Let’s turn on some data labels. Let’s turn on the Category Name which has the sales reps’ names, and the Percentage which shows their sales as a percentage of the whole.
Click Next. Select As New Sheet and let’s name this “Pie Chart”. Click Finish.
And there we go… our pie chart looks awesome.
Now, for making changes, if I click once on the pie, I get dots over every data point… the whole data series is selected.
Now, click on a specific pie piece to select just that wedge. Be careful, it’s not quite a double-click.
Now, right-click and select Format Data Point. Make sure it says “Point” and not “Series” otherwise you’ll be changing the whole chart.
Now you can change the color, use Fill Effects, or any of the neat tricks we’ve learned in previous lessons.
You can also “pull” a slice out of your pie. Click on the pie. Click on it again to select a single wedge. Then, click and drag the piece away from the center.
If you don’t isolate just one slice of the pie, and you drag away from the center, you will pull all of the pieces apart.
To put the pie slices back together again, select them (one or all) and click-and-drag them back towards the center.
Now, find the Plot Area for the chart.
Right-click in the plot area and select 3-D View.
Now we can rotate the pie chart around, and change the elevation (how flat the pie looks).
Click OK when finished, and there’s your new pie…
Now click on one of your data labels. This will highlight all of them.
Click on one of the labels a second time to select an individual label.
Now, click-and-drag the selected label…
Notice how a line pops up. This is called a Leader Line and it will essentially attach the label to the pie wedge.
And, of course, don’t forget that you can select all of the labels, right-click on them, select Format and then change the color, font, etc….
You can also move the labels over the top of the pie, or anywhere else you want them.
· Creating a Calendar
· Sorting Data
· Creating Charts (Column, 3D, Pie)
Tell us what you think. Log on to www.599cd.com/Survey and take a short survey about this course.
RICK’S NOTE: I really do enjoy getting surveys from you! Make sure you visit the web page above and fill out the survey for this class. Let me know if I’ve moved too fast, and whether or not I covered material that was helpful to you!
Take your skills check quiz at www.599cd.com/Test. If you pass, you can print out a Certificate of Completion.
What’s next? Visit www.599cd.com for our complete list of courses.
Need Help? Visit www.599cd.com/TechHelp for technical assistance.
Make sure you’re on our Mailing List. Go to www.599cd.com/MailingList for details.
Contact Us. If you have any questions, go to www.599cd.com/Contact for information on how you can contact us by phone, email, or live online chat.
Don’t forget to visit our User Message Forums online at: www.599cd.com/Forums. You can chat with our instructors, other users, and even Richard too. You can ask us all of your questions, get answers, and tell us what you thought of our class.
This course, handbook, videos, and other materials are copyright 2002, 2003, 2004, 2005 by Amicron Computing. All rights reserved. No portion of this course, handbook, videos, or other course materials may be reproduced, copied, edited, or otherwise distributed without the express written permission of Amicron Computing. Amicron Computing shall not be held liable for any errors or omissions in this document.
This document may not be used as part of a training course without express, written permission from Amicron Computing and the purchase of an Instructional License. For details, contact:
PO Box 1308
Amherst NY 14226 USA
You may want to read these articles from the 599CD News: