Author: Debra Dalgleish

30 Excel Functions in 30 Days: 03 – TRIM

Icon30DayYesterday, in the 30XL30D challenge, we took a poke at the lazy brother-in-law function — AREAS. It’s not used much in the working world, but you saw how the 3 reference operators work, so I hope that was useful!

TRIM Function

For day 3 in the challenge, we’ll examine the TRIM function. In January, some people are trying to TRIM a few pounds, and my Excel Calorie Counter and Excel Weight Loss Tracker workbooks are popular.

Unfortunately, the TRIM function won’t help with the pounds, but can remove extra spaces from a text string.

So, let’s take a look at the TRIM information and examples, and if you have other tips or examples, please share them in the comments. And good luck with the calorie counting!

Function 03: TRIM

The TRIM function removes all the spaces in a text string, except for single spaces between words.

Trim00

How Could You Use TRIM?

The TRIM function can help with the cleanup of text that you’ve downloaded from a website, or imported from another application. The TRIM function:

  • Removes spaces from start and end of text string
  • Removes all except single spaces between words
  • Does NOT remove some special space characters copied from websites

TRIM Syntax

The TRIM function has the following syntax:

  • TRIM(text)
    • text is a cell reference or text string from which you want spaces removed.

TRIM Traps

The TRIM function only removes standard space characters from the text. If you copy text from a website, it might contain special non-breaking space characters, and the TRIM function will not remove those.

Example 1: Removes spaces from start and end of text string

You can use the TRIM function to remove all the space characters at the start and end of a text string. In the screenshot below, there are 2 extra spaces at the start and 2 at the end of the text in cell C5.

The TRIM function in cell C7 removes those 4 spaces.

=TRIM(C5)

Removes extra spaces from text string with TRIM Function
Removes extra spaces from text string with TRIM Function

Example 2: Removes all except single spaces between words

You can use the TRIM function to extra space characters between words in a text string. In the screenshot below, there are 3 extra spaces between the words in cell C5.

The TRIM function in cell C7 removes those extra spaces, as well as the 2 spaces at the start and 2 spaces at the end of the text string.

=TRIM(C5)

Trim02

Example 3: Does NOT remove some space characters

The TRIM function does NOT remove some space characters, such as a non-breaking space copied from a website. In the screenshot below, Cell C5 contains one non-breaking space, and that is not trimmed.

=TRIM(C5)

You can manually delete the non-breaking space character, or use the SUBSTITUTE function or a macro. You’ll see other ways to clean up your data during the 30 Excel Functions in 30 Days challenge.

Trim03

Download the TRIM Function File

To see the formulas used in today’s examples, you can download the TRIM function sample workbook. The file is zipped, and is in Excel 2007 file format.

Watch the TRIM Video

To see a demonstration of the examples in the TRIM function sample workbook, you can watch this short Excel video tutorial.

_____________

30 Excel Functions in 30 Days: 02 – AREAS

Icon30DayYesterday we started the 30XL30D challenge with the action-packed, fun-filled, EXACT function. It had several examples and ways to apply it in your own workbooks.

AREAS Function

Today we’ll investigate the AREAS function, and it’s a bit lighter in the usefulness department (to put it politely). It’s like the lazy brother-in-law, in a bad sitcom, who lies on your couch, and drinks your beer.

But, even a layabout can have a purpose. That lazy brother-in-law can serve as an example for your children, of how NOT to behave. As for the AREAS function, we can use it to see how the 3 different reference operators work, and how they affect the formula results.

So, let’s take a look at the AREAS information and examples, and if you have other examples, please share them in the comments. But don’t send me your brother-in-law!

Function 02: AREAS

The AREAS function returns the number of areas in a reference — an area is a range of contiguous cells or a single cell. The cells can be empty, or contain data – that has no effect on the count.

Areas00

How Could You Use AREAS?

The AREAS function doesn’t have many real-world uses, but it’s an interesting example of how the reference operators work. You can use the AREAS function to do the following:

  • Count the number of areas in a range
  • Count the number of intersections for multiple ranges
  • Calculate an area number for an INDEX function

AREAS Syntax

