Category: Excel

Mar 24

Transform multiple-line cell contents into columns in Excel

By David H. Ringstrom, CPA


Sometimes in Excel you may encounter a situation such as that shown in Figure 1, where each cell contains multiple lines of data. It's not a huge task to manually type a few records, but what if you have dozens or hundreds of records in this format? In this article I'll explain some simple techniques that can make quick work of transforming this data into columns.


Figure 1: It can be difficult to convert multiple lines of text in a single cell into columns
Expert Tip: If you want to recreate data similar to what you see in Figure 1, type a line of text in a cell, and then press Alt-Enter to add a new line.
The first step in the process is to use the SUBSTITUTE function to replace the non-printing new line character with another character. You must use a formula, because Excel's Find and Replace feature doesn't allow you to replace non-printing characters such as new lines. In a pinch, you can use Word to carry out such replacements, as shown in Figure 2, but it's easy to use SUBSTITUTE.


Figure 2: Word allows you to find and replace non-printing characters, but Excel doesn't.
The SUBSTITUTE function has four arguments:
·                     text – In this case, text will be a cell that contains new line characters
·                     old_text – Excel's CHAR function can generate the non-printable new line character
·                     new_text – I like to use the | symbol (often referred to as the pipe symbol) as a unique identifier in place of the new line character. This symbol usually appears above the \ key on your keyboard.
·                     instance_num – This is an optional argument that I'll omit in this case because I want to replace all of the new line characters with the pipe symbol. If I put a number here, only that quantity of new line characters would be replaced.


Excel's CHAR function just has a single argument wherein you indicate the number of the character that you want to return. New line characters are number 10, so I'll use CHAR(10) to indicate a new line. Here is a chart of all of the character symbols.


Now I'm ready to enter the formula shown in Figure 2 into cell B1. Click on cell B1, and then double-click the Fill Handle in the lower right-hand corner of the cell to copy the formula down through cell B5. As you can see in Figure 3, the SUBSTITUTE function takes the data from its original display in multiple rows and puts it into a single row, with a | symbol in between data that was on each row.


Figure 3: The SUBSTITUTE function transforms the multi-line data into a single line.
Now select cells B1 through B5, and then press Ctrl-C to copy the range to the clipboard. Right-click on cell B1, choose Paste Special, and then Values. Leave cells B1 through B5 selected, and choose Data, and then Text to Columns.


As shown in Figure 4, choose Delimited, and then click Next. Then, as shown in Figure 5, choose Other, and enter the | symbol. You can clear the checkbox for Tab, or leave it clicked – this won't have any impact if your data doesn't contain tab characters. Click Finish to complete the wizard steps. As you can see in Figure 6, the data is now transformed into columns!


Figure 4: Choose Delimited on the first screen of the Text to Columns wizard.


Figure 5: Choose Other, specify | as the separator, and then click Finish.


Figure 6: Text to columns transformed the data from rows within a single cell into columns.



A previous version of this article first appeared on www.accountingweb.com .


About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Mar 23

Some unlikely uses for Excel’s SUMPRODUCT function

By David H. Ringstrom, CPA


While you may not be aware of Excel's SUMPRODUCT function, those in the know often use it for its stated purpose: multiplying two ranges together and then summing the total.
As shown in Figure 1, a single formula using SUMPRODUCT can replace the eight formulas listed in Cells D2 through D9. The SUMPRODUCT function multiplies the values in Column B by the values in Column C on a row-by-row basis, and then sums the total.
Although this is helpful, you might not be aware that SUMPRODUCT also can function like VLOOKUP or SUMIF on steroids.


Figure 1: SUMPRODUCT multiplies values together and sums the products, but can also do more.
Excel's VLOOKUP function allows you to look up data from a table based on criteria that you specify, and has four arguments:
  • lookup_value – This is the data that you want to look for in the first column of the table array.
  • table array – The table array is a range of two or more columns.
  • col_index_num – This argument allows you to specify the column within the table array for which you want to return data.
  • range_lookup – In this position you indicate FALSE if you're seeking an exact match for the lookup_value, or TRUE if you want an approximate match. For instance, you'd use FALSE to look up the price of an inventory item, or TRUE if you're determining which tax bracket an income level falls into.


Figure 2: VLOOKUP allows you to look up a single value based on a single criteria.
As shown in Figure 2, VLOOKUP returns $85,106 from Column E for the first instance of Apples that appears in Column D. The additional instances of Apples are ignored. If you need to add up multiple values based on specific criteria, then SUMIF is a better choice. This function has three arguments:
  • range – This is a column or row where you want to look for specified criteria.
  • criteria – This is the same as the lookup_value for VLOOKUP.
  • sum_range – This is a column or row from which you want to add up numbers whenever the criteria is found in the range.


