All articles with 'unique' Tag

Let’s face it. You already know how to use SUMs, COUNTs and IFs and want to level up. But there are 100s of functions in Excel and you are not sure which ones to focus on.

In this article, let me reveal the top 10 important functions in Excel for data analysis work.

Excel Dynamic Array functions are going to change the way to you work your data forever. Watch the replay of the event here and learn about…

* What is so different about Excel’s dynamic array functions
* The new # operator
* 6 important dynamic array functions – FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, RANDARRAY
* 25 practical examples of dynamic array functions for business analysis
* Your questions & answers

Excel Dynamic Array Functions are a true game changer. These newly introduced DA functions can filter, sort, remove duplicates and do much more. The output of these functions can go to a range of cells. Hence the name – dynamic array functions.

Validating your data as you type can prevent any surprises when you are doing analysis / follow-up on the data. Thankfully, excel has the right tools to do it. Excel drop down list can assist you in picking up a value from a valid list to enter in a cell. Here is a short how-to […]

Time for a quick formula finesse check. Let’s say you have a number in A1. What formula can you use to find out if it has duplicate digits.

For example, if A1 has 123405, then answer should be FALSE

and if A1 has 123455, then answer should be TRUE

Go ahead and post your answers (formulas, VBA or M script) in the comments section.

Who is the most consistent of all?

Imagine you are a category manager at a large e-commerce company. Your site offers various products, but you don’t really make these products. You list products made by other vendors on your site. Every day, these vendors would send you invoices for the amount of product they have sold. Above is a snapshot of such invoices.

Looking at this list, you have a few questions.

1. Who is the best seller?
2. Who is the most active seller?
3. Who is the most consistent seller?
4. Which seller has fewest invoices?

Let’s go ahead and answer these using Excel. Shall we?

We all know that data validation is a very useful feature in Excel. You can use data validation to create a drop-down list in a cell and limit the values user can enter. But, do you know that you can use data validation in a multitude of ways to prevent users from entering wrong data?

Here is a practical application: Prevent users from entering duplicate values in a range of cells.

For eg. you are making an invoice. Wouldn’t it be cool if Excel prompted you when you enter a duplicate line item so that increase the item quantity instead of repeating it.

Here is a quick formula trick you can use to generate sequence numbers that only increment when there is a change. Assuming the sequence of values are in column C from C3, you can write the following formula in B4 onwards (B3 will be 1, wake up…) =IF(C4=C3,B3,B3+1) Now just copy paste the formula over […]

Here is a list of formulas I wish MS Excel had. Alas, we need to build some work-arounds to solve them though.