www.lostpassword.com offers easy to use password unlocking utilities for Excel 2000 and earlier, as well as server other commercial programs, e.g. Act!, Lotus 1-2-3, etc. We've successfully used their 1-2-3 and Word modules to recover password protected documents.
Category: Excel
Mar 25
Reading a Lotus 1-2-3 Spreadsheet in Excel
Reading a Lotus 1-2-3 spreadsheet in Excel isn't always as easy as it should be. If you're using Excel 5.0 or 7.0, you'll have to save your 1-2-3 file in either the WK1 or WK3 formats. Excel 97 or 2000 can usually read the WK4 format directly. However, if you're using 1-2-3 97, you're out of luck, since the .123 format isn't recognized by Excel. If your 1-2-3 spreadsheet contains formulas that don't have an exact counterpart in Excel, e.g. @XINDEX, Excel will report an error and replace the formula with its value. In some cases Excel will choke on the formatting contained in a 1-2-3 spreadsheet. If so, save the 1-2-3 file in the WK3 format, then delete the associated .FM3 file. You'll then be able to get the data into Excel, at the expense of losing all of your formatting.
Mar 25
Optimizing Excel Spreadsheet Calculations
The Microsoft web site has an article that describes how to improve the calculation speed of your worksheets.
Mar 25
Rounding Numbers to the Nearest 1000
Use the following custom number format #,###, (4 number signs, two commas) to display 15,000,000 as 15,000 (just be sure to include the words 000's omitted somewhere on your spreadsheet)!
Mar 25
Displaying the Name of a Month
Enter a date into any cell, and then choose Format and Cells. Choose Custom on the Number tab. Enter mmmm into the Type field. Use mmm to abbreviate the month, or use ddd and dddd to display days of the week.
Mar 25
Look Ma, No Mouse!
To quickly select a contiguous range, click on the first cell in the range and press SHIFT+CTRL+END. Or, for rows use SHIFT+CTRL+left/right arrow, and for columns use SHIFT+CTRL+up/down arrow.
Mar 22
Rounding Numbers To Nearest Multiple
Use MROUND to round a number off to a specific multiple. If cell A1 contains 12.93, in cell B2 =MROUND(A1,0.05) returns 12.95
Mar 19
What Version of Excel is on Your Desktop?
By David Ringstrom
Here are seven quick Excel tips for: converting data, amortization tables, spanning Excel across multiple monitors, filtering for blank rows in Excel 2010, recovering unsaved workbooks in Excel 2010, identifying duplicate values in Excel 2007/2010, and recovering damaged Excel workbooks.
Data Conversion Tricks
From time to time you may encounter worksheets where dates are stored in yyyy-mm-dd format, such as 2012-12-31 as opposed to 12/31/2012. You can convert such inputs with just a few mouse clicks.
- Select the column or cells containing the dates you wish to convert.
- Choose Data and then Text to Columns.
- Click Next twice, then on the third tab choose Date, and then YMD.
- Click Finish to complete the process.
Instant Amortization Table
You can add an amortization table to any Excel workbook with a few mouse clicks:
- Right click on any worksheet tab and then choose Insert.
- Navigate to the Spreadsheet Solutions tab and then double click on Loan Amortization.
Spanning Excel Across Multiple Monitors
Multiple monitor users often launch two different sessions of Excel when they need to see two spreadsheets side by side. Doing so can be viable in certain circumstances, but typically it’s more effective to have a single Excel session that spans both monitors. To do so, open any version of Excel and then double click on the words Microsoft Excel at the top of the screen. Your Excel window should now only cover part of a single monitor. Position your cursor over the title bar and hold down the left mouse button to move Excel up to the top corner of the screen. Next, use your mouse to drag the right-hand corner of Excel across the second monitor.
To then see two different workbooks side by side, choose View, Arrange All, and then double click Vertical. In Excel 2003 and earlier, choose Window, Arrange, and then double click Vertical.
Excel 2010: Filtering for Blank Rows
The Filter command, which appears on both the Home and Data tabs in Excel 2007 and 2010, allows users to pick and choose which rows to display within a list. However, it can be tedious trying to isolate blank rows from a lengthy spreadsheet. You must click the Filter arrow, unclick All, and then scroll down to the bottom to click Blanks. In Excel 2010, simply type the word Blanks in the search box instead.
Excel 2010: Recovering Unsaved Workbooks
It's painful when you accidentally (or even purposefully) close a workbook without saving and then regret it. In Excel 2010, you can sometimes recover your work. To do so, choose File, Info, click the Manage Versions button, and then choose Recover Unsaved Workbooks. Excel 2010 automatically keeps a copy of many (but not all) unsaved workbooks for a few days.
To make the most of this feature, set your AutoRecover option to two minutes instead of the default of ten. To do so, click File, Options, and then Save. Make sure that Save AutoRecover Information settings is set to two minutes and that Keep the Last Autosaved Version if I Close Without Saving is checked as well.
Identifying Duplicate Values in Excel 2007/2010
Select a range of cells. Then, on the Home tab, click Conditional Formatting, Highlight Cells Rules, and then Duplicate Values. Select a formatting option and then click OK. To isolate duplicate instances, right click on a highlighted cell, then choose Filter, and then Filter by Selected Cell's Color.
Recovering Damaged Excel Workbooks
Many users overlook the Repair Workbook feature in Excel. To access it, navigate to the Open window in the usual manner, and then click once a workbook name. Click the arrow on the right-hand side of the Open button, and then choose Open and Repair. This will sometimes correct problems with a malfunctioning workbook. If this step doesn't resolve your problem, install the free OpenOffice product , and use the Calc spreadsheet program to try to open your damaged Excel workbook.
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 19
Excel Tip: Text Boxes
By David Ringstrom, CPA
The lowly text box is one of many unsung features in all versions of Excel. While it lurks on the Drawing toolbar of Excel 2003 and earlier, the Text Box icon resides prominently on the Insert tab of Excel 2007 and 2010.
Use text boxes anytime you need to put a paragraph or more of text into a spreadsheet. This saves you from having to manually merge cells and wrap text within your worksheet.
In addition, Excel 2007 and 2010 users can create bulleted lists within a text box by selecting text within a text box and making a choice from the right-click menu. To create a text box, simply click the icon, draw a box on your worksheet with your mouse, and then add your text.
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 07
Seven Quick Excel Tips
By David Ringstrom
Here are seven quick Excel tips for: converting data, amortization tables, spanning Excel across multiple monitors, filtering for blank rows in Excel 2010, recovering unsaved workbooks in Excel 2010, identifying duplicate values in Excel 2007/2010, and recovering damaged Excel workbooks.
Data Conversion Tricks
From time to time you may encounter worksheets where dates are stored in yyyy-mm-dd format, such as 2012-12-31 as opposed to 12/31/2012. You can convert such inputs with just a few mouse clicks.
- Select the column or cells containing the dates you wish to convert.
- Choose Data and then Text to Columns.
- Click Next twice, then on the third tab choose Date, and then YMD.
- Click Finish to complete the process.
Instant Amortization Table
You can add an amortization table to any Excel workbook with a few mouse clicks:
- Right click on any worksheet tab and then choose Insert.
- Navigate to the Spreadsheet Solutions tab and then double click on Loan Amortization.
Spanning Excel Across Multiple Monitors
Multiple monitor users often launch two different sessions of Excel when they need to see two spreadsheets side by side. Doing so can be viable in certain circumstances, but typically it’s more effective to have a single Excel session that spans both monitors. To do so, open any version of Excel and then double click on the words Microsoft Excel at the top of the screen. Your Excel window should now only cover part of a single monitor. Position your cursor over the title bar and hold down the left mouse button to move Excel up to the top corner of the screen. Next, use your mouse to drag the right-hand corner of Excel across the second monitor.
To then see two different workbooks side by side, choose View, Arrange All, and then double click Vertical. In Excel 2003 and earlier, choose Window, Arrange, and then double click Vertical.
Excel 2010: Filtering for Blank Rows
The Filter command, which appears on both the Home and Data tabs in Excel 2007 and 2010, allows users to pick and choose which rows to display within a list. However, it can be tedious trying to isolate blank rows from a lengthy spreadsheet. You must click the Filter arrow, unclick All, and then scroll down to the bottom to click Blanks. In Excel 2010, simply type the word Blanks in the search box instead.
Excel 2010: Recovering Unsaved Workbooks
It's painful when you accidentally (or even purposefully) close a workbook without saving and then regret it. In Excel 2010, you can sometimes recover your work. To do so, choose File, Info, click the Manage Versions button, and then choose Recover Unsaved Workbooks. Excel 2010 automatically keeps a copy of many (but not all) unsaved workbooks for a few days.
To make the most of this feature, set your AutoRecover option to two minutes instead of the default of ten. To do so, click File, Options, and then Save. Make sure that Save AutoRecover Information settings is set to two minutes and that Keep the Last Autosaved Version if I Close Without Saving is checked as well.
Identifying Duplicate Values in Excel 2007/2010
Select a range of cells. Then, on the Home tab, click Conditional Formatting, Highlight Cells Rules, and then Duplicate Values. Select a formatting option and then click OK. To isolate duplicate instances, right click on a highlighted cell, then choose Filter, and then Filter by Selected Cell's Color.
Recovering Damaged Excel Workbooks
Many users overlook the Repair Workbook feature in Excel. To access it, navigate to the Open window in the usual manner, and then click once a workbook name. Click the arrow on the right-hand side of the Open button, and then choose Open and Repair. This will sometimes correct problems with a malfunctioning workbook. If this step doesn't resolve your problem, install the free OpenOffice product, and use the Calc spreadsheet program to try to open your damaged Excel workbook.
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 05
Converting Dates In YYYY/MM/DD Format
To convert yyyy/mm/dd to mm/dd/yy, choose Data, Text to Columns, click Next twice, choose Date, select YMD, and click Finish.
Feb 03
Free Spreadsheet-Based Form 1040 Available for 2011 Tax Year
By David H. Ringstrom, CPA
Glenn Reeves of Burlington, Kansas, recently released his fifteenth annual spreadsheet-based version of the U.S. Individual Tax Return, commonly known as Form 1040. Since 1997, Reeves has pursued this “labor of love,” which means he allows any taxpayer to download and use the spreadsheet for free.
Reeves' spreadsheet empowers tax payers to use their tax return spreadsheet applications, such as Microsoft Excel, OpenOffice, or Gnumeric Portable. As you can see in Figure 1, the form closely mirrors the official IRS format, but Reeves doesn't guarantee that the IRS will accept printed versions of the form. He personally files his return online after using the spreadsheet to compute his return.
Figure 1: Reeves has updated his Excel-based version of IRS Form 1040 for the 2011 tax year
The 2011 version of the spreadsheet includes both pages of Form 1040 as well as the following supplemental schedules:
- Schedule A – Itemized Deductions
- Schedule B – Interest and Ordinary Dividends
- Schedule C – Profit or Loss from Business
- Schedule D – Capital Gains and Losses, along with its worksheet
- Schedule E – Supplemental Income and Loss
- Schedule L – Standard Deduction for Certain Filers
- Schedule SE – Self-Employment Tax
- Form 6251 – Alternative Minimum Tax – Individuals
- Form 8949 – Sales and Other Dispositions of Capital Assets
The spreadsheet also includes several worksheets:
- Schedule D Worksheet
- Line 10 – State and Local Income Tax Refund Worksheet
- Lines 16a and 16b – Simplified Method Worksheet taxable annuities and pension benefits
- Lines 20a and 20b – Social Security Benefits Worksheet
- Line 32 – IRA Deduction Worksheet
- Line 44 – Qualified Dividends and Capital Gain Tax Worksheet
- Line 51 – Child Tax Credit Worksheet
Five additional worksheets round out the tool:
- W-2 input forms that support up to four employers for each spouse
- 1099-R retirement input forms for up to four payers for each spouse
- SSA-1099 input form to record Social Security Benefits
- A tax table
- Change log that records revisions to the spreadsheet
All of the worksheets in the 1040 workbook are password protected. Most of the underlying formulas are hidden, but you're free to add new worksheets to the file or create links to other workbooks. Reeves notes that the spreadsheet is constructed in such a fashion that proper use of the spreadsheet means a user won't need to access any of the protected cells. New this year, though, is the ability to add comments to the unprotected cells, which wasn't possible in previous versions. As shown in Figure 2, the spreadsheet also offers some limited error checking.
Figure 2: The spreadsheet offers limited error-checking prompts.
Although Reeves gives the spreadsheet away, he does accept appreciation contributions, which he reports on his tax return as income. He also donates 10 percent of any contributions to his church. Spreadsheet-based versions of Form 1040 are available for tax years 1996 through 2011 on Reeves' website.
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
Feb 02
Excel Tip: Table Feature Enables Effective Analysis
By David H. Ringstrom, CPA
Excel 2007 and 2010 users often overlook the useful Table feature, which can streamline data analysis. This feature is a major overhaul of the List feature that has long lurked on the Data menu of earlier versions of Excel. In this article, I'll introduce you to the Table feature and demonstrate several ways it can help you work faster in Excel.
When you convert a range of data in a worksheet to a table, several things happen simultaneously, as shown in Figure 1:
- Excel places a Filter arrow at the top of each column.
- Excel applies a table-style format, which shades alternating rows.
- A Design tab appears in the ribbon, from which you can manage aspects of the table.
Figure 1: Excel's Table feature simplifies data analysis.
Other benefits of tables include:
- The headings from the first row move up into the worksheet frame when you scroll down if the entire list can't be displayed on the screen. This means you don't have to freeze the worksheet panes to keep the title row in view.
- Pivot tables based on tables have better integrity, as tables automatically expand to incorporate new data that you append. You must still refresh the pivot table when you add data to the table, but you won't need to manually resize the source data range.
- Formulas that you add within the table get copied down the entire column automatically, as shown in Figure 4.
Long-time Excel users may remember a Table command on the Data menu of Excel 2003 and earlier. This command was renamed Data Table in Excel 2007 and later, and it appears under the What-If Analysis button on the Data tab of the ribbon. A Data Table shows you how changing one or two variables in a formula will affect the outcome. Conversely, the Table feature is used to analyze lists of data.
To create a table, follow the numbered steps in Figure 2.
Figure 2: You can convert a list to a table with four mouse clicks.
You can then add a Total Row as shown in Figure 3. Formulas within the Total Row only tally the visible rows, making it easy to view real-time statistics as you filter the table.
Figure 3: The Total Row in a table only tallies the visible rows.
Figure 4 illustrates another special characteristic with regard to formulas that you add to tables. You may wish to turn off the Use Table Names in Formulas setting, which appears in the Formulas section of Excel's Options window – the formula in Figure 4 would appear as =[@[Total Sales]]/[@[Cases Sold]] if I had left this option on.
Figure 4: Formulas that you add to a table are copied down automatically.
At any point, you can convert a table back to a normal range – click the Convert to Range button on the Design tab. Keep in mind that this command doesn't remove the formatting or total row. To save time, you may wish to turn off any undesired table features before you convert your data back to a normal range.
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 31
Instant Amortization Table
Add amortization table to any Excel workbook-Right-click worksheet tab, choose Insert, double-click Loan Amortization on Spreadsheet Solutions tab
Jan 28
Excel 2010 Filtering Trick
Filtering a list for blank rows in Excel 2010? Type a single parentheses “(” in the Search box instead of unclicking Select All and scrolling down.