Figure 3: SUMIF allows you to add up multiple items, but also is limited to a single criteria.


Figure 4: SUMPRODUCT can return the same results as SUMIF.
 
As shown in Figure 3, SUMIF returns $396,495. In this case, every time it found a match in Column D on the word Apples, it added up the corresponding value in Column E. Like VLOOKUP, you can only search based on a single criteria. Fortunately, SUMPRODUCT allows you to add up values based on multiple criteria.
However, let's first use SUMPRODUCT to match based on a single criteria, as shown in Figure 4. In this case it returns the same result as SUMIF because we only provided a single criteria.
Conversely, in Figure 5, SUMPRODUCT returns $272,584 because we specified that we only want sales for apples sold in North Georgia.


Figure 5: SUMPRODUCT also can return results based on multiple criteria.
As shown in Figure 5, the formula looks at Cells A2 through A19 for the words North GA, and at Cells D2 through D19 for the word Apples. When both criteria are met, SUMPRODUCT adds up the corresponding values from Cells E2 through E19.


Figure 6: SUMPRODUCT also can return the number of matches that meet criteria you specify.
Finally, as shown in Figure 6, if you only specify criteria and omit the range to sum, then SUMPRODUCT returns the number of items that match the criteria that you specify.



A previous version of this article first appeared on www.accountingweb.com .


About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Mar 23

What’s the FREQUENCY? Using Excel’s FREQUENCY function

By David H. Ringstrom, CPA


From time to time you may want to determine how items fall within specific ranges. For instance, a teacher may wish to know how many students earned A's, B's, C's, and so on. A real estate portfolio manager may wish to know how many buildings fall within ranges of 250,000, 500,000, and one million square feet, respectively. In such cases, Excel's FREQUENCY function is the right tool for the job.


The FREQUENCY function has two arguments:
  •  data_array – a range of cells containing numeric values
  • bins_array –a range of cells containing bins into which the numeric values should be grouped
                    
FREQUENCY has a special characteristic, in that you don't simply type it into a cell and press Enter. Instead, you must select the cells where you want to put the FREQUENCY function, type the formula, and then press Ctrl-Shift-Enter. If you simply press Enter, then FREQUENCY may return an incorrect result. Let's look at a simple example.
As shown in Figure 1, let's assume that a teacher wishes to determine how many students earned each letter grade. Columns A and B of Figure 1 contain student names and grades. Columns D and E contain letter grades and the top value of each letter grade range.


Figure 1: Cells B1:B10 will serve as the data_array, while cells E1:E5 will serve as the bins_array.
Now that we've established our data, we're ready to use the FREQUENCY function. First, we'll select cells F1 through F5, as shown in Figure 2.


Figure 2: Select cells F1 through F5 before you type the FREQUENCY function.
 Once you've selected cells F1 through F5, type the formula shown in Figure 3, and then press Ctrl-Shift-Enter. FREQUENCY will not function correctly if you simply press Enter and try to copy the formula to the adjacent cells.


Figure 3: Be sure to press Ctrl-Shift-Enter after you complete the FREQUENCY function.
As shown in Figure 4, when you press Ctrl-Shift-Enter, Excel fills cells F1 through F5 with the FREQUENCY function. Also notice the curly brackets that Excel adds around the formula. These indicate an array function, and these brackets can only be added by pressing Ctrl-Shift-Enter after you type or edit the formula. Unlike most formulas that aggregate results into a single cell, FREQUENCY requires you to select a multiple cell range in order to function correctly.


Figure 4:  Excel automatically fills cells F1 through F5 and adds curly brackets around FREQUENCY.
 
However, FREQUENCY isn't just for teachers. As I mentioned at the start of the article, a portfolio manager might wish to know how many buildings are defined as small, medium, or large, based on square footages of say 250,000, 500,000, and one million. Figure 5 shows an example.


Figure 5: Press Ctrl-Shift-Enter after you enter the formula shown in Figure 5.



A previous version of this article first appeared on www.accountingweb.com .


About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Mar 23

Excel Tip: Converting numbers formatted as <1> to -1

By David H. Ringstrom, CPA


From time to time you may encounter financial data where negative numbers are enclosed in brackets, such as <100>. In most cases, Excel treats such numbers as text, which means such numbers won't be included in totals or other arithmetic functions.
Typically such numbers will be left-aligned, as shown in Figure 1.


Figure 1: Text-based numbers are typically left-aligned.
In this article I'll discuss two approaches you can use to convert such text-based inputs to negative numbers. I'll also share a formatting technique you can use if you want to enclose negative numbers in such brackets instead of parentheses.


