Posted by TJ on Friday February 12, 2016 @ 03:01 PM
[Tags: excel, tips, accounting]

Below are some short cuts I use almost every day in Excel when auditing worksheets in excel. There are certainly more shortcuts than I discuss below but these are certainly less commonly discussed:

Ctrl + { Press these two keys together when you are in a cell with a formulated value and Excel will bring you to the sheet and cell it got linked from. If you go nowhere that likely means the cell does not contain a formula or there were too many variables.

Ctrl + } When you use this shortcut on a value Excel will bring you to the sheet and cell where the selected cell is being used in a formula. If you go nowhere that means the cell is not used in a formula or there are too many variables.

Ctrl + ~ When you use this shortcut you can switch displaying showing formulas or values on a sheet.

Posted by TJ on Friday March 25, 2011 @ 06:44 PM
[Tags: guide, excel, accounting]

One my excel nuisances is spreadsheets that link to other external documents. If you plan on sending a file out, I feel that external links, should be avoided at all costs in the professional environment. External links may seem to work fine when the files are all your computer but once you give that file to another party it creates issues because excel can no longer find the file. Also changing the source document after you link to it also can create more issues.

So having said that, here are useful tips for finding and removing external links.
1) The easiest way to remove links in excel is using the "Edit Link" option on the Data ribbon. Here excel will list all the external documents that a spreadsheet is linking to. You can select each file listed then click "break links" and excel will replace the formulas that contain external links with values. The problem with this feature is you will not always know which formulas you are editing and the feature does not work on some links.

2) If you want to view each external link before editing it another option is to search for all external links. This can be achieved by using the "Find and Select" option on the Home ribbon. If you click the "option" button you can select to search the worksheet or the whole workbook. Now in the "find what" box type "[" or ".xls" without quotes.

3) Now if you tried the above and you still have external links this may be due to defined names that exist in workbook. You can see if you have defined names by clicking the "Name Manager" on the Formulas ribbon. Delete any names here that have errors or that are linking to external sources (make sure you correct any of the formulas using these names first, of course).

4) If you still can't find a link there is a handy tool created by Bill Manville that will find all hidden links and give you the option to delete each. You can download the file here: http://www.oaltd.co.uk/MVP/Default.htm

Posted by TJ on Friday March 26, 2010 @ 12:38 PM
[Tags: guide, excel, accounting]

If you are trying to convert a Microsoft Excel 2007 file to open with Excel 2003 you may receive an error that there are "Too Many Cell Formats". After spending quite sometime I found a fix for this. You need to open the file in the Excel 2007 and run the macro below and then resave the file. Before you do anything make sure you save your file in case you don't like the results.

OPTION 1: The macro below will show a prompt "Delete Style ____" for each style which and you will need to click "YES" for each style. If you are not concerned about deleting a valid style you can use OPTION 2

Sub DeleteStyles()
'
' DeleteStyles Macro
'
Dim styT As Style
Dim intRet As Integer

For Each styT In ActiveWorkbook.Styles
If Not styT.BuiltIn Then
intRet = MsgBox("Delete style '" & styT.Name & "'?", vbYesNo)
If intRet = vbYes Then styT.Delete
End If
Next styT
End Sub

OPTION 2:The macro below will delete all unused styles without a prompt. This is faster but can be more dangerous than OPTION 1. Make sure you save the file both before and after running the macro (thanks to Nick)

Sub DeleteStyles()
'
' DeleteStyles Macro
'
Dim styT As Style
Dim intRet As Integer

For Each styT In ActiveWorkbook.Styles
If Not styT.BuiltIn Then
styT.Delete
End If
Next styT
End Sub

OPTION 3: For those that do not want to deal with Macro's there is a tool you can download that I also find very helpful and easy to use. The tool removes unused cell styles, "stubborn styles" that you can't delete through Excel UI, bad named ranges, named ranges with external references, and unhides non-system created hidden named ranges. Download XLCleaner here: https://skydrive.live.com/?cid=53e1d37f76f69444&id=53E1D37F76F69444!526&sc=documents

Posted by TJ on Tuesday October 21, 2008 @ 12:44 PM
[Tags: guide, accounting, excel]

Let's see how this goes...

I am a CPA and use Microsoft Excel spreadsheet software everyday. Being advanced in Excel makes my job extremely easier. I figured I'd see if there is anyone out there who has quick questions in excel that I can help. Hey what can I say I'm a generous guy.

To ask your question, use the comments form below! I will post responses to your questions below. I do not answer replies through email, as posting the answer here will help others who may have the same question as you.

Ask away...

Note: Please provide enough information for me to be able answer your question (the more info the better). The more effort you put into your question the more effort I will put into my response.

Posted by TJ on Wednesday August 29, 2007 @ 10:04 AM
[Tags: excel, hints, internet]

I use the following macro to save my timesheet using the filename and path in cell "A1"(ie:C:datafilename.xls).

You can also include today's date using concatenate by including the following in Cell A1

=CONCATENATE("c:datafilename_",MONTH(NOW()),"-",DAY(NOW()),"-",YEAR(NOW()),".xls")

Of course you can replace cell A1 in the following code with any cell you want as long as it contains a valid filename and path.

Sub 

savefile()

' SaveFile Macro

' Macro recorded 4/16/2007 by tmiller of tjshome.com

'save a copy of current spreadheet using filename in cell 'TimeFile'

    TimeFile = Range("A1").Value

    ActiveWorkbook.SaveAs Filename:=TimeFile, FileFormat:= _

        xlNormal, Password:="", WriteResPassword:="", 

        ReadOnlyRecommended:=False _, CreateBackup:=False

End Sub