Excel Tip: Display current sheetname

Sometimes it can be very handy in a spreadsheet with multiple sheets, to show the sheet name in the sheet itself.

Say you have a sheet for each salesperson and new sheets are regularly being added and you name the sheets with the salesperson's names and want the same name to appear in the heading on the sheet. This is particularly important when printing the sheets as the worksheet name will not be visible.

You could, of course, type the same name in the header as the name of the sheet, but this has a few disadvantages:

1. It's twice as much work
2. You may forget, and have a different salesperson's name on the printed reports as is being used to pay them for example. This kind of inconsistency can lead to all sorts of problems!

Fortunately, it is relatively easy to get at the sheet name in a formula.

We can use the CELL command to do most of the work. This allows you to pull information about the current cell. In this case we are going to use it to pull the file path (including the sheet name). This is done by entering "filename" as the info type argument in the CELL command as follows:

=CELL("filename")

This will show something like:

C:\Users\Glen\Documents\[MySpreadsheet.xlsx]SheetName

where MySpreadsheet.xlsx is the name of the spreadsheet and SheetName is the name of the sheet that contains the CELL function above.

As we just want the sheet name, we can use the fact that the workbook name is enclosed in square brackets as follows:

=MID(CELL("filename"),FIND("]",CELL("filename"))+1,999)

This earlier post gives more information on both the FIND and MID functions.

Essentially, this returns the 999 characters starting at the position after it finds the "]" (the +1 ensures that we don't start until the character after the "]"). I have simply used 999 to ensure that all of the characters after the "]" are returned. It will not return any extra characters, so will just return all of the characters after the "]", i.e. the sheet name!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Make it easy to enter the arguments for a function

I have said this many times before, but one of the things I like most about working with Excel is that there is always something new to learn.

I don't just mean learning new features as they are added. I mean learning things that have always been there that you didn't know about!

A blog post on another Excel expert's blog taught me a really simple one the other day, that I'm not sure how I've missed!

Reading a post on Charley Kyd's ExelUser blog on Excel's Five Annuity Functions, he revealed a simple feature that I had never come across, but could be useful when entering any function in Excel.

Did you know that you can start typing any function and then press Ctrl-Shift-A for excel to fill it in with the names of the arguments, which you can then simply replace with the actual arguments.

For example, if you type:

=VLOOKUP

and press Ctrl-Shift-A, Excel puts the following into the formula bar:

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

You can then replace these argument names with the arguments for your particular scenario.

Alternatively, you could even leave it as is and name the cells and ranges that hold the arguments with these argument names as range names.

i.e. name the cell that contains the value you want to look up, lookup_value and the range that contains your lookup table table_array, etc.

I'm not sure I would use the range naming approach, not least because you could then only use the function once in your spreadsheet!

Using Ctrl-Shift-A to give me a skeleton to enter the function arguments does sound useful though.


If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel and Business Intelligence

There has been much talk for years now among IT experts, about getting away from Excel for business reporting, while the reality has tended to move the other way.

In my view, much of the case for specialist Business Intelligence Software is more a case for structure and control in business reporting, rather than proving that we are all using the wrong software.

You can see more discussion on this in an earlier post:

http://www.notjustnumbers.co.uk/2015/07/is-excel-error-prone.html

I thought this week, that I would share with you a recent article on the website of Investech.com that gathers the views of 27 Excel experts on the subject, including a few names you might recognise, such as Chandoo, Mynda Treacy and myself. Unfortunately, you can tell by the brevity of my comments that they caught me at a busy time!


27 MICROSOFT EXCEL EXPERTS PREDICT THE FUTURE OF EXCEL IN BUSINESS INTELLIGENCE

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Counting items that meet certain criteria

Today, I am going to cover a quite simple function that has been touched on in previous posts but that I have never covered on its own.

Sometimes, you want to know how many items in a list meet certain criteria. As usual, Excel has a function (or two) to help.

If you simply want to count items in one column (the same column that you want to apply the criteria to), you can use the COUNTIF function.

The syntax is as follows:

=COUNTIF(Range,Criteria)

So, for example, if you had a list of sales and column A contained the sales region, you could count how many sales were in the North region, by using:

=COUNTIF(A:A,"North")

This assumes an "equals" criteria, but you can use other operators. Say that column B contained the value of the sale, then you could return how many sales exceed £5,000, by using:

=COUNTIF(B:B,">5000")

But what if you want to return how many sales exceeded £5,000 in the "North" region?

Here, you can use a more recent Excel function that allows you to apply multiple criteria, COUNTIFS. This works just like COUNTIFS but allows as many pairs of Range and Criteria as you want, so:

=COUNTIFS(A:A,"North",B:B,">5000")

would provide our answer.

And that's it!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: The Curious Case of FIND and SEARCH

One of the things I love about Excel is that I continue to learn and find new quirks all the time.

An issue that arose while working on a spreadsheet for a client last week, caused me to find one of these such quirks.

Read on about the Curious Case of FIND and SEARCH...

I regularly use the FIND function to find text within other text, for various reasons.

The FIND function works as follows:

=FIND(text,within text,[start character])

This function returns the position (character number) of the first occurrence of "text" within "within text" - reading left to right, starting at the start character, or the beginning if omitted.

So,

=FIND("U","NOTJUSTNUMBERS") returns 5

whereas,

=FIND("U","NOTJUSTNUMBERS",7) returns 9

I had used this function as part of a larger formula in a client spreadsheet, but it needed to be case-insensitive. After a little Googling, I discovered the SEARCH function which is exactly the same as the FIND function, the only difference being that it is not case sensitive!

I was even able to use Find and Replace to swap all of my FIND functions for SEARCH functions, to stop them being case sensitive.

I might have come across this earlier if it had been called something like FINDCASEINSENSITIVE!




If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".