The first approach for converting a number like <1> to -1 involves Excel's Find and Replace feature:
  1. Select the range of cells that contain numbers surrounded by < >. It doesn't matter if you select cells that have numbers without brackets, but it's helpful to limit the area that you're searching to avoid unintended replacements.
  2. In any version of Excel, press Ctrl-H to display the Find and Replace dialog box shown in Figure 1.
  3. As shown in Figure 2:
                    a.      Enter < in the Find What field
                    b.      Enter – in the Replace With field
                    c.      Click Replace All

Figure 2: Replace < characters with a minus sign.

 
      4.      Press Ctrl-H again, and as shown in Figure 3:
                    a.       Enter > in the Find What field
                    b.      Leave the Replace With field blank
                    c.       Click Replace All
 


Figure 3: Replace > characters with a blank field.
 
At this point all of the numbers surrounded by < > will be negative numbers and no longer treated as text.
Alternatively, you can tackle this problem formulaically. To do so, I'll use several different Excel functions:
  • IF – The IF function allows us to carry out a true/false test, return a result, or perform a calculation based on the result of the test. IF has three arguments: logical_test, value_if_true, and value_if_false.
  • ISNUMBER – The ISNUMBER function allows us to test whether a cell contains a number. This function has a single value argument, and returns TRUE if a cell contains a number or FALSE if it contains text or is blank.
  • VALUE – The VALUE function converts a number stored as text into a value, and has a single text argument.
  • MID – The MID function allows us to extract text from the middle of a string, which is another way to refer to text within a cell. This function has three arguments: text, start_num, and num_chars.
  • LEN – The LEN function returns the length of text within a cell, and has a single text argument.
Now let's put these functions together into a single formula.
1.      As shown in Figure 1, enter these inputs into a blank worksheet:
                    A1: <1>
                    A2: 20
                    A3: <30>
                    A4: 400
                    A5: <5,000>
2.      Enter this formula in Cell B2, as shown in Figure 4:
=IF(ISNUMBER(A1),A1,-VALUE(MID(A1,2,LEN(A1)-2)))
 
Figure 4: You can use a formula to convert text-based numbers to values.
 
As discussed above, IF has three arguments:
  • logical_test: the ISNUMBER function determines if the data in Cell A1 is a number or not.
  • value_if_true: If ISNUMBER returns true, then Cell A1 contains a number, so I'll simply return that value.
  • value_if_false: If ISNUMBER returns false, I know that I have a number surrounded by < > that I wish to remove. In this case I'll use the VALUE and MID functions together.
As shown above, MID has three arguments:
  • text – In this case, I refer to Cell A1 for the text I wish to shorten
  • start_num – I know that I want to eliminate the starting < character, so I instruct MID to start at the second position
  • num_char – I also want to eliminate the trailing >, so I'll use the LEN function to determine how long the text in Cell A1 is, and then subtract 2 from that number. Thus LEN(A1)-2 would return 1 for Cell A1. LEN(A3)-2 returns 2, and LEN(A5,2) returns 5 (the comma counts as a character).
The MID function returns a text-based number, which I convert using the VALUE function. I added a minus sign before VALUE to return a negative number.


Finally, I promised to share a technique that you can use if you want to use < > instead of parentheses to format a spreadsheet:
  • In any version of Excel, press Ctrl-F1 to display the Format Cells dialog box.
  • Choose Custom from the Number tab.
  • Erase the Type field, and enter this format code, as shown in Figure 4:
#,##0.00_);<#,##0.00>
Alternatively, use this code if you want to format the numbers with dollar signs:
$#,##0.00_);<$#,##0.00>
Or, insert [Red] if you with to make negative numbers red:
#,##0.00_);[Red]<#,##0.00>




A previous version of this article first appeared on www.accountingweb.com .


About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Mar 23

Understanding the Undo option in Excel

By David H. Ringstrom


One of my favorite features in Excel is the Undo feature, which as you might expect, allows you to undo the last action that you carried out in Excel. Indeed, you can generally undo multiple steps in Excel. However, there are some caveats to this functionality, as well as a couple tricks you may not know.


The keyboard shortcut that I use most often in Excel is Ctrl-Z, which is the shortcut for Undo. I can press it repeatedly to undo several actions. In Excel version 2003 or earlier, I can undo up to the last 16 steps that I've carried out. This list of 16 steps is known as the Undo Stack. However, when I'm using Excel 2007 or 2010, I have a much larger Undo Stack available to me: I can undo dozens of recent actions.