The AREAS function has the following syntax:

  • AREAS(reference)
    • reference can be a single cell or range, or can refer to multiple areas.

Reference Operators

When entering references, you can use any of the 3 reference operators:

: colon A1:B4 Range all cells between, and including, the two references
, comma A1, B2 Union combine multiple references into one
space A1 B3 Intersection cells common to the references

AREAS Traps

If you are using a comma in the AREAS function, to refer to multiple ranges or cells, add another set of parentheses.

=AREAS((F2,G2:H2))

Otherwise, the comma will be interpreted as a field separator, and you’ll get a “too many arguments” error.

Areas06

Example 1: Count the Areas in a Range

You can use the AREAS function with a simple range reference, and the count will be 1.

=AREAS(G2:H2)

Count the Areas in a Range with AREAS function
Count the Areas in a Range with AREAS function

Example 2: Count the Areas in Multiple References

You can use the AREAS function with multiple references, to get a total count of areas. Because a comma is used as the union operator, you’ll need to add an extra set of parentheses in the formula.

=AREAS((F2,G2:H2))

The two ranges in the reference are adjacent, but are counted as separate areas, so the formula result is 2.

Areas02

Example 3: Count the Areas in Overlapping References

Even if the references overlap, or one reference is completely within another, when using the comma as the union operator, each area will be counted separately.

=AREAS((F2,F2:H2))

The two references overlap, and F2 is completely within the F2:H2 range, but they are counted as separate areas, so the formula result is 2.

Areas03

Example 4: Count the Areas in Intersecting References

When you use the space character to create an intersection from the references, the intersection areas will be counted.

=AREAS(TESTREF01 TESTREF02)

The named range TESTREF01 is coloured blue and TESTREF02 is coloured purple. These ranges intersect at three points, outlined with bold borders, so the formula result is 3.

Areas04

Example 5: Calculate the Area Number for INDEX

The INDEX function, in Reference form, can use area number as its final argument.

INDEX(reference,row_num,column_num,area_num)

This example, based on an Excel newsgroup post by Leo Heuser, refers to a non-contiguous named range – TestBlock.

In the INDEX formula, TestBlock is the reference, and the AREAS function calculates the number of areas in the TestBlock range.

To get the value from TestBlock, row 5, column 1, last area, use this formula:

=INDEX(TestBlock,5,1,AREAS(TestBlock))

The last area is Day04, and the 5th value in Day04 is H05, which is the formula result.

Areas05

Download the AREAS Function File

To see the formulas used in today’s demo, you can download the AREAS function sample workbook. The file is zipped, and is in Excel 2007 file format.

Try to use each function in your own workbooks. Then, for extra brain-sticking power, teach a friend or co-worker how to use each function. When you explain it to someone else, you’ll remember it better.

Watch the AREAS Video

To see a demonstration of the examples in the AREAS function sample workbook, you can watch this Excel video tutorial.

_____________

Excel Advanced Filter Update

Here’s the final video update for this week — Automatically Copy Excel Data to Another Sheet. This video now shows the steps in Excel 2010 or Excel 2007, instead of Excel 2003.

To wrap up this week of updates, here are links to a few of my previous Excel Advanced Filter articles and posts. You probably know all the basics, but maybe you’ve missed a few of these tips and tricks.

Note: Remember — the 30 Excel Functions in 30 Days challenge starts January 2nd. See you then!

Watch the Excel 2010 Advance Filter Video

See how to automatically copy data to a different worksheet, with an Advanced Filter, in Excel 2010 or Excel 2007.

There is a full transcript after the video.

Video Transcript

In Excel, you can use an Advanced Filter to pull data from a table and put it somewhere else in the workbook, either on the same sheet or a different sheet.

In this example, we have a list of orders and we would like to pull the orders with the highest totals and put them on a different sheet in the workbook. I would like to get the date and the customer for each of the high priced orders.

Criteria Area

Back on the Orders sheet, I’ve started a criteria area here. I’m using total, which is the column where the order amount is in the main table. That’s the criteria heading, and below that I’m going to enter my criterion, which is greater than 1500.

I only want orders where the total is greater than 1500. I’m not putting dollar signs in here. It’s just the number that I wanted to check.

