Save Space With Excel On Demand Slicers

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.)

Continue reading “Save Space With Excel On Demand Slicers”

Use Slicers to Set Filter Criteria in Excel

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”

Mysterious Excel 2013 Filter Problems

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:

Continue reading “Mysterious Excel 2013 Filter Problems”

Paste With Shortcut in Filtered Excel List

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.
filtercopypaste02
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”

Problem Pasting in Excel Filtered 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.

problem pasting in filtered list

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?

filtercopypaste03

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.

problem pasting in excel filtered list

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.
____________

Top Ten Values in Filtered Rows

If I apply an AutoFilter to see the Top 10 Sunday sales in a list, why does Excel just show me the Top 2?

autofiltertop10filtered09

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.

autofiltertop10filtered01

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.

autofiltertop10filtered02

Add a Top 10 Filter

Next, add a filter on the Qty column, to keep only the Top 10 of those records.

autofiltertop10filtered03

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.

autofiltertop10filtered04

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

autofiltertop10filtered05

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.

autofiltertop10filtered06

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.

autofiltertop10filtered07

Now, filter the Day column for Sunday, and the amount in cell G4 changes to zero.

autofiltertop10filtered08

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:

=AGGREGATE(9,3,C6)

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.

_______________________

Ungroup Dates in Excel Filter Drop Down

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.

dategrouping01

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.

dategrouping02

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:

  1. On the Ribbon, click the File tab, then click Options
  2. Click the Advanced category
  3. Scroll down to the Display Options for This Workbook section
  4. Remove the check mark from Group Dates in the AutoFilter menu.
  5. Click OK to apply the setting change.

dategrouping03

Ungroup Dates in Filter Drop Down

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

_____________________