Category Archives: Features

Copy Data to Another Worksheet with Advanced Filter

Dear Microsoft, please fix the Advanced Filter in Excel. Copying filtered data to another worksheet should be simple but it’s not. Fixing this would help out many of your beginning and intermediate users and some of us old dogs who seemingly haven’t learned the trick.

The Problem with Advanced Filter in Excel

I was reading a post on the Contextures Blog, which indicated that you could use the Advanced Filter feature in Excel to copy filtered data to another worksheet. This feature has the potential to shorten some of my VBA code that essentially does the same thing. I mean, using Excel to do the work is simpler and usually faster than writing the code yourself.

So I created a simple worksheet with dummy data and tried out the Advanced Filter feature. In the picture below my List Range and Criteria Range are on Sheet1 and I’m just about to Copy to another location: Sheet2.

Advanced Filter Copy to Another Sheet

However, when I click the OK button the following message pops up.

Advanced Filter Warning

The Trick to Copy Filtered Data to Another Worksheet

My problem was that I didn’t know the secret. You have to initiate the Advanced Filter from the worksheet you are going to copy to. Duh, stupid me. So here’s how I defeated my nemesis, the Advanced Filter.

  1. Activate sheet 2 (where the data goes)
  2. Choose Data > Advanced Filter
  3. Select the List Range on sheet 1
  4. Select the Criteria range on sheet 1
  5. Select the radio button: Copy to another location
  6. Select the Copy to range on sheet 2
  7. Click OK

Here is the resulting data copied to Sheet 2.

Advanced Filter Results

There is a short video from Debra Dalgleish on how this is done. I’m too frustrated with Microsoft to pull off a video without disdain in my voice.

The problem I’m having is that this “work around” has been required for quite some time, with no updating of the Excel software code by Microsoft. (The video link above is for Excel 2003.) And yet in the latest two versions of Excel, 2010 and 2011, we still have to do something, that seems to me to be, completely counter-intuitive and un-productive.

Advanced Filter with VBA

I turned on the macro recorder to document the code required to execute, what I’m calling a “work around.” I then copied the first macro, modified the code, and ran it again from sheet 1 and it worked just fine.

Advanced Filter VBA Code

The first macro: CopyToOtherSheet does two things. 1) Selects Sheet 2 because that is what Microsoft requires, then 2) uses the AdvancedFilter method of the Range property to FilterCopy the CriteriaRange to the CopyToRange, with no Unique records.

The second macro: FilterCopyToOtherSheets only does one thing. It does what Microsoft should have done long ago, and that’s to copy the filtered range (sheet 1) to the copy range (sheet 2) while your on sheet 1.

The only code I changed (highlighted in red) was to eliminate the selection of Sheet 2, and add Sheets(“Sheet2”). to the CopyToRange. You can run this macro from Sheet 1 and the Advanced Filter will copy the filtered data to Sheet 2 with no problems.

Of course with static ranges this is a very limited macro, but my point here is that the code change should be minimal and if Microsoft can spend several billion dollars to get Windows 7 into Nokia phones, this shouldn’t cost to much to fix.

Okay, I’m done ranting.

UPDATE

Here is the file.

Excel 2011, 2010, 2007 format.

Excel 2003 format.

Helpful Links

Excel’s Advanced Filter Help Online

Sorting with Custom Lists in Excel

Custom List SortEvery once in a while I come across a sorting problem where a column of data needs to be sorted in a manner not consistent with ascending or descending order. Thankfully, Excel allows the sort order to come from a Custom List.

As an example I have some data with Date, Name, and Type as column headings, where the Type data can be one of five values: Breakfast, Lunch, Dinner, Snacks, Exercise. I want the data sorted by Date, in ascending order, then by Type, in the order listed above, not in ascending order: Breakfast, Dinner, Exercise, Lunch, Snacks, which is how I get the data.

In years past I would have solved this problem by creating another column (TypeCode) with a VLOOKUP formula keying off the Type data and referencing a new table created (on Sheet 1) with the sort order I wanted, as depicted below.