We want to pull orders onto a different worksheet. In this case, we have to start on this destination worksheet, and I want to stay away from the cells near the heading. So, I’m just going to click a cell somewhere in an empty area of the worksheet.

Start Filter

To start the filter on the ribbon, I’ll go to the Data tab and click Advanced. In the Advanced Filter dialog, I’m going to select Copy to Another Location.

The List range is my main table. So, I’ll click here, then go to the Orders sheet and select the entire table, including the headings. That’s entered as the list range.

My Criteria range, I’ll click in that cell. Go back to the Orders sheet where I created my criteria range and select the heading cell and the criteria cell below that.

For CopyTo, this is where I want the data to end up. I’ll click here and select my two heading cells on the TopOrders sheet and I’ll click OK, and there are the 3 orders where the total is higher than 1500.

If we look back, we can see 1, 2, 3 orders, and those have been copied automatically to the other worksheet using an Advanced Filter.

_________________

Excel Conditional Formatting Update

The holidays are a great time to catch up on tasks. I’ve updated another popular Excel video — Colour a Row Based on a Cell Value in Excel.

To continue the conditional formatting theme, here are a few articles that you might have missed, when they were originally posted.

Watch the Conditional Formatting Video

Watch this short video to see how to colour a row based on a cell value in Excel.

There is a full transcript following the video.

Video Transcript:

With Excel’s conditional formatting, you can easily highlight a cell if it’s over or under a certain value, or if it meets a value that you’ve set.

But in some cases, instead of just a single cell, you might like to highlight a whole row in a table, if one of the cells in that row is over a certain number or under.

In this case, we would like to highlight each row in this list if the number of units sold is greater than 75.

So to do that, I’m going to select all of the rows, all of the columns in each row. So I’ve selected from A2 down to D10.

On the Ribbon, on the Home tab, I’ll click Conditional Formatting, and none of these preset rules will do exactly what I want. So I’m going down to New Rule, and in here I’ll select a formula.

So I’m going to use a formula to determine how to color each row.

When I click that, there’s a spot where I can put the formula.

I want to, in each row, look at the value that’s in column B. So I’ll type =

And we want, from every column, we want to look at column B. So we have to lock that cell. We don’t want it to be relative, we want it to be absolute.

So type a $ to lock that in. And then B.

And we want, in this case, the active cell we can see is white, where the other cells are highlighted with blue.

We can see that, in the name box, A2 is showing up. So that’s the active cell, so the active row is 2. So I’m going to type 2 here.

We’re going to check what’s in B2 and see if it’s greater than 75. So that’s our test.

And if it is greater than 75, we want to format it. So I’ll click Format and I’ll choose a fill color, maybe a blue color and click OK, and click OK again.

And now, any row where the number of units is greater than 75, all four cells in that row are colored blue.

______________

Excel Data Validation Update

I’ve finally updated my Data Validation intro video, so it shows the steps for creating a drop down list in Excel 2010, instead of Excel 2003.

Note: These instructions apply to Excel 2007 too, in case you’re using that version.

Data Validation Drop-Down List
Data Validation Drop-Down List

Data Validation Articles

In honour of this momentous occasion, here are links to a few of my previous Excel Data Validation articles and posts.

You probably know all the basics, but maybe you’ve missed a few of these tips and tricks.

Show or Hide User Tips In Excel – AlexJ shows how to let users turn data validation messages on or off, by choosing TRUE or FALSE from a drop down list.

Dependent Data Validation From a Sorted List – Select an item in the first drop down list, and related items are shown in the second drop down list.

Limit the Total Amount Entered in Excel – use Data Validation to limit the total amount that users enter in a group of cells.

Select Multiple Items from Excel Data Validation List – instead of selecting just one item from a data validation drop down, you can select two or more.

Plan Your Party Seating with Excel – too late for Christmas dinner, but this might help with your New Year’s festivities.

Note: Remember to vote for the Excel functions that you’d like to learn more about during the 30 Excel Functions in 30 Days challenge, starting January 2nd.

Watch the Excel Drop Down List Video

