Excel Tutorials

We all know that Excel takes dull number figures and makes them eye appealing in graphs and charts, but did you know that you can also jazz up graphs and charts with pictures. You can, and it’s so easy in Excel 2007. In this short tutorial, I will show you how to insert a picture in a pie chart slice.
Please Note: Before formatting the pie chart with a picture, have an idea of what picture you want to use and where you have it located in your computer so that you don’t waste time searching for it when working with the pie chart.

1. Go to Start>Programs>Microsoft Excel. Go to Office>Open to open the Excel spreasheet where you have the pie chart saved.
2. Find the pie chart, and click the slice of the pie chart where you want to insert the picture. Once selected, right click it and go down to Format Data Series. In the “Format Data Series” pop up window box, select the “Fill” option and click on “Select Picture.”
3. Next, click on “File” and find the picture you want to insert within the “Insert Picture” pop up window box. Once you find the picture, hit “Insert” and click OK within the “Format Data Series” pop up window box.
4. Now you can see the picture in the pie chart slice area that you chose.

Pretty Neat! This is just one of the many cool things you can do in Excel 2007.

Posted in Charts | Comments (0)

We can automate a lot of things with excel sometimes though we need to have visual appeal. Using charts to impact our audience instead of plain boring numbers makes sense. Pie charts are one of Excel’s most options used to display number and percentage information. You can actually keep the slices within a pie chart together or select them all and separate them for further impact. But, one feature that we don’t use often is separating just one slice from the pie chart to highlight that specific piece of data. I’ll show you how to create a pie chart and highlight just one part of it in a couple of easy steps.

1. For this lesson, we’ll use salespersons and profit information from a beverage distribution company. First, go to Insert>Chart and the chart wizard formatting box will appear. Under “chart type” select the pie chart and under “chart sub type” select the first image, and click Next.

2. Now select the data range that you want to chart in the pie chart – in this example, select all the salespersons and profits data as seen in the screen shot below, and click Next. Then, click on the “Data Labels” tab and select “Percentages,” so that the profits data is displayed as percentages instead of dollar values. Finally, click Next and select the second option to place the pie chart in the same spreadsheet.

3. Your pie chart should look like the image below. Now click on the actual pie chart so that you can see how the whole pie chart slices are selected – at this point you could drag out all the pieces and they would separate from each other. Now deselect the whole pie chart and click on the light green, 16% slice. Notice that only that slice is selected and drag it out slowly. As you can see it will separate from the pie chart alone leaving the other pieces attached together. The image should look like the last screen shot. You did it! Now you know how to separate just one slice from a pie chart for maximum impact.

Posted in Charts | Comments (0)

The 3-D cells we made were cool, but what else can we do with cells? We all use the handy dandy post it notes. They are a life saver to quickly remind us of important points that we might forget with all the chaos that goes on at work. Well you can actually use this same principle within Excel spreadsheets by using this electronic equivalent of the “post it note.” You can do this by inserting a comment. In this short lesson, you’ll learn how to insert a cell comment and customize it.

1. We’ll use the following spreadsheet for our example – this spreadsheet contains salespersons and profit information for a beverage distribution company. We want to insert a comment within the Mary Roberts row to indicate that she continues to be the top seller for past couple of months. First, select cell B9 and go to Insert>Comment and a box with your name will appear where you will type in your brief reminder.

2. Now, go ahead and enter your comment (remember to keep it brief and concise). In this example, I wrote that “Mary Roberts continues to be the top seller.” Once you’re done, click out of the cell. Whenever you want to view the comment, just gently place the cursor over the tiny red triangle within the cell where you inserted the comment, and the comment box will appear.

3. Now if you want to change the look (font type, color, etc) of the comment, select the cell where you placed the comment and right click, and go down to “Edit Comment” and select this option. Now you can play with the look of the words; in this example, I selected the whole comment and made the font blue in italics and bold – remember you can change the font colors and styles by using the format menu or the top formatting buttons.

