Excel Sheet - Update.

Save time reformatting by using Excel’s Fill function across worksheets

• Date: May 27th, 2008
• Blogger: Mary Ann Richardson

You have three worksheets in your workbook. They are all formatted the same; only the data is different. Each worksheet tracks the sales for all 12 months of the year for one of your three divisions. You’ve just made some changes to the font color and the background of the cell range B1:M1 in Sheet1, as shown below. You would like to copy that formatting to the other sheets. Follow these steps:

1. Select the range B1:M1 in Sheet 1.
2. Click Sheet1. Press and hold Shift and then click Sheet3. (All three sheets should be selected.)
3. Click the arrow of the Fill button in the Editing group of the Home tab. (in Word 2002/2003, go to Edit | Fill.)
4. Click Across Worksheets.

Click Formats and then click OK.

Right-click any worksheet tab, and select Ungroup Sheets.

---

Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Use an array to add time components in Excel

• Date: May 26th, 2008
• Blogger: Susan Harkins

I recently acquired a worksheet with time components entered separately. I have no idea why they did this, as doing so seems inefficient and unnecessary to me. Just the same, I had to work with it.

The first problem was getting Excel to recognize integer values as time values. Fortunately, that’s not so difficult. I just used the TIME function to add the hour and minute components using the formula

=SUM(TIME(hour,minute,second))

In this case, there are no seconds, so I used 0 for the seconds. After combining hours and minutes for each row, it was a simple step to add the individual times using this formula

=SUM(firsttime:lasttime)

Then it hit me — I didn’t need intermediate (or subtotal) formulas. An array would return the cumulative time value with just one formula. (An array formula performs multiple calculations on one or more sets of values.) The following array formula returned the same cumulative result:

=SUM(TIME(firsthour:lasthour,firstminute:lastminute,firstsecond:lastsecond))

Again, I used 0 for the seconds. When using the array formula, be sure to apply an appropriate time format. In my case, I applied the hh:mm:ss format. (Be sure to accommodate your regional time settings.)

Quickly add text to your Excel 2002/2003 charts

• Date: May 20th, 2008
• Blogger: Mary Ann Richardson

To add text outside your chart titles, you usually have to create a text box. But with Excel 2002/2003, you can just type it in. Follow these steps:

1. Select the chart you want to add text to.
2. Start typing the text. As you type, the text will appear in the formula bar.

3. Press Enter.
4. Move and format the resultant text box.

The text will be displayed in a text box that can be moved and formatted as required.

---

Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Prevent embedded charts from printing in Excel 2002/2003

• Date: May 13th, 2008
• Blogger: Mary Ann Richardson

You have a number of embedded charts in your worksheet, and you don’t want them to appear when you print it. With Excel 2002/2003, you don’t have to select your data first and print the selection before you print the worksheet to avoid printing the charts. You just have to disable printing for the charts. Follow these steps to disable printing for each chart in your worksheet:

1. Press Ctrl and click the chart to select its container.
2. Go to Format | Object.
3. In the Format Object dialog box, click the Properties tab.
4. Deselect the Print Object check box.

---

Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Deleting range names in Excel

• Date: May 12th, 2008
• Blogger: Susan Harkins

Even a simple worksheet can generate a huge pile of range names. As long as they’re necessary and active, name away! However, consider deleting inactive range names. They crowd the list and make you work harder than necessary to find valid names. In addition, they can be confusing to people sharing the worksheet.

Before you start deleting, you’ll need a comprehensive list of names to review. The Define Name dialog box displays only a few at a time (you must scroll to see all of the names). An easier way to view all of the names is to paste a list into a worksheet:

1. Select a cell in an out of the way place.
2. Press [F3].
3. Click Paste Link.

You’ll find viewing a pasted list easier than viewing names in the Define Name dialog box.

Once you’ve checked the list, you can begin deleting inactive ranges. To do so, press [Ctrl]+[F3], select the range, and then click Delete.

Use the new Excel 2007 WordArt feature for your Word documents

• Date: May 6th, 2008
• Blogger: Mary Ann Richardson

Office 2007 includes an enhanced version of WordArt. However, it is available only in Excel 2007 and PowerPoint 2007. Word 2007 uses the older version. But this doesn’t mean you can’t use Word Art objects created in Excel or PowerPoint in your Word 2007 documents. For example, say you want to use the new WordArt to create a company logo for your Word document header.

