Register for these webinars to get free tips and tricks for working in this platform.

MAY

Getting the Most out of Microsoft Excel 2010
Date: Wednesday, May 16, 2012
Time: 10:00am PDT / 1:00pm EDT /5:00pm GMT
Presenter: Andrew Reed, Senior Training Specialist, Microsoft Corporation
Explore how Excel 2010 takes advantage of a new, results-oriented user interface that provides easy access to powerful productivity tools, offers a larger workspace, and delivers faster performance. This presentation will demonstrate useful tips and tricks about new features and timesavers to help in your day-to-day work with Microsoft Excel.

Getting the Most out of Microsoft PowerPoint 2010
Date: Wednesday, May 23, 2012
Time: 10:00am PDT / 1:00pm EDT /5:00pm GMT
Presenter: Andrew Reed, Senior Training Specialist, Microsoft Corporation
Discover the synergy of working with Microsoft PowerPoint to present and deliver information to your audience. You will learn about new features and timesavers to help in your day-to-day work. This session is good for beginner, intermediate, or advanced users – everyone will learn something!

JUNE

Microsoft Office 2010 Cloud Access
Date: Wednesday, June 20, 2012
Time: 10:00am PDT / 1:00pm EDT /5:00pm GMT
Presenter: Andrew Reed, Senior Training Specialist, Microsoft Corporation
Discover how Microsoft Clouds services and products will provide you endless ways to work and collaborate – from anywhere, at any time, and on any device. This will include storage strategies, Microsoft Office System for productivity and insight into what cloud services mean to the typical user of PCs. This will be demonstrated using Microsoft Word, Microsoft SkyDrive (Microsoft Live), Office365 and other Cloud implementations.

JULY

Microsoft Office 2010 Application Content Sharing
Date: Wednesday, July 18, 2012
Time: 10:00am PDT / 1:00pm EDT /5:00pm GMT
Presenter: Andrew Reed, Senior Training Specialist, Microsoft Corporation
Learn the ways and approaches for sharing content, from simple cut, copy & paste to shared drives, and information from your PC to the internet and back. Learn the tools, formats and reasoning behind how Microsoft Office system shares and accesses content for productivity. This will be demonstrated using Microsoft Word, Microsoft Excel and Microsoft PowerPoint.

New Horizons Remote Classroom offers you the flexibility of taking courses from any remote location, with LIVE Dual Screen Video and LIVE audio participation integrated with our Classrooms and Instructors. So stay home and log-in - you'll remain an interactive student with your own designated seat and computer in our on-site classroom. View more >>>

Go to http://www.myremoteclassroom.com/
to receive 25% off your online class purchase! Ends April 30!

Here's a summary of just a few unique features of New Horizons Remote Classroom that other training centers fail to deliver:

• Live VIDEO with Dual Screen Views
  Rotate your view during your class between instructor view and classroom view! From your classroom view, you can see the on-site students taking the class with you in real-time.


• Live AUDIO with Headset: Included
  We will ship your own headset to you before class begins. This headset ensures you will be able to hear your instructor clearly! Free Shipping!


• Labs On Demand, Kits: Included
  Unlike other IT training centers, we do not charge a separate fee for Labs on Demand or your course kits. A one-time class price includes everything you'll need for your class, without additional hidden fees!

Quicklink Resources for Remote Classroom

• View Our Remote Classroom Demos on YouTube
• Go to the MyRemoteClassroom.com web portal to receive 25% off your course purchase! (use REMOTE25 promo code)
• View the Remote Classroom Online Schedule in PDF Format
• Contact us today for a personal demo

• Excel’s EDATE function doesn’t always give you the flexibility you need when you’re coming up with expiration dates. We’ll show you an alternative that might work better.
• When you need to connect between two locations, such as a main office and a second branch, a virtual private network (VPN) can do the job. We’ll discuss how to use hardware at each end of your VPN tunnel.
• Adding a drop-shadow to your website’s image can slow down your site’s load time and cause problems when you need to edit the image. We’ll show you how to use CSS styles for drop-shadows that work without these drawbacks.
  

Click here for details and instructions for each of these topics.

JULY

Tips & Tricks for Microsoft Office 2007, PowerPoint and Excel
Explore how Excel 2007 takes advantage of a new, results-oriented user interface that provides easy access to powerful productivity tools, offers a larger workspace, and delivers faster performance. See how to quickly create pivot Tables and Charts to better analyze you information, while still creating easy to read & use spreadsheets. Additionally, discover the synergy of working with Microsoft PowerPoint 2007 to present and deliver your information to your audience with presentation tools that allow you to paint a picture of what you need to convey.

