Excel Zoom

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.

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.

Adding comments to your worksheet allows you to share your thoughts with other users, or to serve as a reminder for yourself.

It’s also useful to be able to add comments to a formula, so that you can let other users of your workbook know if you’ve made a change to the formula, let them know what the result of the formula represents, or share other information about the formula.

After entering your formula, type +N(“Your comment here”)

For example if you have a formula that produces a class average, you can type:
=AVERAGE(A1:A25)+N(“Period 1 Class Average”)

Just to have a little fun with the new Twitter account, I thought I’d put together a little Excel tool to make fitting into the 140 character limit a little easier.

Simply type what you want to say, and if it exceeds the 140 character limit, it will append “…” to the end, so that the last “.” will be the 140th character. That way you’ll know how your post will look before putting it online.

To make this more than just a formula that chops off anything in excess of 140 characters, also included is a macro that will open up your Twitter home page and copy the text that is already pre-formatted to 140 characters or less. Now all you’ll have to do is paste the text into the “What are you doing?” box and click “Update”.

Download the file here.

Often times, you’ll do work in one Excel file, that would be useful to show in another Excel file. You can simply copy and paste the data in, but if the data is dynamic (i.e. changes often), you might want to link between the two files.

You can link to a cell in another file just as easily as you can link to a cell in the same workbook, the only difference is that you need to specify the file location of the workbook you are trying to link to.

To explain, we’ll start by showing how to link to another cell in the same worksheet, to another cell in a different worksheet but in the same file, and then to another cell in a different file. Note: in all examples below, we’ll assume that you want to link to cell B1 in whatever worksheet you’re referencing.

First, to link to another cell in the same worksheet, type “=B1″ (without the quotes ” “). This will produce whatever is in cell B1 in the current worksheet.

To link to another worksheet (say, Sheet2) in the same file, type “=Sheet2!B1″ (without the quotes ” “). This will produce whatever is in cell B1 in the worksheet named “Sheet2″.

To link to another worksheet in another file (assuming the other worksheet is called Sheet1, and is stored on your desktop, with a file name Book1.xls), type “=’C:Users**YOUR_USERNAME**Desktop[Book1.xls]Sheet1′!$B$1″ (without the quotes ” “). This will produce whatever is in cell B1 in the worksheet named “Sheet1″ stored in the file Book1.xls located on your desktop. Note: Replace **YOUR_USERNAME** with your computer’s logon user name.

Also, if you have the two files open, you can type “=” (without the quotes ” “) into your worksheet and then use your mouse to click on the cell you wish to link to. Excel will automatically fill in the appropriate information.

When you enter a cell reference into the formula bar, you can press the F4 button to make the reference either absolute, mixed, or relative. This helps your formula work as expected when you copy the formula to another location

• Absolute references look like this: $A$1, where the dollar signs ($) indicate that when copying a formula to another cell, Excel should not change the reference to cell A1.
• Mixed references look either like $A1 or A$1. The dollar sign ($) in $A1 means that if you copy the formula, it will always reference column A, however if you copy the formula down, it will change the row number relative to its new location. The dollar sign ($) in A$1 does just the opposite. Copying a formula will always reference row number 1, however it will change the column letter relative to its new location.
• Relative references look like this: A1, where there are no dollar signs ($). This indicates that both the column letter and the row number will change when copying a formula relative to the new location.

Always check to see if your formula is using absolute, mixed or relative cell references prior to copying them to another location.

Often times, a project manager will be given a project’s start date and the number of days it should take to complete. Considering, however, that most people work Monday through Friday, you can’t simply add a certain number of days to the starting day’s date to come up with your ending date, because that wouldn’t take into consideration weekends and holdiays.

Using the formula =WORKDAY() allows you to specify a beginning date, the number of work days until the ending date, and any holidays that might fall between the start and finish of the project.

If you wish to specify holidays, enter the holiday’s date value in the formula (i.e. Memorial Day in 2009 is May 25, 2009, or in Excel’s number format, 39958). This can be determined by using Excel’s =DATE() formula.

More on these formulas can be found on our Wiki page.

• WORKDAY Wiki Page
• DATE Wiki Page
• NETWORKDAYS Wiki Page

This macro takes whatever values are in a selected range of cells and multiplies them by -1. This is especially useful for accountants who might get a list of client data as absolute values. In accounting world, however, the debits and credits might need to have a sign reversed. With this macro, just select the cells you want reversed, and run the macro.

Copy all of the code below. Paste it into your workbook’s Visual Basic editor, either under a Microsoft Excel Object or Module.

Need help? Use our nifty guide to help figure out how to install and use your macros.

Sub negative_cell_value()
'MACROS BY EXCELZOOM.COM
'Makes data negative in selected cells
Dim cell As Range
For Each cell In Selection
If cell <> "" Then
cell.Value = cell.Value * -1
Else
'skip blank cells, or they'll get set to zero
End If
Next
End Sub

This macro will take the values in a given range and multiply them by whatever value you specify in the code. The code currently multiplies by 1,000,000, however replacing the 1000000 in “cell.Value = cell.Value * 1000000″ with whatever value you need. Use caution with this macro, as it will convert any formulas to a number multiplied by whatever value you specify (i.e. if cell A1 equals 1; running this macro on a cell referencing A1 will produce 1,000,000, not =A1*1000000).

Copy all of the code below. Paste it into your workbook’s Visual Basic editor, either under a Microsoft Excel Object or Module.

Need help? Use our nifty guide to help figure out how to install and use your macros.

Replace the xxxxxx in the code with whatever value you want to multiply your data with (i.e. 1000, 1000000, etc.).

Sub muliply_by_x()
'MACROS BY EXCELZOOM.COM
'Multiplies data by x (hardcodes, will lose formula)
Dim cell As Range
For Each cell In Selection
If cell <> 0 Then
cell.Value = cell.Value * xxxxxx
Else
'skip blank cells, or they'll get set to zero
End If
Next
End Sub

This macro has the same functionality of the “Multiply by X” macro, however it allows you to keep your formulas in tact, by simply adding *1000000 (or whatever value you specify) to the end of the formula (i.e. =A1 will become =A1*1000000, not 1,000,000 as per the example here).

Copy all of the code below. Paste it into your workbook’s Visual Basic editor, either under a Microsoft Excel Object or Module.
Need help? Use our nifty guide to help figure out how to install and use your macros.

Replace the xxxxxx in the code with whatever value you want to multiply your data with (i.e. 1000, 1000000, etc.).

Sub mult_by_mil_bycell_keepformula()
'MACROS BY EXCELZOOM.COM
'multiplies FORMULAS by x
Dim Orig_formula As String
Dim formulaRg As Range
Dim formcell As Range

Orig_formula = formcell.Formula
Orig_formula = Mid("", 1, 1) & Mid(Orig_formula, 1) & "*xxxxxx"
formcell.Formula = Orig_formula
Next
End Sub