Follow these steps:

1. Open a blank workbook in Excel 2007.
2. Click on any cell.
3. Click the Insert tab.
4. In the Text group, click WordArt.
5. Click Fill-Accent 2, Warm Matte Bevel. (It’s in the middle of the fifth row.)
6. Type XMZ Technology.
7. Click the Format tab under Design Tools.
8. In the Shape Styles group, scroll to and click Moderate Effect, Dark 1.

9. Right-click the logo and select Copy.
10. Click where you want the logo to appear in your Word document.
11. On the Home tab, click the Paste button down arrow and then select Paste Special. Select Microsoft Graphic Office Object, if necessary.
12. Click OK. The logo will appear in your document.

If you later want to make changes to the logo, you will need to go back and edit it in Excel 2007.

---

Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Copy an Excel chart to the desired spot in a Word document

• Date: April 29th, 2008
• Blogger: Mary Ann Richardson

You want to place an Excel chart in the top-left corner of the first page of your Word document. However, you don’t want to spend time adjusting it so that it doesn’t interfere with the rest of the page formatting. One solution is to create a table cell as a placeholder for the chart in your Word document.

Follow these steps:

1. Open your Word document and insert a 2-inch high and 3-inch wide table in the top-left corner of your page.
2. With the cell selected, click the No Border tool to remove the borders from the cell.
3. Open the Excel workbook that contains the chart and select it.
4. Resize the chart object to 2 inches by 3 inches. (For Excel 2007, in the Chart Tools Format tab, enter the placeholder table cell’s height of 2 inches and width of 3 inches in the chart’s Height and Width boxes.)
5. Select the chart area and press Ctrl + C.
6. Click in the table cell in the Word document, choose Paste Special from the Edit menu, select a Picture option, and click OK. (In Word 2007, press Alt + Ctrl + V, choose Picture (PNG), and then click OK.)
7. Right-click the chart, choose Format Picture, and click the Layout tab. Under Wrapping Style, select In Line With Text. (In Word 2007, right-click the chart, choose Text Wrapping, and then click In Line With Text.)

The chart will appear in the top-left corner of the page as shown here:

---

Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Add data labels to your Excel bubble charts

• Date: April 22nd, 2008
• Blogger: Mary Ann Richardson

When you create a bubble chart in Excel, you do not select the labels, as Excel would not know what to do with them. Instead, you need to add the chart labels after you create the chart. Adding the x-axis and y-axis labels can be done in the usual way. However, Excel has no specific tools for adding individual data labels to each bubble. You will need to add each data label separately.

For example, say you have just created the following bubble chart from the range B2:D7.

Follow these steps to add the employee names as data labels to the chart:

1. Right-click the data series and select Add Data Labels.
2. Right-click one of the labels and select Format Data Labels.
3. Select Y Value and Center.
4. Move any labels that overlap.
5. Select the data labels and then click once on the label in the first bubble on the left.
6. Type = in the Formula bar.
7. Click A7. (A7 is the name of the employee whose current Salary is represented by the bubble.)
8. Press Enter.
9. Repeat Steps 5 through 8 to add the name of the employee whose salary is represented by the bubble.

The completed data labels are shown below.

---

Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Use Excel 2007’s Top/Bottom rules for quick data analysis

• Date: April 15th, 2008
• Blogger: Mary Ann Richardson

You can use Excel 2007’s data visualization feature to keep tabs on when the data in a worksheet attain a certain value. For example, to discover which regions have achieved above-average sales this quarter, follow these steps:

1. Select the cells you want to analyze.
2. Click the Home tab.
3. In the Styles group, click Conditional Formatting, then select Top/Bottom Rules.
4. Click Above Average, then click OK.

Now all cells with above-average sales values will appear marked in red. To find out who the top 10% of your performers are, follow these steps:

1. Select the columns of cells you want to analyze.
2. Click the Home tab.
3. In the Styles group, click Conditional Formatting and then click Top 10%.
4. Click the drop-down arrow in the With box and select Custom Format.
5. Click the Fill tab and select Yellow under Background Color.
6. Click OK twice.

Now all values in the upper 10% of the range appear highlighted in yellow. You can set an entire spreadsheet with conditional formatting rules and watch the results change each time you enter new data.

Miss an Excel tip?

Check out the Microsoft Excel archive, and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Use Excel’s conditional formatting to mark values that meet your target goals