• Presenter: Andy Reed, Senior Training Specialist, Microsoft Corporation
• Date: Wednesday, July 8, 2009
• Time: 10 am Pacific; 12 pm Central; 1 pm Eastern
• Register: http://bit.ly/Ur1Z

Creating Complex Documents with Microsoft Word 2007
Creating Complex document with Microsoft Word 2007 Get informative tips to help you create better Microsoft Word 2007 documents more easily than ever before, and learn why the less work you do, the better your Microsoft Office Word documents will be. Witness how Microsoft SharePoint Services enhances the collaborative process. See the options available to streamline the creation of your documents and learn how to share this with other Word users, 2007 & previous.

• Presenter: Andy Reed, Senior Training Specialist, Microsoft Corporation
• Date: Wednesday, July 22, 2009
• Time: 10 am Pacific; 12 pm Central; 1 pm Eastern
• Register: http://bit.ly/Ur1Z

AUGUST

Collaborating with Microsoft Office 2007 using Microsoft SharePoint Services
See how enhancements in Microsoft Windows SharePoint Services 3.0 and Microsoft Office 2007 make it easier than ever to share documents, track tasks, use e-mail efficiently and effectively, and share ideas and information. Discover the tools to create Team Sites, Document libraries, and meeting sites with Microsoft SharePoint Services.

• Presenter: Andy Reed, Senior Training Specialist, Microsoft Corporation
• Date: Wednesday, August 26, 2009
• Time: 10 am Pacific; 12 pm Central; 1 pm Eastern
• Register: http://bit.ly/Ur1Z

ENROLL in all New Horizons Webinars at: http://bit.ly/Ur1Z

We've added more courses to our Remote Classroom Schedule! To learn more about our courses and how New Horizons Remote Classroom works, just visit the portal @ http://www.LearnAtNewHorizons.com/RemoteClassroom.

We are happy to answer any questions you have, and as always, if you do not see a course listed on the schedule, just let us know! You can contact us directly or just fill out the form here. We look forward to hearing from you.

Our Excel article shows you which functions to use when you want to count data that you’ve already filtered. To view the graphics to the table references in this article, please view the pdf document located here.

If you’ve ever tried to count the number of rows in your data table, you might have felt frustrated when you filter the data and realize your row count remains the same. Even when you filter the data, your count might still include the entire data table — especially if you use the COUNT function. Instead, we’ll show you how the SUBTOTAL function can give you the accurate total you need. Your SUBTOTAL formula will update with your filtered data — whether you’re working with text or numeric data.

Cast out the COUNT function

The COUNT function comes in handy when you’re dealing with numeric data, but it doesn’t play well with filtered data. First, the COUNT function only recognizes numeric data. If you include text data in the COUNT function’s range, the formula won’t recognize the text data. If your data range only includes numeric data, the COUNT function includes every row — even rows hidden when you filter the data.

Even the COUNTA function, which recognizes both text and numeric data, still includes hidden filtered rows in its final tally.

To illustrate these functions’ failings, take a look at our comparison table in Figure A. Our data includes 12 months of expense and revenue data. We’ve filtered the data to show the five months with the highest expenses. So, logically, our row count should equal five. But even when we include different ranges — the entire data table, a column of text data only, and a column of numeric data only — none of the COUNT or COUNTA functions give us the correct result.

Adapt for Excel 2007

The SUBTOTAL function works the same way in Excel 2007 as it does in earlier versions. The only difference is that Excel 2007 allows up to 254 ref arguments (ranges to include in the SUBTOTAL formula) whereas earlier versions allow only 29 arguments.

Important:
Note that when you include the entire data table in your COUNT or COUNTA function’s range, the formula counts every cell. If you want to count the number of rows in your filtered data table, you should only include one column of the data range in your formula.

Get the right results with the SUBTOTAL function

Don’t despair! The SUBTOTAL function can give you the accurate row count you need for a filtered data table. The SUBTOTAL function follows this syntax: =SUBTOTAL(function_num, ref1, ref2, . . .) The ref1 and ref2 arguments represent any ranges or references you want to include in the subtotal. You can add up to 29 ref arguments, and they should include columns — not rows. The SUBTOTAL function is designed for columns, according to Microsoft.

The function_num argument represents the function you want Excel to use when it subtotals the data in your ref ranges. We’ve listed all of the available functions and their function_num equivalents in Table A.

