Excel tips and tutorials
There’s a built-in tool in Microsoft Excel that makes it easy to remove all the duplicate values from a list. You can remove duplicates based on the items in one column, or multiple columns.
Continue reading “How to Remove Duplicate Items in Excel List”
After you filter a list in Excel, it’s easy to select an copy a few of the visible cells only. After doing that, you can go to another worksheet, and paste just those cells, without any problems. Unfortunately, Excel is not so helpful if you want to copy cells from that other sheet, and paste them into just the visible rows in the filtered list. This is a decades old problem, and there’s no magic solution.
Continue reading “Excel Filtered List Paste Same Row Shortcut”
If you’ve got a long list of sales orders on an Excel worksheet, how can you send one sales rep’s orders to a different sheet? There are lots of creative ways to do that, but my favourite solution is to use an Excel Advanced Filter. Would you use that solution too, or something different? Continue reading “Send Data to Different Sheets in Excel Based on Criteria”
There are two types of AutoFilters in Excel – worksheet AutoFilters, and List (Table) AutoFilters. If you’re using Excel AutoFilter macros, be sure they’re designed for the type of Autofilters that are in your workbook.
In Excel, you can use the drop down arrows in the table headings, to sort or filter the data. In this example, we’ll filter for dates that fall within a specific date range. The video shows two ways to accomplish that task.
In one of my sample files, you can select criteria for an Advanced Filter with Slicers. There are 3 Slicers at the top of the sheet, and an output range below that. And now, AlexJ has created a compact version of that technique, with the Slicers hidden until you need them. (He’s on a mission to rid the world of space-hogging Slicers.)
To make it easy to filter for several different items, you create a list of those items on a worksheet. Then, filter your data based on that list, so you don’t have to check all the items manually each time.
In most cases, it’s best if you keep people away from your data in Excel. It’s too easy to mess things up! Instead, set up a worksheet where they can use Slicers to set filter criteria in Excel.
Then click a button, and a macro dumps the data onto the worksheet. In the screen shot below, you can see how it works – easy, quick, and safe!
Continue reading “Use Slicers to Set Filter Criteria in Excel”
A couple of days ago, I had problems while trying to run a simple macro in Excel 2013. Things didn’t go too well, and it took me quite a while to solve the problem. You can read the details below, and I hope it helps, if you ever run into a similar error.
Continue reading “Mysterious Excel 2013 Filter Problems”
Last week, I shared a couple of workarounds for pasting data into a filtered list. Today, see how to paste with shortcut in filtered Excel list.
Continue reading “Paste With Shortcut in Filtered Excel List”
