Features


Excel » Features »

How to delete empty rows

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 […]

Pivot table calendar

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 […]

Use a map in an Excel chart

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 smallest and largest unique number

This article explains how to calculate the largest and smallest number based on a condition which is if the number […]

Create monthly time sheet using a Pivot Table

Today I am going to demonstrate how amazing pivot tables are! Take a look at this time sheet. You can […]

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 […]

Hide specific columns programmatically

This article describes a macro that hides specific columns automatically based on values in two given cells. I am also […]

How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas

This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]

Copy Excel Table filter criteria programmatically

I will in this article demonstrate a macro that copies criteria from one Excel Table and applies them to another […]

Perform thousands of conditions to a data set

This article explains how to filter a data set based on extremely many conditions in an Excel defined Table, in […]

Populate drop down list with filtered Excel Table values

This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]

Macro creates links to all sheets, tables, pivot tables and named ranges

This article demonstrates a macro that automatically populates a worksheet with a Table of Contents, it contains hyperlinks to worksheets, […]

Count unique distinct values in a filtered Excel defined Table

This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]

Prepare data for Pivot Table – How to split concatenated values?

This article demonstrates a macro that allows you to rearrange and distribute concatenated values across multiple rows in order to […]

Search two related tables [VBA]

This article demonstrates a macro that automatically applies a filter to an Excel defined Table based on the result from […]

Disable autofit column widths for Pivot table

I read this interesting article Quick Trick: Resizing column widths in pivot tables on the Microsoft Excel blog. It is […]

Normalize data [VBA]

To be able to use a Pivot Table the source data you have must be arranged in way that a […]

Use a drop down list to filter 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 […]

Add or remove a value in a drop down list programmatically

This article demonstrates how to add or remove a value in a regular drop down list based on a list […]

Auto populate a worksheet

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 display data in worksheet header [VBA]

Aynsley Wall asks: I have a spreadsheet that I use for 3 different companies. What I would really like to […]

Apply drop-down lists dynamically

This article demonstrates how to automatically create drop-down lists if adjacent data grows, there are two methods explained here. The […]

Dependent drop-down lists in multiple rows

This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]

Basic invoice template

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

I will in this article demonstrate how to use a value from a drop-down list and use it to do […]

Change chart data range using a Drop Down List [VBA]

In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]

Change PivotTable data source using a drop-down list

In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]

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 […]

Auto refresh a pivot table

In a previous post: How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]

How to compare two data sets

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 […]

Create a dynamic named range

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 […]

Cash drawer bill extractor

Question: I need to setup a template to remove the largest available (Qty and denomination)bills and leave exactly $150 back […]

Filter duplicate records

This article demonstrates how to filter duplicate records using a simple formula and an Excel defined table.

Create a drop down calendar

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 […]

Advanced custom date filter

Question: How do I filter the last xx years or xx months in Excel? How do I exclude the current […]

Populate drop down list with unique distinct values sorted from A to Z

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 […]

ADDITIONAL INFO