Learn how hidden data factors into the equation

Table A includes function_num values that both include and exclude hidden values. You might assume that filtered data includes hidden data — after all, the AutoFilter does its job by hiding rows that don’t fit your chosen criteria. But Excel doesn’t consider filtered data “hidden” in this case. When it comes to the SUBTOTAL function, hidden data refers only to rows that you hide by choosing Format Row Hide from the menu bar (or right-clicking on a row number and choosing Hide from the shortcut menu).

So in most cases, it won’t matter which set of function_num values you use for filtered data. But if your data table does include rows you’ve hidden manually, pay attention to whether you want to include those hidden rows in your SUBTOTAL results.

Choose the right function_num value

For our purposes, we’ll need to use either the COUNT or the COUNTA function to count the number of rows in our filtered data. We’ve updated our comparison table to include SUBTOTAL formulas that use COUNT and SUBTOTAL formulas that use COUNTA.

When you use a range that includes column B, which contains numeric data, you can use COUNT with SUBTOTAL for an accurate row count. You can also use COUNTA in this case because COUNTA recognizes both numeric and text data. But when you use a range that includes column A, which contains text data, the COUNTA function_num argument gives you an accurate count. For text data, you can’t use the COUNT function.

For this reason, if you’re counting data that doesn’t contain consistent formatting (such as dates), the COUNTA function_num is your safest bet.

Watch your data flex

When you change the filter on your data range, watch your SUBTOTAL formula’s results update to match the new row count. While using the COUNT or COUNTA functions alone give you the wrong results, the SUBTOTAL function works.

To view the graphics to the table references in this article, please view the pdf document located here.

Business skills for the new world of work

In business today, productivity is key to your success. Whether that means setting up projects for success, forecasting and analyzing trends, or managing critical business information, it is vital that you have the skills to work at peak performance. You already know how to use Microsoft® Office System applications. New Horizons offers Microsoft Business Skills Series Courses to teach you how to use those applications to more efficiently manage, work with, and prioritize information to make better decisions.

Go to www.NewHorizons.com/ for information on courses that cover topics such as:

• 4002 Forecasting and Trend Analysis Using Microsoft Office Excel 2003
• 4004 Managing Critical Business Information Using Microsoft Office Access 2003
• 4008 Building Better Microsoft Office Word 2003 Documents In Less Time
  

Related Course

• Excel 2007 - Level 3

Visit http://www.LearnAtNewHorizons.com/RemoteClassroom to view the newly updated New Horizons Remote Classroom Schedule, Orientation Video, Online Training Resources and more!

Eliminate your commuting challenges with New Horizons Remote Classroom! Stay home and log-in - you'll remain an interactive student with your own designated seat and computer in our on-site classroom.

Remote Classroom offers students the flexibility of taking courses from a remote location, with LIVE video and audio participation integrated with our Classrooms & Instructors. This makes it even easier for you to get the course you want, when and where you need it.

New Horizons can enroll one student or an entire group of students in our online Remote Classroom courses, and virtually any course we provide through our Instructor-Led Classroom curriculum can be delivered to you through our Remote Classroom.

Want to learn more? Just submit your information via the short form located on the Remote Classroom Portal Page @ http://www.LearnAtNewHorizons.com/RemoteClassroom/index.html#contact and we'll get you the information you need to see if Remote Classroom fits your training needs.

APRIL

Introduction to Tips & Tricks for Microsoft Office 2007 (Level 100)

Get the most out of the Microsoft Office 2007 System. As someone beginning to use Microsoft Office 2007, you will learn about new features and timesavers to help you in your day-to-day work. This session is good for beginner, intermediate, or advanced users – everyone will learn something! The Microsoft Office System has evolved from a suite of personal productivity products to a more comprehensive and integrated system. This presentation will demonstrate useful tips and tricks for Outlook, Word, Excel and PowerPoint.

• Presenter: Andy Reed, Senior Training Specialist, Microsoft Corporation
• Date: Wednesday, April 15, 2009
• Time: 10 am Pacific; 12 pm Central; 1 pm Eastern
• Click here to register: www.newhorizons.com/webinars

Tips and Tricks for Maximizing Outlook 2007

Attend this webinar and witness how Microsoft Office Outlook 2007 delivers the functionality to provide better information sharing including publishing and sharing calendars, and exporting Outlook data. Also, see how Outlook 2007 can be configured using settings, trust, add-ins, and customized appearance and content. In this session, we also provide a better under standing of some of the terms used for communications and the capabilities; see Really Simple Syndication (RSS) versus XML, phishing, signatures, and other useful features of Outlook 2007

