Sunday, September 27, 2020

Result for category: All

Free To Do List Template

What is the best way to find duplicates in a workbook with over 40 worksheets? This workbook has over 5,000 names separated into different groups (worksheets). We use this list as a basis for invitation lists. We usually have over 100 names duplicated and are trying to find the best way to highlight the duplicates - we have tried many different methods in the past, but none that finds all the dupes.

Search by Format instead of Value

The ability to search by Format instead of Value can be a really useful feature...

Multi Rule Conditional Formatting

We had a website visitor looking to apply multiple conditional formats to a table. Two (2) of the rules were working, but the third just wouldn't apply correctly. We reviewed the rules and found a couple of quick files to resolve the problem.

Calculated Conditional Formatting

I am trying to come up with a formula where if the sum comes to 0 in the next cell I want it to show Low, but if it comes to 1 then it should show Med or 2 or higher then it should show High....we chose to us the IF statement and combined it with conditional formatting to solve this request.

Convert a Letter to a Number

I am looking for a formula I can place in a column that will convert a letter to a number value. For instance, s=1, m=1.5, L=2.5. I want it to be a continous rule for the column, leaving find and replace not feasible. To solve this problem the built-in vLookup function can be used.

Move filtered rows from a table to another sheet

I built a table in excel where I am comparing items. Some of those items are things will require further action after analysis. I created a column in the table with a drop down menu for yes/no. If the item requires further analysis I select "yes".

Split Row Into Multiple Rows

For this request we needed to loop through a number of rows and where candidate data was separated by a comma split the text into however many separate values are found between the comma’s. As part of the split process to also divide an amount by the number of candidates to be split

How to Force Users to Enter a Custom Format...

Microsoft Excel offers various approaches on providing specific formats for user input. We can use formula, data validation, format cells or VBA depending on the complexity of the format. For complex formats such as product ID and email address, we can use VBA and take advantage of the powerful regular expression. But since the requirement is for a formula in this case, using Data Validation feature is sufficient and as effective

Count Total Number of Deposits by Month

I need a count if function to count cells which returns a date in the current month. We receive deposits on lots, column A is lot no. and Column B is date deposit paid. Refer to the attached sheet - I need formulas for the highlighted yellow cells. Thank you 🙂 What we want basically here is to produce a report that includes the count of the total number of deposits for a particular month. However, the COUNTIF function will not accomplish the result we need. Instead, we will be using the powerful SUMPRODUCT function.


If you are planning to step up your proficiency in Excel, you should learn how to use the look up functions. These functions are commonly used on tables that act like a database. While databases have SQL queries, which is challenging especially for non-programmers, Excel has these simple look up functions and the most widely used are VLOOKUP, HLOOKUP and LOOKUP.

Filtering Dates in Multiple Columns

I am in need of a way to filter the information in the attached file. I need to be able to filter the entire sheet to show all row's that have a specified month/year no matter which column the specified month/year is in. This is used for my monthly report to show all after sales activity for each month. For example, at the end of the month I need to be able to filter all activities for the month of May 2014 to show all emails, phone calls, start-ups, acct notify, one year follow ups so I can convert that to PDF and send to the CEO