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 … Read the rest

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 … Read the rest

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 … Read the rest

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 Read the rest

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 … Read the rest

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 … Read the rest

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 … Read the rest

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 … Read the rest

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 … Read the rest

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 … Read the rest