Excel 2003 and earlier versions also have another key restriction related to the Undo feature. When you save your workbook in these versions of Excel, the Undo Stack is erased, meaning you lose the ability to undo any actions you carried out prior to saving. Excel 2007 and 2010 don't have this limitation, which means you can save your workbook, and then still undo previous actions.


Many users rely on the Undo button on the Excel 2003 toolbar, or the Excel 2007/2010 Quick Access toolbar. However, a lot of users don't realize that the Undo button has a drop-down menu, as shown in Figure 1. When you click the menu, you can undo multiple actions at once by selecting a group of items from the list. You are, however, limited to choosing a consecutive list of items from the top down, and you can't skip items in between. However, this also allows you to see exactly what actions will be undone.


Figure 1: You can select multiple steps to Undo.
Keep in mind that in all versions of Excel there are certain actions that will clear the Undo stack. As previously mentioned, saving a workbook in Excel 2003 and earlier will clear the Undo Stack. In any version of Excel, the Undo Stack will be erased if you delete a worksheet from a workbook, or run a macro. Always be sure to save your work before you carry out either of these actions if you want to preserve a fall-back position in case you encounter unexpected ramifications.


A sister function to Undo is Redo, which has a keyboard shortcut of Ctrl-Y. The Redo toolbar button has a drop-down menu just like Undo. In fact, you can undo, and then redo, one or more actions. When necessary, this allows you to roll back the spreadsheet to how it looked a few steps prior, and then roll it forward to your latest update.



A previous version of this article first appeared on www.accountingweb.com .


About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Jan 05

Trick For Hiding Rows

In Excel 2007, save a couple clicks when hiding rows or columns: choose Format, Row Height or Column Width, and enter 0.

Jan 05

Navigation Menu Trick

Right-click on the navigation arrows in the lower left-hand corner of the screen to display a worksheet selection menu.

Dec 16

Update Multiple Cells At Once

Update multiple cells at once: select two or more cells, type an entry in the first cell, and then press Ctrl-Enter.

Dec 08

Excel Tip: Hide an Excel Worksheet in Plain Sight

Excel Tip: Hide an Excel Worksheet in Plain Sight


By David Ringstrom, CPA


As you’re probably aware, it’s simple to hide a worksheet in Excel 2007: right-click on the worksheet tab, and then choose Hide. To unhide the worksheet, right-click on any worksheet tab, choose Unhide, and then select the sheet you wish to make visible. It’s a little more effort in earlier versions of Excel: choose Format, Sheet, and then Hide or Unhide to change the visible status of a worksheet.

Since most users know these tricks, hiding a worksheet is sometimes of little value, because someone else can simply unhide it. Protecting a workbook makes it harder, but savvy users then know that you have something to hide. In such cases, a user might visit www.lostpassword.com or search the Internet to secure an unlocking utility. However, you can take this arms race to new levels by making a worksheet “very hidden,” which will make it appear as if there are no hidden worksheets within your workbook. To do so, you must access the Visual Basic Editor:

 

  • Excel 2007: Click the Visual Basic icon in the Code section of the Developer tab of the ribbon. If you don’t have a Developer tab, click the Office button, and then choose Excel options. In the Popular section, choose Show Developer Tab in Ribbon, and then click OK.
  • Excel 2003 and earlier: Choose Tools, Macro, and then Visual Basic Editor.

Keyboard shortcut: Press Alt-F11 to access the Visual Basic Editor in any version of Excel.

Within the Visual Basic Editor you’ll need to make sure that both the Project Explorer and Properties Window are displayed, as shown in Figure 1. If you don’t see either of these, choose View, and then Project Explorer and/or Properties Window, as needed.

Figure 1: You may have to turn the Project Explorer and Properties windows on.

You’re now ready to hide an Excel worksheet in such a way that the vast majority of Excel users will never find it:

 

  1. Locate the workbook in question within the Project Explorer window. Click once on the file name if you don’t see a Microsoft Excel Objects folder.
  2. Within the Microsoft Excel Objects folder for a given workbook, you’ll see a list of worksheets, as shown in Figure 2. Click once on the worksheet that you wish to hide.

Figure 2: The Properties Window allows you to change the visible property of a worksheet.

 

  1. Set the Visible property in the Properties Window to xlSheetVeryHidden, as shown in Figure 2. Note that by default the next worksheet on the list will be selected once you change this property, so it may appear that your change didn’t take effect. However, if you click on the worksheet name again in the Project Explorer window, you’ll see that the xlSheetVeryHidden property is set.
  2. Choose File, and then Close and Return to Excel.

