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!

• Another day, another security flaw in Excel.

• There have been reports for another round of Excel attacks.

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.

Microsoft has posted the a workaround for the disovery of the zero day vulnerability discovered in Excel last week.

Anil thinks Microsoft’s Office 2007 is the bravest update becuase the extreme change in the user interface.

Kiss goodbye to the File menu in Excel and say hello to the “ribbon”. I posted a comment on the blog wondering for keyboard short cuts will still work. Anil confirms with a “yes”.

Microsoft Excel 12 will introduce a new conditonal formatting feature called Data Bars. Data bars are graphical representation of the numbers within a cell; basically it looks like a bar graph behind the numbers in the cell. The data bars settings are found in Excel’s conditional formatting menu.

Data bars remind me of Edward Tufte’s sparklines.

David Gainer, the Group Program Manager for Microsoft Excel has a blog what will discuss the upcoming Excel 12. He says:

I am going to be writing about what’s new in Excel 12…I plan to write this blog from now until around the general availability of Office 12

In his first post, he tells us that E12 will have “1,048,576 rows by 16,384 columns. That’s 1,500% more rows and 6,300% more columns than in Excel 2003”

How exciting! I look forward to reading his blog daily.

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.