Excel Zoom

If you have ever used Excel to gather information from people in a pre-defined form setup, you know it can be frustrating when the responses contain added rows, columns, comments out of sight, etc. The purpose of taking the time to set up a form is so that you can get your data in a consistent format/layout.

It’s actually pretty easy to protect your worksheet from people who might want to get creative with your hard work.

Here’s the steps to lock all cells in your workbook, except for the few individual cells you want people to input information to.

1. Click the box to the left of column A (in between column A and row 1). This will select all cells.
2. Right click the same box – select “Format Cells” then click the “Protection” tab.
3. Make sure the “Locked” check box is checked.
4. Click “OK” These first few steps just made sure that all cells are locked. See steps 5-6 for unlocking individual cells.
5. Right click just the cell(s) you wish to have editable by the end user (i.e. unlocked).
6. Select “Format Cells” then click the “Protection” tab. Then un-check the “Locked” box. This will “unlock” the cells that you want the end user to be able to edit.
7. In newer versions of Excel (2007+), click the “Review” tab, then click “Protect Sheet” under the “Changes” group.
   In older versions of Excel (97-2003), this is found under the “Tools” menu, then “Protection”.
8. If you wish, enter a password in the prompt to be used to un-protect the sheet.
   Note: If no password is entered, anyone can repeat step #7 to un-protect the sheet.
9. Under “Allow all users of this worksheet to:” un-check all boxes except for “Select unlocked cells” then click OK.

Users will now only be able to select and input into the cells that were unlocked in step #6. They won’t be able to insert or delete columns or rows, add comments, or any of the other actions listed in the Protect Sheet window.

It might also be a good idea to format your worksheet in such a way that the end user will know which cells can be (or should be) edited. For example, a different cell background or border color might be a good visual cue to someone that a cell is editable. Alternatively, you could type some generic text (i.e. “First Name”, “Last Name”, “Address”, etc.) into the cells, so  the users will know what is expected of them in the cells.

Now what happens if you enter a password, want to go back and edit your worksheet, but forget what the password was? Well in most cases, you are stuck. You won’t be able to edit anything other than the unlocked cells. That is, of course, unless you know how to crack the password.

If you have ever received the “Too many different cell formats” error message in Excel, stop what you’re doing, take a look around your workbook and ask yourself “are all these cell formats really useful?”  After you answer the inevitable “no”, smack yourself in the back of the head for creating such a mess!  Just kidding, don’t actually smack yourself, but apologize to your co-workers for making them put up with your “creativity”.

The reason why you got this error is because an Excel file can only have approximately 4,000 different combinations of cell formats.  At first this might seem like a lot, but think about all the unique formatting characteristics that can be applied to a cell.

• Fonts: including the font, font size, bold, italic, underline, strikethrough, superscript, subscript, color, etc.
• Borders: including which side of the cell has a border (top, left, right, or bottom), border color, border thickness (or weight), etc.
• Fills: including fill color, and patterns.
• Number formatting: such as General, Number, Currency, Accounting, Date, Time, Percentage, Fraction, Scientific, Text, Special, or Custom.  Not to mention the number of decimal places shown.
• Alignment: Top, bottom, left, right, centered, centered across selection, indented, orientation degrees, wrapped text, shrink to fit, merged cells, text direction, etc.

Any unique combination of the above cell formats counts towards the 4,000 limit, however if several cells share exactly the same formatting, it only counts as one.

For example, assuming that all other formats are the same, a 3×3 cell “boxed” with a border going around it would have nine different cell formats, considering that no cell in the group would share the same side border formatting (see below).

 To fix this, simplify the formats of the cells in your file, by using some of the suggestions below:

• Use only one or two standard fonts.  For example, if you want your headings to be bold, and the data to be in a regular style, do so consistently.
• Use consistent borders in your worksheets.  Maybe just box in headings and your data points to make it easier for your files users to read.
• Clear out unnecessary fill colors and patterns.
• Make sure your numbers are consistently presented (i.e. percentages are shown as such with the same number of decimal points, dollars are shown in the currency, or accounting format, etc.).