The shiny new video is below, in case you’d like to see the steps for making a drop down list in an Excel worksheet.

________________

Combine Values for Excel VLOOKUP

Do vampires prefer a specific blood type? Type A? Type B? Type AB? Are you positive? During the holidays, they might drink glögg, or Cosmopolitans!

Anyway, Marsha probably isn’t a vampire, but she wants to choose A or AB when doing a VLOOKUP. Here’s how you can combine values for Excel VLOOKUP formulas.

Continue reading “Combine Values for Excel VLOOKUP”

2011 Challenge: 30 Excel Functions in 30 Days

Icon30Day Your great suggestions in the Improve Your Excel Skills comments got me thinking. What skills would I like to improve in 2011?

It’s hard to pick just one thing, but I’ll start with Excel functions. Even the functions that you use every day can have hidden talents, and pitfalls that you aren’t aware of. And there are so many functions, that you probably only use a fraction of them.

So, in January, let’s explore 30 Excel functions in 30 days. Yes, you’re right — there are 31 days in January. However, I’m being kind, and will give you the first day off, to recover from your hangover, and/or post-holiday exhaustion. 😉

CHOOSE Your Functions

For this challenge, we’ll stick to functions in the Text, Information and Lookup and Reference functions, listed below.

There are about 60 functions in the list, and we can only cover 30, so please vote for your favourites. If you can’t see the list below, click here to go to the form.

Deadline for voting is Wednesday, December 29, 2010, at 5 PM (Toronto time).

Share Your INFO

We’ll start the challenge on January 2nd, and go till January 31st. Please check the blog every day during the challenge, and add your tips and comments, or even a HYPERLINK. There’s no SUBSTITUTE for team work, to ensure we ADDRESS all AREAS of each function.

My mind ISBLANK now, and I can’t FIND any more puns, so I’ll sign off now, and let you CHOOSE your functions. Thanks!

Update: Thanks for voting before the December 29th deadline — votes are no longer being accepted.
___________

Use Excel Scroll Bar to Trim Christmas Tree

An Excel scroll bar can be used for practical (and sometimes boring) things, like testing the effect of price changes, or adjusting a chart’s date range.

But this is the festive season, so let’s use a scroll bar for something more, well, festive!

Trim the Tree

In this example, instead of accounting and finance, you’ll see how to use an Excel scroll bar to decorate a Christmas tree, without macros.

Unfortunately, this Excel file can’t make hot chocolate or eggnog, so you’ll need to provide your own.

Useful Excel Features

It’s not just for the holiday season though — the sample file has useful features that you can adapt to other workbooks too:

  • Scroll bar lets users change a number quickly and easily
  • A text box that displays a changing message based on VLOOKUP formula
  • conditional formatting shows hidden cells when target number is reached
  • named ranges make it easy to work with specific cells
Use Excel Scroll Bar to Trim Christmas Tree
Use Excel Scroll Bar to Trim Christmas Tree

Watch the Video

To see how the Excel Christmas tree trimming scroll bar works, you can watch this short Excel video.

Excel Scroll Bar Sample File and Instructions

For instructions on creating the Excel scroll bar file, and to download the sample file, go to my Contextures website: Excel Scroll Bar Christmas Tree Example.

Improve Your Microsoft Excel Skills

Someone emailed me this week, and asked how he could improve his Excel skills.

Here’s what I suggested:

Books: Read Excel books – there’s a list of my favourites on my Contextures site, and you can browse your local bookstore, or search in Amazon, to see what’s new

Blogs: Follow a few Excel blogs, to see what topics people are writing about. You might learn about new Excel features, or see helpful tips for familial features

Websites: Visit some of the Excel expert websites, for Excel tips, tricks, videos, and sample files. I highly recommend the Contextures website, but I might be biased. 😉

Videos: Another great way to improve your Excel skills is by watching videos. There are lots of videos on my Contextures site, and on my Contextures YouTube channel.

Experiment: And remember keep trying new things in your own Excel files! That’s my favourite way to learn. Just be sure to do your tests on a backup copy of your files, just in case things go horribly wrong!

Your Suggestions

What would you add to that list of ways to improve your Excel skills?