Table of contents

  1. Summation
  2. Counting (NEW)
  3. Search / Find (NEW)
  4. Unique values (NEW)
  5. Sort values (NEW)
  6. Rank values
  7. Dates
  8. Charts
  9. Excel tips
  10. Troubleshooting
  11. Print
  12. Misc
  13. Macro VBA
  14. Excel templates

1. Summation

With dates and adjacent values, this powerful formula sums cell values by month and year

How to automatically sum preceding month and year

How to create a checkbox and a formula to sum cells with check box enabled

Sum cells with check boxes

How to sum an increasing column

Dynamically sum a column

Powerful summation technique

How to efficiently summarize data by category and month

Powerful way to sum specific values

Sum automatically specific posts in a list

Cell value determines the number of rows to be summed

Interactive summation

Dynamic summation

Autosum when a row is inserted

How to sum a column, but not those values that are filtered out

Sum a filtered column

Automatically create a useful list of rates and dates

How to sum different rates with a date criteria

Learn how to put values into categories

Categorize numbers in excel

Sum cell values for a specific currency in a date range

Determine the sum between a range of dates for a specific currency

Back to top

2. Counting

Count the number of mondays, for example, occurs in a date range

Count the number of a specific weekday in a range

Count cells that contain a specific text string

Count the number of cells that meet the given condition

Make your life easier with advanced counting

Count unique values in a list

How to sum and count for example values between 1 and 3 in a list

Count and sum specific values in a column

Find out how many rows are between two specific values in a list

Count rows between two values in a list

Count cells in a specific area

Identify and count cells containing a defined string value

Count number of times a string exist in multiple cells using excel formula

Identify missing values in two columns using excel formula

Identify missing values in a column using excel formula

Back to top

3. Search / Find

Search a list for two text strings and return a list with where both text strings occur

Search for multiple text strings in multiple cells in excel

Search a list quickly

How to create a useful search formula

Powerful way to search an excel list automatically

Smart way to find values

Find the smallest value in a list but bigger than 10

Learn the search and replace function in excel

Effectively search and replace specific characters

Advanced formula to search a list

Find latest date in a list

How to search in two tables

Advanced search between two tables

Quickly identify the values you want

Color cells that meet criteria using conditional formatting

Powerful table searches

Find last input value

Find largest value in a table and return adjacent cell value

Find the largest number in a dynamic column using excel formula

Create a list of cell references of not empty cells

Coordinates of cells that are not empty in a range

How to find a place or sheet quickly with a "table of contents" of hyperlinks

How to navigate quickly in a complex excel workbook using hyperlinks

How to create a list with cell references and row numbers from a single criteria

Finding values in an excel list

Create a list with multiple adjacent values from a single search criteria

Using array formula to look up multiple values in a list

Array formula finds missing values between smallest and largest

Find missing numbers in a range from multiple columns

Automatically search three ranges on different sheets

Search three columns on three sheets

Search three columns on three sheets, part 2

Lookup with multiple criteria and display multiple search results using excel formula

Back to top

4. Unique values

Sort a unique list

Create a unique list and sort by occurrances from large to small

How to sum unique values and unique distinct values using excel array formula

Sum unique values in excel

Create a list with duplicates and their adjacent values

Show duplicates and their adjacent values

How to create a unique list

Automatically filter unique row records from multiple columns

Easily identify unique values

Color unique values using conditional formatting

New button in Excel 2007 to delete duplicate records

Delete duplicate records

Create a unique list with macro

Fetch unique values from several columns (makro)

How to identify duplicates

How to find duplicates in a Microsoft Excel list

Advanced formulas to filter unique combined column values

Automatically filter unique row records from multiple columns

A more userfriendly version of: Automatically filter unique row records from multiple columns

Lookup Unique based on Multiple Conditions

How to count unique combined column values

How to identify duplicate values from two lists

How to find common values from two lists

Identify duplicate values from several columns

How to find duplicates in a range spanning multiple columns?

Delete every duplicate and the original value between two lists

