Work has kept me busy, so this site was forgotten for a while. Now I feel guilty, like a parent who neglects their kids and then buys them stuff to make up for it. So, here’s a quick Excel tip, to make me feel better.

Lets say that you’ve listed, in a row, the months of the year.

Jan, Feb, Mar, etc

Now that you’ve spend the time to type all twelve months (hopefully not becuase you can autofill the months in, but that’s another tip after I neglect you again) you realize that you would rather have the months in one column instead of a row.

A fast way to convert a row of data to a column of data is to use the Copy/Paste Special. Here’s how:

1. Copy your row of data (Jan through Dec).
2. Place the cursor onto the cell where you want to data to go.
3. Select Edit then Paste Special from the Menu.
4. The Paste Special dialog box will appear. Check the box for the Transpose option.

That’s it!

Last week my wife, who is an accountant also, asked me if there was a way to append or prepend a value in a cell. Yes there is.

Assume, for example, that you have a spreadsheet with a long listing (thousands of rows) of account numbers in column A. Further assume that each account number is currently nine digits long (i.e, 123456789) and, for whatever reason, you need to add a “00” in front or in back of the nine digit account number. So, the account number looks like this:

123456789

And, you want it to look like this:

00123456789 or 12345678900

Remember, you’re going to add “00” either to the beginning or the end of the nine digit account number for thousands upon thousands of rows, so you don’t want to do this manually. The quickest why to do this is to use the CONCATENATE function. The function looks something like this:

=CONCATENATE(text1,text2,...)

text1, text2,... are text or numbers that you want to combine together. The text/number will be combined in the order in which you list them. Our example will look like this:

The function in cell C1 is =CONCATENATE(A1,B1), and the result will be 12345678900. The function in cell C3 is =CONCATENATE(B3,A3) and the result will be 00123456789.

Even Shorter

CONCATENATE is a long function to type out. A shorter and faster way to concatenate values is to use the ampersand charger in place of CONCATENATE. The function will change slightly to: =A1&B1 or =B3&A3.

If you want it even shorter, and drop the use of column B altogether, you could type =A1&"00" or ="00"&A3.

Notice that the “00” is now in quotes. Using the ampersand to concatenate is extremely easy and it’s my preferred method for concatenating.

Prosystem fx Engagement Version 4.0.100.1060¹ by default launches multiple instances of Microsoft Excel when you open new work-papers. This is done to support dual computer monitor setups. To disable this feature on your computer (engagement binders and other Engagement users will not be affected) follow the steps below.

1. Open the ProSystem fx Engagement File Room (you should be out of all binders)
2. Select TOOLS from the menu
3. Select OPTIONS (this will launch another window)
4. Select the DATA tab
5. In the field that reads: “Local File room location:” select the entire path, right click, and select COPY.
6. Click the CANCEL button to close the dialog box
7. Close Engagement
8. Right click on the START button in Windows, and select EXPLORE (the Windows file explorer will launch)
9. In the address field, paste the path that was copied in Step 5 above, and press ENTER.
10. Right click on a file called “engage.opt” (you may not see the .opt extension), select OPEN, and open with the Notepad application.
11. In the engage.opt file now opened in Notepad, CRTL+F to launch the Find tool.
12. In the Find What field, type in “separate_excel_launch” (without the quotes).
13. The Find tool will highlight text string that you need to edit. The entire text string should currently read: <SEPARATE_EXCEL_LAUNCH Selected="true"/>
14. Change the text string to read: <SEPARATE_EXCEL_LAUNCH Selected="false"/>
15. Select FILE from the menu
16. Select SAVE.

---

¹ For the uninitiated, Engagement is a paperless document management/work-flow system used primarily by financial statement auditors to manage their audits. This tip will be relevant to a very small number of Microsoft Excel users, but if you use Engagement, it’ll save you a technical support call.

DISCLAIMER: Becareful of what you do, this could mess up the application. You should make a backup copy of the engage.opt file.