Also, consider clearing the formats of blank cells.  To do this, click Edit | Go To | Special | Blanks.  This will highlight all cells that do not contain data.  Next, click Edit | Clear | Formats (or All).  This will clear all the blank cells of any formatting that have been applied to them.  Be careful however, as this could have unintended consequences if you’ve filled the cells in a row or column to act as a “border” between sections in your worksheet, or if you’ve used the Center Across Selection formatting.

After done simplifying your cell formats across your entire file, save, close, and then re-open before adding any new cell formats.

I’ve gotten a few requests for a way to help transition to Excel 2007 from earlier versions of Excel.  As anyone who first tries using Excel 2007 will notice, the traditional File, Edit, View, etc. menus are no longer available.  Instead there is a ribbon, which acts like the old menus.

To help Excel 2007 newbies make the transition, I’ve designed a quick reference mapping of the Excel 2003 menus to the Excel 2007 ribbon on a convenient mouse pad.  For example, if you previously went to Format | Cells to access the format cells menu, you would go to Home Tab | Cells Group | Format in Excel 2007.

If you think it would help, check it out at CafePress.com by clicking on the image below.

Excel 2007 Mousepad

Let me preface this article by saying that this will not help you recover lost data, or gain access to protected data that you otherwise wouldn’t have access to.  What it will do is allow you to unlock a password protected worksheet in Excel, so that you can edit it as necessary.  Simply put, if you don’t already have access to the worksheet, this macro will not help you.

Now that the disclaimer is out of the way, let’s figure out why people password protect worksheets to begin with.  By protecting a worksheet and the contents of locked cells, users can keep themselves or other users from:

• accidentally (or intentionally) removing formulas, or other contents of locked cells
• adding or deleting rows and columns
• changing cell, column or row formats
• sorting data
• using AutoFilter or PivotTable reports
• editing objects or scenarios

Protecting a worksheet will not keep users from

• editing any unlocked cells in the worksheet
• viewing all data in the worksheet, regardless of if it is in a locked cell or not

Being able to unlock a password protected worksheet is useful, when

• you have forgotten the password on your own worksheet
• a co-worker, or other user has password protected a worksheet that you now need to edit, and they are not available to unlock the sheet for you
• you have a need to perform an analysis of the data in a password protected worksheet, but are unable to do so due to the locked cells
• you would like to sort/filter the data in a password protected worksheet, or create a PivotTable report from the data

Since Microsoft Excel is not a “secure” program, it is very easy to unlock the password of a password protected worksheet.  This is because there are several different combinations of passwords that Excel will accept to unlock the worksheet.  For example, a worksheet with the password “treehouse”, can also be unlocked with the password “AAAABAABBBB/”.  The opposite is also true (i.e. protecting with the password “AAAABAABBBB/” can be unlocked with the password “treehouse”).  Go ahead; try locking a worksheet with the password treehouse, and use AAAABAABBBB/ to unlock it.

The macro code below will “unlock” one worksheet at a time, using the method used above.  It won’t provide you with the actual password someone typed in, but rather a random sequence of letters or symbols that will work to unlock the worksheet.


Sub PasswordRecovery()
'
'MACROS BY EXCELZOOM.COM
Dim i As Integer, j As Integer, k As Integer
Dim l As Integer, m As Integer, n As Integer
Dim i1 As Integer, i2 As Integer, i3 As Integer
Dim i4 As Integer, i5 As Integer, i6 As Integer
On Error Resume Next
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If ActiveSheet.ProtectContents = False Then
MsgBox "One usable password is " & Chr(i) & Chr(j) & _
Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub


Recently I found myself deciding if I should sell some stock in order to provide a little extra cash to my bank account.  I purchased shares of the company’s stock four times in various quantities, and at varying prices.  Due to the downturn in the stock market recently the prices dropped steadily, so I was able to purchase some of the shares at a low price.  The company has done a little better in recent months and has produced a gain for some of the shares I bought at the lower price.

The question I want to solve is: if I sell all the shares that have produced a gain, how many of the shares should I sell at a loss in order to offset the gains, and the resulting taxes?

To do this, I set up my spreadsheet like the one below:

Note: these are not the actual amounts I used in my own personal situation. They are only provided for illustration purposes.

Download the example file here.

In cell F8 (labeled “Losses to Break Even” in cell E8), I have the following formula: =(F9*B2)-(F9*D2), which I want to equal the total gains amount, of $1,936.10.  Right now it is showing a zero value because I haven’t determined how many shares need to be sold at a loss to equal the gains.

To do this, select cell F8, then click Tools | Goal Seek.  In the box that pops up, enter the following:

• Set cell: Keep as F8.  If it isn’t F8, enter F8 here.
• To value: Enter 1,936.10, as this is the value we wish to set the losses to.
• By changing cell: Enter cell F9, as you want to determine how many shares need to be sold in order to break even.
• Click OK

The result will tell you that you should sell 43.54 shares in order to break even.

For more information on Excel’s Goal Seek feature, take a look at the post Goal Seek to Solve a Problem.

Excel, by default comes with a standard pre-selected color palette, which is useful, however there are times when those standard colors just won’t do.

It’s possible that your company’s colors are not included in the standard colors found in Excel.  If you’d like to use them in your workbook, you’ll need to change the standard colors.

In order to do this, select Tools | Options | Colors tab (in pre-2007 versions of Excel), or Microsoft Office Button | Excel Options | Save | Colors (in Excel 2007). 

Next, click on the color you wish to change, and click Modify.  You can choose from one of the colors on the Standard tab, or you can click the Custom tab if you know the RGB or HSL format of the color you wish to use. 

Repeat these steps as often as necessary, then when you return to Excel, you can change the fill, font, and chart colors in your workbook to your new custom colors.

Microsoft Excel is jam packed with cool useful features that tend to go unused by most users, simply because they don’t know how to use them, or even that they exist.  One of these features is Excel’s Goal Seek tool.

First, some background on what it does.  Goal Seek is essentially the answer to every middle school aged kid’s math test word problem.  It is best used when you know the answer to the problem you’re trying to solve, but don’t know all the inputs.

For example, a simple word problem might be “You have 432 sheets of paper to give out to a class of 36 students.  How many sheets of paper does each student get.”  Well you know that 36 multiplied by something will give you 432, but you don’t know what that number is.  You could divide 432 by 36 to get the answer, but let’s assume that isn’t possible right now.  With Goal Seek, you need several things:

• An answer expressed as a formula, in this case 432 is equal to 36 times something.  Don’t worry, the result of the formula doesn’t have to equal 432 yet.  That’s the magic of Goal Seek.
• You need one part of the equation, in this case 36.

To start, type the following into your spreadsheet in the cells indicated.

Next, open Goal Seek (Tools | Goal Seek, or Data tab on the Excel 2007 ribbon | What If Analysis | Goal Seek).

You are now going to be asked for three things. 

1. Set Cell: this is the reference to the cell that contains your formula, in which you want Excel to produce your answer of 432.  In our example it should be A3.  Note: the Set Cell MUST contain a formula or function.
2. To Value: this is the value you want in your Set Cell (A3).  The value here should be 432.
3. By Changing Cell: Since we know that the formula in cell A3 is correct, and the value of 36 in cell A1 is correct, we want Excel to produce the answer of 432 by changing cell A2.  Type A2 in this box.  Note: the Changing Cell MUST contain a value.
4. Click OK.

After clicking OK, Excel will attempt to find a solution to the equation.  Once it does, it will enter the missing number in cell A2 and your formula should now equal 432.

Obviously this is a basic example, and doesn’t use Goal Seek’s capabilities to their fullest extent.  Let’s take another example that looks at a company’s sales by product to get their desired level of revenue.

XYZ Company makes four different products, Product A, Product B, Product C, and Product D.  The company can produce 1,000, 750, 500, and 100 of each product respectively.  The products also sell for $15, $20, $35, and $50 respectively.  Their maximum revenue for products produced that month, therefore is as follows:

Now say the company feels that their underdog, Product D, could be producing much more.  They also want their revenues to be 60,000.  How many more of Product D will they need to produce?