How to remove common values between two columns

Calculate the number of common cell values between two lists

How many common cell values are there in list 1 and 2?

Formula to get the number of incomparable cell values in a date period

How to count unique records between a date range

How to modify a list to create a unique list without empty cells

How to automatically create a unique list and remove blanks

Create a distinct list from a range spanning multiple columns

Unique values from multiple columns using array formulas

Create a distinct list using array formula

How to extract a unique list in excel from a column

Identify common values in a list

How to extract a list of duplicates from a column in excel

Create a distinct list from two cell ranges

Create unique list from two columns

Create a distinct list sorted alphabetically

Create a unique alphabetically sorted list, extracted from a column

Create distinct list where adjacent column meets criteria

Create unique list from column where an adjacent column meets criteria

Sum values by category and create a distinct category list sorted from large to small

Create and sort distinct list by adjacent cell value size

Array formula sums only unique or distinct values

Sum unique or distinct values in excel

Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel

Create a list of duplicates where adjacent cell value meets a condition in excel

Create a list of distinct values from a list where an adjacent cell value meets a criteria in excel

Unique list to be created from a column where an adjacent column has text cell values

Identify duplicate invoice records in excel

Identify duplicate invoice records using conditional formatting in excel

Create a unique distinct list from a date range in excel

Highlight duplicates where an adjacent column is in a date interval using conditional formatting in excel

Prevent duplicates using dynamic conditional formatting in excel

Highlight duplicates using conditional formatting in excel

Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula

Highlight common values in two lists using conditional formatting in excel

Highlight the second or more duplicates in two lists using conditional formatting in excel

Count unique values in a column in excel

Count unique distinct values in two columns in excel

Count unique distinct values in three columns combined in excel

Extract a unique distinct list from two columns using excel 2007 array formula

Back to top

6. Rank values

Rank duplicate numeric values uniquely

How to create an array of uniquely ranked duplicate values in excel

Rank duplicate text values uniquely

How to rank text uniquely in excel

Rank text values uniquely within group

How to rank uniquely with criteria in excel

Back to top

7. Dates

How to get the next or previous date in an unsorted list of dates

How to identify two consecutive dates in a list

How sort out all dates later than 2006-11-18 and present the values in a new list

Remove rows containing specific dates

Let Excel notify you when a upcoming date is close

Upcoming date

Count time in excel

Count time

How to calculate negative time

How to adjust the number of holidays

Holidays and networkdays

Sort values into weekdays within a date interval criteria

Find weekday in period

How to identify, for example, every third monday of a month

How to automatically calculate a specific day of a month

How to separate out values by date criteria

Filter records between two dates

Determine if adjacent value is in date range

How to find out if a number is between two dates

Sort numbers if they are in a specific date range

How to sort cells filtered by two dates

Sort cells alpabetically if  in a specific date range

How to sort text cells filtered by two dates, part 2

Identify upcoming date closest to today

Next upcoming date from list

Sum number of records in date range

Count records between two dates in excel

Create a list of largest to smallest date ranges

Sorting date ranges in excel

Highlight dates within a date range using conditional formatting

Finding the nearest date in a range of dates using excel formula

Extract distinct unique sorted year and month list from a date series in excel

Extract dates using a drop down list in excel

Sort dates within a date range using excel array formula

Create a date range using excel formula

Back to top

8. Charts

Chart dynamically updates source data depending on drop list selection

Dynamic charting with drop list

How to create a chart that dynamically updates the values, as they are entered in the workbook

How to create a dynamic chart

Simple and easy way to create a stock chart

Learn how to create a stock chart in excel

Create better looking charts

Creating transparent bars in excel 2003 and excel 2007 charts Back to top

9. Excel tips

Timesaving tip!

Group similar cell values on same row in excel

Create a distinct list using conditional formatting

How to create a unique list using conditional formatting in excel 2007

Identify max or min values from two different columns

Max or min out of two columns

Powerful excel technique to fill blanks in a list with formula or values

How to automatically fill all blanks with missing data or formula

