Save Time, Work Less: The Power of Using Macros to Complete Repetitive Tasks in Microsoft Excel 2007

Product(s): Microsoft Excel 2007
Audience(s): Business Professional.
Date(s): Tuesday, September 21, 2010, 3:00PM (EST) U.S.
Duration: 60 Minutes

Event Overview:
Do you wish you could find a way to increase your accuracy in data manipulation and get it all done faster? These days, it seems there’s more to do and less time in which to do it, so it’s crucial to find ways to automate your every day tasks. Efficiency will help eliminate your daily grind. With so much information on your hands, it can be painstaking and time-consuming to sift through data and manipulate it to present exactly what you’re looking for.

Macros are one of Microsoft Excel’s most powerful, yet most underused, tools. Many users are familiar with the term but have never learned how to take advantage of all macros have to offer. They might sound complicated; within this one hour Webinar we will show you how to become a more proficient user with automating your tasks. You will be working smarter and faster than ever before!

To Register: Click Here. Fee: $99.00

Copy Formulas with the Fill Handle

1. Create the formula in the appropriate cell on the spreadsheet.

2. Click on that cell to make it the active cell.

3. Click and hold down the mouse pointer on the fill handle in the bottom right corner of the active cell.

4. Drag the fill handle to copy the formula to as many cells as needed.

Insert a Picture in a Chart

1. Click the chart area of the chart.
*This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Layout tab, in the Insert group, click Picture.

3. Locate the picture that you want to insert, and then double-click it.
*Tip: To add multiple pictures, hold down CTRL while you click the pictures that you want to insert, and then click Insert.

4. To change the size of the picture, drag a sizing handle (sizing handle: One of the small circles or squares that appears at the corners and sides of a selected object. You drag these handles to change the size of the object.) away from or toward the center of the picture while doing one of the following:
* To keep the center of the picture in the same place, hold down CTRL while you drag the sizing handle.
* To maintain the proportions of the picture, hold down SHIFT while you drag the sizing handle.
* To both keep the center of a picture in the same place and maintain its proportions, hold down CTRL+SHIFT while you drag the sizing handle.

Tip: You can also resize a picture to an exact height and width by selecting the picture and then entering the size that you want in the Shape Height and Shape Width boxes (Format tab, Size group).
5. To move the picture, drag it to the location that you want.

Note   Pictures that you insert in your chart are embedded in your chart and therefore increase the file size. To reduce the file size, you can link to a picture instead of inserting it. In the Insert Picture dialog box, click the picture that you want to insert, click the arrow next to Insert, and then click Link to File.

Save a Chart as a Chart Template

1. Click the chart that you want to save as a template.

Tip: This displays the Chart Tools, adding the Design, Layout, and Format tabs.
2. On the Design tab, in the Type group, click Save As Template.

3. In the Save in box, make sure that the Charts folder is selected.

Note: The Charts folder is typically selected by default. Chart templates that you save in this folder will automatically be added to the Templates folder in the Insert Chart and Change Chart Type dialog box so that you can select it when you create or update a chart.

4. In the File name box, type an appropriate name for the chart template.

Tell Your Story Visually with Eye Catching Charts in Microsoft Excel 2007

Product(s): Microsoft Excel 2007
Audience(s): Business Professional.
Date(s): Wednesday May 26, 2010, 1:00PM (EST) U.S.
Duration: 60 Minutes 

Event Overview:
Almost everyone has seen or worked with a chart at one time or another–charts illustrate data, relationships, or trends, graphically. Like the saying “a picture is worth a thousand words” charts are often a better tool for presenting information than hard-to-read numbers. This Webinar covers just about everything there is to know about charts. The dazzling charts you will be able to create after you finish this course will impress both you and your colleagues.

To Register: Click Here. Fee: $99.00

Microsoft Excel 2007: Clear a filter for a column.

To clear a filter for one column in a multicolumn range of cells or table, click the Filter button on the heading, and then click Clear Filter from <Column Name>.

Change the Chart Type of an Existing Chart

For most 2-D charts, you can change the chart type of the whole chart to give the chart a different look, or you can select a different chart type for any single data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.), which turns the chart into a combination chart.
For bubble charts and most 3-D charts, you can only change the chart type of the whole chart.

NOTE:   To complete this procedure, you must have an existing chart. For more information about how to create a chart, see Create a chart.

1. Do one of the following:
* To change the chart type of the whole chart, click the chart area (chart area: The entire chart and all its elements.) or plot area (plot area: In a 2-D chart, the area bounded by the axes, including all data series. In a 3-D chart, the area bounded by the axes, including the data series, category names, tick-mark labels, and axis titles.) of the chart to display the chart tools.
* To change the chart type of a data series, click that data series.

