The Letter 'X' - Issue 2 [June 2009]
To all of our customers and friends:
Our intent is to provide you with informative articles on technology and business, let you know what XSolutions is up to, and provide you with free and useful programs to make your computing more pleasurable. We hope you enjoy our newsletter and pass it on to your colleagues as well.
Featured in this issue we focus on one of the most widely used office program, Microsoft Excel. Excel is a spreadsheet application that is written for both Windows and Mac operating systems. It is a powerful tool although most are unaware of its full capabilities. There are many useful features that many users are not aware of. We will discuss some useful tips for designing spreadsheets, using text searching features, and exploring the flexibility and efficiency users can acquire when combining Excel with Microsoft SharePoint.
by Joseph Imperato Sr.
Microsoft Excel is the most popular spreadsheet application in the world and used by most businesses to track all sorts of information. In fact, users are more comfortable creating spreadsheet databases when the more powerful MS Access Database Application would better suite their needs. However, this trend will continue since people find Excel’s spreadsheet format easy to use — just open a spreadsheet and start typing. What could be easier?
Most users open a workbook, start typing data, and add a few rudimentary formulas creating a spreadsheet that meets their immediate need but leaves much to be desired for ongoing use. This “spaghetti” format is hard to follow, a nightmare to update, and visually unappealing.
The following tips will help you design better and more useful spreadsheets:
- Plan all spreadsheet applications. Any workbook that you’ll use on an ongoing basis deserves a few minutes of design planning. Sketch the design on a piece of paper, clearly noting the sheets you’ll use, the information they will contain, and how they relate to other parts of your application. Better yet, enter this schematic into the first worksheet tab in your workbook as a visual map for yourself or anyone else who needs to update your application in the future.
- Use a separate tab for each type of information. For instance, one tab can contain the raw data in list form; another tab will house specialized lists for drop down boxes such as data validation, and a summary tab will draw data using formulas from the other tabs displaying information as desired. This will give your workbook a logical framework and make future revisions easier.
- Learn to use Excel’s built-in functions. You don’t have to be a VBA programming expert to create powerful and useful Excel applications. Excel contains potent built-in formulas called functions. Using them appropriately will aid you in your sheet’s design and functionality.
- Use Excel’s built-in Macro Recorder to add simple automation to your workbooks.
- Master Excel’s secret weapon for quickly summarizing data — Pivot Tables. Pivot Tables are a special feature of Excel that provide quick, easy, and powerful ways to display and summarize information from Excel lists.
There are many books on the market to help you create and design powerful Excel applications. Just go to Amazon.com and search on Excel.
In future issues of The Letter 'X', we will review many of Excel’s powerful features and show you how to apply them in your own applications. Make sure you read my Excel article, “Breaking Up A Text String Using Excel” in this issue of The Letter 'X'!
by Joseph Imperato Sr.
Some projects require that you download data from other programs, sometimes in long text strings. Excel has functions that allow you to easily isolate different parts of the string and organize the data into columns. Using the techniques in this article, you can convert thousands of records in just a few minutes.
The Left, MID, and SEARCH functions are three of the most useful formulas in Excel. We will explore these formulas in depth and show you how to use them to pinpoint specific text in long strings and organize the data into neat columns and rows. Then you can simply append them to your Access tables or use them in your own Excel applications. So, let’s get started.
Suppose you downloaded thousands of records from a mainframe database that had the following format: PA4251234567-Acme Publishing Company
Where: the first character was a record type, the next four represented the budget center, the next seven characters a contract number, and finally anything after the dash represents the customer’s name.
How would you go about separating the information into fields? Let’s find out using our three Excel functions. Follow the steps below and use the above text string as a guide. Additionally, at the end of this article is a screenshot of an Excel sheet showing these formulas in action.
Step 1:
Open a new Excel workbook, copy and paste the data into column A, row 6 in a blank spreadsheet. Why row 6? Because, you should skip a few lines so you can place column headings, identifying information, etc. for future reference.
Step 2:
In Column B, row 6 use the LEFT function to isolate the first character. In the above example, it would be the letter “P”.
The format of the Left function is: =LEFT(text, num_char)
Where: TEXT is the text string that contains the characters you wish to isolate.
Num_Char is the number of characters you want to isolate starting from the left.
Therefore our first formula, entered into cell B6 of our conversion sheet is: =LEFT(A6,1).
The result will yield “P” in cell B6.
Step 3:
Now let’s extract the four character budget center using the MID function.
The format of the MID function is: =MID(text, start_num, num_char)
Where:
TEXT is the text string that contains the characters you wish to isolate.
Start_Num is the starting position of the block of characters you wish to select.
Num_Char is the number of characters you want to isolate starting from the left.
Our second formula, entered into cell C6 is: =MID(A6,2,4). The result is “A425” in cell C6.
The above formula isolates a block of text four characters long starting with the second position in the string.
Step 4:
We will use the MID function again to isolate the seven character contract number. However, it may not be as easy as we first think. What if there are contract numbers shorter or longer than the one in the first entry represented in our example above? To plan for this contingency we will need to use the SEARCH function to calculate the proper number of characters. SEARCH simply returns the position of the target text.
The format of the SEARCH function is: =SEARCH(target, in_text, start)
Where:
Target is the text you are searching for.
In_Text is the character string that contains the text you are looking for.
Start is the number of positions starting from the left from which to begin the search.
We will imbed the SEARCH function within the MID formula. Our third formula, entered in cell D6 is: =MID(A6,6,search("-",A6,1)-6). The result is “1234567” in cell D6.
The SEARCH formula isolates the position of the dash (-) within the text in cell A6 starting from the first character. The “-6” at the end tells Excel to select the six characters before the dash. Since we started at the sixth position, the result is a seven character selection.
Step 5:
To isolate the customer’s name, we will again use the MID and SEARCH functions together. This time, we want all characters after the dash. Here is the formula:
=MID(A6,search("-",A6,1)+1,100). This formula simply selects the next 100 characters after the position of the dash within the text string. Using 100 ensures that customer names will not be cut off.
Step 6:
Now that you have entered all of the functions in the first line of the sheet, simply highlight the B6 to E6 range, select copy, and paste the formulas to all of the remaining lines containing the text strings you want to format. Your data is now properly formatted.
Step 7:
Now, highlight the entire Excel sheet by selecting the grey button in the upper right corner (i.e. above the row number “1” and to the left of column “A”). The entire sheet should have a black background. Select COPY, PASTE SPECIAL, VALUES. Then select OK.
The results calculated by your formulas have now been converted to values. You may now import or copy the results into your Access tables or Excel Applications.
Below is a screenshot of an Excel spreadsheet showing the formulas:
Now you have a few options:
- You can add this data to an MS Access table in a much larger application and process it.
- Use this information within a separate Excel workbook you have created.
- Redisplay this information in a different configuration using the Ampersand operator (i.e. “&”), like this:
=D6&”, “&C6&”, “&E6
The result would be Contract#, Budget Center, and customer name as follows:
1234567, A425, Acme Publishing Company
Make sure you download the companion Excel workbook showing these formulas in action!
IF YOU USE EXCEL…SHAREPOINT MAY ALSO BE FOR YOU!
Organizations deal primarily with people. Whether keeping track of suppliers, vendors, customers or staff, updated and detailed records are the cornerstone of any successful business. Microsoft Excel is one of the top tools used by organizations to accomplish these and other tasks.
People use Excel for increasingly creative purposes. Excel sheets that contain valuable information tend to collect on people’s computers. They begin to act as mini-databases that include things like calculations, contact lists, invoices and other information necessary to the day to day operation of your organization. Realizing the potential, Microsoft bundled useful features that allow Excel and SharePoint to complement one another.
SharePoint’s “document workspaces” allows for the seamless file storage, collaboration, and management of documents created within the Microsoft Office suite. Organizations that utilize information stored in a spreadsheet such as payroll, account statements, inventories, contact lists, or schedules are often modified by more than one individual. To increase productivity and to make collaborative efforts more seamless, many organizations have begun using features of SharePoint that integrate Microsoft Excel spreadsheets. This capability has allowed organizations of all sizes to work smarter, faster, and with greater precision.
A brief demonstration will help illustrate these benefits. Suppose you have a list of employees that needs to be shared amongst your organization.
Assume that one of the entries needs to be modified, deleted, or a new record needs to be added. While the changes are simple enough to make using Excel, sharing the new data is not as easy. If one person makes any change, all of the other people now have records that are out of date. In order to share the updated list amongst the staff, emails with file attachments must be sent, workstations need to be manually updated, or files must be physically moved using things like flash drives. Even with five employees, making sure everyone has the same updated files can be a hassle. As organizations grow, email and sharing physical disks becomes an increasingly cumbersome process.
By creating the same contact list using SharePoint, information is securely available anywhere with internet access and it is displayed in a familiar form. If one user makes a modification to the list, changes are tracked (with different version numbers), and ALL individuals with access can work with the most updated list.
Most importantly, if you can use Excel, you will be at ease constructing the same list using SharePoint. For these purposes SharePoint lists operate in similar manner to Excel spreadsheets. Just imagine working with a spreadsheet in a web browser instead of in an office application. In addition, any work done on SharePoint can be exported to Excel with ease. With a SharePoint solution, every person in the organization can have access to either read or modify the contact list. Contact changes can be tracked and synchronized so you are always working with the latest version.
FREE DOWNLOAD – PrimoPDF Creator
The ability to convert your documents into PDF format from your desktop is essential to office productivity. To save on the cost of user licenses, some organizations require employees to scan hard copies of their documents into a multi-function device and email it to themselves as a PDF. From there, they must save it to their hard drives, rename it, and then email the PDF to their coworkers. How archaic!
We found a handy tool to do the job for you absolutely free. It is called PrimoPDF Creator and it installs itself as a printer on your PC. So, the next time when you need to convert a document into a PDF, just select PrimoPDF from the Print dialog box. It is fast, simple to use, and did I mention FREE!
FREE DOWNLOAD - TAME YOUR INBOX WITH XOBNI
Here at XSolutions we use Outlook as our contact and email system. Throughout the years, we've accumulated a large base of emails and contacts. However, one thing Outlook doesn't do well is analyze the wealth of information contained within this knowledge base. That is, until we found Xobni (inbox spelled backwards). Xobni allows you to quickly find important emails, increasing your reaction time to customer inquiries.
Here are some of the main features:
- Associate emails as conversations so you can eliminate time consuming searches
- Super fast email search
- Email analytics such as how long it takes you to respond to clients
- Find attachments fast
- It can even look at your calendar and create an automatic availability email for scheduling
All of these features have made Xobni an indispensable tool at XSolutions. Download this amazing application at www.xobni.com and follow the instructions. It's Free!
Thoughts, Comments, Concerns?
Monday - Friday 9am - 5pm:
Phone: 845.362.9675
Fax: 845.818.3676
Web: http://www.xsolutions.com
Email: Online Contact Form
XSolutions Consulting Services, LLC
PO Box 76
Thiells, NY 10970