| |
Dawn Bjork Buzbee
The Software Pro®
Microsoft Certified Application Specialist (MCAS) Instructor
Certified Microsoft Office Specialist Master Instructor
Certified Microsoft Office Expert
Certified Women's Business Enterprise (WBE)
(303) 699-6868
Dawn@SoftwarePro.com
|
| | Important data in Microsoft Excel is not just numbers and formulas but also text. Whether the text is a column heading or a critical data element, Excel includes many ways to improve the layout, appearance, and value of text. Formatting Text Cells If you are having problems sorting or filtering data lists, one of the reasons may be that some data in a column is formatted as text and some as numbers. In an ascending sort order, numbers and punctuation will sort before text. For instance, entries of 200, 1000, 33 would sort as 22, 333, 1111 if formatted as a number, and 1111, 22, 333 if formatted as text (left-to-right). You will get inconsistent results when a combination of formats exists. The Text format is also needed for data that includes a fixed number of places or leading zeros such as 0001234. One advantage in Excel is that you can format cells prior to entering data. This is true for text as well as numbers. To format entire columns, for instance, for text entry, highlight the column(s), then choose Format > Cells, choose the Number tab, and then select the Text format, finish with OK. Apply the Text format to existing cells as well for better sorting and filtering results (works best with the latest versions of Excel). When Formatting Doesn't WorkTo change a single entry to a text format, type an apostrophe (') in front of the data. Combining Two or More Cells Existing data may not be formatted exactly the way you need. For example, if employee names are in 2 columns, Last Name and First Name, but you want to display a full name for reporting as in Last Name, First Name (Smith, Joe) there are several ways to do this. In the example below, Column B is Last Name and Column C is First Name. To create the Full Name in Column D, the formula would be similar to (either example works): =CONCATENATE(B2,", ",C2) <----- CONCATENATE is a function that "combines"; additional text like the comma are enclosed by quotation marks. =B2 & ", " & C2 <----- the & also combines the values in cells B2 and C2. Another Use of the Ampersand (&) in Excel and Access In the above example, the ampersand (&) is a character that is used to combine or add multiple entries. In Excel as well as Microsoft Access, the & is also part of special codes which means that a single ampersand won't work in most text entries (the result is _ instead). To use an ampersand in a header, footer, or label in Excel or Access, type 2 ampersands, such as TIPS && TRICKS. Neat trick! Wrapping Text For multiple line entries of labels, comments, notes, or descriptions in Microsoft Excel, select the cells (or an entire row or column), choose Format > Cells, pick the Alignment Tab, and check the option for Wrap Text, finish with OK. The column width will determine how many lines are needed to display the text. Choose this over multiple rows for lengthy text entries for greater readability and for any lists of data. Once text is formatted to enable wrapping, you can further control the layout by pressing [Alt] + [Enter] to force a line break within the same cell. Try these tips, tricks, and shortcuts to master your text entry in Microsoft Excel. © Dawn Bjork Buzbee
Dawn Bjork Buzbee
is The Software Pro® and a certified Microsoft Office Expert,
Certified Microsoft Applications Specialist (MCAS) Instructor, and a certified
Microsoft Office Specialist Master Instructor. Dawn shares smart and easy ways
to effectively use software through her work as a software speaker, trainer, and
consultant. Discover more tips, tricks, tools, and techniques
at
www.SoftwarePro.com.
This article and
more can be reprinted at no charge in your publications and website with
copyright and attribution. >>Learn
more about how easy it is to share this useful information.
More Microsoft Excel Tips, Tricks & Handouts Too!
More Smart & Easy Software Tips, Tricks &
Techniques for You
<<Back to Articles
| |
To change a single entry to a text format, type an apostrophe (') in front of the data.
|
| Insert the TRIM() function around formulas to remove extra spaces that might surround a text entry |
|
Tips, Tricks, Secrets
that you need to know now!
|
|
