MrExcel Publishing
Your One Stop for Excel Tips & Solutions
Microsoft MVP

Use Autofilter With A Pivot Table

April 12, 2021 - by Bill Jelen

Use Autofilter With A Pivot Table

Challenge: You’ve created a pivot table to summarize sales by customer. You now want to filter those results to show only the customers with sales between $20,000 and $30,000. The AutoFilter command is grayed out for pivot tables.

Solution: You can fool Excel into turning on the AutoFilter dropdowns by starting your selection one cell to the right of the pivot table headings. In Figure 67, select cell E4. Hold down the Shift key and press the left arrow key four times to select E4:A4.

continue reading »

More Reading

Auto-number Records And Columns In An Excel Database

Auto-number Records And Columns In An Excel Database »

April 9, 2021 - by Bill Jelen

You want to build formulas to automatically serially number records and column headers in a database to which AutoFilter is applied and in which selected columns are hidden.

Get An Array Of Unique Values From A List

Get An Array Of Unique Values From A List »

April 7, 2021 - by Bill Jelen

You want to extract all unique values from a column of text data that may contain several instances of a particular value. A procedure like this is useful when you need to populate a list box or combo box with unique values for user selection.

read more articles »

Featured Products

MrExcel 2021 – Unmasking Excel
MrExcel 2021 – Unmasking Excel

February 2021

This is a 5th edition of MrExcel XL. Updates for 2021 include: LAMBDA, LET, Power Query Fuzzy Match, Sort & Filter in Sheet View, Cut-out people, Save object as image, STOCKHISTORY, Wolfram Alpha Data Types, Custom Data Types from Power Query, Weather data types, bilingual spreadsheets, Performance improvements, Unhide multiple worksheets, Action pen, Collapsible task panes, LET function to re-use calculations, store formulas using LAMBDA, Recursive LAMBDA, Branching LAMBDA, Lambda to return a picture, Excel function quick reference.

Introduction to Financial Modelling
Introduction to Financial Modelling

October 2019

With over 50 examples and an extended case study that creates a simple financial model from scratch to highlight the key concepts, this is a "hands on" book, focused on working with Excel more efficiently and effectively.

Excel Custom Functions
Excel Custom Functions Straight to the Point

July 2019

Excel Custom Functions can be used like any other native functions or user defined functions in Excel. This book shows the process of creating Excel Custom Functions.

Excel Data Cleansing Straight To The Point
Excel Data Cleansing Straight To The Point

July 2019

This Straight to the Point guide provides an introduction to data cleansing, which also goes by names such as data munging and data wrangling. Whatever the name, it basically means doing what needs to be done to make data useful and trustworthy.

see more products »