Features
Excel » Features »
I highly recommend you keep the original data and only copy the data excluding blank rows and paste to a […]
How to quickly select a cell range
Selecting cell ranges in Excel can sometimes be a real pain scrolling forever it seems. There is a quick and easy […]
How to filter using OR logic between columns [Formula]
The filter feature in Excel won't allow you to do OR logic between columns, however, you can if you allow […]
How to filter using OR logic between columns [Advanced Filter]
The built-in filter feature in Excel is a powerful tool, however, it won't allow you to filter with OR logic […]
Become more productive – Learn Excel Defined Tables
An Excel table allows you to easily sort, filter and sum values in a data set where values are related.
Discover Pivot Tables – Excel’s most powerful feature and also least known
A pivot table allows you to examine data more efficiently, it can summarize large amounts of data very quickly and is very easy to use.
Add new items to a drop down list automatically
A drop down list in excel prevents a user from entering an invalid value in a cell. Did you know that you […]
Copy filtered Excel tables [VBA]
Today I want to share some pretty useful macros. My first macro copies an excel defined table with vba. It is […]
Optimize pick path in a warehouse
As you probably already are aware of I have shown you earlier a vba macro I made that finds the […]
Learn Excel’s Subtotal feature
You can group rows or columns manually using the "Group" feature. That will tie a range of cells together so […]
Quickly change chart data source
In excel it is not easy to change a chart´s data source without manually changing the data source in chart […]
Scroll through a data set [VBA]
Sometimes you just want to show a small section of your data set, like in a dashboard or a chart. […]
Prevent duplicate records [Data Validation]
Debra Dalgleish demonstrated last week how to block duplicate entries in excel table. I made a comment and I thought I would share […]
Today let's learn how to create a simple pivot table calendar! The animated gif below shows you the pivot table […]
Use drop down lists to compare data series in an excel chart
The two drop down lists below the chart let´s you compare two data series from separate tables. It is easier […]
This post describes how to add a map (background picture) to a chart. The following animated picture shows a scatter […]
Highlight duplicates in a filtered Excel defined table
You can highlight duplicates in an excel defined table using conditional formatting. However, that won´t work if you only want […]
Sort a table with an array formula
The sorted table to the right is created with an array formula using the data in the table to the left. Array […]
Dynamic data labels in a chart
My fascination for charts and vba continues (see previous posts). This post demonstrates how to highlight a group of values […]
Sum security holdings monthly and yearly in a pivot table
Did you know that you can use a pivot table to summarize portfolio holdings at any point in time? If you trade […]
Run a Macro from a Drop Down list [VBA]
This article demonstrates how to execute a VBA macro using a drop down list. The drop down list contains multiple […]
Drop down list changes cell formatting
You can apply different conditional formatting formulas and formatting to a cell range, using a drop down list. Here is […]
Prevent users from entering overlapping date and time ranges
The picture below shows a table with data validation applied. If a user tries to enter an overlapping date range, […]
Find max unique value from a range that have duplicate numbers and blanks
Marc asks: How to find Min and Max numeric values in a range of cells that have duplicate numbers and […]
Today I am going to demonstrate how amazing pivot tables are! Take a look at this time sheet. You can expand […]
Analyze trends using pivot tables
Table of Contents Introduction to pivot tables Create pivot table Group data Analyze data (pivot table) Compare performance, year to […]
Cyril asks how to hide all columns of a range except columns whose header is found in specific cells. I am […]
How to use a Table name in Data Validation Lists and Conditional Formatting formulas
David Hager gave this valuable comment about how to reference a table name in conditional formatting formulas: =INDIRECT("Table1[Start]") Watch this video to […]
Copy excel table filter criteria [VBA]
Here is how to copy filter criteria from an excel table and use the same table filters on another table. […]
Quickly search a data set with many criteria
Mohsin Ali Raziq asks: I have problem, and o dont know how to solve it, i have data of almost […]
Use filtered table values in a drop down list [VBA]
I read a very interesting blog post about Using Custom Functions in Dynamic Ranges Gabhan Berry creates a user defined […]
Quickly create links to sheets, tables, pivot tables and named ranges in a workbook
A week ago I posted Create links to all sheets in a workbook and today I want to show you how […]
Count unique distinct values in a filtered table
A few days ago Debra Dalgleish described how to create a Line Between Dates in Filtered List. She modified a […]
Disable autofit column widths for all pivot tables in a sheet
I read this post Quick Trick: Resizing column widths in pivot tables on the Microsoft Excel blog. It is about Excel […]
Debra has a great post and video about normalizing data for excel pivot table. This post describes a macro that normalizes […]
Use a drop down list to extract and concatenate unique distinct values
Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that […]
Rodney Schmidt asks: I am a convenience store owner that is looking to make a spreadsheet formula. I want this […]
Use a drop down list to select company info in header (vba)
Aynsley Wall asks: I have a spreadsheet that I use for 3 different companies. What i would really like to […]
Apply data validation lists dynamically [VBA]
Table of Contents Applying Drop Down lists dynamically using an Excel Defined table Applying Drop Down lists dynamically (vba) Add […]
Dependent data validation lists in multiple rows
This post describes how to setup data validation lists in a column and dependent data validation lists in an adjacent […]
Rattan asks: In my workbook I have three worksheets; "Customer", "Vendor" and "Payment". In the Customer sheet I have a […]
Use a drop down list to search and return multiple values
Ainslie asks: I have multiple worksheets in an excel book. I have a drop down menu on the worksheet entitles […]
Change chart data range using a Drop Down List [VBA]
This blog post demonstrates how to quickly change chart data range. I have created a drop down list (form control) […]
Change pivot table data source using a drop down list
In this excel 2007 tutorial I am going to show you how to quickly change pivot table data source using […]
Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]
Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]
Use hyperlinks in a pivot table
Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise […]
Filter an Excel defined Table programmatically [VBA]
In this tutorial, I am going to demonstrate how to filter an Excel define Table through a VBA macro. How it […]
Filter unique distinct records using criteria
This blog post describes how to filter unique distinct records that meet a given condition in an Excel defined Table. This article […]
Extract unique distinct values based on a filtered Excel defined Table
This blog post demonstrates how to filter unique distinct values from an Excel table dynamically. When you change or add […]
Remove common records between two data sets
This article demonstrates how to filter records occurring in only one out of two Excel defined tables. It also shows […]
Count unique distinct values in an Excel Pivot Table
ExcelBeginner asks: I have a small problem that I am not sure on how to solve. I now have a […]
In a previous post:Â How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]
This article demonstrates how to quickly compare two data sets in Excel using a formula and Excel defined Tables. The […]
Count unique distinct records (rows) in a Pivot Table
Excel 2013 allows you to count unique distinct values in a pivot table, this article explains how to use a […]
A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]
How to create a dynamic pivot table and refresh automatically
This article shows you how to refresh a pivot table automatically using a small VBA macro. If you add or delete […]
Dependent drop down lists – Enable/Disable selection filter
Josh asks: I have this working right now with 6 drop downs/lists. I wanted to see if you possibly know […]
Question: I need to setup a template to remove the largest available (Qty and denomination)bills and leave exactly $150 back […]
This article demonstrates how to filter duplicate records using a simple formula and an Excel defined table.
The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]
Invoice template with dependent drop down lists
This article demonstrates a basic invoice template I created. It lets you use dropdown lists to quickly select products on […]
Create dependent drop down lists containing unique distinct values in multiple rows
Sharmila asks: How can i use these list for multiple rows? I would like to use these lists for multiple […]
Apply dependent combo box selections to a filter
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
Create a drop down list containing alphabetically sorted values
This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]
Extract a unique distinct list based on a condition [Pivot Table]
Anura asks: I have a list of credit card transactions showing the name of the cardholder, their Branch and the […]
Create dependent drop down lists containing unique distinct values
This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]
Create a unique distinct list using Advanced Filter in a macro [VBA]
Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]
Extract unique distinct values if value contains string
The image above demonstrates a formula in cell F3 that extracts unique distinct values from column B if they contain […]
Using Excel Solver to schedule employees
This is a question I found at the bottom of this page Using Solver to schedule your workforce Bank 24 […]
Find positive and negative amounts that net to zero
I found this excel question: I am a Controller in a multinational company. We have many transactions (sales, credits, debits, […]
Identify numbers in sum using Excel solver
Here is a useful feature I recently found googling for Excel solver examples. I have summed some random values from […]
Question: How do I filter the last xx years or xx months in Excel? How do I exclude the current […]
Create a drop down list containing only unique distinct alphabetically sorted text values
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]
5 easy ways to extract Unique Distinct Values
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
List all named ranges and their cell references
This article shows you a way to display all named ranges you have in a workbook. This is a powerful […]
Welcome! I am 