Unique function example

Excel Unique Function

June 16, 2020


Have you wanted to count unique values in Excel? Early this year Microsoft released to all users of Excel new array functions that make using an array A LOT easier in order to do so.  These mainly take the place of Control-Shift-Enter arrays (CSE). If you are a user of LibreOffice, I am afraid these are not yet available to that office suite. I am sure they are on their way though. The new array functions will be able to save you a lot of thinking and math.

Under the old CSE style of arrays you could come up with long and complex formulas.  I believe the longest formula I wrote in a spreadsheet was about 600 characters. Needless to say debugging or adding new considerations in the formula was time consuming and took some time. Depending on what you are trying to calculate, that same type of formula could possibly now be written in about 50 characters.

I am going to keep this simple by discussing only the new UNIQUE function here. First lets take a look at the old way. The way I would evaluate a list uses IFERROR to avoid displaying N/A errors. LOOKUP to do what it suggests, lookup values. Finally COUNTIF to evaluate the lookup data to find the unique values. I made a simple spreadsheet with a fictional employee list as seen below:

CSE style array formula to find unique values.

You can see the formula I used up in the formula bar. Using a binary (1 = yes) type of sort I labelled all the active employees. So now I want to find all the unique names. This example is rather impractical in real life, but that is why it is an example. In a nutshell that ugly formula basically says Lookup and write only once unique names from column B and list them in column D only if the value in Column A is 1.  It lists from bottom up and in column D it fills in the names by comparing the names to the name listed above in column D. I then copied that formula into every cell from D2 through D15, Rather complicated.  It works. Yes there are other ways to do this, however the formula will look just as complicated.

So what happened in 2020?  SOMETHING AMAZING! In fact it will blow you away. It will give you free time. It might win over new fans to Excel and other spreadsheet software programs. Really to be honest, this is likely very familiar to you if you use Google Sheets. Are you ready to see how to rewrite the above formula the new way?

The Unique function

UNIQUE formula with spill results.

There you have it, the new way to count unique Excel entries. To get the same list of unique names from the previous formula, just type =UNIQUE(B2:B15).  The only issue you may have with this is if the column the data spills into finds a cell with data in it.  In which case you will get a spill error. Another benefit is that since you only entered the formula one time and the resulting data “spills” down, you end up with a smaller file size that loads faster.

There you have it, one of the new functions Microsoft added to Excel.  Such a time and frustration saver. 

An example of the spreadsheet in action.


Make Your Own Credit Card

Many of the frustrations we have as consumers is obtaining a credit card, making the monthly payment and then realizing that only $10 out of the total $55 payment went towards your balance.  Well maybe that is an exaggeration but you know the feeling. What if you could make your own credit card?  That is what I will describe here. This is the page where you candownload this spreadsheet. So lets get to it, make your own credit card.

First things first, if you do not have self control and feel you must spend money that sits in savings, this concept is not for you. As such This will not affect your credit score in anyway. This truly takes self control and dedication. Second, You will need spreadsheet software.  That is not as expensive as you may realize. In fact you can get one for FREE. LibreOffice is an Office suite absolutely free to use. LibreOffice Calc is the spreadsheet program.

Lets open the file I created.
Lets start with the basics. In cell B1 You set your own Credit Limit. Cell B2 cannot be edited and will tell you your current available to use balance.  In Cell E1 you can set your own Interest Rate. What does all this mean? Well you do not need any money up front to start using this spreadsheet. In fact you can start using it in a maxed out state in order to start saving money for purchases. You can go ahead and set any credit limit you would like. Be mindful, that you still have to make payments (to yourself) and they have to be big enough to cover your interest costs. Here is the cool part, the Interest Rate. This becomes your savings plan.  You can set your rate as high or low as you want.  I suggest setting an obscene rate like I have done. After all you are not giving that money to a bank, you are keeping it for yourself. So if you are broke, you may want to do yourself a favor and start with a $200 limit and low interest rate so you can get your self some cash to use for purchases on your self credit account.

The payments area and you can only edit the Payment column (Column B).  First this is where you will find out how much you can afford to set up. Go ahead and type in what you would like to pay each month.  Then go back to the top and adjust your limit until you are comfortable with the amount you will be paying to the balance each month. In the above example I have $200 available to spend now. So I entered a payment and found that the interest charge is $7.23. If you are fully funded to start each year you can either pay the interest to your savings (like an annual fee) or just ignore it. The next month I used the credit and made a purchase.  It charges me interest against the previous balance of $0 so I opt to pay just $15 to allow myself to earn an interest charge (because that means I will be saving money) and I am left with a balance of $17.49. I continue then with $25 payments while using my credit until I have everything paid down in the 10th month. What happens after the 12 month?  We roll over and clear out the month 1-12. After a full year write down your available balance and delete all the payments in Column B. We will discuss this written down balance after the next section where you track your purchases.

Before then, you likely have one important question.  How and where do I make my payments too.  Well that is up to you.  You can use your own checking and savings. Keep the payments in your checking and then transfer your interest charge to your savings account.  You could also get creative with online banking options like I did.  I use CashApp for my “Credit Account” and ordered a physical card. I recommend finding an online financial institution that will give you a debit card so that you can make your own credit card. Therefore I issued myself an actual physical “credit card.” It also has cool engraving options. Then I transfer my interest to savings.  Then I earn interest to grow my savings. Remember this requires you to be very vigilant and have self control over your own cash.  If you don;t have self control over keeping money in savings or having money sitting in an account for a card then you may want to leave this whole process up to the professionals…

The above is where you track your monthly spending. I left 5 rows for each month (Numbered at the top of each column) for spending.  A handful of rows as you should be using any credit account as little as possible or for large purchases. You will notice in the first month the total is ($200). This is because I entered -$200 in order to have a full $200 to start with. Remember in the previous section I said to write down the available balance at the end of Month 12, this is where you will enter that to start the new credit year. Keep track of your spending for each month and everything will auto calculate when you make the next months payment. As will the payments, after 12 months you will need to delete all your entries. This time entering the ending available balance as a negative in the 1st line of Column H in Month 1. So you want to increase your credit limit? Lets look at that magical section.

This is what this little section is for.  This keeps track of the total interest, er um, your total savings for the year. In this case $20.90.  The next line rounds that down. Why?  Well, my thinking is use the save money to create a higher credit limit and make it immediately available to use. Round up, would mean using funds that don;t exist. So in this case, I would have saved $20.90 over the past year and I could then use a $20 self made credit increase. My new limit could become anything between $200 – $220.


I hope this has been helpful and insightful and you learned how to make your own credit card.

You will need software that can open a Spreadsheet in the XLSX format.  A free to use option is LibreOffice.

Everyone gets frustrated with paying Credit cards and not getting anywhere because of the interest charges.  What if instead, you could collect those interest charges yourself as savings?  Here is a spreadsheet that will help you do just that. You can either use 100% of your payment towards the balance or save the interest into your savings account.  This is possible because everything is controlled by you. Use your accounts and debit card. Set your interest rate as high or low as you would like. I have a blog post explaining how to use it. This Blog Post explains how to use this file.