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!
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.
I also have a couple of announcements, before you read the mystery of the misbehaving macro:
- From Oct. 8-10, Jan Karel Pieterse is offering a 50% discount on his Formula auditing tool, RefTreeAnalyser. Enter the coupon code: MVP2014
- Mynda Treacy’s Excel Dashboard course is open for registration. Save 20% if you sign up by Oct. 16th. Enter DebraD in coupon code box.
To save time when printing reports, you can save filter and print setting in Custom Views. Unless, of course, your workbook contains a Named Excel table – you can’t use Custom Views in those files.
Continue reading “Show Excel Custom View Name”
Last week, I shared a couple of workarounds for pasting data into a filtered list. If you use a simple copy and paste, you could end up overwriting data in the hidden rows.
You can use those workarounds, if you are pasting into the filtered list, with data from another location. However, if you are copying and pasting data from one column to another, within the filtered list, there is a better solution.
Continue reading “Paste With Shortcut in Filtered Excel List”
It looks like a simple task – filter a list, so you an see the items you’re interested in. Then, copy data from one column, and paste it into another. But you can have a serious problem pasting in Excel filtered list.
Unfortunately, it can quickly turn into a big mess, and you’ll end up overwriting some of your data. In the screen shot below, I selected 5 cells in column B, and pasted them into the same rows in column E. However, only 2 of the dates were pasted.
Find the Missing Data
What happened to the other 3 dates that I pasted? When I cleared the filter on the list, I could see that all 5 dates were there. They weren’t pasted into the visible rows though – they were pasted into a block of 5 cells, starting with cell E6, where I pasted the data.
In those 5 cells, only two were chocolate chip orders, so those are the only two that I saw, when the list was filtered.
Yikes! I hit the Undo button, immediately, to get the original data back.
Do you have the same problem pasting in Excel filtered lists?
Why does Excel copy the individual, visible cells in a filtered list, then crush our dreams, by pasting them into a contiguous block?
I’d like the option to paste into visible rows, or have that automatically happen, if the list is filtered. Please and thanks!
Workarounds for Pasting
Even though I’ve asked nicely, Microsoft probably won’t be able to fix this problem pasting in excel filtered list by tomorrow. Or ever.
So, to avoid overwriting your data, be sure that you’re not pasting anywhere that rows are hidden. Here are a couple of workarounds that I use:
- You could clear any filters, and sort by a column that will bring the rows of interest together. Then do the copying and pasting, while no rows are hidden.
- To copy the selected items into the same rows, in a nearby column, use the Fill Right or Fill Left shortcut. I’ve made a video that shows how this works. Thanks to Khushnood Viccaji for sharing that tip in the comments below.
- For filters that are more complex, where you can’t do a simple filter to bring the rows together, you could insert a temporary column, and mark the rows, as shown in the screen shot below. Then, clear the filters, sort the temporary column, and copy and paste in the marked rows.
- The Paste Buddy add-in, from Excel Campus, has a Paste Visible feature that lets you paste the copied data into the visible cells only.
Video: Problem Pasting in Excel Filtered List
Watch this video to see why you have a problem pasting in Excel filtered list, and a couple of workarounds, to help you avoid the problem.
Download the Sample File
To download the sample file for this video, please visit the AutoFilter Tips page on my Contextures website. The file is in xlsx format, and does not contain macros.
If I apply an AutoFilter to see the Top 10 Sunday sales in a list, why does Excel just show me the Top 2?
In the screen shot below, you can see the first few rows of the table. The values are sorted highest to lowest, and the Sunday values are colored, so they’re easy to spot.
In the top 10 rows, there are 2 Sunday rows, and there are another 22 Sundays in the remaining rows. The rows are numbered in column D, to show each value’s overall position.
Filter By Day
To see only the Sunday records, I’ll filter the Day column. At the bottom of the screen, you can see that 24 of the records are for Sundays.
Add a Top 10 Filter
Next, add a filter on the Qty column, to keep only the Top 10 of those records.
Uh-oh! Instead of keeping the highest 10 Sundays, there are only 2 records left after the filter. It only kept the Sundays that are in the overall Top 10.
Add a New Column
I don’t want just the Sundays that are in the overall Top 10 – I want the Top 10 Sundays.
So, the built-in filter doesn’t do what I need, but I can add another column, to do the job.
- First, clear all the filters, so all the rows are visible.
- Then, add another column to the table, with a SUBTOTAL formula. Here’s the formula in cell E6:
- = – – SUBTOTAL(9,C6)
- NOTE: There are 2 minus signs after the equal sign. I included spaces, so they’re a little easier to see. The formula will work with or without the spaces.
- Copy the formula down to the last row of data
Apply the Filters
Now that the new column has been created, try the filter again.
- First, apply the Sunday filter in the Day column, to see the 24 Sunday records.
- Next, instead of filtering the Qty column, apply the Top 10 filter on the Test column.
Success! All 10 of the top Sunday results are shown in the filtered results.
How It Works
To see how this works, I’ll remove the filters from the list, and add a link to cell E6, where the SUBTOTAL formula with highest quantity is stored.
Row 6 is visible, and cell G4 shows the top amount of $1000.
Now, filter the Day column for Sunday, and the amount in cell G4 changes to zero.
The SUBTOTAL function only calculates a sum for visible rows, and Row 6 is no longer visible, so the current result is zero.
Only the Sunday rows will have a TEST value that is above zero, so when the Top 10 filter is applied on the TEST column, the Sunday rows have the highest values.
Of those Sunday values, only the Top 10 are visible, after the filter is applies.
Use the AGGREGATE Function
If you’re using Excel 2010 or later, you can use an AGGREGATE formula, instead of SUBTOTAL. The functions are similar, but AGGREGATE has many more options than there are in SUBTOTAL.
Here is the formula that you could use in cell E6:
You can read more about the AGGREGATE function here.
Download the Sample File
To see the Top 10 filters with SUBTOTAL and AGGREGATE, you can download the sample file from my website. On the Sample Files page, go to the Functions section, and look for FN0033 – Show Top 10 For Filtered Items.
The zipped file is in xlsx format, and does not contain macros.
By default, when you turn on an AutoFilter, dates are grouped in the drop down list. In the screen shot below, the dates have been rolled up to the years, 2013 and 2014.
Maybe you like the group dates feature, because it creates a short list of years, and you can drill down to find the date that you want.
Change the Setting
Sometimes you might prefer to see the full list of dates, instead of digging through each folder, to find the dates that you want. The good news is that you don’t have to put up with those grouped dates.
You can manually change an Option setting, to ungroup them, or use programming to turn the grouping on or off.
Manually Turn Date Grouping Off
Follow these steps to turn off the Date Grouping feature in the current workbook:
- On the Ribbon, click the File tab, then click Options
- Click the Advanced category
- Scroll down to the Display Options for This Workbook section
- Remove the check mark from Group Dates in the AutoFilter menu.
- Click OK to apply the setting change.
When you change the setting, it only applies to the current workbook. So, if you frequently change the grouping option, you can use programming to turn the grouping on or off. Put the following code into your Personal Workbook, or another file that is always open, then add a button for it on the Quick Access Toolbar.
This code toggles the date grouping setting — if the grouping is on, it turns it off, and if grouping is off, the code turns it on.
Sub ToggleFilterDateGroup() ActiveWindow.AutoFilterDateGrouping _ = Not ActiveWindow.AutoFilterDateGrouping End Sub
I use Excel tables in almost every file that I build, these days. They come with a default filter, and it’s easy to sort and filter the table’s data with those drop down lists, and clear them later. But did you know that you can clear Excel filters with a single click?