Best Microsoft Excel Bloggers

Wednesday, December 4, 2019

Flash Fill - A Good Trick to Know.

Flash Fill

If you download data or use a lot of Text functions, Flash Fill is a good trick to know. It is a feature that sometimes allows you to avoid using Text functions and copying the results.
This feature is only available for users of Excel 2013 and higher. 
Flash Fill looks at your data entry and tries to duplicate your pattern.

For example, in this screen shot, I clicked in cell B2 and typed the domain name and then clicked on the Data tab and selected the Flash Fill icon on the tool bar.

 Flash Fill looked at the pattern and then duplicated  and then copied it down.  I did not even have to tell Excel what cells to look at. It kept going down until it did not see a pattern anymore.
So, I didn’t have to worry about using  text functions such as SEARCH, MID, LEFT or RIGHT. Super easy right? It doesn’t replace text functions, but it may lessen your reliance on them particularly if you are doing something quick.
Here are a couple of things to be aware of though.
Be careful as it only works if it detects the pattern on existing text. If I inserted a row between row 3 and row 4 or entered a new email on row 5 and then tried to copy down, it would just copy what was above it so in these case I would have 2 domains showing 

If you right-click, you should see Flash Fill on the short cut menu.

Flash Fill will also allow you to create a pattern. For example, in this screenshot, when I downloaded phone numbers, just the numbers downloaded. So, I can type in the format that I want, in the top cell, and then use Flash Fill.

Now, it is not perfect.  Notice that Flash filled in the text above the cell I was in as well. So my heading now has parentheses after the first 3 characters and the hyphen has been applied as well.  Also, in this case, it assumed that all the cells contain the same number of characters. If I add in a phone number that does not have an area code and use Flash Fill, Excel will still follow the pattern as best it can. 

Leading zeroes can also create issues so be careful of that as well. This is definitely a feature to try. It could be a big timesaver.

Thursday, April 4, 2019

Tired of Nesting IF after IF?
Get off the Nest and use IFS()

IFS()  is a Logical Function that can be found in Excel 2019 and Office 365.
This function allows you to test for up to 127 conditions and determine if they are true.
So, no more pulling out the magnifying glass to find your mismatched parentheses or mumbling the formulas to yourself as you go through trying to figure out where your error is.
IFS() makes it much easier although it is not perfect. Two things to be aware of. It only tests for TRUE conditions and displays a #N/A error if it finds a FALSE. Also, Excel stops as soon as it finds a TRUE condition so the order of your tests (conditions) can matter sometimes. Here is a quick video on the IFS() function.

BTW - If you are doing nested IFs that contain anywhere near 127 conditions, please read about INDEX MATCH and VLOOKUP because no one should be doing nested IFs to that degree. I need to get an aspirin, just thinking about it!

Friday, March 9, 2018

This is a guest post from Joseph Helstrom, CPA.

Adding VLookup as a Control for Your Month-End Workpapers

When closing for the month, many use an Excel workbook to reconcile balances to the general ledger.  As the general ledger changes, do you have a process in place to easily determine that balances previously reconciled still tie to the general ledger?  If not, using Vlookup on each reconciliation worksheet provides a quick and easy way to accomplish this control.

Usually, the month-end balance from a reconciliation worksheet is at the bottom of the worksheet.  Below this balance, you can enter the associated general ledger account number to be used in a Vlookup formula.  After the account number and Vlookup formula have been entered in each workbook, all you need to do is cut and paste the most recent general ledger balances into a separate worksheet.  The Vlookup will display the most recent balance and you can use conditional formatting to highlight whether the two balances are within a tolerable error limit.

The example below assumes that the AR Trial Balance has been created and the total needs to be compared to the general ledger.  There is a worksheet labeled “Accounts Receivable” and a separate worksheet labeled “General ledger”.

The general ledger account for accounts receivable has been entered at the bottom of the Accounts Receivable worksheet and a Vlookup formula is used to compare this total to the amount in the General Ledger worksheet.

The Vlookup function is searching for a worksheet titled “General ledger” in the same workbook.  The key to making this work is that you never delete this worksheet.  When the general ledger changes, simply paste the new general ledger data over the old data in the same worksheet.

For Vlookup to work, the lookup value, in this case the general ledger account number, must be in the leftmost column of the General Ledger worksheet (In this example, column A).  The general ledger data is in columns A and B and the account balance is in column 2 (which is the B column).  The FALSE range lookup value is used to indicate an exact match.  The example general ledger is shown below.

Why use the entire columns of A and B?  That way, if general ledger accounts are added, we’re not constrained by a limited range.  This is important as we want to paste over this data again and again without using new Vlookup formulas each time. 

To calculate any differences, subtract the general ledger balance from the reconciliation balance.  Use the ABS function in the formula since we just want to know the absolute amount of a difference and do not need to know whether it is positive or negative (ABS provides the absolute value of a number).   More.....

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
  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.

Ms. Excel- Resident Excel Geek