Category: Microsoft Office

Posted on

Be more productive with Excel’s UNIQUE function (3 of 4)

This blog is part 3 of a series that looks at the amazing functionality of some of the most popular Office 365 Excel functions. In parts 1 and 2 the focus was on the FILTER and SORT functions. Building on this, we will explore how useful the UNIQUE function is in creating  unique lists more efficiently.

What is the UNIQUE function and why is it useful

The UNIQUE function lets you extract all the multiple entries in a list and populates them as single entries in a unique list. For example, clients that do business with a company may place many different orders so their name will get repeated many times. Having a unique list of clients can help to show summaries of total orders or sales.

How is it different from other functions?

The UNIQUE function is one of a number of Office 365 functions that behaves differently to all other Excel functions. With the UNIQUE function, the results automatically ‘spill’ into all available cells below. In contrast, all other functions require the result to be copied down manually.

How to apply the function

Let’s take a list of names where some of them appear more than once – see below:

Unique

  1. Type =UNIQUE(A1:A14) in an empty cell to produce the following unique list:

    Unique
    2. To return a range from a horizontal list do the following:

    A) Select the range of cells in the list e.g. E1:R1Type a comma and then TRUE – B) this returns all unique items across all the columns.

    Another really useful feature of UNIQUE is being able to produce a list of all items that appear only once. From the original list, we can see all the items that have no duplicates i.e.. those that display no colour (see below):

    Unique
    3. To return a distinct list type =UNIQUE(A1:A14,,TRUE) where:
    A) the 2 commas mean “bypass the 2nd part” because the list is vertical not horizontal
    B) TRUE returns only items that appear once in the original list 

    The result is the following list:

    One thing to look out for is if any data is ‘blocking’ this space then you will get a #SPILL error. Consequently, the resulting data will not populate the cells. (See below)

    To remove the spill error, simply delete the data that is blocking the spill. Other Office 365 functions such as SORT and FILTER also have this ‘spill’ feature.

    Conclusion

    The Excel UNIQUE function is an amazing tool that allows you to extract specific data quickly and efficiently from a list of multiple entries.

Posted on

Excel – The Power of Custom formatting

Most Excel users do not know the power of custom formatting. They know how the currency or accounting format. In this article you will find some useful information which can display your data in different ways, and more clearly.

I got the inspiration to write this blog post from a client question on one of my Excel courses.

The client asked me “I get data from our financial system. In one column I have a percentage, but the column is not percentage formatted. When in Excel, I add percentage formatting, Excel multiply the numbers by 100. I don’t want to have to divide the column by 100. Do you have a solution?”

IMPORTANT: CUSTOM FORMATTING WILL NOT CHANGE THE DATA ONLY CHAGE HOW EXCEL WILL DISPLAY THE DATA.

To create custom formatting in Excel:

Select the data and click the down arrow bottom right corner of the Number group on the ‘Home’ ribbon. In the Format Cells dialog box click Custom bottom left.

custom

In this example you will see an example of dates custom formatting.

You will have to type the code. When it comes to custom formatting for dates dd display day number.

If we enter “dd” in the Type box Excel will only display the day number.

Alternatively, if we type “ddd” in the Type box, Excel will display the first 3 letters of the day name.

custom

4 times d will display the full day name. Use ds for days, ms for moths, and ys for years.

The code dddd dd mmm yy will then display:

formatting

In the next example you will see some options for numbers.

;; will display the cells like they are empty.

custom

If you only want to display positive numbers use the code 0;;

custom

What you have in front of the first semicolon (;) define positive numbers, between the two semicolons define negative numbers, and after the second semicolon define zero. A colour inside square brackets can define font colours. The code [green]#,##0.00;[red]#,##0.00;[blue]0 will result in positive numbers being in green. # are optional digits. So, with a number above 999 you will get thousands separator. You will always get two decimals. Negative numbers will be in red and zeros blue.

formatting

When you have zero in the code, you tell Excel that you want to display the number, but you can also turn it into text only. The code [Green]”good”;”bad”;”zero” will not display any numbers.

formatting

You can find a table of examples below, but it is endless what you can get Excel to display.

formatting

Conclusion

Custom formatting in Excel is useful for numerous reasons. Primarily, it can help improve the visual appearance of your data and make it more readable.

Custom formatting can help save time and reduce errors by automatically applying specific formatting to your data. For example, you can use custom formatting to automatically add a “%” symbol to values entered in a certain column.

Custom formatting can help you better communicate information in your data by allowing you to display it in a more meaningful way. For example, you can use custom formatting to display dates in a specific format or to display times with AM or PM.

Overall, using custom formatting can make your Excel data look more professional, easier to read, and more informative.