Tag: Excel Tips

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 : Audit your Spreadsheet with the Inquire Add-In

new feature for microsoft office 2013For users with the Office Professional Plus package, the Inquire add-in comes pre-installed on Excel 2013. Helping you to analyse, audit and review workbooks, this great new feature also has the potential to highlight errors and security concerns.

We recently discussed, how to take back control of your spreadsheets by reducing “fat finger mistakes” and auditing errors, which can have huge cost implications to businesses. The Inquire add-in is another great tool for preventing these issues from arising and escalating in Excel.

The new tool can be accessed via the Inquire Tab in the ribbon and includes a number of useful functions:

Worksheet relationship

  1. Workbook analysis
  2. Workbook relationship
  3. Worksheet relationship
  4. Cell relationship
  5. Compare files
  6. Clean Excess Cell formatting
  7. Workbook passwords

These seven functions are simple to use, many providing visualisations to help better understand the information.

We all know Excel for being data driven, however, visualising things often makes this mass data easier to understand, take for example recommended charts & graphs or all new quick analysis techniques.

A few of our favourite Inquire features include:

Cell Relationship in Excel 2013

  • Cleaning excess formatting, including formatting in blank cells which bloats file size and contributes to poor performance in Excel.
  • The ability to compare two workbooks, highlighting cells that differ. This is particularly useful during an audit.
  • Being able to visualise the relationships between cells. Understanding the audit trail of how a figure came to be is a great way of maintaining the integrity and accuracy of the data.

How to: Enable the Add-in through File > Options > Add-Ins, from the Manage drop-down choose COM Add-Ins > Go. Tick Inquire and click OK.

To use it select the Inquire Tab in Ribbon > Choose function

For more tips and features on Excel 2013 and other versions, browse Microsoft training Excel courses from STL, available London and UK wide.