• Date: April 8th, 2008
• Blogger: Mary Ann Richardson

Excel 2007’s data visualization feature automatically gives every cell in a range a color, icon, or data bar according to its value. If you are analyzing a small number of values, this makes it easy to see which ones met your target goals. But what if you are examining a list with 100 rows of data? In this case, you will want to use Excel’s conditional formatting feature to build rules for marking only the cells outside your target range of values.

For example, say you want to mark only those cells that have a value greater than 100 with an X. Follow these steps:

1. Select the range of data.
2. On the Home tab, in the Styles group, click Conditional Formatting, then click Icon Sets.
3. Click 3 Symbols (Uncircled).
4. Click Conditional Formatting, then click Highlight Cell Rules | Less Than.
5. In the Less Than dialog, enter 100.
6. Click the arrow in the With drop-down box and select Custom Format, then click OK twice.
7. Click Conditional Formatting, then click Manage Rules.
8. Click the Stop If True check box for the rule Cell Value <>
9. Click the Icon Set rule, then click the Edit Rule button.
10. In the Edit Formatting Rule dialog, click the Reverse Icon Order check box at the bottom of the dialog window.
11. Click the >= drop-down arrow of the first Value list and select >.
12. Click the Type drop-down arrow of the first Value list and select Number.
13. Click in the first Value text box and enter 100.
14. Click the Type drop-down arrow of the second Value list and select Number.
15. Click OK twice.

Now, an orange X denotes all values above 100. An exclamation point denotes values equal to 100, and values below 100 remain unformatted.

By: Amresh Anjan

Automatically generate and assign strong passwords in Windows XP

• Date: May 21st, 2008
• Author: Greg Shultz

Computer users consistently use very simplistic logic when creating passwords. For example, many of us choose meaningful words, personal dates, or a word commonly found in the dictionary because it makes the password easy to remember. These common practices cause us to sacrifice the security that passwords are intended to provide.

If you’re really at a loss when it comes to thinking of a strong password, you can let Windows XP create and assign a random password to your account. To let Windows XP generate your password, follow these steps. (Warning: Before you follow these steps, please be sure that you are paying careful attention and are ready to actually use a password that might not be as memorable as you’re accustomed to! Also, you cannot use this tip on a Windows Server domain.)

1. Open a Command Prompt window and type:

   net user username /random (username is your login account name)

2. Press [Enter]. Windows XP will randomly generate a secure password, as well as assign that strong password to your account. Windows XP will also display the strong password so you can remember it.

At your discretion, you may want to create a Password Reset Disk at this point. This disk will allow you to gain access to your computer in the event you forget your password. Here’s how to create the disk:

1. Open the Control Panel and double-click the User Accounts tool.
2. Click your account icon.
3. Select Prevent A Forgotten Password under Related Tasks.
4. Follow the instructions provided by the wizard.

Note: This tip applies to both Windows XP Home and Windows XP Professional systems in either a standalone or peer-to-peer workgroup configuration.

By: Amresh Anjan

How do I configure SQL mail in SQL Server 2000?

• Date: May 21st, 2008
• Author: Steven Warren

As a database administrator, I like automation. When it comes to my backups, I like to automate as much as possible. SQL Mail gives me the ability to know when my backups are successful and when they fail.

SQL Mail is a component of SQL Server that allows you to send mail. Some of its functions include the ability to send messages to an e-mail pager and to send results via the extended stored procedure (xp_sendmail).

SQL Mail allows you to send and receive e-mail by working side by side with a mail server. There are two services that handle SQL Mail with SQL Server 2000: MSSQLServer and SQLServerAgent. I am going to explain how to configure and take advantage of this hidden gem.

Setup

Before you configure SQL Mail, you will need to configure a mailbox, mail profile, and a Windows 2000 account to start SQL Server. If you are using Exchange, you need a domain account. If you are using a basic POP3/SMTP mail server, you need a local or domain account. For the purposes of this article, I will show you how to configure a POP3/SMTP mail server.

You will begin by creating an account on your domain that will be used to configure SQL Mail, as shown in Figure A.

In order to configure your Microsoft Outlook client, you first need to make sure you have Microsoft Outlook installed. You can install this client from the Microsoft Office CD. Once you have it installed, click Control Panel from the Start Menu, then double-click the Mail icon, as shown in Figure B.

Next, click Show Profiles, and then click Add, to add a new profile, as shown in Figure C.

