Best Microsoft Excel Bloggers

Thursday, January 25, 2018

TextJoin - An Excel 2016 Text Function

If you need to join a lot of text together in a string, check out TEXTJOIN.
It is a text function that was introduced in Excel 2016 to join text in cells together.
Initially, you might think that the function is not worth investigating. After all, we already have the ampersand (& ) as well as the CONCATENATE function to join text together.
However, TEXTJOIN has a decided advantage if you need to join multiple cells together because it only requires you to specify the delimiter once.
The syntax is =TEXTJOIN(delimiter, ignore empty, text…)
textjoin function









Let me give you a simple example using a person’s first, middle and last name and their professional designation.
textjoin function


If we used CONCATENATE, the equation would be : =CONCATENATE(A2,” “,B2,” “,C2,” “,D2). With TEXTJOIN the equation is  =TEXTJOIN(” “,,A2:D2).
textjoin text function









and this result would display: 
Lisa Marie Brown CPA
Clearly, TEXTJOIN is a bit simpler and a bit more elegant if you have a lot of cells that you want to join together and you want to use the same delimiter throughout.
Different Delimiters
You can use different delimiters if you wish and it is still simpler than CONCATENATE as well.
In this example, I wanted spaces after each of the names but I wanted a comma before the professional designation so I used 
=TEXTJOIN(” “,,A2,B2,C2,”,”,D2) and Excel displayed  the following result.
Lisa Marie Brown , CPA
So, add TEXTJOIN to your arsenal of text functions if you have Excel 2016.

Tuesday, January 9, 2018

Searching for Files with Keywords


Using Keywords to Find Your Excel Files

If you have lots and lots of files, you know how difficult it can be to locate one, particularly if you did not name it well. Plus, let's face it, memories don't improve with age.

So, here is a solution. When you create the file, add some keywords, a title and the author's name to make it easier to locate the file at a later date.

It is very easy to do, however, you do have to remember to add this information so that you can search for it later:)

  1. Click on File and then select Info.properties
  2. Click the Properties drop-down arrow - located over on the far right side of the screen and then select Advanced Properties.
  3. Click on the Summary Tab and enter keywords that relate to the file. keywords in Excel file
  4.  Click OK.

 To search, simply go to the Windows Explorer window and in the search box, located on the far right of the window, type in one of your keywords.  Your file should display. You can also search on author, a category or a title as well.
window explorer to search
Lots of options to find your file.

Wednesday, May 17, 2017

VLOOKUP versus INDEX MATCH! Which is Better?


VLOOKUP versus INDEX MATCH! Which is Better?

vlookup and index match

Chris Chau of Defeatexcel.com asked twenty-seven (27) Excel MVPs and community experts their preference  on the ongoing controversy of VLOOKUP vs. INDEX MATCH. it is great reference material and some suggest choices they think are better than those two functions!



Click on the link to read the blog. https://defeatexcel.com/vlookup-vs-index-match


Check out the results. According to Chris, there was no clear winner so which do you use and prefer?   It really is liking asking who is better -Superman, or Batman!


If you want more information after reading Chris' blog, check out my course Become a Lookup Expert.  

It provides a comprehensive examination of the Lookup and Reference category in Excel. It covers the LOOKUP and VLOOKUP functions.
With VLOOKUP, you will learn how to: use Wildcards; search through multiple sheets for a match; and what to do if a match is not in the first column of your table. The course also explores HLOOKUP. It explains the advantages and disadvantages of INDEX MATCH versus VLOOKUP. It reviews the OFFSET function, which allows you to write calculations that automatically update as new data is added. With the INDIRECT function, you will learn how to find data on other worksheets sheets.  Finally, HYPERLINK is briefly discussed.

Friday, May 12, 2017

Determining Median and Mode

Determining the Median and Mode

Sometimes determining the average is not sufficient.
For example, if
 you are analyzing departmental salaries you might want to know the median or mode of the salary levels.




=Median(data range)  internally sorts the data and  then displays the middle value. If there is an even number of values, then the two values in the middle are averaged. =Median(B2:B10)


=Mode(data range) will display the value that appears most often. =MODE(B2.B10)

This from an excerpt from my Excel CPE Course, Must Know Excel Tools and Tips for CPAs.  

Monday, February 13, 2017

RRI Time Value of Money Function


Time Value of Money Functions

Below is an excerpt from Excel Time Value of Money Functions for CPAs, a CPE course offered by CPASelfstudy.com


New Single Sum functions in Excel 2013 

There are two new time value of money functions in Excel 2013, the RRI and PDURATION functions.  Both of these functions will only work if you have Excel 2013 or greater.  You will not be able to replicate the examples using a lower version of Excel.

The RRI function returns the equivalent interest rate for the growth of an investment.  The inputs required are the number of periods, the present value and the future value.
In this blog entry, we are only going to discuss RRI and save PDURATION for another time.

RRI Example:

As an example, let’s say you invested $100,000 for 8 years compounded annually and the investment grows to a value of $150,000.  What is the equivalent rate of return?  Click here to read the rest of the entry.

Ms. Excel- Resident Excel Geek