Download Support Republic's ultimate Excel compilation

by Bill Detwiler | Jun 20, 2003 7:00:00 AM

Tags: Microsoft Office, Support Republic, Microsoft Excel 2002, Bill Detwiler, Download Support Republic, Microsoft Excel

Takeaway: Help your users hone their Excel skills with this collection of Excel information. Our impressive download contains more than 100 pages of invaluable Excel 2000/2002 advice and six sample spreadsheets.

Instead of waiting until your Excel users come to you for advice, be proactive—help them improve their Excel skills with Support Republic's ultimate Excel compilation. This download contains more than 100 pages of Excel tips, tricks, and examples taken from 20 TechRepublic and TechProGuild articles and five TechRepublic downloads.

Include one or two of these tips in your next help desk newsletter, or e-mail this document to your Excel power users so they can share these articles with their coworkers. Regardless of the distribution method you choose, your Excel users will thank you for these helpful tips.

From pivot tables to cross-sheet math
Support Republic's ultimate Excel compilation contains a wealth of information on topics such as:

• Excel 2002's function search feature
• Finding and flagging a subset of Excel records
• Cross-sheet math
• Importing external data into Excel
• Auditing Excel error messages
• Creating custom Excel add-ins
• Saving multiple print ranges
• Creating and using Excel pivot tables and charts
• Calculating elapsed time

This hefty download also includes five of Support Republic's best Excel downloads:

• Copy | Paste | Special Values Excel add-in
• Sample scenarios spreadsheet
• Sample WORKDAY function spreadsheet
• Check box sample spreadsheet
• Random password generation spreadsheet

The following section is a sample of the information this download contains.

Excerpt from "Finding and flagging subsets of Excel records"
Recently, a consulting client called to ask the following Excel question: How can I find all the records that contain a particular word in a certain column?

My first reaction was to recommend Excel’s AutoFilter feature. Go to Data | Filter, and then click on the drop-down arrow for the appropriate column and choose the appropriate value—or in this case, the appropriate word. Excel will filter out all records except those that contain that word.

That solution would have worked except for one crucial detail: The column in question contained long text labels, not single-word entries. The client wanted to locate all the records where the key word appeared anywhere within those text labels. Furthermore, the key word might appear with an initial capital or in all lowercase letters.

Fortunately, Excel provides all the tools needed to ferret out the records that contain the key word. Here’s the solution I recommended.

Find, flag, and sort
We’ll use the Find function simply to “flag” the records that contain a particular key word. Once those records are identified, the client can copy those rows out to another worksheet.

The solution I proposed was to use the Find function, which takes the form
=Find(string_to_find,source)

In this case, the client wanted to find all the records in which the word “sensitive” appears in the label. If the word “sensitive” had been entered in all lowercase letters, we could have used the formula =Find(“sensitive”,A2), assuming the source labels start in cell A2.

Another wrinkle was the fact that, in some of those labels, “sensitive” was the first word in the label and was initial capped. To address that problem, we simply wrapped the Lower function around the cell reference. (The Lower function converts a given string to all lowercase letters.) So our formula took the form:
=Find("sensitive",Lower(A2))

Figure A shows what our sample sheet looked like after we copied our formula. For labels that do contain our keyword, the Find function returns an integer corresponding to the word’s position within the string. Notice that, for labels that do not contain the key word “sensitive,” the Find function returns the #VALUE! error message.


If the Find function locates an occurrence of the specified string, it returns an integer; otherwise, it returns an error message.

We could modify our formula so that it returns a null string instead of the error message. However, in this case, we count on the error message to identify records that we want to exclude from our search. So, we simply sort our records primarily by the Is Found column, and all of the records we want—the ones with integers in the Is Found column—get sorted to the top of the list.

By sorting our records by the Is Found column, all of the rows that contain our key word move to the top of the list.

Download Support Republic's ultimate Excel compilation
You can download Support Republic's ultimate Excel compilation by following this link or by clicking on the Downloads link in the navigation bar at the top of this page. TechRepublic has many useful documents, templates, and applications available for download, so be sure to check out our other offerings.

The 100-plus-page downloadable document is available both as a Microsoft Word document and as an Adobe PDF file. The six sample spreadsheets are Excel 2000 files. To increase download speed, we've zipped these files together into one file. You'll need an unzip utility such as WinZip or PKZIP to expand the zipped file. You'll also need either Microsoft Word or Adobe Acrobat Reader to view each respective document and Microsoft Excel 2000 or higher to use the sample spreadsheets. You can download Adobe Acrobat Reader here.

Print/View all Posts Comments on this article

Awesome joe.brenchley@... | 06/20/03

Most interesting pj.chan | 06/22/03