How often do you receive Excel spreadsheets from coworkers, clients or partners that have your head spinning? Incomplete or confusing spreadsheets often can result in incomplete or confusing data. Thankfully, with the right tools by your side, you can make any Excel worksheet work for you.
For the past few months, our team members Lee Gyomlai and Travis Aguirre have been sharing Excel hacks that they’ve found helpful in their jobs with you. In case you missed their hacks subtracting dates and IndexMatch, take a look here.
This month, after fielding questions from clients who were hoping to better understand data, Betsy Calvillo brings you “IsNumber” and “Text to Columns.”
ISNUMBER()
Purpose:
IsNumber checks whether a value is a number, by returning a result of either “true” or “false.” This can be helpful when someone shares a spreadsheet with you that contains both numbers and formulas and you’d like to ensure the formulas are, in fact, capturing all the numbers.
Why use it?
Numbers formatted as text will not work in mathematical formulas. When you have a large amount of data in a column, this is a simple and effective method to find out which numbers are not read by a formula.
Simply put, “IsNumber” allows you to quickly identify and fix problem areas in data. Likewise, it allows you to understand why there may be variations in the data you’ve received and what your client is telling you about the data.
Finally, “IsNumber” also can give you peace of mind that you’re working with correct data sets.
Limitations:
We haven’t found any yet!
Example and steps to follow:
1. You’ve been provided with a listing of information, where you want to total by location A (See example listing below.) You perform the “sumif” function shown under the formula box to the listing with “Location” and “Amounts.” The amount returned was “8.” From your knowledge of the data, you know the amount at all locations with “A” is over “10,” but you don’t know the exact amount. You reach a conclusion that the formula is not picking up all the data. To identify the cells that are not formatted correctly, as shown below, you would apply the “Isnumber” function.
As you can see by the green arrow, the “Isnumber” formula calls out that the number “4” in cell C20 is contributing to the incorrect result due to incorrect formatting.
2. Apply the function =Isnumber(Cell) in a column beside the information you want to identify, and copy down to the end of the listing.
3. After application of the formula, it is revealed that the original formula is not identifying “4” as a number. You can now select that cell and format it as a number, which will enable you to get the correct outcome.
By following this simple example, you can transfer the same formula to a spreadsheet that may contain thousands of rows of information. By isolating the problematic cells, you may identify the problem areas and quickly ascertain if the result is precise. Overall, “IsNumber” is a beneficial tool for helping you identify which cells are not working to deliver the results you expected.
Speaking of fixing errors in Excel spreadsheets and/or cleaning things up for ease of use, “Text To Columns” is an excellent tool for just that!
TEXT TO COLUMNS:
Purpose:
Have you ever received a report that combines various data types in one unorganized spreadsheet? Would you like to see this same data organized by date, type of item, amount, etc.? “Text to Columns” allows you to separate the contents of one Excel cell into separate columns at each comma, period or other character you specify, so you can do just that.
Why use it?
Reports are not always formatted in an easy-to-use manner, as described above. Dates are often combined with data description and amounts in one cell, but “Text To Columns,” organizes all of this to make the data easier to visualize.
Limitations:
Unfortunately, this tool only recognizes certain characters and may not separate all data. Repeat steps and change the type of delimiter you wish to separate by to try to get around this.
TEXT TO COLUMNS EXAMPLE:
1. Perhaps you are working off a document that has all the information needed in one cell, as shown below. To be able to sort and filter this information, we need to separate the information by hyphen.
2. Select the “Data” tab at the top of the Excel file you’re working within. Inside the “Data” toolbar, you should find the “Text to Columns” icon in the section labeled “Data Tools.”
3. Select the data that will be converted to columns, then select the “Text to Columns” tool. Next, the “Text to Column” Wizard will appear. After reviewing the data in the “preview of selected data” window, you’ll notice that the information can be separated by a hyphen. Choose the option to separate the data by delimiter and select “next.”
4. Step 2 of the Wizard allows you to select the type of delimiter you will want to use to separate the data. The “Delimiters” section of this window has several selections readily available. You’ll want to select “other,” as the hyphen is not one of the selections. Now, you’ll input the delimiter of choice into the blank box. Once you have filled in the type of delimiter, the “Data preview” box at the bottom of the window will have an example of how the information will be presented in each of the columns. If the format looks accurate, press “next.”
5. Step 3 of the Wizard allows you to format each column of the data. If the data contains a mixture of numbers and text, then the “General” format will identify the information in the columns and format them as such. Click “Finish.”
6. After you select the “Finish” button, the data selected will be replaced with the data in separate columns. Rename the columns to identify the information within those columns and the data should be easier to manipulate.
7. Below is the finished table format, where we’ve centered all text, identified the columns and created headers.
Already, this data is much easier to understand, which is just one of the many reasons why we love “Text to Columns.”
Be sure to follow Weinstein Spira on Facebook for new Excel hacks and share the knowledge with your industry friends and peers.