You will now be prompted with a wizard to add a new e-mail account, as shown in Figure D. Once you complete this step with the appropriate information, you are ready to configure SQL Mail.

Next, you need to log in to Windows with the newly created account. Once you are logged in, your next step is to start your MSSQLServer service and SQLServerAgent service.

In order to configure your MSSQLServer account to run under this newly created account, open Enterprise Manager from Start | Programs | Microsoft SQL Server | Enterprise Manager. Next, right-click on your SQL Server and choose Properties, as shown in Figure E, then choose the Security tab. Under the Startup Service Account, choose This Account and type the name of the account you created for use with SQL Mail.

Now that you have configured the MSSQLServer service account, you will need to configure the SQLServerAgent service account. In order to do this, expand SQL Server and Management, then right-click on SQL Server Agent and choose Properties (Figure F). On the General Page, enter the Service Startup Account by choosing This Account and enter the account name and password you created to start your SQLServerAgent service.

In order to configure your Microsoft Outlook client, you first need to make sure you have Microsoft Outlook installed. You can install this client from the Microsoft Office CD. Once you have it installed, click Control Panel from the Start Menu, then double-click the Mail icon, as shown in Figure G.

Next, click Show Profiles, and then click Add, to add a new profile, as shown in Figure H.

You will now be prompted with a wizard to add a new e-mail account, as shown in Figure I. Once you complete this step with the appropriate information, you are ready to configure SQL Mail.

Next, we will show you how to Configure SQL Mail now that you have configured the Outlook client.

Configure SQL Mail

In order to configure SQL Mail, you must open the SQL Server Enterprise Manager from the Start menu, then explore your SQL Server and expand your Support Services folder, as shown in Figure J.

Now right-click on Support Services and choose Properties. From the dropdown menu, choose the profile you just created (Figure K).

If for some reason, you do not see the profile you created in the dropdown menu, you have configured your Outlook mail settings incorrectly. Please go back and check your work for something that you may have missed.

Click Test to verify that your settings work correctly (Figure L). The test starts and stops the MAPI profile you created.

Our next test is to create a SQL Server Operator and test the SQL Mail functionality. To create an Operator, expand SQL Server | Management | SQL Server Agent | Operators and highlight Operators, as shown in Figure M.

Next, right-click on Operators and choose New Operator (Figure N).

Now, type the name of the operator and the operator’s e-mail address for SQL Mail to use. Next, click Test to send a test e-mail (Figure O). A dialog box will display a message that you have sent your e-mail successfully.

Your next step is to open up Outlook and check your e-mail to see that you are receiving these messages, as shown in Figure P.

Now that you have configured SQL Mail, you can take advantage of the xp_sendmail stored procedure that allows you to send messages through T-SQL. In my example, I am going to send an e-mail that will tell me which version of SQL Server I currently have running. Figure Q shows how this would break out.

The parameters for using xp_sendmail are as follows:

xp_sendmail {[@recipients=] ‘recipients [;…n]’}

[,[@message=] ‘message’]

[,[@query=] ‘query’]

[,[@attachments=] ‘attachments [;…n]’]

[,[@copy_recipients=] ‘copy_recipients [;…n]’

[,[@blind_copy_recipients=] ‘blind_copy_recipients [;…n]’

[,[@subject=] ’subject’]

[,[@type=] ‘type’]

[,[@attach_results=] ‘attach_value’]

[,[@no_output=] ‘output_value’]

[,[@no_header=] ‘header_value’]

[,[@width=] width]

[,[@separator=] ’separator’]

[,[@echo_error=] ‘echo_value’]

[,[@set_user=] ‘user’]

[,[@dbuse=] ‘database’]

With the above method, you can create triggers in your SQL code to notify administrators, including yourself, via e-mail if certain conditions occur. For example, you might set up notifications for long running queries, the deletion of certain tables, the rebuilding of indexes, backups failing, and a host of other database-related inquiries.

As you can see, SQL Mail can be very powerful. I have introduced you to the possibilities of SQL Mail and how to incorporate it into your infrastructure. In addition, I walked you through the process of configuring SQL Mail and testing it to make sure the necessary pieces work.

Your next step will be to continue testing with various options, and then begin using this solution in a production environment to automatically notify you of any SQL Server problems, issues, or disasters.

Stay tuned for how SQL Mail has changed in SQL Server 2005/2008 with all new tutorials.

By: Amresh Anjan