You need to know what the hash sign does in Excel formulas

Using a pound symbol (#), also called the sparse range operator, in an Excel formula is a way to tell the program to consider all results in a sparse range. Therefore, even if the extended range increases or decreases, the formula containing the # will automatically reflect this change.

Let’s say you manage an animal sanctuary and your spreadsheet contains a formatted Excel table called Animals_Admit, which shows the animals currently in your care.

In order for you to make the best use of the space you have at the sanctuary, you need to know how many of each type of animal you have and how many different types of animals you have in total.

To see how many of each animal are currently in your sanctuary, in cells D1 and E1 (the cells above where your first flip charts will go), type the column headers Animal And Countrespectively.

Now, in cell D2, type:

=UNIQUE(Animals_Admitted[Animal])

Or UNIQUE is the Excel function that lists unique items in a range, Pets_Allowed is the name of the table where the original data is located, and [Animal] is the name assigned to the third column of this table.

Related

How to list and sort unique values ​​and text in Microsoft Excel

Create a list of unique names, dates or other data in your spreadsheet with a simple function.

When you press Enter, you get a broken down table that lists each unique item in the Animal column. You know it’s a sparse table because a blue line surrounds the result each time you select one of the affected cells.

Related

Everything You Need to Know About Spills in Excel

It’s not worth crying over spilled references.

Now it’s time to make Excel count the number of animals of each animal in your original table, and this is where you use the chop the sign makes life much easier.

To do this, you will need to use the COUNTIF function. However, because you want Excel to count all For animals returned by the UNIQUE function in column D (even if some animals are added or removed from this list later), you must add a pound sign after the criterion reference.

So, in cell E2, type:

=COUNTIF(Animals_Admitted[Animal],D2#)

Or COUNTIF is the function that counts the number of occurrences, Pets_Allowed[Animal] is the range containing each animal in your original table, and D2# tells Excel that the criteria for the COUNTIF function is a spread array starting in cell D2 and therefore can change size.

Now imagine that a hedgehog is brought into your sanctuary, and this hedgehog will be the only one currently in your care.

Because you referenced a formatted table header in column D and used the reverse range operator in your COUNTIF formula in column E, the hedgehog is automatically added to the list in column D and the number of hedgehogs in column E displays correctly as “1”.

Now you want to create a list that sorts the animals by number.

After typing the above headers in cells F1 and G1, in cell F2, type:

=SORTBY(D2#:E2#,E2#,-1)

Or SORT BY is the Excel function that sorts a range based on the values ​​of another range or table, D2#:E2# tells Excel that the table, which occupies columns D and E, contains two spread table columns starting in cells D2 and E2, E2# is the widespread array to sort on, and -1 tells Excel to sort the data in descending order.

Because you used these pound signs in the formula, you can rest assured that your lists will expand and contract dynamically based on the data in your original table.

Finally, you also need a basic count of the total number of animal species in your sanctuary.

To do this, after typing a relevant heading in cell H1, in H2, type

=COUNTA(D2#)

Or COUNTY is the Excel formula that counts the number of cells in a range that are not empty, and D2# tells Excel that the range is a distributed array. In other words, we are telling Excel to count the number of animals returned by the UNIQUE function in the flipped table starting in cell D2, and that this number may change.

Related

How to count cells in Microsoft Excel

To count cells that contain numbers, text, or nothing at all, here are the COUNT functions you need.

As a final test, suppose the turtle is released in London. After removing London from the original data, since he was the only turtle in the sanctuary, cell H2 correctly tells you that there are now only 18 unique animals in your care, and “Turtle” no longer appears in any other lists you have created.

Reverse firing range operators (#): latest indicators

Before we begin, there are a few additional points to note.

First, in addition to referencing a distributed range in the same worksheet, you can also reference a distributed range in another sheet. For example, to reference a distributed table starting in cell A2 on sheet 2, you could type:

=Sheet2!A1#

Second, you can reference a widespread range in another workbook. Although Microsoft says the referenced workbook must be open for this not to return the #REF! error, when I tested it with a closed workbook, the reference continued to work. Just make sure to back up both workbooks and connect them both to the same OneDrive account to ensure you don’t lose any data.

Finally, to prevent your formulas from returning #SPILL! error, make sure that all cells that the table should spill into are free of other data and not merged.

Now, whether you simply want to expand your knowledge of Excel formulas and functions, or have applied for a job that requires Excel expertise, you can confidently say that you know what the number sign does in Microsoft Excel formulas!