• Presenter: Andy Reed, Senior Training Specialist, Microsoft Corporation
• Date: Wednesday, April 22, 2009
• Time: 10 am Pacific; 12 pm Central; 1 pm Eastern
• Click here to register: www.newhorizons.com/webinars

MAY

Intermediate Tips & Tricks for Microsoft Office 2007 (Level 200)

Get even more out of the Microsoft Office 2007 System. As someone who has worked with the Microsoft Office system for some time, you will discover efficient ways to configure, customize and utilize Microsoft Word, Excel and Outlook to get the most out of Microsoft Office. The presentation will demonstrate useful tips and tricks for Microsoft Outlook, Word and Excel. In addition, you will see useful ways to interact between these Microsoft applications.

• Presenter: Andy Reed, Senior Training Specialist, Microsoft Corporation
• Date: Wednesday, May 6, 2009
• Time: 10 am Pacific; 12 pm Central; 1 pm Eastern
• Click here to register: www.newhorizons.com/webinars

Introducing Microsoft Windows Vista® for Microsoft Office 2007 Users (100)

This webinar introduces to those familiar with Microsoft Office the features of Microsoft Vista that best suits them. You will see the advantages of using Windows Vista and Office together including better file origination, starting programs, search, communication, and Internet navigation. Get tips & tricks to better share and find your files, discover the consistency of search capabilities throughout Windows Vista, and navigate the new features of Internet Explorer. From this session, you’ll get a number of tips to help you immediately benefit from installing Windows Vista into your Microsoft Office 2007 environment.

• Presenter: Andy Reed, Senior Training Specialist, Microsoft Corporation
• Date: Wednesday, May 20, 2009
• Time: 10 am Pacific; 12 pm Central; 1 pm Eastern
• Click here to register: www.newhorizons.com/webinars

Join us from the comfort of your own office… on the Web! Webinar events are quick and convenient.

New Horizons Webinars are a great opportunity for business decision-makers, training professionals, developers, engineers and project managers to see the real value these new technologies can bring to your organization. With new topics every month, there's something for everyone.

ENROLL in all Webinars at: www.newhorizons.com/webinars

Enter the New Horizons Power Up Your Skills $25,000 Training Sweepstakes for your chance to win the grand prize worth $10,000 of computer training classes.

Then play our Power Up scratch off game daily to win a free class instantly!

Arm yourself with the IT and computer skills you need to succeed in today's competitive job market and put the power back in your hands!

Enter today! http://newhorizons.promo.eprize.com/powerup/

Join us from the comfort of your own office… on the Web! Webinar events are quick and convenient.

New Horizons Webinars are a great opportunity for business decision-makers, training professionals, developers, engineers and project managers to see the real value these new technologies can bring to your organization. With new topics every month, there's something for everyone. Go to www.newhorizons.com/webinars to enroll in all events.

APRIL

Introduction to Tips & Tricks for Microsoft Office 2007 (Level 100)

Get the most out of the Microsoft Office 2007 System. As someone beginning to use Microsoft Office 2007, you will learn about new features and timesavers to help in your day-to-day work. This session is good for beginner, intermediate, or advanced users – everyone will learn something! The Microsoft Office System has evolved from a suite of personal productivity products to a more comprehensive and integrated system. This presentation will demonstrate useful tips and tricks for Outlook, Word, Excel and PowerPoint.

• Presenter: Andy Reed, Senior Training Specialist, Microsoft Corporation
• Date: Wednesday, April 15, 2009
• Time: 10 am Pacific; 12 pm Central; 1 pm Eastern
• Click here to register: www.newhorizons.com/webinars
  

Tips and Tricks for Maximizing Outlook 2007

Attend this webinar and witness how Microsoft Office Outlook 2007 delivers the functionality to provide better information sharing including publishing and sharing calendars, and exporting Outlook data. Also, see how Outlook 2007 can be configured using settings, trust, add-ins, and customized appearance and content. In this session, we also provide a better understanding of some of the terms used for communications and the capabilities; see Really Simple Syndication (RSS) versus XML, phishing, signatures, and other useful features of Outlook 2007.

• Presenter: Andy Reed, Senior Training Specialist, Microsoft Corporation
• Date: Wednesday, April 22, 2009
• Time: 10 am Pacific; 12 pm Central; 1 pm Eastern
• Click here to register: www.newhorizons.com/webinars

MAY

