Contextures Blog

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.

• Click a Map To Filter Excel Data – set up an Excel workbook so you can click on a map of the USA, and see a list of fictional doctors in the state that you selected.
• Excel Advanced Filter Complex Criteria – see examples of criteria you can use when setting up an advanced filter
• Create New Sheets from Filtered List – a macro uses an Advanced Filter to create a separate sheet of orders for each sales rep visible in a filtered list

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.

_________________

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.

• Highlight Weekend Dates in Excel – To make it easier to keep track of weekends in Excel, you can use conditional formatting to highlight the rows where the date is a Saturday or Sunday.
• Excel Conditional Formatting – Data Bars – In Excel 2007 and Excel 2010, you can use conditional formatting to add data bars to cells. This makes it easy to visually compare the list of numbers, just as you would in a bar chart.
• Highlight Lottery Numbers with Conditional Formatting – If you win the big draw, you’ll be sure to notice, if you use Excel to highlight the winning numbers.

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.

______________