Create a new list from two other different ranges automatically using array formula

Merge two lists

How to highlight 10 largest values correctly if duplicate exists

Conditional formatting highlights more than 10 if there are duplicates

Categorize cell values on adjacent category column

Sort cell values into categories

Sort cell values into categories, part 2

Sort names and then split first and last name

Split first and last names in excel and then sort them alphabetically

Create a row reference list of values from largest to smallest

Return row reference of largest to smallest

Sort text cells alpabetically with array formula

Sorting text cells using array formula in excel

Filter list if criteria is met in adjacent column

Extract all rows that contain a value between this and that

Remove blank cells and cells containing errors with this array formula

Delete blanks and errors in a list

How to automatically combine two excel sheets

Merge two sheets with array formula

Powerful way to transpose values without using the transpose function and with criteria

Multiple cells in a new row

Retrieve cell addresses from multiple cells spanning over several columns and rows with array formula

Get cell addresses from several cells with array formula

How to filter out data from List 1 that is missing in list 2

What values are missing in List 1 that exists i List 2?

Lookup between two lists of data to highlight missing data using conditional formatting in excel

How to create a set of fonts, pictures and other customizations to a template

Create a template and save time

Create a more userfriendly and productive excel workspace

Increase workspace by minimizing the ribbon

How to freeze first row/column and scroll the others

Optimize sheet space

Rotate text to save space

Sheet readability

Increase sheet readability in excel

Color every second row using dynamic conditional formatting in excel

Data in formula can be separated with comma or semicolon. How do i change this setting?

Regional settings

Adding a screentip for your hyperlink can be a useful tool to inform other workbook users about the link.

Add screentip to your hyperlink

How to hide formulas

How to not display the formula in the formula bar

Make your excel sheets easier to work with

How to remove empty cells

How to get values from same cell reference from every sheet in a workbook

Get data from same cell references of every sheet in workbook

Create a list of email addresses from several columns

How to extract email addresses from a excel sheet

Back to top

10. Troubleshooting

Identify errors in a sheet in Excel 2007

How to find errors in a large sheet

How to eliminate output errors

Excel 2007: How do i remove errors from a cell formula?

How to install analysis toolpack

Back to top

11. Print

Get the pagenumbers right when printing several sheets together

Print consecutive pagenumbers from several sheets

Print headers on every page

Back to top

12. Misc

Customize excel to your needs

Automatically create a numbered list

Automatically format cells to integer

Convert text to integer in formula

Smart tip if rows and columns are being deleted without caution

Refer to the same cell regardless of whether the row above the cell is deleted or the cell is moved

Identify all names in a sheet

Excel 2007: Find names and their references in a sheet

Increase visability and readability while working word or excel

Get a better overview within word 2007 and excel 2007

Excel 2003: How to format numbers as text

Pivotables explained

Why use Pivot table and pivot chart?

Learn more about excel charts

Components of a excel chart

Increase your knowledge about excel reference system

Why use R1C1?

Timesaving tips!

Transpose a column of values to values in a row

How to rank cell data by two categories

How to rank by two columns

How to invert data from top to bottom

How to invert a list

Invert a list using cell references in excel

Invert a list using named ranges

Replaces existing text with new text in a text string

How to remove spaces

Convert decimal to hexadecimal

How to convert to hexadecimal

How to select only visible cells if there are hidden cells on same sheet

Select only visible cells

How to color every cell in excel

Create a repeating list of numbers from 1 to Nth value

How to filter out every second row using excel formula

Split values equally into groups using excel array formula

Create a dynamic border to your list using excel conditional formatting

Sorting numbers and text cells also removing blanks using array formula in excel

Sorting numbers and text cells descending also removing blanks using array formula in excel

Back to top

13. Macro VBA

Simple time saving macro to insert a new row at top

Insert a new row at the top every time a value has been entered

How to create excel macro to color every second row

Back to top

14. Excel Templates

School attendee register | Download excel template

Calculate machine utilization | Download excel template

Back to top