Sort Data with VLOOKUP Column

TypeCode is a VLOOKUP formula value that gives me the sort order I want, but with an extra column in my data and another worksheet with the lookup table. Too much extra stuff for this little problem.

Create a Custom List

A Custom List can be created in Excel 2010 by choosing File > Options > Advanced > Edit Custom Lists. In Excel 2011 choose Excel > Preferences > Custom Lists. Click Add then type the list entries — press enter or return to separate entries — then click OK.

Create a Custom List in the Sort Dialog Box

In our example we’re going to add a Custom List from the Sort dialog box. I select a cell inside my data then choose Data > Sort to bring up the Sort dialog box. As you can see below, the first level is sorted by Date from Oldest to Newest. The second level column is Type where I select Custom List… from the Order drop-down box, which will bring up the Custom Lists dialog box.

Sort Dialog Box Custom List Order

Here I click Add, and type in my list: Breakfast, Lunch, Dinner, Snacks, Exercise then click OK.

Custom Lists dialog Box Add

You now see the new Custom List in the second level Order column of the Sort dialog box where you can now click OK to have Excel complete the sort.

Sort dialog box Order by Custom List

The data is now sorted by Date (oldest to newest) and by Type using the Custom List.

Data with Sort

Sorting with a Custom List makes things simpler when Excel doesn’t have a default sort order to fit your needs.

Open File As Read Only in Excel

To discourage users from making any changes to a macro file you can have the file recommend Read-Only when it’s opened.

Open as Read Only

To show this pop-up box when the file opens:

  • Choose File > Save As
  • Select Tools > General Options (on the Save As dialog box)
  • Check the box for Read-only recommended
  • Click OK
  • Name the file and click Save

General Save Options

If you select Yes to “Open as Read-only?” and try to save the file you get a pop-up screen asking you to give the workbook a new name in the Save As dialog box.

Read Only Save As

Of course, selecting No to “Open as Read-only?” will give any user the the ability to make changes to the file.

This is one Excel option to use to avoid unnecessary changes to any file.

Sorting Columns From Left To Right in Excel Causes Problems

Sort Left to Right ProblemYou may never have the opportunity to Sort columns of data from Left to Right, but beware of some apparent problems. A reader mentioned that when sorting from left to right the column width’s don’t change. I also found that formulas can be affected and for me that’s more problematic.

I was asked if Excel 2010 had fixed the problem with having column width’s stay the same when Sorting from Left to Right. I didn’t know so I constructed an example to find out. Note: Both Excel 2010 (Windows) and 2011 (Mac) work the same way for the examples that follow.

Column Width Problem

I used four column headings: ID, Date, Time, and Date Time with some random data, then formatted the cells and adjusted the column widths. I inserted a row above the data and entered the numbers 4, 2, 3, 1 above the headings and Sorted from Left to Right.

In the picture below, the worksheet on the left is Before Sort, and the worksheet on the right is After Sort. The Date Time and ID headings were swapped during the sort. However the column width did not change for column A or D, effectively rendering the Date Time unreadable.

Sort Right To Left Column Width med

Formula Problems in Sorting Left to Right

In these examples below there’s only one, simple formula. Essentially Date Time equals Date plus Time. (Date Time = Date + Time)

Formula Problem – Example 1

In this first example cells B3 and C3 have a blue dot and connected lines pointing to cell D3, which contains the formula =B3+C3 and is the active cell. The visual reference is provided by choosing Formulas > Trace Precedents while the active cell contains a formula.

Formula 1 Before

Formula 1 Before

I provided the same Left to Right Sort, swapping columns D and A during the process, and what to my wondering eyes did appear? A broken in formula for Date Time. (And eight tiny reindeer.) 🙂

The formula reference for cell A3 transformed to =#REF!+#REF! after the Sort was completed. Not the kind of thing you want happening.

Formula 1 After

Formula 1 After

Formula Problem – Example 2