Intermediate Tips & Tricks for Microsoft Office 2007 (Level 200)

Get even more out of the Microsoft Office 2007 System. As someone who has worked with the Microsoft Office system for some time, you will discover efficient ways to configure, customize and utilize Microsoft Word, Excel and Outlook to get the most out of Microsoft Office. The presentation will demonstrate useful tips and tricks for Microsoft Outlook, Word and Excel. In addition, you will see useful ways to interact between these Microsoft applications.

• Presenter: Andy Reed, Senior Training Specialist, Microsoft Corporation
• Date: Wednesday, May 6, 2009
• Time: 10 am Pacific; 12 pm Central; 1 pm Eastern
• Click here to register: www.newhorizons.com/webinars

Introducing Microsoft Windows Vista® for Microsoft Office 2007 Users (100)

This webinar introduces to those familiar with Microsoft Office the features of Microsoft Vista that best suits them. You will see the advantages of using Windows Vista and Office together including better file origination, starting programs, search, communication, and Internet navigation. Get tips & tricks to better share and find your files, discover the consistency of search capabilities throughout Windows Vista, and navigate the new features of Internet Explorer. From this session, you’ll get a number of tips to help you immediately benefit from installing Windows Vista into your Microsoft Office 2007 environment.

• Presenter: Andy Reed, Senior Training Specialist, Microsoft Corporation
• Date: Wednesday, May 20, 2009
• Time: 10 am Pacific; 12 pm Central; 1 pm Eastern
• Click here to register: www.newhorizons.com/webinars

In the March 2009 issue of The Elevate Newsletter from New Horizons:

Microsoft Office Productivity
Three ways to protect your Excel data with confidence
Sometimes you need to keep your Excel data away from prying eyes. But you can protect your data in so many ways that the whole process gets confusing. We’ll break it down for you so your data never stays vulnerable. more >

Information Systems Protection
Twelve ways to harden your domain controllers and prevent security breaches
Domain controllers (DCs) hold a lot of valuable information about your Windows server, which means that you must secure them. We’ve compiled a few ways for you to keep intruders out of your network by strengthening your domain controllers. more >

Web Design & Development
Employ interactive elements to keep users on your site
Finally, nothing drives away online visitors like a boring website. We’ll introduce you to a few ideas for adding interactive elements to your site so visitors want to stay. more >

_____________________________________

New Horizons' goal is for our students to become more productive and successful in their daily activities. With this in mind, we have created Elevate, a newsletter specifically created for New Horizons students. In this free monthly publication you will read articles on how to put what you learned at New Horizons into action.

Simply click on the following link to access the March issue of Elevate.
http://www.newhorizons.com/uploadedFiles/ElevateNewsletter/Elevate_March2009.pdf

Good luck on your path of lifelong learning.

Join us from the comfort of your own office… on the Web! Webinar events are quick and convenient.

New Horizons Webinars are a great opportunity for business decision-makers, training professionals, developers, engineers and project managers to see the real value these new technologies can bring to your organization. With new topics every month, there's something for everyone.

Introduction to Tips & Tricks for Microsoft Office 2007 (Level 100)
Get the most out of the Microsoft Office 2007 System. Enroll Today!
As someone beginning to use Microsoft Office 2007, you will learn about new features and timesavers to help you in your day-to-day work. This session is good for beginner, intermediate, or advanced users – everyone will learn something! The Microsoft Office System has evolved from a suite of personal productivity products to a more comprehensive and integrated system. This presentation will demonstrate useful tips and tricks for Outlook, Word, Excel and PowerPoint.

• Presenter: Andy Reed, Senior Training Specialist, Microsoft Corporation
• Date: Wednesday, January 21, 2009
• Time: 10 am Pacific; 12 pm Central; 1 pm Eastern

Intermediate Tips & Tricks for Microsoft Office 2007 (Level 200)
Get even more out of the Microsoft Office 2007 System. Enroll Today!
As someone who has worked with the Microsoft Office system for some time, you will discover efficient ways to configure, customize and utilize Microsoft Word, Excel and Outlook to get the most out of Microsoft Office. The presentation will demonstrate useful tips and tricks for Microsoft Outlook, Word and Excel. In addition, you will see useful ways to interact between these Microsoft applications.

• Presenter: Andy Reed, Senior Training Specialist, Microsoft Corporation
• Date: Wednesday, February 11, 2009
• Time: 10 am Pacific; 12 pm Central; 1 pm Eastern