To figure this out, run Goal Seek just like before, except this time, the “Set Cell” is cell D6, “To Value” is 60,000, and the “By Changing Cell” is cell B5.  After clicking OK, you’ll notice that Excel has changed the value in cell B5 to 250, indicating that you will need to increase production of Product D to 250 units from 100 in order to have revenues of $60,000, assuming everything else remains the same.

OK, I know the tag-line for this website is “…because it’s more than just a calculator”.  Of course this is in reference to the almost endless number of functions that Excel can calculate when compared to a standard calculator.  That isn’t to say though that Excel can’t calculate things just as well as a standard calculator.

For example, when balancing a checkbook, you could write out your bank balance and manually add or subtract the deposits and checks that haven’t cleared the bank yet.  Or, you could type your bank balance into Excel, type the list of checks and deposits and have Excel do the math for you.

You could also add a list of numbers together in one cell the opposite way from how you would calculate them on a calculator.  If you have the following list of numbers: 8, 17, 49, 32, 19, you would add them on a calculator by typing 8+17+49+32+19= to produce 125.  In Excel, the equals sign is on the opposite side of the equation.  You simply type =8+17+49+32+19 hit Enter and the cell will display the result, 125.

You can do this with all the familiar operators found on a calculator (add, subtract, multiply, and divide), as follows.

So, while Excel is more than just a calculator, it can still perform various functions like a calculator.

For all of you out there still wondering where the “File” menu is in Excel 2007 and other Office 2007 products, another twist is in store for you.  Microsoft announced earlier this week that they are planning on releasing Office 2010, which includes updates to Word, Excel, PowerPoint, Outlook, OneNote, Publisher, Access, SharePoint Workspace (formerly Groove 2007), and InfoPath.

In addition to the new programs, Microsoft is also introducing Office Web Applications, which compliments Word, Excel, PowerPoint, and OneNote to allow you to access your files from anywhere with an internet connection.  It also allows you to share documents with others and even gives you the ability to work simultaneously on the same document.  Further adding to their product offerings is Office Mobile, which allows you to view Word, Excel, and PowerPoint files on a mobile version of the software.  Email will also be easier to manage with Outlook Mobile.

OK, so enough with the Office features, what’s new in Excel 2010?

Spreadsheets can now be run in your web browser, and be published to the web via the desktop version.  The browser version does not have as many features as the desktop version, however it is an improvement over Google Spreadsheets.  Additionally, a new analytic feature is called Sparklines, which produce a visual trend of a data set over time similar to a chart, except the Sparkline is contained within a single cell.  For example, if you’re tracking monthly sales of a product, you can set a Sparkline that will show the peaks and valleys that the data produces.

One thing users of Excel 2007 (and other Office 2007 programs) who are just getting used to the new Ribbon will not have to worry about is learning another new user interface.  The ribbon should remain essentially the same as in the 2007 version, except for a few new features.

This post will get updated as I discover new features, so please bookmark it and check back often.

Using charts in Excel can be a very powerful tool when used correctly.  They can help to quickly visualize trends, and analyze results.  The charts, however are useless if the person who is looking at them can’t tell what the data is that they are trying to analyze.

Excel helps the user by inserting labels in key points to help the user identify what they are looking at.  If you have a dynamic set of data (that is, it can change and represent different different sets of data) that feeds a chart, you might not want to use Excel’s default data labels.  Luckily Excel will allow you to link certain labels to a cell in your file, which will update the label’s value each time the cell value changes.

To do this, simply click on the label so that there is a box surrounding it.  Then click in the formula bar and type “=A1″ (assuming you wish to link the label to cell A1).  Hit Enter and you’re all set!

Linking the chart’s labels is useful when, for example, you have some monthly sales information set up so that when you type “Monthly” in cell A1, it displays the data by month, and when you type “Quarterly” in cell A1 it displays it by quarter.  Essentially its the same data, just aggregated a little differently. 

Having a chart that is as dynamic as the data would be nice so that you don’t have to keep changing the chart title from “Monthly Sales Data” to “Quarterly Sales Data”, or the X-Axis label from “Monthly” to “Quarterly”, and back again each time you change the views.