4. Now every time you click on the comment it will appear as you customized it. Well done! You learned how to insert the electronic equivalent of the sticky note in a excel spreadsheet.

Posted in Cells | Comments (0)

Working with spreadsheets and formulas can become dull, but there are some fun ways that you can easily energize a spreadsheet like for example creating a 3-D effect on a specific cell. In this lesson, I’ll show how to do this in three easy steps. The 3-D effect on the specific cells looks as if the cell is elevated.

1. We’ll use the following spreadsheet with profit and salespeople data, but I’ll show you the 3-D effect on an empty cell within the spreadsheet. First, select cell D5, then go to Format>Cells. When the “Format Cells” box appears, click on the “border” tab. In here, select the thickest line style bar (make sure the color is black or automatic). Now click the right-hand border and then the bottom border.

2. Next, ensure that the thickest line style is still selected, and change the color of the line to white by selecting white from the color option. Now go ahead and click on the left-hand border and top border. Finally, click on the “Patterns” tab to change the fill in color of the cell, and select the gray color. Then click OK to exit out of the “Format Cells” box.

3. The final product should look like the below screen shot. As you can see the formatted cell looks elevated as in a 3D look. Great! Now you have a quick and easy way to spice up individual dull cells.

Posted in Cells | Comments (0)

You probably have one workbook that contains most of the spreadsheets that you use on a daily basis, and scrolling from left to right, and right to left to find the specific spreadsheet that you need wastes time. We know how to input the same data into multiple workbooks simultaneously now, too make your life easier, you can create an index of all the spreadsheets within one specific workbook to help you find a spreadsheet quickly. A sort of table of content. In this lesson, I’ll show in four easy steps how to manually set up an index of spreadsheets within a workbook.

1. We’ll use as an example the following workbook that has three spreadsheets – Jan 07, Feb 07, and Mar 07. First, place your cursor to the left of the first spreadsheet and right click it, and select the first option “insert.” This will insert a brand new spreadsheet. Now click on the new spreadsheet, right click it and select the option “Rename” and call it “index.”

2. Now starting in cell A1, write down the names of the spreadsheets in the workbook – in this example, first enter “Jan 07” and then enter the remaining two names going down column A as shown in the screen shot below.

3. Next, click on cell A1 “Jan 07” to select it and go to Insert>Hyperlink. A box labeled “Insert Hyperlink” will appear, select the second option on the right hand side called “Place in this Document” and enter A1 under “Type Cell Reference” or click “Jan 07” under “Or Select a place in this document.” Repeat this step for all the names you want to hyperlink, in this example, it would be Feb and Mar 07.

4. Finally, when you open the “index” spreadsheet you will see the names of all the spreadsheets highlighted in blue. You can click on any of them, and you will be linked directly to the spreadsheet that you select without having to scroll down to find it. Great! You just created a shortcut to finding your spreadsheets within a workbook.

Posted in Shortcuts | Comments (1)

Sometimes you may need to change the same data in a several of worksheets, so instead of going through each of them separately, Excel let’s you group them and enter the new information once and make it appear in all the other worksheets that you specify.

1. We’ll use the following Excel sheet for this example – it contains sales data for a beverage distribution company. First, you need to group the all the spreadsheets where the same data will be entered, so click the Control key and select the individual worksheets. In this example, you need to fix the last name of Mary Roberts by inserting the missing “t” in Roberts. We are going to do it once and the change will appear in all the other spreadsheets.

2. Now that you have selected all the sheets where the same change needs to happen – in this case, you want to fix Mary Roberts’ last name in the January, February and March 2007 sales sheet. Next, go to cell A8, and fix the last name from “Robers” to “Roberts.”

3. The last name has been fixed in all three spreadsheets. Click on the January and March 07 sheets to double check that the change took place. Now, hit the Control key and de-select all the individual sheets to ungroup them. Fantastic!! Now you know how to make changes or enter data into multiple spreadsheets at the same in three quick easy steps.

Posted in WorkSheets | Comments (1)