Blog Archives

Tom’s Tutorials For Excel: Toggling to Show or Hide Your Group and Outline Buttons

Tom’s Tutorials For Excel: Toggling to Show or Hide Your Group and Outline Buttons

When you have a worksheet with rows and/or columns that have been grouped…

…you can press your keyboard’s Ctrl+8 keys…

…to quickly and easily toggle to show or hide your Group and Outline buttons.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Finding the Minimum and Maximum Numbers in a Filtered List

Tom’s Tutorials For Excel: Finding the Minimum and Maximum Numbers in a Filtered List

You can use the SUBTOTAL function to look up the minimum or maximum number in a filtered list.

In the picture, the formula in cell B1 that returns Sue Flay’s minimum sales number is
=SUBTOTAL(5,B5:B100)

The formula in cell B2 that returns Sue Flay’s maximum sales number is
=SUBTOTAL(4,B5:B100)

The first argument for SUBTOTAL is Function_Num,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Getting Unique Items From a List

Tom’s Tutorials For Excel: Getting Unique Items From a List

In the following picture, you see a list of names, many of which are repeated 2, 3, or more times. Here’s how you can create a shorter list to show each unique item only once.

To create a list of unique names in column C based on the list of many repeated names in column A,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Filtering Automatically On Cell Entry

Tom’s Tutorials For Excel: Filtering Automatically On Cell Entry

Here’s a developer’s approach to make things easy for your users who need to filter a range of data, based on a value they enter in a criteria cell. A Worksheet_Change event is utilized on a worksheet that is designed for this purpose,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Filtering Dates

Tom’s Tutorials For Excel: Filtering Dates

When it comes to filtering dates, a little VBA goes a long way in dealing with the nemesis of seemingly countless different formats a date can be represented in Excel.

In the picture, an obnoxious myriad of dates is shown in the Date column to demonstrate how,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Filtering Columns

Tom’s Tutorials For Excel: Filtering Columns
Free and safe downloadable workbook to filter by columns

As you know, Excel only provides a built-in method of filtering for rows by individual columns.

If you want to filter columns by their row headers,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Showing or Hiding Specific AutoFilter DropDowns

Tom’s Tutorials For Excel: Showing or Hiding Specific AutoFilter DropDowns

You might want to AutoFilter a range of data, but only have the AutoFilter drop-downs be visible in the header row for some columns and not for others. This is not possible to accomplish manually, but it is possible with VBA.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Advanced Filter Calculated Criteria

Tom’s Tutorials For Excel: Advanced Filter Calculated Criteria

Advanced Filter is among Excel’s most powerful and versatile tools. This example shows how a formula can serve as the criterion for filtering a table, based on a separate list of criteria items.

In Picture #1,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Subtotaling Filtered Data

Tom’s Tutorials For Excel: Subtotaling Filtered Data

With the SUBTOTAL function you can perform mathematical operations on visible filtered data.
The SUBTOTAL function wants two pieces of information from you:
(1) The type of operation (SUM, AVERAGE, and so on) you want to perform.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,