TJ's Blog
Search results for 'Excel' returned 7 results: Main Blog
Posted by TJ on Friday August 12, 2016 @ 12:28 PM
[Tags: excel, tips, accounting]
From time to time I run into an issue where for some reason the default styles in an excel workbook get corrupted and I unable to add or remove decimal places from cells even by using the standard ->Format Cells->Number or Account->Decimal places drop down. I found an easy to resolve this would be to import the spreadsheet into Google Sheets ( https://docs.google.com ). Doing this may remove some intentional formatting but it will also should fix the issue with default styles not working.
In Google Sheets:
Upload from Excel to Google Sheets ->File->Open-> Upload
Export file from Google Sheets to Excel : File->Download As...->Microsoft Excel
[Tags: excel, tips, accounting]
From time to time I run into an issue where for some reason the default styles in an excel workbook get corrupted and I unable to add or remove decimal places from cells even by using the standard ->Format Cells->Number or Account->Decimal places drop down. I found an easy to resolve this would be to import the spreadsheet into Google Sheets ( https://docs.google.com ). Doing this may remove some intentional formatting but it will also should fix the issue with default styles not working.
In Google Sheets:
Upload from Excel to Google Sheets ->File->Open-> Upload
Export file from Google Sheets to Excel : File->Download As...->Microsoft Excel
Posted by TJ on Friday March 4, 2016 @ 11:53 AM
[Tags: Excel, tips, accounting]
If you manage a partnership you might need to send out individual or personalized statements showing account activity or member financial information. If you already have a table in excel with the data one of the easiest ways to make an individual statement is to add a new tab and create the statement and then add a drop down (data validation) on the statement tab to select each persons name.
To add a drop down
1) From the Data Ribbon select Data Validation
2) Select Allow "list"
3) Your source should be the list of names from the data table you are pulling information from.
Now that you have a dropdown on the statement tab you can use the formula "SUMIF" to retrieve only the specific data from your data table for the name you select from the dropdown box.
Now if you have a lot of names you might want to automate selecting each name and printing the individual statement to the pdf. The below VB Code/macro will do this as long as on your statement tab you have the dropdown in cell A1 and the filename and path in cell A2 (ex: c:/folder/)
Read More...
[Tags: Excel, tips, accounting]
If you manage a partnership you might need to send out individual or personalized statements showing account activity or member financial information. If you already have a table in excel with the data one of the easiest ways to make an individual statement is to add a new tab and create the statement and then add a drop down (data validation) on the statement tab to select each persons name.
To add a drop down
1) From the Data Ribbon select Data Validation
2) Select Allow "list"
3) Your source should be the list of names from the data table you are pulling information from.
Now that you have a dropdown on the statement tab you can use the formula "SUMIF" to retrieve only the specific data from your data table for the name you select from the dropdown box.
Now if you have a lot of names you might want to automate selecting each name and printing the individual statement to the pdf. The below VB Code/macro will do this as long as on your statement tab you have the dropdown in cell A1 and the filename and path in cell A2 (ex: c:/folder/)
Read More...
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.
[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
[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
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)
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
[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.
[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
[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