NOTE:    You can change the chart type of only one data series at a time. To change the chart type of more than one data series in the chart, repeat the steps of this procedure for each data series that you want to change.

Tip:   This displays the chart tools, adding the Design, Layout, and Format tabs.
2. On the Design tab, in the Type group, click Change Chart Type.
3. In the Change Chart Type dialog box, do one of the following:
* Click a chart type in the first box, and then click the chart subtype that you want to use in the second box.
* If you saved a chart type as a template, click Templates, and then click the chart template that you want to use in the second box.
Tip: If you use a specific chart type frequently when you create a chart, you may want to set that chart type as the default chart type. After you select the chart type and the chart subtype in the Change Chart Type dialog box, click Set as Default Chart.

Rename a PivotTable or PivotChart Report

When you create a PivotTable or PivotChart report, Microsoft Office Excel assigns default names to each of these objects by using the following naming conventions: PivotTable1, PivotTable2, and so on; and Chart 1, Chart 2, and so on. However, you can change the name of each object to make it more meaningful to you.

PivotTable report

1. Click the PivotTable report.
2. On the Options tab, in the PivotTable group, click the PivotTable Name text box.
3. Type a new name.
4. Press ENTER.

PivotChart report

1. Click the PivotChart report.
2. On the Layout tab, in the Properties group, click the Chart Name text box.
3. Type a new name.
4. Press ENTER.

Create a formula in a PivotTable report

NOTE: You cannot create formulas in a PivotTable report that is connected to an OLAP (OLAP: A database technology that has been optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables.) data source.

1. Decide whether you want a calculated field or a calculated item within a field.
* Use a calculated field when you want to use the data from another field in your formula.
* Use a calculated item when you want your formula to use data from one or more specific items (item: A subcategory of a field in PivotTable and PivotChart reports. For instance, the field “Month” could have items such as “January,” “February,” and so on.) within a field.
2. Do one of the following.

Add a calculated field
1. Click the PivotTable report.
2. On the Options tab, in the Tools group, click Formulas, and then click Calculated Field.
3. In the Name box, type a name for the field.
4. In the Formula box, enter the formula for the field.

To use the data from another field in the formula, click the field in the Fields box, and then click Insert Field. For example, to calculate a 15% commission on each value in the Sales field, you could enter = Sales * 15%.
5. Click Add.

Add a calculated item to a field
1. If items in the field are grouped, on the Options tab, in the Group group, click Ungroup.
2. Click the field where you want to add the calculated item.
3. On the Options tab, in the Tools group, click Formulas, and then click Calculated Item.
4. In the Name box, type a name for the calculated item.
5. In the Formula box, enter the formula for the item.

To use the data from an item in the formula, click the item in the Items list, and then click Insert Item (the item must be from the same field as the calculated item).
6. Click Add.
7. If you ungrouped items in step 1, regroup them if you want. For more information, see Group items in a PivotTable field

For calculated items, you can enter different formulas cell by cell.

For example, if a calculated item named OrangeCounty has a formula of =Oranges * .25 across all months, you can change the formula to =Oranges *.5 for June, July, and August.

Do the following:

1. Click a cell for which you want to change the formula.

To change the formula for several cells, hold down CTRL and click the additional cells.
2. In the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.), type the changes to the formula.

If you have multiple calculated items or formulas, adjust the order of calculation by doing the following:

1. Click the PivotTable report.
2. On the Options tab, in the Tools group, click Formulas, and then click Solve Order.
3. Click a formula, and then click Move Up or Move Down.
4. Continue until the formulas are in the order that you want them to be calculated.

Creating Your First Budget Using Excel 2007

Product(s): Microsoft Excel 2007
Audience(s): Business Professional.
Date(s): Tuesday, January 5, 2010, 11:00AM (EST) U.S.
Duration: 60 Minutes

Event Overview:

Are you wondering where your money is going? Let Excel help you stay on track by creating a budget. Whether it is for home or work Excel has the right tools to keep you on budget. A budget helps you compare your income with your expenses and plan for long range financial goals.

Topics covered include track your income and expenses, create a spreadsheet, build formulas, and test your new budget.
* Budget Overview
* Setup of worksheet
* Build Formulas
* Test Budget

About the Presenter: With 8+ years of experience teaching the Microsoft Office Suite, David Thibault, Director of Education for KnowledgeWave Training in South Burlington, VT uses his upbeat, high energy teaching style in the classroom and online.  David has led corporate implementations of new software and written courseware for all applications in the Office Suite and 3rd party software.  He continues to specialize in classroom instruction as well as delivers corporate and open enrollment webinars.  His years in the field managing consulting projects have ranged from rollouts to upgrades and mentoring services, giving him the “real-world” knowledge that brings so much value to his classroom and online lessons.

To Register: Click Here.  Fee: $99.00