In this second example I’ve corrected the formula in cell A3, which is now =B3+C3. This time I’ll Sort from Left to Right using Row 1 to sort from Smallest to Largest. The result will be columns A and D trading places. (Can I get a movie reference here? Don’t worry, I’m almost done.)

Formula 2 Before Sort

Formula 2 Before Sort

The spreadsheet below depicts what happened to the formula in cell D3, which is now =E3+F3 and obviously wrong again.

Formula 2 After Sort

Formula 2 After Sort

Formula Problem – Test Summary

With four columns, swapping one formula back and forth to see what happens could take some time. I compiled a small, sample summary table with 7 different sorts showing the Date Time formula before and after sorting the columns from left to right.

Summary Table Sort Left to Right Formulas

Col Move shows how the formula cell changed columns. In each case, after sorting the formula was incorrect. Some of the time there was one cell, either Date or Time, that was correct in the formula, but I didn’t find a combination that left the formula fully functional. I didn’t do an exhaustive combination so there may be some sorts that don’t break the formula, but I’ve made my point here.

Be very careful of formulas when sorting columns from left to right. And, oh yeah, you’ll probably be auto-fitting column widths as well.

Sorting Columns Left to Right in Excel

Sort Left to Right Theme PicOnce upon a time I had a master worksheet with data in predefined columns that was used for uploading data into a legacy system. Users would submit data in worksheets they developed, but oftentimes their columns didn’t always match that of the master worksheet.

So I used a little known option in the Sort feature of Excel to quickly rearrange their columns of data to match the master worksheet. This made it a snap to copy data from one worksheet to the other.

Default Sorting in Excel is top to bottom, but there’s another option to sort from left to right. Sort Left to Right Data BeforeHere’s a quick example on some bogus data so you can get a feel for how this feature works.

The column headers, from left to right are: Delta, Bravo, Alpha, Charlie. We want them to be in alphabetical order: Alpha, Bravo, Charlie, Delta.

Select a cell in the data range and choose Data > Sort. In the Sort dialog box, un-check the box for My data has headers.

Sort dialog box default settings

Click the Options button, then in the Sort Options dialog box select Sort left to right, and click OK.

Sort options dialog box

In the Sort dialog box, click the Sort by drop-down arrow and select Row 1. Make sure the Sort On selection is Values and Order selection is A to Z, then click OK.

Sort dialog box Sort by Row 1

The data is sorted left to right by using the first column.

Sort Left to Right Data After

Change the Example

Now let’s assume that we need the column order: Bravo, Delta, Charlie, Alpha. What to do? Well this is more like a real-life example that only takes a couple more steps to complete.

We need a blank row above the data, so right-click row 1 and select Insert from the pop-up menu. Enter 1 above Bravo, 2 above Delta, 3 above Charlie, and 4 above Alpha.

Sort Left to Right with row above

Now go through the same steps we did before, except the Row 1 sort Order will be Smallest to Largest.

Sort dialog box Sort Order Smallest to Largest

Once sorted you can delete the row above the header and we’re done. Excel has done all the heavy lifting.

Sort example 2 after

Now you can use this Sort feature to make your life easier.

Goal Seek in Excel

Formulas and Functions give you answers based upon input. If you know what a formula’s answer should be, but can’t derive the input value, Goal Seek is the tool for you.

I have a simple formula from my last post that takes one input, a Date in “m/d/yyyy” format, and returns the Day of the Year. This will tell me that Valentine’s Day — 14 Feb — is the 45th day of the year, or my birthday is day 311 this year.

Goal Seek Formula

The quickest way to find out what Date the 250th day of the year falls on is to use Goal Seek. For this example, here are the steps to take.

  • Choose Data > What-If-Analysis > Goal Seek…
    • In Excel for Mac and Excel 2003 choose Tools > Goal Seek…
  • Click the Set cell box and select cell B2, which has the formula =A2-DATE(YEAR(A2),1,0)
  • Click the To value box and type in 250, which is the outcome I want
  • Click the By changing cell box and select cell A2, the value that Goal Seek will change
  • Click OK

Goal Seek dialog box