To test your handiwork, return to Excel, and try to unhide the worksheet. As shown in Figure 3, you should find that the Unhide command is disabled, even though the workbook contains a hidden worksheet. To make the sheet visible again, return to the Visual Basic Editor and set the Visible property for that worksheet back to xlSheetVisible.

Figure 3: The xlSheetVeryHidden will prevent the average user from discovering your hidden worksheets.




A previous version of this article first appeared on www.accountingweb.com .

About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Dec 05

Copying Cell Contents Down

Double-click fill handle to copy current cell down, stops at the first blank in adjacent column. Or select cells & press Ctrl-D.

Dec 02

AND() Function

The AND function allows you to tests for two or more conditions in an IF statement. =IF(AND(A1>=1,A1<=100),"Between 1-100","Other")

Nov 13

Concatenation Trick

Use an ampersand instead of the CONCATENATE function. The formula =A1&”-“&B1 is much easier than =CONCATENATE(A1,”-“,B1)

Nov 12

Formatting Sets Of Rows

Use this formula with Conditional Formatting to highlight alternating sets of 5 rows: =MOD(CEILING(ROW(),5),2)

Oct 29

Three tricks for formatting rows in Excel

By David H. Ringstrom, CPA



I'm sure that you've worked with data sets in Excel where the information starts blurring together. In this article I discuss several techniques that I use avoid getting lost in the forest. First I'll briefly describe the Table feature in Excel 2007 and Excel 2010, and then share some conditional formatting tricks for anyone that's still using an earlier version of Excel.
It's easy to change the color of every other row in an Excel 2007 or Excel 2010 worksheet:
1.     Select a single cell within your list of data.
2.     Choose Format as Table from the Styles section of the Home ribbon, and then choose a color scheme, as shown in Figure 1.
3.     As shown in Figure 2, Excel will automatically determine the cell coordinates of your table. If you choose the My Table Has Headers option, then Excel will add Filter arrows at the top. As an added bonus, if your table is longer than what you can see on a single screen, your header row contents will move into the worksheet frame when you scroll down, as shown in Figure 3.
4.     To eliminate the table, choose Convert to Range in the Tools section of the Design tab that appears when you click on the table. The formatting remains in place, so to eliminate it you can either choose new formatting, or use the Clear Formatting command in the Editing section of the Home ribbon.
Figure 1: Excel 2007's Format as Table feature.
 
Figure 2: Excel automatically determines the cell coordinates of your table.
Figure 3: Your header row appears in the worksheet frame when you scroll beyond the first screen.
 
The Table feature is a fast way to format your data, but let's say that you need more control over your formatting, such highlighting your data in groups of 5. You can use the Conditional Formatting feature in Excel 2007 or 2010 to do so:
1.     Select the cells that you wish to format.
2.     Click the Conditional Formatting button in the Styles section of the Home ribbon, and then choose New Rule, as shown in Figure 4
Figure 4: Conditional Formatting appears on the Home tab of the ribbon.
 
3.     Select Use a Formula to Determine Which Cells to Format, and then enter this formula:
4.     Click the Format button to assign the formatting of your choice, and then click OK.
=MOD(CEILING(ROW(),5),2)=0
5.     As shown in Figure 5, alternating blocks of 5 rows will be highlighted.
Figure 5: Conditional formatting allows us to highlight data in blocks of 5 rows at a time.
 
Note: Use this formula with Conditional Formatting if you wish to highlight every other row:
=MOD(ROW(),2)
In Excel 2003 or earlier, select the cells you wish to highlight, choose Format, and then Conditional Formatting. Change Condition 1 to Formula is and use one of the formulas shown above.
Here's a quick rundown of how these formulas work:
·         ROW returns the current row number. This function allows you to optionally specify an address argument. However, in this case we're referring to the current row, so there's no need to provide a cell address.
·         CEILING rounds the row number up to the next multiple of 5 (or whatever number you specify). There are two arguments: number, and significance. We use ROW() to provide the number, and significance is the multiple that we want to round the row number to.
·         MOD returns the remainder from a division calculation, and has two arguments: number and divisor. In the case of the first formula, the results of the CEILING function are divided by 2. This means that rows 1 through 5 would round up to 5, and since 2 does not divide evenly into 5, MOD returns 1. Since we added =0 to the end of our formula, the Conditional Formatting feature in turn formats rows where MOD returns 0 instead of 1. Thus rows 1 through 5 don't get formatted, while 6 through 10 do, while 11 through 15 don't, and so on.



A previous version of this article first appeared on www.accountingweb.com .


About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Oct 19

Create A Series Of Month End Dates

Create month-end date series- Cell A1: 10/31/09, Cell A2: =DATE(YEAR(A1),MONTH(A1)+2,0), copy down as needed.