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 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 [โฆ]
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 [โฆ]
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 [โฆ]
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 [โฆ]
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 [โฆ]
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 [โฆ]
English
Spanish