Goal Seek searches for a solution and displays the following results.

Goal Seek After

Click OK to accept the changes, or click Cancel to restore the original value and leave the spreadsheet unchanged.

Note: Goal Seek works only with one variable input value.

Check out Goal Seek next time you know what a formula should be, but don’t have the time, inclination or smarts to figure it out.

Quick Access File List in Excel 2010

I just found a new feature in Excel 2010 that will save you some time. There’s an optional feature at the bottom of the File>Recent menu to Quickly access this number of Recent Workbooks.

When you already have a file open in Excel, clicking the File menu will default to the Info tab so you have to click the Recent tab to see the Recent Workbook files. Checking the option mentioned above throws the Recent Workbook files (you choose how many) into the left-pane of the File menu, so you can see the files listed when clicking the File menu.

My short video shows how this new feature works in Excel 2010.

Equation Editors in Excel 2010 and Word 2011

Equation toolbar menu itemThe Equation Editor is new in Excel 2010 and designed to create a mathematical equation as a graphical object. I didn’t find it in Excel 2011 for Mac, but ran across an article showing they put it inside Word 2011. How very strange.

Here I’ll review the Excel 2010 version of Equation Editor first, then briefly compare Mathematical Equations in Google Docs, and come back to the Word 2011 version of Equation Editor.

Excel 2010 Equation Editor

The Equation Editor in Excel 2010 is difficult to navigate at first. The second time I tried creating an equation it got slightly easier, and the third time with the same equation (shown below) I seemed to almost get the hang of it.

Simple Formula with Equation Editor

The trick I learned was to just type the equation in and let the Editor do its thing in rearranging what you’re typing. On the Equation Tools Ribbon are two helpful selections: Professional and Linear (shown below). Playing around with these helped me understand how the Editor can manipulate simple equations.

Having said that, advanced equations will take some time to figure out, but this editor should handle most anything you can throw at it.

Entering an Equation

To enter an equation with the Equation Editor go to the Insert tab on the Ribbon, and click Equation. This gives you a text box with the words “Type equation here.” You’ll also notice two additional menu tabs on the Ribbon: Equation Tools, which becomes active, and Drawing Tools.

Equation Tools Tab 1

Left Side of Equation Tools Ribbon

There are varied Symbols readily available for insertion on the Equation Tools Ribbon tab (shown above). And several drop-down menu items that expand to show even more symbols for things like: Fraction, Script, Radical, Integral, Large Operator, Bracket, Function, Accent, Limit and Log, Operator, and Matrix. (Shown below)

Equation Tools Tab 2

Right Side of Equation Tools Ribbon

Clicking the Equation drop-down arrow will reveal several predefined equations that can be inserted as starting point for your equation. This how I started my first equation, which did me no favors. I fared much better just typing in the formula and letting the Equation Editor move things around.

One Annoyance

One of the problems with the Equation Editor is that if you stray to far outside the equation, yet still inside the text box, the Equation Tools tab disappears and you’re stuck with the Drawing Tools tab only. Maddening!

Google Docs Mathematical Equations

I looked at the new Mathematical Equations in Google Docs and it seemed easier to learn, but is not nearly as powerful as the Equation Editor. For example, the equation I created above was done in a Google Docs document with Mathematical Equations. I wasn’t able to do a strikethrough for the min text, like this: min.

Google Docs Equation Imported to Excel

And there wasn’t a good way to export this object out of Google Docs.

Equation Editor in Word 2011 for Mac

The Equation Editor in Word 2011 for Mac looks to be identical to the one in Excel 2010. You start an equation from the Document Elements tab on the Ribbon, by clicking Equation or the drop-down menu arrow and selecting a built-in equation.

The Equation Tools tab immediately becomes visible and active after starting an equation. I created the same formula with relative ease.

Equation Editor Word 2011

I kind of like the feel of this Word 2011 version better than what’s in Excel 2010. Go figure.

Introduction to PowerPivot