Collaborating with Microsoft SharePoint Service 2007 for Microsoft Office 2007
Enroll Today! Microsoft SharePoint provides a single, integrated location where employees can efficiently collaborate with team members, find organizational resources, search for experts and corporate information, manage content and workflow, and leverage business insight to make better-informed decisions. As a Microsoft Office 2007 worker you have more coming at you than ever before. Get the tools to take on these challenges and be more productive. Business productivity solutions from Microsoft can enable more efficient collaboration and help you find and share critical information faster. Learn Tips and Tricks on how to work together and exchange information with Microsoft Office SharePoint 2007.

• Presenter: Andy Reed, Senior Training Specialist, Microsoft Corporation
• Date: Wednesday, March 11, 2009
• Time: 10 am Pacific; 12 pm Central; 1 pm Eastern

ENROLL in all Webinars at: www.newhorizons.com/webinars

Planning a project is often easier said than done. If your staff is only in the office on weekdays, you’ll need to omit weekends when building your project schedule. There are many complex project management programs on the market that can make this task less daunting, but they don’t come cheap. Fortunately, Excel offers the only tools you’ll need — the WORKDAY and NETWORKDAYS functions.

Automate your project planning with a few simple functions

Install the necessary ToolPak
These functions are included in Excel’s Analysis ToolPak add-in. If the Analysis ToolPak isn’t enabled, the functions will return a #NAME? error.

To install the Analysis ToolPak:

1. Launch Excel and open a new workbook.
2. Choose Tools Add-Ins from the menu bar.
3. In the Add-Ins dialog box, select the Analysis ToolPak check box in the Add-Ins Available list box, and then click OK.

WORKDAY function syntax

The WORKDAY function uses the following syntax:

WORKDAY(start_date,days,holidays)

The start_date argument is the date to which you want to add workdays, and the days argument is the number of workdays you want to add. The holidays argument is optional; if there are any holidays you want to exclude from your workday calculation, just specify their dates as the holidays argument.

Create the sample worksheet
Let’s see how these functions can simplify your project management.

To set up the worksheet:

1. Enter the necessary text and formatting.
2. Apply Date number formatting to cells D3:E11 with the Format Cells dialog box, but leave those cells empty for now.

Calculate the due date
The value in the Due Date column should calculate the due date by adding the number of workdays specified in the Allotted Days column to the date specified in the Start Date column. With the WORKDAY function, this task will be easy.

To calculate the due date based on allotted days:

1. Enter 9/15/08 in cell D3 and press the [Tab] key to activate cell E3.
2. Type =WORKDAY(D3,C3) in cell E3 and press [Enter].

Analyze the formula’s results
The due date in cell E3 displays as 10/03/08. If you check your calendar, you’ll find that there are 14 workdays between 09/15/08 and 10/03/08. Notice that the start date, 09/15/08, is included as one of the 14 workdays; however, the due date, 10/03/08, is not. The WORKDAY function begins counting each day at 12:00 a.m. So, the six workdays our sample formula calculates fall between 12:00 a.m. 09/15/08 and 12:00 a.m. 10/03/08. Simply put, if you start the First Draft stage first thing in the morning on 09/15/08 and have 14 days to work on it, it will be due first thing in the morning on 10/03/08.

Fill in the remaining start dates
Let’s set up each remaining stage’s start date to match the preceding stage’s due date. By using a cell reference, the start dates update automatically if there are any changes to the preceding due dates.

To synchronize your start dates and due dates:

1. Select cell D4 and type =E3, or type an equals sign (=) and select cell E3.
2. Press [Ctrl][Enter] to save the formula without changing the active cell.
3. Double-click on the cell’s Fill handle to copy the formula down to the remaining Start Date cells.

To copy your WORKDAY formula to other cells:

1. Select cell E3.
2. Double-click on the cell’s Fill handle down to copy down the formula.

Since your WORKDAY formula contains relative references, the copied formulas update when you paste them in a new location.

Tip: In our example, you can not only plan each stage of a project before you’ve started it, but you can also revise your project schedule along the way. For instance, imagine that the Production Schedule stage took only four days as opposed to the five days you originally assigned. If you change the value from 4 to 5, all of the dates affected update automatically.

Exclude holidays from your calculation
At this point,our sample worksheet plots start dates and due dates that begin on 09/15/08 and end on 02/13/09. However, there are a lot of holidays that take place during that time period, including Thanksgiving and Christmas. Let’s account for the 11/20/08 and 12/25/08 holidays in each relevant project stage.

To add company holidays to your worksheet:

1. Select cell A13 and type Company Holidays.
2. Press [Enter], type Thanksgiving, and then press the [Tab] key.
3. Enter 11/20/08, press [Enter], and then type Christmas in cell A15.
4. Press [Tab] again and enter 12/25/08.
5. Select cells A13:B15, click on the Borders button’s dropdown list borders in the Formatting toolbar, and then choose Thick Box Border from the resulting palette.
6. Select cells B14:B15 and apply the same Date number format you applied to cells D3:E11.

Note: If you don’t want to put your holidays or other exceptions right in your worksheet, you can input the holiday argument as a date within quotes. For example, instead of =WORKDAY(D3,C3,B14), you can enter =WORKDAY(D3,C3,"11/20/08"

To exclude holidays from a time span:

1. Select cell E7 and click in the formula bar to place your insertion point directly before the closing parenthesis.
2. Type ,B14 in the formula bar and press [Enter] to accept the change.
3. Select cell E8 and click in the formula bar to place your insertion point directly before the closing parenthesis.
4. Enter a comma and select cell B15.
5. Press [Enter] to accept the holiday.

Note: If Excel tags a cell in which you’ve added a holiday to the WORKDAY
function with a green triangle in the upper-left corner, don’t be alarmed. Excel is letting you know that the formulas you AutoFilled in the Due Dates column are now inconsistent. If you click on the smart tag and choose Ignore Error from the
resulting shortcut menu, the tag disappears.

You can include as many holidays as you like, as long as you also include them in your holidays argument. In our example, we included two vacation days in cells A18:A19 in addition to the company holidays.

Notice that each time you add a holiday as a date for Excel to exclude from the workday count, the other dates in the worksheet update accordingly.

Note: If you’re entering your holiday argument with text (e.g., "10/12/08"), you can include more than one by putting the dates in curly braces ({}) and separating them with a comma. For instance, you can enter the formula =WORKDAY(D11,C11,{"8/22/08","8/23/08"}).

Count the workdays that fall between two dates
What if you know which dates you want to start and end your project, and you’d like to find out how many workdays you have to accomplish it? This is where the WORKDAY function’s relative, the NETWORKDAYS function, can work its magic.

Change the scenario
With the end and start dates already decided, you just determine the values in the Allotted Days column. The only difference in syntax between the WORKDAY function and the NETWORKDAYS function is that the NETWORKDAYS function’s second argument is end_date instead of days.

To find the number of allotted days:

1. Select cells D3:E11 and press [Ctrl]C to copy the values.
2. With these cells still selected, choose Edit Paste Special from the menu bar to open the Paste Special dialog box.
3. Select the Values option button in the Paste panel and click OK. Excel converts the date values from formula results to static values.
4. Select cells C3:C11 and press [Delete] to clear the values.
5. Select cell C3 and enter =NETWORKDAYS(D3,E3).
6. Press [Ctrl][Enter] to accept the formula without changing the selected cell.
7. Double-click on the cell’s Fill handle to copy down the formula.

You can use the same procedures for excluding holidays and vacation days from the allotted days in the NETWORKDAYS function as you did with the WORKDAY function.

Related Courses

• Project Management Fundamentals
• Excel 2003 - Level 2
• Excel 2003 - Level 3
• Excel 2007 - Level 2
• Excel 2007 - Level 3

Adapt for Excel 2007
To install the Analysis ToolPak in Excel 2007, you need to click the Office button and then click the Excel Options button. Select Add-Ins in the left panel. Choose Excel Add-ins from the Manage dropdown list and click Go. Then, you’ll see the familiar Add-Ins dialog box where you can install the add-in as you would in earlier versions.

The WORKDAY and NETWORKDAYS functions operate the same in Excel 2007 as they do in earlier versions. And in 2007, a helpful ToolTip including the function’s arguments pops up as you type your formula in the Formula bar.

Business skills for the new world of work
In business today, productivity is key to your success. Whether that means setting up projects for success, forecasting and analyzing trends, or managing critical business information, it is vital that you have the skills to work at peak performance. You already know how to use Microsoft® Office System applications. New Horizons offers Microsoft Business Skills Series Courses to teach you how to use those applications to more efficiently manage, work with, and prioritize information to make better decisions. Go to www.NewHorizons.com for information on courses that cover topics such as:

• 4004 Managing Critical Business Information Using Microsoft Office Access 2003
• 4007 Creating Effective Presentations Using Microsoft Office PowerPoint 2003
• 4008 Building Better Microsoft Office Word 2003 Documents In Less Time

Percentages interpret your data as smaller parts of a larger piece. This is why pie charts are often a popular way to demonstrate percentages. However, if you want to conserve space and still present an attractive, effective visual of your percentage data, Excel 2007 offers a great alternative.

On the Home ribbon, in the Styles panel, there’s a Conditional Formatting icon that opens a shortcut menu. In this menu, you’ll see a formatting option called Data Bars. When you select a data range and then apply data bars (in any of a wide range of colors), the bars visually demonstrate the percentage or number in comparison to the other numbers in the data range. If you widen or contract the column’s width, the data bars remain proportionate.

The best part about the data bars is that they take seconds to apply — but it looks like they took hours of careful formatting.

When you want to easily package your favorite macros and send them to a colleague or client, an add-in is the way to go. Once installed, the add-in provides all of the macros you included in your add-in on the new system. Excel has several add-ins that come with the application, but the ability to create your own can help you share specialized macros with others — and they don’t need to know how it works.

Unfortunately, Excel 2007 isn’t exactly add-in friendly. Keep reading if you’re frustrated with an add-in that worked like a charm in previous Excel versions, but now seems impossible to locate.

Install add-ins through the new Office button
Excel 2007 has a completely new interface so you may not know where to find the familiar Add-in Manager. The good news is that it’s there — it’s just hidden within the new Office button.

To install an add-in:

1. Click the Office button and then click the Excel Options button.
   
2. Click Add-ins in the left panel. Excel displays a list of your active and inactive add-ins.
   
3. Choose Excel Add-ins from the Manage dropdown list and click the Go button. Excel opens the familiar Add-ins dialog box from earlier versions.
   
4. Click the Browse button to open the Browse dialog box.
   
5. Navigate to the add-in you want to install, select it, and then click OK. The add-in displays in the Add-ins dialog box with its corresponding check box selected.
   
6. Click OK to return to your spreadsheet.


7. Go back to the Excel Options window and you’ll see the new add-in listed in the Active Application Add-ins section.
   

New file format: Excel 2007 uses a different file extension for add-ins. Instead of .xla, 2007 add-in files have the extension .xlam.

I can’t find my add-in!
Unfortunately, installing add-ins isn’t the hard part when it comes to 2007. When you need to run the macros you packaged into the add-in, that’s when things get tricky.

Locate add-ins that come with Excel
You can locate the Analysis Toolpak, the Lookup Wizard and other familiar add-ins based on what they accomplish. You’ll find these pre-packaged add-ins in one of two places: the Formulas ribbon’s Solutions area or the Data ribbon’s Analysis area.

Find custom add-ins
The most frustrating aspect of using add-ins in Excel 2007 is finding a way to run macros included in the add-ins you’ve created and installed.

The problem: When you produce an add-in for someone else, you often create a way for the user to quickly run the macros you’ve included, such as a new menu item or a custom toolbar button. Well, there are no menus in 2007 and the toolbars have morphed into ribbons. So what can you do?

In theory, when you install an add-in, Excel 2007 displays the Add-ins ribbon, which should include any custom menus or toolbars. We’ve found that this isn’t necessarily the case. Even after hours of trying to get the Add-ins ribbon to appear, it can still be elusive.

The most reliable way we’ve found to run a macro from your installed add-in is to add the macro to your Custom Quick Access Toolbar. This toolbar is the only toolbar you can customize in 2007 — there isn’t much you can do to change the ribbons unless you’re an XML expert.

To add a macro to your Quick Access Toolbar:

1. Click the Office button and then click the Excel Options button to open the Excel Options window again.


2. Choose Customize from the panel on the left-hand side of the window.
   
3. Select Macros from the Choose Commands From dropdown list.
   
4. Choose the macro you want to create a toolbar button for and click the Add button to move it to the list box on the right.
   
5. Select the newly added macro from the list box on the right and click Customize.
   
6. Select an icon to represent the macro on the Quick Access Toolbar and enter a new display name if necessary.
   
7. Click OK to return to the Excel Options window and click OK again to dismiss it.
   
8. You’ll immediately see a new toolbar button on the Quick Access Toolbar, and the name you designated displays as a ScreenTip.

Related Courses
• Excel 2000, 2002, 2003, 2007 & 2007 New Features
• 4002 Forecasting and Trend Analysis Using Microsoft Office Excel 2003
• 4003 Summarizing Microsoft Office Excel 2003 Data to Make Better Business Decision

Link : Elevate 12/07