Excel Zoom

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 G8 (labeled “Total Losses” in cell F8), I have the following formula: =(G10*C3)-(G10*E3), 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 G8, then click Tools | Goal Seek.  In the box that pops up, enter the following:

• Set cell: Keep as G8.  If it isn’t G8, enter G8 here.
• To value: Enter 1,936.10, as this is the value we wish to set the losses to.
• By changing cell: Enter cell G9, 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.

Being able to copy and paste a cell’s information from one location to another is a great time saving feature, but sometimes you don’t need to copy everything from the source cell.  Thankfully, Microsoft Excel will allow you to copy a cell and paste only selected information if you wish.

The menu below is a screenshot of the Paste Special menu from Excel 2007, which, except for the “All using Source theme” option, is the same menu as is found in earlier versions of Excel.

Excel 2007 Paste Special options

By pasting using the default options as shown in the image, Paste Special will paste the same way that the regular Paste option will in Excel.  To get more bang for your buck, let’s explore the various options that Paste Special offers.  Note that the following “Paste” options are assuming that you are selecting “None” from the Operation section (discussed below), and that Skip blanks and Transpose are not checked.

• Formulas: Assuming that the cells you copied contained formulas, you can use this option to paste only the formulas in your destination cells.  This is useful, because it retains the formatting of the destination cells, including comments, which might be different from the source cells.  Be careful with this, as any cells referenced in your formulas will change unless they are absolute or mixed references.
• Values: This option will only paste the values (numeric or text) as displayed in the source cell.  It doesn’t matter if the source cell has a formula in it, Excel will convert the data and only paste the values as displayed in the source cell.  This is useful if you wish to paste data from one worksheet that contains formulas into another that does not.
• Formats: This option is the opposite of the previous two options.  Where the previous options pasted data (formulas or values), this option only pastes formats, and is similar in use as the Format Painter  option found in Excel.  This is useful if you have a range of cells with data that are formatted in a particular way that you would like to copy to another location.  If the data is not necessary to paste, then this option will strip out the unnecessary data.
• Comments: If you have a cell with a comment that you would like to have in another cell, this option allows you to paste only the comment, without pasting any of the formats or data from the source cell.  This option saves you the time of re-typing the comment, while preserving the data and formats in the destination cell.
• Validation: When creating Data Validation rules in your cells, you might want to apply the rules to other cells after they have been created.  You can do that with this option without copying any source data or formats, and preserving the destination data and formats.
• All using Source theme: This option is new in Excel 2007.  It pastes all the information from the source cell (numbers, formulas, text, formats, comments, etc.) and applies the source worksheets theme’s colors, fonts and graphics to the destination.
• All except borders: This option will paste any numbers, formulas, text, comments and formats except for borders from the source cell and paste them in the destination cell.
• Column widths: To present a uniform appearance across your spreadsheet, the column widths option will allow you to copy one column’s width and apply it to whatever columns you select.  If your spreadsheet had one really narrow column of data, next to a very wide column, which was next to a regular sized column, you could copy the regular sized column, select the narrow and wide columns and paste the column width so that all three columns are of equal width.
• Formulas and number formats: This option will paste only the formula from the source cell and the number formats applied to that cell.  It won’t paste the other formats (border, fill color, font, etc.), just the number formatting, such as percentage, currency, accounting, scientific, etc.
• Values and number formats: This option will paste the same information as the Formulas and number formats option above, except it won’t paste the formulas from the source cell, it will only paste the values.  This is similar to the Values option discussed above with the addition of the number formatting.

The Operation section allows you to perform a mathematical operation with the data you have copied.

For illustration purposes, I’m going to assume that the source cell to be copied contains the number 4, and the destination cell contains the number 10.  Below I’ll discuss the results of each Operation option.  Also, assume that the “All” option is selected from the “Paste” section with Skip blanks and Transpose left unchecked.

• None:  As discussed previously, this operation will perform no arithmetic, and therefore the resulting number in the destination cell will be 4.
• Add:  By selecting 4 from the source cell and pasting it into the destination, which contains 10, Excel will add 10+4 to return 14.
• Subtract:  Again, Excel will subtract 10-4 to return 6.
• Multiply: Excel will take the product of 10 and 4 to return 40.
• Divide: Excel divides 10/4 to return 2.5.

Finally, you’ll notice three more options on the menu: Skip blanks, Transpose and Paste Link.

• Skip blanks: This option does just as it says.  Say you have a set of data in cells A1:A5, where A1 = 100, A2 = 200, A3 = blank, A4 = blank, and A5 = 500.  Now you have another list of numbers in B1:B5, where B1 = 600, B2 = 700, B3 = 800, B4 = 900, and B5 = 1,000.  If you copy the cells A1:A5, then select cell B1, go to the Paste Special menu, check the Skip blanks box and click OK, the values you should see in B1:B5 are as follows: B1 = 100, B2 = 200, B3 = 800, B4 = 900, and B5 = 500.  This is because the Skip blanks option tells Excel to only paste what is in the source cells where there is data to paste.  If the cells are blank, Excel will skip over them and not paste anything, which is why cells B3 and B4 had the values of 800 and 900 respectively instead of being blank.
• Transpose: Take the same original list from cells B1:B5, going sequentially from 600 through 1,000.  Copy cells B1:B5, then select cell C1.  Go to the Paste Special menu, check Transpose and click OK.  The values in cells C1:G1 should be the same sequential values (600 through 1,000) from B1:B5.  Essentially, all this option does is take a copied list of numbers from a column and pastes them across a row, or copies a list of numbers from a row and pastes them down a column.
• Paste Link: This option can save a lot of time if you’re working with multiple worksheets or multiple files.  If you want your destination cell to link to the source cell you can use this option.  Assume you have a value in cell A1 that you want cell C1 to link to.  You can copy cell A1, select cell C1, go to the Paste Special menu and choose Paste Link.  In cell C1, you should see the formula =$A$1.  If you select multiple cells to paste, the formula will be a relative reference, and not an absolute reference (i.e. it won’t have the dollar signs $).

As you can see there are many applications for the Paste Special feature, which can save you a lot of time if you only need to copy a limited amount of information from your source cells.