In the year 2000, I began to use Microsoft Query to pull data from corporate client’s database tables, using their raw data to create PivotTable reports. Each table required a different query, each linked by using VLOOKUP formulas. And of course the data had to be restricted to Excel’s 65,536 rows.

Soon I was having clients write queries to get around using VLOOKUP formulas and started pulling data into Microsoft Access to get around the row limitations. But to get data into Microsoft Excel, the primary interface was Microsoft Query. I don’t know the history of this program but it served me well, even though it’s old, clunky, and not supported very well.

Enter PowerPivot

PowerPivot is a program that works with Excel 2010 to do some amazing things.

  • Import hundreds of millions of rows of data
  • Import data from multiple sources
  • Build relationships from imported data
  • Visualize the data with PivotTables and PivotCharts

If any of this sounds remotely interesting you can check it out for yourself on the PowerPivot website. Better yet, check out the video page where you can see an Overview (there’s three of them, the first one is enough to get the idea), check out how to download and install the PowerPivot program, and see the Importing Data video series.

Here’s a screen shot from the first video in that series.

PowerPivot Importing Data Video Series

And if that isn’t enough, you can even check out a demo.

Pretty awesome stuff.

A Sparkline Example in Excel 2010

Each week I download the over-under calories report from Lose It! and dump the data into a spreadsheet. I just created my first Sparkline graphic to show the last 7 days of this data.

For this example I’ll use an OFFSET Function inside a named range, which was created in my last couple of posts.

A Little Prep Work for a Dynamic Range

In this post I’ll use the formula:

=OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$B:$B)-7,0,7)

inside the named range: LastSeven, which will return the last 7 data points of column B data, dynamically. As I add more data, the range reference always returns the last 7 data points.

Sparklines are New in Excel 2010

Keep in mind that older versions of Excel won’t be able to see Sparklines, because they’re new in Excel 2010. There are three types of sparklines: Line, Column, and Win/Loss. In my example I’ll use Line, then add markers and show negative values differently, and finally add an axis to give it some definition.

Create a Dynamic Sparkline Graphic

To visually see the last seven data points, I’ve added a Sparkline graphic at the top of my spreadsheet. Here are steps used in my example to create a sparkline graphic in cell C1.

  • Select cell C1, or the range you want the sparkline to go
  • Select the Insert menu, then above Sparklines click Line
  • In the Data Range box, enter =LastSeven (don’t forget the (=) equals sign)
  • The Location Range box should have the correct cell address, if not select it now.
  • Click OK

Create Sparklines Dialog Box

Here’s what I got in cell C1 by doing all of that.

Sparkline Line

It’s a bit non-descriptive, so we’ll make some enhancements.

Make Changes to a Sparkline Graphic

By selecting a sparkline cell, a new Excel menu appears on the ribbon: Sparkline Tools. To modify the sparkline in our example, select Sparkline Tools, then above Show, select Markers and Negative Points.

Sparkline Show Settings

Above Group, click Axis then select Show Axis.

Toggle Sparkline Axis

Now the sparkline shows markers at each data point and all points below the axis line are red. That’s about as snappy as it gets. Below you can see the sparkline graphic beside the data range it represents, B263:B269.

Sparkline and Data

Now that I’ve set up a sparkline graphic with a dynamic range, each time I add data the sparkline graphic automatically updates.

Bonus Formulas

Bonus FormulasThe very top picture in this post has two cells, D1 and E1, that represents an evolution from a previous post. I’ve combined a text heading with a formula that’s inside a TEXT Function for formatting purposes. The information is self-explanatory. Here are the formulas:

  • D1 =”Last Date: ” &TEXT(OFFSET(A1,COUNTA(A:A)-1,0),”m/d”)
  • E1 =”7 Day Avg: ” &TEXT(AVERAGE(OFFSET(B1,COUNTA(B:B)-7,0,7)),”#.00″)

And of course, I’ll substitute my named range to make the second formula more readable.

  • E1 =”7 Day Avg: ” &TEXT(AVERAGE(LastSeven),”#.00″)
Related Posts Plugin for WordPress, Blogger...