Archive for 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.
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 […]
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 […]
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 […]
This post demonstrates how to merge two related tables before creating a pivot table. A pivot table is limited to […]
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 […]
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 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 […]
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 […]
Rodney Schmidt asks: I am a convenience store owner that is looking to make a spreadsheet formula. I want this […]
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 […]
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 […]
Follow 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 […]
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 […]
Excel 2007 pivot table: Count unique distinct records (rows)
Table of Contents Count unique distinct records Count duplicate records Excel 2007 pivot table: Count unique distinct records (rows) The […]
Google spreadsheets: Pivot tables
This week Google Docs introduced Pivot Tables in Google Spreadsheets. I am not that familiar to google spreadsheets but I had to […]
Calculate average of last 10 data with possible blank cells
Question: Answer: This array formula creates a dynamic range, filtering the 10 last data. Adjust cell ranges $A$1:$A$25 in formula below. […]
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
David Hager commented: Looks like an easy pivot table solution to me. Thanks for your comment! Now i know how to […]
Cash drawer bill extractor template in excel
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.
List people with the highest scores based on criteria in a pivot table (Excel 2007)
Overview This post describes how to create a list of people, sorted by occurrence, based on criteria in a pivot […]
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 […]
How to return the total for orders with more than one item in excel
Question: I have given up trying to figure out the following problem as it seems over my head, and am […]
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 […]
Filter unique distinct values if value contains specific string [Formula and Advanced Filter]
Table of contents Filter unique distinct values using a "contain" condition Filter unique distinct values using a "contain" condition (Advanced […]
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 2007? How do I exclude the […]
Find the largest number in an expanding cell range
Question: I have a list that I add data to every day. I would like to find the largest number […]
5 easy ways to extract unique distinct values
You have quite a few options to choose from if you are looking for a way to create a unique […]
List all named ranges and their cell references in a worksheet
This article shows you a way to display all named ranges you have in a workbook. This is a powerful […]
